Sometimes, you find that all of a sudden your column headings are changed into numbers and your formulas are Rs and Cs. And you need to change them back to alphabetical columns. The option to do it very simple – File > Excel Options > Formulas > Uncheck R1C1 Reference Style But if that happens…
Category: Tips and Tricks
Tips & Tricks 108 – Extract Date and Time from Date Timestamp
Suppose you have a date timestamp value in cell A1 A1 = 06/14/15 10:15 PM And you want to extract date and time out of this. To extract date, use following formula and format the result cell as date = INT(A1) To extract time, use following formula and format the result cell as time =…
Tips & Tricks 107 – Autofill on a Filtered List
Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill – Article 7 – Generate a Sequence of Numbers Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The…
Tips and Tricks 106 – Find the nth Largest Number when there are duplicates
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…
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 –…