Excel: How to Round Properly
Microsoft’s spreadsheet program, Excel, not only helps you to create clear tables and charts, it can also do a lot of computing work for you. You can do complicated calculations with functions and formulas – if you know how they work. We explain how to correctly use the Excel formula ROUND.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
The Excel Rounding Feature Explained
Excel offers you several functions which you can use to round. But let’s start with the simplest variant: the ROUND function. Its application is relatively simple: You first enter the number you want to round. The second parameter within the ROUND formula is the number that indicates to how many places Excel should round the value.
=ROUND(number; num_digits)The parameter “number” refers to the value that you want to round. You can either enter a number directly into the formula or a reference to another cell.
With the parameter “num_digits”, you determine from which digit you want to round off. You enter the decimal places with positive values. Accordingly, the value 0 would have no decimal places and therefore generate an integer. If you use negative values, the digits to the left of the point will also be rounded up or down to the nearest decimal place.
=ROUND(22.5172;1) = 22.5
=ROUND(22.5172;0) = 23
=ROUND(22.5172;-1) = 20You can either ROUND using the feature “Insert function ()” or enter the function directly in the formula bar. With the latter method, you need to remember to enter the function with an equals sign (=), otherwise Excel will treat your input as text to be displayed.
You do not have to use a dedicated cell to round a value. Excel also enables nesting functions. This allows you to combine the ROUND function directly with another formula.
=ROUND(AVERAGE(17.251;3.4687;7.2513);2) = 9.32In this example, you create the average of different numbers and round the result. Only the rounded value then appears in the cell.
MROUND: The More Complex Rounding Formula
With the function MROUND, you can round a value to the suitable multiple of a different number. In the formula, you first enter the value you want to round, as with the normal ROUND function. Also, with this method, you can either enter the value directly, refer to a cell, or put another function in its place. As a second parameter, you then enter the value whose multiple is to be rounded.
=MROUND(number, multiple)For example, if you only want to return the results in increments of 25, you can use the MROUND function. The distance to the multiple decides whether it is rounded up or down. Excel will always choose the shortest distance. In the case of an identical distance in both directions, it is rounded up.
=MROUND(136;25) = 125
=MROUND(142;25) = 150If you always want to round minutes up or down to full hours, you can also work with the MROUND function.
=MROUND(A1;60)/60
Simple Rounding: EVEN & ODD
With two very simple rounding methods, you can round to the next even or odd integer. It does not matter if you want to round the number with or without decimal places. As a parameter, only the number to be rounded occurs in the syntax.
=EVEN(number)
=ODD(number)Positive numbers are always rounded up (3 becomes 4); negative numbers always round down (-1 becomes -2).
Adjust Presentation: Rounding Superficially in Excel
You can also format cells in Excel to display only a certain number of decimal places. By right-clicking on the appropriate position, a context menu will appear in which you can then select “Format cells...”. Under the “Number” tab and the “Number” category, you have the option of specifying the number of decimal places desired. For example, if you enter 0, only integers are displayed. The difference to the ROUND function is that rounding by formatting only affects the presentation. Your spreadsheet shows the rounded number, but the background of the cell contains the correct value including decimal places.
The difference shows up at the latest when you continue to calculate with the numbers: If you have the values 2.4, 2.3, and 3.3 in formatted cells without decimal places, you will only see 2, 2, and 3. But now create the sum of these values, and Excel will show 8 because the program still takes into account the decimal places. If you had worked with the ROUND function instead, the result would be 7.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
Related articles
ChinnapongShutterstock Excel: The MATCH Function Explained
To work with large data sets, long tables, and large arrays, Excel offers several features. These facilitate navigation through the mass of values and data. For instance, to quickly find the right cell, you can use MATCH in Excel. The function searches for a value and then specifies the position of the cell. How does it work?
Transposing in Excel – How it Works!
Realigning a table can be a marathon task. Copying or re-typing each cell individually – that takes time. Thankfully, areas can be transposed in Excel. This way, you can convert rows into columns and columns into rows, almost like turning the table on its side. Excel offers a function to do this with paste and even its own array formula.
fizkesShutterstock Faster with Excel: the VLOOKUP function explained
It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,…
How to create graphs in Excel
Long sequences of numbers can be off-putting and rarely provide quick overviews. But by creating a graph in Excel, you can ensure that everyone will immediately understand the relationships and trends you’re presenting. The Microsoft Excel spreadsheet application allows you to create many different types of charts and customize them exactly to your needs.
Ground PictureShutterstock How to calculate time in Excel quickly and easily
You can use the SUM function to quickly add up several values. But if you want to calculate hours in Excel, you first have to adjust the format of the cells. The format has to be correct, otherwise you’ll encounter problems when you add up more than 24 hours. In that case, your total could be missing an entire day. We’ll teach you how to add hours in Excel and avoid common mistakes.
