Pivot tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hannes - 18 Dec 2005 12:27 GMT

Hi,

I have a pivot table and and a pivot graph that I have to update regularly.
Can I add lines to the dataset (if I dont change to columns) and make the
table read the new data?

Reply to this Message

bpeltzer - 18 Dec 2005 16:15 GMT

Two ways you could have lines added to an existing Pivot. The first is to
bring up the Pivot Table Wizard (right-click on the pivot table and choose
Pivot Table Wizard), and click on Back until you see the data range defined
for the table; just change the ending row number to accomodate the new data.
The alternate, if your column headers are in row 1, is to define your data
range to be the entire columns. If, for example, you're using $A$1:$E$10,
change it to just $A:$E. Then you won't have to adjust the range as data is
added.
In either case, Pivot Tables and Charts have to be refreshed to pick up new
data or changes to the data. Right-click on the table and select Refresh
Data.
--Bruce

> Hi,
>
> I have a pivot table and and a pivot graph that I have to update regularly.
> Can I add lines to the dataset (if I dont change to columns) and make the
> table read the new data?

Reply to this Message

Debra Dalgleish - 18 Dec 2005 16:19 GMT

You can use a dynamic range for the source data, as described here:

http://www.contextures.com/xlPivot01.html

When you refresh the pivot table, the new records will be included.

> Hi,
>
> I have a pivot table and and a pivot graph that I have to update regularly.
> Can I add lines to the dataset (if I dont change to columns) and make the
> table read the new data?

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

swjtx - 18 Dec 2005 16:56 GMT

Hi,

You could spec the columns as another user describes but I found tha
that will tend to create a very large file since I guess the pivot i
looking at all 65000 rows. Another method I have used is just to loo
at a larger range of rows. For example, if you have 2000 rows and yo
are going to be adding 3 or 4 a week, it's going to take a long time t
get to 4000 so if you choose rows 2 to 4000, you will be safe until you
data exceeds 4000 rows. I had a worksheet with 36 pivot tables and 1
charts. Limiting the data made the file about 4 meg, choosing th
entire column made the file about 65 meg.

$A$2:$E$4000 (or whatever number you feel comfortable with)

You still have to refresh the data to see the changes

Reply to this Message