Let’s take a look. When you’re working with a pivot table, you can move fields from one area to another at any time. Each time you move a field, the pivot table will rebuild itself to respect the new field configuration. The easiest way to move a field is just to drag it from one area to another. In this pivot table, we have the Product field in the Row Labels area and Region in the Column Labels areas. We can just drag the fields to swap locations. And drag them back again to restore the original orientation. In this same way, we can look at product sales by region and state by adding State to the Column labels area. Product sales are now grouped by Region first, and then State. Let’s remove Region and add Category under products. Note that the order that the fields appear is reflected in the pivot table. Some sequences don’t make sense. For example, it doesn’t make sense to have Product appear above Category. If we try that, The pivot table rebuilds itself correctly, but the structure isn’t useful, since each product appears in only one category. However, if we move the Category field so that it appears before Product, we get a configuration that does make sense. The pivot table now shows product sales with products grouped by Category. Numeric fields are normally placed in the Values area. If we put a numeric field like Quantity into the row labels area, the pivot table breaks out product sales by product, and then by quantity, which is interesting but not especially useful. As you experiment with different pivot table configurations, keep the Undo command at the ready. You can use Control-Z to step backwards until the pivot table is in good shape, then start experimenting again.
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.
