My favourite Killjoy members. Column A – From most favourite. Column B Saving the best for last.
Sometimes you have a need to reverse a list quickly in Google Sheets.
That’s pretty easy to do if the list is sorted alphabetically. Just go on into the <Data> menu and choose from one of the sort functions. But what if the data you want to flip is not in alphabetical or numeric order?
(Select both the image and the Google Sheet, right-click and select ‘Make a copy’)
The basic pattern is as follows:
=HYPERLINK("URL - Link",IMAGE("URL to your image"))
Working from the inside out, the IMAGE function requires a link to the image that is being used. There are a number of ways of getting this link that I will cover in a moment.
The HYPERLINK Google Sheets function takes the destination link as its first argument and then and then a link label as its second argument. Generally, this argument is text, but we can coerce this label into an image by replacing it with our IMAGE function.
But how do we get the image URL?
Image from an external website
If you store your images on a personal website or from some online photo hosting sites you can get the link to the image.
In the example below, I have a link from my website to my image. I want to use that image as my URL label. This is what my image link would look like:
Unfortunately, we can’t just go to Insert > Image > Insert image in cell for this. Nor can we go to our Google drive, select the image share it and get the URL from the share.
It won’t work.
There are a couple of workarounds for this, but probably the easiest approach is to open the image in a drawing.
Here is how we do it:
Navigate to the Google Drive folder where your image is saved.
In a blank space in the folder right-click > More… > Google Drawings
Click to Expand!
Inside Google Drawings select Insert > Image > Drive.
A sidebar will appear. Navigate to your image and double-click it. It will load into your drawing.
Holding the left mouse button down, drag the image up to the top-left corner of the drawing.
You will notice a checkered grey and white area in the background, this is the page area. We want the page area to be equal to the image. In the bottom right of the page area, there is a little move triangle-shaped button click and drag it up to match the size of the image.
Click to expand
In the top right of the Drawing app, rename it by clicking on the top-left text where it says “Untitled”. Make sure it is something meaningful for your task.
Now we get the URL for the image. Select File > Share with others > Publish to the web.
A dialogue box will appear. Make sure Link is selected, choose your preferred size and then select Publish.
You will get an alert that comes down from the top of the page making sure you want to publish this image. Select Ok.
You will now have a Google Drawing URL. Hit Ctrl + C (⌘ + C) to copy it.
Head back to your Google Sheet and insert it into IMAGE function. It should look similar to this:
Another approach to adding links to images in Google Sheets that you may want to consider is to add a URL to a cell that you want to use for your image and then insert an image over cells. Then move that image so that it fits inside the cell with the URL.
When a user clicks or hovers over the cell and image the link will appear.
Here are the detailed steps:
Select a cell. If you want something larger, you may have to merge cells together to make it just right.
Add your URL to the selected cell.
Change the text colour of the cell to match the background colour. This will hide the URL just in case you didn’t fit your image in perfectly.
Now go to Insert > Image > Insert image in cell
Select your image from your desired location.
One the image appears in the Sheet, move the image to the cell with the link and resize it so that it fits just inside the cell. Note! You should leave a tiny bit of space from the edges of the cell so that the URL is picked up.
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.
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
Table of Contents
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.
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:
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 Ge
~Yagi
Changelog
2022-07-16: Added two videos. One for each section of the tutorial.
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!
Calculating the duration of time between two times is a pretty common task. From measuring how long a process took, to entering the hours you put into a project, to creating a timesheet. You will find it everywhere, so working out duration is a hand skill to have in Google Sheets.
However, there are some gotchas when calculating the duration of time. So it is worth a good looking into.
To get a feel of how calculating durations work out in the wild, you can check out the video below for a great example on how I use it to build a Project Task Google Sheets workbook.
