How to calculate percentages in Excel: A complete guide

Excel is a powerful tool for managing and analyzing data. In this article let's show you how to calculate percentages in Excel, so you can take full advantage of this feature. We also explain how to can apply advanced techniques for performing more complex calculations.

Contents of this manual:

How to calculate percentages in Excel

Basics of percentages in Excel

To calculate percentages in Excel, we must first understand what a percentage is and how Excel handles percentages. A percentage is a ratio expressed as a fraction of 100. Excel recognizes percentages as decimal values, so 50% is stored as 0.5 and 75% is stored as 0.75.

 

In Excel, you can calculate percentages in different ways, depending on your needs and available data.

Simple percentage calculations

Let's start with some basic calculations:

 

  • To calculate a percentage

To calculate the percentage of a number, multiply the number by the percentage. In Excel, the formula would look like this:

 

=Getal * Percentage

 

For example, if you want to calculate 20% of 150, enter:

 

=150 * 20%

 

or

 

=150 * 0,2

 

  • Displaying a number as a percentage

To display a number in Excel as a percentage, follow these steps:

 

  1. Select the cell(s) you want to display as a percentage.
  2. Go to the "Start" tab on the Excel menu bar.
  3. Find the 'Number' drop-down menu in the 'Number' section of the ribbon.
  4. Click on the drop-down menu and choose the "Percentage" option.

The selected numbers are now displayed as percentages, with a percentage sign (%) after the number.

Start Number Percentage
Start >> Number >> Percentage

Calculate percentage difference

The percentage difference between two numbers is a common calculation. Here's how to do it in Excel:

 

= (NewGetal - OldGetal) / OldGetal

 

Don't forget to record the result as a percentage.

Formula
Outcome
Outcome

Calculate percentage of a total

To calculate the percentage of a part relative to the total in Excel, use the following formula:

 

= Part / Total

 

For example, if you want to calculate the percentage of a specific number of sales compared to the total number of sales, enter:

 

= NumberSales / TotalSales

 

Don't forget to record the result as a percentage.

Percentage of a total calculate formula
Formula
Percentage of a total calculate outcome
Outcome

Calculating cumulative percentages

Cumulative percentages are often used when analyzing data, for example, to identify trends. Here's how to calculate cumulative percentages in Excel:

 

  1. Sort your data in ascending order.
  2. Calculate the total of all values.
  3. Calculate the cumulative total for each value by adding the current value to the cumulative total of the previous row.
  4. Calculate the cumulative percentage for each value using the formula: = CumulativeTotal / Total

Advanced percentage calculations

In some cases, you may need to perform more advanced calculations, such as weighted averages or composite percentages. Here are some examples of advanced percentage calculations:

 

  • Weighted average

The weighted average takes into account the relative importance of each number in a series. In Excel, you can calculate the weighted average using the following formula:

 

= SOMPRODUCT(Values, Weights) / SOM(Weights)

 

Using this formula, the values are multiplied by their respective weights and then summed. The total is then divided by the sum of the weights to calculate the weighted average.

 

  • Compound percentages

Compound percentages are often used to calculate growth or decline over multiple periods. To calculate compound percentages in Excel, use the following formula:

 

= (1 + Percent1) * (1 + Percent2) * ... * (1 + PercentN) - 1

 

This formula takes into account the growth or decline over each period and calculates the total compound rate for all periods combined.

 

Charts and graphs for percentages

Visualizing percentages in Excel can help with data analysis and interpretation. Here is an example of a Mermaid chart that shows the distribution of percentages:

 

pie
title Distribution of percentages
"Percentage 1": 25
"Percentage 2": 35
"Percentage 3": 15
"Percentage 4": 25

 

To use this diagram in your document, you must first use the Mermaid extension install and then insert the above code.

 

In Excel, you can also use standard graphs and charts to visualize percentages, such as pie charts, bar charts and stacked column charts. To create a chart in Excel, select the data, go to the Insert tab and choose the desired chart type.

 

In this article, we discussed several methods for calculating percentages in Excel. We covered simple percentage calculations, as well as more advanced techniques for calculating percentage difference, cumulative percentages and weighted averages. In addition, we showed how to use charts and graphs to visualize and analyze percentages. By mastering these techniques, you will be able to maximize the power of Excel and effectively manage and analyze your data.

 

Conclusion

In this article, we have covered different ways to calculate percentages in Excel, both for simple and more advanced scenarios. We also highlighted the importance of visualizing percentages using charts and graphs so that you can better understand your data and make more informed decisions. Mastering these methods and techniques will help you improve your skills in Excel and make your work more efficient and effective.

 

It is important to remember that calculating and analyzing percentages is only a small part of what Excel has to offer. There are countless other functions, formulas and techniques you can use to perform even more complex data analysis. By continually expanding your knowledge of Excel, you can increase your productivity and continue to improve your data analysis skills.

Don't want to miss a Blog or News article? Quickly subscribe to the newsletter