Abstract
Transcript
Hey, Dave here from Exceljet. So, in these last couple of videos, I've given you a short introduction to the power of pivot tables.
In the first video, I talked about how fast they are. And how you can, using pivot tables, build reports way faster than even an advanced user building the same reports with formulas.
In the second video, we looked at how good pivot tables are at answering questions about data, and how you can use pivot tables a little bit like a food processor to slice and dice data any way you want to answer questions and reveal key information.
But, although pivot tables are easy to set up, they're a little bit slippery, and I get a lot of questions about pivot tables. So in this video I want to take a minute and show you how to handle some common questions that come up when you're working with pivot tables.
Let's go take a look.
Okay, so the first problem we're going to look at is that you've got a pivot table and some data, and you add more data but when you refresh that data doesn't show up in your pivot table.
So, for example, here we've got a pivot table and it's based on this data here. I'm going to take this data over here and I'll add it at the bottom, and come back to the pivot table and refresh, and you can see we still have just 16 properties.
So it's not included, and one way to solve that problem is to go to Pivot Table Options and Change Data Source, and then I can just set the source to include all the new data. And when I hit OK we'll get 26 properties, so that's the new set of data.
I'm going to undo that... undo, undo, undo. And let's look at another way to do it.
So, a better way to do it is to take this data and convert it to an Excel table. So Ctrl + T will do that and, when I click OK, we'll get the table.
And now I'm going to take this same data and paste it below, and you can see it picks up the formatting, and when we come over to the pivot table and click refresh then we get 26 properties.
So, converting your data to a table first is the best way to solve this problem.
Okay, in this example, we have a pivot table and the problem is that the pivot table contains blanks. And this happens most commonly because the source data just doesn't contain an entry for that particular subtotal. So, in this case, like in the top row, Alper doesn't have any sales in September, which is why it's blank.
And the most common way to handle this is to ask the pivot table to display a value, usually zero, for blank entries. And you can do that by just going to pivot table options, and where it says "for empty cells" just add a zero.
Now, this respects the settings for number formatting. So if I change this field to accounting, then you'll see that we get the accounting number format, and where there were zeros we see the single hyphen, which is standard for the accounting number format.
Okay, here's some sales data we've looked at already and the problem, in this case, is that we can see that we have about $3,700 of orders for Bacon Chocolate but we want to know that data that makes up that number and we could start adding more information. We could add Customer, or maybe City and so on, but this is going to get really complicated fast.
So undo that...
If you just want to know the data behind a subtotal—any subtotal in a pivot table—you just have to double-click that subtotal and Excel will give you a full set of data. You can hand that out to somebody else or use it any way you like.
Okay, so in this example, we have a large set of sales data. And, if we want to break this down by Product...I've got a pivot table here that's easy to do, just put in product as a Row Label and Sales as a Value.
And the problem, in this case, is that we don't like that name "sum of sales." Excel will automatically name your value fields, and that names just not very intuitive. So, if you want, you can just rename it to anything you want.
And, if you want to use the same name as in your source data, in this case, Sales, Excel won't like that and will complain. But to solve that problem just add one space at the end of that and you're good to go.
Okay, in this example we have some employees, we've got 300 employees each in a department, and we have a pivot table that shows a breakdown by Department.
So, the problem in this case is we want to show both count but also with a percentage. So the easiest way to do that is just to add the same field again, and then change the second instance to show a percent of total. In this case we'll use percent of grand total. And now we have both a count and a percent of total.
Okay, so here's a pivot table we looked at earlier, and we have again 300 employees broken down into Department. So in this case, the problem is we don't want the data sheet. We just don't want this data sheet to be in this worksheet. Maybe we want to save file size or we just don't want the clutter
So because pivot tables have their own cache that stores a copy of the data, I can just delete this worksheet and the pivot table still is completely operational. And if for some reason you want to get back the full set of data you can just double-click the grand total and the pivot table will give you a full set of data again
Okay, in this example we have some sales numbers and we have a pivot table here that's grouped by quarter and month, and if I copy this pivot table and paste it over here then the two pivot tables will share the same pivot cache. And, one consequence of that is that if I do certain operations on this pivot table, for example if I ungroup this pivot table, then that will affect the first pivot table because they share the same pivot cache.
I'm going to undo that. If you want to avoid that problem you can force the two pivot tables to use a different cache, and one way to do that is just to cut one of the tables and make a new worksheet, paste it in there, refresh it, and then I'm going to copy it and come back over here, paste it, and now, I can ungroup this and you notice that it doesn't affect the first table because now both pivot tables have their own pivot cache. They both still use the same data but they each have their own pivot cache.
Alright, so in this example we have some sales data broken down by product and we're gonna look at the problem of a field losing number formatting.
So if I format these numbers here with a shortcut and get rid of those decimals, they look fine. Notice I forgot to do the grand total and, if I change this pivot table now, I break it down by, say, Region, or Category, notice that my number formatting is completely lost.
So, a better way to apply number formatting...I'm gonna undo this back to where we started...is to just set at the field setting level the number formatting there. So here I'll just do currency, and no decimal places. And now, no matter how I break this down, we'll get the currency number format retained.
Okay, in this example, we have some sales data and the problem is that we have a simple title at the top with a year and if we refresh this table, it changes our column width. And even if I set this back, if I refresh again, then it changes it again or, for example, if I sort, same thing.
Okay, so to fix this problem, what you want to do is go to Pivot Table Options and just turn off the Auto Fit setting. And now we can sort or...fix this first...refresh, and the column widths are not affected.
In this example, we've got some sales data and I can build a report easily that shows a quarterly break down. The date in this case has already been grouped by quarter. But you notice that we get these kind of strange labels at the top column labels and row labels. They really don't make any sense. They take up space and make the column wider.
And there's two ways you can handle this: One of them is to go to pivot table options and...they're called field headers...just turn those things off completely.
The other way to handle them is to go to Design and switch, in this case, (we're on compact, you can't see that, but we're on the compact layout) switch to Outline or Tabular. So if we switch to outline, then we'll get more meaningful field headers.
Okay, so I hope you found those tips on how to overcome some basic problems that you might run into and working with pivot tables useful. And if you're still with me at this point, I think you probably agree that pivot tables are a powerful and useful tool. In fact, if you're using data at all in Excel then I highly recommend that you learn pivot tables. They'll save you a ton of time and aggravation, and in a world that's now overflowing with data, they'll give you a valuable skill that will pay you back again and again.
