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.
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.
- Go to the Home Tab > Number Group and click on the Percent Style button.
Now you can see your data in terms of percentages.
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.
The format cells dialog box will appear. You can now specify how many digits you want after the decimal point.
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:
- Calculate the total runs scored in cell F6.
- Use the following formula to calculate percentages of the total: =F2/$F$6.
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.
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
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:
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:
- In cell H2, enter the formula (G2 - F2) / F2.
- Copy the formula to the below cells.
- 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.
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.
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.
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 course 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!