The VLOOKUP function in Excel is designed to search for a ‘Lookup Value’ in the first column of the specified Table and return the corresponding Value against the first matching Value. But what if there are two tables, one of which has the possible matching value? A single VLOOKUP function can’t lookup into two different … Read more
This blog post is about different ways to find the ‘Most Repeated Number or Text’ in a data set. Formula to find the most repeated Number The MODE function in Excel is designed to return the most repeated number from a numeric dataset. An example with vertical data ranges. Another example with a 2 dimensional … Read more
This blog post is about an Excel formula which will return the Count of a particular Weekday or Weekend between two dates. The following formula will give us the Count of ‘Sundays’ between ’01/01/2022′ and ’31/12/2022′. =SUMPRODUCT(–(WEEKDAY(SEQUENCE(“31-12-2022”-“01-01-2022″+1,,”01-01-2022”))=1)) Let’s see how this formula works. Explanation of the Formula The SEQUENCE function will generate a list of … Read more
This blog post is about 3 different methods to extract the decimal part of a Number. TRUNC function to extract the Decimal part of a Number The TRUNC function in Excel truncates a number to the given precision. The formula =TRUNC(4.159) will return 4. The following formula will extract the decimal part of the number … Read more
In Excel, we have the VLOOKUP function to perform Lookup in Vertical direction (Rows) and the HLOOKUP function to Lookup in Horizontal direction (Columns). But for 2 Way Lookup? i.e. to perform Lookup in both columns and rows? No. Excel doesn’t have a dedicated function for this purpose. We should create the formula for 2 … Read more
This blog post is about 5 methods to remove formulas from an Excel worksheet without altering the results. In the following dataset we have formulas in the columns D and E. Let’s see how to remove the formulas in the columns D and E while keeping the results intact. Copy and Paste as Values To … Read more
This blog post is about 4 different methods to Display Formulas in Excel. Following is the screenshot of the data used to explain the different ways for displaying formulas. Show Formulas button To display all those formulas used in a worksheet, go to the Formulas tab in the Excel ribbon and click on Show Formulas … Read more
In this tutorial, I will show you 5 different ways to Extract only Numbers from a list of Strings. Following is a Table containing Text as well as Numeric Values and we will be using this data for explaining the different methods. FILTER Function to extract Numbers The ISNUMBER function in Excel can be used … Read more
While dealing with tabular data in Excel, quite often you will need the same formula in multiple cells. Following are 3 quick methods to apply the same formula to an Entire Column in Excel. Using Keyboard Shortcut Ctrl + Enter is the keyboard shortcut to enter the same value or formula into multiple cells. So … Read more
Following are 3 different methods to reverse text strings in Excel. Dynamic Array Formula to reverse Text The following formula combines the MID, TEXTJOIN, LEN and the SEQUENCE function to reverse the text string supplied into it. =TEXTJOIN(“”,1,MID(B3,SEQUENCE(LEN(B3),,LEN(B3),-1),1)) Let’s see what happens when we supply the text ‘Hana’ into the above formula. =TEXTJOIN(“”,1,MID(“Hana”,SEQUENCE(LEN(“Hana”),,LEN(“Hana”),-1),1)) Length of … Read more