Row and column fields in Excel - Dashboard Guide

Dashboard Mode - Row and column fields

Dashboard Goal

Understand how to organize data using row and column fields in a pivot table to analyze sales by product and region.

Sample Data

Order ID	Product	Region	Sales
1001	Apples	East	120
1002	Oranges	West	150
1003	Bananas	East	90
1004	Apples	West	200
1005	Bananas	West	130
1006	Oranges	East	170
1007	Apples	East	160

Dashboard Components

• Pivot Table: Shows total sales by Product as row fields and Region as column fields.
  Formula: Use Excel's Pivot Table feature with Product in Rows, Region in Columns, and Sales in Values (Sum).
• Total Sales per Product: Sum of sales for each product.
  Formula example (outside pivot): =SUMIF(B2:B8, "Apples", D2:D8) for Apples.
• Total Sales per Region: Sum of sales for each region.
  Formula example: =SUMIF(C2:C8, "East", D2:D8) for East region.

Dashboard Layout

+-----------------------+-----------------------+
| Pivot Table           | Total Sales per Product|
| (Rows: Product,       | Apples: 440            |
|  Columns: Region)     | Oranges: 320           |
+-----------------------+-----------------------+
| Total Sales per Region |                       |
| East: 540             |                       |
| West: 480             |                       |
+-----------------------+-----------------------+

Interactivity

Adding a filter for Region will update the pivot table and total sales per product to show only sales from the selected region. Similarly, filtering by Product will update total sales per region and the pivot table accordingly.

Self Check

If you add a filter to show only Region = East, which components update and what are the new totals?

• Pivot Table updates to show sales only for East region.
• Total Sales per Product updates to sum sales only in East region:
  Apples: 280 (120 + 160), Oranges: 170, Bananas: 90
• Total Sales per Region shows East: 540 and West: 0 (or hidden).

Key Result

Pivot table showing sales by product and region with total sales summaries and interactive filters.