Two fields in a report

A report by city shows how much each city earned. But you often want to know more: what exactly people bought there, electronics or clothing. Here's such a report: Austin has a single number in it, and you can't see what it's made of:

The answer was in the same orders. The report was missing a second field: one splits the orders by city, the other splits each city by category.

Groups and subtotals

When Rows holds two fields, the first one sets the groups, and the second one is nested inside each group. In the report, it looks like this:

• the city row is in bold, and next to its name there's a subtotal: the revenue of the whole city;
• below it, indented, come the categories of that city;
• at the bottom, Grand Total for all the cities.

Which field goes outside, which inside

The groups come from the field that's first in Rows, and Excel puts fields there in the order you checked them. A "City, then Category" report helps you compare categories within one city. A "Category, then City" report gathers the cities inside each category: that way you can see which city bought the most electronics. The numbers in both are the same.

A second field in columns

You can put the categories next to the cities instead of under them. Then the report becomes an ordinary table: cities in rows, categories in columns, and where they cross, the city's revenue in that category.

Columns are handy when there are two or three categories and you want to compare them in one row. If there are many categories, the table spreads too wide, so they stay in Rows.

One checkbox broke Austin down into parts: it bought $24,000 worth of electronics and only $3,900 worth of clothing.