- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Mobile.
In this post, you will learn how all about rounding a number to your preferred number of decimal places instead of having a number with seemingly endless amount of decimal places.
Firstly, you might have a preference to only seek to have the required decimal places in cells since they result in ###### symbols displaying out of the blue. On the other hand, you may be in the position of accepting some degree of accuracy, in which situation you can then modify the cell format to acquire your desired number of decimal places.
Alternatively, if you prefer to round to the closest significant unit, like thousands, hundreds, tens, or ones, employ a function within a formula.
By using a button:
- Pick your ideal cells that you intend to format.
- On the Home tab, select Increase Decimal or Decrease Decimal to display greater or fewer digits following the decimal point.
By applying a built-in number format:
- Go to the Home tab, within the Number group. Then, pick the arrow beside the list of number formats, and then choose More Number Formats.
- In the Category list, according to your specific data type, select Currency, Accounting, Percentage, or Scientific.
- In the Decimal places box, state your desired number of decimal places that you prefer to present.
By using a function in a formula:
Round a number to your sought number of digits by applying the ROUND function. This function solely contains two arguments (arguments are segments of data which the formula must include to operate).
- The first argument is the number you are looking to round, This can either comprise a cell reference or a number.
- The second argument refers to your preferred number of digits you seek to specifically round the number to.
Suppose that cell A1 has 823.7825. To round the number to the nearest:
Rounding a number to the nearest thousands
- Input =ROUND(A1,-3) which equals 1,000
- 823.7825 is nearer to 1,000 than to 0 (0 is a multiple of 1,000 )
- Set a negative number here since your intention is to get the rounding to occur to the left of the decimal point. The exact result is reflected in the following two formulas that round to hundreds and tens.
Rounding a number to the nearest hundreds
- Enter =ROUND(A1,-2) which equals 800
- 800 is closer to 823.7825 than to 900. We’re confident you get the gist at this point.
Rounding a number to the nearest tens
- Input =ROUND(A1,-1) which equals 820
Rounding a number to the nearest ones
- State =ROUND(A1,0) which equals 824
- Add a zero to round the number to the nearest single digit.
Rounding a number to the nearest tenths
- Enter =ROUND(A1,1) which equals 823.8
- Apply a positive number in this position to round the number to the number of decimal points you define. The same principle is relevant to the upcoming two formulas that round to hundredths and thousandths.
Rounding a number to the nearest hundredths
- Write =ROUND(A1,2) which equals 823.78
Rounding a number to the nearest thousandths
- State = ROUND(A1,3) which equals 823.783
To round a number up, you simply use the ROUNDUP function. It works virtually the exact way as ROUND, with the exception that it invariably rounds a number up. For example, if you want to round 3.2 up to zero decimal places:
- =ROUNDUP(3.2,0) which equals 4
Conversely, to round a number down, you use the ROUNDDOWN function. It has the same functioning as ROUND, however it differs in that it constantly rounds a number down. For instance, if you want to round down 3.14159 to three decimal places:
- =ROUNDDOWN(3.14159,3) which equals 3.141
Specify a fixed decimal point for numbers
You can define a standard decimal point for numbers within Excel Options.
- Press Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).
- In the Advanced category, below Editing options, choose the Automatically insert a decimal point checkbox.
- In the Places box, type a positive number for digits directly to the right-hand side of the decimal point or conversely, a negative number for digits on the left-hand side of the decimal point. Note: For example, if you state 3 in the Places box and then enter 2834 in a cell, the value will be 2.834. If you input -3 in the Places box and then write 283 in a cell, the value will be 283000.
- Press OK. The Fixed decimal indicator emerges in the status bar.
- On the worksheet, select a cell, and then enter your preferred number. Note: The data that you entered prior to you clicking the Fixed decimal checkbox is unaffected.
Tips and techniques
- To momentarily bypass the fixed decimal option, enter a decimal point once you write the number.
- To remove decimal points from numbers that you already entered with fixed decimals, do the following:
- Firstly, click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).
- Secondly, in the Advanced category, under Editing options, clear the Automatically insert a decimal point checkbox.
- Thirdly, in a blank cell, enter a number like 10, 100, or 1,000. This is according to number of decimal places that you intend to erase. For example, input 100 in the cell if the numbers include two decimal places and you aim to change them to whole numbers.
- Fourthly, on the Home tab, in the Clipboard group, press Copy or press CTRL+C.
- Within the worksheet, pick the cells which include the numbers with decimal places that you prefer to adjust.
- On the Home tab, in the Clipboard group, select the arrow under Paste, and then choose Paste Special.
- In the Paste Special dialogue box, below Operation, pick Multiply.