Excel PivotTable Quick Start in 10 Minutes: Summarize, Aggregate, and Build Reports

If you want to create reports quickly, Excel PivotTables are the answer. Just drag and drop your source data to instantly calculate sums, averages, and percentages. Follow the 10-minute routine below.

Quick Fix (Complete in 10 Minutes)

1. Create a table: In the source range, press Ctrl+T → name it SalesT.
2. Insert > PivotTable: Select SalesT → New Worksheet.
3. Arrange fields: Rows=Category, Columns=OrderDate, Values=Amount (Sum), Filters=Region.
4. Summarize values: In Value Field Settings, choose Sum, Count, or Average.
5. Percentages: Show Values As → “% of Column Total” (or % of Grand Total).
6. Formatting/layout: Adjust number formats, subtotals/grand totals, and tabular or outline layout.

PivotTable Essentials

• Understand the arrangement and roles of Rows, Columns, Values, and Filters.
• Summarize values (Sum, Count, Average) and Show Values As (%/running total).
• Use layout to set tabular form and turn subtotals/grand totals on or off.

Practical Example: Sales by Month and Category

OrderDate	Category	Region	Amount
2025-01-05	TOP	East	120000
2025-01-20	BTM	West	90000
2025-02-03	TOP	East	80000
2025-02-14	ACC	South	45000
2025-03-09	BTM	North	150000

Grouping: Right-click the date field > Group (Months/Quarters/Years).

Averages and percentages: Add Amount to Values twice → set one to Average and the other to “% of Column Total.”

Slicers: PivotTable Analyze > Insert Slicer > Region.

Date Groups & PivotCharts

• Right-click a date > Group > Months/Quarters/Years.
• Use PivotTable Analyze > PivotChart for visualization.

Automatic Updates

• When based on an Excel table, new rows are automatically included.
• Refresh: PivotTable Analyze > Refresh (Alt+F5) / Data > Refresh All.
• Change data source: PivotTable Analyze > Change Data Source.

Troubleshooting

Issue	Cause	Solution
Calculated as Count	Text/blanks	Convert to numbers; set the value field to Sum
Cannot group by month	Dates are text	Convert to dates, then group
New data not included	Fixed range	Convert to a table or change the data source
No percentage option	The value field is not selected	Select the value field, then use Show Values As
Slow performance	Too many fields/excessive formatting	Minimize fields and clean up subtotals/formatting
Slicer controls only one PivotTable	Connections are not configured	Select multiple PivotTables in Report Connections

Conclusion & Related Articles

• Automate Aggregation with SUMIFS and AVERAGEIFS
• Conditional Maximum and Minimum Values with MAXIFS and MINIFS
• Text Preprocessing with TEXTSPLIT
• Practical Multiple-Criteria VLOOKUP Patterns
• IF Function Branching Patterns

엑셀 조건부 서식 완벽 가이드 — 기초·상위N·아이콘셋·색조·수식 규칙