How to create a dynamic Pivot Table in Ms-Excel?

July 1, 2007 at 2:13 pm 9 comments

I have come across many times with this question, and some people even say pivot table is not useful because they do not support additional data in the main data table.

But as many other things in excel are not yet fully explored, the same is with this function. Most of the time, we do not get answer to our problems because we always look at it from the same angle. Try to look at any problem from different angles and you will get the solution. The main problem here is not the pivot table but the data that we are using with the pivot table.

So let’s start looking at this thing from different angle… How any pivot table understands what the data is? For this we have to see how we create a pivot table. While creating any pivot table we have option to select cell range. Now this is the place where we define the data source for the pivot table.

There is confusion in our understanding of one word ‘Dynamic’. We see dynamic means changeable, But always remember dynamism will not be possible with out some parts are being constants. This is very much true in our case. To make the pivot table dynamic we need to keep the data source of it constant.

Thinking impossible… let’s see how it is possible. One thing is for sure, by giving cell range as data source will not do as it will be changing whenever you add new data. So what are the other alternatives to select data source? The other alternative is ‘Named Ranges’. Now only giving a name to any range will not make it dynamic at all, but think if you use the offset function while defining the name then….

There are three essential things we need to know to enable us to make it possible. Named Ranges, Pivot Table and Offset Function. If you do not know any of this then, stop here and first get some primary information on these parts then read further.

Let’s see how to make any data table dynamic where just one name can select the entire table of whatever size it is. For creating such a named range we have to some how calculate it’s rows and some times even columns on the fly. This is possible with ‘CountA’ Function.

Logically the entire activity gets performed in the following way.

1. CountA Function gives us no. of rows and if required then no. of colums
2. Offset function selects the range based on the above no.s
3. Named Ranges define range as per above offset function selection
4. Pivote table just refer named range and analyze the data.

Let’s start doing it practically for better understandability.

Create one table in excel, where its header row is 2 and starting column is A. Go to Insert>Name>Define

Type the name of range as Data and in refer to box type =OFFSET(A2,0,0,CountA(A:A),CountA(2:2)) And then click ok

Now as you have created dynamic rage ‘Data’ time to test it, by typing ‘Data’ in Name box it is located just before your formula bar. Add one more row or column and again try selecting same range.

If this test is successful you have to just give this range name while creating pivot table instead of selecting cell range.

Your pivot table is ready to test. Add new row to main data and then just click refresh button on pivot table toolbar and see new row is there.

If you have any comments, feedback please add comment or mail me at


Entry filed under: Excel, MS EXCEL, MSEXCEL, PivotTable.

The Start Using Named Ranges

9 Comments Add your own

  • 1. R. Rebello  |  November 29, 2007 at 1:18 pm

    Excellent combination of applications

  • 2. Rastaman01  |  January 2, 2008 at 2:30 pm

    Have you been able to get this to work on Excel 2007? I used this frequently in 2003 and prior and I can’t get it to work in the new version.

    Answer: Yes, it works with 2007. This solution is universal and Version will not affect this one as this is completely based on formulas.

  • 3. joseph  |  March 25, 2008 at 4:56 pm

    hi guide with excel learning

  • 4. michael  |  July 28, 2008 at 2:45 am

    Alternatively, you could’ve just highlight the whole column(s) for the source of data and hide the resulting row/column in your pivot table.

    Each time you add a new row to the data, just refresh the pivot table and it would be updated.

  • 5. Rajendra Shewade  |  July 28, 2008 at 7:56 am

    As Michael said, I agree that the suggested traditional approach to add the entire column as source and refreshing the Pivot Table every time will do the needful.

    The approach used here is to completely automate the task.

    The traditional approach is having some disadvantages:

    1. It would be waste to include entire column containing over 65000 rows.
    2. Also every time you need to keep that thing in mind and refresh it.

    As I said the Blog will give you Tips and Tricks to automate the routine tasks, It’s up to you to take it or not.

    Please do not use the tips given if you find them difficult.

  • 6. pokemon  |  July 31, 2008 at 7:05 pm

    I tried this but Excel 2007 does not seem to be abel to take a named range in as a source for a pivot table. Has anyone else experienced the same problem?

  • 7. thinley wangdi  |  September 10, 2008 at 2:30 pm

    while, i m doing one year course (C/A) so i need to learn about the excel,that formula usining in the excel all the time, i faced problem with formula actually,
    Therefore please favour on me to teach me about formula which is used in excel.

    Thanking you
    Yours faithfully

    Thinley Wangdi
    Bhutan Telecome Ltd

  • 8. Antoine  |  December 30, 2008 at 11:42 am

    In Excel 2007 there is no need for this approach. Excel 2007 has the ‘Format as table’ functionality. New data is automatically added to the table and the table can be used as a source for a pivot (use the table name instead of a range). See Excel help for more information.

  • 9. shahnaz  |  October 8, 2009 at 5:32 am


    I am also facing the same issue , when i try to give source data the reference name ( in pivot table – by giving the offset function and range name )- there seems to be something wrong when you refresh the data, can you please help us here this should really help me a lot if works properly


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


We have shifted this blog to following new site

We have added more sections for Basics, External Links and Video tutorials

Blog Stats

  • 104,217 hits

%d bloggers like this: