So, today we’re going to tackle the question Why Pivot Tables? And this question comes up a lot when people first run into Pivot Tables, because they’re wondering…why should they care? And you’re trying to explain that pivot tables are really fast, that you can summarize data interactively in different ways…blah, blah blah And you can kind of see their eyes just glaze over because, until you’ve actually used a pivot table yourself, it’s really hard to understand why they would be useful to you. So, I think one of the best ways to understand how pivot tables can help is to look at some examples of building some reports, side by side, where one set of reports we’re building manually using normal Excel functions and formulas, and the other set, we’re building with pivot tables. And that’s exactly what we’re going to do in today’s video. And, I want to be really upfront with you here. I love pivot tables. Ever since I got my first pivot table to refresh correctly back in the 1990s, I realized like wow these are really useful tools. So I think that if you use Excel with a lot of raw data, and you need to summarize that data in different ways, and if you need to create good-looking reports, then pivot tables are definitely worth your time. So, I hope by the end of the video you’ll agree with me. Let’s go take a look at some examples.
So, here we are in Excel, and you can see we’ve got a set of raw data. This data represents chocolate sales to several customers in different locations, at wholesale level. You can see we have Date and Customer. City, State, and Region, Product and Product Category, and finally, Quantity, Revenue, Cost of goods sold, and Profit. Now, imagine that your manager sends you this data and asks for a one-page summary that shows a breakdown of sales by product. So, assuming that you don’t know how to use pivot tables, but you are pretty good with Excel formulas, let’s build out a manual report first. The easiest way to do this is to use the SUMIFS function. SUMIFS is easy to use, but it can create some pretty ugly formulas, so to make things easier to read, and avoid a lot of absolute referencing, I’m going to go ahead and name the columns first. Now we have a name for each column in the data. While I’m here, I’m also going to name the full set of data so I can easily refer to it when I create a pivot table later. Now let’s build a manual report. First, I’m going to grab a copy of all the products, and then remove duplicates, and sort. Now I’ll add in some labels and formatting. So, we have the bones of the report in place now, but no data. And it’s time to write out the first SUMIFS formula. I could actually use SUMIF, but because I’ll be adding more criteria in just a minute, it’ll be easier if we start with SUMIFs. Because I already named the columns, I can easily type the names as I go, without going back to select the data. Now I need to copy the formula and add a total, and I’ll format the numbers as currency. Now we have the basic report your manager wants…a one page sales report with a breakdown by product. So the manual approach isn’t too terrible, but of course I already knew how to do it, and I used a lot of tips and shortcuts to speed things up. If you’re not as comfortable with formulas, it’s going to be quite a bit harder, with plenty of mistakes and learning as you go. Now let’s build the same report using a pivot table. Since I already named the data, I can just use that name here. We’ll need product as a row label, and revenue as a value. And then apply a number format to revenue. And, that’s it. We now have a pivot table with the same results. And notice that I didn’t use any formulas at all. Of course, no manager will ever be satisfied with their original instructions. They’ll all request changes. So, let’s look at how both of these reports handle a few change requests. First, let’s say your manager comes back and asks for a breakdown by product and region. She wants region across the top. For the manual report, I’ll start by grabbing a copy of all the regions and I’ll follow the same process as we did for products before. This time, I need to transpose the regions across the top. Then I need to clean things up a bit. Next, I need to extend the SUMIFS function to handle both product and region. I’ll need to lock the column reference for product…because I’ll be copying that in just a moment…then add a new range and criteria for region with the row locked. Now I can copy the formulas across the table and add totals. Again, it’s not too terrible, but lots of clicks and plenty of formulas. Changing the pivot table report is a bit easier. All I need to do is add region as a column label and we’re done. Again, there’s no need to update any formulas. I’m also going to change the pivot table layout to “outline”, and line it up again with the manual report. So you hand that back to your manager and an hour later she tells you it’s great. But can she also see a breakdown by year instead of region? Hmmm. This will be trickier. In this case, I know that the data spans only 3 years….2011, 2012, and 2013, so I’ll start there and add those in manually as headers and get rid of the extra column. Now I just need to update the formulas. This is actually a good use case for the SUMPRODUCT function, but that would require a lot more explanation, so let’s stick with SUMIFS for now. Now to update the SUMIFS function to match dates by year, we’re going to have to use two criteria along with the DATE function and some concatenation. The first criteria matches dates that are greater than or equal to the first of the year. The second criteria matches dates that are less than or equal to the last day of the year. In both cases, we’re referencing the year directly and we need to lock the row. Once I copy the formula down and across, we’ve got our report. You can see that this change was quite a bit more complicated, and required a good understanding of Excel formulas and functions, and concepts like concatenation. What about the pivot table? To update the pivot table, I need to first get rid of region. Then, I need to add the date field, and group by Year. As before, there are no formulas to update. So, you get the idea. It’s totally possible to build these reports manually, and in some cases it’s desirable to do it that way. But it’s a lot faster and easier to use a pivot table. Ultimately, a pivot table can build much more complex reports than you’d ever want to tackle manually. Alright, so I hoped you liked that comparison of building some reports manually and the same reports in pivot tables. As you can see, it’s not even close, pivot tables are way faster. So, any time you’re analyzing data in Excel, I think you should be thinking about using pivot tables. But, beyond simple convenience, pivot tables give you a real competitive edge. So whether you’re competing internally with your peers for a raise or promotion, or just recognition. Or, you’re competing externally with other companies in your market to identify trends or opportunities, pivot tables are a tool that can help you stand out and do your job better.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
