Accidental Pressing of CTRL+6 toggles hiding / unhiding of all objects which includes charts also. Pressing CTRL+6 will unhide all charts (objects) again. Sometimes, Charts (objects) can be hidden through Selection Pane (Home tab>Find and Select>Selection Pane > This has Show All / Hide All buttons. In this case, you can show / hide appropriately….
Category: Tips and Tricks
Tips & Tricks 163 – Convert Alphabets to Numbers
If you want to convert a, b, c….z to 1, 2,3….26, the you can use following type of formula – =CODE(UPPER(A1))-64
Tips & Tricks 162 – Convert a Month Name to Month Number
Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number =MONTH(1&A1) =–TEXT(1&A1,"m") In case, cell A1 contains the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).
Tips & Tricks 161 – When is Thanksgiving Day in a Year
Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November. Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.
Tips & Tricks 160 – When is Labor Day in a Given Year
While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September. Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day =CEILING(DATE(A1,9,1)-2,7)+2 Note – This utilizes the knowledge gained in Article 34…
Tips & Tricks 159 – Prompted to "Save File" even if I haven't changed anything
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…
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…