# Pivot Table and Number Format

4 replies to this topic

### #1 Shyley

Shyley

Newbie

• Members
• 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

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

Thanks again!

### #4 Jonathan

Jonathan

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

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
• 5 posts

Posted 15 January 2015 - 01:15 PM

Brilliant! Thank you ever so much!

Best regards,

Shyley

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users