How to Calculate Percentages in Excel

When doing any kind of data analysis, it’s important to be able to pull in not just raw data but also to show percentages. From a period-over-period percent change to how much an item represents of a total, showing a percentage can give readers multiple different viewpoints. Below, I’ll show you how to calculate percentages in Excel and to give your data more context.

In this example, I’m going to use data from Netflix’s most recent quarterly results. The streaming giant always releases its numbers in a friendly Excel format, making it easy to analyze the data. Here’s what its income statement tab looks like, unchanged for the second quarter of fiscal 2020, which includes previous periods:

Showing period-over-period changes

Netflix’s numbers look impressive — $6.1 billion in revenue for the quarter ending June 30, 2020. However, that number on its own may not be very helpful. One way to add some context is to calculate the percentage change to show the increase or decrease from a previous period, aka its rate of growth.

I’ll add a column next to those quarterly results and add a formula that shows the percentage difference from the previous quarter (ending March 31, 2020). To calculate the percent change, all I need is to take the difference and divide it by the old number, or base amount. A good way to remember this is: (new-old)/old.

In this example, column Q contains the quarterly results for June 30 and column P is the previous period. And the revenue is in row nine. The formula for the first item looks as follows:

=(Q9-P9)/P9

Where Q9 is the new total, while P9 is the old number. This gives me the following:

The $0 isn’t really helpful here, and it’s also not a dollar amount. Excel’s just defaulted it to that format based on the other numbers. To properly show it as a percent change, I need to change it to a % format. It’s as simple as selecting the entire column and clicking on the % sign on the Number tab:

That will now give me the following results, after centering the column:

However, this still may not be ideal. If I want a bit more detail, such as to show multiple decimal places, you’ll again want to go back to the format section and select the item to add decimal places:

Clicking on this button twice will now give me a couple more decimal places:

Now, with my percentage change looking correct, I can copy the formula down for the rest of the items:

Showing the percentage of a base amount, or grand total

Another way you may want to show a percentage is how much an item makes up of a total. One common way to analyze financial statement is to look at items as a percentage of revenue. A company’s profit margin, for instance, takes its total profit and divides it by revenue to determine what percentage of its top line makes it through to the bottom line.

How to calculate percentages in Excel when just looking at how much an item makes up of a grand total is an easier process. In this example, the calculation just takes the current item and divides it by revenue. The key is just freezing the denominator, which in this case is revenue. Here’s how the formula looks like:

Using the % of revenue analysis, it’s easy to see that operating income was 22% of revenue and Netflix’s profit margin was 11.7%. Replicating these formulas for other periods can help compare multiple periods to see the % of revenue trends. Here’s how the current quarter looks against the previous one when looking at the percent of revenue:

Compared to the earlier quarter, Q1, the profit margin becomes a bit less impressive in Q2 as it’s declined from the previous period. Despite a stronger overall Q2 performance, a higher tax bill led to a smaller overall profit margin than in Q1.

As you can see, by adding percentages to your analysis you can create very different viewpoints and add a lot more context to the numbers.

If you liked this post on how to calculate percentages in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.