30 Google Sheets tips that improve productivity

The spreadsheet application Google has been gaining more and more users, given its versatility and the practical and simple possibilities of sharing and joint use between users in an online environment. However, due to its minimalist look, many may think that it is not a “Excel” complete and think a little before using it. To make the platform users even more knowledgeable, we have separated a list containing 30 Google Sheet tipss that will give that increment in its use. 

Excel or Google Sheets?

With the convenience of using online spreadsheets, the Google Sheets, or Google Sheets, guarantees good results when it comes to cloud productivity. Although some features seem to be missing, such as options and activity tabs, “Excel” in Google guarantees the production of many works and is already used by most people. Both for the safety of use in an online environment — which avoids loss of custody and keeps track of changes — and also for its shareable appeal, whose possibility of mutual and real-time editing between collaborators is increasingly required in remote work.

Answering the question about which program is the best depends on each one, but we can guarantee that the app from Google does not owe anything to its competitor in the ecosystem for many tasks, and can be the most cost-effective and practical choice for many who choose cloud editing in a simple and intuitive way. To guarantee this statement, we put together this special containing 30 Google Sheet tipss that will show you many of the features that the Google app has to offer.

Include date and time with shortcuts

O Google Sheets allows several shortcuts to make the user's life easier. One of them, very interesting, is the current date insertion through the command ctrl + c (or Ctrl + ; in the US version of Google Sheets). With another command, the Ctrl + Shift + C it is possible to launch the current time. Now to add both date and time at once, use Ctrl + Alt + Shift + C. Pretty useful, isn't it?

One of the series of other shortcuts can be consulted using the command Ctrl + /.

Open a New Document directly from the Browser

When typing the command sheet.new ( sheets. new, or yet spreadsheet.new) in your browser's address bar, a new worksheet will be created and opened directly. For the command to work, just be logged into your account Google. Simple and straightforward!

clear spaces

through the formula = TRIM (=TRIM), you can correct cells by taking out misspelled spaces. In this way, it is possible remove spaces accidentally added at the beginning or end of a certain text or else fix double, triple space insertions or more typed.

• Example:

= TRIM (B1)

To use the formula in a set of cells, use the formula ARRAYFORMULA and the preference command, as in the following example. 

• Example:

=ARRAYFORMULA(TRIM(A1:A112))

translate texts

Another function available in Google Sheets is the one of translate texts. with the command GOOGLET TRANSLATE, it is possible to translate texts without having to open a new tab and consult the Google translator. Just insert in the formula, after the word or cell to be translated, the source languages ​​and the one to be translated.

• Example: 

=GOOGLETRANSLATE(D25; “EN”;”PT”)

If you want to find out the language used in a certain cell, or even applying the text between quotation marks in the function, use the formula DETECTLANGUAGE, for the Google Sheets respond with a two-character acronym.

• Example:

=DETECTLANGUAGE (“Frühstück”) 

Follow a list of language acronyms (also known as ISO 639–1 code) to be used with this formula in Google Sheets. It is also possible to use, in place of the input language, the option "CAR" so that the system identifies the language to be translated.

• Example: 

=GOOGLETRANSLATE(D22; “Auto”;”PT”)

format values

you can change value formatting in cells in Google Sheets through the following commands: Ctrl + Shift + 1: decimal🇧🇷 Ctrl + Shift + 2: time🇧🇷 Ctrl + Shift + 3: data🇧🇷 Ctrl + Shift + 4: currency🇧🇷 Ctrl + Shift + 5: percentage; and Ctrl + Shift + 6: exponent. Useful to fix that mess of values ​​coming from different sources.

convert measurements

Another very useful feature in Google Sheets is what makes it possible to measurement conversion such as meters, kilos, miles, among others. through the formula CONVERT, you can convert measurements from different formats to others, such as converting from “feet” — a measurement widely used in the United States — to meters. At the Google support there are more parameters, as well as a list of the abbreviations of the units available for conversion.

• Example:

=CONVERT(56; “FT”;”M”)

Freeze rows or columns easily

A task, sometimes complex to find, which can now be done in a completely simple way. To hover over the upper left corner, before the number 1 or the letter A, a hand will reveal itself in place of the pointer, allowing you to drag down or to the side to freeze the respective row or column.

multiply cells

To copy a value from within a cell to multiple vertically or horizontally, first click on the cell you want to copy and it will be bordered in blue with a tiny square symbol in the lower right corner. Click on that square and drag to one of the directions you want multiply the cells. Just be aware if the value contained in the cell is a date, as it will be replicated with subsequent dates automatically.

Use alternate colors

One of the qualities in keep a spreadsheet neatly organized it's being able to show it to the world and also keep things in order. To help visually control the middle of so many lines, especially in a scenario of cells that look a lot like each other, create the formatting of alternating colors across the way Format > Alternate Colors. It will give your data a whole new look and let you not get lost when you are looking for some linear match in a distant column.

Another possibility is to create a heat map all with accentuate certain values ​​in comparison to others. To do this, select a dataset and go to Format > Conditional Formatting > Color Scale. In this area, it is possible to configure some points to make a kind of abstract mosaic of colors in the selected cells. Ideal for those tables containing varied price values, crossed percentages, among others.

Generate QR codes

This tip is super! Using an external link QR Code generator, you can create an image of a QR code and attach it to a cell to take the user to any address. To do this, enter a site to be turned into QR Code inside a cell and, in another unit of the worksheet, enter the formula IMAGE, as in the example below; still not forgetting to select the corresponding cell in the final attribute. Ready! Now you have a QR Code at your disposal within the Google Sheets!

• Example:

=IMAGE("https://image-charts.com/chart?chs=150×150&cht=qr&choe=UTF-8&chl=” &ENCODEURL(H23))

Share links to copy or download

When sharing a table link in Google Sheets with someone, try changing the ending where it says “edit” to “copy”. When the new link opens, a download screen will arise, suggesting that the user make a copy test for yourself. Useful when you are going to send a model to someone or even to suggest to the recipient to make a copy of the document for him.

• Example:

https://docs.google.com/spreadsheets/d/1yvPi_jZ3aMus96vGveD0OT0o5tI337JIYdPxnggW_XU/copy

Another option is offer the user the direct download of the document in PDF format, just change the end for “export?format=pdf”. With this, when the link is opened in the browser, the PDF download automatically to your machine. 

• Example:

https://docs.google.com/spreadsheets/d/1yvPi_jZ3aMus96vGveD0OT0o5tI337JIYdPxnggW_XU/export?format=pdf

View website feeds

To display the feed (content) of a site or a specific page, the command IMPORTFEED can be used, placing the link in quotation marks in its parameters, with the inclusion of the letters “rss” after the final slash. The symbol rss means Rich Site Summary (rich site summary), or Really Simple Syndication (really simple distribution), and it's a way to show the content of a particular website in real time and in a simpler way.

• Example: 

=IMPORTFEED("https://www.showmetech.com.br/rss")

Load tables from sites

It is also possible to load tables from sites that contain one in their formatting. To do this, use the command IMPORTHTML and use the link of the desired site, followed by the description “backgammon” and the corresponding table number on the page.

• Example: 

=IMPORTHTML(“https://www.mariowiki.com/Donkey_Konga”🇧🇷 “table”; “5”)

View data from other worksheets

You can bring in cell data from other worksheets, to display them in the current one, using the command IMPORTRANGE. The data of this function is composed of the link of the worksheet referred to in quotation marks, followed by the name of the cell or a range between them also in quotation marks (so that cells from the current worksheet do not come). Finally, for it to work, hover over the cell and click the button Allow access to allow the two worksheets to connect.

• Example: 

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1yvPi_jZ3aMus96vGveD0OT0o5tI337JIYdPxnggW_XU🇧🇷 “A15:A17”)

Increase or decrease the letters

As in Word, you can increase or decrease all letters with a simple command in Google Sheets. Using the following functions UPPER, LOWER ou PROPER, it is possible to transform all capital lettersOn lowercase or make capitals only the first letters of words, respectively.

• Examples:

=UPPER (G16)

=LOWER (A1)

=PROPER("showmetech")

rotate texts

O Google Sheets it may surprise its users with some features, but this one maybe few know. After selecting a cell with content, click the button text rotation, represented by a letter A in a rotated position and located in the upper right corner, or in Format > Rotation all with rotate a text or any other data from your cell. You can even adjust the rotation angle to create a distinctive effect on your data.

Use special characters

It's possible add special characters in cells through the use of some codes with the CHAR variable, such as =CHAR (9757), in which a hand appears with a finger pointing up, or else =CHAR(9838) for the musical symbol natural. Also check out this complete listing of special characters (use the numbers from the HTML code) to be used in Google Sheets.

Separate text into columns

You know when we have a list of full names and we want to separate the first and last names into columns? O Google Sheets has a great tool for separating text into columns. through the menu Data > Split Text into Columns, you can perform this task. You can leave the app auto detect separating into columns or setting some parameters, such as separating after Comma, Semicolon, End, Dining Room or any other symbol Personalized.

Hide rows and columns

A way of hide data or else from hide unwanted lines for a moment is using the right button next to the corresponding row selection (in the numbers in the left corner of the table). For show them again, click on the arrows that will reveal themselves when hovering the mouse. It is also possible do the same with the columns, simply repeating the procedure in the letters representing the columns fixed in the upper corner.

Block access to cells

It could be that you are sharing a spreadsheet with someone, but you don't want a particular cell, or set of cells, to be changed by a user other than you. And guess what, Google Sheets has this option! For block cell access to one or more users, just click on it with the right button and choose the option See more actions in cell > protect range. It is even possible, instead of blocking, to activate the option Show a warning when editing this range.

Check emails and websites

With Google Sheets, you can check the validity of emails or websites, to see if they were typed correctly, with the function ISEMAIL ou ISUR. These functions can help in case the user has a list of emails or websites and wants to check its validity, since the formula will return the value True ou False. If you want to use the function on a set of cells at once, insert it into the function ARRAYFORMULA.

• Examples:

=ISEMAIL(G19)

=ISURL (G19)

=ARRAYFORMULA (ISEMAIL (G19:G22))

View quick calculations and graphs 

When selecting a set of cells containing numbers, simply view your sum which will appear in the bottom right corner, outside the worksheet area, next to the Explore button. By clicking with the mouse on this same rectangle, it is also possible to visualize other calculations such as average, minimum value, maximum value, count and count numbers, being able to change the default quick calculation to be displayed. Already for one broader view with graphics and the option to drag your drawings into the table, use the explore button.

Highlight pie charts

you can give even more highlight a portion of your pie chart, the next time you create a chart, from the insert menu in Google Sheets. With the graph ready, double-click on one of the slices you want to highlight and then choose, on the right side, the distance from the center so that it gains even greater prominence with its distance from the main pie.

create a sparkline

from the formula SPARKLINE, it's possible create a sparkline inside a cell. To do the magic, enter, inside the formula, the range of cells you want to use. To create differentiated and even colorful models, just use some of the options that can be seen in this list of examples on Google support.

• Examples: 

= SPARKLINE(B3:B7)

Use filters

With a lot of data and a well-filled spreadsheet, it becomes necessary to use of filters to do some surveys and checks, as well as to assemble reports and highlight certain conditions. To use the function Filters No. Google Sheets It's simple. With the line to be investigated selected, click on the button with a Filter symbol (a triangle with the point down), or in the menu Data > Create a Filter, to search and display data ordered or containing some information according to some rule.

Add Checklists

Want to add a to-do list and complete them by triggering checkboxes — the famous “checklists”? In Google Sheets this is also possible through the menu Insert > Checkbox (checkbox in the American version). With it, you will have full control of your completed activities and tasks to do.

Receive edit notifications

One of the most interesting properties in the Google Sheets is the possibility of share a link to a document with another user so that he can also use it — step done through the button Share. However, the app also allows notifications to be generated and sent by email every time the document is changed, either immediately or through a daily summary. To configure this option, go to the menu Tools > Notification Rules.

Check stock market values

For those who are connected to the financial market and Stock Exchange (B3), it is possible to consult values ​​of company shares within the Google Sheets with the function GOOGLEFINANCE. To do so, you must enter the company code in B3, followed by the attribute to be displayed, and other attributes can also be added, such as the quote start date and the number of days later for a daily return of values. Here, using this link, there is a list of attributes that can be used with this formula.

• Example:

=GOOGLEFINANCE ("AMER3"; "price"; "2022/01/04"; "4")

See all functions

from the menu Help > List of Functions, or through Google support, you can consult a list containing all Google Sheets formulas, all containing syntax examples and descriptions with a link to more information. Ideal for searching for that desired formula or possibility.

create macros

Is there a formula or task that you use constantly and want one shortcut to call it in Google Sheets? You can do this as a simple shortcut using the command Record Macro, located in the menu Extensions > Macros. By clicking on it, a recording screen will appear and you must perform the task to be recorded for posterity. However, first you must choose if you want Use absolute references ou relative and, when finishing the execution of the task, click on Save, choosing a number to be your shortcut together with the Ctrl + Alt + Shift buttons. 

You can use a recorded Macro by clicking on the menu Extensions > Macros and choosing the Macro desired, or using the corresponding shortcut. The first time you use a Macro, you must authorize the App to use Macros to work.

See also:

And those were our tips to use in Google Spreadsheets. If you are also interested, check out the 10 Spreadsheet Templates for Google Sheets:

Best Google Sheets for Organizing Your Life and Work

Sources: Fast Company, Google, Medium.GRID.is, computer point