Courses / PivotTables & PivotCharts
1. Selecting source data range
How to choose and prepare the contiguous range or named table that will be used as the PivotTable source.
How It Works
Think of the Pivot source like a photo frame: whatever fits inside the frame when you create it is what the Pivot will analyze; the frame doesn't automatically grow unless you use a flexible frame (Table or dynamic range).
The Pivot reads every cell inside the rectangular range you give it and treats each column as a field.
Blank rows or header-like text inside the range can break field detection and grouping.
Whole-column references or excessively large ranges slow performance and can include unintended blanks.
Converting the source to an Excel Table creates a dynamic range so new rows are included after refresh.
Level Up
Use structured Table references in calculated fields: Create calculated fields that reference Table column names so formulas remain correct as the table grows and columns are renamed.
Dynamic named ranges with OFFSET or INDEX: Build a dynamic named range with INDEX (preferred over volatile OFFSET) to feed the Pivot source when you can't convert the sheet to a Table.
Clean-and-validate pre-Pivot checklist: Automate a short validation routine or checklist (no blank header rows, consistent data types, no subtotals) to ensure grouping and aggregations work as expected.
Prove It
Given a sample sales worksheet described below, produce a one-paragraph decision and a short checklist: 1) choose the exact source you will use for a PivotTable and justify why, 2) list 5 preparation steps you will run (e.g., convert to Table, remove totals, fix text-numbers), and 3) explain how you'll ensure new daily rows are included going forward. Worksheet: columns A:Date, B:Region, C:SalesRep, D:Product, E:Amount; rows 1 header, data rows 2–1200, occasional blank rows every ~200 rows, a totals row at row 601, and teammates add daily rows below row 1200.
Excel / PivotTables
Selecting source data range
Choosing and preparing the contiguous range or named table that a PivotTable or PivotChart will use as its data source.
"I want the Pivot to always include new rows and group dates correctly without surprises."
Select a clean, contiguous block of data or convert the data to an Excel Table (or dynamic named range) so the Pivot reads the correct records and fields. Clean means no stray headers, totals, blank rows/columns, or mixed data types in a column.
"I want the Pivot to always include new rows and group dates correctly without surprises."
Select a clean, contiguous block of data or convert the data to an Excel Table (or dynamic named range) so the Pivot reads the correct records and fields. Clean means no stray headers, totals, blank rows/columns, or mixed data types in a column.