Adding tax to the sales price or subtracting tax from a total price of an item in Google Sheets (GST)[Update 16 Jul 2022]
We have Goods and Services Tax (GST) here in Australia; it is a sales tax on all sorts of things. You might have something similar in your own country. If our business turnover is more than $75,000 AUD for the year we need to register to pay this GST. This means that we have to include the tax in the total value of an item. In this tutorial, I’ll show you how to do this quickly in Google Sheets.
You can calculate the Total Price of an item including Sales Tax in two ways:
- Add tax to the sales price: Sales Price + Sales Tax = Total Price
- Subtract tax from the total price: Total Price – Sales Tax = Sales Price
Adding Tax to the Sales Price
This is the easiest and often most common approach. Here we set a sales price and then calculate how much tax needs to be applied to the total price.
Let’s say we have $100 and we need to add 10% tax to our total price.
First, we get the amount of tax we need to add. We do this by multiplying the Sales Price by the Tax percentage.
Sales Price = Sales Price Γ Tax percentage/100
Sales Price = Sales Price Γ Tax as a decimal
10 = 100 Γ 0.1
If you have set up the formatting of your cells in your Google Sheet as I have in the image above where the format for A2Β is dollars ($) andΒ B2Β is a percentage (%) then you don’t have to worry about converting the percentage to a decimal. Google Sheets will handle that for you. Take a look at the formula:
=A2 * B2
=100 * 0.1
=10
Note that we use the asterisk symbol (*) in Google Sheets (and in most digital calculations) to multiply items.
All we then need to do is add the Sales Price to the Sales Tax:
= A2 + C2
= 100 + 10
= 110
We can combine both formulas like this:
= Sales Price + Sales Price * Sales Tax
= A2 + A2* B2
= 100 + 100 * 0.1
= 110
Remember that in our order of operations in mathsΒ the multiplication of the Sales Price by the Tax rate as a decimal will occur before adding the Sales Price.
Practice: Go ahead and try and change the Sales Price or Tax % and see what results you get.
Adding Sales Tax Video
Subtracting Tax From the Total Price
Sometimes we don’t want to change the price of an item for a customer, so we will subtract the amount to tax from the original price that we offered them.
Say that we have been selling a widget for $100, but now we have reached the GST threshold and need to include sales tax on the item. Our customers are going to be cranky with us for adding an extra $10 bucks onto the price. Instead, we decide to include the tax in the $100 sales price of the widget.
So, how do we do this?
This time we start off with the Total Price and the Sales Tax.
We’ve used the same example before, where our total price is $100.00 and our tax rate is 10% on the item.
To calculate the Sales Tax included in the Total Price we divide the total price by, one plus the tax rate as a decimal multiplied by the tax rate as a decimal.
=Β Total PriceΒ /(Β 1Β +Β Tax as decimal) * Tax as decimal
= A2 / (1 + B2) * B2
= 100 / (1 + 0.1) * 0.1
= 100 / 1.1 * 0.1
= 9.09
Then to get the Sales Price, simply subtract the Total Price from the Sales Tax:
= Total Price – Sales Tax
= A2 – C2
= 100 – 9.09
= 90.91
Subtracting Tax Video
Get The Google Sheets Sales Tax Calculator
You can grab your very own copy of the Google Sheets Tax Calculator here. Plus there are Google Sheet tabs with the examples above included.
If you have found the tutorial helpful, why not shout me a coffee β? I'd really appreciate it.
Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.
~Yagi
Changelog
- 2022-07-16:Β Added two videos. One for each section of the tutorial.
