How to Change Default Excel Date Format (such as MMDD to DDMM)

Posted on April 2, 2019 by Wyman W

This Excel tutorial explains how to change default Excel Date Format from Change Date and Time Format in Control Panel. How to Change Default Excel Date Format In the country I live, we normally use date format dd/mm/yyyy. However whenever I work for the US company, the system defaults to mm/dd/yyyy. This is annoying and it causes Excel error. For example, if I mistakenly type UK format 31/12/2019, the date will be recognized as a text because there is no such month as 31. It is even risky if ...

Excel automatically select specific columns using Custom Views and Query

Posted on March 9, 2019 by Wyman W

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...

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

Posted on March 9, 2019 by Wyman W

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...

Excel VBA convert date period to date row

Posted on August 23, 2018 by Wyman W

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 ...

Excel assign sequence number to duplicate records

Posted on July 10, 2017May 5, 2018 by Wyman W

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...

Excel dependent dropdown list | dependent validation list

Posted on June 4, 2017 by Wyman W

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 ...

Excel compare time or apply condition on timestamps

Posted on February 4, 2017 by Wyman W

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...

Excel calculate year of service with different methods

Posted on January 25, 2017 by Wyman W

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...

Excel Security Warning – Macros have been disabled

Posted on January 15, 2017 by Wyman W

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...

Excel VBA find all Cells contain Named Range

Posted on December 26, 2016December 26, 2016 by Wyman W

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...