Using source data from a table to make your PivotTable more dynamic

- When you're working with a pivot table and the source data is dynamic, it's advantageous to convert the source data to a table. This particular data here is not a table. I'm going to create a pivot table from this and then contrast that action with data that has been converted into a table. So this data, not a table. We're gonna create a pivot table, Insert, Pivot Table. From the data here, we're going to simply put this on the Existing Worksheet, click on the Location panel here and we'll put it in cell G1.

Click OK. And from the Pivot Table Field list here, I'm going to choose Department, Status as a Column labels area. And we want to simply tabulate the number of employees in each of these categories, like this. We can move the Pivot Table Field list aside. And before I go any further, too, instead of seeing Row Labels and Column Labels here, by changing, by way of the Design tab in the ribbon the Report Layout to be either Outline or Tabular, we'll see the actual field names there. So, we've got a list here.

And just as a matter of interest here, if you're not too familiar with pivot tables, one of the early things you learn about them, and it's kind of a surprise, is that when you update the source data, the pivot table does not update until you make it happen by way of a phrase called refresh the pivot table. So I'm gonna make a change in the source data here so that we can see what's going on. Two of the people in the ADC group here, two of the Contract people here, have been offered full-time jobs. So I'm gonna change that Status to Full Time. I'll simply copy these two entries here, Full Time, with the control key put them right here.

Now, to the right in the pivot table you'll see in row 3 of the pivot table there, where it says ADC, actually, row 3 of the worksheet, Grand Total of 5 and no Contract people. What happens if I change these entries to Full Time? What's happened over here? Nothing. We need to refresh the pivot table. Analyze from the Contextual tab, Refresh or we could press alt F5. Now we've got our 2 Full Time people here and 1 Contract person. Now, suppose we add a new record. Now, I don't wanna do much typing, so I'll simply say we've got a new employee called Mary Hayes.

So this is fairly close here. Maybe that person's gonna be a Half-Time employee also. We'll change the Compensation amount and no Rating at first. So we'll simply copy this to make it a little bit faster. So I'm gonna take this data and copy it and tack it onto the bottom. So I'll press control C, double-click the bottom edge of this highlighted area, zip right down here over to cell A743 and control V to paste. And double-click here, and that's Mary. And we'll get back to changing some of the other things a bit later. No Rating yet, and we'll change the salary when I get to it and so on.

Anyway, back up top. What's happened in the pivot table? We now have 6 employees. Well, let's do a refresh. We didn't do a refresh yet. Alt F5. What happens? Nothing. In other words, we added data to the bottom of the original source data. What we probably should have done and could have done, was to insert some cells, perhaps here or anywhere, and then inserted the new information above that. That's a little bit cumbersome. Adding records at the bottom tends to be easier, particularly if you're adding a group of them.

So one approach here is, with the active cell in the pivot table, go to the Analyze tab, choose Change Source Data and account for the additional records. Now, we only added one here, so the last row instead of being 742 is now 743. And as soon as I click OK, keep an eye on that Grand Total up there in cell L3, instead of that being a 5, it's now going to be a 6. So we see that change. So that's not exactly as smooth as it could be. In other words, every time we add records to the bottom of this list, refreshing the pivot table just doesn't work.

We've gotta go back to the Analyze tab and change the source data. Now, I've got another copy of that source data right here. I'm going to convert this into a table. Not everybody is familiar with this concept. Table concept was introduced in Excel 2007. Initially, it seems as if it's only a visual feature, but it does have some content aspects as well. You can convert data into a table. First of all, make sure that the data you're looking at has no empty rows or columns within it.

It's in one solid cluster. And make sure that the titles are in a single row on top. Just click within that data and you could press control T, think of T for table, control L, think of L for list, possibly on the Home tab. You can start there with Format as Table or from the Insert tab right here, Table. Excel makes a quick scan of your data and nearly always gets it right. Click OK. You'll see some visuals applied, primarily a feature called banded rows is what stands out.

You will also see filtering arrows there. And if you start to scroll, watch the column letters disappear. They're replaced by the actual field names. But there's more to tables than that. If you add a new row at the bottom of the table, it's automatically part of the table. If you add new data on the right edge of it, it's automatically part of the table, too. So let's create a pivot table from this, like we did with the previous example. Insert, Pivot Table, click in the Existing Worksheet option, Location, click there. G1, OK.

And like we did before, Department. We'll make Status be a Columns label entry and drag Employee Name into Values. We want a head count here. And again, changing the Row Labels, Column Labels entries by going to the Design tab in the ribbon, choosing Report Layout, Outline or Tabular. And we can readjust the column widths a bit over to the right here. There we go. So there's our pivot table. Now, I'm not going to change the Status as I did before. That would be a bit redundant. But let's again go through the motions here of adding information to the bottom of the table.

Remember, when you add information to the bottom of the table, it automatically becomes part of the table. So that's gonna make it easier to refresh. I'll simply highlight the data here, press control C, double click at the bottom edge to get to the bottom, click right there, control V. I'll adjust the name a little bit later. Let's head back up top, control home. Here's our pivot table. Now, I haven't done the refresh yet, but that's all we need to do now. I'll simply press Alt F5 and the table's refreshed. There's that Grand Total of 6, you can see it up there.

So, the Refresh feature works much better when the source data is a table, and particularly if the data's dynamic. If we're adding new records a lot, even deleting records a lot, any time you've got source data for a pivot table that's changing often, it's to your advantage to convert that data into a table. And that simply means when you add new records at the bottom, all you have to do in the pivot table is a simple refresh. You don't have to go back and change the source data. So some real advantages to using source data for a pivot table, convert it into a table first.

It is a little confusing at times because on the one hand, we're using the term pivot table, and on the other hand, we're using the term table. But in the example, as we've seen here, working with a table is more efficient than data that's not converted to a table.