

Category Archives for "Formula Hacks"

9 Find the Last Occurrence of a Lookup Value a List in Excel

In this tutorial, you’ll learn how to find the last occurrence of an item in a list using Excel formulas. Recently, I was working on setting the agenda for a meeting. I had a list in Excel where I had a list of people and the dates on which they acted as the ‘Meeting Chair’. […]

10 Excel Holiday Calendar Template (FREE Download)

Excel Holiday Calendar List Template - Demo

Last few weeks have been really hectic for me at work. Weekdays are crazy and the weekends are never enough. Early this week, I was going through our annual holiday calendar to check for the next holiday, and plan a vacation if possible. And that gave me the idea for this Excel holiday calendar template. In […]

1 How to Round to the Nearest Integer or Multiple of 0.5 / 5 / 10 in Excel

Watch Video – Round to the Nearest Integer or the Multiple of 0.5 / 5 / 10 in Excel Rounding a number to the nearest integer or the nearest 0.5 or 5 or 10 multiple is a common task for many people. For example, if you’re a project manager involved in effort estimate, you can’t […]

85 Get the List of File Names from a Folder in Excel (with and without VBA)

On my first day in my job in a small consulting firm, I was staffed on a short project for three days. The work was simple. There were many folders on the network drive and each folder had hundreds of files in it. I had to follow these three steps: Select the file and copy its […]

0 Test Multiple Conditions Using Excel IFS Function

Excel 2016 came with a new function – the IFS function. You can use this function to test multiple conditions at once and then return the result based on it. This is helpful as you don’t have to create those long nested IF formulas that used to get confusing. Note that the IFS function is […]

5 Get Only the Numeric or the Text Part from a String Using Excel Formulas

Dataset to get the numeric or the text part in Excel

There is no inbuilt function in Excel to remove all the text part from a string and get only the numeric part (or vice versa – remove the numeric part and give only the text part from a string in a cell). However, this can be done using a cocktail of Excel functions. Let me show […]

5 Lookup the Second, the Third, or the Nth Value in Excel

Formula to lookup the second third and nth value

Watch Video – Lookup the Second, the Third or the Nth Matching Value When it comes to looking up data in Excel, there are two amazing functions that I often use – VLOOKUP and INDEX (mostly in conjunction with the MATCH function). However, these formulas are designed to find only the first instance of the lookup […]

1 2 3 … 7 Next »