How to round numbers (up/down) to hundreds, thousands, or millions in Excel?

The ROUND, ROUNDUP, and ROUNDDOWN functions in Excel make it straightforward to round numbers to specific units such as hundreds, thousands, or millions. These formulas are especially useful when you want the results to be dynamically linked to the original data, ensuring the rounded values update automatically if the source numbers change.

As an example, suppose you want to round values to the nearest million. Select a blank cell adjacent to the number you wish to round (for instance, if your data starts from cell A2), and type the following formula:

=ROUND(A2,-6)

After entering the formula, press Enter to confirm. To round other numbers in your list, drag the cell’s AutoFill Handle down or across your data range. This ensures all selected numbers are rounded according to the desired unit. See screenshot:

• ROUND(A2,-6): -6 specifies rounding to the nearest million. The negative value tells Excel to round to the left of the decimal point.
• A2: This is the reference to the cell containing the value you wish to round. Adjust this reference according to your worksheet setup.

• To round to the nearest thousand, use:

  =ROUND(A2,-3)

• To round to the nearest hundred, use:

  =ROUND(A2,-2)

• To always round up to the next million, use:

  =ROUNDUP(A2,-6)

• To always round down to the previous million, use:

  =ROUNDDOWN(A2,-6)

After entering a formula, you can copy it to adjacent cells—select the cell containing the formula, press Ctrl+C, then select the range you want to paste into and press Ctrl+V.

• If your original numbers are formatted as text, these formulas may return errors. Ensure your data is numeric before applying the formula.
• If formulas return #VALUE! errors, check for blank cells or non-numeric data in your selection.
• Dragging the AutoFill Handle beyond your data range may result in empty or incorrect results.

This formula method is ideal when you want to preserve original data and have rounded values update automatically if source numbers change. However, if you prefer overwriting the original values rather than having separate formula results, consider Kutools or the macro methods below.

Kutools for Excel provides an intuitive Round utility that facilitates rapid batch rounding of numbers—without requiring manual formula entry. This tool is particularly helpful when you want to overwrite existing values directly, speeding up formatting for reports and presentations.

Select the number cells you want to round, then click Kutools > Round.

In the "Round without Formula" dialog box, enter -6 in the Decimal place field. Choose your preferred rounding option (Round, Round Up, or Round Down), and click OK. See screenshot:

• Type -6 to round to millions.
• Type -3 to round to thousands.
• Type -2 to round to hundreds.

Once the process is complete, the original values in the selected range will be replaced with rounded numbers based on your settings.

• This method is efficient for cleaning up large data sets for reporting where formulas are not needed.
• It overwrites original data, so consider making a backup if you need to retain raw values.

• If you accidentally overwrite important numbers, use Excel’s undo function (Ctrl+Z) immediately.
• Check that calculation mode is set correctly in Kutools dialog—it affects rounding direction.
• Data formatted as text will not be processed—ensure selected cells contain numerical values.

Kutools for Excel also features an Operation tool that allows batch rounding of numbers to hundreds, thousands, or millions. You can choose whether to apply rounding as a dynamic formula or simply overwrite the numbers with static results. This flexibility is well-suited to varying user needs, such as maintaining ongoing calculations in financial analysis or preparing finalized figures for published reports.

Select the numbers you want to round, then go to Kutools > More (under the Editing group) > Operation.

In the Operation Tools dialog, select Rounding from the Operation box, then type -6 in the Operand box. Choose whether to enable the Create formulas option:

• If checked, the tool generates formulas, so the rounded values update automatically if the source values change.
• If unchecked, numbers are replaced with fixed rounded results.

After clicking OK, all selected numbers are rounded, and results will overwrite your selection. Depending on your choice for "Create formulas", either dynamic formulas or static values are produced, as illustrated below:

• Preserves source data if formulas are created; useful for dashboards or dynamic reports.
• Static rounding is best when numbers must not change after rounding.

• When creating formulas, avoid deleting source data, as it will affect your rounded results.
• Always preview changes—in large datasets, incorrect operands may produce unexpected results.
• Resetting options between operations ensures you do not accidentally apply the wrong rounding level.

Average range with rounding in Excel

Round percentage values to two decimal places in Excel