Sometimes, you may need to calculate the total amount of a value after applying a percentage increase or decrease. For example, you may want to know how much you have to pay after adding a 10% tax, or how much you can save after applying a 20% discount. In this article, we will show you how to calculate total with percentage in Excel using different methods and formulas.
The basic formula to calculate total with percentage is:
where:
- Total is the final amount after applying the percentage.
- Base is the original amount before applying the percentage.
- Percentage is the percentage rate expressed as a decimal. For example, 10% is 0.1, 20% is 0.2, and so on.
This formula works for both positive and negative percentages. For positive percentages, it means adding a percentage of the base to the base. For negative percentages, it means subtracting a percentage of the base from the base.
Procedures
To calculate total with percentage in Excel, you can use the following steps:
- Enter the base amount in a cell, such as A1.
- Enter the percentage rate in another cell, such as B1. You can enter the percentage as a decimal (e.g. 0.1) or as a percentage (e.g. 10%). If you enter it as a percentage, make sure to format the cell as a percentage by clicking the Percent Style button on the Home tab.
- In another cell, such as C1, enter the formula
=A1*(1+B1)and press Enter. This will calculate the total amount after applying the percentage. You can also format the cell as a currency or a number by clicking the Number Format button on the Home tab. - You can copy and paste the formula to other cells if you have multiple base amounts and percentages to calculate.
Example
Let’s say you want to calculate the total price of a product after adding a 15% tax and a 5% service charge. You also want to calculate the total savings if you get a 10% discount on the original price. Here is how you can do it in Excel:
| Product | Price | Tax | Service Charge | Total Price | Discount | Total Savings |
|---|---|---|---|---|---|---|
| A | $100 | 15% | 5% | $121 | 10% | $12.10 |
| B | $150 | 15% | 5% | $181.50 | 10% | $18.15 |
| C | $200 | 15% | 5% | $242 | 10% | $24.20 |
To create this table in Excel, you can use the following steps:
- Enter the product names in column A, starting from A2.
- Enter the prices in column B, starting from B2. Format the cells as currency by clicking the Currency button on the Home tab.
- Enter the tax rate in C2 as 15%. Format the cell as a percentage by clicking the Percent Style button on the Home tab. Copy and paste the cell to C3 and C4.
- Enter the service charge rate in D2 as 5%. Format the cell as a percentage by clicking the Percent Style button on the Home tab. Copy and paste the cell to D3 and D4.
- In E2, enter the formula
=B2*(1+C2+D2)and press Enter. This will calculate the total price of product A after adding the tax and the service charge. Format the cell as currency by clicking the Currency button on the Home tab. Copy and paste the formula to E3 and E4. - In F2, enter the formula
=B2*0.1and press Enter. This will calculate the discount amount of product A based on the original price. Format the cell as currency by clicking the Currency button on the Home tab. Copy and paste the formula to F3 and F4. - In G2, enter the formula
=E2-F2and press Enter. This will calculate the total savings of product A after applying the discount. Format the cell as currency by clicking the Currency button on the Home tab. Copy and paste the formula to G3 and G4.
