by Allen Wyatt
(last updated February 5, 2022)
When Terry adds slicers and timelines to a PivotTable, he finds them incredibly helpful. He wonders if there is a way to add them to other parts of Excel, as well. There are times he doesn't want to go through creating a PivotTable, but would still like to get the benefits of slicers and timelines.
Slicers were introduced in Excel 2010 and timelines in Excel 2013, both for use with PivotTables. (They will also work just fine with PivotCharts.) In Excel 2013 slicers were "expanded" so that they could be used with formatted tables in a worksheet, as well as with PivotTables. Both tools allow you to easily filter data to home in on exactly what you want to find in your data.
I won't go into how to add slicers or timelines to PivotTables, as Terry apparently knows how to do that. To add a slicer to a formatted table in your worksheet, select a cell in the table and make sure the Table Design tab of the ribbon is displayed. (This tab is available only if you select a cell in a formatted table.) There you'll find an Insert Slicer tool that can be used to add what you want.
Timelines can only be used in PivotTables; they are not available elsewhere in Excel. You can attempt a workaround, though, by adding a date column to your formatted table data and then adding a slicer based on that data column.
The nature of both slicers and timelines requires a formal data structure and so it makes sense that they work with formatted tables, PivotTables, and PivotCharts. For free-form data, there would be no way for the slicer or timeline to know what to include, though some people have tried to work around this limitation:
The approach is interesting, but it seems (to me) much easier to just format your data as table and then use the tools you want.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12837) applies to Microsoft Excel 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
The filtering feature in Excel allows you to quickly copy unique information from one data list to another. If you want ...Discover More
Using the fill handle is a great way to quickly fill a range of cells with values. Sometimes, however, the way to fill ...Discover More
Some types of data may have certain fields that contain partially identical information. In such cases you may want to ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.