Using Expand/Collapse on PivotTables and PivotCharts for rapid display changes

- [Instructor] One of the most appealing aspects of working with pivot tables is the speed with which you can change the layout, the appearance of a pivot table. I've got some data here. It's about 900 rows. I want to create a pivot table on this sheet. I'll simply go to the Insert tab in the ribbon and Pivot Table. Excel figures out the extent of the data. It goes down to row 910 in columns A through G. I'll simply click OK. We'll be on a new sheet. And in the pivot table, based on the Pivot Table Field List we see to the right, I'd like to track Items Sold.

So I'll click the Items box. That's a numeric field that will automatically appear in the values they're in. I want to track data, initially by date. That will automatically appear in the Row Labels area. And let's break this up by region, which I want as a Columns Label entry. So a pretty small pivot table. When you do work with dates in pivot tables, you've got lots of capabilities though. And one way to start working with this would be to click the plus right here to the left of the 2016 or to double-click 2016.

Either way works the same. It expands the list. Now notice how 2016 is expanded but 2017 isn't. Expand that too. This might take some time as we click all these different pluses because what's behind these? What kind of detail do we have? I think you can quickly see what's going on here month by month. Now, it would be better and easier if we could expand these all at once, and we can. I'm going to click on 2016 and go to the Analyze tab in the ribbon, and there's a plus right there, Expand Field, expand all items of the active field.

At first we don't see anything. If we click it again, it's expanding the other months. This probably will appear a bit better and more vividly if we change by way of the Design tab the report layout here. Now the choice I'm going to make will make this report wider. In some cases, you wouldn't want to make this choice, but Tabular Form will put those different hierarchical entries, namely Year and Quarter and Month in separate columns, and that might bring out the hierarchical nature a bit better.

I'll zoom back a little bit using control and the mouse wheel. Now let's show different ways we can expand this. We also can do this not only within the pivot table but also by way of a pivot chart. Let's first focus on this idea. We might want to collapse this by clicking 2016 and then going to the Analyze tab as I did before, I'll click the minus here. Watch the display, now we're back to this. What happens when I click the plus? We're seeing the entire set of data expanding into years and quarters, and we also see these months.

One minor fix I'm going to make here, you could understand why. We see Years, and we see the two years below it. We see Quarters, and we see the four quarters. But then the word Date here is a bit out of sync. Why not put in the word Months? Now the field name originally was Date, but we can change it here. It doesn't change the source data but it's going to change how we refer to this data in the pivot table. So we'll type Months. Now let's create a pivot chart too, and we can create it either when the data's expanded or collapsed because we want a little bit more room on the screen here.

Let's do collapse this by choosing that icon we see here, the Collapse Field icon on the Analyze tab, do it that way, click it again. And also, those top two rows are actually empty. Let's drag across rows one and two, right-click, and we'll delete those. Another feature we can use by the way when we're trying to achieve more space on the screen is simply to hide those ribbon icons. You can do that with control + F1, say for presentation purposes. They don't need to be there. Or bring them back. So for the moment, they're there.

To create a pivot chart, all we need to do is click within the data and press alt + F1. That's going to be a little bit faster than going to the Analyze tab and clicking Pivot Chart because Pivot Chart will ask us which kind of chart type we want. And what do we see in the corner of the pivot chart? Pluses and minuses. Now, before proceeding, I do recommend that when you use a pivot chart, convert it into a stacked column. What we're seeing at the present isn't so bad but if we start to show more detail, and I'll do it with the plus button here on the chart, see what's happening there? A bit more, a bit more.

That's pretty crowded, and not really a very good chart. A stacked column chart is going to be more workable than this one is. So on the Design tab, off to the right, Change Chart Type. Let's use a stacked column, then there's that option. And although not necessary, I'm going to right-click on one of the columns here and choose Format Data Series. That brings up another dialog box to the right. And I'm going to reduce the gap width. You might want to experiment with this. I'm going to change it to 20. It's going to make those columns a lot wider.

And maybe move this Pivot Chart Fields dialog box around too and by the way, notice when you do have a pivot chart and the chart is selected, the dialog box to the right has the title Pivot Chart Fields. If I click on the pivot table, now it's Pivot Table Fields. And when we do make that change too, I'll go back to the chart, before I do, notice down below, we see rows and columns, but if I click on the chart, now we see Access and Legend. So if you are about to move fields here, still retain the same idea of the location here even though the wording is different.

So we might be hard-pressed for space here. So I'll again press control + F1 to get rid of those ribbon icons for the moment. Move the pivot chart field so it's a bit off to the right. Move the chart around. And again, there will be times there's just not enough room to show everything. Zooming back, holding down control and the mouse wheel possibly, let's again explore some of these expansion options. Again, if we only wanted to see quarters one and two, we could collapse quarter three, double-click it. That collapses both quarter threes, collapse quarter four.

You see what's happening? The chart is reacting at all times, not at the moment looking so great but nevertheless, that's okay. What if we clicked the plus on the chart? Click it again. It can't go any farther, minus. So in varying degrees, when you are showing data this hierarchical in nature, and this is not restricted to dates, by the way, but we've got three fields in the Rows area of this pivot table, and so, using the pluses that we see, going back to the Analyze tab, we could be using the pluses that we see there, the minuses that we see there, or possibly the pluses that we see in the chart, we've got quick, easy ability here to expand and collapse.

And again, another way, by right-clicking, for example the 2016, we see Expand/Collapse here, let's collapse the entire field. We go back to that display. Or at a later time, come back, right-click, Expand/Collapse > Expand Entire Field. So different approaches there. And as we use the pluses and minuses either on the chart or on the Analyze tab, we get ultimately the same capability. So these are powerful tools when you work with pivot tables, the idea to quickly change the display to show more data or less data as needed.