If you’re wondering how to round up in Excel for precision, adherence to standards, or to simplify financial calculations, read this Microsoft Excel tutorial now.

In day-to-day life, there’s very little need to express numbers in long decimal points. Such decimal values can often be problematic. Consider a scenario where your customer is required to pay $70.8912 for an order, as calculated by your billing system.

Making such a payment, whether in cash or digitally, can be complex. In such cases, rounding up the invoice value to the nearest whole number, such as $71, can simplify things for both you and the customer.

There are numerous other instances when dealing with currencies, units, time, etc., in Excel for practical situations. To help you with such calculation challenges in Excel, I’ve aggregated below all popular methods for Excel round up so you can use the skill in personal and professional capacities without fail.

Excel Round Up to a Specific Decimal Space

Suppose, you’re required to convert all decimal values in a dataset to up to two decimal points to ensure a constancy in further calculations involving the dataset.

Find below multiple methods to achieve that using Excel desktop and web apps:

Using the Decrease Decimal Button

If the database you’re working with is pretty small and you prefer to use user interface buttons or commands in Excel, this method is best suited for you.

Select the dataset which you need to round up.

Click the Decrease Decimal button inside the Number commands block in the Home tab.

Clicking once shall reformat the number values up to a specific number of decimal points. In the current tutorial, it’s up to four decimal points.

Then, you must click twice to reduce the decimal numbers to two decimal points.

You can also use the shortcut key combination Alt + H + 9 to round up in Excel by reducing the decimal points.

Alternatively, you can also use this method to round up to the nearest non-fractional numbers by removing all the decimal points. Excel shall add 1 to fractions like 98.578 and 96.667, to find the next whole number.

Using the Format Cells Dialog

Highlight the input dataset in your worksheet and press Ctrl + 1 to bring up the Format Cells dialog box.

There, select the Number category in the left-side navigation to open the Number formatting menu on the right.

Enter the desired value in the Decimal places field.

Click OK and Excel shall format the selected dataset of numbers to a uniform fractional number, like up to two decimal points.

If you put 0 in the Decimal places field, you get the nearest non-fraction integers.

Using the ROUNDUP Function

Enter the following ROUNDUP formula into a cell to the right of the target number value you want to round up to two decimal points.

=ROUNDUP(B2,2)

Hit Enter to calculate the cell.

You can drag down the fill handle to apply the formula to the rest of the numbers in your dataset.

You can now replace the calculated values on the original dataset using Ctrl + Alt + V and selecting Values in the Paste Special dialog.

Excel Round Up to the Closest Whole Number

If you wish to get rid of all the decimal points of a fractional integer and round the value up to the nearest whole integer, you can try any of these methods:

Using the ROUNDUP Function

Suppose, your input dataset is in the cell range B2:B11. Choose C2 and enter the following formula inside the cell:

=ROUNDUP(B2,0)

Hit Enter to calculate the cell.

Now, you can use the fill handle and drag it down until C11 to transform fractional numbers of different decimal points to the nearest whole number.

Using the INT Function

Another way to round up a fractional number in Excel is the INT function.

In the cell, where you want to convert a fractional integer to the nearest whole number, enter the following formula:

=INT(B2)+1

Hit Enter to calculate the cell and get the nearest whole number.

You can now use the fill handle to apply the formula to the remaining cells in the adjacent column.

Excel Round Up to Tens

Rounding up to the nearest tens is often necessary when dealing with large sets of data or financial calculations where precision isn’t required but simplicity is.

This is particularly useful in scenarios like estimating budgets or determining quantities for ordering supplies. To round up to the nearest ten in Excel using the ROUNDUP function, follow these steps:

Select a cell where you want an output and put the following formula into it:

=ROUNDUP(B2,-1)

Press Enter on the keyboard to get the value 100, which is the nearest ten for the fractional number 97.45056.

You can now drag the fill handle down the column until the cell to which data exists in the parallel column to transform all the input datasets.

Excel Round Up to Hundreds

Similarly, to calculate the nearest tens in Excel, the following formula calculates the nearest hundreds:

=ROUNDUP(B3,-2)

Excel Round Up to Multiple of 5

Rounding up to a multiple of five is often needed in various scenarios such as pricing calculations or when working with quantities that are typically rounded to the nearest multiple of five.

This can streamline processes like invoice generation or stock inventory management. Using Excel functions like MROUND and CEILING makes it easy to achieve this rounding precision. Here’s how to accomplish it using each function:

Using MROUND

Select the cell where you want the rounded value to the nearest multiple of five to appear.

Type the following formula into the selected cell and hit Enter to calculate the cell:

=MROUND(B2,5)

In the above formula, you must replace the cell reference B2 with the cell that contains the first instance of the fractional number you want to transform.

The value 5 in the formula tells Excel that you want the multiple of 5 for the input value. Suppose, you want the nearest whole number multiple of 3 for the same input data, you can replace 5 with 2.

You’ve now got the output value for one cell.

You can use the fill handle to calculate the multiples of 5 for the rest of the dataset.

Using CEILING

I’ve used the MROUND function in the above dataset to get the nearest whole number that are multiple of 5.

If you notice carefully, Excel reduced the values for certain input numbers that are closer to the lower multiple. This means MROUND shall round up and round down according to the input value.

To avoid this loss or confusion in real-world scenarios, you can use the CEILING function. It always rounds up the given fractional number to the nearest whole number which is also a multiple of a given integer, like 5, 10, 15, etc.

Highlight the cell where you’d like to get the whole number value that’s also completely divisible by 5.

Enter the following formula into the cell and hit Enter:

=CEILING(B2,5)  

You can now use the fill handle to copy and paste the formula to the rest of the cells in the column where you’re generating the whole numbers.

Excel Round Up to the Nearest Hour

When analyzing time-based data, rounding up to the nearest hour can provide a clearer overview of trends and patterns, especially when aggregating data into hourly intervals.

Again, in billing and time recording, you might need to round up the given timesheet entries to the nearest hour value if your client allows hourly billing only.

In all the above scenarios, you can follow these steps:

Go to the worksheet where the timestamp data exists. Select an empty cell to the right of the first time stamp.

In this cell, enter the following formula and hit Enter:

=CEILING(B2,1/24)

In the above formula, B2 is the input timestamp, and 1/24 is the significance value that converts the output to the nearest hour value.

Now, you should see a fractional value in the selected cell.

Use the fill handle to apply the formula to the rest of the cells.

Now, highlight the new fractional values you’ve got after applying the CEILING formula.

Press Ctrl + 1 to bring up the Format Cells dialog. There, go to the Custom cell formatting category and enter the code hh:mm:ss into the Type field.

Press OK to apply the custom cell formatting. Excel shall transform the fractional timestamps to the nearest whole-hour values.

Excel Round Up to the Nearest Minute

Enter the following formula in the cell where you want to convert a fractional time stamp to the nearest minute:

=CEILING(B2,1/1440)

The cell reference B2 is the timestamp you’re converting. Change it according to your own dataset.

1/1440 is the significance argument of the CEILING function that converts the input timestamp to the nearest whole minute value.

Hit Enter to calculate the cell.

Use the fill handle to apply the same formula to the rest of the cells in the column.

Apply the custom time formatting code hh:mm:ss by following the steps mentioned in the previous section.

You shall see that Excel has converted all fractional timestamps to the nearest minute values.

Excel Round Up in Power Query

Select your input dataset and click the From Table/Range command in the Get & Transform Data commands block on the Data tab.

Click OK on the Create Table dialog to export the Excel worksheet dataset to the Power Query Editor app.

On Power Query, go to the Add Column tab and click on the Custom Column option.

You shall see the Custom Column wizard. There, enter a name in the New column name field. Inside the Custom column formula field, enter the following Power Query M formula:

Number.Round([Price],0)

The formula element [Price] is the reference to the column whose data you’re modifying. The number 0 indicates that you want the rounded-up value to be the nearest whole number of the input values.

Click OK to create the new column containing the rounded-up whole numbers.

Go to the File tab and click on the Close & Load To option to export your Power Query dataset to the Excel worksheet.

In the Import Data dialog, choose Existing worksheet and highlight the cell range where you want to import the data. Click OK to confirm.

By now, you should have rounded up your dataset in Excel using Power Query.

If you wish to round up fractions up to two decimal points, use the following Power Query M formula:

Number.Round([Price],2)

Find above a working example of this formula.

Excel Round Up Using VBA

You can use Excel VBA to programmatically round up fractional numerical values in Excel through visual guidance.

Use this script to create a VBA macro:

Sub RoundUpData()
    Dim rng As Range
    Dim dest As Range
    Dim roundType As Variant
    Dim decimalPlaces As Variant

    ' Prompt the user to select the input data range
    On Error Resume Next
    Set rng = Application.InputBox("Select the range of data you want to round up", Type:=8)
    On Error GoTo 0

    ' Check if a range was selected
    If rng Is Nothing Then
        MsgBox "No range selected. Exiting..."
        Exit Sub
    End If

    ' Ask the user for the rounding type
    roundType = Application.InputBox("Enter 1 for rounding to the nearest whole number, or 2 for rounding to decimal places")

    ' Check if a valid rounding type was entered
    If roundType <> 1 And roundType <> 2 Then
        MsgBox "Invalid rounding type entered. Exiting..."
        Exit Sub
    End If

    ' If rounding to decimal places, ask the user for the number of decimal places
    If roundType = 2 Then
        decimalPlaces = Application.InputBox("Enter the number of decimal places to round to")
    End If

    ' Prompt the user to select the destination range
    On Error Resume Next
    Set dest = Application.InputBox("Select the top-left cell of the destination range", Type:=8)
    On Error GoTo 0

    ' Check if a destination was selected
    If dest Is Nothing Then
        MsgBox "No destination selected. Exiting..."
        Exit Sub
    End If

    ' Loop through each cell in the input range
    For Each cell In rng
        ' Only process cells that contain numeric data
        If IsNumeric(cell.Value) Then
            ' If rounding to the nearest whole number
            If roundType = 1 Then
                dest.Value = WorksheetFunction.RoundUp(cell.Value, 0)
            ' If rounding to decimal places
            ElseIf roundType = 2 Then
                dest.Value = WorksheetFunction.RoundUp(cell.Value, decimalPlaces)
            End If
            ' Move to the next cell in the destination range
            Set dest = dest.Offset(1, 0)
        End If
    Next cell

    MsgBox "Data rounded and pasted successfully!"
End Sub

If you don’t know how to set up a macro using a VBA script, check out this Excel tutorial now:

📒 Read More: How To Use The VBA Code You Find Online

Are you done creating the VBA macro? Awesome!

Press Alt + F8 to launch the Macro dialog. Select the RoundUpData macro and hit the Run button.

The VBA script shall show an input box so you can select the input data cell range using the mouse.

Next, there will be another prompt so you can choose between rounding up to whole numbers or decimal point options.

If you choose decimal points, then enter the numbers of decimals you want in the next prompt.

Finally, an input box shall ask you to select the first cell of the destination column.

Excel shall automatically transform the selected data.

Conclusions

These are all the tried and tested techniques to round up fractional numbers in Excel.

You can use the methods that involve Excel user interface commands and functions for relatively small datasets that too only if you’re exercising this occasionally.

If your dataset is gigantic, use the methods like Power Query and Excel VBA.

Did you find this Excel tutorial on rounding up fractional values useful? Do you know a better technique that I missed mentioning? Comment below!