Excel from Basics to Advanced — PivotTables and Charts

Why Are PivotTables So Powerful?

PivotTables let you aggregate and analyze thousands of rows of data in seconds, without writing a single formula. They’re the most widely used analysis tool in real-world work — for sales performance analysis, cost aggregation by department, month-over-month revenue trends, and more.

Creating a PivotTable

Data prerequisites

Before you build a PivotTable, your data needs to meet the following conditions.

Condition	Description
First row is a header	Column titles must be clear
No blank rows or columns	No gaps in the middle of the data
No merged cells	Merged cells can’t be pivoted
Consistent data types	Date columns should all be dates; number columns all numbers

Steps to create one

Click any cell within your data
Insert → PivotTable
Confirm the range (auto-detected) → placing it on a new sheet is recommended
Drag the fields you want in the Field List pane

Understanding the 4 areas

Filters area: filters the whole dataset (e.g., year)
Rows: row labels / vertical grouping
Values: aggregated values (sum, count, average)
Columns area: horizontal axis (e.g., quarter)

Area	Role	Example
Rows	Vertical-axis grouping	Region, department, product name
Columns	Horizontal-axis grouping	Quarter, month, year
Values	Aggregate calculation	Total revenue, average quantity
Filters	Filter the whole view	Show only a specific year

PivotTable: Practical Configuration

Changing the value aggregation method

The default is SUM, but you can switch it to something else.

Click the field name in the Values area → Value Field Settings

Choose Sum, Count, Average, Max, Min, StdDev
“Show Values As” → % of grand total, % of row total, etc.

Grouping dates

Placing a date field in Rows/Columns automatically groups it by year, quarter, or month.

Right-click the date field → Group

Choose years, quarters, months, weeks, or days
Selecting multiple options creates a hierarchical structure

Sorting and top-N filtering

Right-click a row label
Sort
Descending (highest revenue first) Right-click a row label
Filter
Top 10 by value

Slicer

A button-based UI that filters a PivotTable with a single click.

Inserting a slicer

Select the PivotTable
PivotTable Analyze tab
Insert Slicer
Choose the field to filter by (e.g., region, product category)

Connecting multiple PivotTables

Right-click the slicer → Report Connections

Synchronize with other PivotTables that share the same data source
Filter multiple charts at once from a single dashboard

Timeline

A slicer specialized for date fields.

Select the PivotTable → PivotTable Analyze tab → Insert Timeline

Choose a period by year, quarter, month, or day
Drag to adjust the date range

PivotChart

A dynamic chart linked directly to a PivotTable.

How to create one

Select the PivotTable
PivotTable Analyze tab
PivotChart
Choose a chart type
Insert

Characteristics:

The chart updates automatically whenever the PivotTable changes
Works together with slicers and timelines
Field buttons let you filter directly from within the chart

Standard Charts — Types and When to Use Them

Chart types and best uses

Chart type	Best for	Example
Column	Comparing items	Comparing revenue by department
Bar	Comparing items with long labels	When product names are long
Line	Trends over time	Monthly revenue trend
Pie	Share of a whole	Market share
Doughnut	Pie chart with center text	KPI attainment rate
Area	Cumulative trend	Cumulative sales by product
Scatter	Correlation between two variables	Ad spend vs. revenue

Basic steps to build a chart

Select the data range (including headers)
Insert → Chart → choose a type
Edit the chart title, axis labels, and legend
Adjust the chart style/colors

Dual-Axis Charts (Combo Charts)

Used when you need to show two datasets with different units on the same chart.

Example: showing monthly revenue (in dollars) and growth rate (%) together

How to create one:

Create a chart with both data series included
Right-click the series you want on the secondary axis
“Format Data Series” → “Secondary Axis”
Change that series’ chart type (e.g., to a line)

Creating a combo chart directly

Insert
Chart
Combo
Set each series’ chart type and axis (primary/secondary)

Sparklines — Miniature In-Cell Charts

Ultra-compact trend charts sized to fit within a single cell.

Insert → Sparklines → Line/Column/Win-Loss

→ Choose the data range and where to display it

Use case: visualizing each row’s trend in an adjacent column next to a table

Formatting Charts — Practical Tips

Quick formatting

Select the chart → the ”+” button on the right:

Add/remove chart elements (title, data labels, gridlines, etc.) Select the chart → the paintbrush icon on the right:
Change the chart style and color theme

Adding data labels

Right-click the chart → Add Data Labels
Click a label → Format Data Labels → choose what to display
(value, series name, category name, percentage, etc.)

Formatting the chart axis

Double-click the axis → Format Axis

Fix the minimum/maximum value (when the auto range isn’t ideal)
Change display units (e.g., “thousands” for values in the 1,000s)
Set the date-axis interval

Example: Conditional PivotTable Aggregation

Steps for a monthly, by-department revenue analysis:

1. Prepare the source data — Date | Department | Rep | Revenue

2. Configure the PivotTable

Rows: Date (grouped by month)
Columns: Department
Values: Revenue (sum)

Slicer: add a filter for Rep
PivotChart: column + line combo

(columns: each department’s revenue, line: month-over-month growth rate)

5-Question Practice Quiz

Q1. When you place a date field in the “Rows” area of a PivotTable, how is it grouped by default?

① Only shown at the daily level
② Automatically grouped by year, quarter, and month
③ Displayed as a plain date list with no change
④ It causes an error

Answer: ② (Excel automatically applies hierarchical grouping to date fields)

Q2. Which statement correctly describes the difference between a slicer and a standard filter?

① A slicer can only be used with numeric fields
② A slicer can be connected to multiple PivotTables at once
③ A standard filter offers more options
④ A slicer can only be used with date fields

Answer: ② (A slicer’s key advantage is its report-connection capability)

Q3. Which situation is best suited to a dual-axis chart?

① Comparing sales of the same product across colors
② Displaying monthly revenue (in dollars) alongside customer satisfaction (a 1–5 scale)
③ Ranking revenue by region
④ Year-over-year revenue growth trend

Answer: ② (Use dual axes when comparing two metrics with different units)

Q4. How do you show ”% of row total” instead of the plain revenue sum in a PivotTable?

① Type a ”%” symbol directly in the Values area
② Value Field Settings → Show Values As → ”% of Row Total”
③ Apply conditional formatting
④ Calculate it separately with a SUMIF formula

Answer: ②

Q5. Which statement about sparklines is correct?

① They’re created on a separate sheet, just like a normal chart
② They’re a miniature chart displayed inside a single cell
③ They support up to 100 data series
④ They don’t print