I have created a Pivot table and saved it to a new worksheet. If I go back to my original sheet and add a row of data, do I manually have to amend the range or is there a button to click to have this done so that the pivot table is updated to include this new row?

smiler44

Howzit

What I tend to do is highlight the entire columns (as the range - having my column headings on the first row) when creating pivot tables to ensure additional data added later is included when I refresh the Pivot. I also in the design hide any blank rows, so is not cluttered in the results.

This probably a little more resource intensive, but at least I know all the data will be returned.

Thank you Kiwiman, great piece of advice.

Smiler44

Hi Smiler44

If you highlight a cell inside the pivot table & right click, you will see an option to go to 'Pivot Table Wizard', select it & then go to 'Back'.

From here you can adjust the range that the Pivot reads.

This is also handy for times when you add a new column to the source data.

Note though that you can't add a column range that is beyond what exists on the source table or you will get an error. hth

noboffinme,
am I right in thinking that by going to the wizard and clicking back I will have to modify the range so the range stops at the last row with data in it? If I do this and then add another row, the refreash button on the pivot table chart will not include this new row when it refreashes.
smiler44

Kiwiman, this may be a daft question but how do you hide the blank rows? Hightlighting the column headers works a treat but in the graph I get an entry "Blank" and I can not work out how to get rid of this.
thanjs
smiler44


Howzit

What I tend to do is highlight the entire columns (as the range - having my column headings on the first row) when creating pivot tables to ensure additional data added later is included when I refresh the Pivot. I also in the design hide any blank rows, so is not cluttered in the results.

This probably a little more resource intensive, but at least I know all the data will be returned.

Kiwiman, this may be a daft question but how do you hide the blank rows? Hightlighting the column headers works a treat but in the graph I get an entry "Blank" and I can not work out how to get rid of this.
thanjs
smiler44
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''

not to worry I have found out how to do it.
go to the new sheet that was added automaticlly, click the down arrow and uncheck Blank.
smiler44

Hi Smiler44

You're correct, so the pivot will only read the range you apply/adjust.

If you're concerned about blanks & you have fields in the 'Column' &/or 'Data', - as you said uncheck the blanks.

If you have field that is in the 'Page' area, you can double click on the field button & 'hide' that data.