Pivot Timeline to Title

Learn Excel from MrExcel Podcast, Episode 2170: Convert a Pivot Table Timeline to a Printable Title.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question from Nick in Utah. I ran into Nick at the Excel Appaloosa down in Dallas back in September, and Nick has a Pivot Table with the time line. Now, I love Pivot Tables; I create Pivot Tables all the time. But I hate timelines; I hardly ever use timelines.

So, let's just create a quick little report here showing Customer and Revenue, and we want to Sort this data High-Low-- so, Data, Z to A-- and I only want to see the top 5 Customers-- so, Value Filters, Top 10, I'll choose Top 5, I love the Top 5, this is great. Now, I want to get rid of the Grand Totals, so I right-click on that heading and say Remove Grand Total, like that. Alright.

Now, I have a report of just the Top 5 Customers, but we're going to change this up and add a timeline where we can choose a particular Month or something like that. So we come here to the Analyze tab, and Insert slicer, Insert Timeline-- now, the Timeline could only be based on Date, that's the only Date field in my data set. So we get our nice little timeline here, and you can see that we can, you know, choose a particular month or a period of months, like July through September, or something like that. We can even change the timeline to be quarters. And I'm going through this in more detail because I know that I've never covered timelines-- just because I don't like how they work. I'd rather use Slicers, but here we are, we have, you know, a timeline.

But Nick, when he prints his report, he doesn't want to have to print the timeline; he'd like a nice little heading up there showing who's involved. Alright. So here's what we're going to do: We're going to take our Pivot Table that is tied to this timeline, and copy it and just go way out here to the right where no one's ever going to see it, and paste-- Ctrl+V, like that-- and we're going to change this version the Pivot Table up. See, this version of the Pivot Table is reacting to that timeline, alright? So, we're going to take this field and I'm going to ask for 2 things: I don't want Customer anymore; what I do want, is I want Date. And I'm going to put the Date in the Values area, and I want to put the Date in the Values area twice-- so we get Count of Date and Count of Date 2. And I'm going to go to this first one, I'm going to change the calculation to show a Min, and this would be called Earliest-- or From, let's call it “From”. And then I go to the second one, and double-click the heading and I change it to a Max, and we'll call this

“Through”, like that. Click OK.

Now we want to apply a little bit of formatting here and we have to choose: Do we want to show the actual Date-- so a short date would show from April 3rd through April 28th because we only invoice on weekdays, and so the 1st and 2nd of April must have been weekends, alright-- or do we want to convert that to show a month? So, More Number Formats, go to Custom, and then, MMM YYYY, something like that. And then, you can imagine, this would be simple to build a little Title here, so we'll Concactenate some text together, so-- ="Top five customers from "& --and I'm not going to click on the cell, I'm not going to click on P4, instead I'm just going to type it. Because if I clicked on it, I would get the Pivot data which is really annoying. So we want to make sure to just type this formula, and we'll say, MMM YYYY, like that, and then through ampersand, TEXT of Q4, and MMM YYYY, like that. (="Top five customers from "&TEXT(P4,"MMM YYYY")&" through "&TEXT(Q4,"MMM YYYY")). Alright. So, top five Customers from April 2017 through April 2017.

Now that's going to look great when we choose two months like April through June, and that's going to be beautiful. It looks really weird though, when we choose just one month. So what we could do here, is do a kind of crazy formula. If the text of the from date is equal to the text of the through date, then just do Top 5 Customers from the first date; otherwise, do the Top 5 Customers from that, through that. Alright, so, if you want to go to the extra trouble, I guess it'll look better, and then, of course, just cut-- Ctrl+X-- and paste up here below our Table-- Ctrl+V-- and it should all be working. The titles will update appropriately.

Now, more Pivot Table tricks are in this book, Power Excel with MrExcel, the newest Edition, 2017 Edition, with 617 Excel mysteries. Click that "I" in the top right-hand corner for more information.

Alright, wrap-up of this Episode: Nick from Utah wants to show the dates chosen from a Pivot Table timeline and make it be a readable Title. Alright. So, take that first Pivot Table, it's tied to the time line, copy it and paste it out of view; change that second Pivot Table to just have the Date field twice, and the values area; change the first Date to a Min, change the second date to a Max; the fast way to get to Field Settings, double click the heading above the column, and then use the TEXT function to concatenate together a useful heading.

I want to thank Nick for that question in Excel Appaloosa, and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.