Slicers in Google Sheets

Slicers in Google Sheets are a powerful new way to filter data in Pivot Tables.

They make it easy to change values in Pivot Tables and Charts with a single click. Slicers are extremely useful when building dashboards in Google Sheets.

Video: How Slicers Work And How To Add Them

What do Slicers in Google Sheets do?

Consider this basic dashboard in a Google Sheet. It consists of three small pivot tables and a chart, displaying (fictional) data about house sales.

Slicers in Google Sheets dashboard

You’ll notice the two black boxes above the chart labeled “Agent Test Slicer” and “Lead Source”. These are slicers in Google Sheets.

When you click on the drop-down arrow in the slicer, it brings up a filter menu:

Slicer filter menu

In the Home Sales dashboard example above, we can click on the slicers to focus on a subset of agents and/or subset of lead sources.

For example, we might select “Emma Johnson” only, so that we can see data that is just relevant to this person.

The three pivot tables (labeled 1, 2 and 3 in the image below) and the chart (labeled 4) are all updated to just show the rows of data from our dataset that are associated with “Emma Johnson”.

Slicer in Google Sheets

You’ll also notice that the slicer has updated the drop-down to say “1 of 4” instead of “All”, to signify that we’ve filtered on one value (“Emma Johnson”) from a possible set of 4 names.

(Note: you can absolutely choose more than one value at a time in your filter.)

How do you add a Google Sheets slicer?

(Note 8/1/19: According to the G Suite blog, it may take up to 15 days for feature visibility.)

Slicer Template

Feel free to make your own copy of this file (File > Make a copy…)

Slicer Template including the Home Sales Dashboard

The Data

Start with this table of fictitious real estate data (from Sheet1 of the template above):

Data in Google Sheets

Create a Pivot Table

If you’re new to Pivot Tables, have a read of Pivot Tables in Google Sheets: A Beginner’s Guide

Back in the real estate dataset, insert a Pivot Table: Data > Pivot Table

Create a simple Pivot Table in a new Sheet, for example this one shows property types and total sales price for each category:

Pivot Table

Add a Slicer Control

Back in the Data menu, choose: Data > Slicer

If your cursor was inside the Pivot Table when you added a Slicer, it’ll be added automatically and you’ll see the default slicer control:

Slicer column choice

If your cursor was outside the Pivot Table in another cell, you’ll be prompted to choose the data to use inside your slicer.

The first thing to do with a slicer is to select a column.

In this example, let’s choose the “Side” column, which is the column containing data about whether the agent acted as a Buyer or Seller in the transaction.

Our slicer will update to show the column name:

Slicer column added

We now have a perfectly good, working slicer.

However, you can customize it under the “Customize” menu and change the heading and formatting.

Here we give it a more descriptive title and change it to have a blue background:

Customize slicer in Google Sheets

Slicer Settings Menu

When you’ve finished setting up your slicer, you can get back to the editing menu by clicking the 3 dot menu next to the drop-down. This brings up the slicer’s settings menu:

Slicer Settings Menu

You can choose to copy the slicer (super helpful if you need to create several slicers), edit it, delete it or set the current filters as default.

Using Slicers

Clicking the drop-down arrow brings up the slicer filtering menu.

Slicer Filter in Google Sheets

This will look familiar if you use Filters with datasets in Google Sheets. It has exactly the same functionality, but now exists in a standalone control that you can position next to Pivot Tables and Charts as part of a dashboard report.

The data in our Pivot Tables will update when we change the filter applied in our Slicer menu.

Look at the data in the Pivot Table changing in this example as I change between Buyer and Seller:

Google Sheets slicer

I can’t wait to explore slicers in more depth and start adding them to more complex dashboards.

More information

Announcement: Additional tools for enhanced reporting in Google Sheets on the G Suite Updates Blog

Filter charts and tables with Slicers in the Docs Editors Help page.

The Complete Guide to Simple Automation using Google Sheets Macros

Google Sheets Macros are small programs you create inside of Google Sheets without needing to write any code. They allow you to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

Sound good?

They sure are! Read on to learn how to use them, see some examples, discover their limitations and also see how they’re a great way into the wonderful world of Apps Script coding 😀

Contents

  1. What are Google Sheets macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. A peek under the hood of Google Sheets Macros
  6. Google Sheets Macros examples!
  7. Resources

Continue reading The Complete Guide to Simple Automation using Google Sheets Macros

How To Make a Table in Google Sheets, and Make It Look Great

A collection of formatting tips for how to make a table in Google Sheets.

Let’s start with a simple table, completely devoid of any formatting:

Table no format

How to Format a Table in Google Sheets

The goal with this article is to show you how to make a table in Google Sheets look great, like this:

How to make a table in Google Sheets and make it look great

Header rows

Go for bold, center-aligned and wrap the text, so it all shows.

Google Sheets formatting column headings

Aligning columns

Let’s align those columns, they’re messy!

Table alignment GIF

Center column headings, ID numbers, or other standardized entries.

Left align text.

Right align numbers (which they are by default). Really the only exception to this rule is for numbers that are not really numbers, i.e. they’re not being compared against each other or being used in any sort of calculations. For example, the ID numbers above can be center aligned.

Right align dates (which they are by default). However, if you’re working with just a year, as in the example above, you can get away with center aligning, just be consistent.

Formatting data

Choose appropriate formatting options for the data in your tables.

Add thousand separators to big numbers above a thousand. Add currency signs to financial numbers to add context.

Choose an appropriate number of decimal places. For example 2 decimal places if you need that level of detail to show cents on the dollar, but remove decimal places if they’re not needed for large numbers:

Google Sheets formatting format as currency

I could stop the article here, as the table is now sufficiently formatted to maximize legibility.

However, for tables that are part of dashboard reports or are being presented to clients, you can add colors to match your branding, or bring additional context to your numbers.

How to make a table in Google Sheets with Alternating colors

Sure you can do this manually, but it’s way easier and quicker to do with the Alternating Colors tool under the formatting menu.

Simply highlight your whole table and then open up the Alternating Colors option sidebar. Select the color scheme you want, whether you have a header or footer row, and even choose custom colors if you wish:

Google Sheets formatting alternating rows

How to make a table in Google Sheets with colors and indicator arrows

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Google Sheets percent table

Now take a look at the same table with colors and arrows added to call out the % change column:

Google Sheets table

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

Google Sheets Char formula

=char(A1)

=char(A2)

=char(A3)

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

Google Sheets custom number format menu

3. Change the 0.00% in the Custom number formats input box to this:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

as shown in this image:

Google Sheets custom number format

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

Read more about custom number formatting here: Excel custom number formats

(It’s an Excel article, but the rules are the same.)

Updates to the Google Sheets Training Courses

Sep 18 announcement: Whew! This was a big task, but today I’m proud to announce that my video training courses are bang up-to-date! I sent out emails to students earlier today, announcing updates to 40 videos in my Google Sheets training courses.

Specifically:

Read on for more details.
Continue reading Updates to the Google Sheets Training Courses

A Guide To The Google Sheets Filter Function

The Google Sheets Filter function is a powerful function we can use to filter our data. The Google Sheets Filter function will take your dataset and return (i.e. show you) only the rows of data that meet the criteria you specify (e.g. just rows corresponding to Customer A).

Suppose we want to retrieve all values above a certain threshold? Or values that were greater than average? Or all even, or odd, values?

The Google Sheets Filter function can easily do all of these, and more, with a single formula.

This video is lesson 13 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge.

What is the Google Sheets Filter function?

In this example, we have a range of values in column A and we want to extract specific values from that range, for example the numbers that are greater than average, or only the even numbers.

The filter formula will return only the values that satisfy the conditions we set. It takes two arguments, firstly the full range of values we want to filter and secondly the conditions we’re going to apply. The syntax is:

=FILTER("range of values", "condition 1", ["condition 2", ...])

where Condition 2 onwards are all optional i.e. the Filter function only requires 1 condition to test but can accept more.

How do I use the Filter function in Google Sheets?

Filter function in Google Sheets

For example in the image above, here are the conditions and corresponding formulas:

Conditions Formula
Filter for < 50 =filter(A3:A21,A3:A21<50)
Filter for > average =filter(A3:A21,A3:A21>AVERAGE(A3:A21))
Filter for even values =filter(A3:A21,iseven(A3:A21))
Filter for odd values =filter(A3:A21,isodd(A3:A21))

The results are as follows:

Google Sheets Filter function

(Note: not all the values are shown in column A.)

Grab the data and solution file for this tutorial:
Click here to get your own copy >>

Can I test multiple conditions inside a Google Sheets FILTER function?

Absolutely!

For example, using the basic data above, we could display all the 200-values (i.e. values between 200 and 300) with this formula:

=FILTER(A3:A21, A3:A21>200, A3:A21<300)

Can I test multiple columns in a Google Sheets Filter function?

Yes, simply add them as additional criteria to test. For example in the following image there are two columns of exam scores. The Filter function used returns all the rows where the score is over 50 in both columns:

Filter Function Google Sheets

The formula is:

=FILTER(A1:B20,A1:A20 > 50,B1:B20 > 50)

Note, using the Filter function with multiple columns like this demonstrates how to use AND logic with the Filter function. Show me all the data where criteria 1 AND criteria 2 (AND criteria 3...) are true.

For OR logic, have a read of this post: Advanced Filter Examples in Google Sheets

Can I reference a criteria cell with the Filter function in Google Sheets?

Instead of hard-coding a value in the criteria, you can simply reference another cell which contains the test criteria. That way you can easily change the test criteria or use other parts of your spreadsheet analysis to drive the Filter function.

For example, in this image the Filter function looks to cell E1 for the test criteria, in this case 70, and returns all the values that exceed that score, i.e. everything over 70.

Filter Function Google Sheets Reference Cell

The formula in this example is:

=FILTER(A1:A20,A1:A20 > E1)

Can I do a filter of a filter?

Yes, you can!

Use the output of your first filter as the range argument of your second filter, like this:

=FILTER( FILTER( range, conditions ), conditions )

Resources

Advanced Filter Examples in Google Sheets

Google documentation for the FILTER function.

Related Articles