Spreadsheets

Add GST to a price in Excel: exclusive to inclusive with rounding

Add GST to a price in Excel with =ROUND(price*(1+rate),2), set up a rate dropdown, and build a line-item invoice whose total matches the printed bill.

Editorial illustration for Add GST to a price in Excel: exclusive to inclusive with rounding

To add GST to a price in Excel, multiply by one plus the rate and round to two decimals: =ROUND(B2*(1+C2), 2), where B2 is the price before tax and C2 is the rate as a percentage. A price of ₹2,499 at 18% becomes ₹2,948.82. The same formula works in Google Sheets.

Build the rate dropdown first

Typing the rate by hand on every line is how mistakes enter. Make a short list instead.

  1. On a second sheet called Lists, type the rates you actually use in A2 downwards, for example 5%, 12% and 18%, each as a percentage.
  2. Select the rate column of your invoice (say D5:D24), then open Data, Data Validation.
  3. Choose List from a range and point it at =Lists!$A$2:$A$10.
  4. Each rate cell now offers a dropdown, and a stray 18 or 1.8 is refused.

What does the line-item layout look like?

Columns: A Item, B Quantity, C Unit price, D Taxable value (=B5*C5), E GST rate (dropdown), F GST amount (=ROUND(D5*E5, 2)), G Line total (=D5+F5). A short hypothetical invoice:

ItemTaxable valueRateGST amountLine total
Rice, 5 kg bag (1 x ₹1,150.00)₹1,150.005%₹57.50₹1,207.50
Table lamp (2 x ₹399.00)₹798.0012%₹95.76₹893.76
Filter cartridge (1 x ₹2,499.00)₹2,499.0018%₹449.82₹2,948.82

Totals: taxable ₹4,447.00, GST ₹603.08, invoice total ₹5,050.08. Put =SUM(G5:G7) in the total cell, so the invoice total is the sum of rounded lines.

Why can my total differ by a paisa from the printed bill?

Because rounding can happen on each line or once on the whole invoice, and the two methods can disagree. Take three lines of ₹10.30 at 12%. Line by line, each GST is 1.236, rounded to ₹1.24, and three lines give ₹3.72. Rounding once on ₹30.90 gives 3.708, rounded to ₹3.71. Neither is wrong; they follow different conventions. Find out which one the printed bill uses and match it in your sheet.

Where does a discount go?

If the seller gives a discount, many bills show the discount first and then tax on what is left. Say a price is ₹2,000, the discount is 10% and the rate is 18%. Taxable value is 2,000 x 0.90 = ₹1,800, GST is 1,800 x 0.18 = ₹324, and the total is ₹2,124. In one cell: =ROUND(B2*(1-C2)*(1+D2), 2). Applying the discount after tax would give a different total, so check how the printed bill treats it and follow that.

How do you avoid rounding the wrong thing?

  • Round the GST amount, not the rate and not the intermediate multiplication you do not display.
  • Use ROUND, not just a number format. A cell formatted to two decimals still holds the long figure and can add up wrongly.
  • Do not round the taxable value unless the invoice does.
  • Keep the rate as a real percentage, not a typed number such as 18.

Can one price carry two rates?

Not in a single line. If a bill has a service charge at one rate and goods at another, split them into two rows, each with its own taxable value and rate. The dropdown makes this easy, and the totals still add up.

What are good test values?

Before using the sheet for real bills, try prices whose answers you can check by hand. ₹1,000 at 18% should give ₹1,180.00. ₹999 at 5% should give ₹1,048.95, because 999 x 1.05 is 1,048.95. A price of ₹0.50 at 12% shows how rounding behaves on tiny amounts. If all three match your calculator, the formula and the format are right.

Before you send or file anything

A sheet like this is a calculator, not a tax authority. The rate on an item depends on how it is classified, and rules and rates change, so confirm the current position with your CA or the official source before issuing an invoice or claiming credit on one. Keep a copy of the printed bill beside the sheet to compare line by line.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports