Nowadays using Excel is quite common in day-to-day life. Specially, we need to work on large datasets in corporate life. But there is a bit of a hassle when you need to filter data according to a particular field. There you can use the slicer to minimize the hassle. Sometimes we need to filter data according to date or sum up the data according to a specific range of date. Here we can use a timeline slicer with a date range. In this article, we will learn how to create a timeline slicer with a date range in Excel.
The above overview video shows the process of creating a timeline slicer. You will know details about this topic once you go through the whole article.
What Is Slicer in Excel?
A slicer is an option that helps to filter the data according to the selected field. While working on a large dataset, managing all the data properly is a huge task. But if you use the slicer in Excel then this option helps to filter the data and helps to simplify the working procedure.
What Is Timeline in Excel?
A timeline is also a slicer but a timeline slicer is used when we need to filter data according to date. You can categorize every single piece of data using this option which simplifies the analysis.
Excel Timeline Slicer with Date Range: Create with Easy Steps
The below dataset contains the Product Name, Date of arrival of the product, Sales, and Profit. Here different slots of products arrive on different days. In case, you need to sort the products datewise to calculate the profit and sales from the dataset you can use the timeline slicer option from the pivot table. This option shows the required output of any dataset according to the date.
Follow the steps below to create a timeline slicer with a date range in Excel.
Step 1: Insert a Pivot Table
In this step, we will insert a Pivot Table in Excel.
- Initially, select the data range B4:E13 to build the pivot table
- Then select Insert >> PivotTable from the Toolbox as below.
- After that, Pivot Table from Table or Range dialog box will appear as below.
- Select New Worksheet so that, the pivot table appears in another sheet, and lastly, select OK to complete this process.
Step 2: Add Fields to the Pivot Table
Now, we will add Fields to the Pivot Table we inserted in the previous step.
- Once the OK option is clicked then the structure of the pivot table will appear in another sheet.
- Select the required fields from the PivotTable Fields and the pivot table is complete.
Step 3: Add Timeline Slicer for Date Range
Here, we will add Timeline Slicer for the date range.
- Select Pivot Table Analyze >> Filter >> Insert Timeline from the ribbon.
- Consequently, the Timeline dialog box will appear.
- Afterward, select Date of arrival from the dialog box and click OK.
Step 4: Final Output
- Finally, the Timeline slicer will appear like the picture below.
- Now, select the area to get the required result.
- Selected Area: This area is the required area of data. Once you select a particular area then the data of that area is shown as output.
- Unselected Area: This area is the muted area. If you don’t need data from a particular area then unselect the data.
- Filter: This feature filters the data from the Date of Arrival option.
- Date Grouping: This feature represents the time period of the data. If the data represents Year then it shows year in date grouping. If the data represents Month then this feature represents month.
How to Remove Timeline Slicer in Excel
Adding a timeline slicer is helpful if you want data based on the dates. But sometimes you need to remove it for different reasons. Here, we will learn how to remove the timeline slicer in Excel using the below process.
- First, select the Timeline slicer and right-click the mouse to open the menu.
- Then, select Remove Timeline from the menu bar and remove the timeline slicer if it is unnecessary.
How to Add a Slicer to a Pivot Table in Excel
We can add a slicer to a pivot table similarly to adding a timeline slicer. When you need to sort or differentiate the data regarding a particular option then you should use the slicer to complete those processes easily. Follow the below steps to add a slicer to a pivot table in Excel.
- In the beginning, select Pivot Table Analyze >> Filter >> Insert Slicer from the toolbox.
- Now, the Insert Slicer dialog box will pop up as before, and select the required field (For instance, select Product).
- Once you select Product click on OK.
- In the end, the slicer will pop up and choose according to the requirement.
Things to Remember
- A timeline slicer is used when we need to filter the data according to the date. Otherwise, the timeline slicer will not work.
- You can select multiple fields at a time while using a timeline slicer.
Download Practice Workbook
You can find the practice workbook here.
In this article, we covered how to create a timeline slicer with a date range in Excel. The timeline slicer helps to filter the data according to the date. Hopefully, you can execute the examples shown in this article. Please let us know in the comment section if there is any query or suggestions.