One way to display figures in an Excel table according to certain time periods is by using filter buttons. However, a better way to do this is to use a timeline, a dynamic filter option that allows you to quickly display data from individual days, months, quarters, or years.

Step 1: Turn Your Data Into a PivotTable

The first step is to turn your raw Excel data into a PivotTable. To do this, select any cell in your formatted or unformatted table of data, and click “PivotTable” on the Insert tab on the ribbon.

Then, in the dialog box, check that the whole range of data is selected, including the headings, and choose either “New Worksheet” or “Existing Worksheet,” depending on where you want your data and timeline to be. I prefer to create PivotTables on new worksheets, so that I have a blank canvas to play around with its tools and features. Once you’ve made your selection, click “OK.”

Now, in the PivotTable Fields pane, check the fields that you want your PivotTable to display. In my case, I want to see both the month and sales totals, so I’ll check both fields.

Since Excel recognizes that my data contains months and values, it automatically places the Months field in the Rows box and the Sum Of Sales field in the Values box.

Notice, also, that Excel has added years and quarters to the PivotTable’s Rows box in the screenshot above. This means that my PivotTable has been condensed into the largest time unit (in this case, years), and I can click the “+” and “-” symbols to expand and contract the PivotTable to display and hide the quarterly and monthly figures.

However, since I want my PivotTable to always display the monthly data in full, I’ll click the down arrow next to each of the other fields in the PivotTable Fields pane, and click “Remove Field,” leaving only the original Month field in the Rows box. Removing these fields helps the timeline to work more effectively, and I can re-add them directly through my timeline when it’s ready to go.

Now, my PivotTable displays each month and the respective sales totals.

Step 2: Insert Your Timeline Filter

The next step is to add a timeline that is linked to this data. With any cell in the PivotTable selected, open the “Insert” tab on the ribbon, and click “Timeline.”

In the dialog box that appears, check “Month” (or whichever time period you have in your table), and click “OK.”

Now, reposition and resize the timeline on your spreadsheet so that it sits tidily near your PivotTable. In my case, I’ve inserted some extra rows above my table, and moved the timeline to the top of my worksheet.

You can now use the timeline to display selected data in your PivotTable. For example, if I click “JUL” under the 2024 label, only the data from July 2024 will show in the table.

To display more than one month at a time, click and drag the handles on the left and right edges of the colored bar in the timeline. Alternatively, click one month, hold Shift, and click another month to display those two months and all months in between. Your PivotTable will also show a grand total row to make data analysis even easier.

Another way to use the timeline is to select different time units, like quarters or years. Even though you removed these in the PivotTable Fields pane earlier, they still appear as filters in your timeline.

Simply click the down arrow in the top-right corner of your timeline box, and, for example, click “Quarters.” In my case, I want my table to display the data for each month from the first three quarters of 2023, so I’ve selected these on my timeline.

Step 3: Format Your Timeline Filter

As well as resizing and repositioning your timeline, you can also format it so it looks the part. When you select your timeline, Excel adds the Timeline tab to the ribbon. There, you can choose which labels to show by checking and unchecking the options in the Show group, or select a different design in the Timeline Styles group.

Although there’s no way to reformat the preset timeline styles, you can create a copy of a style and reformat that instead. To do this, right-click your chosen style, and click “Duplicate.”

Then, in the Modify Timeline Style dialog box, rename your new style in the “Name” field, and click “Format.”

Now, run through the “Font,” “Border,” and “Fill” tabs to apply your own design to your timeline, and click “OK” twice when you’re done to close both dialog boxes and save your new style.

Finally, with your timeline selected, click the new timeline style you just created to apply its formatting.

Go One Step Further: Add a PivotChart

The final step to making the most of your timeline is to add a PivotChart, which will update according to the dates you select in your timeline. Select any cell in your PivotTable, and in the Insert tab on the ribbon, click “PivotChart.”

Now, in the Insert Chart dialog box, select a chart type in the left-hand menu and a chart in the right-hand selector area. In my case, I’ve selected a simple clustered column chart. Then, click “OK.”

Reposition and resize your chart, double-click the chart title to give it a new name, and click the “+” button to choose which labels to display.

Now, select a time period on your timeline, and see both the PivotTable and PivotChart display the relevant data.

Another way to quickly filter data in an Excel table is to add an Excel data slicer, a series of buttons representing different categories or values within your data. The added benefit of using slicers is that they don’t require you to convert your data into a PivotTable—they work just as effectively with regular Excel tables.