1. Conditional Formatting

Conditional formatting lets you format your cells based on criteria in the cells.

In this example, we'll highlight the cell in green if the date is today.

First, you highlight you cell group, right click, and click "Conditional Formatting".

Then, you use the formatting rules to format cells if the date is today.

In the first screenshot, we're getting to the conditional formatting settings.

‍

In this screenshot, we're setting the formatting rules.

‍

2. Lookup Sheet History

Sheet history lets you see everything that's been edited and who has edited it on your sheet. It also lets you roll back to previous versions of the spreadsheet if a mistake was made.

To get there, just click on the "Last edit was ____ minutes ago" text, to open up the screenshot below.

Click into version history on the right side to roll back to previous versions and see who's been editing the sheet.

‍

3. SUMIF

SUMIF is a super useful formula which lets you sum up data based on criteria.

Here's how it works: 

First, we highlight the Criteria Range. In our case, we're summing up all data greater than $1 (which is what ">1" is in reference to).

Last, we highlight the Sum Range. In this example, it's the same as the Criteria Range.

If our criteria was to sum up all transactions that were "Milk", we would change the formula in the screenshot to: 

=sumif(A2:A4, "Milk", B2:B4)

‍

4. Concatenate

Concatenating is a really useful text function. One great use case is to join a first and last name in two columns into one column.

Here, you can see we're joining the text in cell A1 and B1, and adding a space with " " in the middle. If we didn't add the space, the result would be JohnSmith, instead of John Smith.

‍

5. Import from another sheet (IMPORTRANGE)

IMPORTRANGE is super helpful if you have a second spreadsheet that you want to pull information in to.

It's as simple as copying in the URL (make sure you have quotes around it!), and selecting the range you want to import it. You also have to be sure to follow the exact formatting Google Sheets has in the preview of the formula for the range to pull in correctly.

‍

‍

6. IFERROR

IFERROR is when working with complex formulas, where sometimes the result will show up as "N/A". An example of this is if some items are dividing numbers by zero.

With IFERROR, you can show a different result of your choosing instead of an error.

All you have to do is wrap your formula in =IFERROR(). If you want the result to be blank on an error, just put "" as the "value if error"

‍

‍

7. Paste as value only

Pasting as value only is helpful if you have a list of items that you used a formula to calculate, but you want to paste JUST the results somewhere else on your sheet.

All you have to do is: 

1. Copy your cells like your normally would
2. Right click on the cell where you want to paste, go to "paste special", then click "Paste Values Only"

You can also use the shortcut shown in the screenshot to paste values only.

‍

8. Random function

This random function creates a random number between any two numbers (in this case, 1 and 100). Perfect for picking contest winners!

‍

9. Macros

Macros are a more complex feature of Google Sheets that lets you record a set of actions (such as applying formatting) and then saving it to run anytime you want.

This is perfect for saving time on tedious tasks.

If you'd like to learn more about Macros, check out our in-depth Google Sheets course.

‍

‍

10. Removing duplicates

If you have a large list of data, you can easily remove duplicates! Just got to data -> remove duplicates to make it happen in one click.

‍

11. Connect Google Sheets to Zapier

By connecting Google Sheets to Zapier, you can add rows to Google Sheets when triggers occur in other apps, or you can send data from Google Sheets to another app.

Below are popular ways to use Google Sheets workflows from Zapier's website.

Zapier turns your Google Sheet into a database.

‍

12. Build an app using Google Sheets

By using Glide Apps, you can turn your spreadsheet into a fully functioning app like Zillow, Tinder, Yelp, and much more.

Check out our tutorials on Glide Apps to learn more about what's possible.

‍

13. Split text

This is the opposite of "Concatenate" from earlier! 

With this formula, the second input is what you will split the text on. In this case, we split it on the space between the names.

14. Get Crypto Data

Did you know you can pull in live crypto data into Google Sheets? 

Here's a formula to pull in the live Bitcoin price: 

=GoogleFinance("CURRENCY:BTCUSD")

At the time of writing, the price is $63,179!‍

‍

15. Use Add-ons

There's a lot of functionality you can add to Google Sheets using Add-ons!

Just click "Add-ons" on the top of the sheet to open up the marketplace of add-ons.

You can do cool things from Sheets like send emails, create quizzes, and more. It's worth checking out!

‍

16. Create QR Codes

This is a very cool feature!

Use the following formula to automatically create a QR code (in this case, A15 is the cell I'm creating a QR code of).

=IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&choe=UTF-8&chl="&ENCODEURL(A15))

This QR code should take you to apple.com.

‍

17. Use Shortcuts

Using shortcuts is a great way to save time when navigating Google Sheets.

The most common is probably Command+V / Command+C (or Control+V / Control+C for PCs) to copy and paste, but there are many more!

Below are some common shortcuts Google Sheets offers. Below are Mac shortcuts, to view on PC as well go to this link.

18. Array Formulas

Using array formulas are an advanced technique which allows you to write one formula that automatically runs on every cell in an array (an array is an area of cells, such as A1:A10).

Below is an array formula in cell D2, which is calculating the length of the words in column A.

As you can see, you only need to write the formula in cell D2, and all the cells in column D are automatically filled in with the correct length.

‍

19. Currency Format

This is a simple trick to automatically make your numbers a currency! 

Just hit the dollar sign (below the word "format" up on the toolbar).

‍

20. Remove extra decimal points

Refer to the above screenshot, and on the top right you'll see a zero with an arrow pointing left.

If you hit that button while you've highlighted a group of cells, you'll remove decimal points, which is very helpful to clean up data visually.

You can also use the button next to it on the right (cut off in the above screenshot) which will add decimal places.

‍

21. Remove gridlines

This is a quick formatting trick!

Go to View -> Gridlines in the tool bar while you have highlighted over cells on your sheet (click the empty box next to "1" and "A" to highlight every cell.

If you uncheck Gridlines, all gridlines will be removed, making your sheet a bit cleaner to look at visually.

‍

22. Freeze rows and columns

If you're not freezing rows and columns on your sheet, you should start now!

This is great if you have a lot of data and want your labels in the top row to stay at the top, so you can see what the data is referring to.

Just go to View -> Freeze -> Select your freezing options

‍

‍

23. Create drop downs

This is easier than it looks, I promise! 

First, create a list of options on your spreadsheet.

In our case, I've created a list of grocery options (Bread, Milk, Cheese) in cells A2:A4.

Then go to another cell where you want the dropdown to be, and then on the top tailback go to Data -> Data Validation.

Make sure the criteria is "List from a Range", and then just highlight your range or type it in (in our case, A2:A4).

Click save, and you are good to go!

Here is our dropdown in action!

‍

24.Transpose data

Last trick for now!

This is a super useful formula that lets you turn a column of data into a row (or vise-versa).

In our example, =transpose(A2:A4) turns the grocery items from a column to a row.
‍