How to Resolve Duplicate Data within Excel Pivot Tables | AccountingWEB

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.
 
Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.
Premium content is currently locked

Editor's Choice

WHAT KIND OF FIRM ARE YOU?
As part of our continued effort to provide valuable resources and insight to our subscribers, we're conducting this brief survey to learn more about your personal experiences in the accounting profession. We will be giving away five $50 Amazon gift cards, and a $250 Amazon gift card to one lucky participant.
This is strictly for internal use and data will not be sold
or shared with any third parties.