Forum Discussion
Change how Excel Rounds Displayed Values
I don't think that is possible in the current Excel versions.
But to be sure I asked my secret contact at Microsoft:
I understand your requirement. You want Excel to display the rounded-up value in the cell, but you don’t want the actual value stored in the cell to change. Unfortunately, Excel’s cell formatting doesn’t provide an option to round up for display purposes only.
However, there’s a workaround you can use by creating an additional column to display the rounded-up values. Here’s how you can do it:
Suppose your weights are in column A. In column B (or any other column), use the ROUNDUP function to round up the weights. The formula would look like this: =ROUNDUP(A1, 0). This will round up the value in cell A1 to the nearest whole number.
Drag this formula down to apply it to all cells in column B.
Now, you can adjust the width of column A such that only the rounded-up values in column B are visible.
Remember, this is just a workaround and might not be ideal for all situations. It’s always recommended to keep the original data visible and clear for accuracy and reference purposes1234. If you need more help with Excel functions, there are many tutorials available online567.
I'm only looking to change the rounding of the Displayed number.
I don't think that is possible in the current Excel versions.
But to be sure I asked my secret contact at Microsoft:
I understand your requirement. You want Excel to display the rounded-up value in the cell, but you don’t want the actual value stored in the cell to change. Unfortunately, Excel’s cell formatting doesn’t provide an option to round up for display purposes only.
However, there’s a workaround you can use by creating an additional column to display the rounded-up values. Here’s how you can do it:
Suppose your weights are in column A. In column B (or any other column), use the ROUNDUP function to round up the weights. The formula would look like this: =ROUNDUP(A1, 0). This will round up the value in cell A1 to the nearest whole number.
Drag this formula down to apply it to all cells in column B.
Now, you can adjust the width of column A such that only the rounded-up values in column B are visible.
Remember, this is just a workaround and might not be ideal for all situations. It’s always recommended to keep the original data visible and clear for accuracy and reference purposes1234. If you need more help with Excel functions, there are many tutorials available online567.
- I have spent many hours trolling through search results, and digging into the Options available in Excel, all to no avail, so I was pretty certain that this wouldn't be possible. But I was hoping 🙂
Unfortunately, due to the specialized circumstances that I'm in, a second set of helper columns doesn't suit my needs, but I can see how they would work, and I agree that in general, that's a valid solution.
My problem is that I'm using a Gov Mandated form, and the cells are set to exact standards that I can't change. So when I output the data to the form, I am forced into rounded Display values. I was really hoping to be able to change how those Display values rounded, for safety considerations.
I appreciate all of your help with this,
Thank you,
Aaron A new use (or variation) of "helper columns": not to do calculations, per se, but to handle the display. I like it.
I will tell my secret contact.
