You know the LARGE function which can find the nth largest value. Hence, if you have a series like below – And you give =LARGE(A1:A10,3), you get the answer as 18 Now, if we have a series like below Now, you give =LARGE(A1:A10,3) and now the result is 24. The reason is that large function…
Category: Tips and Tricks
Tips & Tricks 105 – Column Chart with Primary and Secondary Axes
You want to draw a column chart with two data series and one you want to make a primary and one you want to make secondary. Below, you have drawn a normal column chart in first chart. The second series (orange one), you have converted to secondary axis. Now, it has overlapped the first series….
Tips & Tricks 104 – Hide a Sheet Securely
You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only…
Tips & Tricks 103 – I need to fill in A-Z or a-z very often. What to do
1. Put =CHAR(96+ROW()) in cell A1. 2. Drag down till A26 to generate a to z. 3. Put =CHAR(64+ROW()) in cell B1. 4. Drag down till B26 to generate A to Z. 5. File > Options > Advanced > Go down till you find Edit Custom Lists (You have to go down till bottom) and…
Tips & Tricks 102 – How to Find all Links in an Excel Workbook
Rogue links troubling your workbooks. You have visually inspected them and still you have no clue where to find them, then read on… Approach 1 – Easiest Approach Bill Manville offers a free Add-in which finds all links given in your Excel. The utility is a very popular one and it should be must have…
Tips & Tricks 101 – Get Column Name for a Column Number
Let's suppose, you have a number in A1 and you want to get the column Name for that. Hence, if A1=1, you want "A" Hence, if A1 =26, you want "Z" Hence, if A1=27, you want "AA" and so on. The formula to derive the column name would be be – =SUBSTITUTE(ADDRESS(1,A1,4),1,"")
Tips & Tricks 100 – Get Sheet (tab) Name, Workbook Name and File Name through a formula
There are many situation while working in Excel that you need to get the name of the sheet. (Note – For formulas to work, the workbook must be saved at least once) The formula to retrieve file name would be – =CELL("filename",$A$1) The formula to retrieve the sheet name would be – =REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"") Note –…
Tips & Tricks 99 – Remove Middle Name
Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to remove the middle name. Hence, you want to have an answer Roberto Carlos here. Formula Way – The formula for the above case would be =IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"") OR =IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND("…
Tips & Tricks 98 – Extract Middle Name
Suppose, you have been given a name in A1 = Roberto Albert Carlos and you need to extract the middle name. We have already talked about extracting first name and last name in following Tips and Tricks. We also talked about extracting initial of a middle name. Tips & Tricks 22 – Extract First Name…
Tips & Tricks 97 – Force Text to Columns Wizard to Appear for CSV Files
You have a csv file and 1. You double click it, the file will be opened directly in Excel. 2. You opened the file from File > Open but still the file opens directly in Excel. The solution is to change the extension of file from csv to txt. Hence, if you are having a…