Rounding: ROUND and what the cell shows
A cell formatted to two decimals can show 1.65 while it stores 1.649175. Only ROUND changes the stored number.
"It says 1.65, so it is 1.65"
Many beginners hold one belief about spreadsheets without ever saying it: the number you see in a cell is the number in the cell. Take a sales tax of 8.25% on a price of 19.99. The tax is 19.99 × 0.0825 = 1.649175. Format the cell to two decimal places and it reads 1.65. It still holds 1.649175, and every formula that uses it works with that full value. Formatting changes the look of a number. It never changes the value underneath.
The belief breaks in totals. Put =1/3 in three cells and format them to two decimals, and each shows 0.33. Their SUM shows 1.00, because the stored values add to exactly 1. Someone adding the visible numbers by hand gets 0.99 and decides the sheet is broken. On invoices and pay slips, where every visible figure has to add up, the cure is to round the stored value itself, at the point where the money is calculated.
There is a second, quieter reason the stored value can surprise you. Spreadsheets store numbers in binary, and many decimals, such as 0.1, have no exact binary form. Microsoft gives Excel 15 digits of precision, so after a long chain of arithmetic, tiny leftovers can appear far past the fifteenth digit. You usually can't see them. They are still part of the stored value, and a comparison with an exact figure can come out FALSE because of them. Rounding before you compare, as in =ROUND(A1,2)=0.3, is a safe habit.
What ROUND does to the stored number
ROUND(number, digits) rounds to a given number of decimal places, and unlike formatting, it changes the stored value. Microsoft's examples: =ROUND(2.15, 1) is 2.2, =ROUND(2.149, 1) is 2.1, and =ROUND(23.7825, 2) is 23.78. A halfway digit rounds away from zero, so =ROUND(-1.475, 2) is −1.48. Google's help phrases it as up and down in terms of size, irrespective of sign. Digits of 0 round to a whole number. A negative value rounds to the left of the decimal point: =ROUND(626.3, -3) is 1000 and =ROUND(1.98, -1) is 0.
| Formula | Result |
|---|---|
| =ROUND(2.15, 1) | 2.2 |
| =ROUND(2.149, 1) | 2.1 |
| =ROUND(-1.475, 2) | −1.48 |
| =ROUND(21.5, -1) | 20 |
| =ROUND(626.3, -3) | 1000 |
| =ROUND(-50.55, -2) | −100 |
Here the programs part ways slightly. In Excel the second argument, num_digits, is required. In Google Sheets, places is optional and 0 by default, so =ROUND(2.5) gives 3 in Sheets while Excel asks you to finish the formula. Writing the digits every time works in both and reads more clearly. For rounding that always goes one way, both programs have ROUNDUP and ROUNDDOWN; the lab here uses ROUND only.
ROUND can wrap any calculation. =ROUND(SUM(B2:B9)/COUNT(B2:B9), 1) gives an average to one decimal place, and =ROUND(A2*1.1, 0) adds 10% to a price and rounds to the nearest whole unit. Read formulas like these from the inside out: the calculation first, then the rounding of its result.
Drag to turn. Scroll or pinch to zoom.
- Clicking "decrease decimal" and calling the number rounded. That is number formatting. The cell looks rounded, but formulas still use every digit. Only ROUND changes the stored value.
- Rounding only the grand total. Each line still holds unrounded amounts, so the visible lines don't add up to the visible total. Round each amount where it is calculated.
- Writing =ROUND(A2) in Excel. Excel requires num_digits. Sheets accepts the formula and rounds to a whole number, so a sheet that works in Sheets can stop at this formula in Excel.
- Expecting -1.475 to round to -1.47. Halfway rounds away from zero by size, so the result is −1.48.
- What does =ROUND(2.15, 1) return?
- What does =ROUND(2.149, 1) return?
- What does =ROUND(-1.475, 2) return?
- What does =ROUND(626.3, -3) return?
- What does =ROUND(21.5, -1) return?
- What does =ROUND(23.7825, 2) return?
- B2 holds 1.649175 formatted to show 1.65. What does =B2*100 return?
- Three cells each hold =1/3, shown as 0.33. What does their SUM show at two decimals?
- 1.00
- 0.99
- 0.33
- #VALUE!
- What does =ROUND(2.5) do?
- Google Sheets gives 3; Excel asks for the missing argument
- Both give 2
- Both give 3
- Both show #N/A
- Where should money be rounded so that visible figures add up?
- Where each amount is calculated
- Only in the grand total
- Nowhere; formatting is enough
- In a separate sheet
- Number formatting changes what you see, not what is stored.
- ROUND(number, digits): halfway rounds away from zero; negative digits round left of the point.
- Excel requires the digits argument; Google Sheets defaults it to 0.
- Round money where it is calculated so visible numbers add up.
Sources for this course (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Checked: 2026-10-19