MSFT Excel pivot table links to external data

Question

Question1: What is the best online forum to ask MSFT Excel questions of the following variety?

Question2: How can I link an excel pivot-table to a potentially changing source table without having to re-draw the excel pivot-table layout every time the source table changes data. (Note, the columns are not changing, just the data in the rows).

Background:

I have an excel 2007 pivot table that is grabbing data from another sheet (an excel "table" ... tables are a new feature of excel 2007).

When I change the data in the source table, and then go to the pivot table and press "refresh" ... the pivot table reverts to its "blank" format and requires me to re-drag the columns rows and values.

What I want is for the pivot-table to simply re-draw itself without me having to re-create the pivot table layout.

Answer 1

Mr. Excel

Excel User Group

Answer 2

I know you've accepted an answer, but I thought I would answer your question about how to 'redraw a Pivot Table (PT) when the data changes'.

This is a common occurrence - rows are added and fields completed, but columns stay the same.

To update your PT go to the sheet with your PT on and single click in the data; now go to the top menu bar and click on the new OPTIONS link. There you will see a Change Data Source icon. Click on this and it will ask you to define your data source. It will revert to your previous choice, and in this screen you can now manually include/select the whole table again.

Go back to your PT sheet and your new data should be there.

Hope it helps.

Answer 3

You can also select the whole column so you don't have to add rows every time you update your data sheet. You will get "blank" as an option but you can filter that out of the pivot table results.

Answer 4

Maybe this used not to be the case, but right click on the PivotTable then Refresh works for me.