Tue 13 Jun 2017

Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET($A$1,ROWS($1:1),MATCH(B$13,$1:$1,0)-1,,IFERROR(MATCH(C$13,$1:$1,0),COUNTA($2:$2)+1)-MATCH(B$13,$1:$1,0)))

Sat 13 May 2017

Challenge 62 - Produce the Sum for Merged Cells Headers

By |Saturday, May 13th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

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.

Sun 10 Jan 2016

Tips & Tricks 126 - Press CTRL+A Three Times to Select Entire Worksheet not Two Times

By |Sunday, January 10th, 2016|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

The safest bet to select entire worksheet is through pressing CTRL+A three times not two times or one time, if you are using shortcut (The safest bet is to press the triangle between 1 and A as marked in Red in the given picture. The tip is for CTRL+A shortcut.)

This peculiarity of CTRL+A shortcut comes into picture when your worksheet contains tables also. Suppose, you have a worksheet like below which has at least one table.

1

1. Select a cell outside any data range, press CTRL+A and it will select entire sheet.

2. Select a cell inside A1:D2, press CTRL+A once and it will select A1:D2 range only. Pressing CTRL+A again will select entire sheet.

3. Select a cell inside header row in the table , press CTRL+A once and it will select entire table. Pressing CTRL+A again will select entire sheet.

4. Select a cell inside the table data range i.e. A6:D7, press CTRL+A once and it will select table data range i.e. A6:D7 only. Press CTRL+A again, now it will select entire table i.e. A5:D7 only not entire worksheet.. Now, press CTRL+A again i.e. 3rd time and now, it will select entireĀ  worksheet.

Sat 31 Jan 2015

Tips & Tricks 76 - Whenever I Refresh a Pivot, its Column Width Changes

By |Saturday, January 31st, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You can stop Pivot from doing this behaviour. Take following steps -

1. Right Click on Pivot Table > PivotTable Options
2. Uncheck Autofit column widths on update.

1

Sat 08 Nov 2014

Tips & Tricks 64 - Show a 0 in place of Blanks in Pivot Table

By |Saturday, November 08th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

When you create a pivot table and if a sourceĀ cell has no value (i.e. cell is blank), in pivot table, it will show a blank. All other results in pivot table are carrying numeric values and you see blanks. It is odd though it doesn't impact the results.

1

But you still prefer to show this as 0.

1. Click anywhere in the pivot table and choose PivotTable Options.

2. Make "For empty cells show" box 0.

1

 

Sat 30 Aug 2014

Tips & Tricks 46 - Microsoft QUERY Error - the data source contains no visible tables

By |Saturday, August 30th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Sometimes, people prefer to use Microsoft Query to extract data from Excel sheets and you encounter this error -

1

The Fix - Query will recognize only those tables whose range has been named. It will not recognize any range which has been made into Tables. Hence, only named ranges are considered as tables for Query purpose.

1. Hence, you just need to select the range and type the range name in Name Box. Now this range name will appear in Query when you select the workbook.

1

OR

You can select the cells who you want to be named and right click. Second item from bottom is "Name a Range"

1

2. If you have already made tables, you can convert the table to ranges. To do this, click a cell in the table and Design menu will get enabled. Press Convert to Range. After this, you will have to execute step 1 to make it Named Range.

1

 

Sat 07 Jun 2014

Tips & Tricks 10 - Convert from Word Tables to Excel

By |Saturday, June 07th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

The best way to do this would be through utilities. The best one which I found out and is free is following -

http://watermark-images.com/convert-word-to-excel.aspx

Another way is to copy Word tables one by one (or in one go if layout permits) into Excel -

http://office.microsoft.com/en-in/excel-help/copy-a-word-table-into-excel-HP010254130.aspx