Create a TIMELINE SLICER for your Excel Dashboard!
Table of Contents
· Intro
· What is a timeline slicer?
· Introducing the Dataset
· Creating the Pivot Table
· Building the Pivot Table
· Creating the Timeline Slicer
· Video on Creating a Timeline Slicer
Intro
In this blog post, I’ll show you how to create a timeline slicer in Excel. It’s a pretty useful tool that will make your dashboard more interactive, especially if you have a timeline graph, because as you click on the different buttons on a timeline slicer, the values on all your other visuals will change based on the buttons you press.
I’ll explain more on this later, so keep reading!
What is a timeline slicer?
A timeline slicer is a filtering tool that lets you see values based on the certain dates or times that you filter on. You’ll see how it works in this example I’m about to show you…
Introducing the Dataset
To use the timeline slicer, I’m going to use a Kaggle dataset on Netflix movies and TV shows that you can find here.
Here’s a quick glance at the data:
I formatted the “release_year” column as a date, which is going to be important for later. Also, I noticed that for some reason, all the dates are inaccurate because none of these movies and TV shows came out in 1905. They came out wayyy later.
However, I’m still gonna work with it because the point of this blog post is to show you HOW to create a timeline slicer, and you can apply this process to ANY other dataset.
Creating the Pivot Table
Before we create the timeline slicer, we have to first build a simple pivot table. So, click on the “Insert” ribbon and click on the “Pivot Table” button on the top left-hand corner:
Make sure that the whole data is selected inside the range and click on “OK”:
Doing that’s gonna create a new worksheet where you can build the pivot table.
Building the Pivot Table
Inside the new worksheet, I’m going to drag the “age_certification” column into the “Rows” box and the “runtime” column into the “Values” box:
But I don’t want to get the SUM of the runtime values. I want to get the AVERAGE, so I’m going to click on the dropdown arrow next to “Runtime” and click on “Value Field Settings”. From there, I’ll click on “Average”:
Here’s what my pivot table looks like:
After you’ve finished building your pivot table, you can move on to the main part, which is creating the timeline slicer.
Creating the Timeline Slicer
Make sure you’re selected inside the pivot table and go to the “PivotTable Analyze” ribbon and click on the button that says “Insert Timeline”:
This is going to open a new pop-up window, and it shows the “release_year” column, so I’ll check that box:
If I didn’t format the “release_year” column as a date before, then this wouldn’t have worked because I can only create a timeline slicer if one of the columns are formatted as a date in the dataset.
Anyways, here’s what the timeline slicer should look like:
But there’s only one year in this whole dataset, so I’m going to change the timeline slicer to display the quarters instead of the years. So, I’ll click on the dropdown arrow next to “YEARS” and change it to “QUARTERS”:
That’s going to show you 4 quarters of the year, and you can click on each different quarter, which will change the values on the pivot table based on the quarter you select.
Video on Creating a Timeline Slicer
If you want to see a more interactive version of this blog post, check out a video that I made:
It’s a 5-minute video that will walk you through all the steps of creating a timeline slicer.
I hope to see you over there, and thank you so much for reading!
Originally published at https://dylans0ng.github.io on December 9, 2023.
