In the year 1882, a German philosopher, Friedrich Nietzsche made a claim that has revolutionalized Philosophical and Theological discussions since then. His Claim–God is Dead. There has been a similar discussion in the Excel world, not about God, but about VLOOKUP. Of all the over 450 functions in Excel, VLOOKUP is one of the most popular […]
In the previous article, we looked at 7 ways to find the last or Nth occurrence in a Sorted list in excel. Unsorted list poses a challenge since you need to find the occurrence using multiple criteria. To show the different methods I have created a list of Customers and their Purchase Orders (P.O). You can download […]
There are times when you want to look up and retrieve values based on a list. Normal filtering can only take up a maximum of two items and limited criteria conditions. In this article, you shall learn 4 ways to lookup items not matching a list. For example, below we have a list of mechanics […]
Duplicates can be a pain in data analysis leading to the wrong conclusion. This is why knowing how to lookup unique values is very important. This article shows 2 ways to tackle the duplicate data menace and create a dynamic lookup table with unique values. The task is to create a dynamic lookup list of […]
Every organization requires weekly summaries to monitor peak and off-peak periods. This is mostly so when you are in the production section. Excel has a beautiful but overlooked function (WEEKNUM) that returns an integer representing the week in the year (1 to 53). For anyone not familiar with WEEKNUM function, it contains 2 arguments; Serial_number ( […]
When was the last time you used excel tables? Do you know how powerful excel tables are? If you have never or not recently used excel tables, it could be because you have not realized how powerful they are in data analysis. By reading below 7 excel table’s secrets, your answer to above question will […]
The ability to lookup number values in excel is a must have for all data analysts. Some Excel users just know the basic VLOOKUP function but there are 7 more functions to lookup values. The more lookup function you know the better you become in retrieving numbers like sales for a certain month, a balance for a […]
When is the last time you comprehensively looked at HLOOKUP function and its alternative? To look up data horizontally, many excel users tend to use HLOOKUP but it has its limitations. So what are the alternatives? In this article, we shall look at? Horizontal lookup using HLOOKUP, VBA & INDEX & MATCH How to return multiple items in a Horizontal Lookup How […]
On Linkedin, a user commented on how he has been struggling to recreate Federal Reserve dot Plot. After being puzzled for almost 2 years, he came to realize how REPT function in excel can be of great help. After searching the web, I have found there is no comprehensive article on how to recreate this […]
When is the last time you used REPT function in Excel? REPT is one of excel’s little-known, overlooked and underutilized function, yet very useful. Generally, REPT returns a specific text string a specified number of times. =REPT(“Text”, Number of times) Here are 7 ways you can start using REPT function. ADD LEADING ZEROS CREATE INLINE […]
IF function is one of the most used functions in Excel. In my opinion, it is the foundation of all programming and Excel’s formulae mastery. However, it is also one of the most misused functions, especially Nested IF. Especially now with Excel 2007 and beyond, you can nest up to 64 IF functions to form complex, […]
