Spreadsheets

|

November 4, 2020

32 Essential Google Sheets Tips & Tricks [2020 Edition]

Google Sheets screen with sticky notes and coffee mug in foreground
SECTIONS
  1. Formatting the Content 
  2. Spicing Up the Spreadsheet
  3. Using Shortcuts to Work Faster
  4. Using Special Formulas 
  5. Expanding the Capabilities of the Spreadsheet
  6. Collaborating through the Spreadsheet
  7. An Additional Resource
SECTIONS
  1. Formatting the Content 
  2. Spicing Up the Spreadsheet
  3. Using Shortcuts to Work Faster
  4. Using Special Formulas 
  5. Expanding the Capabilities of the Spreadsheet
  6. Collaborating through the Spreadsheet
  7. An Additional Resource

Even if you regularly use spreadsheets, you might not be familiar with the breadth and depth of their capabilities that they can give you. This rings especially true with Google Sheets!

Google has been developing Google Sheets as part of its G Suite since 2006; and with today’s need for quick and easy ways of collaborating across the world, Google Sheets is an attractive solution to quickly analyze and process data. 

In this list, I summarize the tips and tricks you must learn today to quickly boost your mastery of Google Sheets.

Are you ready?

Formatting the Content

1. Quickly change number format

Typing a number that’s supposed to be a currency or a percentage? You can quickly change the number format of a cell by selecting it and then going to the main toolbar. Look for the following symbols:

The number of formatting symbols in the toolbar, highlighted.
The number of formatting symbols in the toolbar, highlighted.


Click the dollar symbol to change the number format to currency, automatically inserting a currency symbol and adding (or reducing) the decimal digits to two digits. 

Click on the percentage symbol to change the number format to percentage. Make sure the percentage is written in decimal form before doing this action.

2. Display formulas as text strings

Say you'd like to show a formula on your sheet to explain what you're doing in another cell... once you type it in, however, Google Sheets automatically runs it as an actual formula. What do you do now?

A simple trick to enter the formulas without Google Sheets processing them is by inserting an apostrophe ‘ before typing it. This tells Google Sheets to preserve the succeeding characters into a string.

A cell displaying a formula selected, with the content displayed on the formula bar. An apostrophe precedes the formula. 
A cell displaying a formula selected, with the content displayed on the formula bar. An apostrophe precedes the formula. 


3. Convert a number to a string

Sometimes it is more convenient to process the number as a string. To do so, use the TEXT() function to reference the cell containing the number or simply type it inside the TEXT() function. 


4. Wrap text in certain cells

If the cell contains a string that does not fit its size, you can make the text wrap inside the column by clicking Format on the main menu and then clicking Text wrapping in the drop-down menu. Another smaller drop-down menu will appear. Select Wrap

Text wrapping options: overflow, wrap, and clip. Overflow is set as the default option.
Text wrapping options: overflow, wrap, and clip. Overflow is set as the default option.


5. Use Google Sheets themes

If you want to make your spreadsheet more visually appealing, Google Sheets has a set of themes you can apply. 

To access the themes, click Format on the main menu. The Themes option is listed at the top of the drop-down list. A sidebar on the right side of the spreadsheet will appear, where you can choose the theme to apply. 

6. Add borders to cells

Besides adding fill colors and adding fonts and font styles, you can also add borders with almost the same functionality and flexibility as that of Microsoft Excel. You add borders by selecting the range of cells and then choosing the side or sides of the cells where you want to add a border. 

Border colors and styles can also be customized, so you can have dotted red borders or dashed blue borders.

Border options displayed after clicking the border symbol (a square box with four divisions). This includes options for which specific sides will be bordered and what border color and border style to apply. 
Border options displayed after clicking the border symbol (a square box with four divisions).


Spicing Up the Spreadsheet

7. Add checkboxes to the spreadsheet

Sometimes you have to enter entries where the value is either TRUE or FALSE. You can make your job easier by changing the cells to checkboxes

To do so, highlight the cells or columns where checkboxes are to be added, then click Insert in the main menu, and then click Checkbox on the drop-down list.

8. Limit choices by adding a drop-down box

If the entries in a certain column or columns in your spreadsheet would have a limited number of choices, you can limit the possible choices and save more time by formatting them into drop-down boxes

Click Data on the main menu, then click Data validation. A pop-up box will appear, where you can set the list of items that should be chosen.

9. Lock the header row at the top

When reading a very long spreadsheet, it is often easy to forget what the individual columns are for. Fortunately, it is possible to lock the header row in Google Sheets

Simply drag down the thick gray bar in the upper-left corner of the spreadsheet until it is near the bottom of Row 1.

The thick gray line used to freeze/lock the top rows highlighted. 
The thick gray line used to freeze/lock the top rows highlighted. 


10. Apply conditional formatting to highlight cells that fulfilled a condition

It is much easier to see which cells have fulfilled a certain condition if they are filled with a certain shade of color. To do so, we need to apply conditional formatting. Click Format on the main menu, then click Conditional formatting on the drop-down menu. 

A sidebar will appear on the right side of the spreadsheet where you can include the condition that needs to be checked. 

The Conditional formatting option from the Format menu highlighted.
The Conditional formatting option from the Format menu highlighted.


To learn more, read the following tutorial on conditional formatting: How to Use Conditional Formatting Based on Another Cell in Google Sheets 

11. Take advantage of the Slicer feature

The new Slicer feature by Google Sheets allows you to filter a long sheet to see entries that you are looking for. The Slicer automatically analyzes the sheet for common patterns in the entries and uses them as potential filters for slicing the data, with the relevant entries remaining.

To learn more, you can check our tutorial here: How to Use the Google Sheets Slicer: Filtering Data by Value or Condition 

A Slicer widget on a sheet. The Slicer is set to filter the rows by its value under the date column.
A Slicer widget on a sheet. The Slicer is set to filter the rows by its value under the date column.


12. Summarize data using Pivot Tables

A handy tool to summarize the data in a large spreadsheet is by using the Pivot Table feature of Google Sheets. Click on any cell containing the relevant data, then click Data on the main menu then click Pivot table in the drop-down menu. 

A pop-up box will appear, asking you whether to add the Pivot Table in the new sheet or insert it in the existing sheet. A pivot table will now be produced, where you can select which values to display and aggregate. 

A Pivot Table summarizing the total amount of items and profit per date. 
A Pivot Table summarizing the total amount of items and profit per date. 


You can learn more in our in-depth guide: Jumpstarting Data Analysis with Pivot Tables

Using Shortcuts to Work Faster

13. Show formulas instead of results

If you want to check the consistency of the formulas entered in the spreadsheet without modifying the spreadsheet, click View in the main menu and then click Show formula in the drop-down menu. The formulas will be shown instead of the results.

The Show formulas option from the View menu highlighted. 
The Show formulas option from the View menu highlighted. 


14. Ensure that spelling is correct

Google Sheets allows you to check the spelling of the words. Click Tools on the main menu, then look for Spelling. Click on it to find Spell check. Click Spell check and Google Sheets will automatically check the spelling of the words in the sheet.

Tools menu clicked, Spelling option selected, Spell check highlighted. 
Tools menu clicked > Spelling option selected > Spell check highlighted 


15. Paste the values instead of the formulas

Often we need to copy the resulting values of an array of formulas to another sheet. To paste the values instead of the formulas, use the keyboard shortcut Ctrl+Shift+V

Another way is to right-click on the cell where you want to paste the values, then click Paste special, then click Paste values only.

Paste special options listed. The “Paste values only” option is on the top of the list. 
Paste special options listed. The “Paste values only” option is on the top of the list. 


16. Copy an array, then paste it with rows and columns flipped

If you have an array that you need to copy, but with rows and columns flipped, simply copy the array, then right-click on the first cell where you want to paste the array. Click Paste special, and then click Paste transposed on the list of options that will appear on the right side (or sometimes left side).

Paste special options listed. The “Paste transposed” option is on the bottom of the list. 
Paste special options listed. The “Paste transposed” option is on the bottom of the list. 

17. Copy the formatting of one cell to another cell

It is also possible to copy the formatting of one cell to another cell without changing the data in the destination cell. Copy the cell containing the desired format, then click on the destination cell, then do the keyboard shortcut Ctrl+Alt+V.

18. Quickly check how a formula works

There is a trick for situations where you know the formula to use but you suddenly forgot its syntax. When you type a formula in Google Sheets, a small blue box with a question mark will appear on the formula bar. Click on it to see a short description of the formula including how the input should be written.

A quick guide to the formula entered into the formula bar. The quick guide appears after clicking the small box with a question mark that appears when you complete the name of the formula without the input arguments.
A quick guide to the formula entered into the formula bar. The quick guide appears after clicking the small box with a question mark that appears when you complete the name of the formula without the input arguments.


19. Access a list of keyboard shortcuts

You can access a list of keyboard shortcuts while using Google Sheets by pressing Ctrl+/. This will open a window containing the keyboard shortcuts grouped by category.

The Keyboard shortcuts window containing the keyboard shortcuts for Google Sheets. 
The Keyboard shortcuts window containing the keyboard shortcuts for Google Sheets. 

Using Special Formulas

20. Check if the cell is really blank

Characters that do not show up in the spreadsheet, such as apostrophes and stray spaces can mess up the results of the COUNTA() function which you may use to check the number of cells with existing entries. The solution is to use ISBLANK() function to spot which cells contain the offending characters.

21. Translate words using Google Translate

Google Sheets has a built-in GOOGLETRANSLATE() function that you could use to translate simple words from an original language to a certain target language. Specify the cell containing the text, and the original and the target language. 

English words “nice”, “money”, and “profit”, translated to Korean, Japanese, and French, respectively, by using the GOOGLETRANSLATE() function. 
English words “nice”, “money”, and “profit”, translated to Korean, Japanese, and French, respectively, by using the GOOGLETRANSLATE() function. 


22. Verify if the text is in a valid email address format

If a certain column is designated for containing email addresses, you can use the ISEMAIL() function to check if the syntax of the email is in a valid email address format. This will help you filter out the obvious responses that do not fit the syntax of a valid email address.

23. Insert links to external sites

To insert links to external sites, Google Sheets has a built-in HYPERLINK() function. The syntax is as follows:

=HYPERLINK(“<url>”,”<label>”)</label></url>

For our example below, we used the following syntax:

=hyperlink("www.lido.app","Lido App")

And we got the following result:

A hyperlink added in a cell using HYPERLINK() function. 


24. Properly capitalize names

Google Sheets has a built-in function called PROPER() that you can use to properly capitalize names. It does not just capitalize the first letters of the names but also changes to lowercase the letters that are not the first letters of the names.

An improperly capitalized name is properly capitalized using the PROPER() function.


25. List down unique entries in a selected array

You can use the UNIQUE() function to list down all unique entries in a selected column. Type in the function, and then select the column you want to filter.

The unique entries in a selected column with repeating entries is listed in the next column using the UNIQUE() function. 
The unique entries in a selected column with repeating entries is listed in the next column using the UNIQUE() function. 


Expanding the Capabilities of the Spreadsheet

26. Add Custom Functions via the Script Editor

Are the current functions not enough for what you want to do? Google Sheets allows you to add custom functions through its Script Editor. 

The Script Editor allows you to add a JavaScript-based code that defines a custom function. You can access the Script Editor by clicking Tools and then selecting Script editor. The Script Editor will load in a new tab or window. 

The Script editor, where you can add JavaScript-based functions to add functionality to your spreadsheet. 
The Script editor, where you can add JavaScript-based functions to add functionality to your spreadsheet. 

One example of that is a code that imports and processes JSON files. For that, you can read our tutorial here: SQL + Google Sheets? How to Export SQL Data to Google Sheets AND Update Export Automatically 

27. Use Google Sheets Add-ons

If you don’t want to learn JavaScript, you can always look for Google Sheet add-ons by clicking Add-ons in the main menu and then clicking Get add-ons. A box will load listing the available add-ons. You can also search for a specific add-on that you want to use. 

The Add-ons library. It contains a search bar at the top of the library to help you look for the add-on you want. 
The Add-ons library. It contains a search bar at the top of the library to help you look for the add-on you want. 


Collaborating through the Spreadsheet

28. Copy a specific sheet to a new spreadsheet

If you want to copy a specific sheet to a new spreadsheet (for example, to preserve the data at the moment), just right-click on the tab of the sheet on the bottom of Google Sheets. A list of options will appear. Click Copy to, and select New spreadsheet

A selected tab right-clicked. A list of options appears; the Copy to option selected. The New spreadsheet highlighted. 

29. Revert to a previous version of the spreadsheet

Google Sheets allows you to revert to a previous version of the spreadsheet seamlessly. To do so, click on the link on the upper part of the Google Sheets that start with “Last edit was…” 

A portion of the Google Sheets page, focused on the “Last edit was 2 days ago” link at the top-center portion of the page. 
A portion of the Google Sheets page, focused on the “Last edit was 2 days ago” link at the top-center portion of the page. 


This will bring you to a page with a right sidebar listing the previous versions of the spreadsheet while displaying it on the left side of the page. 

The Version history page of Google Sheets. On the right sidebar is the list of versions of the spreadsheet. The highlighted version is displayed on the left side of the page.
The Version history page of Google Sheets. On the right sidebar is the list of versions of the spreadsheet. The highlighted version is displayed on the left side of the page.


30. See edit history

Google Sheets also includes a way to see the edit history for an individual cell. Right-click on the target cell, then select Edit History. A small box will appear listing down each edit done, with a set of arrows on the top for going back and forth through the edit history.

Edit history box of a specific cell shown. 
Edit history box of a specific cell shown. 

31. Add comments

If you are reviewing a sheet and want to place comments on certain cells without changing the content (or you don’t have access rights to outright edit them), you can use the comments feature of Google Sheets. Select a cell where you want to place a comment, then press Ctrl+Alt+M

A small window will appear where you can write your comment. It will appear alongside the name on the email address you used in adding the comment. 

The comment box where you can enter comments to a specific cell. 
The comment box where you can enter comments to a specific cell. 


32. Share the spreadsheet with other users

To share the spreadsheet for real-time collaboration, click the Share button on the upper-right corner of the Google Sheets. A pop-up box will load where you can enter the email addresses of the members of your team who need to access the spreadsheet for collaboration. 

The Share with people and groups box. A space is added where you can add users by typing either their email addresses or their names if you regularly correspond with them. 
Where you can add users by typing either their email addresses or their names if you regularly correspond with them. 


Once you enter the email address, you can choose where the user can either just view, comment, or outright edit the spreadsheet. You can also add a custom message alongside giving the user access as Google Sheets will send an email to that user to notify them that they have access rights to the spreadsheet you are using.

After entering an email address, You can add a custom message before sending them.
After entering an email address, You can add a custom message before sending them.


An Additional Resource

Impressed? We've got more!

Just visit our Resources Library for in-depth tutorials and blog posts on Google Sheets. See you there!

SECTIONS
  1. Formatting the Content 
  2. Spicing Up the Spreadsheet
  3. Using Shortcuts to Work Faster
  4. Using Special Formulas 
  5. Expanding the Capabilities of the Spreadsheet
  6. Collaborating through the Spreadsheet
  7. An Additional Resource

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.