How to Calculate GST in Excel
You’ve got a spreadsheet full of prices and need to add GST to all of them. Doing it one by one? That’s going to take forever.
Here’s the good news: with one simple formula, Excel can calculate GST for hundreds of items in seconds. Let me show you exactly how.
Quick Formula Reference
For those in a hurry, here are the formulas you need:
| What You Want | Excel Formula | Example Result |
|---|---|---|
| Add GST to price | =A2*(1+18/100) | ₹10,000 → ₹11,800 |
| Calculate GST amount | =A2*18/100 | ₹10,000 → ₹1,800 |
| Extract base from total | =A2/(1+18/100) | ₹11,800 → ₹10,000 |
| Extract GST from total | =A2-A2/(1+18/100) | ₹11,800 → ₹1,800 |
Now let’s break these down step by step.
Setting Up Your Excel Sheet
Before writing formulas, let’s set up a proper structure:
Basic Column Layout
| Column | Header | Purpose |
|---|---|---|
| A | Item Name | Product or service description |
| B | Base Price | Price before GST |
| C | GST Rate | Tax percentage (e.g., 18) |
| D | GST Amount | Calculated tax |
| E | Total | Final price including GST |
Pro Tip: Use a Fixed Rate Cell
Instead of typing “18” in every formula, put your GST rate in a single cell (like B1) and reference it. This way, if the rate changes, you only update one cell.
Cell B1: 18Formula 1: Calculate GST Amount from Base Price
This is the most common calculation – you have the base price and want to find the GST amount.
The Formula
=Base_Price * GST_Rate / 100In Practice
If your base price is in cell A2 and GST rate is in cell B1:
=A2*$B$1/100Why $B$1? The dollar signs create an “absolute reference” – when you drag the formula down, Excel won’t change B1 to B2, B3, etc. It stays locked on your rate cell.
Example
| A | B | C |
|---|---|---|
| Base Price | GST Rate | GST Amount |
| 10000 | 18 | =A2*$B$1/100 → 1800 |
| 5000 | 18 | =A3*$B$1/100 → 900 |
| 25000 | 18 | =A4*$B$1/100 → 4500 |
Formula 2: Calculate Total Price (Base + GST)
Once you have the GST amount, you can add it to the base price. But there’s a faster way – calculate the total in one step.
The Formula
=Base_Price * (1 + GST_Rate / 100)In Practice
=A2*(1+$B$1/100)This multiplies the base price by 1.18 (for 18% GST), giving you the total directly.
Example
| Base Price | Formula | Total |
|---|---|---|
| ₹10,000 | =A2*(1+$B$1/100) | ₹11,800 |
| ₹5,000 | =A3*(1+$B$1/100) | ₹5,900 |
| ₹25,000 | =A4*(1+$B$1/100) | ₹29,500 |
Formula 3: Extract Base Price from Total (Reverse Calculation)
Got an invoice showing the total amount? Here’s how to find the original base price.
The Common Mistake
✗ Wrong Approach
=A2 - A2*18%
₹11,800 - ₹2,124 = ₹9,676 ❌
✓ Correct Approach
=A2/(1+18/100)
₹11,800 / 1.18 = ₹10,000 ✓
The Formula
=Total_Price / (1 + GST_Rate / 100)In Practice
=A2/(1+$B$1/100)Example
| Total (Inclusive) | Formula | Base Price |
|---|---|---|
| ₹11,800 | =A2/(1+$B$1/100) | ₹10,000 |
| ₹5,900 | =A3/(1+$B$1/100) | ₹5,000 |
| ₹29,500 | =A4/(1+$B$1/100) | ₹25,000 |
Formula 4: Extract GST Amount from Total
Need to know exactly how much tax is included in the total price?
The Formula
=Total_Price - Total_Price / (1 + GST_Rate / 100)In Practice
=A2-A2/(1+$B$1/100)Or a cleaner alternative:
=A2*$B$1/(100+$B$1)Example
| Total | Formula | GST Amount |
|---|---|---|
| ₹11,800 | =A2-A2/(1+$B$1/100) | ₹1,800 |
| ₹5,900 | =A3-A3/(1+$B$1/100) | ₹900 |
Pro Tip: Use Named Ranges
Instead of remembering $B$1, give your rate cell a name:
- Select cell B1 (your GST rate)
- Click the Name Box (left of formula bar)
- Type
GSTRateand press Enter
Now your formulas become much cleaner:
// Calculate GST amount
=A2*GSTRate/100
// Calculate total
=A2*(1+GSTRate/100)
// Extract base from total
=A2/(1+GSTRate/100)Benefits:
- Formulas are self-documenting
- Easier to update the rate
- Less chance of reference errors
Advanced: Multiple GST Rates
Real-world scenarios often involve different GST rates (5%, 12%, 18%, 28%) for different products.
Method 1: Rate Column
Add a “Rate” column for each item:
| Item | Base Price | Rate | GST Amount | Total |
|---|---|---|---|---|
| Food | 1000 | 5 | =B2*C2/100 | =B2+D2 |
| Clothing | 2000 | 12 | =B3*C3/100 | =B3+D3 |
| Electronics | 5000 | 18 | =B4*C4/100 | =B4+D4 |
| Luxury | 10000 | 28 | =B5*C5/100 | =B5+D5 |
Method 2: VLOOKUP for Automatic Rates
Create a rate lookup table and auto-fill rates based on category:
Rate Table (in cells F1:G5):
| Category | Rate |
|---|---|
| Food | 5 |
| Clothing | 12 |
| Electronics | 18 |
| Luxury | 28 |
Formula to get rate:
=VLOOKUP(A2, $F$1:$G$5, 2, FALSE)This automatically picks the correct rate based on the item category.
Using These Formulas in Google Sheets
Great news – all these formulas work exactly the same in Google Sheets!
Bonus: ARRAYFORMULA
Google Sheets offers ARRAYFORMULA to apply a formula to an entire column automatically:
=ARRAYFORMULA(A2:A100*(1+GSTRate/100))This fills the entire range without needing to drag the formula down.
Common Mistakes to Avoid
Mistake 1: Using relative references for the rate cell
Use $B$1 (absolute) instead of B1 (relative)
so the reference doesn’t change when you copy the formula.
Mistake 2: Subtracting percentage from total to get base
Wrong: =A2-A2*18%. Correct: =A2/(1+18/100).
See the formula section above for why.
If your formula shows an error, check if your price cells have a green triangle (text format). Convert them to numbers first.
Complete Spreadsheet Example
Here’s a full working example you can recreate:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | GST Rate: | 18 | |||
| 2 | Item | Base Price | GST Amount | Total | |
| 3 | Product A | 10000 | =B3*$B$1/100 | =B3+C3 | |
| 4 | Product B | 5000 | =B4*$B$1/100 | =B4+C4 | |
| 5 | Product C | 25000 | =B5*$B$1/100 | =B5+C5 | |
| 6 | Total | =SUM(B3:B5) | =SUM(C3:C5) | =SUM(D3:D5) |
Result:
| Item | Base Price | GST Amount | Total |
|---|---|---|---|
| Product A | ₹10,000 | ₹1,800 | ₹11,800 |
| Product B | ₹5,000 | ₹900 | ₹5,900 |
| Product C | ₹25,000 | ₹4,500 | ₹29,500 |
| Total | ₹40,000 | ₹7,200 | ₹47,200 |
Related Tools
Frequently Asked Questions
Frequently Asked Questions
What is the formula for GST calculation in Excel?
To add GST: =Price*(1+Rate/100). To extract GST from total: =Total/(1+Rate/100). For example, to add 18% GST to ₹10,000: =10000*(1+18/100) gives ₹11,800.
How do I calculate GST for multiple items at once?
Enter your formula in the first row, then drag the fill handle down to apply it to all rows. Use absolute references ($B$1) for the rate cell so it doesn't change when you drag.
Can I use these formulas in Google Sheets?
Yes! All Excel formulas shown here work exactly the same in Google Sheets. You can also use ARRAYFORMULA for automatic expansion.
How do I handle different GST rates (5%, 12%, 18%, 28%)?
Create a lookup table with item categories and their rates, then use VLOOKUP or INDEX/MATCH to automatically pick the correct rate for each item.
Why can't I just multiply by 18% to extract GST from total?
Because the 18% was applied to the base price, not the total. If you multiply ₹11,800 by 18%, you get ₹2,124 – which is wrong. The correct GST is ₹1,800.
Is there a free Excel template I can download?
We're working on a free GST calculator template with pre-built formulas. Check back soon for the download link!