For working professionals
For fresh graduates
More
27. Columns in Excel
33. Count In Excel
49. Slicers in Excel
54. Solver in Excel
56. Macros In Excel
Have you ever looked at a spreadsheet filled with numbers and wondered if you could solve it all in no time? As someone who has spent countless hours trying to find a solution, I can assure you that the Excel percentage formula is the go-to function you need.
In recent times, many businesses have been using Excel in their daily lives, because the availability of simple Excel formulas is advantageous to them. This function allows you to calculate sales growth, track profitability, or even analyze budget allocations with just a simple syntax.
In this article, I have curated a few methods that you can use in your work to unlock their potential in solving a huge set of raw data. Let’s see the ways to observe the formula in action in this tutorial.
The word percentage signifies any value divided by a number and a hundred. The symbol % helps to identify the percentage value. There are various simple ways in which you can choose to calculate Excel percentage or percentage change.
Suppose you make a spreadsheet with a set of data that shows marks secured by individual students. Column A has the students’ names and columns B and C have the secured marks and total marks, respectively.
To calculate percentages in Excel, of each student, you need to divide secured marks by the total marks. Here, the part is secured marks obtained by the student and the whole is total marks for the students. You can us this function to calculate the percentage value from the data. Microsoft Excel has the percentage function to solve it for you, you are just required to follow a few steps:
Source: MS Excel
Source: MS Excel
As a result, this will help you to get the percentage value from the decimal number.
Source: MS Excel
There is another way that you can get the percentage value by using one single formula. Microsoft Excel does not have any built-in formula to calculate percentages in round-up form. In these cases, we just need to make sure to multiply the value by 100. The Excel Percentage Formula will be:
=(B2/C2)*100
Source: MS Excel
Here, the value of (B2/C2) is divided by 100 to simplify the result from decimal to the percentage for better insight. Once you get the percentage or the first row, you can simply drag and drop the rest of the results.
In some cases, you may see the total number at the end of the part column, and you have to calculate the percentage of each category concerning that specific total. Here, we use the absolute reference or the calculation process.
They are mainly used to keep a certain row or a column constant. These constant references cannot be changed or copied as they remain unaffected. If look at the example below, column A indicates the names of the students in your classroom and column B shows the marks they have acquired in their examination.
Source: MS Excel
Now if you look closely, you can see the total marks are written below the secured marks in column B (unlike what we have seen in previous examples). Here, the absolute reference would be cell B7 as it requires no change and needs to remain unaffected.
Therefore, we use the symbol “$” in the formula, to keep the value static as an absolute reference. To get the percentage of each student, you need to run a formula where the individual values will be divided by the total and it remains the same for all calculations.
Here, in this case, to find out the percentage of individual students, the Excel percentage formula will be:
=B2/$B$7.
Source: MS Excel
Source: MS Excel
The percentage values are acquired in the designated cells under the C column. You can see each percentage is in its decimal form. You can increase or decrease decimals by using the “increase and decrease decimal function” under the number function from the home ribbon on your Excel sheet.
Source: MS Excel
This will help you to get a standard decimal form in the percentage column like the image below.
Source: MS Excel
Once the percentage value is achieved, you can apply the Excel percentage function for the values, then drag and drop the same to calculate the percentage for the rest of the students.
In many cases, you can use the percentage formula in Excel to calculate the percentage change between the values. It is used to track any difference or change in a quantity over time. The concept of percentage change mainly denotes the ratio between two different values which goes like this:
=((value 2-value1)/value 1)*100
Suppose, in 2022, the share price of Company A was x1 and in 2023, the share price was x2. Now to calculate the percentage change, we need to follow the percentage change formula which is:
Percentage change= ((x2-x1)/x1)*100
This is how we can get the increase and decrease percentage from the percentage concept. Similarly, calculating the same concept of percentage change is very easy to get in Excel. I have provided a detailed method of percentage change formula in Excel. See for yourself.
Step 1:
Source: MS Excel
The following image shows some companies X, Y, Z, and D and their share prices over two years 2022 and 2023.
Step 2:
To calculate the percentage increase, over the year, we can use the formula:
=(Final Share Price – Initial Share Price) / Initial Share Price
For Excel, the formula will be:
=(C3-B3)/B3
Source: MS Excel
This specific percentage increase formula in Excel helps to get the change in share price for the initial share price.
Step 3:
Source: MS Excel
After achieving the increased value, we can select the Excel percentage function to get the percentage value. You can drag and drop the rest of the data.
Source: MS Excel
If the share price of the companies decreases over the year, the same formula can be applied as the percentage decrease formula in Excel to acquire the data.
If you follow the previous examples, you might have observed how we always obtained the percentage results in decimal form after applying the Excel percentage formula.
Source: MS Excel
There is nothing to panic about as you are getting the results in decimal because you have not applied the percentage function in the cells yet. If we go to Home, click on Number functions from the drop-down menu, and choose Percentage from the panel, you can get the percentage value in the designated cells. Drag and drop for the rest of the results.
Source: MS Excel
The result is not an accurate round number, in many cases, the percentage with no zero in the decimal position is known as a round-up in Excel. To add the decimals to the percentage values, we can follow some easy steps:
Step 1:
Source: MS Excel
First, you need to select the cell. Then click Home, followed by ‘Number functions’ and then ‘Number formatting’ options.
Step 2:
Source: MS Excel
When you click on number formatting options, it launches a “format cells” dialogue box
Step 3:
Source: MS Excel
Once the dialogue box opens, click on “percentage” from the format cells options and set the desired decimal position to any number you like.
Step 4:
Source: MS Excel
Excel adds the decimals as per the given instruction (here in this case, the decimal is set up to 2) and the value shows in percentage.
Tips:
Source: MS Excel
Instead of using number formatting options, you can also increase or decrease the decimal positions with the help of an easy shortcut.
In my experience, being skillful in Excel percentage formulas has been a huge game changer for me. The ultimate power of these simple formulas ultimately saved me from endless frustration and longer hours of manual work.
With better practices, you will master the formulas like using a tool with utmost confidence. So dive in and explore to embrace the power of Excel percentage formulas. You can also opt for Excel courses from the UpGrad site to boost your career.
To calculate the percentage in Excel, you need to put the formula: (Part/whole) and then multiply the result by 100.
The formula for calculating percentage is (Part/whole)*100.
To calculate proportions, you need to select two cells that contain the values you need to compare and select “Proportion” from the formula bar.
You can multiply the desired cell by 0.8 to take a 20% discount in Excel.
A percentage formula in Excel is = ((Part/whole)*100). Here, you can replace part and total by the referred cell.
The shortcut for percentage in Excel: CTRL+SHIFT+%
You need to multiply the number by 0.05. The formula will be: =0.05*number.
After calculating the formula, you can click on the percentage style to format it in Excel.
Author
Talk to our experts. We are available 7 days a week, 9 AM to 12 AM (midnight)
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
1.The above statistics depend on various factors and individual results may vary. Past performance is no guarantee of future results.
2.The student assumes full responsibility for all expenses associated with visas, travel, & related costs. upGrad does not provide any a.