Pivot Table Refresh Issue

Question

Created a Pivot Table.  Made changes to the data source.  Refreshed Pivot table and changes appeared.  Removed the changes I made to the data source (ie, took out the income I added to a column) and when I refresh, the pivot table DOES NOT CHANGE.  Only when I go to the cell and clear contents  will the pivot table refresh properly.  Please help!

Answer 1

Hi

You need to make your source a Dynamic Range - not a Fixed Range as you have

at present.

How you achieve this can depend upon the version of Excel that you are

running.

For XL2003

Sit your cursor in the source data>Data>List>Create>click my data has

headers>OK

Right Click on the PT>Pt Wizard>back>ensure the source is the range you

selected for your List>OK>Finish

As more rows are aded to the List, it will grow automatically.

For XL2007 or XL2010

Sit your cursor in the the Source data>Insert tab>Table>my table has

headers>OK

With your cursor still in the source data you will see a new tab called

Design>Table name>change it from Table1 to something you want to call it.

Click on your PT>Options tab>Change data source>Enter the name of your

table>OK

For any version of Excel

Create a Defined Name >

XL2003 and earlier Insert>Name>Define

XL2007and later     Formulas Tab>Define Name

Name        myData

Refers to   =$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

(If in versions above XL2003, the row number 65536 as above can be increased

to any number up to 1048576)

 --

Regards

Roger Govier

"snydermans4" wrote in message news:*** Email address is removed for privacy ***...

Actually Shane - perhaps you can help me figure out how to solve my original problem.  I had a spreadsheet that my pivot table data is housed. I added a column to this spreadsheet.  When I go to the field list on my pivot table that column is not shown as an option.  I simply want to add that column (it is the new fiscal year data) to my pivot table.  Is there a way to change the area that the pivot table data should pull from?  I know I am not using the correct terminology... sorry.

________ Information from ESET Smart Security, version of virus signature database 5223 (20100623)________

The message was checked by ESET Smart Security.

http://www.eset.com

________ Information from ESET Smart Security, version of virus signature database 5223 (20100623)________

The message was checked by ESET Smart Security.

http://www.eset.com

---

Roger Govier Microsoft Excel MVP

Answer 2

You should always clear cells by using the Del or Delete key (or its equivalent).  You can also clear cells

1.  Choose Edit, Clear, Clear Contents or Clear All

2.  There is a toolbar button for this but it is not displayed.

3.   You can right click a selection and choose Clear Contents

4.  And there is a mouse trick not worth mentioning

5. You can press Backspace, Enter.  But that only works for one cell at a time and all the other methods work for any size selection.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Actually Shane - perhaps you can help me figure out how to solve my original problem.  I had a spreadsheet that my pivot table data is housed.  I added a column to this spreadsheet.  When I go to the field list on my pivot table that column is not shown as an option.  I simply want to add that column (it is the new fiscal year data) to my pivot table.  Is there a way to change the area that the pivot table data should pull from?  I know I am not using the correct terminology... sorry.

Answer 4

Thanks this is great help.  So, should I press delete when I change data in the cell?

Answer 5

When you say you removed the changes - how did you do that?  Because a little further along you said you needed to clear the contents?  Removed = Cleared.

If you are doing a count calculation and you removed the entries by pressing Spacebar - you aren't clearing the cells you are putting a Spacebar in them, and spacebars count!

You should never clear cells in a spreadsheet using Spacebar. 

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire