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?
Pivot tables do not refresh contents when data is deleted an
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.
If your headings are always the same you should be using a dynamic named range. Heres the drill.
DYNAMIC NAMED RANGE
 Activate Insert / Name / Define,
 Enter “EndRow” (Without the quotes) as name in the Names in the Workbook box,
 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.
 Activate Add,
 Enter “Database” (Without the quotes) as name in the Names in Workbook box,
(Just type over what is there)
 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.
 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?
and the database is as shown (attached gif)
Regarding your first problem see the attached workbook. It seems to work for me.
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.
So it does, but if you check on the Counties drop down it has updated.:puzzled: I guess this is just another of excel's puzzles. Sorry for the delay responding. Been out of town.:smash:
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?
Yes, sure. I've refreshed it. Let's see is someone else has a fix....
Just a thought. Perhaps use "Clear Contents" rather than "Delete" would alleviate the problem.
Attached a third version of the XL file where I have changed some destinations, and still appears on the filter...
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.
Follow the thread below for more help.
Re: Pivot tables do not refresh contents when data is deleted anCode
Sub DeleteMissingItems2002All() 'prevents unused items in non-OLAP PivotTables 'in Excel 2002 and later versions 'If unused items already exist, 'run this macro then refresh the table Dim pt As PivotTable Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone pt.PivotCache.Refresh Next pt Next ws End Sub Programmatically Clear Old Items -- Excel 97/Excel 2000 Sub DeleteOldItemsWB() 'gets rid of unused items in PivotTable ' based on MSKB (202232) Dim ws As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name <> "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False pt.RefreshTable Next pt Next ws End Sub
Don’t have an account yet? Register yourself now and be a part of our community!