How to Easily Calculate Sales Tax (GST 🦘) in Google Sheets

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.

Calculating the taxable amount of a value in Google Sheets step 1

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

Calculate the taxable percentage portion of a value in Google Sheets

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

Add the sales price to the tax added to the item in Google Sheets

We can combine both formulas like this:

= Sales Price + Sales Price * Sales Tax

= A2 + A2* B2

= 100 + 100 * 0.1

= 110

Adding sales tax to an amount in Google Sheets

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.

Subtracting the sales tax from the total price of an item in Google Sheets Part 1

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

Calculate the tax included in the total value of an item in Google Sheets

Then to get the Sales Price, simply subtract the Total Price from the Sales Tax:

= Total PriceSales Tax

= A2C2

= 1009.09

= 90.91

Calculate the sales price after tax based on a total price value in Google Sheets

Subtracting Tax Video

Get The Google Sheets Sales Tax Calculator

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.

Google Sheets Sales Tax (GST) Calculator

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.

Β 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

 

~Yagi

Changelog

  • 2022-07-16:Β Added two videos. One for each section of the tutorial.

Displaying Odd and Even Row Data in Google Sheets

Have you ever wanted to split a list of Google Sheet rows in two by odd or even? Or had a time when you wanted to display all items in a range by odd or even ids in two separate lists?

You don’t have to do this manually, we can work smart and create a formula for each of these scenarios to easily separate your data.

In this tutorial, we will cover three scenarios:

  1. Separate a range of data by odd and even rows.
  2. Separate a range by odd and even where a selected column has a list of numerical ids.
  3. Separate a range by odd and even where a selected column contains a list of alphanumeric ids.

If you want to play along, you can get a copy of the starter sheet here:

Odd and Even | Starter Google Sheet*

*Contains Easter Eggs.

Continue reading “Displaying Odd and Even Row Data in Google Sheets”

Filtering IMPORTRANGE data in Google Sheets

Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.

In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.

Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.

I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:

Sales.sheet

Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios.Β 

Continue reading “Filtering IMPORTRANGE data in Google Sheets”

Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Note!Β This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial.

When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on IMPORTRANGE data was better than another. However, I wanted to be certain. I ran some basic tests comparing the two approaches to see how they both perform over large data sets, and you know what? I was surprised to find I couldn’t find a discernable difference in performance.

Next, I reached out to some of the other fellow Google Sheets nerds, who like to go way too far with software for all the wrong reasons, and they seemed to feel that those demi-gods of Google devs really understand the wayward predilections of their users and may have benevolently stored the IMPORTRANGE data locally in the sheet you have imported to.

Will we ever know for certain? Only ever perhaps in the lay of the tea leaves or roll of the bones. We can but only guess the ways of the Googler as they traverse the digital world in all their etheral glory.

What does this mean in short? There are two pretty solid ways to use VLOOKUP with IMPORTRANGE. I’ll cover both in this tutorial.

Continue reading “Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function”

Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet

Note!Β This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial here:

Importing Range Data From One Google Sheet to Another

And here is a link to the sample data that I am importing if you want to play along:

Template – Project Tasks (IMPORTRANGE)

You’ve grabbed data from a source Google Sheet and used the IMPORTRANGE function to insert it into your destination sheet carefully selecting the range and sheet tab of the data that you want to display.

So you might be thinking,Β Great! I’ve managed to only display the content from my original Google Sheet that I want my users to see and hidden the rest from them.Β 

While this may be the case if you are only providing ‘View’ or ‘Comment’ permission to a Google Sheet, I do have an ugly little surprise for you if you.

via GIPHY

Are you sitting down? Are you comfortable? Do you have a support network nearby?

Let’s have a look at the IMPORTRANGE docs:

IMPORTRANGE editors can access other parts of the original Google Sheet - What to do
Click to Expand!

So basically, if you have other editors on your new sheet editing your document, they can access anything in the imported Google Sheet by copying the access granted IMPORTRANGE. All they need to do is change the Sheet tab and the range location.

Continue reading “Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet”

%d bloggers like this: