20 things you didn’t know could be done on Google Sheets – WAU

Knowing the main tips and tricks of Google Sheets, you will certainly increase your productivity and have even more complete and organized spreadsheets.

You can even use spreadsheets every day, but probably does not explore even a third of the features that Google Sheets has to offer.

Sometimes, even those who are already familiar with many of the Sheets’ functions still have a universe of possibilities to know and use.

The good news is that you don’t have to be an expert on formulas or spreadsheets to explore dozens of features that this app offers! In this post, we separated 20 Google Sheets hacks for you to know and apply, without having to be an expert!

1. Create shortcuts to frequent tasks

Do you usually do the same tasks within Google Sheets?

To optimize your time, you can use shortcuts for those tasks that you do frequently.

2. Use Macros also for frequent tasks

In addition to shortcuts, another Google Sheets tool that helps you optimize your time with frequent tasks is Macros.

Through it, you can record actions that you do repetitively and then play the recording you saved to repeat that task.

If you are interested in this tool, visit Tools> Macros> Record Macro.

Use Macros also for frequent tasks

3. Translate text without needing other applications

A feature that not many people know about Google Sheets is that it can integrate with Google Translate, being able to translate the text you want in the spreadsheet.

To activate this function, just type = GOOGLETRANSLATE (“phrase in the current language”, “current language”, “language you want to be translated”).

See the example below, in which we want to translate a text from Spanish to English:

= GOOGLETRANSLATE (“hola”, “es”, “en”)

4. Transfer data from another Google Sheets spreadsheet

Another feature that can save you a lot of time is the integration between Google Sheets spreadsheets.

That way, you avoid having to copy and paste information from one file to another, and that data is automatically sent.

The functionality that allows this is called IMPORTRANGE. To test it, use the example below:

= IMPORTRANGE (“Url of the spreadsheet you want”, “Tab and ballot reference”).

Here is an example:

= IMPORTRANGE (“https://docs.google.com/spreadsheets/d/test”, “Sheet1! A: A”)

5. Connect your spreadsheet to other apps

In addition to connecting one spreadsheet to another, you can also connect your Google Sheets spreadsheet other online applications.

This means that you can get data from sources like Instagram and Twitter.

To do this, you can use apps like Zapier or IFTTT.

6. Know formulas within the app

As you may already know, Google Sheets allows a number of different formulas for you to apply to your data.

We know that there are many formulas and it is difficult to know or use them all, but it is possible to have a vision of the possibilities that you have typing the equal sign and a parenthesis.

Here’s an example:

Discover formulas within the app

7. Invite collaborators

One of the great advantages of using Google Sheets is being able to add collaborators to your spreadsheet, who can view, comment, or make edits in real time.

In addition to making this flexibility available to each employee, it is also possible to see what each one is doing in that document and have access to a history of issues.

To share your spreadsheet with more people, just click on “share” and add the emails of those you want to be part of the document.

Invite collaborators

8. Lock banknotes to prevent edits

As much as Google Sheets allows the collaboration of several people in the same file, you will not always want everyone to have access to all types of editing.

To have more control over these permissions, you can lock specific bills and select who you want to have permission to edit it.

Configure this permission by right clicking on the data you want to restrict, and then “protect range”.

Lock banknotes to prevent edits

In addition to protecting specific data, you can also protect an entire tab by clicking on “sheet”.

Lock banknotes to prevent edits

9. Make a checklist for your team

Another great feature for those who use Google Sheets with teams is create a checklist of activities.

Find this option in Insert> Checkbox:

check list

10. Use filters on your data

With so much information that we put in spreadsheets, filters become a facilitator to find specific data quickly.

To do this, just click on the filter button and specify the type of data you want to see:

Use filters on your data

11. Transfer information from a form to your spreadsheet

Do you pass data from a form to a spreadsheet manually?

With this tip, you won’t need to do that anymore!

All data on your Google Forms can go toutomatically for a Google spreadsheet. See how:

Click Insert> Form

Transfer information from a form to your spreadsheet

Then, a Google form will open in a new tab. Create the form normally.

In doing so, you will notice that Google Sheets will create a tab in your spreadsheet with the form responses:

Transfer information from a form to your spreadsheet

12. Import updated financial data

This is a more specific feature, but it saves a lot of time for those who follow the financial market.

If you need to include stock price information in your spreadsheets, there is a command in Google Sheets that does this automatically!

This command is the = GOOGLEFINANCE. Here is the parameter you need to use:

= GOOGLEFINANCE (company, “price”, “start date”, “end date”, “frequency”)

An example, with Google’s own actions, is:

GOOGLEFINANCE (“GOOG”, “price”, “1/1/2014”, “12/31/2014”, “DAILY”)

13. Transfer an RSS feed to your spreadsheet

Another more specific but also very useful feature is the automatic transfer of an RSS feed to your spreadsheet.

With functionality IMPORTFEED, you can import recent website publications into your document.

Here’s how to use IMPORTFEED:

IMPORTFEED (“URL”).

An example with Google’s own feed would be:

IMPORTFEED (“http://news.google.com/?output=atom“

Transfer an RSS feed to your spreadsheet

14. Standardize the text format

Often, when you import data, the data text format will be unordered (eg, DATA, data, data).

To standardize this format, you can use some functions like:

  • = UPPER (A1): leave all letters in caps lock
  • = LOWER (A1): leaves all lowercase letters
  • = PROPER (A1): first letter in uppercase, the rest in lowercase

15. Hide columns

If you want to stop seeing some data from your spreadsheet, without deleting it, just hide the column or row that corresponds to the information.

Right click on the row or column and select the option “hide row ” and “Hide column”, respectively.

Hide columns

If at any time you want to see the column again, click on the arrow that was generated instead:

Hide columns

16. Format lines with colors

Want a simple and quick way to improve the layout of your spreadsheet?

Look for the “Alternating colors”In the menu, in the“ Format ”section.

When you select this option, Google Sheets will automatically apply an alternate color rule to your lines.

See below:

Format lines with colors

With “Alternating colors”, your spreadsheet looks something like this:

Format lines with colors

17. Create a small graph on a ballot

To show your numbers in a quick visual way, Google Sheets allows you to creating small graphics on banknotes!

If you want to create only one line, just enter = SPARKLINE (C2: E2), assuming that the data you want to consider is between lines C1 and E1.

If you want to create a mini column chart, you must use = SPARKLINE (C2: E2, {“charttype”, ”COLUMN”})

Create a small chart on a ballot

18. Confirm emails in your spreadsheet

When importing email data into your spreadsheet, it will often not be formatted correctly.

The good news is that Google Sheets allows, through the function ISEMAIL, identify which emails are valid or not.

To check emails from a specific data range, use the following formula:

= ArrayFormula (ISEMAIL (H2: H3))

Google Sheets will provide a view as follows:

Confirm emails in your spreadsheet

19. Validate URLS

In addition to validating emails, Google Sheets can also validate URLs in your spreadsheet.

Use the same formula as indicated above for emails, replacing ISEMAIL with ISURL:

= ArrayFormula (ISURL (L2: L3))

From this configuration, Sheets can now show the right and wrong URLs:

Validate URLS

20. Separate names and other data

Have you spent a lot of time separating data together (eg, first and last name) manually?

There is a feature in Google Sheets that makes this job easier for you!

O “Split text to columns” helps to keep your information as organized as possible, in a quick and simple way.

As you saw in this post, Google Sheets has several hacks that make your life easier and everyday tasks faster.

You don’t need to be an expert to start using these features right now – so get the ones you like best and start using them!

If you want to get to know other Google apps and how they can help in your day to day, be sure to check out our list of 52 company apps for the most different goals!