Send Emails When Commenting

Google Sheets is great for collaborating with others— everything updates in real-time. But if you need to notify someone about something they should check, you can send an email when you add a comment to Google Sheets. Just add a plus sign (+), then type in their email address (or name), and once you add your comment, the designated recipient will automatically receive an email with your message.

Heatmaps - Conditional Formatting

Heatmaps are a great way to draw attention to important data in your spreadsheet. You can highlight specific values, outliers, or errors using conditional formatting. Applying a color scale quickly distinguishes lower and higher values in your data. This is especially useful when pulling data from Google Analytics, allowing you to quickly identify areas where you should focus your analysis.

Apply filters

You can use filters to display only the rows you're interested in within your spreadsheet. This can be really helpful when working with larger datasets. Just select the filter icon (or choose "Filter" from the "Data" tab), then click the filter icon in the first row of your sheet's column and select the value(s) you want to include.

If you use filters regularly, you can save them for future use. Select "Create new filter view" by clicking the filter icon (or under the "Data" tab, then "Filter views").

Clear Values with CLEAN and TRIM

If you find unwanted characters in your cells, you can use the CLEAN function to remove any non-printable characters and the TRIM function to remove white space from the beginning and end of cells.

Protect Data in Cells

If multiple people are working on a spreadsheet, you might want to lock certain data to prevent errors. You can protect entire sheets or even individual cells to keep data from being accidentally changed. If you don't want to fully lock cells, you also have the option to display a warning before they're edited.

Validate Data in Cells

You can ensure that certain cells contain only selected data by applying data validation in your spreadsheet. For example, you can set validation so that specific cells only accept numbers or even a value from a predefined list.

Setting a predefined list of values will also make a drop-down selector available in the sheet.

Integration with Google Forms

After collecting responses using Google Forms, you can use Google Sheets to analyze and visualize the data.

To send data to Google Sheets, select "Responses" within the form, then click the Google Sheets icon to send existing (and future) responses to your spreadsheet.

Insert a Chart from Google Sheets into a Google Doc

After you've created a chart in Google Sheets, you can easily insert it into a Google Doc. Just go into your document, select "Insert," then "Chart," and finally "From Sheets."

This can save you a lot of time because you can update the chart in your document to reflect any changes you've made in Google Sheets. All you have to do is click 'Update' in the document, and you're good to go!

Import Data from a Website or RSS Feed

You can use various functions to import data into your spreadsheet from websites and RSS feeds, including:

ImportHTML for importing HTML tables and lists

ImportFeed for importing RSS entries

ImportData for importing an online CSV file

ImportXML for importing a custom section of a webpage that can be identified using XPath

Change Text Case in Cells

You can capitalize the first letter of each word using the PROPER function. This is useful if you need to clean up values to ensure consistency.

Alternatively, you can use the LOWER function to make all letters lowercase.

Translating text

If your spreadsheet contains cells with text in a different language, you can use the GOOGLETRANSLATE function to automatically translate the values into another language.

Split Names and Other Data

If you ever find that you need to split data from one cell into multiple cells, you can use the "Split text to columns" option (which you'll find under the "Data" tab). This can be useful for organizing data, for instance, if you need to split first and last names into separate columns in your spreadsheet.

Validate Email Addresses

If you have a list of emails and want to ensure they have a correct email address structure, you can verify them using Google Sheets. This won't confirm if your emails will be delivered, but it will help identify addresses that are likely to bounce (e.g., those missing an '@' or '.com').

Quick Data Summarization

If you're looking for a quick way to summarize data, SUMIFS and COUNTIFS can be excellent solutions, as they allow you to define multiple criteria. For instance, you can sum all numbers above a specific value or count the number of rows containing a particular string or value. This will also facilitate any advanced calculations.

Import Data from Other Sheets

Instead of maintaining data across multiple spreadsheets, you can simply import it from one sheet to another. This also means you only need to update the data in one place (rather than in several sheets), which can save a lot of time.

Visualize Data with Sparklines

You can easily add sparklines to your spreadsheets to quickly see trends in your data. This can be especially useful if you're comparing data (like metrics from Google Analytics) or if you want to turn your sheet into a dashboard.

Creating QR codes

Barcodes are a useful way to track things, like checking people into an info session, conference, or event. You can quickly create QR codes right in Google Sheets.

This example will pull data from cell A1 to create a QR code:

=IMAGE("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl="&A1&"")

Quick data visualization

If you're looking for new ideas on how to visualize your data, you can use the Explore function to see suggested ways to present your data.

Extend Google Sheets with Add-ons

There are many add-ons you can use in Google Sheets. They can make tasks quicker and easier—for instance, the Google Analytics add-on for pulling data into your sheets, and Supermetrics for pulling data from platforms like Facebook, Twitter, and LinkedIn. The entire Google Workspace suite offers numerous integration possibilities, and you can also automate many processes by choosing the right add-ons.

Learn Formulas Quickly

Google Sheets makes it easy to learn formulas as you work. When you start typing a formula, you'll see a handy reference containing important details about the formula you're adding to your spreadsheet. Just begin typing, for example, =DATEDIF

Keyboard shortcuts

You can speed up your work by using keyboard shortcuts in Google Sheets. From clearing formatting within cells to adding comments and hiding rows, the built-in keyboard shortcuts can help you save time. To access the keyboard shortcuts (or even create your own), press Command + / on a Mac or Control + / on Windows.

Based on:

https://www.lovesdata.com/blog/google-sheets-tips