Jump to content


Photo

Pivot Table and Number Format


  • Please log in to reply
4 replies to this topic

#1 Shyley

Shyley

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 14 January 2015 - 06:35 PM

Hello,

Thank you for your very informative and professional video series. Although I have been an Excel user for may years, I am learning a little more with every video.

 

I have a spreadsheet that lists fractional hours in decimal form. When I attempt to use the data in a pivot table, the pivot table converts the decimal to a whole number. All cells with less than 1 hour are rounded up and displayed in the pivot table as 1. Is the pivot table function unable to understand how to transfer and sum the decimal?

I suspect I may have to add an equation to the original data column to convert the decimal prior to converting the data range to a table for use with the pivot table function. 

 

Best regards,

Shyley

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 14 January 2015 - 07:06 PM

Hi Shyley,

 

It's most likely that the pivot table is adding your decimals together correctly 'behind the scenes', but the formatting of the column is rounding the number up.  You can fix this by changing the number format of the pivot table column.

 

To do this, simply right click on the column in question and click Number Format... from the shortcut menu.  You can see this being done in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

I hope this helps and you're able to get the result you need, but please feel free to reply if you continue to have problems.  It would also be very helpful if you could attach an example workbook in this case.  For instructions on how to do this, click 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.


#3 Shyley

Shyley

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 14 January 2015 - 08:36 PM

Hello,

I wish I could report my issue as resolved. I tried the format number option, but I'm still getting rounded numbers.

Attached is my spreadsheet for reference. Thank you for any additional help you can offer.

 

Thanks again!

 

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 14 January 2015 - 10:42 PM

Hi Shyley,

 

I can see the problem now - your Pivot Table is calculating the Count of your numbers instead of the Sum.

 

You can switch between calculation types by using the Value Field Settings dialog. You can see a video walkthrough of how this is done in: Expert Skills Lesson 5-16 Display multiple summations within a single pivot table.

 

I have also attached a copy of your workbook with the calculation switched to Sum.

 

Attached File  CVS Service Time.xlsx   90.89KB   181 downloads

CSV Service Time.png

 

I hope this is what you were looking for, but as usual please feel free to reply if I can offer any more assistance.


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.


#5 Shyley

Shyley

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 15 January 2015 - 01:15 PM

Brilliant! Thank you ever so much!

 

I followed your guidance and made the adjustment to my spreadsheet. My data is now exactly as it needs to be.

 

Best regards,

Shyley






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users