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…
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…
Paste Special to add a Number to multiple Cells The Paste Special feature in Excel helps us to perform Addition, Subtraction, Multiplication and Division with multiple cells and data ranges in Excel. In the following screenshot you can see two data ranges filled with numbers, B2:D11 and F5:J9. To add 10 to all those numbers…
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…
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…
Following are 4 different methods to generate random numbers in Excel RAND Function The RAND function in Excel can be used to generate a random number between 0 and 1. The RAND function is a volatile function and will update every when the worksheet is recalculated. RANDBETWEEN Function The RANDBETWEEN function returns a random number…
In this blog post, we will see how to calculate Time difference in Excel. The methods include calculating Time difference with Overnight schedules. Let’s say we have the ‘In Time’ and ‘Out Time’ of an Employee for a week. Following are the different methods to calculate the difference between the In and Out times. Simple…
In this blog post we will go through 3 different formulas to add Hours and Minutes to Time in Excel. Before jumping into the formulas we should know that Excel stores Time as Numbers. In Excel, 1 Day = 1 1 Hour = 1/24 (1 Day/24 hours) 1 Minute = 1/(24×60) (1 Day/24 hours x…
Till date, GoogleSheets doesn’t have any in-built function to convert Numbers to Words. But a developer, Alexander Ivanov has created a GoogleSheets add-on called NumberText which makes the above translation possible. When we install the above said Add-on, we get access to two Custom functions, NUMBERTEXT and MONEYTEXT. NUMBERTEXT will convert Numbers into Words of…
5 x 5 Rule of PowerPoint Each of your slide should have no more than 5 lines. Each of those lines should have no more than 5 words. An improved version of this rule, which is called ‘5 x 5 x 5 rule’ suggests ‘not to use more than 5 text heavy slides in a row’. Why…