Useful Tips

How to round numbers in Excel

Rounding numbers in Excel is necessary for convenient formatting, forecasting and communication.

There are many rounding methods, such as rounding to an integer, rounding up, rounding down, etc. In Excel, we can round numbers with functions. We will consider them in this article.

Rounding numbers in Excel by changing cell format

If you want to round numbers solely for the purpose of presentation, you can change the format of the cell by following these steps:

  • Click on a cell with a numerical value,
  • Open the cell formatting menu using the keyboard shortcut CTRL + 1 or click on the cell with the right button and select “ Cell format “,
  • In the popup window “ Cell format Go to tab Number “,
  • In section " Number formats “Click on item” Numerical “,
  • Find “ Number of decimal places ”And indicate the exact number of decimal places you want to leave,
  • Click on the “ OK ”To save formatting settings.

Important! This method only visually rounds the number, without “cutting” the number after the decimal point.

Round a number in by changing the cell format

If you want to round numbers in Excel solely for visual presentation, you can change the format of the cell by following these steps:

  1. Select the cell with the numbers you want to round.
  2. Open the "Format Cells" dialog box by pressing Ctrl + 1 or right-click on a cell and select "Format Cells" in the context menu.

How to round a number in Excel - Cell Format
  1. In the "Number" tab, select "Numeric" or "Money" format and enter the number of decimal places that you want to display in the "Number of decimal places" field. Preview how will rounded number appears in the Sample section.
  2. Click OK to save the changes and close the dialog box.

How to round a number in Excel - Round a number by changing the cell format

Note! This method changes the display format without changing the actual value stored in the cell. If you refer to this cell in any formulas, then all calculations will use the original number without rounding. If you need to actually round the number in a cell, then use Excel rounding functions.

How to round a number with the ROUND function

ROUND is the main function of rounding numbers in Excel, which rounds a number to a specified number of characters.

Syntax:

Number is any real number that you want to round. This can be a number or a cell reference.

Bit_Number is the number of digits to round the number. You can specify a positive or negative value in this argument:

  • If the number of digits is greater than 0, the number is rounded to the specified number of decimal places. For example, = ROUND (17.25, 1) rounds the number 17.25 to 17.3.

To round the number to tenths, in argument number_bit the value is equal to 1.

How to round a number in Excel - How to round a number to thousandths
  • If the number of digits is less than 0, all decimal digits are deleted, and the number is rounded to the left of the decimal point (to tenths, to hundreds, to thousands, etc.). For example, = ROUND (17.25, -1) rounds 17.25 to the nearest multiple of 10 and returns 20 as a result.
  • If the number of digits is 0, the number is rounded to the nearest integer (without decimals). For example, = ROUND (17.25, 0) rounds 17.25 to 17.

The following image shows some examples. how to round a number in Excel in the ROUND formula:

How to round a number upward with the ROUNDUP function

The ROUNDUP function rounds a number up (from 0) to a specified number of digits.

Syntax:

Number is the number to round.

Bit_Number is the number of characters to which you want to round the number. You can specify both positive and negative numbers in this argument, and it works as the number of bits of the ROUND function described above, except that the number is always rounded up.

How to round a number downward with the ROUNDDOWN function

The ROUNDDOWN function in Excel does the opposite of what ROUNDDOWN does, that is, rounds a number down.

Syntax:

Number is the number to be rounded.

Bit_Number is the number of characters to which you want to round the number. Works as an argument number_digits of the ROUND function, except that the number is always rounded down.

The following image demonstrates how to round a number in Excel downward function of the ROUNDDOWN function in action.

The content of the article

The operation of rounding numbers in Excel is quite simple, so it will not cause special difficulties even for a beginner. At the same time, like most other operations, it can be applied to a single number or to an entire array of desired numbers.

Select an array to round

In order to make the program understand which parts of the database the rounding operation should be extended to, it is necessary to select the part of the array to be implemented. You can do this by left-clicking on the desired cell and stretching the selection field to the required number of cells. However, in the process, it may become clear that the array to be rounded is discrete, that is, discontinuous. One of the most obvious, but also the most time-consuming options in this case will be the alternate rounding of data in each part of the array. You can do it easier: during selection, press the keyboard and hold the Ctrl key. This will allow you to select discontinuous data arrays with the mouse, in relation to which it will subsequently be possible to carry out a general operation. Finally, the third way is to set the data array for rounding using a formula.

Fraction rounding operation

In order to round the selected numbers, click on one of the cells in the area subjected to selection with the left mouse button. This action will cause a menu to appear, one of the items of which will be "Format Cells" - it should be selected. In this menu, in turn, you will see several tabs: the parameters you need are located on the "Numbers" tab. The specified section allows you to select the type of numbers, which is located in the selected cells. In order to carry out the rounding operation, it is necessary to select the format designated as “Numeric” from the proposed list. Selecting this format will bring up a menu with additional settings. One of the items on this menu will be the number of decimal places that you can choose as you wish. At the same time, the number itself recorded in each of the rounded cells will not change as a result of this operation, since only the format of its image will change. Thus, you can always return to the original format in the same way or choose a different type of rounding.

Rounding fractions

The rounding of fractional numbers allows the user to set the specified number of decimal places in decimal, which will be displayed in the cell regardless of the actual number of these characters entered in the program. In order to carry out the rounding operation, it is necessary to use the left mouse button to select a cell or a numerical array in relation to which it should be performed. If you want to round two or more numbers or arrays located in different parts of the table, you can select them by holding down the CTRL key.

The operation of rounding a fractional number can be done by selecting the “Format” tab in the upper menu and left-clicking on the “Cell” position. This action will bring up the menu necessary for the operation. In addition, you can go to this menu in another way: by right-clicking on a selected section of the table and selecting the "Cell format" item.

In this menu, select the number format of the cell and in the special window mark the required number of decimal places. So, for example, if this indicator was set in the form of the number 2, then the initial fraction of the form 1.58165874, subjected to rounding, will take the form 1.58.

Rounding integers

  • Rounding a number

Tip 2: How to round numbers in Excel

The content of the article

Rounding is a mathematical operation that allows you to reduce the number of characters used to fix a particular number, due to some decrease in its accuracy. In Excel, rounding can be done automatically. Moreover, it allows you to round off both fractional and integer numbers.

Rounding fractions

The rounding of fractional numbers allows the user to set the specified number of decimal places in decimal, which will be displayed in the cell regardless of the actual number of these characters entered in the program. In order to carry out the rounding operation, it is necessary to use the left mouse button to select a cell or a numerical array in relation to which it should be performed. If you want to round two or more numbers or arrays located in different parts of the table, you can select them by holding down the CTRL key.

The operation of rounding a fractional number can be done by selecting the “Format” tab in the upper menu and left-clicking on the “Cell” position. This action will bring up the menu necessary for the operation. In addition, you can go to this menu in another way: by right-clicking on a selected section of the table and selecting the "Cell format" item.

In this menu, select the number format of the cell and in the special window mark the required number of decimal places. So, for example, if this indicator was set in the form of the number 2, then the initial fraction of the form 1.58165874, subjected to rounding, will take the form 1.58.

Rounding integers

In addition, Excel also allows you to perform rounding operations on integers, making them more readable. This can be done by resorting to using a special function, designated ROUND. It should be borne in mind that the same method of performing the necessary action can be used with respect to fractions.

The specified function has two arguments. The first of them is a cell or a numerical array, in relation to which a rounding operation is necessary. The second argument to this function is the number of digits to round. Positive digits are used to round off fractional numbers, and the digit denoting the digit in this case is equal to the number of decimal places. Zero digit will cause rounding of the number to an integer value, and negative - rounding to a certain digit. For example, a digit equal to -1 will cause rounding to tens, -2 will round the number to hundreds, and so on.

As a result, the function used to carry out this operation will look as follows. For example, there is a need to round the number 101, located in cell A3, to hundreds. In this case, write the function as follows: = ROUND (A2, -2). The use of this function will ensure that the specified number is displayed in cell A3 as 100.

Storing numbers in Excel memory

All numbers that Microsoft Excel works with are divided into exact and approximate numbers. Numbers up to 15 bits are stored in the memory, and are displayed until the discharge that the user himself indicates. But, at the same time, all calculations are performed according to the data stored in the memory, and not displayed on the monitor.

Using the rounding operation, Microsoft Excel discards a certain number of decimal places. Excel uses the generally accepted method of rounding when a number less than 5 is rounded down, and greater than or equal to 5 is up.

Rounding with Ribbon Buttons

The easiest way to change the rounding of a number is to select a cell or group of cells, and in the "Home" tab, click on the ribbon "Increase the capacity" or "Decrease the capacity" button. Both buttons are located in the “Number” tool block. In this case, only the displayed number will be rounded, but for calculations, if necessary, up to 15 digits of numbers will be involved.

When you click on the button “Increase bit depth”, the number of entered decimal places increases by one.

When you click on the button "Decrease bit depth" the number of digits after the decimal point is reduced by one.

Rounding through cell format

You can also set rounding using the cell format settings. To do this, select the range of cells on the sheet, right-click, and select the "Format Cells" item in the menu that appears.

In the window that opens, the settings for the format of the cells you need to go to the tab "Number". If the data format is not numerical, then you need to select the numerical format, otherwise you will not be able to adjust rounding. In the central part of the window near the inscription “Number of decimal places” we simply indicate with the number the number of characters that we want to see when rounding. After that, click on the “OK” button.

Calculation accuracy setting

If in previous cases, the set parameters affected only the external display of data, and more accurate indicators (up to 15 digits) were used in the calculations, now we will tell you how to change the accuracy of the calculations itself.

To do this, go to the “File” tab. Next, we move to the "Parameters" section.

The Excel options window opens. In this window, go to the "Advanced" section. We are looking for a settings block called "When recounting this book." The settings in this side apply to not a single sheet, but to the whole book as a whole, that is, to the entire file. We put a tick in front of the "Set accuracy as on screen" parameter. Click on the “OK” button located in the lower left corner of the window.

Now, when calculating the data, the displayed value of the number on the screen will be taken into account, and not the one stored in Excel memory. Setting the displayed number can be done in any of two ways, which we talked about above.

Function Application

If you want to change the rounding value when calculating with respect to one or several cells, but don’t want to lower the accuracy of calculations as a whole for the document, then in this case, it is best to take advantage of the opportunities that the ROUND function provides and its various variations, as well as some other features.

Among the main functions that regulate rounding, the following should be highlighted:

  • ROUND - rounds to the specified number of decimal places, in accordance with generally accepted rounding rules,
  • ROUND - rounds to the nearest number up modulo,
  • ROUNDDOWN - rounds to the nearest number down modulo,
  • ROUND - rounds a number with a given accuracy,
  • OKRVERH - rounds a number with a given accuracy up modulo,
  • OKRVNIZ - rounds the number down modulo with a given accuracy,
  • OTDB - rounds data to an integer,
  • EVEN - rounds data to the nearest even number,
  • Odd - rounds data to the nearest odd number.

For the functions ROUND, ROUND UP and ROUND DOWN, the following input format: “Name of the function (number, number of bits). That is, if, for example, you want to round the number 2.56896 to three digits, then use the ROUND function (2.56896.3). The output is the number 2.569.

For the functions OKRUGLT, OKRVVERH and OKRVNIZ the following rounding formula is applied: “Name of function (number, accuracy)”. For example, to round the number 11 to the nearest multiple of 2, we introduce the function ROUND (11,2). The output is the number 12.

Functions SELECT, EVEN and Odd use the following format: "Name of function (number)". In order to round the number 17 to the nearest even, we use the NUMBER function (17). We get the number 18.

You can enter a function both in the cell and in the function line, after having selected the cell in which it will be located. Each function must be preceded by a “=” sign.

There is a slightly different way to introduce rounding functions. It is especially convenient to use when there is a table with values ​​that need to be converted to rounded numbers in a separate column.

To do this, go to the “Formulas” tab. Click on the button “Mathematical”. Next, in the list that opens, select the desired function, for example ROUND.

After that, the function arguments window opens. In the "Number" field, you can enter the number manually, but if we want to automatically round the data of the entire table, then click on the button to the right of the data entry window.

The function argument window minimizes. Now we need to click on the very top cell of the column whose data we are going to round. After the value is entered in the window, click on the button to the right of this value.

The function arguments window opens again. In the field “Number of digits” we write the bit depth, to which we need to reduce fractions. After that, click on the “OK” button.

As you can see, the number is rounded. In order to round off all other data of the desired column in the same way, move the cursor to the lower right corner of the cell with a rounded value, click on the left mouse button, and drag it down to the end of the table.

After that, all values ​​in the desired column will be rounded.

Как видим, существуют два основных способа округлить видимое отображение числа: с помощью кнопки на ленте, и путем изменения параметров формата ячеек. Кроме того, можно изменить и округление реально рассчитываемых данных. Это также можно сделать двумя способами: изменением настроек книги в целом, или путем применения специальных функций. The choice of a specific method depends on whether you intend to apply this type of rounding for all data in the file, or only for a certain range of cells.

Thank the author, share the article on social networks.

Function Basics

  • if the argument bit_numbergreater than “0”, the number is rounded to the specified number of decimal places. For example, = ROUND (500.51.1) will return “500.5”,
  • if the argument bit_number equals “0”, then the number is rounded to the nearest integer. For example, = ROUND (500.51.0) will return “501”,
  • if the argument bit_numberless than “0”, the number is rounded to the left of the decimal value. For example, = ROUND (500.51, -1) will return “501”.

Function ROUND It works on the basis of a mathematical rule by which it determines whether the number should be rounded up or down:

  • If the number after the decimal point is from 0 to 4, then rounding will be done downward,
  • If the number after the decimal point is from 5 to 9, then the function will round up.

The screenshot below shows examples of using the function:

Round up in Excel using the ROUND UP function

Function ROUND UP rounds a number up, regardless of what number is after the decimal point.

= ROUNDUP (number, number_digits)

Function Arguments

  • number - the numerical value you want to round,
  • bit_number Is the value of the decimal place to which you want to round the first argument of the function.

Examples of using the function below:

Function Arguments

  • number - the numerical value you want to round,
  • bit_number Is the value of the decimal place to which you want to round the first argument of the function.

Function Basics

  • if the argument bit_numbergreater than “0”, the number is rounded to the specified number of decimal places. For example, = ROUND (500.51.1) will return “500.5”,
  • if the argument bit_number equals “0”, then the number is rounded to the nearest integer. For example, = ROUND (500.51.0) will return “501”,
  • if the argument bit_numberless than “0”, the number is rounded to the left of the decimal value. For example, = ROUND (500.51, -1) will return “501”.

Function ROUND It works on the basis of a mathematical rule by which it determines whether the number should be rounded up or down:

  • If the number after the decimal point is from 0 to 4, then rounding will be done downward,
  • If the number after the decimal point is from 5 to 9, then the function will round up.

The screenshot below shows examples of using the function:

Round up in Excel using the ROUND UP function

Function ROUND UP rounds a number up, regardless of what number is after the decimal point.

= ROUNDUP (number, number_digits)

Function Arguments

  • number - the numerical value you want to round,
  • bit_number Is the value of the decimal place to which you want to round the first argument of the function.

Examples of using the function below: