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...

A great way to increase business for your accounting firm is to remain at the front of your clients' minds all throughout the year. One of the best ways to do that is through an email newsletter. Perhaps you send out a...
Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...

Already a member? log in here.

Upcoming CPE Webinars

Sep 30This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 9In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards.
Oct 15This webinar presents the requirements of AU-C 600, Audits of Group Financial Statements (Including the Work of Component Auditors).
Oct 21Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience’s communication style.