MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Timeline to Title


November 08, 2017 - by Bill Jelen

Pivot Timeline to Title

Timeline slicers are an Excel feature for filtering a pivot table. But what if you would like to show the start date and end date from the timeline in a report title? This article discusses ways to do that.

Watch Video

  • Nick from Utah wants to show the dates chosen from a pivot table Timeline to a readable title.
  • Copy the pivot table that is already tied to the timeline and paste it out of view
  • Change that pivot table to have the date field twice in the Values area
  • Double-click each heading to get to Field Settings. Choose Min in the first cell and Max in the second cell.
  • Use the text function to concatenate together a useful heading

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2170 convert a PivotTable
  • timeline to a printable title hey
  • welcome back to the MrExcel net cast
  • I'm Bill Jelen and today's question from
  • Nick in Utah I ran into Nick at the
  • Excel Appaloosa down in Dallas back in
  • September and Nick hasn't PivotTable
  • with the time line now I love pivot
  • tables I create pivot tables all the
  • time but I hate time lines I hardly ever
  • use time line 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 z2 a and I
  • only want to see the top five customers
  • so value filters top ten I'll choose top
  • five I love the top five this is great
  • now I want to get rid of the grand
  • totals like right click on that heading
  • and say remove grand total like that
  • alright now I have a report of just the
  • top five customers but we're gonna
  • change this up and add a time line 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 want to
  • be based on date that's the only date
  • field in my data set so we get our 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 I don't like how they
  • work I'd rather use slicers but here
  • where we have you know a timeline but
  • Nick when he prints his report he
  • doesn't 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 gonna do we're gonna 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
  • gonna see it and paste ctrl V like that
  • and we're gonna change this version the
  • pivot table up see this this version of
  • the pivot table is reacting to that
  • timeline alright so we're gonna take
  • this field and I'm gonna ask for two
  • things I don't want customer anymore
  • what I do want and I want date but I'm
  • gonna put the date in the values area
  • and I want to put the date in the values
  • area twice and so we get count of date
  • and count of date two
  • this first one I'm gonna 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 changed
  • to a Max and we'll call this through
  • from and through like that like okay 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
  • why why why why something like that and
  • then you can imagine this would be
  • simple to build a little title here sold
  • can cat need some text together so top 5
  • customers from ampersand and I'm not
  • gonna click on the cell I'm not gonna
  • click on P 4 instead I'm just gonna type
  • it because if I clicked on it I would
  • get the get pivot data which is really
  • annoying so we want to make sure to just
  • type this formula and we'll say mmm why
  • why why I like that and then through
  • ampersand text of q4 and and I'm like
  • that
  • all right it's a top five customers from
  • April 2017 through April 2017 now that's
  • gonna look great when we choose two
  • months like April through June and
  • that's gonna 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 five
  • customers from the first date otherwise
  • do the top five customers from that
  • through that all right 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 pasted 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 addition 2017
  • edition with 617 excel mysteries all
  • clicked a tie in the top right hand
  • corner for more information all right
  • 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 you
  • 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
  • and Excel is what I want thank you for
  • stopping by we'll see you next time for
  • another net cast from MrExcel

Download File

Download the sample file here: Podcast2170.xlsm

Title Photo: Jacob Miller / Unsplash


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.