This Excel tutorial explains how to select specific columns in a worksheet with many columns using Custom Views and Query.
You may also want to read:
Create Excel Query and update Query
Excel automatically select specific columns using Custom Views and Query
Many people including myself like creating a master report with many columns and then send it to users for them to manually select columns they need. The reason is that it is time consuming to customize a lot of reports and it is dif...

More
# Chapter 7 – By Excel Topic

# Excel find the percentile of a number using PERCENTRANK.EXC function

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 location of observation at 40% of total data.
Percentile is commonly used in salary survey to find o...

More
# 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.
Begin Date
End Date
1/1/2018
1/1/2018
2/1/2018
2/1/2018
3/1/2018
1/3/2018
...

More
# Excel assign sequence number to duplicate records

This Excel tutorial explains how to assign sequence number to duplicate records using COUNTIF Function.
You may also want to read:
Excel check duplicate values using Conditional Formatting
Excel delete duplicated data in consecutive rows
Excel assign sequence number to duplicate records
Suppose you have two sales transactions on the same date but you don't have any transaction timestamp (but records are in ascending order of time), you may want to add a number to each transaction so tha...

More
# Excel dependent dropdown list | dependent validation list

This Excel tutorial explains how to create dependent dropdown list where dropdown list values depend on another list value.
Excel dependent dropdown list / dependent validation list
Suppose you have a continent list in Excel (Asia, North America, South America, etc). When an user selects a continent from a dropdown list, the user can then select the second dropdown list which contains all the countries of your selected continent, so the country list is dependent on the continent value.
For ...

More
# Excel compare time or apply condition on timestamps

This Excel tutorial explains how to compare time or apply condition on timestamps in Excel spreadsheet
Excel compare time or apply condition on timestamps
When we have two timestamps (a date with time) on the same date, we can use simple subtraction on the two timestamps to compare.
If the two timestamps have different dates, we can still minus the timestamps to compare.
But if we only want to compare the time only without considering the date, we need to extract the time portion of time...

More
# Excel calculate year of service with different methods

This Excel tutorial explains how to calculate year of service in Excel using different calculation methods.
Excel calculate year of service
Having worked for human resources field for some years in different companies, I have seen different systems / companies use different calculation methods to calculate year of service for annual leave entitlement. In this post, I want to summarize how to use Excel to calculate year of service using different methods to help HR professionals.
Method 1 - ca...

More
# Excel Security Warning – Macros have been disabled

This Excel tutorial explains how to solve the problem of Excel Security Warning - Macros have been disabled.
Excel Security Warning - Macros have been disabled
This Excel Security Warning is one of the most annoying warnings in Excel. If you click on the Options button, you will see the below message.
In fact, this message does say something about the reason for this alert, it explained that this error is caused by encrypted Macro. What it means encrypted is that the Workbook contai...

More
# Excel VBA find all Cells contain Named Range

This Excel VBA tutorial explains how to find all Cells contain Named Range using Macro.
You may also want to read:
Find all external links and broken links in workbook
Replace Named Range with formula
Find all Cells contain Named Range
In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager)
We can refer to Name Manager to see the formula the Named Range is referring to, but it does not tell which Cells are using the Named Range. Therefore...

More
# Excel extract text in brackets

This Excel tutorial explains how to extract text in brackets or in defined text in Excel spreadsheet and VBA.
Extract text in brackets in Excel spreadsheet
In the previous post, I demonstrated how to remove text in brackets or in any defined text, in this post I will explain how to extract the text in brackets or in any defined text.
In Excel spreadsheet, you can extract any substring using Mid Function.
To recap the syntax of Mid Function
MID( text, start_position, number_of_characte...

More