Jump to content


Photo

Certain Year Appear Twice in Pivot Table

Alignment

  • Please log in to reply
1 reply to this topic

#1 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 28 December 2015 - 01:37 AM

Hi,

 

I exported data to Excel from a data source. Now in a pivot table, I get the year 2014 twice. I don't want that. It seems that one of the 2014 is indented to the left while the others are to the right. I used format cells, I also tried to move them to the same side on the main or non-pivot table workbook. I remember from months ago a friend showing me a formula to get Excel to view/display the numbers as number in the way they need to be seen and to have just one 2014 in my pivot table. I forgot the formula. How can I fix my problem? Do you know the formula I have in mind?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 28 December 2015 - 11:11 AM

Hi MikeyMike,

 

I am guessing that you are trying to summarize some data by year. To do this, you should have the Year in the ROWS section and the values in the VALUES section.

 

pivotsettings.png

 

You can see an explanation of this in the video tutorial: Expert Skills Lesson 5-3 Understand pivot table rows and columns.

 

If there are any other fields in the ROWS section, the pivot table will automatically try to create subtotals for these, so the most likely solution is to remove anything else from the pivot table apart from the rows and values.

 

If you have already done this, it is likely that one of the entries in your Year column has an extra space before or after the year. Even though you can't see it, this makes the value slightly different so Excel will treat it as a separate total.

 

I hope you're now able to solve the problem, but please feel free to reply if you're still having trouble. If you'd like to attach a copy of your workbook, I should be able to figure out the problem (instructions here).


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users