This article is about 3 different methods to Reverse a List in Excel. Following is a list of candidates applied for a job. Let’s see the different methods to reverse this list for scheduling the interview. 1. Reverse the list using a Helper column Create a helper column with sequential numbers (for example 1,2,3,..) adjacent…
A few lesser-known, but pretty useful Keyboard Shortcuts in Excel are explained in this post. 1. Legacy shortcut for Creating Table Ctrl + T is a very popular Excel shortcut that is used to create Tables. But there is one more shortcut for creating a Table in Excel and that is Ctrl + L. Select…
Basic formulas for calculating Percentage, Percentage Difference and Formulas to Add or Subtract n% to a number are explained in this blog post. Formula for calculating Percentage Scenario 1 Following is a stadium of seating capacity 80,000. If 51,296 of the seats of this stadium are filled, what is the Percentage of Occupancy? Percentage of…
In this blog post, I will explain the step by step procedure to Connect Google Sheets with Excel using Power Query. Once a spreadsheet in Google Sheets is connected with an Excel file, the changes made in the Source (Google Sheet) will be reflected in the Excel file at the expense of a single mouse…
Let’s see how to combine the SUMPRODUCT and TEXT functions in Excel to find out the number of birthdays in a particular month. Formula We have a list of Date of Births in the cells C2:C17. The following formula will return the number of birthdays in the month of July. =SUMPRODUCT(–(TEXT($C$2:$C$17,”MMMM”)=”July”)) Explanation SUMPRODUCT functions multiplies…
By combining the SUMPRODUCT and YEAR functions in Excel we can find the number of Dates in a given Year. Formula Here we have a list of dates in the cells from B3:B15. The following formula will return the number of dates in the year 2020. =SUMPRODUCT(–(YEAR(B3:B15)=2020)) Explanation SUMPRODUCT functions multiplies the arrays specified in it and returns the…
By combining SUMPRODUCT and ISERROR functions in Excel we can find the number of cells containing the Errors. Formula The following formula returns the number of errors in the range E3:E12. =SUMPRODUCT(–ISERROR(E3:E12)) Explanation SUMPRODUCT functions multiplies the arrays specified in it and returns the ‘sum of products’ as result. If a single array is used…
To count the number of cells that contain a particular text, COUNTIF function can be combined with the Wild Card * (Asterisk). Formula to find the Count of cells that contain a specific Text The following formula will return the number of cells containing the word ‘burger‘ in the range B3:B12. =COUNTIF(B3:B12,”*burger*”) Explanation COUNTIF function…
In this blog post, I will walk you through the Top 25 Tips & Tricks in Microsoft Word. 1. Insert Date and Time Alt + Shift + D is the shortcut for inserting Current Date into a word document. Similarly, Current Time can be inserted into a document using the shortcut, Alt + Shift +…
The following is the generic formula to extract the numbers from Alphanumeric Data. =IF(SUM(LEN(Data)-LEN(SUBSTITUTE(Data, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0, SUMPRODUCT(MID(0&Data, LARGE(INDEX(ISNUMBER(–MID(Data,ROW(INDIRECT(“$1:$”&LEN(Data))),1))* ROW(INDIRECT(“$1:$”&LEN(Data))),0), ROW(INDIRECT(“$1:$”&LEN(Data))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(Data)))/10),””) As you can see in the following example, the formula returns all numerals from the text string supplied to it. Even if this formula is handy in several situations, the tool which I…