Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |0 Comments

Download the workbook related to this challenge - Sum for Merged Cells

This time challenge is to produce the sum where header is merged cells. For the layout like below -

1

You need to write a single formula which can be dragged right and down to generate the sum for below table

1

The solution to this challenge will be published after a month i.e. on 13-Jun-17.

Sat 06 May 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, May 06th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

Mon 01 May 2017

Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions|Tags: , , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS($1:1)*COLUMNS($A:A)

Sat 29 Apr 2017

Excel Quiz 51

By |Saturday, April 29th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , , |0 Comments

Excel Quiz 51 - A Quiz on Page Layout Tab in Excel

This quiz checks your knowledge on Page Layout tab in Excel.

Sat 22 Apr 2017

Tips & Tricks 159 - Prompted to "Save File" even if I haven't changed anything

By |Saturday, April 22nd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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 this question at following link -

(more…)

Sat 15 Apr 2017

Downloads 14 - Excel Shortcuts Bible

By |Saturday, April 15th, 2017|Categories: Downloads|Tags: , , , , , , , |0 Comments

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

Sat 08 Apr 2017

Tips & Tricks 158 - Overcoming column_index_number problem in VLOOKUP when a column is inserted / deleted

By |Saturday, April 08th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

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, 754761, the formula would be =VLOOKUP(754761,$A:$G,6,0) and answer would be F. Now, let's delete a column and now the answer would be lelia.vang@gmail.com as column_index_number is still 6. Now, add a column and the answer would be Vang as column_index_number is still 6.

(more…)

Sat 01 Apr 2017

Challenge 61 - Generate Multiplication Table

By |Saturday, April 01st, 2017|Categories: Challenges|Tags: , , , , , , , , , |2 Comments

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.

1

The solution to this challenge will be published after a month i.e. on 1-May-17.

Sun 26 Mar 2017

Article 44 - Not able to open .xls file after upgrading to Excel 2016 / Windows 10

By |Sunday, March 26th, 2017|Categories: Articles|Tags: , , , , , |0 Comments

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 file but thisĀ  may not solve the problem of double clicking the file and opening it.

2.A quick repair usually fixes the file association

https://support.office.com/en-NZ/Article/Repair-an-Office-application-7821d4b6-7c1d-4205-aa0e-a6b40c5bb88b

3. If this doesn't fix the association, when you right click on .xls file > Open With > You should get the list of programs which can open this file and you should select Excel.

In case, the list of program doesn't have Excel, then choose BROWSE and select Excel.exe. But, in case the folder which you get after clicking BROWSE doesn't have Excel.exe, you will need to find the location of Excel.exe to select Excel.exe. To find the path, you can refer to below link -

http://eforexcel.com/wp/tips-tricks-147-find-the-location-of-excel-exe/

1

4. If there is a file block issue

4.1 Right Click on the file > Properties > In the General tab, you will get Unblock option down below > Check the unblock box

4.2 If you don't get unblock option, ALT+T+O (or File > Options) > Trust Center > Trust Center Settings > File Block Settings > Uncheck the box in front of .xls file

1

Sat 25 Mar 2017

Solution - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, March 25th, 2017|Categories: Solutions|Tags: , , , , , , , , |0 Comments

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),"")