Sheets AcademySheets Academy
Premium
Formulas

Percentages and rounding

A monthly sales report is made up almost entirely of percentages. It needs to answer three questions:

  • what part of the revenue each manager brought in;
  • by what percentage sales grew over the month;
  • what an item will cost after a markup or a discount.

A short formula answers each one. And when cents show up in prices, you'll need rounding too.

Share of a total

A share is the part divided by the whole. If a manager brought in $120,000 out of $400,000, their share is 120,000 ÷ 400,000 = 0.3, which is 30%.

Total revenue: $400,000
Click the pieces of the bar.
Emma brought in $120,000 out of $400,000.
Share = part ÷ whole = 120,000 ÷ 400,000 = 0.3
In Percentage format, the same fraction looks like 30%.
All the shares together: 0.3 + 0.2 + 0.15 + 0.35 = 1, that is, 100%.

In a table, the share is calculated with a formula. Below, it was written for Emma, =B2/$B$6, and filled down. The reference to the total is locked with dollar signs. Otherwise it would shift to the empty cells B7, B8 and beyond when filled.

fx
A
B
C
D
1
2
3
4
5
6
7
8
Manager
Revenue, $
Share
Emma
120000
Ben
80000
Olivia
60000
Leo
140000
Total
Sheet1
Click any share: the borders show that the formula divides its row's revenue by the total in B6.

The formula gives the fraction 0.3, and the cell shows 30% thanks to the Percentage format. Here's how to set it: select the cells with the shares, then on the Home tab choose Percentage in the Number Format list. The format multiplies the number by 100 only on the screen. The cell still stores 0.3.

Growth

Growth shows by what percentage a number changed compared to the previous one. Revenue grew from $200,000 to $230,000, that is, by $30,000. The percentage is calculated from where you started: 30,000 ÷ 200,000 = 0.15, or 15%.

In the formula, you subtract the old value from the new one and divide by the old one:

=(B3-B2)/B2

The parentheses are required. Without them, Excel first divides B2 by B2 and only then subtracts. If revenue went down, the growth comes out negative.

fx
A
B
C
D
1
2
3
4
5
6
7
Month
Revenue, $
Growth
January
200000
February
230000
March
207000
April
248400
Sheet1
C3 shows February's growth over January: 15%. Fill the formula down to C5: in March, revenue fell by 10%.

Adding or subtracting a percentage

A price with a 20% markup is the whole price plus 20% of it, that is, 120% of the price. For Excel, 100% is 1 and 20% is 0.2. So the price needs to be multiplied by 1 + 0.2. A price with sales tax is calculated the same way.

The percentage itself doesn't go into the formula. It's kept in a separate cell, for example F1. If the markup changes, you only need to fix one cell, and all the prices recalculate. This formula calculates the price with the markup:

=B2*(1+$F$1)

The 1 here stands for the whole price, and the percentage from F1 is added to it. The parentheses make Excel add 1 and the percentage first and only then multiply. The dollar signs keep F1 in place when the formula is filled down: the items have different prices, but the markup is the same.

A discount is calculated the same way, only the percentage is subtracted: =B2*(1-$F$1). If F1 holds a 15% discount, the price is multiplied by 0.85.

You don't add a percentage to a price directly. The formula =B2+$F$1 adds the number 0.2 to the price, not 20%: for Excel, 20% is just 0.2.

fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
Item
Price, $
With markup, $
Markup
0,2
Headphones
80
Speaker
120
Charger
25
Cable
10
Sheet1
Fill the formula from C2 down to C5. Then type 25% in F1: all the prices recalculate.

A markup or a discount of any size comes down to a single multiplier:

+20%
$80×1.2=$96
A multiplier of 1.2 means 120% of the old price: 100% + 20%. In the formula, that's 1 plus the percentage from F1.
The formula in Excel when F1 holds 20%
=B2*(1+$F$1)

Rounding

A format with two decimal places doesn't round a number. It only hides the extra digits. Excel calculates with the real values. That's why a total sometimes differs by a cent from the sum of the prices you see on the screen.

To round the value itself, there's the ROUND function. It takes two arguments in parentheses, separated by a comma: first the number or formula to round, then how many decimal places to keep. Two places keep the cents, and zero rounds to whole dollars.

Sometimes you need to round only up: 41 mugs packed 12 to a box need 4 boxes, even though 41/12 gives 3.4. For that, there's ROUNDUP with the same two arguments: =ROUNDUP(41/12,0) returns 4.

If the sales tax rate is in F1, this formula calculates the price with tax to the cent:

=ROUND(B2*(1+$F$1),2)

On the sheet below, the prices with tax are calculated without rounding, and the total doesn't match the screen. Find where the cent is hiding and fix it:

Select C2 and look below the sheet to see what it stores
fx
A
B
C
D
E
F
G
1
2
3
4
5
6
7
Item
Price, $
With tax, $
Tax
0,08
Coffee
12,69
Tea
4,69
Sugar
3,19
Total
Sheet1
Add up the prices on the screen0.00
Total in C5
Select a price with tax to see what the cell stores.

Key points

  • A share is the part divided by the whole: =B2/$B$6. The total is locked with $ signs, and the Percentage format shows 0.3 as 30%.
  • Growth is the difference between the new and old values, divided by the old one. The parentheses are required.
  • The percentage is kept in a separate cell. A price with a markup is the price multiplied by 1 plus the percentage, and a price with a discount is the price multiplied by 1 minus the percentage.
  • A format only hides digits. The ROUND function changes the value itself, and the total matches the screen.