PivotTable Data Source - Excel Glossary

The PivotTable Data Source is the foundation of any pivot table analysis in Excel. It can be a contiguous range of cells, a named range, an external database connection, or a data model. Proper source configuration ensures data integrity and enables real-time updates when the underlying data changes. Understanding how to select, maintain, and refresh your data source is crucial for accurate reporting and efficient data management in business intelligence workflows.

Definition

A PivotTable Data Source is the range or table of raw data that feeds a PivotTable, defining what information is available for analysis and summarization. It's critical because the source determines which fields, records, and dimensions you can use in your pivot analysis. Use it whenever you need to consolidate, reorganize, or analyze large datasets dynamically.

Key Points

1The data source must have headers (first row) and be a contiguous range for automatic recognition.
2You can change the data source after creation via PivotTable Design > Change Data Source or Refresh All.
3Excel supports multiple source types: ranges, tables, external connections, and Power Pivot models for scalability.

Practical Examples

→Sales data from A1:F500 containing Date, Product, Region, Quantity, Price, and Revenue columns becomes the source for a pivot table analyzing revenue by region and product.
→A database connection pulling monthly customer transaction data automatically updates the pivot table when the database refreshes.

Detailed Examples

Sales performance analysis

A manager selects quarterly sales data (500+ rows, 8 columns) as the PivotTable source to create a summary by salesperson and region. When new sales are added to the source range, they refresh the pivot to include the latest figures without rebuilding it.

Multi-source consolidation

A finance team uses a Power Query connection as the data source, combining data from multiple Excel files and a SQL database. The pivot table automatically reflects changes across all integrated sources when refreshed.

Best Practices

✓Always use Excel Tables (Format as Table) as your data source instead of ranges—tables automatically expand and are recognized as sources more reliably.
✓Include proper headers and ensure no blank rows or columns within your source data to prevent pivot table errors.
✓Set up scheduled refreshes or manual refresh routines if your source data updates frequently, keeping your analysis current without manual rebuilding.

Common Mistakes

✕Selecting a non-contiguous range as source data causes Excel to exclude certain rows; always verify your range is continuous before creating the pivot.
✕Forgetting to refresh the pivot table after updating the source data results in outdated analysis and incorrect conclusions.

Tips

✓Use keyboard shortcut Ctrl+A within your data range to select all contiguous data automatically before creating a pivot table.
✓Name your data source range for easier reference and management, especially when sharing workbooks with others.

Related Excel Functions

GETPIVOTDATA REFRESH

Frequently Asked Questions

Can I use multiple non-contiguous ranges as a PivotTable data source?

No, a single pivot table requires one contiguous data source. To analyze separate datasets, create multiple pivot tables or consolidate your data into one range first using Power Query.

What happens if I delete rows from the original data source?

The deleted data is automatically excluded from the pivot table when you refresh it. The pivot table structure remains intact, but totals and summaries update to reflect the removal.

Can I change the data source of an existing PivotTable?

Yes, go to PivotTable Design > Change Data Source and select a new range or table. Excel preserves your pivot table layout and field configuration where possible.