TEXTSPLIT and 13 New Functions in Excel

In this blog post, we will have quick look on the 14 New Functions added to Excel on the 3rd week of March 2022. They are TEXTSPLIT, TEXTAFTER, TEXTBEFORE, VSTACK, HSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSEROWS, CHOOSECOLS, and EXPAND The first 3 of these new functions TEXTSPLIT, TEXTAFTER, TEXTBEFORE are designed for manipulating…

Two Way Lookup in Excel

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…

Different methods to Extract only Numbers from a Column

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…

Separate Positive and Negative numbers in Excel

Following are 3 different ways to segregate Positive and Negative values from list of numbers in Excel. MAX and MIN Function to separate Positive and Negative values The MAX function in Excel will return the largest among 2 or more numbers supplied into it. This property of MAX function is used below to check whether…

Different ways to find SUM in Excel

This blog post is about 10 different ways to find the Sum in Excel. We have a list of 5 numbers in the cells B3, B4, B5, B6 and B7 as shown below. Now let me show you the most common and uncommon methods for finding Sum in Excel. Using Addition Operator ‘+’ =B3+B4+B5+B6+B7 SUM…

Highlight cells according to current Time in Excel

This blog post is about combining the NOW function with the Conditional Formatting feature in Excel to highlight a cell or cells corresponding to Current Time. The following is a very common spreadsheet template used at working environments for recording manhours. Let’s see how to spot the cell corresponding to 10:23 A.M (Current Time) on…

Find the Count of each Item in a List in Excel

In this blog post I will explain 3 different methods to find the Count of each Item in the list shown below. The methods include the use Pivot Table, Power Query and Formulas. Note that I have converted the list into an official Excel Table and named it as ABCD. Dynamic Array Formula to find…

Find Common Values between two lists in Excel

This Excel tutorial is about 3 different methods to find the common values between two Lists or Tables in Excel. Here we have 2 lists of World’s Billionaires, one from the year 2015 and other one from 2020. Following are different methods to find the Names that are present in both lists. MATCH function to…

Different methods to find Duplicates in Excel

Following are 3 different methods to find the duplicates in a dataset. FILTER Function to find Duplicates In this formula, we will use the COUNTIF function to find out how many times each value is repeated. Then the FILTER function is used filter out those values with a count greater than 1. Combining FILTER and…

Different methods to find the Sum of first 50 Natural numbers

Following are 4 different methods to find the Sum of first 50 natural numbers using Excel. Formula using SUM and SEQUENCE function The following formula will return the sum of numbers from 1 to 50. =SUM(SEQUENCE(50,1,1,1)) For the sum of first 75 numbers, =SUM(SEQUENCE(75,1,1,1)) Formula using SUMPRODUCT and ROW function Formula that combines SUMPRODUCT and…