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.

Importing Range Data From One Google Sheet to Another

One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google Sheet that you have imported the data to.

Everything starts with the IMPORTRANGE Google Sheets function.

But before we get started on the “How to’s,” you might be wondering why you might need to import live Google Sheet data from one sheet to another.

Or check out the table of contents to dive straight into what you need right now!

Continue reading “Importing Range Data From One Google Sheet to Another”

New course ๐ŸŽ‰๐ŸŽ‰๐ŸŽ‰ Google Sheets: Learn the Essentials with Three Detailed Projects & 60% off ๐Ÿ˜ƒ

It’s the big day!

I’m very excited to introduce you to my new course:

Google Sheets: Learn the Essentials with Three Detailed Projects

Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three detailed projects.

And because this is the opening week, I am offering a whopping 60% off the course for the next 7 days only.

Just use the Coupon Code…

NEWCOURSEDISCOUNT

…on the checkout page and you can start the course today.

CHECK OUT THE COURSE!

Google Sheets Project Main Page
Click to Expand!

First 5 Chapters Free

I have also opened up the first 5 Chapters of the course for you to try completely FREE.

Go on and give it a try now!

CLICK HERE!

Share the love

If you are already a Google Sheets legend, you know how important it is to learn how to master spreadsheets. If you know someone transitioning industries, just starting out or simply needing to boost their skillset please share this post.

This is a whopper of a discount and not one to miss.

The offer expires on Sunday the 9th May 2021.

DON’T MISS OUT!

Why I built the course

This has been a project I wanted to build for a long time. The best way to learn something is by getting hands-on and building a project.ย  That’s how I know I learn best.

I wanted to build a Google Sheets beginners to pro course that I wanted when I first started in the world of Google Sheets. Something with real-life examples and a set of projects that I can build and maybe even modify and reuse in the future. Something that not only gives me the how-toos but the why I should be building and designing my spreadsheets in a certain way.

Go on, check out the link to find out more about the course and try out the first 5 chapters on me. I know you are going to get a lot out of it.

FIND OUT MORE!

This offer expires on Sunday 9 May 2021.

~Yagi ๐Ÿ

Google Sheets Beginners: Getting Help (44)

One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn.

Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a lot of the frustration of finding the right information and get you there in a much more efficient way.

In the video below I will go over how to use all of Google Sheets help tools effectively and then discuss how you can research to find out how to build more complicated functions and processes in Google Sheets.

Check out the video below!

00:00 Intro
00:27 The help menu
01:13 Keyboard shortcuts
01:30 The help search bar
02:08 The Sheets Help tool
02:44 Understanding the help tool for functions
04:03 Preparing complex formulas with the Function List
05:14 How to ask the right questions

This is the last video in the Google Sheets beginners Youtube course. This is the first online course I have created. It was a long and rewarding journey. I’ve learned a lot along the way and I hope you got a lot out of the course.

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

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

Google Sheets Beginners: Understanding The Explore Tool (43)

The Google Sheets team seem to come out with more and more great tools to help us do more, help us think creatively about how we display our data and improve our efficiency.

On the wave of machine learning, it seems that the Google Sheets devs are making our lives just that little bit easier with the Explore Tool.

You can find the explore tool down the bottom right of your Google Sheets. The tool strives to do a number of things:

  • Bridge the gap, and considerable learning curve, by providing some natural language support for asking questions about your data in Google Sheets.
  • Create a constantly improving list of styling, chart and pivot table suggestions that you can simply click an add to your own project.
  • Provide inspiration for you to think about how to best present your data in a clear and informative way.

Check out the video!

00:00 Intro
00:16 Features of the Explore tool
00:30 The first Example
00:42 Understanding the Explore Menu
02:00 Asking Explore questions
04:06 The second Example
07:46 My opinion of the current state of the Explore Tool

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

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

%d bloggers like this: