Chart Hierarchy in X-Axis

Learn Excel from MrExcel Podcast, Episode 2176: Pivot Chart Hierarchy.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was doing a seminar and Tara... I had a cool chart that had two fields along the x-axis-- along the bottom. So it was a stacked column chart with different Regions, but then along the axis, x-axis, you have both a Year and a Date. And these, at least for me, are easiest to create with a Pivot Chart. Alright?

So, we choose our data here, so Insert, Pivot Table, let it go to a new worksheet like that. And then the two fields that you want to have along the x-axis, we're going to take those to the rows area. And that behavior that you just saw right there, where they took the daily days and rolled them up, is new in Excel 2016. And I press Ctrl+Z to undo that. Alright.

So, here, in most versions of Excel, when you drag the Date field over there, you'll just have daily dates. And then we're going to use Group Field to group that up to Months and Years, like that. Alright. So now, we have two fields in our rows area, and then let's use Product in the columns, and then Revenue or Profit in the heart of the Pivot Table, alright? So now from this Pivot Table, we create a Pivot Chart. I'll choose the Column chart, and a Stacked Column chart-- like this-- click OK. And now what we have is, along the bottom we have both Years and Months in, kind of, this format. And what's cool is the Pivot Chart will react to slicers as well. So if we would insert a slicer here for, maybe, Sector, click OK-- alright, so now, we can, very quickly, if we would choose Healthcare we see the numbers for Healthcare or Financial or Communications, right-- so the chart will update based on what's in the Slicer. Alright?

And I've seen these a lot with Dates, and Tara actually had two different charts: One with Dates and Months, and then another one with, it looks like, two or three fields down there-- both, you know, like, two different things and they weren't necessarily Date fields. So let's... We have Products already there, let's take Sector and Region-- so we'll put Region first, so we have East, Central, and West-- and then within Region, we'll show the data by Sector, like that. And when we choose All Sectors, we get this report with East, and then all the things within Eastern, right? And, so, depending on the order year controls, what's going to be the outer field. If I would reverse this, then within each Sector we're going to have the various Regions. Alright? So, it's just a little backwards. And also, if you have the chart selected, what used to be called the rows area is now axis, and what used to be called the columns area is now legend or series. A bit confusing, but in general, with a Pivot Table creating a Pivot Chart, this is an easy way to go.

Alright, well, my book, Power Excel with MrExcel, the 2017 Edition, has a lot of topics on Pivot Tables. Click that "I" on the right-hand corner to check that out.

Quick recap today: Tara trying to create a chart with two fields along the x-axis. So I create a Pivot Table with two fields in the rows area and then make it into a Pivot Chart.

Well, I want to thank Tara for being in my seminar and for that question, and I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.