How to Calculate a Percentage Formula in Excel

Calculating percentages is a fundamental aspect of working with data in Excel. Whether you’re managing budgets, analysing data trends, or preparing reports, understanding how to calculate and use percentages will greatly enhance your productivity and accuracy. In this blog, we’ll explore how to calculate percentages in Excel, provide step-by-step instructions, and share examples to make the concepts easy to understand and apply. 

Introduction to Percentages in Excel

A percentage is a way of expressing a number as a fraction of 100. It’s often denoted using the percent sign (%). In Excel, you can format decimal numbers as percentage values to make them easier to interpret and work with. For example, 50% is equivalent to 50/100 or 0.5. In Excel, calculating percentages is a straightforward process that can be done using simple formulas. 

Percentage of Total

Often, you need to calculate the percentage that each value contributes to a total using an Excel formula. Finding out what percentage of your salary your rent takes up is a common calculation in personal budgeting. For instance, you might want to calculate the percentage of monthly sales that each product contributes to the total sales. 

Example: 

Assume your salary is £2,500, and your rent is £500. Here’s how to find out what percentage of your salary your rent takes up. 

Entering the formula  

Completed Excel percentage formula 

 Step-by-Step: 

1. Click on the cell where you require the answer (B10 in the above example). 
2. Type = 
3. Click on the rent figure (B6 in the example above). This cell should be the figure you want to display as a percentage. 
4. Press the forward slash to indicate division. 
5. Click on the salary figure (B3 in the example above). This cell should be the figure you are trying to find the percentage of. 
6. Press Enter or click on the tick. 
7. The answer will be displayed as a decimal. 

Display the number as a Percentage: 

1. Click on the cell you wish to change. 
2. Click on the percentage icon in the Number section of the Home tab on the Ribbon. 

Displaying numbers as percentage values helps in clearly understanding the proportion of the part to the whole. 

Tip: 

Use simple round figures to make it easier to check the formula works as expected.  You can then replace them with the figures you need to use.   

Percentage Increase/Decrease

Calculating the percentage increase or decrease between two numbers is a common task in data analysis. To calculate percentage growth, the formula to calculate percentage increase is: 

MATHS: 

Percentage Increase=((New Value−Old Value​)/Old Value) x 100 

EXCEL: 

Percentage Increase=(New Value−Old Value​)/Old Value 

Then format as a percentage using the % button on the Ribbon 

Note: although the formula is the same in Excel, the format applies the x 100 and should not be included in Excel. 

In the first example able.  The £500 rent is 0.2 of the £2,500 salary, but becomes 20% when formatted. 

Example: 

Assume you have the sales data for two years. The sales in 2022 were 500, and in 2023 they increased to 600. 

If the new value is less than the old value, the result will be a negative percentage, indicating a decrease. 

Step-by-Step: 

1. Enter the old value (2022 sales) in A2: 500 
2. Enter the new value (2023 sales) in B2: 600 

To calculate the percentage increase: 

1. In cell C2, enter the formula: 

= (B2 – A2) / A2 

Don’t forget the brackets! 

2. Press Enter.  The result will be 0.2

3. Format C2 to a % 

4.  To calculate percentage decrease, the formula is the same but will yield a negative result if the new value is less than the old value. 

Compound Percentage Growth Rate

Compound Annual Growth Rate (CAGR) is useful for calculating the growth of investments over multiple periods. The formula for CAGR is: 

MATHS: 

CAGR= (End Value/Start Value​) (1/Number of Periods ​)− 1 

EXCEL: 

CAGR= (End Value/Start Value​)^(1/Number of Periods ​)− 1 

In case the Maths above is not clear: in maths we superscript raise to the power of, in Excel we use the operator ^ 

This formula is used to calculate percentage growth over multiple periods, providing insights into the growth rate of investments. 

Example: 

Assume an investment grows from £1000 to £2000 over 5 years. 

Step-by-Step: 

1. Enter the start value in A2: 1000 
2. Enter the end value in B2: 2000 
3. Enter the number of periods (years) in C2: 5 

To calculate the CAGR: 

4. In cell D2, enter the formula: 

= (B2 / A2)^(1 / C2) – 1 

5. Press Enter. The result will be 0.1487 or 14.87%, indicating the annual growth rate of the investment.

6. To display this as a percentage, format the cell as described earlier 

 Tips and Tricks 

• Absolute References: Use absolute references (e.g., $B$4) when referring to a total or fixed value in formulas to make copying formulas easier. 

• Formatting: Use the Percentage Style button to format cells quickly as percentages. Remember you can also increase/decrease the number of decimal places 

• Data Validation: Ensure your data is clean and free from errors before performing percentage calculations. 

• Visualisation: Use charts to visualise percentage data. Pie charts are particularly useful for showing parts of a whole. 

• Excel Functions: Explore Excel functions like `PERCENTILE`, `PERCENTRANK`, and `PERCENTILE.EXC` for advanced percentage calculations. 

Calculating percentages in Excel is an essential skill for data analysis, financial planning, and many other tasks. By mastering basic and advanced Excel formulas, you can analyse data more effectively and make informed decisions. Practice these techniques with your data, and you’ll become proficient in no time. 

Ready to enhance your Excel skills? Enquire about our Microsoft Excel training courses today.