20 Essential Google Sheets Tips and Tricks for Everyone

14 min readMar 7, 2019

If you ask us, Google Spreadsheets is one of the most useful tools that mankind has known.

Why? Well, they’re endlessly versatile, and you can adapt google sheets tips and tricks in pretty much any scenario you can think of.

If you’re collaborating with a cross-functional team on an important project, you can use Google Sheets to organize the project flow, and track everyone’s progress.

If you’re working to submit certain deliverables to a client, and your process involves plenty of back and forth, you can use Google Sheets to keep tabs on the different files you’ve submitted, as well as your client’s feedback on each file.

And if you’re coming up with a content marketing strategy for your company, you can use Google Sheets to organize your weekly blog and social media posting schedule.

So, regardless of whether you’re:

A project manager who needs to coordinate projects with multiple teams using different project management methodologies
A freelancer who needs to keep track of all their submissions to their clients, or
A marketing manager who needs to have a high-level overview of your company’s content strategy

Chances are, you’ll probably benefit from learning how to maximize the utility you get out of your Google Sheets.

Lucky for you, we’ve done the research work, and compiled a list of Google Sheet’s Best Tips and Advanced Tricks for your perusal.

All you have to do is pick out which one of them is a good fit for you, and then start incorporating them into your work.

Alright, let’s jump right in!

Best Tips for working with Google Sheets

Regardless of what you’re using your Google Sheets for, these best practices will help you organize your spreadsheets better, and reduce mistakes.

1. Use ARRAYFORMULA() to Group the Cells in a Particular Order

An array, simply put, is a table of values.

To group the values of your cells together in a particular order, use arrays in your spreadsheet. When you apply ARRAYFORMULA to your data, this processes your data in a single batch.

Here’s what’s interesting: with this formula, you can make changes in just one place, and have the effect ripple across the entire data range.

Here’s the formula: =ARRAYFORMULA(array_formula)

Where array_formula is either a range, a mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell.

To help you understand this better, we have a clear example here of how a PPC expert analyzes ad spend data on Google Sheets using ARRAYFORMULA.

2. Unleash the Power of Pivot Tables

A pivot table is a tool that you use to summarize a large set of data.

More specifically, when creating a pivot table, you’re taking your standard two-dimensional table and pivoting it around aggregation of the data to come up with a third dimension.

Pivot tables can be used to answer questions such as…

Which sales rep brought in the most revenue in the month of November?

OR: How many earphones did we sell in each retail store in Q4 of 2018?

To create your first pivot table, follow this simple guide:

Open your Google Sheets, and highlight all the cells containing data.
Click “Data” from the top menu, then “Pivot Table”.
If Google’s suggested pivot table analyses answers your question, you’re good to go.
If not, you’ll have to create a customized pivot table. Locate “Rows and Columns”, and click on “Add” next to it. Select the data you want to analyze.
Locate “Values”, and click on “Add” next to it. Select the values you want to display within the rows and columns.
Click “Filters” to display the values meeting your criteria.

Here’s a simple example with visualization for you to better understand:

Let’s consider data for the amount of revenue generated by each Sales Representative in the time frame of June 2018 — November 2018.

Now, we’ll create a pivot table to understand which Sales Representative has brought in the highest revenue for the month of November 2018.

3. Filter Data in Cells

If you’ve got a huge dataset on your hands, then Google Sheets’ filtering function will probably be a lifesaver.

To use the filter, click on “Data” and “Create a filter”, then use the drop-down list to choose what you want to filter your data by.

4. Visualize the Spreadsheet using Conditional Formatting

What’s conditional formatting?

This basically refers to formatting your data to have it automatically change text or background color if they meet certain conditions.

To use this function, highlight the cells you want to apply format rules to. Then click “Format” from the top menu, and click “Conditional formatting”.

Using the toolbar that pops up, create a rule. You can choose the condition that you want to trigger the rule by clicking on “Format cells if”.

Once you’re finished, click “Done”. That’s all there is to it!

Let us consider an example here:

Every marketer would definitely love to slice & dice website data to understand in depth what sources are bringing in the most traffic. You can heatmap your “new users” column in the traffic report using Conditional Formatting (Color Scale option) to see the highest and lowest traffic sources highlighted.

Below is a visualization for you to better understand this:

5. Use Data Validation by setting up criteria on Data

Data validation allows you to constrain the values that are entered into a worksheet cell.

To use this function, select the column of cells where you want to constrain user-entered values. Click on “Data” from the top menu, and click on “Data validation”.

Choose either “List from a range” or “List of items”, and to remove the downwards arrow in the cells, uncheck “Show dropdown list in cell”.

If anyone enters data in a cell that doesn’t match an item on the list, they’ll see a warning. If you want to further restrict people, and only allow them to enter items from the list, click on “Reject input” next to “On invalid data”.

Finally, click “Save”.

6. Automate your Repetitive Tasks using macros

The “Macro” feature is a pretty recent update to Google Sheets, and it’s exceptionally useful for people who need to automate repetitive tasks.

To use macros, you’ll have to first create your own macro, then perform it subsequently.

To create a macro, click on “Tools” from the top menu, then click on “Macros” and “Record Macro”.

Choose your cell reference. With absolute references, your macro will only perform tasks on the exact cell you record. With relative references, the macro will perform tasks on the cell you select as well as its nearby cells.

After choosing your cell reference, complete your task and click “Save”.

Congrats, you’ve created your first macro! Now name the macro, create a custom shortcut for it, and click on “Save”.

To run your macro, click on “Tools”, then “Macro”, and select the macro you want. Your macro will perform its task automatically, without any input from you.

7. Lock Cells to Prevent Unwanted Changes

Want to lock certain cells in your Google Sheets? There’s actually a function that allows you to do that.

First, highlight a cell or a range of cells that you want to protect. Next, click on “Data” from your top menu then on “Protected sheets and ranges”.

Using the sidebar that pops up, name your protected range, and set the permissions for this range. You can choose to show a warning when anyone edits the range, or restrict access so that only you can edit the range.

8. Embed Charts on your Website

We all know that you can create beautiful-looking chart and graphics using Google Sheets… but did you know you can also easily publish these charts to a website?

To do this, click on your chart, and click on “More” at the top right-hand corner.

Then click on “Publish chart”, and click on “Embed”. Go ahead and paste the HTML code that’s generated for you onto your website, and you’re all set.

9. Sync Data between Apps

By connecting your Google Sheets to other apps, you can quickly and easily sync your data back and forth.

Here are some of the best Google Sheet Integrations that you can try for a seamless data sync:

Marketing:

Connect
Sync Facebook Leads to Google Sheets

Connect
Add a Row in Google Sheets on a New Form Entry in Wufoo

Connect
Export MailChimp subscribers to a Google Sheet easily

Connect
Add a Row in Google Sheets on a New Form Entry in Unbounce

Sales & CRM:

Connect
Create new Salesforce leads from Google Sheets

Connect
Push new Infusionsoft contacts as rows on a Google Sheet

Collaboration:

Connect
Add a Row in Google Sheets on a New Email in Gmail

Connect
Create Google Calendar events from Google Sheets

Payments & E-Commerce:

Connect
Record new customers in Stripe on a Google Sheet

Connect
Sync new Shopify customers to new rows in Google Sheets

10. Store Form Entries to Google Sheets

If you’re creating a survey form on another platform (such as SurveyMonkey), and THEN copying and pasting your data onto Google Sheets to analyze, you’re doing it wrong.

You can directly send your form entries onto a Google Sheet like this:

Connect
Sync Typeform form entries as rows on Google Sheets

Connect
Push Wufoo form entries as new rows in Google Sheets

Connect
Add new rows in Google Sheets for a New Survey Response in SurveyMonkey

You can also minimize downtime by simply creating a form directly within Google Sheets. This way, you can access everything in one place.

Adding a form on Google Sheets is simple — just click on “Insert” and “Form” from the top menu, then start building your form.

Once you’re done, click on the “Send” button on top of the form builder, and share the link with your friends and family.

To check your responses, click on “Responses” on the form builder. You can also click on the Sheets icon to switch to a spreadsheet view.

Advanced Google Sheets Tricks

These hacks help you get more out of Google Sheets, and adapt them to a wide variety of purposes.

1. Fetch any Image from its URL

Adding images to your Google Sheets is simple.

Just key in =IMAGE(“URL”) and replace the “URL” with your image URL, and you’re done!

2. Pull Data from Social Media Channels

If you want to use your Google Sheets to record and analyze your company’s social media posts or follower count or any other metric, there’s an easy way of doing that.

Tools like Automate.io help you connect Google Sheets to your social profiles like Facebook Lead Ads, Facebook Pages, Twitter etc. You can sync and import/export data between your other apps and saves hours of time!

Let us consider an example: Generating hundreds of Facebook Leads and manually adding them to your Google Sheets is a tedious task.

You can connect your Facebook Lead Ads to Google Sheet app and create new rows in Google Sheet with all lead details whenever there is a new lead generated.

Connect
Sync Facebook Leads to Google Sheets

A social media marketer would want to maintain a database of your Twitter followers and use this list to run marketing campaigns.

A simple integration between the Twitter app with Google Sheet app helps you create automation like this: for every new follower, you can create a row with follower details on a Google Sheet.

Connect
Add new Twitter Followers as rows on a Google Sheet

3. Format the text in Cells using simple Formulas

Say you copy some text over from a website, and it’s formatted messily.

To clean up your text, use the UPPER, LOWER and TRIM functions in Google Sheets. These functions allow you to transform your data to uppercase, lowercase and remove extraneous spaces within the data.

Here’s how you can transform data to uppercase using the UPPER Function:

Formula: UPPER(text), where “text” — the string to convert to uppercase.

To automatically change all the Uppercase text to lowercase, use the LOWER Function.

Formula: LOWER(text), where “text” — the string to convert to lowercase.

The TRIM function to remove those extra spaces within the data without manually doing it. The formula: TRIM(text), where “text” — the string or reference to a cell containing a string to be trimmed.

4. Analyze the Data using Filters

Most people know there’s a “Create A Filter” function under their “Data” tab, but not many folks make full use of this function.

First up, to create a filter, simply click on “Create A Filter”. This allows you to:

Sort your data from A to Z
Sort your data from Z to A
Filter your data by condition, and
Filter your data by values

Don’t worry — you’ll still be able to see all your data once you turn the filter off.

Create A Filter aside, there’s also “Filter views”.

These allow you to:

Save multiple views, and access them later
Name each view
Share different filters with people

To get started, click on “Data”, “Filter views”, and “Create new filter view”.

Then sort and filter your data. When you’re done, click the close button on the top right to close and save your filter view.

If you want to delete your filter view, click on the settings button on the top right, and choose “Duplicate”.

5. Get Data Insights from Google Sheet’s AI Tool ‘Explore’

Who would’ve thought? There’s also a hidden function that allows you to use AI to generate charts and reports in Google Sheets.

To get started, click on the “Explore” button at the bottom right-hand corner. Either use one of the built-in options or type your request for whatever chart you want to create.

6. Become more Productive by using Google Sheets Shortcuts

For heavy users of Google Sheets, you’ll save a ton of time by using keyboard shortcuts.

Here’s a complete list of shortcuts to check out.

7. Translate the Text using GOOGLETRANSLATE() Function

If you need to translate some text, you don’t need to switch to Chrome, navigate to Google Translate, and paste your text in.

Simply use the =GOOGLETRANSLATE function, and you’re all set.

8. Collaborate using Google Sheet’s sharing option

If you’re working with a large team, it’s too troublesome to get everyone’s email addresses, then manually invite each person to the Google Sheet you’re using.

Instead, click on the “Share” button, then “Get shareable link”.

If you wish to give edit access to your team, click on the drop-down menu to change it to “Anyone with the link can edit”. Then copy this link and share it with all your colleagues!

9. Clone Formatting without manual Copy-Paste

To clone your formatting, use this nifty format painter icon in your toolbar.

Highlight the cells that you want to copy the format from, then click the above icon, and drag your mouse over the cells that you want to apply the formatting to.