Ravindra BagaleCourses & study guides मराठी Track your progress

Excel PivotTable with Multiple Values, Nested Rows and Columns: Count and Sum Side by Side, City by Payment Mode

By Last updated:

The first PivotTable chapter put one field in Rows and one in Values. Real reports ask more at once: how many orders and how much profit per city, how each city splits between COD and Online, and a grid of cities against payment modes. All of it comes from dragging more than one field into the same box. In this chapter you build those pivots on the same 24 made-up orders.

What you'll learn in this chapter

  • Two fields in Values: Count and Sum side by side
  • Two fields in Rows: City with Payment_Mode nested inside
  • A field in Columns: a cross-tab of City and Payment_Mode
  • Columns vs Filters for the same field
  • What really decides Count or Sum, and numbers in Rows
  • Adding a pivot by typing the table name

Practice file: excel-pivottable-multiple-values-columns-practice.xlsx: Orders (the table named Orders) and Pivot answers (every pivot result in this chapter, worked out with COUNTIFS and SUMIFS).

Made-up data: the 24 orders are made up for practice. They imitate a quick-commerce app such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

1. Start from the table

Click inside the Orders table and use Insert > PivotTable > From Table/Range, New Worksheet, OK, as in the first PivotTable chapter. The PivotTable Fields pane has four areas:

  1. Rows: the groups, down the side.
  2. Columns: the groups, across the top.
  3. Values: what is counted or added.
  4. Filters: a condition for the whole pivot.

2. Two fields in Values

  1. Drag City into Rows.
  2. Drag Order_ID into Values: Count of Order_ID.
  3. Drag Profit into Values as well: Sum of Profit appears in a second column.
Row Labels Count of Order_ID Sum of Profit
Kolhapur 4 64
Nagpur 4 154
Nashik 4 36
Pune 6 6
Sambhaji Nagar 3 61
Solapur 3 6
Grand Total 24 327

Pune has the most orders (6) but almost no profit (6). Nagpur has 4 orders and the highest profit, 154. Two numbers side by side tell a story that neither tells alone.

When you add a second field to Values, Excel also puts a Σ Values item in the Columns area. It only decides where the value columns go; leave it there.

Two fields in Values: count and sum side by side Row Labels Count of Order_ID Sum of Profit Kolhapur 4 64 Nagpur 4 154 Nashik 4 36 Pune 6 6 Sambhaji Nagar 3 61 Solapur 3 6 Grand Total 24 327 Values area: Count of Order_ID Sum of Profit Columns area: Σ Values (added by Excel) Pune: most orders (6), profit only 6 Nagpur: 4 orders, highest profit 154

Figure 1. City in Rows; Order_ID and Profit both in Values. Each city gets a count and a sum in one row: Pune 6 orders but profit 6, Nagpur 4 orders and profit 154. Grand Total 24 and 327.

3. Two fields in Rows: nested groups

  1. Remove Profit from Values (drag it out), keep Count of Order_ID.
  2. Drag Payment_Mode into Rows, below City.
Row Labels Count of Order_ID
Kolhapur 4
  COD 2
  Online 2
Nagpur 4
  Online 4
Nashik 4
  COD 3
  Online 1
... ...
  1. Each city gets its own small group, with its payment modes inside.
  2. Nagpur shows only Online: it has no COD orders, so there is nothing to list.
  3. The order of the fields in Rows matters. Payment_Mode above City would give COD and Online as the outer groups, with cities inside each.
  4. Click the − button next to a city to collapse its group.

4. A field in Columns: the cross-tab

Now drag Payment_Mode from Rows into Columns.

Row Labels COD Online Grand Total
Kolhapur 2 2 4
Nagpur 4 4
Nashik 3 1 4
Pune 1 5 6
Sambhaji Nagar 1 2 3
Solapur 2 1 3
Grand Total 9 15 24

Every value of the Columns field becomes a column of its own: COD and Online. The grid answers "how many COD orders does Pune have?" at a glance (1), and you get totals in both directions. Nagpur's COD cell is empty, not 0, because there are no rows to count; PivotTable Analyze > Options > For empty cells show: 0 fills it in.

Classroom line

When you put a field in Columns, a column is created for every value it has.

Payment_Mode in Rows (nested) vs in Columns (grid) Row Labels Count Kolhapur 4 COD 2 Online 2 Nagpur 4 Online 4 Nashik 4 COD 3 Online 1 Pune 6 COD 1 Online 5 ... Row Labels COD Online Grand Total Kolhapur 2 2 4 Nagpur 4 4 Nashik 3 1 4 Pune 1 5 6 Sambhaji Nagar 1 2 3 Solapur 2 1 3 Grand Total 9 15 24 Nagpur has no COD orders: the cell stays empty (Options > For empty cells show: 0)

Figure 2. The same field, two places. Payment_Mode under City in Rows gives nested groups (Nagpur has only Online). Payment_Mode in Columns gives a grid with COD and Online columns; Nagpur's COD cell stays empty.

5. Columns or Filters?

Payment_Mode can also go into Filters, which you used for Status in the first PivotTable chapter. The difference:

  1. Columns shows every value side by side: COD and Online together.
  2. Filters shows one value at a time (or a chosen few) and hides the rest: choose Online and the pivot shows only Online orders, 15 in total.

Use Columns to compare; use Filters to focus.

6. What really decides Count or Sum

Excel chooses the calculation from the column it puts in Values: a column with text (such as Order_ID) is counted, a column with only numbers (such as Profit) is summed. This rule is only about Values.

Rows, Columns and Filters can hold text or numbers. Drag Quantity into Rows, for example, and the pivot groups the orders by quantity: 1, 2, 3, 4, 5, 6 and 10. A year, a rating or a store number works the same way. To change Count to Sum (or anything else), use Value Field Settings.

Ravindra Bagale's Tip

Before dragging fields, say the question out loud: "count of orders, by city, split by payment mode". The words map straight onto the boxes: what you count or add goes to Values, the first "by" to Rows, the "split by" to Columns.

7. Another pivot from the table name

The first PivotTable chapter showed how to copy a pivot. The second way is to start a fresh one on the same sheet:

  1. Click an empty cell, for example H3.
  2. Insert > PivotTable > From Table/Range.
  3. In Table/Range, type the table name: Orders.
  4. Choose Existing Worksheet; Location already shows H3. Click OK.

Typing the table name, rather than selecting cells, means the pivot grows with the table: add orders, then PivotTable Analyze > Refresh.

"Count of orders, by city, split by payment mode" Filters (Status, if needed) Columns Payment_Mode Rows City Values Count of Order_ID What you count or add → Values The first "by" → Rows "Split by" → Columns "Only for" → Filters

Figure 3. The question "count of orders by city, split by payment mode" mapped onto the four areas: Order_ID to Values, City to Rows, Payment_Mode to Columns, Status left for Filters.

8. The whole chapter on one page

You want Drag
Count and profit per city City to Rows; Order_ID and Profit to Values
Payment modes inside each city City, then Payment_Mode, to Rows
City × payment mode grid City to Rows, Payment_Mode to Columns
Only Online orders Payment_Mode to Filters, choose Online
  1. Several fields in Values give several number columns.
  2. Several fields in Rows give nested groups; their order matters.
  3. A field in Columns gives one column per value, with totals both ways.
  4. Columns compares all values; Filters shows one.
  5. Text in Values is counted and numbers are summed; Rows can hold either.

9. Try at home

Try at home

Task 1

  1. In the cross-tab, put Profit in Values instead of Order_ID. What is the Sum of Profit for Pune, Online? (18.)

Task 2

  1. Put Payment_Mode in Rows above City. How many cities appear under COD? (5: every city except Nagpur.)

Task 3

  1. Put Status in Columns and City in Rows with Count of Order_ID. How many Delivered orders does Pune have? (5.)

Recap

In short

  1. Drag two fields into Values for Count and Sum side by side.
  2. Two fields in Rows nest one inside the other.
  3. A field in Columns makes a cross-tab with totals both ways.
  4. Columns compares; Filters focuses.
  5. Type the table name to add another pivot that grows with the data.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: PivotTable layout: two values and a cross-tab. Example data table with Count and Sum side by side: 3 and ₹220; Cross-tab by payment: ₹180 Online, ₹40 COD; A pivot that grows with data: Source: orders.
Card 1 of 2: PivotTable layout: two values and a cross-tab · Download PNG
Revision card: Columns or Filters?. Covers Shows; Use to; Example. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: Columns or Filters? · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Total parcels (Easy): Add up Parcels in D2:D11.
  2. Average cost (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail (Easy): How many rows have Carrier "Royal Mail"?
  4. Largest parcels (Easy): The largest value in D2:D11.
  5. Parcels for Royal Mail (Medium): Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Manchester (Medium): Total of D2:D11 where B is "Royal Mail" and C is "Manchester".
  7. Best in Royal Mail (Medium): Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Vikram Singh (Medium): The Parcels for "Vikram Singh" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Royal Mail (Hard): Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →