Sat 29 Nov 2014

Tips & Tricks 67 - Show the Complete Data in Chart even though Data is Filtered

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

By default, charts in excel show filtered data only.

To show complete data i.e. to ignore filtering -

1. Right click on Chart and click on Select Data.

2. Click on Hidden and Empty Cells at the bottom

1

3. Select Show data in hidden rows and columns. Now, you will have the result which you want.
1

Sat 29 Nov 2014

Excel Quiz 3

By |Saturday, November 29th, 2014|Categories: Quizzes|Tags: , , |2 Comments

The third quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 29 Nov 2014

Article 8 - Calculating Percentage Change between New and Old Value

By |Saturday, November 29th, 2014|Categories: Articles|Tags: , , , , , |0 Comments

Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post - Percentages

You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in Finance industry where you have to report percentage growth for many parameters like revenue, cost etc. Economics has demand growth, supply growth and every other industry has their own growth metric. Telecom will have subscriber growth, ARPU growth and so on.

Note - The article is not about growth between percentage values i.e. it is not about growth between 20% and 30% but between absolute values.

(more…)

Sat 29 Nov 2014

Challenge 4 - SUM of Multiplication of Preceding Digits by Succeeding Digits

By |Saturday, November 29th, 2014|Categories: Challenges|Tags: , , , , , |1 Comment

Let's say you have a number 64934 in a cell A1. Now, the challenge before you is to work out the following SUM through a formula which should be flexible enough to capture any number of digits (Don't limit this to 15 digits maximum as allowed by Excel precision but to any number of digits as numbers can be entered in text also bypassing 15 digits limit of Excel)

= First Digit x Second Digit + Second Digit x Third Digit +...........+Last Digit x 0 (Last digit wouldn't be multiplied by something else  as there is no succeeding digit)

The samples cases are given below for better clarity

1

Note - You may choose to post the response in comments section.

Sat 22 Nov 2014

Tips & Tricks 66 - Show the Complete Area of a Worksheet as only Limited Area is being shown

By |Saturday, November 22nd, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

If you can see only limited area of a worksheet

1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing.
- If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need to give the correct password to make the sheet unprotected.
- If Protect Sheet Button is appearing sheet is not protected. Then nothing needs to be done.

1

2. Press CTRL+A, Right Click on a Column Name A, B, C, D.....and Click Unhide. All columns will be unhidden.

3.  Press CTRL+A, Right Click on a Row Number 1, 2, 3, 4.....and Click Unhide. All rows will be unhidden.

 

Sat 15 Nov 2014

Tips & Tricks 65 - Show a Limited Area of Worksheet

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

You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them.

Use following steps -

1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get selected right of column H. Now right click and hit Hide. Now all columns right of G will be hidden.

2. Similarly click row 51 so that entire row 51 is selected. Now CTRL+SHIFT+Down Arrow key so that all rows starting 50 are selected. Now right click and hit Hide. Now all rows starting 51 will be hidden.

Using above technique, I have hidden entire area except A1:G20 in below screen shot.

1

3. If you want to make sure that nobody ever is able to unhide these hidden rows / columns, protect the sheet with password after making the visible area not locked. To make visible area unlocked, select the visible area > Right Click > Format Cells > Protection, here uncheck the Locked Box.

1

Sat 15 Nov 2014

Excel Quiz 2

By |Saturday, November 15th, 2014|Categories: Quizzes|Tags: , , , |0 Comments

The second quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.

Sat 15 Nov 2014

Challenge 3 - Generate a Chessboard in Excel

By |Saturday, November 15th, 2014|Categories: Challenges|Tags: , , , |1 Comment

Below chessboard has been generated in Excel. The challenge before you is to give me steps which will generate this chessboard of 8x8. The approach should be flexible to generate any NxN chessboard (Colouring should not be done manually).

Note - You may choose to post the response in comments section.

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 08 Nov 2014

Article 7 - Generate a Sequence of Numbers

By |Saturday, November 08th, 2014|Categories: Articles|Tags: , , , , , |1 Comment

Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.

Once again, I would like to tackle this topic in 4 ways.

1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA

(more…)