Forum Discussion
Solved
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 dimensions.
Normal functionality is when a cell is too small to show the full value, Excel displays a rounded value. This is just a displayed value, which does not affect the actual value. This fine with me; except that I need to change how that rounding is done. I need the displayed rounded value to always round up (safety concerns).
* Say I have 1.2 lbs of a hazardous material, but the cell is only big enough to show one character. Normally Excel rounds the displayed down to 1, while not affecting the actual value of 1.2.
* I need this to round up to 2; but again, only the display and not the actual number.\
I've chosen small numbers to make the example easy, while in reality the numbers are longer ~10 digits or so. Changing the precision doesn't work, because I don't have infinite width for the cells. At some point, the cell is going to be skinnier than the value in the cell, and Excel will round the display.
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.
6 Replies
- I am familiar with those functions. The issue is that they are changing the actual value of the cell. I need the actual value of the cell to stay untouched.
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.
