What are the best productivity hacks, tips, tools or resources for using Google Spreadsheets?

Posted by:

What are the best productivity hacks, tips, tools or resources for using Google Spreadsheets?

Here’s my list of resources for using Google Spreadsheets that will help you to be more productive:

1 – Keyboard shortcuts

You may know a few shortcuts like copy and paste (Ctrl + C, Ctrl + V) but there are more powerful commands at your fingertips. Shortcuts that let you move to the next sheet and change data formats, for example.

Locate the ‘Help’ menu to learn a few more or check out this list of keyboard shortcuts.

resources for using Google Spreadsheets

2 – Add Images to cells

resources for using Google Spreadsheets

To place an image straight into a cell type =image(“URL of the image you want to add”). There are a few options for inserting and formatting images:

  • scaled to fit the cell
  • stretched to fit the cell
  • keeps its original size
  • in a custom size

3 – Create live charts and publish on the web

Infogrames integration with Google Sheets automates the process of adding new data to a chart by updating the data periodically. It is simple to set up, so you might want to try it when designing your next interactive chart or online report. View the tutorial here.

resources for using Google Spreadsheets

4 – Use SQL commands and the query function

Take advantage of the QUERY( ) function in Google Sheets to start treating your tables like databases where you can retrieve any figure based on SQL code.

Query offers all the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER.

resources for using Google Spreadsheets

5 – Extract only the date portion of a timestamp

If you have timestamps in your spreadsheet there is an easy way to remove the time data. Use the INT( ) function to extract only the date portion of a timestamp.

resources for using Google Spreadsheets

6 – Create QR codes

QR codes can be used for a number of reasons – including WiFi login, concert tickets, advertisements, and product purchases. Google Sheets lets you generate QR codes with any input you like. Just use the following formula:

  1. =image(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&A2).

A2 is the cell with the URL or text you want to use to create your QR code.

resources for using Google Spreadsheets

7 – Lookup values based on multiple criteria

You may be aware of lookup functions (VLOOKUP or INDEX/MATCH) that allow you to search based on one term. If you want to search using multiple criteria you can use the ARRAYFORMULA function. Here’s an example, using this formula:

  1. =ARRAYFORMULA(index($E$4:$E$8,match($G4&$H4&$I$3,$B$4:$B$8&$C$4:$C$8&$D$4:$D8,0)))
resources for using Google Spreadsheets

The diagram below explains how the formula works. Learn more here.

resources for using Google Spreadsheets

8 – Use the FILTER() function

The FILTER function allows you to easily return values from a column that satisfy certain conditions. FILTER can only be used to filter rows or columns at one time.

The syntax is =FILTER(“list of values”, “conditions we’re testing”).

resources for using Google Spreadsheets

9 – Get only unique values from a column

Use the function UNIQUE( ) to get a list of unique values from a range or column.

resources for using Google Spreadsheets

10 – Web scraping with Google Sheets

You can access data from a website in your spreadsheet without having to copy and paste using the IMPORTHTML( ) or IMPORTXML( ) functions.

Copy the formula below into A1 and you’ll see the same data as the image below:

=IMPORTHTML(“https://en.wikipedia.org/wiki/Li…)

resources for using Google Spreadsheets

11 – Pick randomly from a list

resources for using Google Spreadsheets

The functions CHOOSE( ) AND RANDBETWEEN( ) help you pick a value from a defined list. RANDBETWEEN( ) generates a random integer between 1 and 5. CHOOSE( ) helps you pick from the list of entries.

  1. =CHOOSE(RANDBETWEEN(1,5),”London”,”Berlin”,”Rome”,”Madrid”,”Lisbon”)

12 – Make your formulas easier to read

Have a long formula? Break it into easy to read lines using the command ALT + Enter.

Before using the formula:

=IF(( SUMIF(A2:A20,”SOLD”,B2:B20)) > 2000,”More than $1000 SOLD”,”Less than $100 SOLD”)

After using ALT + Enter:

=IF( ( SUMIF(A2:A20,”SOLD”,B2:B20)) > 2000,

“More than $2000 SOLD”,

“Less than $200 SOLD” )

13 – Detect and Translate a language

Google can detect a language but also translate from one language to another.

Use the formula DETECTLANGUAGE( ) and it will return a two-letter language code. The full list of language codes can be found here.

resources for using Google Spreadsheets

The formula GOOGLE TRANSLATE( ) has three parts – text to translate, the current language of the text, and the language you want to translate into.

resources for using Google Spreadsheets

14 – Check for valid emails and URLs

The function IS EMAIL( ) checks to see if a string of text or cell has a valid email syntax.

resources for using Google Spreadsheets

The function ISURL( ) returns true or false if a string of text or cell has a valid URL.

resources for using Google Spreadsheets

15 – Project future results with GROWTH( )

The GROWTH( ) function can be used to extrapolate a trend and predict future values.

The image below shows the sales of a product over 5 periods. With the formula =growth(B1: B5, A1: A5, A7: A9) you can estimate what the values would be for 6 – 8.

resources for using Google Spreadsheets
I hope these hacks have been proved helpful for you.
0

Add a Comment