The Ultimate Guide to Google Sheets Essentials

Do you want to learn how to use Google Sheets like a pro?

This post is packed with the essential features you need to know in Google Sheets that will take your skills to the next level. You’ll soon be able to create complex formulas, format data, and much more.

With this post, you’ll be able to work faster and more efficiently in Google Sheets. You’ll be able to get more done in less time, and have more free time for the things you love.

Download your free copy of the Google Sheets Essentials ebook with the accompanying workbook today and follow along!

Workbook Overview

The Google Sheets interface can be overwhelming if you’re not familiar with it already. These are the main components that you will need to use and understand to use Google Sheets successfully.

  1. Workbook Name displays the current file name and you can click on it to rename the file.
  2. Command Menu contains all the commands available in Google Sheets and is organized by type of command. For example, you can insert a chart or pivot table from the Insert menu.
  3. Saved Changes Notification provides information about the last time a change was made in the spreadsheet. Click on it to access the workbook version history.
  4. Share Menu contains collaboration and file-sharing tools. The menu contains the spreadsheet’s link which you can share with anyone.
  5. The Toolbar contains a selection of tools from the command menu. These tools or commands are some of the most frequently used in the spreadsheet and the toolbar is designed to provide quick access to them.
  6. Name Box displays the name of a selected cell or cell range. In the spreadsheet, cells are referenced by their column and row labels by default, but you can rename a cell or range of cells by double-clicking on the cell reference in the name box.
  7. Formula Bar allows you to enter data into a cell. You can also edit cell contents and write formulas with it.
  8. Columns are cells that run vertically from top to bottom. They are referenced with letters in the spreadsheet
  9. Rows are cells that run horizontally from left to right. They are referenced with numbers in the spreadsheet
  10. The Worksheet is a grid of all the cells in an active sheet
  11. Active Cell is a cell that is currently selected. When you select a cell, it has a blue border around it.
  12. Cell is an area created by the intersection between a row and a column. They contain your data and formulas.
  13. Sheet Tab contains information about the worksheets created in the workbook. Here you can create a new worksheet. You can also rename, hide, duplicate, or delete a worksheet.

Basic Formatting

Formatting refers to the appearance of contents in your spreadsheet. There are many formatting options that you can use to change the appearance of items in the spreadsheet.

  1. Number Format: You can format numbers in different ways such as whole numbers, decimal numbers, dates, accounting, and currency.
  1. Font: You can change the font style, size, and color. It also includes adding bold font, italicizing, and underlining options.
  2. Fill Color: You can fill cells with a range of colors.
  3. Borders: You can change the cell border with various thicknesses, line styles, and color options.

You will find all the formatting options in the Format menu or toolbar commands.

Top 30 Functions

Intro about what functions are. Show how to insert a function via the Insert tab.

FunctionDescription
SUMReturns the sum of values in cells or a range.
COUNTReturns the number of numeric values in a dataset.
AVERAGEReturns the numerical average value of a range, ignoring any text values.
SUBTOTALReturns a subtotal for a vertical range of cells using a specified aggregation function.
SUMIFSReturns the sum of a range depending on multiple criteria.
COUNTIFSReturns the count of a range depending on multiple criteria.
DATEConverts a year, month, and day into a date.
YEARReturns the year specified by a given date.
MONTHReturns the month of the year a specific date falls in, in numeric format.
DAYReturns the day of the month that a specific date falls on, in numeric format.
EOMONTHReturns a date representing the last day of a month which falls a specified number of months before or after another date.
LEFTReturns a substring from the beginning of a specified string.
RIGHTReturns a substring from the end of a specified string.
SUBSTITUTEReplaces existing text with new text in a string.
CHARConvert a number into a character according to the current Unicode table.
TEXTConverts a number into text according to a specified format.
TEXTJOINCombines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.
SPLITDivides text around a specified character or string, and puts each fragment into a separate cell in the row.
VLOOKUPVertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.
INDEXReturns the content of a cell, specified by row and column offset.
MATCHReturns the relative position of an item in a range that matches a specified value.
OFFSETReturns a range reference shifted a specified number of rows and columns from a starting cell reference.
IFReturns one value if a logical expression is TRUE and another if it is FALSE.
IFERRORReturns the first argument if it is not an error value, otherwise returns the second argument if present, or a blank if the second argument is absent.
SORTSorts the rows of a given array or range by the values in one or more columns.
FILTERReturns a filtered version of the source range, returning only rows or columns that meet the specified conditions.
UNIQUEReturns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.
GOOGLEFINANCEFetches current or historical securities information from Google Finance.
ARRAYFORMULAEnables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.
QUERYRuns a Google Visualization API Query Language query across data. Check out this post for a full guide to the QUERY function in Google Sheets.

VLOOKUP Function

The VLOOKUP function searches for a given value in a table and returns its corresponding value from a specified column. It is called a VLOOKUP because it searches a column vertically from top to bottom.

You will find the VLOOKUP function very useful when you want to get data that is located in another table, worksheet, and even another workbook.

VLOOKUP Syntax

= VLOOKUP ( search_key, range, index, [is_sorted] )

The VLOOKUP function has the following required arguments.

  • search_key: The value you want to find.
  • range: The range: A table where your search will take place.
  • index: The index number of the column containing the value you want to return.

📝 Note: The column index starts from 1. If you specify a index value less than 1, the formula will return a #VALUE! error. Specifying a index value greater than the number of columns in the selected range will return a #REF! error.

The VLOOKUP function has the following optional arguments.

  • [is_sorted]: Indicates whether to search for an exact or partial match.
    • TRUE will search for an approximate match and will return the value that is the closest match to the search_key. Your range will need to be sorted for this to work properly.
    • FALSE will search for an exact match and doesn’t require the column to be sorted. If no exact match is found, the function will return an #N/A error.
    • TRUE is the default when the is_sorted argument is omitted.

VLOOKUP Example

Check out this article 7 Ways to Lookup Data in Google Sheets for more ways to lookup data in Google Sheets.

INDEX & MATCH Functions

The INDEX & MATCH functions are individual functions that work together to perform similar operations as the VLOOKUP.

It is a preferred method among spreadsheet users for looking up data because the VLOOKUP can only search for values from the first column in a table.

With the INDEX & MATCH functions, you can search for items from any location in a table.

INDEX Function

INDEX returns the content of a cell as specified by a row and column offset.

INDEX Syntax

= INDEX ( reference, [row], [column] )

The INDEX function has the following required arguments.

  • reference: The range where you want to conduct your search.

The INDEX function has the following optional arguments.

  • [row]: The row index number containing the value you want to return. The default value is 0.
  • [column]: The column index number containing the value you want to return. The default value is 0.

📝 Note: The row and column index starts at 1, but when you input a value of 0 the INDEX function will return all rows or columns from the reference range.

MATCH Function

MATCH returns the position of the cell that contains a specified value.

MATCH Syntax

= MATCH ( search_key, range, [search_type] )

The MATCH function has the following required arguments.

  • search_key: is a mandatory argument that specifies the unique value you want to look for. It can be a text, a cell containing a value, or even a formula that returns a string or a number.
  • range: specifies the one-dimensional array within which you search for the key value. It is a mandatory argument.

The MATCH function has the following optional arguments.

  • [search_type]: Allows the function to decide how to search for the value.
    • If search_type is 1, the function assumes that the range provided is sorted in ascending order and returns the largest value less than or equal to the search_key.
    • if search_type is 0, the function searches for the exact match for the search_key.
    • if search_type is -1, the function assumes that the range provided is sorted in descending order and then returns the smallest value less than or equal to the search_key.
    • If omitted the default value is set to 1.

INDEX & MATCH LookUp Example

Conditional Formatting

Conditional formatting applies formatting to a cell based on a specific rule or criteria. You can set up conditional formatting rules to apply various formatting options when a cell meets a condition.

Conditional Format Example

In this example, a conditional format has been applied to the list in the Item column based on the Status column.

When a box in the Status receives a checkmark, a strikethrough format is applied in the corresponding cell in the Item column.

You can also use color scales to set up other conditional formatting options. With a color scale, cells will receive varying colors depending on the criteria.

You can add conditional formatting by going to the Format menu and selecting Conditional formatting from the options.

Keyboard Shortcuts

Shortcuts are great for helping you execute commands and navigate the spreadsheet quickly and easily.

Some of the keyboard shortcuts in Google Sheets need no special introduction as you most likely are already familiar with them.

These include the copy (Ctrl + C), paste (Ctrl + V), cut (Ctrl + X), bold (Ctrl + B), italics (Ctrl + I), and underline (Ctrl + U) keyboard shortcuts.

These shortcuts find usage across various software applications and as such are very commonplace and probably the most widely used keyboard shortcuts.

However, here’s a list of the top 20 Google Sheets specific keyboard shortcuts you should know.

ShortcutDescription
Ctrl + HFind and Replace
Alt + UpMove to previous sheet
Alt + DownMove to next sheet
Ctrl + Alt + Shift + HSee version history
Ctrl + Shift + VPast values only
Ctrl + \Clear formatting
Ctrl + DFill down
Ctrl + 0Hide columns
Ctrl + 9Hide rows
Ctrl + Shift + 0Unhide columns
Ctrl + Shift + 9Unhide rows
Shift + SpaceSelect row
Ctrl + SpaceSelect column
F7Spell check
Alt + N + VInsert pivot table
Alt + F1Insert chart
Ctrl + `Show all formulae
Ctrl + Alt + MInsert comment
Ctrl + /See keyboard shortcuts

Dropdown Lists and Checkboxes

A dropdown list is a data validation tool that allows you to control the contents that go into a cell. It is a very useful tool that helps to prevent errors during data entry.

Creating a Dropdown List

Follow these steps to create a dropdown list.

  1. Go to the Data menu.
  2. Select the Data validation option.
  1. Click on the Cell range section of the Data Validation dialogue box, click on the grid icon to select the range you want to apply the data validation.

In this example, the dropdown lists will be added to B2:B5.

  1. In the Criteria section, select List from a range from the dropdown options.
  2. Select the range containing the list of items you want to use in the Enter a range or formula input.

You can use the dollar $ sign to lock the selected range unless you intend to expand the list.

  1. When you’re done click OK.
  1. The On invalid data allows you to choose what happens when there’s an invalid entry. Show warning flags the entry but keeps the input. Reject input triggers a notification pop-up that prevents invalid entries.
  1. In the Appearance section, you can create a help text by ticking the Show validation help text checkbox.
  2. When you’re done with the data validation setup, click on Save.

The cells in the Category column now have a dropdown arrow.

You can click on the dropdown arrow to reveal and choose from the list of options.

Insert a Checkbox

A checkbox is a small square box you can insert inside a cell. It allows you to add a tick mark that can indicate various things such as the completion of a task/step.

One advanced way you can use the checkbox involves using it to trigger some other type of action such as hiding rows or columns when checked.

Inserting a checkbox is simple.

  1. Select the range where you want to insert the checkboxes.
  1. Go to the Insert menu.
  2. Select the Tick box option.

When you insert the tick box, it comes with a default TRUE or FALSE display in the formula bar. You can change these values in the Data validation menu.

Go to the Data menu and select the Data validation option.

In the Criteria section, tick the Use custom cell values options and add the values you want the checkbox to display.

Click on Save when you’re done.

Now when you go back to your checkbox column, it will reflect the new custom values.

For more on data validation check out the definitive guide to data validation in Google Sheets.

Smart Fill

The Smart Fill feature is an AI-powered tool that uses identified patterns in existing data columns to suggest intelligent autofill options.

For the Smart Fill to work properly, you will need to provide at least two examples. Sometimes, you may have to provide more examples, depending on the complexity of the task.

When suggesting autofill options, Smart Fill searches through a library of formulae to find a specific one that will do the task. You can even see the formula used for providing the suggestions before accepting the autofill option.

Asides from making data entry super easy, the Smart Fill feature can also help you increase your understanding of the Google Sheets formulas.

Using the Smart Fill is very straightforward.

First, you need to enable Smart Fill from the Tools menu.

Click on Tools menu and select Autocomplete. Make sure all three options are checked. Smart Fill works best when all three options are enabled.

After you’ve done that, you just need to start entering data in an adjacent column to your data.

You can use the Smart Fill to do all sorts of things including extracting characters from one column to another, combining or concatenating data from two columns, changing text capitalization, etc.

Suppose you want to extract the name of the City from the address column. Doing this task will need a combination of the text functions and can get pretty daunting. With Smart Fill, you don’t have this problem.

You just need to start typing the city names from the Address column into the City column. In this example, Smart Fill has identified the pattern that you’re trying to extract from the second component of the Address column.

It then uses this information to suggest a formula that you can use to extract the remaining city names and populate the empty cells in the CIty column.

You only need to accept this suggestion by clicking on the green tickmark to autofill the empty cells.

= MID ( C2, FIND ( ",", C2 ) + 1 + 1, FIND ( CHAR ( 160 ), SUBSTITUTE ( C2, ",", CHAR ( 160 ), 2 ) ) - 1 - ( FIND ( ",", C2 ) + 1 ) )

Without the Smart Fill, this is the formula you’d have needed to create to extract the city names from the Address column.

Using the Smart Fill involves following the same process irrespective of the task. You just have to provide a few examples and the Smart Fill will kick into action.

For more on smart fill check out everything you need to know about Smart Fill in Google Sheets [10 examples].

Sort and Filter Data

How to add filters to the dataset and basic sorting.

Sorting means arranging your dataset in a clear and defined order. Usually, this order involves sorting data in ascending or descending order.

When you sort data in a defined order, it becomes easier to locate specific items.

Sort Data

You will find the sort command in the Data menu.

You will also find it in the right-click menu after you select a range.

Sorting a dataset is super easy. Assuming you want to sort this sample dataset by the Quantity column so that all small quantities will appear first and the larger quantities will appear towards the end of the range. This is in ascending order.

Follow these steps to sort your dataset in ascending order.

  1. Select any cell in the column to which you want to sort.
  1. Go to the Data menu.
  2. Click Sort sheet.
  3. Select Sort sheet by column F (A to Z).

This will sort the data in ascending order (from smallest to largest). If you want to sort the other way round, that is from largest to smallest, use Sort sheet by column F (Z to A).

Column F is now sorted in descending order, as well as other columns in the data including the column headers.

To avoid including the column headers in the sort operation ensure you freeze the top rows.

To freeze the top row select any cell in the top row, then go to the View menu ➜ Freeze1 row.

Now when you sort the dataset using column F, the column row headers stay in position.

Notice that the Sort range option is greyed out all through. That is because you cannot use it unless you select a specific range.

Follow these steps to sort a text column in alphabetical order.

  1. Select the cells in the City column.
  2. Go to Data
  3. Select the Sort range option.
  4. Click on Sort range by column B (A to Z).

This will sort only the items in the City column.

Check out this post on everything you need to know about sorting data in Google Sheets.

Filter Data

Data filtering involves keeping in view only items that meet specific criteria.

When you have a large dataset and you only want to see certain items, the filter feature can help you remove items that don’t match the specifications of what you want to see.

Follow these steps to add filters to your dataset.

  1. Select any cell in your dataset.
  2. Click on the Filter icon in the toolbar.

You can also add filters from the Data menu.

Once you create a filter, you will see three lines that look like an upsidedown pyramid. This symbol is your filter applied to the table.

You can use this to filter the table as per your needs.

Assume you want to see only information regarding Regina in the City column.

Follow these steps to filter on a specific item.

  1. Click on the upsidedown pyramid near the column label.
  2. Select Clear to deselect all the options.
  1. Scroll down the list to find Regina or simply type it into the search bar.
  2. Click on the city name to select it.
  3. Click the OK button to apply the filter.

The dataset now reflects only information about Regina city. And you get the filter icon in column B indicating that the dataset is being filtered by that column.

To remove filters, just click on the filter icon in the toolbar.

Check out the full guide to filtering data in Google Sheets.

Pivot Tables

Pivot tables help you summarize your dataset in order to gain insight.

Pivot tables are great for managing a large dataset because it arranges information in a simple and meaningful layout that makes it easy to spot key values.

Follow these steps to create a pivot table.

  1. Go to the Insert menu.
  2. Click on the Pivot table option.

This will open the Create pivot table dialogue box,

  1. Use the Data range section to select the data you want to use in the pivot table.
  2. The Insert to section is where you choose a location to place the pivot table.
  3. Click on Create when you’re done.

This will create an empty pivot table in the workbook which is ready to use.

Add Fields to Rows, Columns, Values, and Filters

After you click on create, the spreadsheet will insert a pivot table in the location you specified. You will also get a new Pivot table editor window to the right of your screen.

The Pivot table editor is where you control everything that happens inside the pivot table.

The Pivot table editor is divided into two sides.

  1. The right section contains.
    • You have the data range used to create the pivot table. You can change the data range using the grid icon.
    • There are Suggested pivot tables. Click on any of these to create the pivot table.
    • The Rows, Columns, Values, and Filters sections of the pivot table. These determine how the pivot table displays and summarizes your dataset.
  2. You have the column labels present in your dataset. The pivot table assumes that the first rows contain column headers and it treats them as such. Before you insert a pivot table, make sure your dataset has column headers.
    • The search window at the top of the column header names allows you to run a quick search for column names if your dataset has a large number of columns.

As a rule of thumb, Rows, Columns and Filters take categorical data, while Values take numerical data.

You will usually place columns that have numbers you want to summarize in the Values area and columns with text data in the Rows, Columns, or Filters area.

You can populate the pivot table using the Add command or by simply dragging and dropping items from the fields list into the pivot table Rows, Columns, Filters, or Values section.

With fields added to your pivot table, you can easily see a summary of information from your data.

To make this even more effective, you can use features like conditional formatting to highlight important information.

Slicers

Google sheets have multiple options through which you can apply filters your data. One such filtering option is slicers!

Slicers allow you to apply filters in an intuitive and visual way when using datasets or pivot tables.

Slicers are a visual filter. They allow you to slice your tables, Pivot tables, and Pivot Charts by providing a visual clickable way to filter data of a specific type.

One reason why Slicers are a preferred method for adding filters to your dataset is that they’re user-friendly. Also, they provide a lot of flexibility and other users of the spreadsheet can interact with them without changing the underlying data.

How to Create a Slicer

This dataset contains records of items sold. It has 1912 rows and 11 columns.

The data consists of different items that are being sold daily in different regions, with quantities, price, sales price, line price, etc. You can see an overview of this data, as shown in the screenshot above.

Adding a slicer to the dataset is easy.

  1. Go to the Data menu,
  2. Select the Add a slicer option.

After you click on Add a slicer, the spreadsheet will insert an empty slicer. You need to select a column to use in the slicer from the Slicer editor in order to use the slicer.

The Slicer editor has two sections.

  1. The Data section will be active by default. You can change the range in the Data range section.
  2. In the Columns section, use the dropdown to select a column to use in the slicer.

Here, the City column has been added to the slicer. Now you can use the slicer to filter the data by City.

Assume you want to see only records for Saskatoon, you can do it in two ways.

  • Filter by values
  • Filter by condition

Both options are available when you click on the All command in the slicer.

Filter by values allows you to choose from a list of the values available in the data. In this case, the slicer shows a list of all the city names available in the City column. This option will be active by default.

Filter by condition allows you to filter the data based on specific criteria which may or may not conform with the values present in the selected column. It also allows you to apply filters using a custom formula.

Using the Filter by values,

  1. Click on the Clear command to deselect all values that have been preselected by default.
  2. Use the search box to find the city name. You can also scroll through the values to find the city name.
  3. Click on Saskatoon.
  4. When you’re done, click on OK.

The data now only displays information about the city of Saskatoon. The slicer provides information that the City column only displays 1 out of 101 cities available in the dataset.

You can use the Filter by condition option to achieve the same result.

  1. Click on the Filter by condition dropdown.
  2. Click on the None box that appears. Select Text is exactly.
  1. Type the city name in the second box that appears. Make sure you have the right spelling.
  2. Click on OK.

When using the Filter by condition method, make sure you select all the values in the Filter by values option.

Add Date Slicer

Follow the earlier steps to add a slicer. When you’ve added a slicer, choose the Order Date column.

For a date slicer, it is ideal to use the Filter by condition option.

There are date conditions from which you can choose.

  • Date is – returns records for the specific date provided.
  • Date is before – returns all records before the specific date provided.
  • Date is after – returns all records after the specific date provided.

Assume you want to see all Saskatoon sales records that occurred before the 30th of June 2020

  1. Select Date is before option from the Filter by condition options.
  2. A new box will appear. Click on it and select exact date from the dropdown options.
  3. In the new box that appears, enter 2020-06-30.
  4. Click OK.

The table now reflects all the sales made in Saskatoon before June 30, 2020.

When inputting a date, make sure the date format is similar with the one available in the date column.

Slice by Values

You can also slice your data by values.

Create a slicer using the Sales column.

  1. Select the greater than condition from the conditions option
  2. Enter a specific number.
  3. Click OK.

The table shows sales greater than 10 that happened before June 30, 2020 in Saskatoon.

You can see how easy it is to use slicers. Unlike filters, they don’t live at the top of the column and it is very easy to interact with slicers.

Others who have access to the data can also interact with slicers even when they have view-only permission. This means they can slice and dice the data without changing the underlying data.

Check out the full guide to slicers in Google Sheets.

Conclusions

Google Sheets is a powerful tool that can help you get more organized and efficient with your work.

Like any complex tool, it takes time and effort to learn how to use it effectively.

The Google Sheets Essentials ebook has given you a great place to start learning the basics of Google Sheets. However, there is always more to learn.

If you’re ready to really master Google Sheets, then check out this 8 course Google Sheets bundle!

Enhance your Google Sheets skills with this course bundle, covering the entire data workflow, from dealing with raw data through to creating beautiful reports.

This bundle includes these courses.

  • Google Sheets Essentials
  • Data Analysis in Google Sheets
  • Build Dashboards with Google Sheets
  • Pivot Tables in Google Sheets
  • Advanced Formulas in Google Sheets
  • Extreme Formulas in Google Sheets
  • Introduction to Apps Script
  • Automation with Apps Script

Thanks and good luck in your Google Sheets journey 😃

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!