Google Sheets – Use Slicers to Filter a Pivot Table on the Fly

In order to sort your spreadsheet data in a powerful and organized way, we can add Pivot Tables to isolate specific data, then Slicers to further sort those tables. These filters can sort data in a different way than the built-in pivot table filters and provide additional options for your data sets.

For this example, we’re going to keep the source sheet as is and display the filtered list. The starting data is a typical spreadsheet that we will utilize in two different pivot tables.

 

The shared Google Sheets for this post is available as part of the free course.

Original Data

Create your Pivot Table

To create the first Pivot Table, go to Data then Pivot Table.

Using menu to get to Data then Pivot Table

We will use the source as the entire table including headers. We will be showing the sales by item and an additional Pivot Table to sort by item and date.

Selecting the source data

Select “Existing Sheet” and drop the table into G2 by clicking the box to the right, then selecting G2.

Choosing to use the existing sheet

We’ll sort this by the quantity of sales by item description. Next to Rows in the Pivot Table Editor, click Add, and select Item Description.

Choosing item description for Columns

The Value will be “Quantity Sold.”  We want this to be summed, so we will leave this drop-down with the SUM value. This creates a simple table that sums the sales by item description.

Choosing qty as the value

Create Another Pivot Table

To create the second Pivot Table, once again select Data then Pivot Table. We’ll use this table as a source and put this in G13. We’ll leave space because pivot tables expand and contract depending on what you do with them.

Creating a second pivot table
Second pivot table

First, we’ll add a row for the dates using the same method above, then right-click and group the dates. Go to “Create Pivot Date Group” and click “Month” for this example. We’ll do quantity again as the Value to populate the total sales for the items. 

Create Pivot Date Group
Date Group

This means we’ll need to add items to this second Pivot Table, so we’ll drop in another row by clicking the Add button next to Row, and selecting Item Description.

adding item description row

These two different tables are showing the same data in different ways – one by item description and one by item description and date.

Filter – First Method

The first way to filter these Pivot Tables is to create normal filters that will act on both of these pivot tables as long as they are in the same sheet and working on the same data. Left-click on the Pivot Table, then go to the bottom of the editor to add a filter – let’s say to filter Item Description.

Pivot Table Filter Options

If you left-click on the status, you can either filter by condition or by values (which will be the same in the slicer). We’re going to take “garden hose” out.

Filter options

Filters are specific to one pivot table. You won’t see this being filtered unless you look at the options in the editor.  If we remove the filter, Garden Hoses will come back to the table. This means that no one will be able to see that this data is being filtered unless they look at the editor. This also only applies to one Pivot Table.

After the filter is applied

New Filter Option – Slicers

Instead of filters, we’re going to use Slicers and see how they operate differently. Go to Data then Slicer.

Data slicer

Now it’s asking for the data range. Click the original table, not the pivot table, for the data range. We’re going to slice the source data.

Select data range

Drag the slicer over to the right. Since we’ve already selected the range, it automatically populates in the Slicer options. We’re going to skip the column for now, and we’re going to make sure that the checkbox is selected so that it applies to Pivot Tables.

Clicking checkmark to apply to pivot tables

In addition, you can further customize the slicer by choosing different colors, fonts, etc. Let’s leave it the way it is.

Customization options

Let’s filter by item description. We can only do one column right now – if we need more, we’ll need to make additional Slicers.

Selecting Item Description for the column

The first thing we’re going to notice is that the Slicer is already here for everyone to see. There are the same options to filter by conditions and values when you click it. We’re going to do the same thing again and filter out Garden Hose. Start by clicking the Filter icon on the left.

Slicer filter

Add Another Slicer

The data still exists but is hidden. If we want to filter by date, we will have to add an additional Slicer. All the Slicers on one sheet have to work with the same ranges. Additionally, they are sheet specific, so they will only filter the data on the original sheet.

The changes we have been making will only work in our user profile with our sheet, so if you want these settings to apply to additional users, you need to edit the slicer and pick “Set Current Filters as Default” – that way they will see the same filters that you have.

Slicer filter

Let’s continue filtering by date. We’re going to select date once again, and “Filter by Condition.” We only want it if it’s after August 1st, 2019. This table only has July values on it.

Filter by condition

Choosing a date range

Before I click “okay” we can see that this table has July values in it. When we update it, it will only have august.

Two filters

Now the sheet has both Slicer filters applied.

4 Ways to Find the Top or Bottom Values Using Google Sheets

In this example, we will be looking at four different methods for sorting a table of data in Google Sheets. All of the examples are from this Google Sheet. We will review simple sorting, filter creation, utilizing the SMALL and LARGE functions, and using the SORTN function. These techniques can also be found in this this video on the Prolific Oaktree YouTube channel.

Sorting Data

The first technique is simply to sort the data in a sheet utilizing the menu options, then deleting what information we don’t want. This is a very rudimentary and simple method, but it unfortunately ends with us losing data (that we might need later).

First, begin by selecting the data that you want to sort, then go to “Data” in the top menu, and navigate to “Sort Range.” We specifically want to sort row C.

Sort range options

Click the “Data has a header row” box, and click the dropdown menu to “Run Time.”

Has header row

Now that the data is sorted, you can manipulate it as you see fit, including deleting or moving the data you do not need. This technique, however, puts the data out of order and makes you lose the rest of the data.

sorted table

Creating Filters

The second method of manipulating data is to select the data again, go to the Data menu once again, and this time instead of choosing sort, we will create a filter. Both sorting and filtering are covered more in-depth in Prolific Oaktree’s Google Sheets Basics course.

create a filter menu option

Filters can sort, but have much more functionality. For this example, we will sort by Throw Distance. We’ll get the top three throwers. Click the dropdown at the right side of the header.

filter dropdown button

This dropdown puts the data in order for you. There are a variety of ways you can sort here.

filter dropdown button

Hit Clear and then choose the longest three by scrolling down to the end and put checkmarks on the last three values.

filter values

You can use functions in this menu for additional sorting capabilities. For this example, we will simply use the final three pieces of data. There are ties, so the sorter decides to show all of these entries as well. The final sorted list is still in its original order.

filtered

The advantage to using Filters is that the data still exists and is still able to be interacted with. This means you won’t have to worry about missing data.

The LARGE and SMALL functions

The following two methods are much more powerful ways to manipulate and view your data that draw from the data without directly affecting it. We have created another area of the sheet where we can manipulate the data separately from the original list itself. Here, we will go over the LARGE and SMALL functions within Google Sheets.

What the LARGE function does is return the largest number in a chosen dataset. In this picture, we can see “$D$3:$D$21” which tells the function to look at the data collected from D3 through D21.

range of LARGE function

From here, we tell the function how to rank the data it finds. We can simply type in “1” or any other number depending on our needs, but in this case, we will refer to “H3,” which is the cell that contains our ‘Rank 1’.

LARGE function

We did this as a cell reference to H3 so I could drag the LARGE function down to do 2, 3, and 4.

Using ‘$’ on the range function in the formula tells the function “don’t shift this range down when I drag this formula down.” It’s a fixed reference for a range.

Rank 2 has the same formula, but by dragging it down it is now looking for the second-largest value, and so on.

selecting the range

The SMALL function is simply the opposite of the Large function, and in the example here, it is picking the fastest run time and sorts to the slowest run time.

SMALL function

These functions don’t interact with any other row, but also don’t affect the dataset itself.

SORTN Function

Native to Google Sheets and not found in Excel, the SORTN function is a powerful function that you can utilize to maximally sort to your desired preferences.

SORTN

Start by highlighting all the data in your Sheet. This function will auto-populate all the fields, but you only have to type it once. Every variable is broken up with commas.

Highlighting range for SORTN

The function asks of these options, how many do you want me to return? We’ll put six. In the upper left corner of the screen, we can manually input 6 with another comma.

Data returned from SORTN

After 6, it asks what we would like to do with ties. For now, let’s choose 0.

SORTN tie value

The next variable is what column we are going to look at. This number is the number of columns from the left where the data resides. If we want to sort by throw distance, for example, it is the third column from the left, so we will enter 3.

Column value of SORTN

Then we will select FALSE for “how to sort”. Then we hit Enter.

SORTN how to sort values

We have now picked up the longest six throw distances and it picked up names and jersey numbers, while leaving all the original data. Additionally, any changes to the original data will get automatically populated in the new function’s list.

Result of SORTN

Every situation is different, but now you have four different options to choose from. Let’s hope you can find the one that’s best for you!

 Live examples in Sheets

Go to this spreadsheet for examples of methods to find the top or bottom values that you can study and use anywhere you would like.

Collect Data into Google Sheets | Forms, Quizzes, Surveys

By effectively utilizing Google Forms, you can sort, manipulate, and track a detailed database of information gathered from a number of sources. This tutorial will show you how to create a Google Form, how to send it to as many people as you would like, and how to manipulate the data afterward in Google Sheets. This tutorial is also in a video on YouTube.

Creating the Form

Begin by opening the main dashboard of Google Drive, and by clicking the “new” button and selecting “Form.” You can also right-click the dashboard, navigate the cursor to “More,” and create a new form.

create a new form

The form menu will appear, and you can begin filling in the questions you would like to be answered. Start by adding a title to your form, then fill in the questions below.

Give your form a title

For our example, we will be taking the names, emails, and other information from the recipient.

Question Types

The first type of question will be short answer – the first field, ‘First Name’, has a selection box to the right. This allows the report creator to decide how the recipient will answer the question. You may choose between a variety of options, but most reports utilize common question types like short answer or multiple choice.

Short answer question type

The ‘First Name’ field will be left as short answer, but we will make it a required field as well. Making a field required will place a red asterisk next to the question for the recipient, and they will not be able to submit the form without answering this question. This option is a toggle on the bottom right of the question box. We will do the same process with the next two fields, ‘Last Name’ and ‘email address.’

Name fields

To ensure the recipients enter valid dates, we will choose the “Date” option in the question type selection box. This ensures that the data we receive from the form recipients will be uniform and in a valid format. The specific date format (whether month/day/year, day/month/year, or other variation) can be modified as well.

date field question type

For questions with a specific answer out of a list, we will use the multiple-choice option.  We will create the “What time should we contact you?” field this way, allowing three options for recipients to answer. The number of choices can be added with the “Add Option” button, or subtracted with the “X” button on the right side.

Multiple choice question type

Additionally, multiple-choice questions can have multiple answers. The “Do you own a car?” field will use a check-mark multiple choice option, which allows users to select as many options as are applicable.

Check mark

Sending the Form

With a paid G Suite account, you have the option of clicking a box at the top of the “Send Form” window to automatically collect email addresses within your organization. Otherwise, there are multiple ways to send the form to recipients.

If you contact me here or email me at [email protected], you will receive a code for 20% off of your first year of G-Suite. If you sign up for G Suite using this link and apply the code during checkout, the discount will be applied.

Option to collect email addresses

You can email a notice to your list about the form. This notifies the users about the form itself, and you can also choose to include the form itself inside the email so that recipients can immediately fill it out upon receiving the notice.

Include the form in the email

Google Forms can also produce a link that you can send via a variety of forms. This gives your users a clickable option that doesn’t use their email (for sending through text messages, for instance).

Send form as link

Lastly, you can embed the form as HTML directly into your website. Using this option keeps the form directly onto your webpage so your users don’t have to leave.

embed the form in html

Analyzing the Data

To analyze the responses received, you can go back to the main page of the form and click the “Reponses” tab next to the “Questions” tab. This allows you to graph the data received from the form.

See responses to form

On the upper-right side of this menu, you can see the database itself. This holds all the raw data you have received.

Option to review responses in Sheets

Now that you have the data in Google Sheets, you can manually sort and analyze everything at a glance! Learn more about how to sort and analyze data in online training courses that Prolific Oaktree offers with videos, linked Google Sheets, notes, and quizes.

View entries in Sheets

Google Sheets – Insert Sheet Names into Cells

See the linked video to learn how to insert the sheet names into cells in Google Sheets. This page is the for the text that accompanies the YouTube video.

The code for the regular expression is =IF(TODAY()=TODAY(), REGEXREPLACE(CELL("address",'Detail 1'!A1),"'?([^']+)'?!.*","$1"), ""). The text in the box below is meant to be copy and pasted for the custom functions that were used in this sheet. The text in this Doc is the same, grab it from here if it’s easier.

//Return the current sheet name.
function SheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

//Return all current sheet names.
function SheetNames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out 
}

//Return specified sheet name
function SheetNumber(idx) {
if (!idx)
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
else {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var idx = parseInt(idx);
if (isNaN(idx) || idx < 1 || sheets.length < idx)
throw “Invalid parameter (it should be a number from 0 to “+sheets.length+”)”;
return sheets[idx-1].getName();
}
}