There are 3 different types of protection in Excel. Protecting the Workbook, Protecting the Worksheet and Protecting the structure of the Workbook Protect an Excel File We can protect the content of an Excel Workbook by adding a Password to it. Go to the File tab in the Excel ribbon > Info > click on … 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
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more
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 … Read more