In this article, we will learn How to calculate different percentages in Excel.
Percentages:
Calculating percentages is a basic task in calculating data. We surely need to know how much percentage of work is done. For example In school/collage we always calculated our attendance percentage carefully to be able to appear in exams. So calculating percentages is basic and anyone who works with numbers must know how to handle data in percentages. Generally, In mathematics if we need to multiply the result by 100 to get the percentage value but whereas Excel considers decimal as a percentage value. And you can change its formatting to percent by selecting the percent option in Home > % (symbol under Number section) as shown in the below snapshot.
Normal Percentage formula in Excel
Get the Percent for the sold product data in excel using a simple formula. Divide the sold product amount by the total amount of the product.
Syntax for the formula:
| Percent = Sold Amount / Total product |
Types of percentage calculation in excel
Example :
All of these might be confusing to understand. Let's understand how to use the function using an example. Here Let’s say you distributed your product in five regions of the country. You would like to know what percentage of total product is distributed in each region. See below image.
I always prefer my total on the top of the table, so that whenever I extend the table I do not need to replace it. And it is always visible too if rows are frozen. So yeah it has its benefits.
Now to calculate percentage of total write this Percentage formula in cell C4 and drag it down:
Use the Formula
| =B4/$B$2 |
You will see fraction numbers. Now to convert them into percentage, select the cells and press CTRL + SHIFT+ %. This is a shortcut to convert numbers into percentage. Same thing can be done from the Number section of the Home tab.
Goto home tab
Click on % formatting in Number section.
And it’s done. We calculate the percentage of distribution in each region of Total distribution.
Note :
we have given absolute reference of Total and relative reference of region. It will allow regions to change when copied in a relative cell but total will not change. In C2 we have some percentage, which is 100%. This can be used to validate the report. It should always be 100%. Here we use number formatting to add % symbol. In the background they are actually fraction numbers. 7% mean 0.7.
If you want your percentage in whole numbers then use this formula instead.
Use the formula
| =(B4/$B$2)*100 |
In the above example we used a total cell. Although you would like to have that total cell but in case you don’t, write this formula to calculate percentage of total.
| =B4 / SUM($B$4:$B$8) |
Here we just replaced the total cell with absolute function in that cell.
The above examples are static. You will need to edit them if you extend your table. So how do you Calculate Percentage of Total Dynamically? We will use Tables here.
To tablise your data,
Now each column is a dynamic named range. The headings are the name of ranges. This makes it very easy to read and write the formulas.
Increase or Decrease in Percentage change
Here we will use the increased percentage value formula to get the new Amount for the data set.
Use the Formula in D3 cell
| =B3*(1+C3) |
As you can see in the above snapshot first New Amount after increment is 7700.
Copy the formula in the remaining cells to get the New Amount for the rest of the data.
New Amount can be calculated using the above procedure and understand how to handle with percentage decrease.
Here we have some products with their current Cost price and percentage decrement.
And we need to find out the new discounted Price for the products.
Use the formula
| =B2*(1-C2) |
B2 : Current Value
C2 : percentage change in Value
Explanation :
Value gets multiplied with (1 - C2) to get the discounted amount.
Here the new Sales Price is listed in the Sales Price column.
Copy the formula to other cells using shortcut key Ctrl + D
This method can be used for various purposes. As you can see here the new Sales Price is listed in the Sales Price column.
Here we will use the percentage value formula to get the percent value for the data.
Use the Formula in D3 cell
| = B2 / A2 |
Explanation : The sold amount will be divided by the total amount for a product.
Here the values to the function is given as cell reference.
As you can see in the above snapshot first old Amount before increment is 91% .
Copy the formula in the remaining cells using Ctrl + D shortcut, to get the New Amount for the rest of the values in the table.
In the above snapshot the mathematical formula is used to get the Percentage value for the sold product amount in excel.
Here we will use the above percentage discount formula in the below example.
Use the Formula in D3 cell
| =1-(C3/B3) |
As you can see in the above snapshot the first data percentage discount is 6%.
Copy the formula in the remaining cells to get the percentage discount for the rest of the data.
Percentage discount can be calculated using the above method.
Profit margin percentage calculates the percentage change in profit margin using a simple mathematical operation shown below in the box.
Mathematical formula:
| Profit margin percentage = 1 - (cost price / sale price) |
We will use this in Excel to find Percentage change in profit margin.
Let’s use the above formula on a few examples to learn better
Use the Formula in D2 cell
| =1-(B2/C2) |
As you can see in the above snapshot first data percentage of profit margin is 8%.
Copy the formula in the remaining cells to get the percentage change of profit margin for the rest of the data.
Profit margin percent can be calculated using the above method.
How to get the total amount from Percentage?
Here we will use the basic mathematics observation used in excel. Excel considers percent value as decimal means 20% is 0.2 in excel. So we just need to divide the amount with percent value so as to get the total amount.
Generic formula:
| Total amount = discounted amount / discount percent |
Discounted amount : amount after the discount applied
Discount percent : percent at which discount is given
Example:
All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we will use the generic formula to get the total value for the data.
Use the Formula in D3 cell
| = B3 / C3 |
The formula will be like as shown in the image above. Here the values to the function are given as cell reference.
As you can see in the above snapshot first old Amount comes out to be $ 7,700. Copy the formula in the remaining cells using Ctrl + D shortcut, to get the New Amount for the rest of the values in the table.
In the above snapshot the mathematical formula is used to get the total value from the percent amount in excel.
Here are all the observational notes using the formula in Excel
Notes :
Hope this article about How to calculate percentages in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.
Related Articles:
Calculate Percentage of Total in Excel : Calculating percentages is a basic task in any field of work. We always want to know how much work is done. In school/collage we always calculated our attendance percentage carefully to be able to appear in the exam.
How to Calculate Percentage Discount in Excel : To calculate change in percentage discount of items, we simply subtract the discount price from total price and then get the percentage. This simple excel formula will get this done.
How to Increase by percentage in Excel : To increase a given value by a given percentage we can simply add the percentage of increment in 100% and then multiply the actual number with it. This will increase the given value with the given percentage.
How to Calculate Profit margin percentage in Excel : To calculate the profit margin we simply divide the sales price by purchase price and subtract the result from 1. This gives us the percentage of profit margin.
Mathematical Percentage Decrease Change in Excel : In this article, how to use percentage decrease change formula in Excel 2016.We need to find the percentage decrease in value due to decrement percentage change.
How to Calculate Project Complete Percentage in Excel : When each task is completed, we say that project is completed 100%. In that case we would like to monitor how the task is completed while we work on the project. In this article, we will learn how you can calculate the project complete percentage in Excel
Get the percentage for the sold product in Excel : In this article, we will learn about how to calculate the percent for the sold product in excel. Get the Percent for the sold product data in excel using a simple formula. Divide the sold product amount by the total amount of the product.
Find Total Amount From Percentage : for instance, working with percentage values in excel sometimes we need to get the total amount from the given percentage and the number. Given a discounted amount and discount percent, get the amount before discount or the total amount.
Popular Articles :
50 Excel Shortcuts to Increase Your Productivity : Get faster at your tasks in Excel. These shortcuts will help you increase your work efficiency in Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
