To round a number to the nearest whole number in Excel, use ROUND with 0 as the number of digits. For example, if the value is in A2, enter =ROUND(A2,0) in another cell. Excel returns a numeric result, such as 13 for 12.6, rather than merely hiding the decimal places. You can then fill the formula down a column or adapt it for a different rounding rule.
What You’ll Need
- A Microsoft Excel workbook, preferably with the numbers you want to round in a worksheet column.
- An empty cell or column for the rounded results. Keeping the original values is useful if you later need to check or change the rounding method.
- Basic familiarity with selecting a cell and entering a formula. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
Step-by-Step
- Identify the values to round. Find the column or cells containing your numbers. For example, suppose the original values are in A2:A6 and you want the rounded results in column B. Use a separate output column so the original precision remains available.
- Select the first output cell. Click B2, or whichever empty cell is on the same row as the first value. If your data has headings, put a label such as Rounded in B1 so the new column is easy to identify.
- Enter the ROUND formula. With the value to round in A2 and the result in B2, enter
=ROUND(A2,0). The first argument identifies the number or cell to round; the second specifies how many decimal places to keep. Zero means round to a whole number. - Press Enter and check the result. If A2 contains 12.6, B2 displays 13. If A2 contains 12.4, B2 displays 12. Excel rounds a value whose fractional part is exactly one-half away from zero: for example, with -12.5 in A2 and the formula in B2, the result is -13.
- Copy the formula to the remaining rows. Select B2 and point to the small square at the bottom-right corner of the cell, called the fill handle. Drag it down through the rows you need. You can also copy B2, select the destination range, and press Ctrl+V on Windows. Excel adjusts the reference for each row, so the next result uses A3, then A4, and so on.
- Confirm the formula references. Click a few results farther down the column and check the formula bar. For example, the result in B3 should refer to A3, not continue referring to A2. This helps catch an accidentally copied or locked reference before you use the numbers elsewhere.
- Use the rounded results where appropriate. Formulas in column B remain linked to the original values in column A. If the source number changes, the rounded result updates automatically. If you need fixed results instead, copy the output cells and use Home > Paste > Paste Values. This replaces the formulas with their current numeric results.
Choose a different rounding direction
ROUND rounds to the nearest whole number. If your requirement is always to round up or always to round down, choose the function that matches that rule instead. These are not interchangeable: the choice affects every value, including negative numbers and values close to zero.
With the original number in A2 and the result in B2, use =ROUNDUP(A2,0) to round away from zero. For example, 12.1 becomes 13, and -12.1 becomes -13. This is useful when a rule requires moving every non-whole value to the next whole-number magnitude, but it may not match a business or reporting policy that defines up differently.
With the original number in A2 and the result in B2, use =ROUNDDOWN(A2,0) to round toward zero. For example, 12.9 becomes 12, while -12.9 becomes -12. This does not mean rounding toward negative infinity; that distinction matters for negative values.
Common Mistakes
- Changing the display instead of the value. The Increase Decimal and Decrease Decimal buttons, or the number format controls on the Home tab, change how many decimal places appear. They do not necessarily change the underlying value used in calculations. Use a rounding formula when later calculations must use the rounded number.
- Using the wrong number of digits. The second argument controls decimal places. A value of 0 gives whole numbers, a positive value keeps decimal places, and a negative value rounds to the left of the decimal point. If your goal is an integer, check that the second argument is zero.
- Assuming ROUND always rounds upward. It rounds to the nearest value. A number with a fractional part below one-half rounds down in magnitude, while one at or above one-half rounds up in magnitude. For a negative value, away from zero is the more reliable way to describe the tie behavior.
- Using INT when you mean nearest whole number. INT rounds down toward negative infinity, so it can give a different result from rounding to the nearest integer. For example, an input of -2.1 becomes -3 with INT, even though the nearest whole number is -2. Use ROUND for nearest-number rounding.
- Overwriting source data too soon. Replacing original values with rounded results removes the detail that may be needed for auditing or later calculations. Keep the source column until you have confirmed the result and know the rounded values are the ones you need.
- Expecting the formula to change the original cell. A formula entered in B2 returns its answer in B2; it does not alter A2. This is usually safer, because the raw value remains intact. If you must replace the source values, make a copy of the data first.
Tips
- Use an absolute reference for a shared input. If every row should round using a single value stored in another cell, lock that reference with dollar signs. For example, a formula that refers to a setting in D1 can be copied down while continuing to use D1. Do this only when the reference really should stay fixed; ordinary row-by-row values should use relative references.
- Make the rounding rule clear to other users. Label the output column with a description such as Nearest whole number or Rounded quantity. If up or down rounding is intentional, say so in the heading or a nearby note.
- Keep display formatting separate from calculation logic. You can format the result column with zero decimal places for a tidy presentation, but the formula is what determines the returned value. If the result column will feed another calculation, use the formula result rather than relying on what the cell looks like.
- Check the data before filling down. Blank cells, text entries, and errors in the source range can produce unexpected-looking output or errors. Review a few source cells and results, especially at the top, bottom, and any rows with unusual values.
- Use the fill handle efficiently. When the adjacent column has a continuous data range, double-clicking the fill handle often fills the formula down alongside it. Check where it stops; gaps in neighboring data can affect how far Excel fills.
- Know the regional separator setting. The formulas here use commas, as in standard English-language Excel settings. Some installations use semicolons between function arguments because of regional settings. If Excel rejects a formula with commas, try the separator used by other working formulas in that workbook.
Troubleshooting
The formula appears as text instead of calculating. Check whether the cell is formatted as Text. Change its format to General from Home > Number Format, then edit the cell by pressing F2 and Enter. Also check that the formula begins with an equals sign and that there is no apostrophe or extra space before it. If many formulas are displayed instead of results, make sure Show Formulas has not been turned on from the Formulas tab.
Excel reports a problem with the formula. Confirm that the opening and closing parentheses match and that the arguments are separated correctly. The standard formula in B2, with the number in A2, is =ROUND(A2,0). In a regional setup that uses semicolons, the equivalent separators may differ. Use the same argument separator as formulas that already work in your Excel installation.
The result looks unchanged. The source may already be a whole number, or the fractional part may be small enough that nearest-number rounding returns the same integer. Select the result cell and inspect the formula bar to verify it contains the expected formula. If you only changed the number format, you may have changed appearance without rounding the stored value.
Negative numbers are not rounding as expected. Decide whether the requirement is nearest whole number, always away from zero, or toward zero. Use ROUND for nearest, ROUNDUP for away from zero, and ROUNDDOWN for toward zero. Do not substitute INT without checking the effect on negative values, because INT rounds toward negative infinity.
A copied formula gives the wrong row’s result. Click the cell and examine the reference. For a normal list, the formula in B3 should refer to A3. If the formula keeps referring to A2, remove an unintended dollar sign from the row reference or copy the formula again using the fill handle. Dollar signs make references fixed and are helpful only when you intend that behavior.
The formula returns an error or an unexpected result for a particular row. Look at the source cell for text, an existing error, or an unusual value. A number that looks numeric may have been imported as text. Correct or convert the source data as appropriate, then recalculate. Avoid masking errors until you know why they occurred, particularly if the rounded results will be used in reports.
Frequently Asked Questions
What formula rounds a number to the nearest whole number in Excel?
Use ROUND with zero decimal places. With a number in A2 and the answer in B2, enter =ROUND(A2,0). It returns a numeric result rounded to the nearest integer.
Does Excel round 0.5 up?
For positive numbers, a value exactly halfway between two whole numbers rounds to the larger whole number. Excel rounds halfway values away from zero, so a negative halfway value rounds to the more negative integer. This differs from some rounding conventions that use even-number rules for ties.
How do I round a whole column?
Put the formula in the first result row beside your data, then drag the fill handle down or copy and paste it into the rest of the output range. Check a lower row to confirm the cell reference moved with the formula. Keeping the results in a separate column preserves the original values.
Can I round a number without a formula?
You can change the number format to display zero decimal places, but that usually changes only the appearance; calculations can still use the original decimal value. If you need later calculations to use an actual rounded result, use a rounding formula and refer to its result.
What is the difference between ROUND, ROUNDUP, and ROUNDDOWN?
ROUND chooses the nearest value. ROUNDUP always moves away from zero, and ROUNDDOWN always moves toward zero. With zero decimal places, the latter two can differ from nearest rounding even when the decimal part is small.
Can I replace formulas with fixed rounded numbers?
Yes. Copy the result cells, then choose Home > Paste > Paste Values. The cells will contain the current numbers rather than formulas, so they will no longer update if the original inputs change. Keep a copy of the workbook or source data first if you may need to recalculate later.
Related
- How to Print in Excel on One Full Page
- How to Print Excel on One Page Without Cutting Off Data
- How to Print in Excel Fit to Page Without Clipping
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Copy a Formula Down All Rows in ExcelTo copy a formula down a column in Excel, enter it in the first cell, select the cells…
- Excel Trim a Table: Clean Text or Remove Rows?To trim text inside an Excel table, add a column with =TRIM([@[Raw Name]]); to trim the table’s physical…
- Add in Excel Without a Formula: Practical Ways to TotalTo add numbers in Excel without putting a formula in a cell, select the numbers and read their…
- How to Get the Highest Number in Excel with the MAX FunctionTo get the highest number in a range, use Excel’s MAX function. With sample values in A2:A8, enter…
- Excel Filter by Text Length: Steps and FormulasTo filter Excel rows by the number of characters in a text cell, add a helper column with…
- Excel LEFT: How to Return Text Before a Specific WordTo return everything before a particular word in an Excel cell, use =TEXTBEFORE(A2,B2) in Microsoft 365, where A2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.