Locked lesson.
About this lesson
Timelines are date specific filters for PivotTables.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Timelines.xlsx210.6 KB Timelines - Completed.xlsx
211.8 KB
Quick reference
Timelines
A date-specific filter for PivotTables.
When to use
When you want users to easily be able to filter the PivotTable by different date periods (year, month, quarter, day).
Instructions
Creating timelines
- Select a cell in your PivotTable
- Go to PivotTable Analyze > Insert Timeline
- Select the field to use and click OK
- Change the timeline’s name/caption in the Timeline Caption field at the top left of the Timelines tab
- Other formatting options also available in the Timelines tab
Aligning timelines
- Hold down ALT to snap to grid, making it easier to line it up with other elements
- This also works for slicers
- You can resize the timeline’s length
- You cannot resize the height, it just cuts off the bottom and doesn’t scale
Applying the filters
- Change the date period by clicking the arrow in the top right corner of the Timeline
- The default period is Months
- You can only filer by contiguous dates
Key points to remember
Only fields that are set up as dates can be used to create a timeline
- Only fields that can be filtered by date appear as an option
- 00:04 We've got one more filtering tool to look at here.
- 00:07 And this one came in Excel 2013 and it's called a timeline.
- 00:11 And it's similar to a slicer, but it's actually specific to dates.
- 00:14 To get to it, we'll click inside our pivot table.
- 00:17 We'll go to Pivot table tools, Analyze, and we'll choose Insert timeline.
- 00:21 You'll notice that even though we have a whole bunch of fields inside
- 00:25 our pivot table list, we only get to choose from POSChitDate in this case.
- 00:29 So we're going to check the box and say OK.
- 00:31 The question is, why only that one versus all the other fields?
- 00:35 And the reason is because in order to actually use this for a timeline,
- 00:39 you must have a field that is filtered as a date.
- 00:41 And this is the only field in the dataset that is actually filtered as a date.
- 00:45 Now I'm going to grab this guy here, I'm going to hold down my ALT key,
- 00:48 because this actually allows me to snap to grid, and it works for slicers as well.
- 00:52 And I'm just going to hold down my ALT key and drag this a little bit wider, so
- 00:55 we can get a little bit more room on this timeline.
- 00:58 And this is what a timeline actually looks like.
- 01:00 Now, I've made it wide enough so I can see all the individual months.
- 01:04 But what is this all about?
- 01:05 A timeline is a very specific date filter.
- 01:08 It comes in set to months by default.
- 01:10 And if I go in and click on January, it filters to January.
- 01:13 Now the thing is, is that in this dataset, I only have January records.
- 01:17 So it's really not going to filter down to anything.
- 01:19 So what I need to do is change from months to days.
- 01:24 And now I can say, let's see January 1st, or
- 01:28 maybe I want to see a few days in January.
- 01:31 So I'll just grab the right side of the elephant ear here and pull a little wider.
- 01:34 And it now brings me in part of Week 1 and Week 2.
- 01:37 And I'll clear the filter on this whole thing.
- 01:39 Now you'll notice that my timeline, as the pivot table was resizing its columns,
- 01:42 was moving.
- 01:43 So like splicers, we're going to go to right-click, Size and Properties, and
- 01:47 we're going to choose to Don't move or size with cells.
- 01:50 And again, if we didn't want to print it, we would uncheck the print object box.
- 01:55 At this point, you'll see that now when we click January 1st,
- 01:57 things don't move around.
- 01:59 We can go into specific days, we can actually drag wider, wider or even shorter,
- 02:04 and it allows it to dial in to just the region of dates that we want.
- 02:09 We can drill this back up, we can go to Months.
- 02:12 And notice that it'll allow us, or show us that we've got a partial month selected.
- 02:16 The only place you can select a partial month is by drilling down into Days and
- 02:20 then coming back up.
- 02:21 We can also go and take a look at quarters.
- 02:24 There's no work I need to do inside my pivot table to get this stuff to show up,
- 02:27 it works beautifully.
- 02:28 And if I go to years, it'll show me every year in my dataset.
- 02:32 So I click on that, it'll now actually grow it out quite nicely.
- 02:35 I of course, with this one, would probably want to be drilling down to the days
- 02:40 level, so I'll set it back to this particular area.
- 02:42 Timelines are amazing.
- 02:44 One challenge that we have with them, I'm not really a big fan of the date.
- 02:47 So one of the things you'll notice, and this happens with both slicers and
- 02:50 timelines, when you actually select their timeline options or slicer options,
- 02:54 the very left-hand side you'll notice the name.
- 02:57 So I can actually rename this guy here to actually call it something else.
- 03:01 Please select your date range here.
- 03:06 And there we go,
- 03:07 we get a nice new caption at the top of it that actually looks a lot better.
- 03:11 This is my favorite way to slice up dates.
- 03:13 I far prefer them to slicers, because they actually allow me to drill into contiguous
- 03:17 data ranges, so I never end up with any gaps.
- 03:19 They also have the ability to set up individual styles,
- 03:22 just like you would with a pivot table style.
- 03:24 So you can actually make the slicer look the way you want.
- 03:27 The only challenge with them, it's very difficult to shorten them,
- 03:30 they don't actually scale anymore.
- 03:31 So you actually lose parts of the timeline if you try and do this.
- 03:35 That doesn't work.
- 03:36 But outside of that, they're magnificent tool for
- 03:39 being able to drill into specific date ranges.
Lesson notes are only available for subscribers.