Google Sheets Tips: New Google Spreadsheet Hacks, Tricks with Examples

Google Sheets Tips: Even if you use spreadsheets regularly, you may not be aware of the width and depth of their capabilities. This is true with Google Sheets in particular! Google has been developing Google Sheets since 2006 as part of its Google Workspace formerly known as G-Suite. Today, Google Sheets is an attractive solution to rapidly analyze and treat data, because it requires quick and easy collaboration worlds.

On this page, we will summarize a list of tips and tricks you must learn today to increase your Google Sheets skills quickly. Scroll down and explore the hacks to use the Google Spreadsheet like a pro!!

Latest Google Sheets Tips

Edit & Format a Spreadsheet

Use functions & formulas

Work with Data

Sort, filter, or format data

Create and Edit Charts

Print or Publish a spreadsheet

Shortcuts & Troubleshooting

Improve Your Sheets Performance

  • Learn more about Import functions
  • Learn how to improve Sheets performance
  • Optimize your data references to improve Sheets performance

Tools

Use BigQuery data in Google Sheets

  • Get started with BigQuery data in Google Sheets
  • Sort & filter BigQuery data in Google Sheets
  • Analyze & refresh BigQuery data in Google Sheets using Connected Sheets
  • Write & edit a query
  • Fix problems with BigQuery data in Google Sheets
 

Google Sheets Tips and Tricks

Python OpenPyxl

Python XlsxWriter

Python – Google Sheets – Spreadsheets

MS Excel

Excel R

Java Apache POI Excel

Send Emails When You Comment In Google Sheet

Google Sheets are great for collaborating in real-time. Any individual can easily send an email to the concerned person just with the help of their recipient’s email id. All you have to do is, just insert a plus sign (+), then type the e-mail address (or name) and the recipients will receive an e-mail automatically when you insert your comment. Also, you can make use of the “@” symbol to send an email.

Refer to the steps given below, where you will find an example of how to send an email through google sheet:

  • Step 1: Open the google sheet and select the cell where you have to write a mail.
  • Step 2: Right-click and select comment from the drop-down menu.
how to send mail in google sheet
  • Step 3: Now either use the “+” or “@” symbol and type the email id as shown in the image given below.
  • Step 4: Type the email and click on assign. The email will be sent.
sending email through google sheets

Add Heatmaps Using Conditional Formatting

Heatmaps are a great way to highlight important information on your Google sheet. You can use conditional formatting to highlight specific values, outliers, or errors. The function of a colour scale enables you to highlight lower and higher values quickly in your data.

Follow the steps listed below to add heatmaps using conditional formatting.

  • Step 1: Click on the “Format” and select “Conditional Formatting” from the drop-down menu.
  • Step 2: A new window will open towards the right side of the screen.
  • Step 3: Select the “Apply To Range” under “Color Scale“.
  • Step 4: Now under format rules, Select the value.
  • Step 5: Enter the “Mid Point value, Max point value, Min Point Value“.
  • Step 6: Click on “Done“
Add Heatmaps Using Conditional Formatting

Apply Filters In Google Sheets

You can only display interest rows within the sheet by using filters. This can be helpful if you work with a wider range of data. Follow the steps listed below to apply filters in the sheet.

  • Step 1: Select the column on which you will have to apply the filter.
  • Step 2: Click on the filter icon as shown in the image below.
  • Step 3: Now you will have the option to choose your desired filter by selecting the required dataset and so on.
  • Step 4: Also you will have options to filter by colour, values and conditions.
  • Step 5: After choosing the desired filter click on “OK“.
  • Step 6: The filter will be applied to the sheet.
apply filters in google sheets

Clean Up Values with CLEAN and TRIM

TRIM function is used to clean or remove from the cell start and from the ends. Follow the steps listed below to make use of the CLEAN and TRIM functions in Google Sheets.

  • Step 1: Select the columns where you will have to remove the whitespaces.
  • Step 2: Click on the “Data” tab and select “Trim Whitespaces” from the drop-down menu.
  • Step 3: Now you will get a message saying that “Trimmed Whitespace from (No) selected rows“.
  • Step 4: Click on “OK“.
Clean Up Values with CLEAN and TRIM

Another way to trim the whitespace is to simply select the cells and type the formula – “=TRIM(A2)” and the whitespace will be cleaned.

Note: A2 is the cell that we have given here for reference.

Protect Data in Cells in Google Sheet

You might want to limit data to prevent mistakes if a lot of people work on a sheet. You can also protect sheets and cells so that information is not changed by accident. Follow the steps listed below to protect the data in cells in Google Sheets.

  • Step 1: Select the columns and click on the “Data“.
  • Step 2: Now, scroll down and select “Protected Sheets and Ranges“.
  • Step 3: A new window will open towards the right side of the screen.
Protect Data in Cells in Google Sheet
  • Step 4: Now select the “Range“.
  • Step 5: Click on “Set Permissions“.
  • Step 6: Now edit the permissions as per your requirements.
Protect Data in Cells in Google Sheet

Validate Data in Cells

By applying data validation to your cells on Google sheet, you can ensure that certain cells only contain selected data. For instance, validation can be set to include numbers or even a value in a defined list in certain cells only. A drop-down selector is also made available in the sheet by defining a predefined list of values.

Follow the steps listed below to validate cells using data validation.

  • Step 1: Select the cells and click on the “Data” tab.
  • Step 2: Now select the “Data Validation” from the drop-down menu.
  • Step 3: Validate the selected cells as per your requirements.
  • Step 4: Once validation is done, click on “Save“.
Validate Data in Cells

Integrate Google Sheet With Google Forms

Although it is one of the most powerful ways to optimise your forms to integrate your Google Form into Google Sheets. You will need to establish a form before you start feeding your Google Sheet information to automatically sync all of your information. It takes only a few minutes to establish your form.

From multiple selections, drop-downs, short answers, long responses, checkboxes, and more, choose different answers.

Integrate With Google Forms

Follow the steps listed below to integrate Google Sheets with Google Forms.

  • Step 1: Click the Responses tab.
  • Step 2: Click the Google Sheet icon now.
  • Step 3: Choose to create a new spreadsheet.
  • Step 4: Enter your sheet name.
  • Step 5: Click Create.

Insert a Chart from Google Sheets into a Google Doc

You can insert a chart into Google Docs when you have created a chart inside Google Sheets. Follow the steps listed below to insert a chart from Google Docs to Google Sheets.

  • Step 1: Select ‘Insert‘ in the Google document.
  • Step 2: Now select “Chart” and hit on “From sheets“.
  • Step 3: Now select the “Sheets” from your drive.
  • Step 4: Now click on “Select“.
  • Step 5: The chart will be displayed on the Google doc.

This saves you much time since you can reflect any modifications made in google sheets by updating the diagram in the document.

Insert a Chart from Google Sheets into a Google Doc

Import Data from a Website or RSS feed

You can import data from websites and RSS feeds via various features. The steps to import data on  Google Sheets are explained.

  • Step 1: Select the cell where you have to import the data.
  • Step 2: Now type the formula “=Import(function type)(URL)”.
  • Step 3: Click on “Enter“.
  • Step 4: The data will be imported on the sheet.

Various features to import data are:

  • ImportHTML tables and lists to import HTML
  • ImportFeed to import RSS feed
  • ImportData to import a CSV file on the web
  • ImportXML to import a customised section of an Xpath webpage.
Import data from a website or RSS feed

Change Capitalization in Cells

You can use the PROPER function to capitalise on the first letter in each word. This is useful if values are to be cleaned for consistency. Alternatively, the LOWER function can be used to reduce all letters. The steps to change the case are explained below:

  • Step 1: Select the cell where you have to make modifications.
  • Step 2: Now to capitalize, write the formula “=Upper(Cell Number)“.
  • Step 3: Click enter. The word or sentence will be capitalized.

Referto the image below to know the different types of cases used to represent a word.

Using-formulas-to-change-case-in-Google-Sheets

Also, one can make use of the Add-Ons function in google sheet, to change the case of an word.

Translate Text in Google Sheet

Below is the formula to translate the text,

=GOOLGETRANSLATE(“Text”, “Source Language”, “Destination Language”)

translate language in google sheet

Example:

Split Names and Other Data in Google Sheet

These are steps to split the full names into the first name and last name:

  • Step 1: Choose the data you would like to divide.
  • Step 2: Go to the Data tab.
  • Step 3: Click on “Split text to columns“.
  • Step 4: It divides the names in the first and last names immediately.
Split names and other data

Check for Valid Email Address

You can check it by using Google Sheets if you have a list of e-mails and you want to make certain that they have a valid e-mail address structure. It will not verify that your e-mails are delivered, but it will help identify all rebounding mail addresses (such as ‘@’ or ‘.com’ missing).

  • Step 1: Select the cells for which you have to validates the Email.
  • Step 2: Enter the formula “=ISEMAIL(Cell Number)“
  • Step 3: Click on “Enter“.
  • Step 4: The Email Address will be validated with True or False
Check for valid email addresses

Spell Check in Google Sheet

You can check the word spelling on Google Sheets. Click the Main Menu Tools and then search for Spelling. For a Spell check, click on it. Click on the Spell check and the spelling of words is checked by Google Sheets automatically.

Spell check on google sheet

Import Data from Other Sheets

You can simply import data from one sheet to another instead of maintaining data in multiple sheets. This also means that only one place (and not several sheets) needs to update your data, which can save time.

  1. Open the Google Sheets.
  2. Enter the formula, “=IMPORTRANGE” in a cell that is empty.
  3. In parenthesis, in quota marks and separated by a comma* add the following specifications:
    • The URL is in sheets of the table.
    • The name of the sheet and the cells to be imported are (optional).
  4. Press Enter.
  5. Click “Allow” access.

Refer to the image given below for example.

import data from other sheet

Visualize Data with a Sparkline

In order to quickly see trends in your data, you can easily add sparklines to your sheets.
This may be useful if you compare data or you want to turn your sheet into a dashboard, like comparing metrics, sales and so on. The steps to visualize the data with sparkline are given below:

  • Step 1: Select the cells.
  • Step 2: Enter the formula “=Sparkline(B2:B31)“
  • Step 3: Click on Enter.

You will see a chart showing the trends. Refer to the image given below for reference.

Visualize data with a sparkline

Create QR Codes

Barcodes are a useful way to monitor things, such as checking people for information, conferences or events. And in Google Sheets you can easily create QR codes. All you have to do is, select the cell and enter the formula. The formula to be entered has been provided in the sheet below:

Create QR codes using google sheet

Formula used to create QR code: =IMAGE(“https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=”&A1&””)

Check if the Cell is Really Blank

The results of a COUNTA() feature that you may use to control the number of cell with existing entries can be misleading characters that do not appear as apostrophic and stray spaces on the table. The solution is to use ISBLANK() to identify which cells have the attributes that offend.

Extend Google Sheets with Add-ons

You can easily add many features to Google Sheets, just by using the “Add-Ons” option. The steps to use add-ons on Google Sheets are explained below.

  • Step 1: Click on the “Add-ons” tab.
  • Step 2: Click on “Get add-ons“.
  • Step 3: You will be displayed with various add-ons tab.
  • Step 4: Select the Add-ons and click on “Install for Google Sheets“.
  • Step 5: The add-on will be enabled.
Extend Google Sheets with Add-ons

Quickly Learn Formulas

Google Sheets make it easy during work to learn formulas. You will see a handy reference when you begin typing a form that contains important information about the formula you are adding. For example, just start typing the formula = DATEDIF(and you will see this:

Quickly learn formulas

Keyboard Shortcuts for Google Sheets

Below provided Google sheets keyboard shortcuts make it easier to perform common actions, like copying and selecting the cells(rows and columns) in the Google sheets:

  1. Ctrl+C: Copies the selected row/column
  2. Ctrl+V: Pastes the selected row/column
  3. Ctrl+X: Cut/Deletes the selected row/column
  4. Ctrl+Shift+V: Pastes only the value of cell
  5. Ctrl+Space: Selects the whole column
  6. Shift+Space: Selects the whole row
  7. Ctrl+A: Selects entire sheet/cell
  8. Ctrl+Z: Undo an action
  9. Ctrl+Y: Redo an action
  10. Ctrl+F: Find in the sheet/cell
  11. Ctrl+H: Find and replace in the sheet/cell
  12. Shift+F11: Insert a new sheet
  13. Ctrl+Alt+Shift+H: Opens the history of the sheet

Below provided Google sheets keyboard shortcuts will help the user to format the cells easily,

  1. Ctrl+B: Bold
  2. Ctrl+I: Italicize
  3. Ctrl+U: Underline
  4. Ctrl+Shift+E : Center align
  5. Ctrl+Shift+L : Left align
  6. Ctrl+Shift+R : Right align
  7. Ctrl+; : Insert the Current date
  8. Alt+Shift+7: Apply the outer border to the selected row/column
  9. Alt+Shift+6: Removes the outer border for selected row /column
  10. Ctrl+Shift+1: Format as decimal
  11. Ctrl+Shift+2: Format as time
  12. Ctrl+Shift+3: Format as date
  13. Ctrl+Shift+4: Format as currency
  14. Ctrl+Shift+5: Format as percentage
  15. Ctrl+Shift+6: Format as the exponent
  16. Ctrl+\: Clear formatting from the selected cell

Below provided Google sheets keyboard shortcuts make it easier to move around the spreadsheet easily

  1. Left/Right/Up/Down Arrow: Move from one cell to another to the left, right, up and down
  2. Ctrl+Left/Right Arrow: Moves to first or last cell with the data in the row
  3. Ctrl+Up/Down Arrow: Moves to first or last cell with the data in the column
  4. Home: Moves to the beginning of a row
  5. End: Moves to end of the row
  6. Ctrl+Home: Moves to the beginning of the sheet
  7. Ctrl+End: Moves to the end of the sheet
  8. Ctrl+Backspace: Scroll back to the active cell
  9. Alt+Up/Down Arrow: if you have more than one sheet in the current file, use this shortcut to move to the next or previous sheet
  10. Alt+Shift+K: Display a list of all sheets
  11. Ctrl+Alt+Shift+M: Move focus out of the spreadsheet

Below provided Google sheets keyboard shortcuts make it easier to use and find the formulas applied on the sheet.

  1. Ctrl+~: Shows all formulas in the sheet
  2. Ctrl+Shift+Enter : Insert an array formula (type “=” first)
  3. F1: Full or compact formulas help
  4. F9: Toggle formula result previews

Below provided Google sheets keyboard shortcuts make it easier to Add or Change Rows and Columns eailsy.

  1. Cmd+D: Duplicate the data from the first column of the selected range down
  2. Cmd+R: Duplicate the data from the first row of the selected range to the right
  3. Cmd+Enter: Duplicate the data from the first cell of the selected range into the other cell
  4. Cmd+Option+9: Hide a row
  5. Cmd+Shift+9: Unhide a row
  6. Cmd+Option+0: Hide a column
  7. Cmd+Shift+0: Unhide a column
  8. Ctrl+Option+I, then R: Insert the row above
  9. Ctrl+Option+I, then W: Insert the row below
  10. Ctrl+Option+I, then C: Insert the columns to the left
  11. Ctrl+Option+I, then O: Insert the columns to the right
  12. Ctrl+Option+E, then D: Delete Rows
  13. Ctrl+Option+E, then E: Delete Columns

Below provided Google sheets keyboard shortcuts make it easier to access the menu easily

  1. Alt+F: Access the File menu
  2. Alt+E: Access the Edit menu
  3. Alt+V: Access the View menu
  4. Alt+I: Access the Insert menu
  5. Alt+O: Access the Format menu
  6. Alt+T: Access the Tools menu
  7. Alt+H: Access the Help menu
  8. Alt+A: Access the accessibility menu
  9. Shift+Right-click: This shows your browser context menu
  10. Ctrl+Shift+F: Switch to compact mode

Insert Bullets in Google Sheets

On Google sheets, we don’t have a feature to insert bullets automatically. To insert bullets or number list of Google Spreadsheet, follow the steps listed below:

  • Step 1: Choose the cell where you want the bullets to go.
  • Step 2: Now type “=CHAR(8226)” in the CHAR function.
  • Step 3: On your keyboard, press the “Enter” key.
how to add bullets in google sheets

Wrap Text in Google Sheets

  • Step 1: Choose the cells you’d like to wrap.
  • Step 2: Go to the menu bar and select “Format.”
  • Step 3: Go to the section titled “Text Wrapping.”
  • Step 4: From the drop-down option, select “Wrap.”

The selected text will be wrapped now.

How to Wrap Text in Google Sheets