How do I show formatted numbers only in the formatted form in Excel cells?

Question

So when I am using excel, sometimes a formula used will prompt an answer such as 67.9999999.

When I then paste this into another cell which I have formatted to 2 decimal places it shows 68, but in the top formula bar it will still show it as 67.99999.

How am I able to change this without manually going through each one and rewriting it in the 2 decimal place format, so both the cell and the formula bar now reflect the 2 decimal place number?

Thankyou

Answer 1

Here is a typical example of the value displayed in the cell not matching the value in the Formula Bar:

Note the cell format is General

Select the cells like this that you want to fix and run this short macro:

Sub FixData()
    Dim r As Range

    For Each r In Selection
        r.Value = r.Text
    Next r
End Sub

to yield:

(the macro make no attempt to adjust the format of the cell)

Answer 2

Sure. Copy the data from the public spreadsheet (at a time when no one has it open and might modify it before you paste the corrected data back in!) into your spreadsheet in some convenient place. If it is a spreadsheet you use only for this purpose, or one you just open New, do the work, then close without ever saving, that'd be best: either of those two choices.

Once it is there with all the decimal places it came with, write a formula in a column nearby like the following:

=ROUND( DATA, 2)

ROUND() will change each value to two decimal places in the same manner Excel uses when choosing how to display it showing only two decimal places.

Copy the cell down matching the column of data you copied in. You now have a column of values rounded to two decimal places that matches the displayed values in the public spreadsheet.

Copy that range of cells. Go back to the public spreadsheet and Paste | Special | Values overtop the original data cells. They are now replaced with the same value that Excel was showing.

If you aren't familiar with Paste | Special | Values, there are many ways to make it happen, but the easiest is to use a shortcut key combination. I use a different one that is hard to explain here, but for many years used Alt-E-S-V, then pressing Enter. ANY way you know how to do it though, the end result is happiness.

You still have the original values in the throwaway spreadsheet so take a quick look that nothing funky happened. If it all looks good, save it and then close the throwaway spreadsheet. If it looks funky though, try to figure out why, but then either just close the public spreadsheet WITHOUT saving (you can always start completely over, eh?), press Ctrl-Z, or copy the original data back over the stuff you just pasted in. Three good ways to make sure an unhappy bit of work goes away leaving you the original.

The key is that you are willing, as you say above, to do the "copy it over, fix it, copy it back" work. If something makes that impossible (like... the public spreadsheet is never closed by all possible users for long enough for you to do this), then this method won't work.

Answer 3

Anyone with the same need (I don't think Fiona Appleton will be reading this answer after five years!), might get value from the answer I gave to:

Decimals still in formula bar view (not in cell view) - need to round and remove

which asks a similar question.

It is VERY similar, but different so pasting it here would not really suit, exactly. It basically explains the gains and losses from Precision As Displayed to help someone choose to use it or not based on their situation. It points out there are some VBA approaches and the other answer to that question gives one of those solutions. It also has a method for exporting the data in the form needed (exporting it so is the need in the other question, but it doesn't seem so here so that might not be of any use at all). It also mentions the downside to hiding the formula editor if one's goal is that the users NEVER see the precise values, only the displayed values, but that wouldn't matter if one only needed to not rub the precise values in their faces.