How to Remove 5% GST from Total Amount in Excel

How to Remove 5% GST from Total Amount in Excel

by

Are you trying to figure out how to remove 5% GST from a total amount in Excel? Whether you’re managing invoices, filing taxes, or analyzing expenses, knowing how to separate GST from the total can save you time and ensure accuracy.

While Excel is a powerful tool for such calculations, it can sometimes feel tricky if you’re not familiar with formulas. Don’t worry—we’ve got you covered! In this article, we’ll walk you through the process step-by-step. Plus, we’ll introduce you to a simpler solution: Profit Journal’s Free GST Calculator.

Why Remove 5% GST from the Total Amount?

Before we jump into the “how,” let’s quickly understand the “why.” Removing GST from the total amount is essential for:

  1. Claiming Input Tax Credit (ITC) : Businesses need to know the exact GST component to claim ITC.
  2. Budgeting and Expense Tracking : Individuals and organizations often want to see the pre-tax value of goods or services.
  3. Reimbursements : If you’re reimbursing expenses, knowing the base amount helps avoid overpayment.
  4. Accurate Record Keeping : Properly separating GST ensures compliance during audits or tax filings.

Now that you know why it’s important, let’s explore how to do it in Excel.

How to Remove 5% GST from Total Amount in Excel

Removing 5% GST from a total amount involves a simple mathematical formula. Here’s how you can do it in Excel:

Step 1: Understand the Formula

The formula to remove GST from the total amount is:Copy

Base Amount = Total Amount / (1 + GST Rate)

For 5% GST, the formula becomes:

Base Amount = Total Amount / 1.05

Once you have the base amount, you can calculate the GST amount by subtracting the base amount from the total amount:

GST Amount = Total Amount - Base Amount

Step 2: Open Excel and Set Up Your Data

  1. Open a blank Excel sheet.
  2. Create two columns:
    • Column A: Total Amount
    • Column B: Base Amount
    • Column C: GST Amount

Step 3: Enter the Formula

Assume the total amount is in cell A2 . Follow these steps:

  1. In cell B2 , enter the formula to calculate the base amount:
=A2/1.05
  1. In cell C2 , enter the formula to calculate the GST amount:
1=A2-B2

Step 4: Test the Calculation

Let’s say the total amount is ₹105 (which includes 5% GST). After entering the formulas:

  • Base Amount (B2) will show ₹100.
  • GST Amount (C2) will show ₹5.

Step 5: Drag the Formula Down

If you have multiple rows of data, simply drag the formulas down the column to apply them to all entries.

Example: Removing 5% GST from ₹525

Let’s work through a real-world example:

Problem:

You have a total bill of ₹525, which includes 5% GST. How much of that ₹525 is GST, and what was the original price before tax?

Solution Using Excel:

  1. Enter ₹525 in cell A2 .
  2. Use the formula =A2/1.05 in cell B2 to calculate the base amount.
  3. Use the formula =A2-B2 in cell C2 to calculate the GST amount.

Result:

  • Base Amount: ₹500
  • GST Amount: ₹25

Now you know that ₹25 is the GST portion, and the actual cost of the item/service before tax was ₹500.

Limitations of Using Excel for GST Calculations

While Excel is a great tool, it has some limitations:

  1. Manual Effort : You need to set up formulas every time you start a new sheet.
  2. Risk of Errors : A small typo in the formula can lead to incorrect results.
  3. Time-Consuming : For large datasets, dragging formulas and checking results can take time.

That’s where Profit Journal’s Free GST Calculator comes in handy!

Simplify GST Calculations with Our Free GST Calculator

If you’re looking for a faster and more accurate way to remove GST from a total amount, try our Free GST Calculator. Here’s how it works:

FREE GST CALCULATOR
  1. Enter the Total Amount : Type in the amount that includes GST.
  2. Select the GST Rate : Choose 5% (or any other rate).
  3. Click “Calculate GST” : Get instant results showing the base amount and GST amount.

No Excel sheets, no formulas—just quick and reliable answers!

Video Step by Step Guide:

Final Thoughts

Removing 5% GST from a total amount in Excel is straightforward once you know the formula. However, if you’re looking for a quicker and error-free solution, our Free GST Calculator is here to help. It’s perfect for businesses, accountants, and individuals who want accurate results in seconds.

Ready to simplify your GST calculations? Try Profit Journal’s GST Calculator today and experience the ease of financial management!

Got questions or feedback? Let us know—we’re here to help!