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…
Category: Tips and Tricks
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…
Tips & Tricks 96 – Abbreviate Given Names
If you have names given like – Smith Johnson Liz lotte Christy tiara Lewisk John And you need to produce abbreviations or acronyms for them like below in all capitals Smith Johnson – SJ Liz lotte – LT Christy tiara Lewisk – CTL john – J Then you can use following formula for the same…
Tips & Tricks 95 – Send Excel Workbook / Worksheet as Attachment
You can very easily send an Excel / Workbook as an attachment directly from Excel. To send Excel Workbook To use this functionality, your workbook need not be saved. When you use a step from below, it will attach that point of time copy of Excel workbook in the mail. File > Share > E…