From the course: Excel for Engineering Professionals

When (and when not) to use PivotTables - Microsoft Excel Tutorial

From the course: Excel for Engineering Professionals

Start my 1-month free trial Buy for my team

When (and when not) to use PivotTables

“

- [Instructor] Engineers work with lots of data. Instrument readings from production systems, results from lab tests, weather reports, you name it. Eyeballing thousands of rows of data spanning dozens of columns is pointless. So to get information from your data, creating a pivot table is oftentimes a very handy first step. So let's insert a pivot table and I'll just accept the defaults here. Suppose we need a summary of our defect counts for every day. How is that done? Well, first of all, we need the date field and we'll put it in a row area and look what Excel did. It automatically grouped all of the dates on the month and we can change that later on. For example, by just unchecking month here. I said we wanted to summarize number of defect panels. So let's drag that to the values area. And there you have it, the sum of the defect panels for every day in our date range. Now, if you don't like the looks of this you can always select a different view of our pivot table. Now, what if you don't need the sum of the number of defect panels, but you want the average? Suppose that we have multiple machines in this data, all it takes is right click the sum area here, go to the summarized value buy and click on average and that gives me the average number of defect panels per day. If you paid close attention when I right clicked, you will have noticed that there is a select number of choices for the calculation that we have. There's only six here. Let's click the more options button here. And this is the exhausting list of all of the different calculations that my pivot table is capable of doing. Pivot tables do offer calculated fields like in this option, calculated fields but those have serious limitations as well. So I'm not going to talk about that in this video. You can also change the calculation in another way and that is by selecting the show values as and perhaps you want a running total. So this gives us a running total of the number of defect panels. Still, if you need a more complicated calculation than the ones offered here, you're more or less out of luck. A pivot table might not be your best bet. Of course, you might be tempted to start adding formulas here to do those calculations, but beware because if you refresh the pivot table and you have more or maybe less data, then the formulas that you've entered here will not automatically grow or shrink with that data. So to conclude, a pivot table is great for creating basic summaries of your data, but if you need more complex calculations like determining whether machine A's number of defects is significantly higher than machine B's, you might still need formulas. Or you could have a look at the more complicated DAX formulas in a power pivot data model, but that is material for another course altogether.

Contents