How to Resolve Duplicate Data within Excel Pivot Tables

Notice that most rows show TRUE, but in two instances it shows FALSE. This signifies that the account numbers on rows 3 and 7 are stored as text. This nuance explains why Pamela’s pivot table was reflecting duplicate data. 
 
If you wish to recreate this scenario, add an apostrophe before one or more account numbers to convert the number to text. Make sure that you leave at least one instance of the account number as a value. Right-click on your pivot table and choose Refresh to make the duplicate values appear.
 
Should you encounter this situation in the future, an easy fix is shown in Figure 5:
 
In any version of Excel: Select column A, choose Data, Text to Columns, and then Finish. 

 
Figure 5: The Text to Columns wizard offers the easiest way to convert a text-based numbers to values.

Next return to your pivot table, right-click any cell within it, and choose Refresh. The duplicate values should vanish from your pivot table, as shown in Figure 6.

 
Figure 6: Duplicate values vanish from the pivot table when all account numbers are stored as values instead of a mix of text and numbers.

 

You may like these other stories...

For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...
Earlier this year I wrote about my mixed feelings on Excel for iPad's debut. My closing sentence for that article read "The early iterations of Microsoft products tend to have rough edges that get smoothed out with...
Say goodbye to the days of manual expense reports. Automated expense management products have arrived in the market with advanced processes that are saving companies tremendous amounts of time and money. In fact, a recent...

Already a member? log in here.

Upcoming CPE Webinars

Sep 18
In this course, Amber Setter will shine the light on different types of leadership behavior- an integral part of everyone's career.
Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This 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 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.