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…
Challenge 60 – Generate a list of 2nd and 4th Saturdays for a Year
In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar. To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs…
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.
Downloads 12 – Template 10 – A Flexible Amortization Schedule Generator
This flexible Amortization Schedule Generator can be downloaded from Amortization Schedule Template Unprotected template – Amortization-Schedule-Template_Unprotected 1. You can specify Interest Rate not only annually but also weekly, monthly, quarterly and half yearly. Default is Yearly if nothing is specified. 2. You can specify Loan Period in weeks, months, quarters, half years and years. Default…
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…