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.

2. Font: You can change the font style, size, and color. It also includes adding bold font, italicizing, and underlining options.
3. Fill Color: You can fill cells with a range of colors.
4. 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.

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.

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.

3. 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.

4. In the Criteria section, select List from a range from the dropdown options.
5. 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.

6. When you’re done click OK.

7. 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.

8. In the Appearance section, you can create a help text by ticking the Show validation help text checkbox.
9. 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.

2. Go to the Insert menu.
3. 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.

2. Go to the Data menu.
3. Click Sort sheet.
4. 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 ➜ Freeze ➜ 1 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.

3. Scroll down the list to find Regina or simply type it into the search bar.
4. Click on the city name to select it.
5. 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,

3. Use the Data range section to select the data you want to use in the pivot table.
4. The Insert to section is where you choose a location to place the pivot table.
5. 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.

3. Type the city name in the second box that appears. Make sure you have the right spelling.
4. 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 😃