Expand pivot table

[Fred Smith]

I've added data to my pivot table, and want to change the data range. How do
I do this in Excel 2007?

Thanks,
Fred

 

[Dave Peterson]

Show the pivottable wizard for that pivottable.

Hit the back button as many times as you need to get to the step that asks you
for the range.

If the range grows and contracts rows, you may want to create a dynamic range
name so all you have to do is refresh the pivottable.

Debra Dalgleish shares instructions for that kind of dynamic name:
http://contextures.com/xlNames01.html#Dynamic

 

[Fred Smith]

Thanks, Dave, but I still can't get it to work. The instructions you listed
are the ones I remember from XL2003, but I can't see how to do the same
thing in 2007.

If I highlight a cell in the PT, then the Pivot Table icon is grayed out. If
I right-click on the PT, I don't see any menu item that lets me edit the
data range.

If I go to the data table, I see only a Create Pivot Table dialogue box. I
can't see the Show PT Wizard anywhere.

Thanks,
Fred

 

[Roger Govier]

Hi Fred

You should be using a Table as your source data, as this will
automatically grow as you add more data.

Place cursor in your source data>Insert tab>Table>Click my Table has headers

Now, with cursor within Table, a Design tab will be visible.
Design tab>you have the option to change the Table name from Table1 to
anything you want (if required)
Also, on Design tab>Summarize with Pivot Table

If all you want to do is change source, then
Once you have a PT created, place cursor with PT and you have an Options
Tab.
Select Options tab>Data>Change Source

--
Regards
Roger Govier

Thanks, Dave, but I still can't get it to work. The instructions you
listed are the ones I remember from XL2003, but I can't see how to do
the same thing in 2007.

If I highlight a cell in the PT, then the Pivot Table icon is grayed
out. If I right-click on the PT, I don't see any menu item that lets me
edit the data range.

If I go to the data table, I see only a Create Pivot Table dialogue box.
I can't see the Show PT Wizard anywhere.

Thanks,
Fred

 

[Fred Smith]

Thanks, Roger.

It's hard to lose 2003 habits, and I keep forgetting to look for the extra
menu item. Your solution worked great.

Fred.

Hi Fred

You should be using a Table as your source data, as this will
automatically grow as you add more data.

Place cursor in your source data>Insert tab>Table>Click my Table has
headers

Now, with cursor within Table, a Design tab will be visible.
Design tab>you have the option to change the Table name from Table1 to
anything you want (if required)
Also, on Design tab>Summarize with Pivot Table

If all you want to do is change source, then
Once you have a PT created, place cursor with PT and you have an Options
Tab.
Select Options tab>Data>Change Source

 

[Roger Govier]

Hi Fred

You're welcome.
I still spend more than half of my time in 2003, as most of my clients
have still not made the change to 2007.
I too, still often forget the way things work in 2007.

 

[Fred Smith]

By the way, whatever happened to your offer to look at the file for
frankjh19701 regarding highlighting the lowest three values in a range? Were
you able to get it working for him?

Fred.

Hi Fred

You're welcome.
I still spend more than half of my time in 2003, as most of my clients
have still not made the change to 2007.
I too, still often forget the way things work in 2007.