One of the biggest revelations that I came across that really changed my whole view of learning was that learning how to ask the right questions or know where to get help completely transforms how you learn.
Knowing how to get help quickly and efficiently in Google Sheets is really going to cut away a lot of the frustration of finding the right information and get you there in a much more efficient way.
In the video below I will go over how to use all of Google Sheets help tools effectively and then discuss how you can research to find out how to build more complicated functions and processes in Google Sheets.
00:00 Intro
00:27 The help menu
01:13 Keyboard shortcuts
01:30 The help search bar
02:08 The Sheets Help tool
02:44 Understanding the help tool for functions
04:03 Preparing complex formulas with the Function List
05:14 How to ask the right questions
This is the last video in the Google Sheets beginners Youtube course. This is the first online course I have created. It was a long and rewarding journey. I’ve learned a lot along the way and I hope you got a lot out of the course.
If you enjoy the videoes and want to learn more about how to use Google Sheets, check out the Youtube Playlist:
Finding out how frequently something occurs compared to the rest of the data in a range is a pretty common task in Google Sheets. You will use it to find the frequency of grades in a grade book, the number of sales of an item compared to your total sales or the frequency of choices of a particular item in a survey.
In this tutorial, we will cover how to find the occurrence of a value in a range as a percentage in Google Sheets. I then show you how to do this quickly for all your values in a range as a set of formulas and as a pivot table.
Let’s get started…
Table of Contents
The Example
In our example, we have a survey item asking quite humbly and without any leading statements 🐐😇:
How awesome are goats?
A: Great
B: Good
C: Okay
D: Meh
E: Jealousy is a curse
Then in column A, we have a list of respondents and, in column B, we have a list of responses or choices.
Click to expand!
Here is a link to the raw Google Sheet data I am using in the example so that you can play along.
Just go to File > Make a copy. Then you have your own version to edit.
Get the Occurrence as a Percentage of a Single Value
To get the number of times a value occurs as a percentage of all the values in a list we will need to:
Get the count of the times the value appears in the range.
Get the total count of all the values in the range.
Divide the value count by the total count.
Change the result to be displayed as a percentage.
In our example, we want to humbly look for all the A’s, because it is pretty clear that the survey is a shallow ruse to fish for compliments.
Our range of choices is from B3:B20.
Let’s break our formula down into manageable chunks.
Get the total count of all A’s
To get the total count of all the A’s in our range we can use the COUNTIF function. This function allows us to get the count of something based on a value.
It doesn’t have to be just A though. You could use this function to get the count of, say, the number of students who got higher than a grade in a particular test.
It’s a really useful function.
COUNTIF takes two arguments:
The range you want to find your values in.
The criterion you want to find to count.
=COUNTIF(Range to search, Criterion)
In our example, the range is column B and the criterion is A.
=COUNTIF(B3:B20,“A”)
For our example, this will return a value of 7.
Get the total count of all values in the range
Next, we need to get the total count of all the values in a range. We can’t use the traditional COUNT function here because COUNT only deals with numbers. Instead, we need to use COUNTA that counts any value that isn’t blank.
COUNTA can take a single range or multiple ranges. However, we only want to count our range of choices. Here is what it would look like:
=COUNTA(B3:B20)
Which will return a value of 18.
Dividing the value count by the range count
Next, we need to divide the value count by the range count.
=COUNTIF(B3:B20,“A”)/COUNTA(B3:B20)
This will return a decimal value: 0.3888888889.
We can change this in our submenu by clicking the Percentage format symbol:
This will result in 38.89%. Much lower than what is conscionably right and true. 🐐
Get the Percentage of Occurrences of Each Value in the Range with Formulas
Get the percentage of one value occurring in our range is fine, but often we need to get the percentage of all the values in a range.
I try and keep my data as dynamic as possible. This means that I want to create formulas that can update easily if changes are made to the data. So that the next time I have to do a similar task, I already have a working template ready to copy and paste into.
In this example, I will show you how to create a summary chart of all the values as a percentage. Here is what our end result will look like:
Click to Expand!
If you are playing along, go to the next Google Sheets tab in the sample sheet named Range (formulas).
Titles and subtitles
First, in column F3, paste in the header: Occurrence of Choices as a percentage of all data. Format to taste.
Then in F4 and G4 add the sub-headers: Choice and Choice as % respectively.
Get a dynamic list of values
We could just list our values by hand but that doesn’t make a very good template. We can get a unique list of all of our choices by using the UNIQUE function in Google Sheets. UNIQUE takes a range of values.
=UNIQUE(B3:B20)
This won’t sort your choices alphabetically though. So let’s put our UNIQUE function inside a SORT function. The SORT function first takes the range you want to sort and then can take a number of optional arguments that we won’t use here.
Here is what our example will look like:
=SORT(UNIQUE(B3:B20))
Click to Expand!
Now we have a dynamic list of choices that will change automatically if our data changes. Sweet!
Getting the percentage of occurrences for each value
Now we can use our original formula to get the frequency of occurrence of each value in our Google Sheet. This time around I have made a few minor changes to the formula that you might miss at first glance:
=COUNTIF($B$3:$B$20,F5)/COUNTA($B$3:$B$20)
First of all, I have referenced the corresponding value cell on the left. In our first row, this is F5 or the value “A”. Next, I have added dollar signs to my range to lock it in or make it absolute. Otherwise, the cell range would have moved down as I dragged my formula down each cell in the column causing and error in our count.
You can find out more about absolute and relative references in my post here:
Once you have dragged down your formula to accommodate all your values you should get something that looks a little like this:
Click to Expand!
Finishing up
Finally, we can add the total to the bottom, just in case someone doesn’t know that everything should equal 100%. Oh, and to check that we didn’t mess up.
You can simply do this with the SUM function. That will take the range or all the calculated percentages. If you are lucky, when you put in the equals sign, Google Sheets will suggest the calculation for you.
=SUM(G5:G9)
Add in the Total as a side header and get it to stand out to make it look pretty.
Click to Expand!
Next, we will look at another way of achieving this that you might find easier or quicker.
Get the Percentage of Occurrences of Each Value in the Range with a Pivot Table
In this example, we are going to grab our occurrence percentages for each value using a pivot table. This can be a much quicker approach. Plus it has the added benefit of adjusting to the remaining values in a filter or a slicer.
Let’s get cracking!
If you are playing along, go to the next sheet tab named, Range (Pivot Table).
Pivot tables in Google Sheets allow you to analyse and transform data into interesting charts that allow you to quickly summarise your data.
Creating the pivot table
First, we will select our column of Choices. This time around, we will include the header:
B2:B20
Next, access the Pivot table by going to Data > Pivot Tables. A popup window will appear containing:
The data range you selected.
A choice of where to insert your data.
Select > Existing Sheet.
A line with an example will appear.
Select the grid symbol on the far right.
Click to Expand!
Another pop-up window will appear. Here you can choose what cell you want to start your Pivot Table.
Select cell E3 and then hit, Okay. ( You can drag the pop-up to a different location if it is in the way)
Click to Expand!
Back in the original popup and click Okay.
Creating your pivot table data
Once you hit okay, a Pivot Table editor in the sidebar would have appeared on the right. And a small template would have appeared starting in your selected cell.
Click to Expand!
Take a look at the Pivot Table editor. Look at the Rows item, select Add and then selectChoice.
You will get a little box with your Choice data.
Next, move down to Values and select Add. Again, select Choice. If you are lucky, Google Sheets would have added a count of all the values in the range. If not select the Summarized by dropdown and choose COUNTA.
At this stage, things should look like this:
Click to Expand!
We’re almost there.
Next, under Values, select the Show as dropdown and select % of column.
Bang! We did it! Go ahead and close the Pivot Table editor.
Now, to tidy up let’s change that verbose subheader with the COUNTA of blah…blah…
Click on cell F3,and enter “Choice as %”.
This is what it should look like now?
Click to Expand!
That’s it you are all done. The great news is that the pivot table will change as you change your data too.
Conclusion
We’ve covered how to get the frequency of a value occurring in a range by creating our custom formula. Then we have looked at how to make a dynamic summary of all the values as a percentage using formulas first and then as a pivot table.
So which one did you like the most the pivot table or the formulas to build the summary? Let me know in the comments below. I’d be interested to hear your thoughts.
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:
01:12 What can I choose to use data validation for?
02:10 Criteria – List from a range in your Google Sheet.
04:49 Criteria – List of items not in your Google Sheet.
05:18 Criteria – Having to choose a Date.
06:29 Criteria – Choosing numbers between a range.
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.
00:00 Preview of what our dropdown will look like.
00:22 Creating an ordered dropdown list from a range of cells.
01:59 Creating a dropdown list from horizontal data.
02:45 Create a dropdown list from a matrix of data.
03:50 Using relative and absolute values for dynamic lists.
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:
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.
00:00 Introduction to creating a list from a range with data validation.
00:08 Why use List of Items rather than List from a Range.
00:18 What it all looks like.
00:30 Creating a list from items.
01:43 Duplicating the dropdown.
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.
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:
00:00 Intro to using text validation in Google Sheets.
00:11 Enter text that contains certain a certain string of text characters, word or phrase.
3:05 Enter text that does not contain a certain string of text characters word or phrase.
03:54 Enter text that equals a certain string of characters, word or phrase.
04:19 Enter a valid email.
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!
00:34 Users entering different date formats. Is it a problem?
01:04 Formatting data validation cells. The solution to many of your date validation issues.
01:47 Displaying a date picker and quickly showing your users how to find it.
02:49 Enter date equal to a specific date.
03:41 Enter date before a specific date.
04:58 Enter date on or before a specific date.
06:52 Enter a date after a specific date.
07:20 Enter a date on or after a specific date.
08:10 Enter a date between two dates.
09:20 Enter a date that is not between two dates.
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.
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.
Formula example: =AND(LEN(B9) = 10, NOT(REGEXMATCH(TO_TEXT(B9),"\D|\s")))
10:14 Whole numbers between 1-20.
Formula example: =AND(ISNUMBER(B10), NOT(REGEXMATCH(TO_TEXT(B10),""[.]"")), B10>0, B10<21)
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.
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.
Google Sheets: WEEKNUM, WEEKDAY, FILTER, VLOOKUP, TODAY
One really helpful metric to check performance is to compare the current weeks worth of data against the previous weeks. I have had need of this in all sorts of projects such as:
Comparing sales performance from previous weeks to current.
Comparing hours worked on different tasks from week to week.
Compare the assessment balance between weekly tests.
Compare attendance numbers.
The problem is you often only receive data in to form of a date, like 1 Nov 2020. We will go through one possible solution to this problem in this tutorial.
We’ll run through an example together that you should be able to quickly adapt to your own project.
Have you ever had a set of data in Google Sheets where you want to use FILTER to grab rows based on if a condition in one column is met or if a condition is another column is met?
Maybe you got frustrated that the OR function did not work inside the FILTER as you might have expected. Well, let me tell you matey-potatey, ‘or’ works a little differently in FILTER.
In this tutorial, we will explore how to apply alternate ‘or’ conditions in a single column or multiple columns.
NOTE:If you are looking for how to use ‘or’ in a FILTER, I am assuming you know how to use FILTER in Google Sheets.
