Rounding the value itself rather than only how it is displayed, so a column of pennies adds up to what it shows.
The formula
=ROUND(A2,2) in column B
What each part does
A2
The figure being rounded.
2
How many decimal places to keep.
How it works, and what to watch for
Formatting a cell to two decimals hides the rest of the number but keeps it. Add up a column of those and the total can look a penny or two out, because Excel is adding the hidden decimals.
ROUND changes the stored value, so what you see is what is added. On money, that is nearly always what is wanted.
ROUND follows the usual rule: .5 and above goes up. ROUNDUP and ROUNDDOWN take the same two arguments when the direction has to be fixed.
Questions
Why is my total off by a penny?
The figures are formatted rather than rounded, so hidden decimals are being added. Round the values themselves and it settles.
How do I round to the nearest 5 or 100?
MROUND(A2,5) or MROUND(A2,100) rounds to a multiple rather than a number of decimals.
Does ROUND work on negatives?
Yes, and it rounds away from zero: -2.345 becomes -2.35.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
