Excel Percentage Formula: The Best Introduction

Percentages are used in many areas of our life. Whether it is calculating your income tax or calculating how much discount you are getting on your product. Excel makes this task easier by giving you the option to apply formulas that allow you to calculate percentages directly in your spreadsheets. By going through this tutorial, you will understand a few effective ways for calculating the percentage in Excel.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Percentage Basics

The word “percent” is of Latin origin which means “by a hundred”. A percentage is calculated by dividing the numerator of a fraction by the denominator and multiplying the result by 100. The basic formula to calculate the percentage is :

Percentage  = (Part / Whole)*100

Excel Percentage Formula and Formatting

The formula to calculate the percentage in an Excel worksheet is:

Part / Total = Percentage

When calculating the percent in Excel, you do not have to multiply the fraction by 100. The percentage format option takes care of it.

Suppose you have an Excel sheet with your marks in different subjects. To find the percentage of marks scored, follow these steps:

  • In cell H2, enter the formula =F2/G2 and copy it down to the below cells.

1-percentage-formula

  • Go to the Home Tab > Number Group and click on the Percent Style button.

2

Now you can see your data in terms of percentages.

3-percentage-formula

Adjusting Percentage Formatting in Excel

Currently, percentages are displayed without decimal places. These settings are default by nature. However, you can change the percentage formatting in Excel by clicking the little arrow icon at the bottom right in the “Number” group. 

4-adjust

The format cells dialog box will appear. You can now specify how many digits you want after the decimal point.

5

Calculating the Percentage of Total in Excel

Let’s say you want to know the total run scored by the four batsmen and what percentage of the total run is attributed by each player. To calculate the percentage of the total, follow these steps:

  1. Calculate the total runs scored in cell F6.
  2. Use the following formula to calculate percentages of the total: =F2/$F$6.

oftotal.

The $ signs represent an absolute reference to cell F6. This means that the value does not change even if the other values change according to the spreadsheet.

Calculating the percentage of total from different rows 

Now suppose a retailer has multiple rows of the same products and wants to use Excel to find out what percentage of the total that product represents.

differentRows-percentage-formula

In this case, you will use the SUMIF function to add up all numbers for a given product, and then divide that number by the total.

Syntax: =SUMIF(range, criteria, sum_range) / total

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

In our example, we have entered the criteria “Pens” in cell E1. The values that we have to add are in column B and the product names for a selection are in column A. The corresponding formula in Excel is as follows:

=SUMIF(A2:A7,E1,B2:B7)/$B$8

Your Excel sheet will now look like this:

multipletota

Percentage Change

The formula to calculate the percentage change between two values A and B is explained below:

Percent Change = (B - A) / A

Suppose that you have the scores of different players of ODI 1 and ODI 2. To calculate the percent change in score of ODI 2 over ODI 1, follow these steps: 

  1. In cell H2, enter the formula (G2 - F2) / F2.
  2. Copy the formula to the below cells.
  3. After copying the formula, click on the Percent Style button to display the value in percentage

The negative sign signifies that there is a decrease in the score.

percentchange

Calculating Amount and Total by Percentage

Suppose you want to calculate the 11% of $1,040. Multiply the value in cell A2 with cell B2 and you will get the 11% of $1,040.

bop

Note: When you type a number followed by the percent sign (%) in Excel, the number is interpreted as a hundredth of its value. For example, 11% is stored as 0.11 and Excel uses this value to calculate the result.

Increasing and Decreasing a Number by Percentage

You can also increase or decrease the number by a percentage in Excel. To increase the amount by a percentage, the following formula can be used:

= Amount * (1 + x%) 

Suppose you want to increase the rate of your products by 30%. All you have to do is enter the above formula in cell C2.

bynumber

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

With this article, you’ve successfully understood various effective ways to apply the Excel percentage formulas. You have seen several examples on how to calculate the percentage of the total, increase and decrease in percentage, and percentage change.

If you are looking for a strong boost to your analytics career, acquiring powerful new Microsoft Excel skills offered in Simplilearn’s Business Analytics with Excel course (which includes Power BI training) would be the right choice for you.

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions for us? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Kartik MenonKartik Menon

Kartik is an experienced content strategist and an accomplished technology marketing specialist passionate about designing engaging user experiences with integrated marketing and communication solutions.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.