Basic Pivot Table question re adding rows to source data and refre

K

KateW

I've been trying to add data (new rows) and then refresh my pivot to show the
changes but it isn't working and I suspect there's something basic here I'm
missing. I've looked at my Excel Step by Step book and all it says is add
the data and refresh which I've done. I created the pivot by clicking in the
data and then setting it up - do I need to link to the data in a different
way to get added rows? Office Online and Help don't seem to address this
basic issue.
 
J

Jim Thomlinson

Pivot tables have a source range. If you added or removed rows you will need
to reset the source range. Right click on the pivot table and select the
wizard. When the dialog comes up select the back button to get back to where
you set your source range... Modify as necessary. This is an ideal spot for a
dynamic named range. Check out this link...

http://www.cpearson.com/excel/named.htm
 
D

Debra Dalgleish

Make sure the pivot table source range includes the new rows. To do
this, you can right-click on a cell in the pivot table, and click on
PivotTable Wizard
Click the Back button, to got to Step 2.
Expand the range to include the new rows.

To include the new rows automatically, you can base the pivot table on a
dynamic range, as described here:

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

Or, in Excel 2003, create a List (Data>List), and base the pivot table
 
K

KateW

Thanks Jim - that worked (yay) but the way I modified the source data
reference was pretty kluge-y. I re-clicked on bottom right cell and then on
top left and it accepted it. Is there a better way to do the modification or
select source data in general?

I read over the reference to named ranges but I'm not really sure how it
would work in this situation. I assume I would need to create a formula to
lookup the data and summarize it but I'm not sure I need that; I just need to
pivot out totals per month of a certain type at this time but I'm open to
suggestions. I've done some basic classroom learning on named ranges. Are
they useful where I'm only using one worksheet of data?

Thanks for your help! Kate
 
K

KateW

Thanks Debra but this doesn't really address my concern of keeping adding
data to the source data simple. Lists and dynamic ranges seem like more work
than need be. :=>

Is there no simple way of just adding rows and adjusting the source reference?

Kate
 
D

Debra Dalgleish

You could insert the new rows above the last row in the existing data,
and the range will automatically expand.
Otherwise, use one of the previous suggestions. They really aren't that
much work, and the only concern mentioned in your first message was that
you wanted the new data included.
 
P

Patti Newman

Jim,
I'm having the same problem with updating my pivot table's source range. I'm
using Excel 2007. However, when I right click on my pivot table, the wizard
doesn't display in the contextual menu. Any thoughts on this?
Thanks,
Patti
 
Top