When you open a workbook and you change something in the workbook and you close the workbook, it will ask you to save the workbook. But sometimes, even if you have not changed anything, still the workbook asks to be saved even if you haven't changed. Point 1 – Microsoft has provided the answer to…
Category: Tips and Tricks
Tips & Tricks 158 – Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted
One of the negative points which gets attributed to VLOOKUP is that whenever a column is added / inserted within the range of VLOOKUP, the column index number doesn't change. Hence, it gives wrong result. Let's consider below dataset and for a given Emp ID, I need gender of that person. Hence, for Emp ID,…
Tips & Tricks 157 – Search for Online Templates Greyed Out / Disabled
When we create a new workbook through File > New, we get a box where we can search for online templates. But, at times, it may be greyed out or disabled i.e. you can not type out anything out there. To correct this, you need to enable internet setting for Excel. File > Options (You…
Tips & Tricks 156 – Get Workbook's Directory from Formula
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2) Note – For this formula to work, you workbook must be saved at least once.
Tips & Tricks 155 – Gridlines not Visible, Help!!
You may try following one by one and verify your results 1. Update your Printer Driver to latest from internet (Or change your default printer driver to PDF/XPS/One Note) 2. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times). Home tab > Go to Paint Bucket and…
Tips & Tricks 154 – Insert a Space after Each Character
You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro….
Tips & Tricks 153 – Sum only Visible Columns
You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2) If you hide the columns, the sum stays…
Tips & Tricks 152 – While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page
One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8……Last Page should say 8…
Tips & Tricks 151 – Saving in ODS (OpenDocument Spreadsheet) but Don't Need Excel Warning Message – "Do you want….."
Question – Sometimes, I may require to work in different Office Suites and to maintain compatibility, I use Open Document Format. But whenever, I save in Open Document Format (ODS – OpenDocument Spreadsheet), I get the Excel warning like below. I don't need this. What is the way out? Answer – This requires a registry…
Tips & Tricks 150 – Save Each Worksheet as Different Workbooks
You have got a workbook and you want to save each worksheet as different workbook – You can adopt following method to do so if worksheets are not many in number. 1. Right click on a worksheet tab 2. Move or Copy 3. Select new book 4. Save this new workbook 5. Do it for…