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!
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:
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
In our example, we have a survey item asking quite humbly and without any leading statements 🐐😇:
How awesome are goats?
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.
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.
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:
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.
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. 🐐
Check out the short video demonstration:
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:
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.
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:
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:
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:
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.
Add in the Total as a side header and get it to stand out to make it look pretty.
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:
Next, access the Pivot table by going to Insert > 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.
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)
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.
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:
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?
That’s it you are all done. The great news is that the pivot table will change as you change your data too.
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 these video tutorials, we will cover the Filter tool in Google sheets.
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: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:34 Filter by values
07:46 Filter by colour
08:27 Filter by condition
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.
Filter views also have the benefit of being viewable only to the user who created them. This means that you will not annoy other users with your view when you selected one of your filtered views.
Take a look at the video:
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:
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,
00:30 The basic copy and paste function and it’s shortcuts.
01:59 Copy and pasting basic formatting.
02:38 Differences with formulas with copy and paste.
04:01 Copy and pasting – across select ranges in the same Sheet.
04:43 Copy and pasting – from one Google Sheet to another.
05:38 Copy and pasting – from a different Google Sheet to another.
06:19 Copy and pasting – from other document types like Text, Excel, or a Webpage.
10:15 Copy and pasting – same values or a range of values multiple times.
11:57 Copy and pasting – formatting only.
13:12 How copying the formatting also copies conditional formatting.
13:55 Copy and pasting – excluding borders.
14:36 Copy and pasting – column widths only.
15:40 Copy and pasting – formulas only.
16:44 Copy and pasting – data validation
17:27 Copy and pasting – conditional formatting only.
18:40 Copy and pasting – transposed ( changing rows to columns and columns to rows)
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: