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…
Downloads 14 – Excel Shortcuts Bible
This is a mammoth work. Contains all short cut keys published by Microsoft and also all ALT keys which are not published by Microsoft. Download it from Excel – Shortcuts Bible
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,…
Challenge 61 – Generate Multiplication Table
This time, I want to set a challenge which is not difficult and useful for your kids. The challenge is to write a formula which can be dragged right and down to generate a multiplication table. The solution to this challenge will be published after a month i.e. on 1-May-17.
Article 44 – Not able to open .xls file after upgrading to Excel 2016 / Windows 10
The problem of .xls file not getting opened after upgrading to Excel 2016 / Windows 10 is a common problem. This problem may occur after any windows / MS Office update as well. 1. Start the Excel and CTRL+O (or File > Open) > Locate the file and open the file. This should open the…
Solution – Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year
Below is a possible solution to the challenge – Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year Drag the below formula down =IF(ROWS($1:1)<25,FLOOR(DATE($A$1,ROUNDUP(ROWS($1:1)/2,0), 2*(MOD(ROWS($1:1)-1,2)+1)*7),7),"")
Downloads 13 – Template 11 – Invoice Template
The invoice template is specifically designed for Service Invoices in India but this can be used anywhere in the world with little customization. There are two templates, one which populates the amount in words in Rupees and Paise and one which populates in Dollar and Cents. If you need to change these currencies, you can…
Solution – Challenge 59 – Clean the Problem Workbook Data
Below is a possible solution to the problem Challenge 59 – Clean the Problem Workbook Data Formula to convert would be which you need to drag down would be =–SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"") Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.
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…