This Excel tutorial explains how to find the percentile of a number from an array using PERCENTRANK.EXC function.
Excel Percentile
Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the loc...
Book 1 – Microsoft Excel
Excel VBA copy contents of protected worksheet
This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet.
You may also want to read:
Excel VBA protect worksheet with password
Excel VBA hide worksheet with password (xlVeryHidden)
Copy contents of protected worksheet
Some authors would protect worksheets with password so that other people cannot modify and not even able t...
Excel VBA convert date period to date row
This Excel VBA tutorial explains how to convert date period to date row.
Excel VBA convert date period into date row
Different systems store date transactions differently. For example, if you apply for annual leave from Jan 1 to Jan 3, some system store the data in data base using date period
Begin Date
End Date
1/1/2018
1/3/2018
However some system store one date per row.
...
Excel VBA filter value then copy filtered data to new worksheet
This Excel VBA tutorial explains how to automate AutoFilter to filter value and then copy data to new worksheet or copy data to new workbook.
You may also want to read:
Excel VBA copy each worksheet to new workbook
Access VBA auto generate mass report by group to Excel
Filter value then copy filtered data to new worksheet
I find it very difficult to come up with a suitable title and key words f...
Access Excel VBA sort Array items
This Access Excel VBA tutorial explains how to sort Array items in VBA in ascending order / descending order.
You may also want to read:
Use VBA Excel Function to sort data in ascending order
Access Excel VBA sort Array items
In an Array, you may have stored items that you want to sort. For example, if you store a name list, you may want to sort them in alphabetical order (Apple, Banana, C...
Excel check duplicate values using Conditional Formatting
This Excel tutorial explains how to check duplicate values in Excel spreadsheet by highlighting duplicate values using Conditional Formatting.
You may also want to read:
Excel assign sequence number to duplicate records
Excel delete duplicated data in consecutive rows
Excel check duplicate values
Back in Excel 2003, it was a pain to identify duplicate values. What I used to do was to sort...
Excel convert data from column to row
This Excel VBA tutorial explains how to convert data from column to row (transform one column data to one row).
You may also want to read:
Excel VBA consolidate multiple workbooks into one workbook
Excel VBA combine worksheets columns into one worksheet
Excel convert data from column to row
Different database have different structure. Some database put similar data in the same column wi...
Excel Exact Function – compare case sensitive text / case sensitive lookup
This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup.
Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup)
In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, "Peter" is considered same as "peter" in Excel.
Vlookup also ...
Access Excel VBA convert number to English words
This Access Excel VBA tutorial explains how to convert number to English words in VBA.
Access Excel VBA convert number to English words
I want to share a custom Function I found in Microsoft Support website, which is to convert number to English words using Excel VBA (also applicable to Access). I want to highlight that the article is unlike other articles written by other contributors, this art...
Excel delete all pictures or delete all pictures in specific Cells
This Excel tutorial explains how to delete all pictures in a worksheet or delete all pictures in specific Cells.
Excel delete all pictures
When you copy contents from website to Excel, it is unavoidable to also copy unwanted pictures. As some websites contains dozens of pictures, it is a waste of time to delete pictures one by one. In this tutorial, I will demonstrate how to delete all pictures...