Fri 06 Jul 2018

Tips & Tricks 169 - Get the Source of a Pivot Table

By |Friday, July 06th, 2018|Categories: Tips and Tricks, VBA|Tags: , , , , , , |0 Comments

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy & Paste the below code in this module

Now, you can call this function like


Where E5 is any cell in your Pivot Table

' *** Macro by *****
Function GetPivotRange(Cell As Range) As String
    GetPivotRange = Application.ConvertFormula(Cell.PivotTable.SourceData, xlR1C1, xlA1)
End Function


Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

By |Sunday, July 09th, 2017|Categories: Articles|Tags: , , , , , , , , |1 Comment

The file related to this article can be downloaded from Dynamic Pivot Tables

We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.


Sat 16 Apr 2016

Article 38 - 10 Features I would like to see in Excel

By |Saturday, April 16th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , |0 Comments

Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would like Excel to have.

1. Making Volatile Functions Non-volatile on the basis of a Parameter

We all like TODAY(), NOW(), RAND() and RANDBETWEEN() (there are other volatile functions also). They are very useful but suffer a fatal flaw, if something gets recalculated, they always get recalculated also. Hence, if I am using TODAY() to derive today's day and if I open the sheet tomorrow, TODAY() will change tomorrow's date. I wish that all volatile functions should carry a parameter so that they should not recalculated when the sheet is recalculated. For example, we should have something like TODAY(N), NOW(N),RAND(N) and RANDBETWEEN(N) where N

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.


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.


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.