My pivot table is showing the same data item twice


Problem

Your pivot table is showing the same data item two times, effectively treating each instance of the data item as a separate entity. Figure A.6 demonstrates an example of this error.

Figure A.6. Notice that 1/3/2003 is shown in the pivot table twice. There is something wrong here. If these dates are the same, they should be grouped together to give you one calculation, not two.


Solution

You are getting this behavior because, although the two data items look the same to you, there is something different between them. Excel is picking up this difference and deciding that they are two different data items. In order to fix this issue, you will have to ensure that the problem data items are exactly the same. Follow these steps to correct this problem:

1.

Go to your data source and sort your table on the problem field. In the example shown in Figure A.6, the problem field is the Date field.

2.

Find all instances of the problem data item (1/3/2003, in this case) and make sure they are all the same. You can do this by copying the first instance of the data item and pasting over the other instances.

3.

Highlight the column that contains the problem data item. Go up to the application menu and select Data, Text to Columns. This will activate the Text to Columns Wizard. All you have to do in this wizard is click Next, click Next again, and then click Finish.

4.

Go back to your pivot table, right-click, and select Refresh Data.



    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net