Share this content
0
10
1315

Pivot tables

Hello

I have a pivot table in Excel 2010, on a separate sheet from the source data. This separate sheet is in the same workbook.

Now I have added rows in the pivot table, I cannot see in Excel 2010 how to tell the pivot table to pick up the extra rows (I recall in earlier versions that there was an option to change the data source).

Can anyone help?

Many thanks - SA

Replies

Please login or register to join the discussion.

By shurst
12th Mar 2012 10:07

PivotTable data source

Try PivotTable Tools, Option tab. Data group, Change Data source

However, if you turn your data source into an Excel 2010 table, and base your PivotTable on that, it should adjust automatically for added rows (always as well to check though!)

Thanks (0)
avatar
12th Mar 2012 10:23

Yes!!!!!!!!

Thank you very very much!!!!

SA

Thanks (0)
By shurst
12th Mar 2012 10:37

Good

I take it that it worked then!

Thanks for confirming that it was what you were after.

Thanks (0)
avatar
16th Mar 2012 15:18

Another way...

...is to give your data table (not to be confused with the Excel idea of data tables as described above) a name and set the following formula (instead of A1:Q300, for example) as the range:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

This counts the number of columns users and the number of rows used to set the named range.

If your data table starts in A3, because you may have a title in Row 1, then tweak:

=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2,COUNTA(Data!$3:$3)).

Now set your pivot table to look for the name you gave this range and you will never need to do anything other than right-click and choose refresh in future.

Thanks (0)
By shurst
16th Mar 2012 15:47

But..

I may have misunderstood, but If you were going to do that, couldn't you just refer directly to the range name for the PivotTable range, e.g. =data ? Also, if you added a row at the bottom of a normal Excel block of cells (unlike a 'Table') I think that would fail to extend the range the name referred to. You would also need to be careful that the first column of the range didn't contain any blank cells (same is true of the first row, but as this should be the headings that is less likely).

Just for clarification, my reference to Tables was to Excel 2007 and 2010 tables (an enhanced version of Excel 2003 lists) as distinct from the What If? Data tables.

Thanks (0)
avatar
16th Mar 2012 16:42

Thank you for the extra input - will look at these posts  over the weekend! Have a good one!

SA

 

Thanks (0)
avatar
16th Mar 2012 17:48

Just to clarify...

shurst wrote:

I may have misunderstood, but If you were going to do that, couldn't you just refer directly to the range name for the PivotTable range, e.g. =data ? Also, if you added a row at the bottom of a normal Excel block of cells (unlike a 'Table') I think that would fail to extend the range the name referred to. You would also need to be careful that the first column of the range didn't contain any blank cells (same is true of the first row, but as this should be the headings that is less likely).

Just for clarification, my reference to Tables was to Excel 2007 and 2010 tables (an enhanced version of Excel 2003 lists) as distinct from the What If? Data tables.

 

Yes, exactly, you put that formula in the name manager box, and you put the name of of the range in the data source for the pivot table. The COUNTA on column A can be changed to any column where you have contiguous data. I personally don't leave anything blank when setting up data tables for pivots because I don't like the way they're handled - (blank) at the bottom of a column or end of a row just looks unsatisfactory!

I was talking about a normal spreadsheet 'table', not one that is formatted especially by Excel to be a table.

Thanks (0)
By shurst
16th Mar 2012 18:06

Why not use the name directly?

What advantage is there in using the formula allocated to a range name over just pointing the PivotTable directly at the original range name ('data' in your example)?

Thanks (0)
By shurst
16th Mar 2012 18:17

Sorry - 'data' is the sheet name

Sorry - understand now, the table is on a sheet called data and of course must be the only thing on that sheet. Is there any reason why you would use that method rather than an Excel table if you were using Excel 2007 or 2010?

Thanks (0)
By shurst
16th Mar 2012 18:39

More apologies

I should really have looked at your formulae more carefully - 'data' could only be a sheet name. However, I think the point you made about making sure the column you use has no blanks is an important one.

Thanks (0)