How to Resolve Duplicate Data within Excel Pivot Tables

An attendee from my recent pivot table webinar posed a question that I hadn’t encountered before.
 
Pamela had an issue where some, but not all, items within her pivot table were being duplicated, with two different totals. If you’re new to pivot tables, you can catch up by watching a free recording of the webinar.
 
In this article I’ll explain how I helped Pamela track down and resolve a nuance within her data. Although Pamela’s data set was much larger, I only need the two columns of data shown in Figure 1 to illustrate what she was experiencing. There’s a hidden aspect to this data that I’ll reveal in a moment, but let’s first create a pivot table from this data:
 
Figure 1: I’ll use this data set to explain why duplicate data may appear within a pivot table.
 
  • Excel 2007 and later: As shown in Figure 2, click on cell A1, choose Insert, Table, and then click OK. Click Summarize with Pivot Table from the Design tab, and then click OK.
  • Excel 2003 and earlier: Choose Data, List, Create, and then click OK. Next, choose Data, Pivot Table Wizard, and then click Finish.
Figure 2: Carry out the steps shown to create a pivot table.
 

You may like these other stories...

How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...
Event Date: October 30, 2014, 2 pm ETMany Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word...
Event Date: September 9, 2014, 2:00 pm ETIn this session we'll discuss the types of technologies and their uses in a small accounting firm office. Included will be:The networked office: connecting everything together for...

Already a member? log in here.

Upcoming CPE Webinars

Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.