Forum Discussion
brakthol
Aug 24, 2023Copper Contributor
Change how Excel Rounds Displayed Values
From all of my searching, it appears that I want the opposite of most of the questions out there. I have a spreadsheet dealing with hazardous materials. On it, there are lists of weights, and dimensi...
- Aug 24, 2023
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.
Detlef_Lewin
Aug 24, 2023Silver Contributor
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.
mathetes
Aug 24, 2023Silver Contributor
A new use (or variation) of "helper columns": not to do calculations, per se, but to handle the display. I like it.
- Detlef_LewinAug 24, 2023Silver Contributor
I will tell my secret contact.