Jump to content
Welcome Guest!

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

Sign in to follow this  
spacecityguy

Excel adding 2 decimal places after editing a formula

Recommended Posts

Hi all,

 

I thought I was a proficient Excel user, and I'm ashamed to say that I can't figure out how to solve this through this years (have done web searches every now and then). I just put up with it, but when I think how much time I have spent simply correcting Excel's automatic formatting, it hurts.

 

I build a lot of Excel templates/models and often use complex formulas. I noticed ever since Excel 2007, when I edit a formula in a cell with number formatting applied to show whole numbers (no decimals) beforehand, Excel will ignore my number formatting and insert 2 decimal places to the result. I have tried the advanced option on automatic inserting decimal places and also pre-format all cells used in the formula have the same format but to no avail.

 

Anyone know what causes this behavior and how to fix it? I initially thought it was a bug in Excel 2007 and MSFT would fix it in the next version, but it doesn't seem to be the case. So I think I'm missing something.

 

Thanks.

Share this post


Link to post
Share on other sites
Caleeco    8

Hello Spacecityguy, 

 

Thanks for joining the forum! Could you please tell me which version of Excel you are using, as I don't seem to be able to recreate this problem. Or better yet, upload a sample file where this problem exists. 

 

I tired the following:

  1. Populated A1:A5 with decimal values
  2. Populated B1:B5 with decimal values
  3. set up a formula below in cell C1 and change the format to Number > 0 decimal place
Code:

=SUMPRODUCT(A1:A5,B1:B5)

 

If I change the values in columns A or B, or if I change the Range of the arrays (in the sumproduct) the number formatting does not change. I am running Excel 2016.

 

I look forward to hearing from you

Caleeco

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×