Summarize data from different worksheets with a PivotTable

- [Instructor] When you've got similar data on different worksheets in a workbook, sometimes you might want to consolidate the information, and there is a technique available using a PivotTable. If you're familiar with the capabilities of PivotTable, you know what a great tool it is. Let's take a look on this worksheet, first of all, at Oregon, and what we're seeing here is information about sales by way of different outlets of different items. Now, the next sheet over Arizona is similar, but certainly not identical in the layout. We see some of the same items, but some different ones as well, not only for the items being sold, but the sales outlets as well, and over in Nevada and California, and to bring this out more clearly, I've highlighted the relevant sales in yellow.

So, how could we pull this data together? One approach could be to copy, paste, and put all this information on the same sheet, possibly do some sorting, some rearranging, do it that way, but let's say these lists in each of these states, they're going to grow somewhat, both horizontally and vertically, and we've got other states involved, eventually, they're going to be in the mix too. We want to somehow be able to consolidate these quickly. Now, by way of a PivotTable, we can do this, but oddly enough, we have to resurrect, seemingly, a feature out of the past.

If you worked with PivotTables back in Excel 2003 or earlier, you may be familiar with the term, PivotTable Wizard. We can get to that feature because it's got a consolidation option that we'd want to use. So, going to the existing Quick Access Toolbar at the top of your screen. Now your Quick Access Toolbar might look different than mine. You might have more or fewer buttons than what you're seeing at the top of my screen, but if you right click anywhere up there, you do have an option, Customize Quick Access Toolbar, and this activates the Excel Options dialogue box.

Choose commands from Commands Not in the Ribbon and in this list here, scrolling down near the letter P, PivotTable and PivotChart Wizard. Add it to the Quick Access Toolbar, click OK. Now, with that button present and ideally, you start on an empty sheet, but we could start anywhere. We're on an empty sheet, let's activate this button. The PivotTable and PivotChart Wizard, step one of three has an option, Multiple consolidation ranges.

Choose that option, Next. For step two, simply go with the default setting, Next, and now we're going to be gathering data from those four sheets. So go to the Oregon sheet, highlight just the yellow cells, not the totals on the right side or on the bottom. Add that to the range max here. Go to Arizona, highlight just the yellow cells. It's a bigger range here, Add, and then, Nevada, same thing, and of course, California ...

And add. Next, where are we going to place this? How about in the upper left corner of the current worksheet, so just click up there and Finish. Now we've got some answers, but it's not exactly obvious what they always mean. First order of business might be, let's get rid of these terms Row Labels and Column Labels. We can do that from the contextual tabs that appear when a PivotTable is active. The Design tab, Report Layout, off to the left. Choose outliner tabular.

The terms that pop up here though, are not exactly helpful. Now, Row, here is actually for these entries here. For one of a better term, I'm going to use the word, Outlet, here. Zoom in so I can see it better. I'm simply going to type over this. The method in which these items are being sold. The items themselves, I'll just use the word, Items, right here. So that's reasonably clear, I believe. Meanwhile, we do have the PivotTable Fields list off to the right. The term, Page1, doesn't make a lot of sense.

So, let's for a moment, deal with that by-- In the PivotTable list to the right, let's move, Outlet, out of the Rows area, put it into Filters temporarily, and bring in Page1. Now what does this relate to? Would any of these numbers be familiar? As we move back and forth across the sheets, here, we've dealt with the items in the outlets, what's missing? The actual sheet names. Now, we may have to guess here at first, but California's got a 40,000 total. Let's go over here, there's a 40,000 total.

So, Item2, this instead of being Item2, should be California. We'll make that change. Arizona, I believe was the second highest. They were 20,000. So, that entry out here, make that be Arizona. So, there will be some adjustments you need to make and I believe Oregon was bigger than Nevada, so it's likely to be this one. Yeah, a little over 15,000. So, we'll make that adjustment to Oregon here, and then above at Nevada.

Now, those of you familiar with PivotTables know that when you make changes to these other sheets, you might think, at least initially, that the PivotTable updates automatically, but it won't. So remember, if you do make changes to the sheets here, and we've got a PivotTable consolidate that information, the PivotTable will not reflect those changes until you've refreshed the PivotTable. That's with Alt + F5, or in the Analyze tab, Refresh. So, keep in mind that idea, if you're not too familiar with PivotTables.

Nevertheless, we've got something pretty valuable here and if you are familiar with the dynamics of PivotTables, you know how easy it is to move these fields back and forth, get a sense of the data, and as we, also, in the PivotTables, zoom back, get some sense of how the items are being sold and we knew earlier based on moving back and forth, how some items are not sold in every state or some outlets are not active in every state. We see these various interrelationships popping up in different ways. From time to time, you might see subtotals.

Keep in mind, when the Design tab, you might want to get rid of them, or possibly later, bring them back, but we've got all kinds of capability here too, and remember you can easily create a PivotChart. Simply click within the data, press Alt + F1, and if you do, I do strongly recommend, go to the Design tab and change the Chart Type to be a stacked column. Those tend to work better in PivotTables, simpler, easy-to-work-with charts, but we've got a lot of power and capability here by consolidating data by way of a PivotTable, and keep in mind, if this list were to grow and shrink, you want to put the new states between Oregon and California, refresh the PivotTable, possibly you'll have to make some label changes, but you will be able to build on this.

Now, if these worksheets were in different workbooks, and those workbooks were open, you could go through the same process of consolidating information the way we suggested here, but in that case, as you were consolidating the information, you'd be going to different workbooks and copying and pasting the data. That's always a little bit risky when you're consolidating from multiple workbooks, but it's the same general idea as what we're doing here. So in the example here, we've got a PivotTable that consolidates information based on information in four other related worksheets within the same workbook.