How to round a range of cells in Excel?
When working with numerical data in Excel, it is common to encounter values with multiple decimal places. For instance, you may have a dataset where numbers such as 2.1054 appear, but you only require them in a rounded format, such as 2.11 to standardize presentation or prepare the data for reporting. Manually rounding each value can be tedious, especially with large data ranges. Fortunately, Excel provides several efficient ways to round a range of cells to a desired number of decimal places. Below, you'll find step-by-step instructions for several practical methods, along with usage tips and comparative notes for choosing the most suitable approach for your needs.
Round a range of cells with ROUND function
Round a range of cells with VBA code
Quickly round / round up / round down a range of cells with Kutools for Excel
Alternative: Use Cell Formatting for Displayed Rounding
Alternative: Use MROUND function for rounding to a specified multiple
Round a range of cells with ROUND function
Excel’s built-in ROUND function is a straightforward way to round numbers to a specific number of decimal places. Its syntax is ROUND (number, num_digits), where “number” is the value to round, and “num_digits” specifies the number of decimal places.
Suppose you have a range of numeric values in cells A2:A10 that you want to round. Follow these steps:
1. Click in a blank cell where you want to place the rounded result—for example, C2. Enter the following formula:
=ROUND(A2,2)
2. Press Enter. This will display the value from A2 rounded to two decimal places in C2.
3. To round the entire range, move your cursor to the lower-right corner of cell C2 until it becomes a small plus sign (the fill handle). Drag this handle down over your target range (e.g., to C10). This will copy the ROUND formula for each corresponding value in column A. The results display as shown below:
If you want the rounded values to replace the original numbers, you can copy the rounded results and use Paste Special > Values to overwrite the original range. However, note that the original data will be lost if you overwrite.
The ROUND function is reliable for ensuring calculation accuracy and can be combined with other formulas as needed. However, remember that using formulas will require maintaining an additional column, unless you overwrite the originals.
Round / Round up / Round down a range of numbers without formula: With Kutools for Excel's Round feature, you can quickly round, round up, round down or round to even a range of cells as you need without any formulas.
|
Round a range of cells with VBA code
For advanced users or those who frequently perform batch rounding on different ranges, automating the rounding process with VBA can improve efficiency. This method is especially suitable if you want to process a large number of cells at once without relying on extra columns.
Here’s how to use a VBA macro to round a selected range of numbers in place:
1. First, select the range of cells you want to round.
2. Go to the Excel menu and click Developer > Visual Basic. If you do not see the Developer tab, you may need to enable it in Excel’s options. In the Microsoft Visual Basic for Applications window that appears, click Insert > Module to add a new code module, and then paste the following code:
VBA: Round a range of cells:
Sub RoundNum()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xNum As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNum = Application.InputBox("Decimal", xTitleId, Type:=1)
For Each Rng In WorkRng
Rng.Value = Application.WorksheetFunction.Round(Rng.Value, xNum)
Next
End Sub3. To run the code, click the 
“Run” button in the VBA editor. A dialog box will appear. Select the range you wish to round if prompted (you can also use your mouse to select directly in the sheet). See screenshot:
4. Click OK. You will then be prompted to enter the number of decimal places for rounding—enter your needed value (for example, 2 for two decimal places), then click OK again. See screenshot:
5. The values in your selected range will be rounded in place according to your input. See screenshot:
When using VBA, be aware that changes are immediate and cannot be undone. Always consider keeping a backup of your data before running macros, especially on important sheets.
Quickly round / Round up / round down a range of cells with Kutools for Excel
Kutools for Excel offers an efficient solution that allows rounding, rounding up, or rounding down a selection of cells—all without formulas. This is very suitable for users who want quick results and need an option to customize how rounding is handled (normal rounding, always up, or always down) across an entire range.
To use this feature, make sure you have already downloaded and installed Kutools for Excel on your computer. Follow these instructions:
1. Highlight the group of cells that you wish to round. Then go to the Kutools tab, select Round in the options menu. See screenshot:
2. In the Round without Formula dialog box, enter your desired number of decimal places into the Decimal place field. Next, select the operation you want: Rounding (standard rounding), Rounding up (always round numbers upwards), or Rounding down (always round down). When ready, click Ok. All selected cells will be rounded according to your settings. See screenshot:
Kutools applies the result directly to the original cells, simplifying your workflow and producing immediate results. You don’t need to create extra columns or write formulas. It’s especially useful when handling large datasets or repetitive data-cleaning tasks. However, note that this change overwrites original values, so consider keeping a backup if needed.
Click Download and free trial Kutools for Excel Now!
Alternative: Use Cell Formatting for Displayed Rounding
If your main concern is only how numbers appear (not their true calculated value), Excel’s built-in cell formatting can “display” rounded values without modifying the real data. This is ideal for reports or presentations where displayed values should be concise, but you want to retain calculation accuracy in the background.
To change the display of decimals:
- Select the range you wish to format.
- Right-click and choose Format Cells, then go to the Number tab and select Number category.
- Adjust Decimal places to your preference (for example, set to 2 for two decimals).
- Click OK. The values in your range will now appear rounded, but their full original values are still present in the cell, making this approach non-destructive.
This method is quick and risk-free; however, calculations based on these cells will still use the full original values, so keep this in mind when analyzing data.
Alternative: Use MROUND function for rounding to a specified multiple
Sometimes you may want to round values to the nearest specified multiple, such as rounding 7.8 to the nearest 0.5 (resulting in8). Excel’s MROUND function is suitable for such scenarios.
1. In a blank cell (e.g., C2), enter the following formula to round the value in A2 to the nearest multiple of0.5:
=MROUND(A2,0.5)2. Press Enter to calculate, and then use the fill handle to copy the formula for other cells as needed.
MROUND is particularly useful in pricing, scheduling, or categorization scenarios where data must align to a specific increment or standard value. However, note it requires the Analysis ToolPak add-in in older Excel versions (Excel 2007 or earlier).
Tips & Troubleshooting:
- When overwriting data (with VBA, Kutools, or by pasting values), always consider saving a backup to prevent data loss.
- If you receive a #NAME? error with MROUND, check if you have enabled the necessary add-ins under File > Options > Add-ins.
- ROUND formulas will not automatically change your original data—copy values back if you want to replace old numbers.
- Kutools streamlines routine tasks for users working with large volumes of data or repetitive reports.
- Always confirm you are rounding the correct range before running macros.
By understanding these different methods, you can optimize your rounding approach according to your workflow, whether you favor formulas, automation, or direct in-cell operations.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in