So far, we have covered percentages in a small portion and we mainly discussed formatting and dealing with them in Excel.
In the text below, we are going to further discuss this topic, and show how to round percentages in Excel.
Round Percentages
Although Excel has a pretty neat feature called Increase Decimal and Decrease Decimal, it does not get us where we want.
Let us suppose we have a random number with seven decimals:
We want to decrease the decimals to two. We will copy and paste the number in the B column, and then go to the Home tab and press Decrease Decimal five times:
We will have the following result:
We can notice that although we have a smaller number of decimals shown in cell B2, we still have the same number in our formula bar.
But what if we wanted to round this number to two decimals in a way that we can see only two decimals in the formula bar? To do this, we have to use the ROUNDDOWN formula.
This formula returns a certain number rounded down to as many places as we want.
Our formula in cell C2 will be:
|
1 |
=ROUNDDOWN(A2,4) |
Meaning that we will round our number to two decimal places:
We can also use a ROUNDUP formula, that rounds percentages up to a certain number of places we want.
We will use it for the same example, and input the following formula in column D2:
|
1 |
=ROUNDUP(A2,6) |
And we will have the following results:
To show the difference between ROUNDUP and ROUNDDOWN, we will use the following example:
Let us say we have a number 86.9% in cell A3. In cell C3 we will input the following formula:
|
1 |
=ROUNDDOWN(A3,2) |
And in cell D3:
|
1 |
=ROUNDUP(A3,2) |
We will have the following results:
So basically, not only that we left our percentage with no decimals, but the different formulas returned different results. In the case of ROUNDDOWN, we got the number 86%, and in the case of ROUNDUP, we got the number 87%.
So this is also something to keep an eye on.
For the final note: if you want to round the percentages to any number of decimal places, you have to use the following formula:
|
1 |
=ROUND(desired cell, N+2) |
For example, if you want to round your number to 4 decimal places (N is now number 4), you should use the following formula:
|
1 |
=ROUND(desired cell,6) |
If you round the percentages, and you get the number that is not formatted as a percentage, click on your cell and then click on the Percentage button in the Home tab:
If, however, your results after rounding are not as expected, i.e. the number is not within the specified decimal place, you can use the Increase Decimal or Decrease Decimal button.