Chapter 5: Understanding Percentages in Excel

Introduction to Percentages in Excel

Percentages are a fundamental part of data analysis and financial calculations in Excel. Whether you’re working with tax rates, discounts, or percentage changes, Excel provides powerful tools to handle these calculations efficiently. In this chapter, we will explore three common types of percentage questions and how to solve them using Excel formulas.

Types of Percentage Calculations

1. Calculating Final Amount with Tax or Discount

• Scenario: You have an initial price and a percentage of tax or discount. You need to find the final amount.
• Formula:
  • To calculate the final amount with tax: Final Amount = Initial Amount + (Initial Amount * Tax Percentage / 100)
  • To calculate the final amount with a discount: Final Amount = Initial Amount – (Initial Amount * Discount Percentage / 100)
• Example:
  • Initial Price: $100
  • Tax Rate: 10%
  • Formula: =100 + (100 * 10 / 100) which results in $110.
  • Initial Price: $100
  • Discount Rate: 15%
  • Formula: =100 – (100 * 15 / 100) which results in $85.

2. Finding Percentage from Initial and Final Amount

• Scenario: You know the initial amount and the final amount, and you need to find the percentage change.
• Formula: Percentage Change = (Final Amount – Initial Amount) / Initial Amount * 100
• Example:
  • Initial Amount: $80
  • Final Amount: $100
  • Formula: =(100 – 80) / 80 * 100 which results in 25%.
• In Hindi: “jitna mila / jitne me se mila”

3. Finding Initial Amount from Percentage Change and Final Amount

• Scenario: You know the percentage change and the final amount, and you need to find the initial amount.
• Formula: Initial Amount = Final Amount / (1 + Percentage Change / 100)
• Example:
  • Final Amount: $120
  • Percentage Increase: 20%
  • Formula: =120 / (1 + 20 / 100) which results in $100.

Practice Assignment:

Create a Percentage Calculation Sheet

1. Set Up Your Data:
   • Column A: Initial Price
   • Column B: Tax or Discount Percentage
   • Column C: Final Amount
   • Column D: Percentage Change (if applicable)
2. Using Formulas:
   • Calculate Final Amount:
     • In Column C, use the following formula to find the final amount with tax or discount:
       • For Tax: =A2 + (A2 * B2 / 100)
       • For Discount: =A2 – (A2 * B2 / 100)
   • Calculate Percentage Change:
     • In Column D, if you have initial and final amounts, find the percentage change:
       • Use: =(C2 – A2) / A2 * 100
   • Find Initial Amount:
     • If you have the final amount and the percentage increase:
       • Use: =C2 / (1 + B2 / 100)

Pro Tip:

When working with percentages, always double-check your formula references to ensure accuracy. If you’re dealing with both tax and discount in the same worksheet, clearly label each calculation to avoid confusion. Also, use Conditional Formatting to highlight cells where calculations are applied—this will help in reviewing your data and formulas quickly.

This chapter introduces you to essential percentage calculations that you’ll frequently encounter in Excel. By practicing these methods, you’ll enhance your ability to handle financial data and percentage-based questions effectively. At Anjni Computer Education, we focus on providing practical, hands-on experience to make these concepts clear and actionable.