Pivot tables do not refresh contents when data is deleted an

  • When we create a database in an Excel Spreadsheet and then some pivot tables, we use to copy the spreadsheet with another name, in order to clean the data and update it with another date (for instance, one excel spreadsheet for one BU or country, another one for other bU or country), to avoid creating the pivot tables again and again. However, when we clean up all the data and enter the new one, on the Pivot Tables fields, you still see the "old data that was deleted"... Do you know if there is a way to delete that data without recretaing the pivot table?

  • The is a Refresh button that updates the data. Look on the Pivot Table toolbar. Right click the toolbar area and select Pivot Table. The button looks like an exclamation mark.

    Try That.


    There are three types of people in this world.
    Those who can count and those who can't.

  • If your headings are always the same you should be using a dynamic named range. Heres the drill.


    [1] Activate Insert / Name / Define,
    [2] Enter “EndRow” (Without the quotes) as name in the Names in the Workbook box,
    [3] Enter as formula in the Refers to box:


    Note. Replace x with the sheetname where the database is. ]

    Example: If the name of the sheet is INPUT

    If you don’t have numbers (amounts) in column $A:$A then change that reference to a column that does.


    [4] Activate Add,
    [5] Enter “Database” (Without the quotes) as name in the Names in Workbook box,
    (Just type over what is there)
    [6] Enter as formula in the Refers to box:
    (Again type over or alter what is there)

    (5 indicates the number of columns in the table. Alter to suit.)

    [ Note. Replace 'INPUT' with the sheetname where your database is. ]

    If your database does not start in row one change the formula.
    For example if your database starts in row 10 then subtract 9 from the formula and change the first part to reflect where the top left corner of the database is.

    =OFFSET(INPUT!$A$10,0,0,EndRow-9,5) (Again change the sheet name.)

    Remember the database starts in the row where the headings are.

    [7] Activate OK.

    Now, you're ready to use the dynamic range name, Database in your workbook. It will expand or shrink automatically along with deletions from or the additions to the data area. When used with a Pivot Table it will update automatically upon Refresh Data.

    NOTE: You must create the Pivot table AFTER you create the Dynamic Range.

  • Thank you Neale, for your answer. However, the issue is that, despite you refresh the pivot table, the dropdown fields still show records (rows) that were deleted...
    I'll test George's suggestion and let you know if it works (I hope so). thank you again:cheers:

  • Hello George. I've learned how to create dynamic databases. However, the pivot table does not refresh, as we expected. I'm attaching a printscreen of what's happening. As you can see, I had a database with information for a lot of countries. Then, I deleted almost all rows to let only two countries on the report. However, the Pivot table "filter" still shows all the other countries that have been deleted... Weird, isn't it? Another curiousity: I have a pivot table where the columns should be ordered alphabetically.. However, the first colum is VIE and the second is ARG... and I cannot make the PTable to fix this issue.. Excel problem?

  • George. I've deleted some rows from your excel spreadsheet.
    As you can see, when you select the combo box for Site, you still see Data1 AND Data2, despite the fact we don't have data2 as a value anymore... See attached file.

  • Thhank you George. Probably I didn't explain well the problem... that issue is what I wanted to solve. Sometimes we have hundreds!!! of values that we don't want to see as they've been deleted... Should it be a third party product that "cleans" this information? :puzzled:

  • The only sure way that I know of is to re-create the pivot table but that negates the value of the dynamic range. Looks like more investigation is warranted. Maybe someone else has an idea for a fix. By the way you did refresh the table didn't you?

  • Just a thought. Perhaps use "Clear Contents" rather than "Delete" would alleviate the problem.

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    (retired Excel 2003 user, 3.28.2008)

  • Hi,

    It is a "feature" of pivot tables to retain the classings. The usual response is that you have to re-create the pivot table. There is, however, a quicker work-around:

    1) in the source data, rename the field that has changed. hit refresh
    2) change it back to the original name, hit refresh.
    3) pull the field back into te pivot table.

    ...not elegant, but qicker than building the whole thing again.


  • Re: Pivot tables do not refresh contents when data is deleted an


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!