13 Ways to Round Up in Microsoft Excel

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.

Decrease decimal first attempt
Decrease decimal first attempt

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.

Click Decrease decimal multiple times
Click Decrease decimal multiple times

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

Format cells decimal places
Format cells decimal places

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.

Transformed fractions to uniform decimal points
Transformed fractions to uniform decimal points

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

Decimal places zero for whole numbers
Decimal places zero for whole numbers

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

Using the ROUNDUP Function

Using ROUNDUP function for decimals
Using ROUNDUP function for decimals

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.

Excel round up using ROUNDUP function
Excel round up using ROUNDUP function

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

Use Paste Special to replace values
Use Paste Special to replace values

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

Excel round up to whole number
Excel round up to whole number

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.

Round up in Excel using ROUNDUP function
Round up in Excel using ROUNDUP function

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.

Using INT to round up in Excel
Using INT to round up in Excel

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.

Round up in Excel using INT
Round up in Excel using INT

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:

ROUNDUP function for nearest ten
ROUNDUP function for the nearest ten

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.

Using fill handle for ROUNDUP
Using fill handle for ROUNDUP

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

Getting the nearest hundreds with ROUNDUP
Getting the nearest hundreds with ROUNDUP

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

Using MROUND
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.

Excel round up using MROUND
Excel round up using MROUND

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

Using CEILING

Problem with MROUND
Problem with MROUND

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.

Using CEILING
Using CEILING

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)  
Excel round up using CEILING
Excel round up using CEILING

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:

Using the CEILING formula
Using the CEILING formula

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.

Using fill handle for CEILING
Using fill handle for CEILING

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

Custom time codes
Custom time codes

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.

Round up fractional time to hour in Excel
Round up fractional time to hour in Excel

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

Using CEILING to convert to minutes
Using CEILING to convert to minutes

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.

Using fill handle for CEILING for minutes
Using fill handle for CEILING for minutes

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

Round up time stamps to minutes
Round up time stamps to minutes

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

Export data to Power Query
Export data to 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.

Custom column formula
Custom column formula

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.

Roundup using Power Query
Roundup using Power Query

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

Close and load to
Close and load to

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

Import data
Import data

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

Excel roundup in Power Query
Excel roundup in Power Query

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

Power Query round up up to two decimals
Power Query rounds up up to two decimals

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

Number.Round([Price],2)
Rounded up to two decimals
Rounded up to two decimals

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.

VBA script for rounding up
VBA script for rounding up

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!

Macro dialog
Macro dialog

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

Input dataset
Input dataset

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

Choose decimal or whole numbers
Choose decimal or whole numbers

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

Enter decimal points
Enter decimal points

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

Select destination cell
Select destination cell

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

Round up using VBA
Round up using VBA

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!

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃