Using Google Sheets: Complete Beginners Guide – ABOUT MAG 2020

Using Google SheetsUsing Google Sheets: A Beginner’s Guide

This Google Sheets tutorial will help you move from a beginner or basic user to a confident, competent, and intermediate user.

Google Sheets is an extremely powerful tool for everything from digital marketing to financial modeling, from project management to statistical analysis, in fact, almost any activity that involves data recording and analysis.

And if you’re (relatively) new, you really pay dividends to learn how to use Google Sheets correctly. This tutorial will help you change from novice to ninja in no time!

If you’re new to Google Sheets, I recommend starting at the beginning of this article.

However, if you’ve used Spreadsheets before, skip sections 1 and 2 and start with Basic data and formulas section.

A template is available to copy to your Drive to accompany this tutorial:

In addition, several advanced features are listed for you to take a step further. Look for this logo: Advanced feature

Content

  1. Using Google Sheets: Total Beginner
    • What is Google Sheets?
    • How is it different from Excel?
    • Creating your first Google spreadsheet
    • The Google Sheets edit window
    • Working with data in Google Sheets
  2. How to use Google Sheets: The edit window
    • Editing columns and rows
    • Creating new tabs
    • Formatting
    • Removing formatting
  3. Using Google Sheets: Basic data and formulas
    • Different types of data
    • Doing math in numbers
    • Starting functions: COUNT, SUM, AVERAGE
    • Dividing data into cells
    • Combining data in cells
  4. Using Google Sheets: killer features
    • Adding comments and notes
    • Sharing your spreadsheet
    • Real-time collaboration
  5. How to use Google Sheets: intermediate techniques
    • Freezing panels for easy viewing
    • Understanding cell references
    • Basic conditional formatting
    • Data sorting and filtering
    • Adding graphics
    • Using the Explore feature
    • BONUS: The VLOOKUP function
  6. Using Google Sheets: Next steps

1. How to use Google Sheets: Tutorial for beginners on Google Sheets

What is Google Sheets?

Google Sheets is a free cloud-based spreadsheet application. This means that you open it in the browser window like a regular web page, but you have all the functionality of a complete spreadsheet application to perform powerful data analysis. It is really the best of both worlds.

How is it different from Excel?

Undoubtedly, you’ve heard of Microsoft Excel, the long-established heavyweight in the spreadsheet world. It is incredibly powerful and versatile software, used by approximately 750 million to 1 billion people worldwide. So, yes, a difficult act to follow.

Google Sheets is similar in many ways, but also distinctly different in other areas. It has (mainly) the same set of functions and tools for working with data. In fact, some people mistakenly call it “Google Excel” or “Google spreadsheets”.

At the risk of getting into an opinionated debate about the strengths / weaknesses of each platform, here are some important differences:

  • Google Sheets is cloud-based, while Excel is a computer program. With Spreadsheets, you will no longer have versions of your work floating around. Everyone always sees the same most up-to-date version of Sheets, showing the same data as the spreadsheet.
  • Collaboration is built into Sheets, so it works extremely well. Excel is still trying to catch up here.
  • Both have graphing tools and pivot table tools for data analysis, although Excel is more powerful in both cases.
  • Excel can handle data sets much larger than Spreadsheets, which has a limit of 2 million cells.
  • As a cloud-based program, Google Sheets integrates very well with other Google online services and third-party sites.
  • Both have scripting languages ​​to extend their functionality and create customized tools. Google Sheets uses Application Script (a variant of Javascript) and Excel uses VBA.

However, for the material that we will cover in this article, there is very little difference between the programs.

To delve deeper into the differences between Excel and Google Sheets, take a look at ExcelToSheets.com

Why use Google Sheets?

How is it for beginners:

  • It’s free!
  • It is collaborative, so that teams can see and work with the same spreadsheet in real time.
  • It has enough resources to perform complex analyzes, but…
  • … it is also very easy to use.

Need more convincing? Here it is 5 more reasons from Google itself.

Can you still do advanced things?

Absolutely! You can build dashboards, write formulas that make your head spin and even build forms to automate your work. The sky is the limit!

You will find many Resources on this site for intermediate / advanced users, in addition to online training courses.

Okay, where can I get it? Creating your first Google spreadsheet

If this is your first time with Sheets, go to https://www.google.com/sheets/about/ which, in January 2018, looks like this:

Using Google Sheets: Home screen

click on the Go to Google Sheets button in the middle of the screen. You will be asked to sign in:

How to use Google Sheets: login screen

And you arrive at the Google Sheets home screen, which shows the previous spreadsheets you created.

Click on the huge additional green button to create a new Google spreadsheet:

Using Google Sheets: Google Sheets home screen

Opening your first Google spreadsheet in Drive

You can create new Google spreadsheets from the Drive folder by clicking the blue NEW button:

Create a Google spreadsheet in Drive

When you create a new Google spreadsheet, it is created in your main Drive folder (your root folder):

New Google spreadsheet in the Drive folder

(Note: don’t panic if you still don’t see the spreadsheet, as it won’t appear until you rename it. See the next step on how to do this.)

Here you can drag it to a different folder, if you wish (to keep things organized). Do this by clicking and holding the file and dragging where you want it to go:

Moving the Google spreadsheet in Drive

The Google spreadsheet edit window

This is what your blank Google spreadsheet will look like:

Google blank sheet

You can rename your spreadsheet in the upper left corner. Click where it says Untitled spreadsheet and enter the name you want to give your spreadsheet, in this example “New spreadsheet”.

So, we are going to introduce some important terminologies and the fundamental concept spreadsheets work on:

Using Google Sheets: Terminology

There are two menu lines above your spreadsheet, which we will see later in this tutorial.

The main window consists of a grid of cells. An individual cell is a single rectangle, at the intersection of a column and a row, and contains a single piece of data.

Columns are vertical ranges of cells, labeled by letters at the top of the Worksheet.

Lines are horizontal bands of cells, labeled by numbers that are on the left side of your spreadsheet.

In the example above, I highlighted column E and row 10.

** The fundamental concept of spreadsheets: **

Column E and row 10 intersect in one cell and only one cell. Thus, we can combine the column letter and the line number to create a unique reference for that cell, E10. Now, when we want to refer to this cell, for example, to access data in that cell, we use the E10 address to do that.

Understand this and you understand spreadsheets. The rest are just details!

Data entry, selection, deletion and movement

Now the fun really begins! Let’s start using this new blank sheet that we created.

Click cell A1 (which is the intersection of column A with row 1, the cell in the upper left corner of the Worksheet) and you will see a blue box around the cell to indicate that it is highlighted:

Cell A1

Then you can just start typing and you will see the data entered in that cell:

Typing cell A1

Press Enter when you are finished entering data and you will move to the next cell after completing data entry. If you press the Tab key, it will move a cell to the right!

It is worth highlighting an important nuance here:

Clicking ONCE in the cell highlights the entire cell. Clicking TWICE enters the cell, so you can select or work with only the data.

If you are trapped inside a cell, press the button ESCAPE to deselect the content and level up, just to select the cell.

Try it yourself and see how the cursor appears inside the cell when you double-click, allowing you to edit the data.

To delete the data we just entered, click the cell once and press the Delete key or click the cell twice and press the Delete key until all of your data is cleared.

Help! I made a mistake

First of all, don’t panic!

Google Sheets saves every step of your work, so you can always go back one step (or two), if necessary.

Press Cmd + Z if you’re on a Mac or Ctrl + Z if you’re on a PC and undo the previous step. Keep pressing and you will simply go further in the changes. (Pressing Cmd + Y on a Mac or Ctrl + Y on a PC advances to retrace your last step.)

You can also undo using the Undo arrow in the menu:

Undo arrow

Creating a basic table

Okay, with all that in mind, it’s time for a quick workout.

See if you can create the following table for our fictional gym membership site by entering the data in the correct cells (there is no formatting or other tricks used at this stage):

Google Sheets gross table

Feel free to use your own data if you wish. Please also note that the dates entered above are in the US format, with the first month, so don’t worry if your table has the first day.

2. How to use Google Sheets: the desktop

Changing the size, inserting, deleting, hiding / displaying columns and rows

To select a row or column, click the number (rows) or letter (columns) of the row or column you want to select. This will highlight the entire row or column in blue, to indicate that you have selected it.

To change the width of a column or the height of a row, hover the cursor over the gray line, indicating the border of the column or row, until the cursor changes to look like this:

Column width cursor

Then click and drag the cursor to the left or right to change the width of this column. It is the same process for changing the height of the lines.

Professional tip: To quickly change the column width according to the contents of the cell, double-click when you hover over the gray line.

How to add columns in Google Sheets: To insert additional columns or rows, click on the existing column or row next to where you want to insert a new column or row. With the column or row selected (highlighted in blue), right-click to open the options menu and select Insert Before (or Insert After) for Columns, or Insert Above (or Insert Below) for Rows:

Editing rows and columns in Google Sheets

Adding extra rows and columns at the end

If you reach the outer edges of a Google Spreadsheet, you’ll notice that the rows and / or columns stop. But don’t worry, you can add more.

If you’ve scrolled all the way to the bottom of your spreadsheet (or added a lot of data), you’ll notice that you get 1,000 rows by default. There is a button to add more lines, if you need, 1,000, as shown, or any number you want (up to a limit, more on that below).

Add more lines

If you reach the right edge of the Sheet, that is, the last column, add more columns in the standard way. Right-click on a cell in the last column to open the menu and choose to add a column on the right.

Professional tip: If you want to add more than one column, there is a trick to do it at once. As an example, suppose you want to add three new columns to the right side of your spreadsheet, start by highlighting the last three columns that already exist, right-click and choose to insert new columns. Then it will insert three new columns for you!

Data limit: Finally, keep in mind that each Google spreadsheet is limited to 2 million cells, which sounds like a lot, but has 50,000 rows per 40 columns (a database of totally reasonable transactions for an ecommerce store, for example). Either way, you’ll see that Google Sheets decreases considerably before reaching this limit. Most people report a slight slowdown with tens of thousands of data lines and complex formulas and models.

Adding / removing multiple sheets, renaming them

Super easy!

Click the big plus button in the bottom left corner of your Google Spreadsheet add a new spreadsheet (also called a guide).

Adding a new tab

Why use multiple tabs on your Google Spreadsheet?

Well, like a book with chapters on different topics, it can help you separate different data and keep your spreadsheet organized.

For example, you can have a Spreadsheet just to record your global settings (any variables such as name, email, tax rate, number of employees …) and another for transactional data, and another for analysis and graphics.

The button with the three bars, next to the plus sign, is your index button, listing all the tabs on your Google Spreadsheet. This is super useful when you start to have several different tabs to manage.

To rename a worksheet or delete a worksheet, click the small arrow next to the name (for example, Sheet1) to display the menu. Here you will see the option to rename, delete or even hide (and show) spreadsheets.

Rename a spreadsheet in Sheets

To name, I try to indicate what is on that tab. Use names like: Settings, Panel, Graphics, Raw data.

Formatting

You will find all the formatting options in the top toolbar, so you can centralize your headings, make them bold, format numbers like currency, etc. You can find them all on a single line or find some under the More as shown in this image:

Google Sheets formatting

They are similar to a word processor and quite self-explanatory. You can always undo if you make a mistake (Cmd + Z on Mac or Ctrl + Z on PC).

Try the following to format our basic table:

> Make the header bold and size 14px
> Center the column headings and make them bold
> Center the layer column
> Change the date format to 01/01/2018 (Hint: the date format is found in the button that says 123.)
> Add a dollar sign, $, to the rate column
> Add a border around the entire table.

Here is a GIF to guide you:

Formatting Google Sheets

(Note, you can also find formatting options under the Format menu, between the Insert and Data menu options.)

Alternating colors

Let me show you the option to add alternating line colors (bands) for your tables.

Let’s apply it to our basic table, highlighting the table and then the menu:

Format> Alternate colors

as shown here:

Google Sheets Alternating Colors

Remember, a little bit of formatting helps a lot. If your spreadsheet is more readable and organized, people will likely understand and absorb the information.

Removing formatting

This is my number 1 productivity tip on Google Sheets.

To remove all formatting from a cell (or cell range), press Cmd + on a Mac or Ctrl + on a PC.

This will save a lot of time when you want to remove formatting that is not yours or that you no longer want or need.

Advanced feature: Read more about formatting

3. How to use Google Sheets: basic data and formulas

Different types of data

You’ve already seen different types of data in Google Sheets in our basic table.

The main point to understand with the spreadsheet data is that each cell contains its own data, and a format applied to that data.

For example, suppose a cell contains:

2or
2.00or
$2or
$2.00

In each case, the underlying data is number 2, but with a different format applied each time. If we add 2 to each of these cells, we return the number 4 in all cases (with formatting applied).

Using Google Sheets: Basic data types
Basic data types in Google Sheets

You will notice that the currency, percentage and even dates in fact, they’re just secret numbers (dates? Really? Yes, they are, but that’s a discussion for another day). They are all aligned to the right, hanging from the right end of the cell.

The text is left-aligned by default.

If you want to force something to be stored as text, you can add a single quote, ' before the cell contents. So typing '0123 will show you how 0123 on your phone and align to the left. If you omit the single quotes, it will be stored as a number and displayed as 123 without the 0.

Doing math in numbers

Easy, like you do on a calculator.

You click on the cell where you want to do the calculation, type an equal sign (=) to indicate that you are performing a calculation, and then type your formula, for example

Google Sheets calculation

Notice how the calculation will appear in the formula bar (1) as well as in the cell 2).

You will notice that you get a preview of the answer (in this case, 25) above the formula.

Starting with functions: COUNT, SUM, AVERAGE

Technically, you already wrote your first formula in the section above on mathematical calculations, but in fact, your career in formula starts when you start using the built-in functions (of which there are hundreds!).

Going back to our basic table, let’s count how many members we have, what are the total monthly fees and what are the average monthly fees.

SCORE

Click in cell B8, type equal to (=) and start typing the word SCORE. You will notice that an autocomplete menu is displayed, showing all functions that start with C, like this:

Automatic formula filling

You can continue typing COUNT in its entirety or locate and select it from the list (hover over it and click it OR press Enter or click the Hit tab).

Then, you will see the auxiliary formula window, which informs you about the syntax of the formula and how to fill it in correctly:

Using the Google Sheets formula helper

In that case, the COUNT function expects a list of numeric values.

You need to select the range of cells you want to count. Then click on B4, keep the mouse pressed and drag to B7, so that the four cells are highlighted in orange and B4: B7 appears in its function:

Then, close the function with a closing bracket “)”:

=COUNT(B4:B7)

note: COUNT is used to count numbers. If you want to count the text (for example, the names), COUNT will not work (it will receive a 0). Instead, use COUNTA (with an A at the end), otherwise the method is the same.

SUM

Your turn! Try to create a total for membership fees in cell D8. Follow the same process as the counting function, except use SUM and highlight the values ​​in column D.

=SUM(D4:D7)

AVERAGE

You’re fine, so go ahead and average the membership fees. Use the AVERAGE function in cell D9.

=AVERAGE(D4:D7)

Psycho, you’ll notice that Google even helps you sometimes and suggests the exact formula you were looking for:

Google Sheets Average

Here it is:

Google Sheets sum and average

If you make a mistake with your formula, you’ll see an error message, probably something like # N / A, #REF!, # DIV / 0 etc.

You will need to re-enter your formula and correct it before continuing. However, these error messages provide a lot of context, so it’s worth understanding.

Advanced feature: Learn more about formula errors

What is the difference between a function and a formula?

Well, the two are used interchangeably and rather loosely, so that I don’t get caught up in that.

For the pedant, a function refers to the single method word (for example, SUM), while a formula refers to the entire operation after the equal sign, usually consisting of several functions.

Separating data with the Text feature in columns

Let’s suppose you wanted to First name and Last name, instead of simply Name as we have in our table of members of famous dead authors. How will we do this?

Well, of course, as with everything in spreadsheets, there are several ways, but let me show you the easiest, using the Text to Columns feature.

Back to our basic table, create a new column to the right of Name before the Layer column, that is, create a new blank column B.

Highlight the four names and click:

Data> Split text into columns …

From the submenu that appears, choose SPACE and marvel at how Google Sheets separates the full name into a first and last name. Feel free to rename the First and Last name columns as well.

How to use text divided into google sheets

Combining cells

Oh, how nice to hear you say! You intended to keep this complete Name column too.

No problem, let’s learn how to combine text so that we can reconstruct it.

Insert a new blank column between B and C (between Last name and Layer) and call it Full name, in cell C3.

Add this formula in cell C4:

= A4 & B4

That’s A4, ampersand, B4.

What it does is to combine the data in cell A4 with the data in cell B4 and produce it in cell C4.

Hmm, but this provides an output like this:

CharlesDickens

This is obviously not good enough! We need a space between names!

Change the formula for this by clicking right after the ampersand and adding double quotes, space, double quotes, and ampersands:

= A4 & " " & B4

Here we ask Google Sheets to add a space to the mix, and the output now will be:

Charles Dickens

Voilà, this is better!

Your formula is fine in cell C4, but how do you make it work for the other lines?

Copy it!

You can also:
i) right-click, copy, move down to select the next cell, right-click again, click paste or
ii) Cmd + C (on Mac) or Ctrl + C (on PC), move down to select the next cell and then Cmd + V (on Mac) or Ctrl + V (on PC) or
iii) drag the formula down by holding the small blue box in the lower right corner of the blue highlight around the original cell.

The most interesting thing is that, when copying this formula, the cell references will be changed from line 4 to line 5, from line 5 to line 6, etc., automatically! How cool is that!

(This is known as relative references. More on this in section 5 below.)

Here is a GIF to show this technique in action:

Google Sheets combines cells

4. How to use Google Sheets: killer features

Let’s look at some of the unique and powerful features that Google Sheets has, such as cloud-based software.

Comments (and notes)

Do you want to add some context to the numbers in your Spreadsheet cells, without having to add extra columns or disrupt their formatting?

Add a comment to a cell!

You can tag people (via their email address) for those who also want to see the comment. They can respond and mark it as resolved after taking action.

You can also add simple notes to the cells, if desired.

Comments and notes can also be deleted when they are no longer needed.

To add a comment to a cell, first select the cell and click the right mouse button to display the options menu. Select “Insert comment” and enter your comment.

To tag someone in your comment, type the plus sign (+) and the name or email address (you’ll see auto-complete options for your contacts, so you don’t have to enter the entire email address).

Google Sheets reviews

You will notice a small orange triangle in the upper right corner of the cell to indicate the comment. The comment will be displayed when you hover over this cell. If you click on the cell, it will also add orange shading to the bottom of the cell.

Comments can be edited, deleted, linked, responded to and resolved (the comment disappears from the Spreadsheet and is archived).

You can access and control all Comments in your Spreadsheet from the big comments in the upper right corner of the screen, next to the blue Share button.

(The first time you tag someone in a comment, you’ll be asked to share the spreadsheet with them. See more about that below.)

You can also add a Note to cells in the same way (look for it in the menu next to Insert comment). It is like a simplified version of a comment, intended for your own reference.

Share your sheets

(If you just added a comment and tagged someone else, as shown above, you should have already done this step!)

You can share your Google spreadsheets with others. As it is in the cloud, they can access your spreadsheet and see the same active spreadsheet you are on.

In other words, if you make changes, they will appear automatically and in near real time to everyone viewing the spreadsheet.

You can have multiple people viewing and working on the same spreadsheet.

Essentially, you have three options for sharing your Spreadsheet:

  • View-only access, so that the person cannot change or comment on any data
  • Comments-only access, so that the person can add comments, but still not make any changes to the data in the Spreadsheet
  • Edit access, so that the person can make changes to the spreadsheet (including comments)

The sharing options are found by clicking on the big blue button in the upper right corner, which will open the sharing settings:

Google Sheets sharing settings

You can take the link (the URL) of the spreadsheet, choose the sharing setting (view / comment / edit) and share that link with the people who want to see the spreadsheet. (1)

Or you can enter someone’s email address directly, choose the sharing setting (view / comment / edit) and share the spreadsheet directly with the person. 2)

If you want to review your sharing settings or have even more control, click on the advanced radio buttons. (3)

The Advanced sharing settings window:

Google spreadsheets that share advanced settings

Here you can:

> Get the share link (1)
> Review who has access 2)
> Change the access rights of any person listed (3)
> Invite new people to access the Spreadsheet 4)
> Change advanced owner settings to restrict who can control sharing settings and specific viewing / commenting rights (5)
> Confirm when finished (6)

I used the sharing settings link to share the template for this tutorial with you!

Real-time collaboration

Okay, so you shared your spreadsheet with someone. If they open it while you’re still working on the Spreadsheet, the cursor will appear in any cell (or range) they’ve selected. It will be a different color, for example, green to blue.

If they enter or delete data, you’ll see it in real time!

Real-time collaboration in Google Sheets

In this case, my active cell is the cell outlined in blue. I see another person, denoted by cell outlined in green, appear in this spreadsheet and enter the data in some cells before deleting them.

Magic!

5. How to use Google Sheets: intermediate techniques

Freeze panels

This is one of the most useful tricks you can learn on Google Sheets, which is why I recommend that you learn today.

Sooner or later, you will work with a data table that continues beyond the area shown on the screen (now, for example, I can see up to line 26, but it depends on the size of the screen and other factors).

When you scroll down to see the data further down in your table, you lose the column headings at the top of the screen and therefore cannot see the context of your columns.

Of course, scrolling up and down to see what the column headings are doesn’t make sense. It is a right path for spreadsheet errors and insanity!

See this data table showing the tallest buildings in the world, which extend below the bottom of what you can see on a single screen in Sheets. Scrolling results in the header row disappearing, so you no longer know which columns are:

Google spreadsheets scrolling insanity

What you need to do is freeze the header lines.

Fortunately, it’s super easy.

Click on the row number with the column headings (for example, row 3) and, from the menu, choose:

View> Freeze> Up to the current line (3)

Google Sheets Freeze Dashboards

Now your titles will remain in place. Ah, this is much better!

You can do the same with columns, if you want to freeze names, for example, so you can scroll horizontally across multiple columns of data.

Relative / absolute references

This is arguably the most difficult concept to understand in this tutorial. Se você o entende e pode aplicá-lo, tem uma compreensão muito boa de como as planilhas funcionam e está no caminho de se tornar um usuário qualificado.

Suponha que você tenha alguns dados na célula A1 e insira a seguinte fórmula na célula B1:

= A1

Essa fórmula recuperará todos os dados da célula A1 e os mostrará na célula B1.

Agora copie a fórmula (Cmd + C em um Mac ou Ctrl + C em um PC) e cole-a (Cmd + V em um Mac ou Ctrl + V em um PC) em outro lugar da sua planilha, por exemplo, célula D5.

Nada aparecerá em D5. Na verdade, você pode estar se perguntando se a sua cópia e colagem funcionou. Dê uma olhada na barra de fórmulas e agora você deve ver isso:

= C5

A fórmula está lá, mas aponta para uma célula diferente, não A1, portanto, não mostra os dados de A1.

Mas ainda aponta para a célula que é UM À ESQUERDA E NA MESMA LINHA como a fórmula original.

Ah ha! Eureka!

A fórmula copiou perfeitamente, mantendo a mesma estrutura, apontando para a célula à esquerda.

Essa propriedade incrível é chamada de referência relativa, o que significa que está em relação à célula em que a fórmula está (por exemplo, uma à esquerda).

É por isso que você pode arrastar as fórmulas para baixo nas colunas e elas serão alteradas automaticamente para calcular com os dados de suas linhas.

Percebido?

Agora, se você deseja corrigir sua fórmula (por exemplo, para que ela sempre aponte para a célula A1), use o que é chamado Referência Absoluta.

Bloqueamos a referência de célula na fórmula, para que o Planilhas Google saiba que não deve mover a referência quando a fórmula é movida.

A sintaxe usa um sinal de dólar, $, na frente da referência da coluna e na frente da referência da linha para bloqueá-los, respectivamente, da seguinte forma:

= $A$1

Agora, onde quer que você copie essa fórmula, a saída sempre apontará para a célula A1 e retornará os dados da célula A1.

Planilhas Google Referências absolutas e relativas

Observe que você pode bloquear a coluna ou a referência da linha e deixar a outra parte como uma referência relativa, mas isso está além do escopo deste tutorial.

Trabalhando com fórmulas entre folhas

Seguindo o tópico de referenciar outras células no momento, como se vincula aos dados em uma Planilha diferente?

Voltando mais uma vez à nossa tabela básica de membros da academia para autores famosos mortos, na Folha 1, vamos recuperar o cabeçalho da tabela e imprimi-lo na Folha 2 com esta fórmula, inserida na célula A1 na Folha 2:

= Sheet1!A1

Observe o ponto de exclamação no final da referência à Plan1, ou seja, Sheet1!

Agora vamos fazer uma soma simples de dados na Folha 1, mas mostre nossa resposta na Folha 2.

Na célula A3 na Planilha 2, digite a seguinte fórmula:

= SUM( Sheet1!F4:F7 )

Isso retornará a soma do intervalo de células F4 a F7 na Folha 1 e imprimirá a resposta na Folha 2.

Formatação condicional básica

A formatação condicional é uma técnica poderosa para aplicar diferentes formatos (por exemplo, sombreamento em segundo plano) a células com base em algumas condições.

Vamos ver um exemplo de formatação condicional, ou seja, formatação com base em condições variáveis.

Por exemplo, em um modelo financeiro, você pode mostrar um crescimento positivo de ativos com uma cor de fonte verde e um fundo verde claro, enquanto o crescimento negativo pode ser mostrado com letras vermelhas em um fundo vermelho claro. Isso fornece contexto extra aos seus números, e atributos de atenção (as cores) ajudam a transmitir a mensagem com mais eficiência.

Tirando a cópia simples da tabela de associação (pressione Cmd + Z em um Mac ou Ctrl + Z em um PC para voltar, se necessário), destaque a coluna final da taxa de associação e, em seguida, no menu:

Formatar> Formatação condicional

Aqui você pode escolher uma regra, por exemplo, valores inferiores a US $ 100 e destacá-los em vermelho:

Como usar a formatação condicional das folhas do google

Observe que o intervalo é mostrado (1), em seguida, um menu suspenso para escolher uma regra 2) e então a opção de formatação (3), que é o padrão em vermelho nesse caso, embora você possa personalizar completamente se desejar.

O poder da formatação condicional é destacar dados dinamicamente. The formatting is based on a rule, so if another value should drop below the threshold ($100 in this case), it will trigger the formatting rule and be highlighted red.

Advanced Resource: Conditional formatting to show % change

Sorting data

Sorting your data is a common request, for example to show transactions from highest revenue to lowest revenue, or customers with the greatest number to least number of purchases. Or to show suppliers in alphabetical order. Você entendeu a ideia.

So let’s sort the dead famous authors gym membership table, from earliest members to most recent members, i.e. we’ll sort our table based on the date column.

Highlight the whole table, including the header row. Then from the menu:

Data > Sort range…

and be sure to check the “Data has header row” option. Then you can select the column you want to sort by, and sort option from A to Z, or Z to A.

Google Sheets sort range

This re-sorts the table, showing the earliest members first:

Google Sheets sorted table

Filtering data

The next step after sorting your data is to filter it to hide the stuff you don’t want to see. Then you can just look at the data that is relevant to the problem at hand.

Taking the world’s tallest buildings data again, let’s apply a filter to only show skyscrapers built before the 2000s.

Click somewhere inside the data table (click on a cell containing data in the table), and then add the filters from the menu:

Data > Filter

or by clicking this icon on the menu bar above your Sheet:

Google Sheets Filter

You’ll notice a light green shading applied to row and column headings of your filtered table, and also a green border around your table. Most importantly though, you’ll now have little green filter buttons in each of your heading cells.

To filter out all the buildings built in the year 2000 or after, click the little green triangle next to the column heading Built, to bring up the filter menu.

You’ll notice you can manually select or de-select items to show. Let’s create a rule this time though.

Under the “Filter by condition” section, choose “Less than” and enter 2000 into the value box:

Google Sheets filter

Hit OK.

Ta-da!

You’ll see a reduced table with just 9 results. The 9 skyscrapers built before the year 2000:

Google Sheets filtered table

To remove the filter, click the green triangle button again (now solid green) and under the “Filter by conditions” set the rule to “None”.

There’s also a native FILTER function, by which you can formulaically filter your data.

Advanced Resource: How to use the FILTER function

Adding Charts

As a final exercise with the tallest building data, let’s draw a chart to show the buildings built by year, so we can see the trend graphically.

First, let’s sort the table by the year Built column, from oldest to newest. We can do this using the sort function that is provided in the menu when you click on the green triangle (from the filter).

Then highlight this single column called Built and from the menu:

Insert > Chart

This creates a default chart in your window and opens the chart editing tool in a sidebar.

Here you can change your data range and chart type, as well as a multitude of chart custom formatting options (which are also generally accessible by clicking on the elements directly in the chart).

Insert chart

The chart is an object in your Sheet now. Click it to select it, so it has a blue border around it. You can resize it and drag it to move it, just as you would with an image.

Using the Explore feature

The robots are coming!

Soon we won’t have to create complex formulas or charts ourselves. We’ll simply ask our Sheet to do it for us. Sound far fetched?

Well, you can do that now! The future is here.

(Soon we won’t have to open Google Sheets at all, we’ll simply type, or more likely speak, our data questions into a dashboard console and out will pop the answers, but I digress.)

Google Sheets has a feature called Explore, powered by Machine Learning/AI/Deep Learning/Neural Network sorcery, that will ingest your data, analyze it and show you some common answers (like the SUM, COUNTs etc. we’ve seen so far) and basic charts:

Google Sheets explore button

Let’s see an example, using the data about the world’s tallest buildings. Highlighting the column showing the years when each skyscraper was built and then clicking on the Explore button (bottom right corner) leads to these insights:

Google Sheets Explore insights

52 buildings in my set. The earliest tower on the list was built in 1931 (the venerable Empire State Building!) and the most recent built in 2017. The average of all the years built is 2007.

Some interesting and useful data points, without having to do a lick of work!

I mentioned charts, so let’s see an example of that with the Height (ft) column. Google Sheets Explore creates a Histogram for us, showing the distribution of tower heights:

Google Sheets Explore chart insights

We also get this interesting and insightful summary:

“Ranges from 1,148 to 2,717, but 80% of values are less than or equal to 1,480.”

Wow! At a glance we have the min and max heights, but more impressive, we know that 80% are under 1,480ft tall.

Not all of the “insights” are useful, and I don’t use this feature much myself yet, but it shows a glimpse of how we’ll all work with Sheets in the future.

If you’re still not worried about AI taking over your job (whether that’s data analysis work or something else) in the next 10 to 50 years, then have a read of this article.

BONUS: VLOOKUP

No “How to use Google Sheets” article would be complete without at least a quick look at the VLOOKUP function.

Why?

Because it’s the most famous function. Tell anyone you work with data and spreadsheets and they’ll immediately ask, “yes, but do you know how to do a VLOOKUP?”

It’s a good bellwether for spreadsheet competency, even though there are ultimately better ways to work with data. It’s also a relatively advanced formula compared to what we’ve seen so far, so if you can understand it, it bodes well.

What does it do then?

It’s used to search for a term and return information about that term from a different table.

Generally it’s used when you have two tables that share some common attribute (e.g. a name, or ID number, or email), but otherwise store different information. Suppose you want to bring this information together though. Well you can, and you link the information via that common attribute.

One table might have details an employee’s name and address, and the other table might have their name and work details like title and salary. You can use the VLOOKUP function to bring these bits of data together in a single table.

The syntax is as follows:

= VLOOKUP( search_term, table_to_search, column_number, FALSE )

In words: you select a search term which you search for in the first column of the search table. If you find it, you return a piece of information from the search table that relates to the search term (because it’s on the same row). The column number refers to which column of the search table you return the data from (1 being the column you searched in, so typically this number is 2 or greater).

Let’s see an example, by adding addresses to the dead famous authors gym membership table.

I’ve added a second table to Sheet 1, showing the addresses of our dead famous authors. What I’d like to do is add that data to the original gym table.

I want to do it efficiently with the VLOOKUP formula, rather than adding them manually. Not only is it much, much faster with larger tables (imagine ten thousand rows of data), but it’s also less error prone.

The address table is in columns I and J, with the names in column I and addresses in column J.

So I’ll search for my author name in column I and return the address from column J, and print the output into whichever cell I created my formula in.

The formula is:

= VLOOKUP( C4, $I$3:$J$7, 2, FALSE )

And this is what’s happening:

Google Sheets VLOOKUP

I search for the name (1) in the search table 2) and return the data from column 2 of the search table (3).

I don’t expect you to understand all of this immediately (and there’s a lot more to this formula than what I’ve shown here), but if you try it out and persevere, you’ll get there and realize it’s actually not too difficult.

(The FALSE argument, the final piece of information in the VLOOKUP formula means you want to do an exact match. 99.9% of the time you use a VLOOKUP, you’ll want to use FALSE.)

Advanced Resource: Dynamic charts with VLOOKUP

6. Next steps

The old adage “Practice makes perfect” is as true for Google Sheets as anything else, so hop to it!

Keep up-to-date with new articles, course launches and exclusive offers, by signing up for my Google Sheets newsletter, and get my free 80-page ebook on Google Sheets tips.

When you’re ready, check out the intermediate and advanced Google Sheets tutorials on this site.

Confira meu Google Sheets Bootcamp (professional training course).

Check out my free Advanced Formulas 30 Day Challenge course.

If all else fails, ask for help on the Google Sheets forum.

Wow, that’s it for this Google Sheets tutorial! Happy spreadsheeting!

This is an updated version of an article that was previously published. We update our tutorials to ensure they’re useful for our readers.

Paula Fonseca