Here we have a worksheet that contains a large set of sales data for a business that sells specialty chocolate to retailers. This data contains columns for date, customer, city, state, region, product, category, quantity, total sales. You can see that there are a lot of rows, almost 3000 rows total, each representing an order for one kind of chocolate to one customer. In its current form, this data is hard to understand, because there’s too much detail. To make sense of the information, we need to summarize it, and a pivot table is the perfect tool. Before we look at the pivot table, let’s quickly check the total of all sales. If we select column I, and check the Status Bar, we can see the total is over $278 thousand dollars. On the pivot table sheet, we see a simple pivot table that currently shows only the total of all sales. Notice the total matches the number we just checked manually. Building a pivot table is the process of answering questions you have about the data. For example, what are total sales by customer? With just one click, we can instantly subtotal by customer. What are total sales by City? What are total sales by Product? Or, product sales by state? Or product sales by year? Or, perhaps we only want to see sales for the 5 best selling products? A pivot table makes answering these questions easy. A pivot table is a tool that allows you to explore large sets of data interactively. Once you create a pivot table, you can quickly transform huge numbers of rows and columns into a meaningful, nicely formatted report.
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.
