Timelines

Quick reference

• 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.