Excel round to 2 decimal places (1 decimal place, 0 00 show 0, if 0 then blank or -)
If there are multiple decimal places in the calculation results in Excel, we can round to 1 or 2 decimal places.
Chapters:
0:00 Excel round to 1 decimal place
0:55 Excel round to 2 decimal places
1:26 Excel 0.00 show 0
1:55 Excel if 0 then blank or -
Description:
Excel round to 2 decimal places (1 decimal place, 0.00 show 0, if 0 then blank or -)
If there are decimal places in the calculation results in Excel, the default will round to multiple decimal places, and we can round to 1 or 2 decimal places, so we need to re-take the number of decimal places for the calculation results, but the problem arises, some results are integers, and then it is obviously not appropriate to round to the decimals, so it is necessary to round to the decimal when there is a decimal place, and only keep the integers when the decimal point is 0. In addition, when some cells are 0, if 0 is not displayed or displayed as a dash (-). Some of them can be implemented in custom formats, and some can be implemented in combination with formulas.
One. Excel round to 1 decimal place or 2 decimal places, and 0 behind the decimal point is not displayed, only integers are retained
1.Excel round to 1 decimal place
Select the cell we want to set, press Ctrl + 1 on the keyboard to open the "Format Cells" window, select the "Number" tab, select "General" on the left, click "OK", click E2, change the average formula =AVERAGE(B2:D2) to =ROUND(AVERAGE((B2:D2),1), press "Enter" on the keyboard, the value of E2 has been set to round to 1 decimal place, and move the pointer to the cell fill handle of E2 in the lower right corner, hold down the left button and drag down, then all the cells it passes through are set to round one decimal place, and the original 87.0 becomes 87.
2. Excel round to 2 decimal places
A. The method is the same as rounding to one decimal place, first set the format of cell to "General", then change the formula to =ROUND(AVERAGE(B2:D2),2), press "Enter"on the keyboard, then the numbers of E2 is set to round to two decimal places.
B. If you want to round to three or more decimal places, you only need to change the second parameter of ROUND in the formula, for example, round to three decimal places, change 2 to 3, that is, =ROUND(AVERAGE(B2:D2),3).
3.Explanation of the formula =ROUND(AVERAGE(B2:D2),1)
The formula consists of two functions, ROUND and AVERAGE, ROUND is the function of rounding the number of decimal places, the expression of the ROUND function is: ROUND(number, num_digits), the formula is averaged first, and then one decimal place is rounded.
Two. Excel 0.00 show 0
1. If there is a set of data to round to two decimal places, if they are 0.00, 0s will be displayed. Select the cells we want to round to two decimal places, press Ctrl + 1 to open the "Format Cells" window, select the "Number" tab, select "Custom" on the left, and enter 0.00;-0.00;0 under "Type", click "OK", then round to two decimal places if they are not 0s behind the decimal point, and only 0s will be retained if they are 0s before and behind the decimal point.
2. If you want to round to 3 decimal places, just put 0.00;-0.00; 0 to 0.000;-0.000; 0, and so on.
Three. Excel if 0 then blank (Excel 0 disappears, How to stop 0 disappearing in Excel, How to stop the 0 disappearing in Excel) or a dash (-)
1. Excel if 0 then blank
Press Ctrl + F on the keyboard to open the "Find and Replace" window, enter 0 to the right of "Find What" to find, click "Options" to expand, select "By Columns" for "Search", select "Values" for "Look in", check "Match entire Cell contents", click "Find All", press Ctrl + A on the keyboard to select all 0s, close the "Find and Replace" window, press Ctrl + 1 to open the "Format Cells" window, select the :Number" tab, select "Custom" on the left, and on the right Enter [=0]"" under "Type" and click "OK", all 0 are not displayed.
2. 0 is displayed as a dash (-)
A. The operation method is the same as setting "Excel if 0 then blank", just change [=0]"" to [=0]"-". After clicking "OK", all 0s in the table are displayed as dashes (-).
B. If 0 is to be displayed as other symbols, just change the "-" in [=0]"-" to the corresponding symbol, for example, if it is *, it will be changed to [=0]"*".
Watch video Excel round to 2 decimal places (1 decimal place, 0 00 show 0, if 0 then blank or -) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 19 January 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 302 once and liked it 1 people.