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.
2 – Add Images to cells
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 – Createand 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.
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.
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.
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:
A2 is the cell with the URL or text you want to use to create your QR code.
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:
The diagram below explains how the formula works. Learn more.
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”).
9 – Get only unique values from a column
Use the function UNIQUE( ) to get a list of unique values from a range or column.
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:
11 – Pick randomly from a list
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.
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.
The formula GOOGLE TRANSLATE( ) has three parts – text to translate, the current language of the text, and the language you want to translate into.
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.
The function ISURL( ) returns true or false if a string of text or cell has a valid URL.
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.