Google Sheets Beginners: Charts (40)

A top data presentation is all about displaying your data in an easy to understand and memorable way. This is why being able to quickly create charts with your Google Sheets data is an essential tool.

In this video tutorial, I’ll cover the basics of how to build a number of common chart types in Google Sheets. I’ll then show you how to quickly format them and cover a few gotchas new chart users face.

Here is a link to the data, if you want to play along!

Charts Data

Check out the video!

Contents:

00:00 Intro
00:27 How to access charts
01:07 Google Sheets suggested charts
01:23 Pie charts
01:31 The Chart Editor
01:36 The Setup Tab
02:43 The Customize Tab
02:47 Chart Style
03:18 Pie Chart menu
03:42 Slice labels menu
04:09 Pie slices menu
04:39 Chart Axis & Title
05:03 Legend menu
05:16 Interacting with a chart
05:39 Bar Charts
06:39 Modifying the data in a chart
07:23 Formatting your chart
10:37 Copying a chart as a template
11:11 Aligning charts in your Google Sheet
11:39 Column Charts
12:10 Displaying the total or average of a range
13:20 Area Chart
14:20 Updating the range
14:50 Setup for auto-update

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Filter (38) & Filter View (39)

In these video tutorials, we will cover the Filter tool in Google sheets.

Filter

You can set up a specialised view of your Google Sheet data that will let you sort, or filter out unwanted data in any column with the Filter tool.

This tool can pack a punch. You can sort different columns alphabetically, numerically, suing custom rules or even background colours. You can then use the filter part of the tool to remove unwanted parts of your data.

Don’t worry though, the filter will not affect your original. All you have to do is remove the filter and your data is back to normal.

In this video tutorial, we will go into the details on how to use the Filter tool. We will also cover some common gotchas, that beginners stumble on when trying to use Filter for the first time.

Check it out:

00:00 Intro
00:26 Accessing the Filter tool
01:14 About column headers and Filter
01:26 Fixing up hidden headers
01:39 A basic sort
01:46 Sorting A-Z & Z-A
02:00 Sorting with numbers
02:28 Sort by a background colour
03:09 Sort by order then colour
03:34 Sort by multiple columns (gotchas!)
04:23 Sort by grades
05:25 Filtering
05:34 Filter by values
07:46 Filter by colour
08:27 Filter by condition

Filter Views

You can create a variety of different filtered views without code or changing your data with Google Sheets Filtered Views. These views can be really handy for quickly presenting your data to your team or clients.

Take a look at the video:

00:00 Intro
00:17 How to create a Filter View
00:44 Filter view layout
01:03 Create your first Filter View
01:32 Updating the range
02:48 Accessing & hiding Filter Views
03:07 Create another Filter View
04:13 Duplicating a Filter View
05:57 View a list of Filter Views
06:21 Remove a Filter View
06:41 Educating your team

If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Grouping Rows and Columns (36)

When your Google Sheet spreadsheet starts to grow in complexity it is often a good idea to group similar parts of your data so you can easily collapse and expand them as you need them. Fortunately, Google Sheets has your back with the Grouping tool.

In this tutorial, we will look at how to group rows and columns in Google Sheets. We’ll also look at a few advanced capabilities and show you how to avoid some common traps.

Check it out!

This tutorial covers:

  1. 00:00 Intro
  2. 00:28 Example of grouped data
  3. 01:44 How column and row groupings work.
  4. 02:52 How to create groupings in Google Sheets
  5. 02:59 Grouping columns
  6. 04:03 Grouping adjacent columns and rows
  7. 05:13 undo grouping
  8. 05:38 Grouping rows
  9. 06:27 Sub or secondary groupings.

If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Google Sheets Beginners: Cutting, Copying and Pasting ( + bonus complete guide) (35)

Just when you thought that copy and paste was a no-brainer you start working in Google Sheets spreadsheets and find that there is a little more here than meets the eye.

The good news is that Google  Sheets has some great pasting types to help you copy or cut your data and put it in the cells or ranges exactly how you want it.

This tutorial takes you through a simple beginners tutorial on copying and pasting in Google Sheets to rapidly get you underway and then we dive into a Complete Guide to cut, copy, all the various paste types and possible gotchas you might face.

Cut, Copy & Paste Basics

The most important takeaway from this tutorial are the shortcuts that are going to be vital for you to boost your efficiency:

  • Cut : PC – ctrl + X, Mac – ⌘ + X
  • Copy : PC – ctrl + C, Mac – ⌘ + C
  • Paste : PC – ctrl + V, Mac – ⌘ + V
  • Paste just the values  : PC – ctrl + shift + V, Mac – ⌘  + shift + V

Cut, Copy & Paste Complete Guide

Here we dig into the weeds of all the capabilities that Google Sheets has with Cut, Copy and Paste.

In this tutorial, I’ll cover,

  1. 00:00 Intro
  2. 00:30 The basic copy and paste function and it’s shortcuts.
  3. 01:59 Copy and pasting basic formatting.
  4. 02:38 Differences with formulas with copy and paste.
  5. 04:01 Copy and pasting – across select ranges in the same Sheet.
  6. 04:43 Copy and pasting – from one Google Sheet to another.
  7. 05:38 Copy and pasting – from a different Google Sheet to another.
  8. 06:19 Copy and pasting – from other document types like Text, Excel, or a Webpage.
  9. 10:15 Copy and pasting – same values or a range of values multiple times.
  10. 11:57 Copy and pasting – formatting only.
  11. 13:12 How copying the formatting also copies conditional formatting.
  12. 13:55 Copy and pasting – excluding borders.
  13. 14:36 Copy and pasting – column widths only.
  14. 15:40 Copy and pasting – formulas only.
  15. 16:44 Copy and pasting – data validation
  16. 17:27 Copy and pasting – conditional formatting only.
  17. 18:40 Copy and pasting – transposed ( changing rows to columns and columns to rows)

Conclusion

So did anyone get the Tribble easter egg? Anyone…? 👓🐐

Spend some time masting the shortcuts for cut, copy and paste. Most of them will apply for all sorts of software (not just Google Products). So it is well worth knowing.

If you enjoy the video and want to learn more about how to use Google Sheets, check out the Youtube Playlist:

Google Sheets Shorts: Beginners

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

The Monster Guide to Data Validation in Google Sheets: Free Course

In this tutorial, we will cover everything there is to know about Data Validation in Google Sheet.

Why am I writing this tutorial? Well, there is a lot to cover for one, but I also find that there are a lot of snags and nuances to Data Validation in Google Sheets that make it easy for even the experienced user to get stuck.

I didn’t start out to write a course on data validation. To be honest, I was just preparing some notes for a short introduction as a part of a beginners video series, but the more I looked at it the more I found that data validation really deserves a deep dive on its own.

There are even some things in this course that I learnt that I wish I had known years ago that would have saved me and my team a lot of grief.

If you are coming at data validation as a complete beginner or a seasoned spreadsheets veteran, there should be something in here for you to learn. Below you will see a contents page of major topics and you will also find an embedded video tutorial for each of the topics if you need to just jump to something important.

Using the contents page to jump to a link will also give you a URL to that item if you want to bookmark it for quick reference later.

Let’s dive in!

Data Validation Basics

What is data validation?

Data validation helps you to control what your users enter into your cells. Among many other things it also allows you to:

  • Create drop-down menus for your users.
  • Provides date pickers.
  • Ensure users of your Google Sheet to enter text into a cell only.
  • Validate emails and URLs.
  • Even create designer rules for your users to abide by to ender data in a cell.

Why is data validation important?

Let’s say you need users to put in a set of numbers in a cell. These numbers are then calculated in another cell by one of your awesome formulas. Perhaps your data entry users were not properly instructed or were confused about their task. Without data validation, they could put in words instead, breaking your carefully crafted formula.

Likewise, you may want to reduce responses to a question you have asked in a sheet to specific items that your users select from a dropdown list for you to better analyse the frequency of the response. Without data validation rules, your users could enter any response that may well be similar, but is now incredibly difficult for you to analyse.

Finally, you may just want to help guide your uses and provide convenient suggestions for them to enter their data in cells. You can also do this with data validation.

How do I get me some data validation goodness?

To get to data validation in Google Sheets, you have two approaches. First, select the cell or range for your data validation and then:

  • In the menu bar go to  Data > Data validation. OR
  • Right-click and scroll to the very bottom.

Data Validation basics video

Check out the Data Validation basics video for a quick tour of what this Google Sheets tool can do for you:

In the video tutorial, I’ll cover,

  1. 00:00 Intro.
  2. 00:17 Accessing the data validation menu.
  3. 00:41 What does each part do?
  4. 01:12 What can I choose to use data validation for?
  5. 02:10 Criteria – List from a range in your Google Sheet.
  6. 04:49 Criteria – List of items not in your Google Sheet.
  7. 05:18 Criteria – Having to choose a Date.
  8. 06:29 Criteria – Choosing numbers between a range.
  9. 08:14 Removing Data Validation

List from a range. Dropdowns!

In this part of the course, we will dive into creating dropdown menus from reference data in your Google Sheet. I’ll guide you through how to set up and organise your reference data to work best for your data validation list from a range dropdown.

We will even dip our toes into creating a basic dynamic dropdown list that changes based on your selection of a previous dropdown list.

Check out the video below!

In the video tutorial, I’ll cover,

  1. 00:00 Preview of what our dropdown will look like.
  2. 00:22 Creating an ordered dropdown list from a range of cells.
  3. 01:59 Creating a dropdown list from horizontal data.
  4. 02:45 Create a dropdown list from a matrix of data.
  5. 03:50 Using relative and absolute values for dynamic lists.
  6. 05:57 Wrapping up data validation list from ranges.

An  extra note on relative and absolute ranges in data validation in Google Sheets

As we mention in the video above, you can make your dropdown somewhat dynamic by using relative ranges.

With a normal Google Sheets formula or function in a cell, you are able to drag down the cell to duplicate the range. In this case, the formula will update automatically and reference the data in the next row or column of the cell depending on if you dragged the data down or across.

For example, in cell C1 we have the formula = A1 + B1. If we drag C1 down to cell C2. Then the formula in that cell would be = A2 + B2. Drag it down again and the formula is =A3 + B3 and so on.

If you don’t want your formula reference to change you are ostensibly locking them and making them absolute. So if we want to just add A1 to all the cells in column B we could change our column C equation to look like this = A$1 + B1 for our first row. The dollar sign indicates that row one will be locked or made absolute.

Dragging the formula down now to cell C2 will result in this  = A$1 + B2, C3 to = A$1 + B3 and so on.

We have locked the row in place.

If we drag our C1 cell to the right to cell D1, however, we would get this = B$1 + C1 . So to lock cell A1 from moving to the right we need to lock the column too with a dollar sign like this = $A$1 + B1.

You can find out more about relative and absolute cell references in my tutorial here:

How do I lock certain cells in a formula in Google Sheets?

In data validation, it is a little different, however. Your data validation’s row and column cell references will come locked or absolute out of the box. You will need to update them manually. You can do this by adding an equals sign at the front of your range and removing the relevant dollar sign ($) reference, just like in the video.

List of Items

You can also create a data validation dropdown list from a list of items in your Google Sheets cells. One of the benefits of this is that you don’t need to reference any data in your Google Sheet.

Nevertheless, there are some things to consider and I will be covering them in the video below.

Check it out!

  1. 00:00 Introduction to creating a list from a range with data validation.
  2. 00:08 Why use List of Items rather than List from a Range.
  3. 00:18 What it all looks like.
  4. 00:30 Creating a list from items.
  5. 01:43 Duplicating the dropdown.
  6. 02:01 A better approach to duplicating data validation rules.

Validating Numbers

You can also restrict cells to only display numbers or even ranges of numbers. Google Sheets data validation has a large list of rules to help you set your cells up just how you want them.  Having said that, there are some pitfalls that you may fall for that I hope to steer you away from in this video below. I’ll also show you a few alternate solutions.

Have a gander!

  1. 00:00 Intro to using number data validation.
  2. 00:25 Number is between two digits.
  3. 01:14 Can I use decimal numbers?
  4. 01:32 Numbers is not between to digits.
  5. 02:18 Enter a number less than a value.
  6. 02:52 Number is less than or equal to a value.
  7. 03:15 Number is greater than a value.
  8. 03:43 Number is greater than or equal to a value.
  9. 04:07 Number is equal to a value.
  10. 05:12 Number is not equal to a value.
  11. 05:31 Number is between multiple sets of ranges.

Validating Text, Emails and URLs

The text data validation in Google Sheets is more than just validating some simple text, it allows you to check if certain text exists in your cells or not. The text criteria allow you to check for valid emails and URLs. However, like most technology, there are some limitations that we will highlight in the tutorial below:

  1. 00:00 Intro to using text validation in Google Sheets.
  2. 00:11 Enter text that contains certain a certain string of text characters, word or phrase.
  3. 3:05 Enter text that does not contain a certain string of text characters word or phrase.
  4. 03:54 Enter text that equals a certain string of characters, word or phrase.
  5. 04:19 Enter a valid email.
  6. 05:36 Enter a valid URL or website.

While text validation has a few weaknesses we can use custom formulas to resolve these. I will provide a few examples in part 1 of using custom formulas with data validation.

Validating Dates and a Date Picker in Google Sheets

I’ve really leaned on date data validation quite a lot in my Google Sheets career. It is extremely helpful to help guide users into putting in the correct date into the spreadsheet that you need to conduct good analysis.

We will also talk about educating your team on using the hand dandy date picker!

Check out the video!

  1. 00:00 Intro to date validation.
  2. 00:16 Enter a valid date (My favourite!).
  3. 00:34 Users entering different date formats. Is it a problem?
  4. 01:04 Formatting data validation cells. The solution to many of your date validation issues.
  5. 01:47 Displaying a date picker and quickly showing your users how to find it.
  6. 02:49 Enter date equal to a specific date.
  7. 03:41 Enter date before a specific date.
  8. 04:58 Enter date on or before a specific date.
  9. 06:52 Enter a date after a specific date.
  10. 07:20 Enter a date on or after a specific date.
  11. 08:10 Enter a date between two dates.
  12. 09:20 Enter a date that is not between two dates.
  13. 09:56 Wrap up.

While there might seem like a bit of repetition in this tutorial, I have sprinkled in a few troubleshooting tips and tricks along the way to help you better prepare your date validation.

Custom Data Validation with Using Simple Regular Expressions

I won’t lie, exploring the depths of custom data validation is a vast and endless rabbit hole (Of fun! 🐐).

I’ve gone ahead and split up this part of the course into two chapters. This first one explores using custom data validation with regular expressions.

Wait! Please done run away.  Here! Here’s a cookie… 🍪. Okay, gang! Grab em and hold them down! Someone get the eye vices.

Yeah, yeah, regular expressions can be scary. They are so esoterically irregular (Pun intended) that they can be quite difficult to memorise or master. But I will let you in on a little secret: only the most bespeckled, sun fearing of goats memorise data validation rules. The rest of us just look them up.

In this chapter, we will cover just a few very common rules that you can simply copy and change for your own use. Hopefully, they should be useful for your day to day Google Sheets work.

Check out the video!

I this tutorial we will cover:

  1. 00:00 Intro to custom formulas.
  2. 00:09 A basic example.
  3. 01:54 Removing data validation.
  4. 02:02 Cell must contain the word.
    1. Formula example: =REGEXMATCH(B2,"\b(be)\b")
  5. 05:03 Must contain at least one word from a list.
    1. Formula example: =REGEXMATCH(B3,"\b(be|at|in)\b")
  6. 07:01 Must start with a word.
    1. Formula example: =REGEXMATCH(B4,"^(Be)\b")
  7. 08:09 Must end with a character.
    1. Formula example: =REGEXMATCH(B5,"\?$")
  8. 09:34 Wrap up.

Got a bit of Stockholm Syndrome for regular expressions now? Check out a couple more examples of using regular expressions in Google Sheets:

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

Google Sheets: Conditional Formatting with Custom Formula

More Data Validation Using Custom Formulas in Google Sheets

In this second part of using custom formulas in our data validation, we will branch out and use some other formulas to create some interesting validation rules. We will also start using some more compound formulas that I hope will inspire you to create your own custom formula rules. If you come up with a good one I would love to hear about it in the comments below.

I’ll be sure to add the formula examples for each part in the show notes below the video if you want to copy and modify them for your own project.

  1. 00:00 Intro to custom formulas in data validation part 2.
  2. 00:27 Greater than the current day’s date.
    1. Formula example: =B6>TODAY()
  3. 02:28 Date must be a weekday.
    1. Formula example: =AND(WEEKDAY(B7) <> 7, WEEKDAY(B7) <> 1)
  4. 04:47 No more than 15 characters in the cell.
    1. Formula example: =LEN(B8) <= 15
  5. 06:08 Must meet specific phone number parameters.
    1. Formula example: =AND(LEN(B9) = 10, NOT(REGEXMATCH(TO_TEXT(B9),"\D|\s")))
  6. 10:14 Whole numbers between 1-20.
    1. Formula example: =AND(ISNUMBER(B10),
      NOT(REGEXMATCH(TO_TEXT(B10),""[.]"")),
      B10>0,
      B10<21)
  7. 12:37 Wrap Up

Oh…yeah. I squeezed in some more regular expressions too. Umm…sorry. Not sorry. 🐐

Using Data Validation on Checkboxes

In this chapter, we will look at how we can control check or tickboxes in Google Sheets. One of the cool things I discovered is that  I  changed the true and false condition to whatever I want.

Such power! Yes, I abused it.

Have a look a the video, if you dare!

  1. 00:00 Intro checkbox validation.
  2. 00:10 Enforce entry of true and false in a cell.
  3. 01:18 Change what is truthy and what is falsy.
  4. 02:38 Changing a checkbox from another cell.
  5. 04:21 Wrap up.

Update dropdown list in Google Sheets dynamically based on previous dropdown choice

That’s a mouthful. However, this is one of the most searched for things people want to do with data validation. In our previous chapter on List from a Range we covered one approach, but it is kinda messy.

Here, I will give you a more appropriate example on how to create a dropdown menu that changes based on another menu choice.

One great example of this is a list of cities after you have chosen a state. Or a list of parts once you have selected a specific tool.

Here is the link to the starter file if you want to follow along. Go to File > Make a copy to generate and edit your very own version.

Starter File

Here’s the video!

  1. 00:00 Intro.
  2. 00:16 Demo example.
  3. 01:23 Why using relative cells is not a great option.
  4. 01:44 Add a ‘Notes’ Google Sheets tab.
  5. 02:24 Designing the input data.
  6. 02:33 Getting a unique list of items.
  7. 03:13 The item selector dropdown setup.
  8. 03:39 Create a dynamic option list in ‘Notes’.
  9. 04:39 Create a dynamic dropdown list.
  10. 05:00 Wrap up.

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice.

I’ve added a bonus tutorial on dynamic dropdown lists here after some comment queries. Check it out. It contains a detailed written tutorial and a YouTube video!

Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice

Conclusion

Hey, you made it! Congratulations on complete the course. I hope you found it useful. It was a big one to build for you, but I enjoyed the process.

So what did you find useful? Can you think of anything  I missed? I would love to hear you thought in the comments below.

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi