Rounding in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2101956%22%20slang%3D%22en-US%22%3ERounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2101956%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20six%20tables%20(in%20one%20sheet)%20from%20data%20in%20six%20different%20sheets.%3C%2FP%3E%3CP%3EFrom%20those%20six%20tables%20I%20have%20merged%20and%20created%20two%20pivot%20tables%20(income%20and%20expenses)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Please%20don't%20ask%20how%20I%20did%20it%20as%20I%20have%20no%20idea%20now)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20one%2C%20only%20one%2C%20of%20the%20tables%20is%20rounding%20up%20the%20numbers.%26nbsp%3B%3CBR%20%2F%3EMy%20columns%20are%20all%20set%20to%20Number%20and%20two%20decimal%20places%20on%20both%20original%20data%20and%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20stop%20this%20rounding%20please%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2101956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2101973%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2101973%22%20slang%3D%22en-US%22%3EPlease%20provide%20some%20example...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2101986%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2101986%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-01-27%2013.46.33.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249814iD56DD16430D1FF9C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-01-27%2013.46.33.png%22%20alt%3D%22Screenshot%202021-01-27%2013.46.33.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83This%20is%20the%20original%20data%20which%20I%20have%20discovered%20is%20the%20only%20one%20that%20is%20General%20cells%20not%20Number%20cells%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Table.jpg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249815i1D75069EA847EC33%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Table.jpg%22%20alt%3D%22Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BThis%20is%26nbsp%3B%E2%80%83how%20it%20looks%20on%20the%20table%20in%20the%20lower%20part%20-%20203.69%20has%20been%20rounded%20to%20204%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20the%20original%20data%20to%20Number%20and%20refreshed%20the%20table%20but%20it%20did%20not%20solve%20the%20problem%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F210670%22%20target%3D%22_blank%22%3E%40Boriana%20Petrova%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2102041%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F944566%22%20target%3D%22_blank%22%3E%40hokeycokey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%3C%2FP%3E%3CP%3EJust%20right%20click%20on%20this%20number%20and%20select%20Number%20format.%20This%20action%20will%20open%20Format%20cell%20window.%20The%20second%20table%20is%20pivot%2C%20right%3F%3C%2FP%3E%3CP%3ESo%2C%20when%20select%20Number%20in%20Format%20cells%20window%20it%20will%20apply%20to%20all%20cells%20in%20current%20field.%3C%2FP%3E%3CP%3EDo%20the%20same%20for%20all%20fields.%3C%2FP%3E%3CP%3EHope%20that%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2102051%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102051%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20-%20yes%20I%20have%20done%20that%20-%20I%20have%20even%20just%20removed%20all%20the%20data%20in%20the%20cells%20that%20are%20General%2C%20changed%20the%20cells%20to%20Number%2C%20re-entered%20the%20data%20and%20then%20updated%20the%20table%20and%20it%20is%20still%20rounding.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20that%20the%20table%20is%20bringing%20in%20the%20rounding%20from%20the%20original%20General%20cells%20and%20not%20respecting%20it%20has%20changed%20to%20a%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20I'm%20not%20sure%20how%20to%20recreate%20what%20I%20have%20done%20I%20can't%20re-do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20can't%20find%20how%20to%20access%20the%20properties%20to%20tell%20it%20to%20bring%20in%20the%20Number%20data%20and%20not%20the%20General%20(rounded)%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F210670%22%20target%3D%22_blank%22%3E%40Boriana%20Petrova%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2102488%22%20slang%3D%22en-US%22%3ERe%3A%20Rounding%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F944566%22%20target%3D%22_blank%22%3E%40hokeycokey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20speak%20about%20formatting%2C%20not%20about%20rounding.%20To%20be%20sure%2C%20did%20you%20apply%20Number%20format%20with%20Field%20settings%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20392px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249861i4D9E6EEC7A5452D2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have created six tables (in one sheet) from data in six different sheets.

From those six tables I have merged and created two pivot tables (income and expenses)

 

(Please don't ask how I did it as I have no idea now)

 

However one, only one, of the tables is rounding up the numbers. 
My columns are all set to Number and two decimal places on both original data and table

 

How do I stop this rounding please??

 

 

9 Replies
Please provide some example...

 

Screenshot 2021-01-27 13.46.33.png

 This is the original data which I have discovered is the only one that is General cells not Number cells

 

Table.jpg

 This is  how it looks on the table in the lower part - 203.69 has been rounded to 204

 

I changed the original data to Number and refreshed the table but it did not solve the problem

 

@Boriana Petrova 

@hokeycokey 

Okay,

Just right click on this number and select Number format. This action will open Format cell window. The second table is pivot, right?

So, when select Number in Format cells window it will apply to all cells in current field.

Do the same for all fields.

Hope that help

 

 

Hi

 

Thank you - yes I have done that - I have even just removed all the data in the cells that are General, changed the cells to Number, re-entered the data and then updated the table and it is still rounding.

I think that the table is bringing in the rounding from the original General cells and not respecting it has changed to a number.

 

Because I'm not sure how to recreate what I have done I can't re-do it.

 

I also can't find how to access the properties to tell it to bring in the Number data and not the General (rounded) data

 

@Boriana Petrova 

@hokeycokey 

We speak about formatting, not about rounding. To be sure, did you apply Number format with Field settings?

image.png

How do I find that setting as have no idea??

 

Thanks

 

@Sergei Baklan 

@hokeycokey 

That's in Field Settings

on ribbon

image.png

or expand within right pane

image.png

or from right-click menu on column

image.png

Great

 

Thanks so much for your help

 

@Sergei Baklan 

@hokeycokey , you are welcome