Reusing a Pivot Table

Question

From my previous question on creating a pivot table, I want to be able to re-use the workbook.

In sheet1 I created a pivot table of countries based on data in sheet5. In sheet5 of the workbook I have a column of countries, representing the distribution of visitors to my website. The pivot table accesses the the country name column, then counts the number of times that country appears in that column, then computes the percentage so the pivot table looks something like this:

Country  Count   Percent

USA           150         32%

Canada       32           9%

and etc.

I am using named ranges, thinking it would make using the pivot table easier.

Well, if I go back to the data in sheet5 of the workbook and change the name of a country and then refresh the pivot table, suddenly the entire pivot table goes blank!

I would like a reusable workbook so that all I need to do is paste new data into sheet5 and then refresh the pivot tables. Can anyone please tell me what I'm doing wrong???

Thanks!!!

Answer 1

Hi,

OK so you have use the offset function to ensure that the source data of the pivot expands as and when you add data by rows.  You essentially want the pivot table to include new rows (when refreshed) added to the source.

You can achieve the above without the Offset function.  Try this:

1. Select the explicit data range (first row is the header row) and press Ctrl+L to conver the range into a list.  A range converted to a List inherits the property of automatic expansion
2. Now add data by rows to the source (do not leave an entire row blank)
3. When you right click and refresh the pivot table, data added in new rows shoould become part of the pivot.

Hope this helps.

Answer 2

O.K., after clicking crl-L, I see a popup with a checkbox "My list has headers". I left that checked.

I now see a popup that says "Your selection contains one or more external data ranges. Do you want to convert the selection to a list and remove all external connections?" I clicked Yes and now I have a list toolbar.

When I add the data source to the pivot table, I selected the entire column, including the header that says "Country".

And... looks like it works!

Thank you Ashish and Jim!

Answer 3

The countries are in Column F of sheet5, which looks like this:

Country

USA

USA

Canada

Russia

Canada

:

So, cell F1 has the word "Country" and my data I want is from F2 onwards.

The formula for the named range is

ColCountry =OFFSET(ClickData!$F$1,1,0,COUNTA(ClickData!$F:$F),1)

I'll admit I don't completely understand the formula. I wanted a named range that would be flexible if I put new data into it.

I also noticed something wrong with the totals. While the total amount of data for that column is 277 entries, the pivot table counts 276.

Thanks for any advice you can give me,

Bill

Answer 4

Hi,

Check for one of the following:

1. Are you changing any column heading in sheet5?  This should be avoided; OR
2. Does you named range include blanks?  If yes, then ensure that the named range contains only the cells which have data i.e. no blanks.  After doing this, change any individual country name in the Country column of sheet5.  The refresh should work

Answer 5

From your description you are not doing anything wrong. There must be something in the details we are missing. When you say that you are using named ranges that is a good way to go assuming you are using a Dynamic named range to define the source data for the table. That should only requre a single named range. That is the first thing I would check is the source range for your pivot table. If that is wrong then when you refresh things would go badly. Please post the named range for your source so we can give it a quick once over.