# Excel Pivot Tables and Charts

> Summarize thousands of rows in seconds with PivotTables, then chart the result without lying: shape your data, build and reshape a pivot, slice and refresh it, and pick charts and axes that show the truth.


---

# Excel Pivot Tables and Charts

You have a sheet with four thousand sales rows and someone asks, "Which region is selling the most laptops, and is it growing?" You could write a dozen SUMIFS formulas, or you could drag three fields around and have the answer in ten seconds. That is what a PivotTable is for. It is the fastest way most people will ever answer a question about a big table of data.

This guide teaches the idea underneath (a PivotTable is a group-by with a friendly face), the data shape that makes it work, the controls that matter, and how to turn the result into a chart that does not mislead. Checked against Excel for Microsoft 365 on Windows. Where Mac or older versions differ, the text says so.

## Prerequisite

You should be comfortable with cells, ranges, and basic formulas. If not, start with [Excel From Zero](/guides/excel-from-zero). To see the same idea in SQL, [Querying Basics: SELECT and WHERE](/guides/querying-basics-select-where) covers `GROUP BY`.

## How to read this

- **In a hurry?** Read [Phase 1](01-what-a-pivottable-does-and-getting-your-data-ready.md) for the shape rules, then jump to Phase 2 and build one.
- **Pivot not updating?** Phase 3 has the refresh gotcha.
- **Want it to finally make sense?** Read in order. The same twelve-row sales sheet runs through every phase, so you can check every number by hand.

## The phases

1. **[What a PivotTable Does, and Getting Your Data Ready](01-what-a-pivottable-does-and-getting-your-data-ready.md)** - group and aggregate, plus the shape rules and the Table trick.
2. **[Building and Reshaping a PivotTable](02-building-and-reshaping-a-pivottable.md)** - the four areas, Sum vs Count vs Average, Show Values As, grouping, sorting.
3. **[Slicers, Timelines, and Keeping a Pivot Fresh](03-slicers-timelines-and-keeping-a-pivot-fresh.md)** - click-to-filter, refreshing, and the GETPIVOTDATA surprise.
4. **[Charts That Tell the Truth](04-charts-that-tell-the-truth.md)** - choosing a chart, PivotCharts, and the axis tricks that mislead.

## Where to go next

For formulas that reach into your data, see [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work). For data models, Power Query, and larger datasets, see [Advanced Excel](/guides/advanced-excel). To put charts into a living dashboard, read [BI Dashboards That Work](/guides/bi-dashboards-that-work) and [Power BI From Zero](/guides/power-bi-from-zero), and before you trust any number, [Metrics That Lie](/guides/metrics-that-lie).


---

# What a PivotTable Does, and Getting Your Data Ready

A PivotTable looks like magic the first time: you drop a column name into a box and thousands of rows collapse into a tidy summary. There is no magic, only one operation done very fast. Once you can see that operation, you also know why messy data breaks pivots, and most "my pivot is broken" problems are really "my data was the wrong shape" problems.

## What a pivot actually does

A PivotTable does two things in order. First it **groups** your rows by the values in one or more columns. Then it **aggregates** a number column inside each group: add it up, count it, average it.

Here is our running example, twelve sales rows (a real dataset has thousands, but the idea is identical and twelve can be checked by hand):

| Date | Region | Product | Units | Revenue |
|---|---|---|---|---|
| 2026-01-05 | East | Laptop | 2 | 2400 |
| 2026-01-12 | West | Monitor | 5 | 1500 |
| 2026-01-19 | East | Monitor | 3 | 900 |
| 2026-02-03 | North | Laptop | 1 | 1200 |
| 2026-02-10 | West | Laptop | 3 | 3600 |
| 2026-02-17 | East | Laptop | 4 | 4800 |
| 2026-02-24 | North | Monitor | 6 | 1800 |
| 2026-03-02 | West | Monitor | 2 | 600 |
| 2026-03-09 | East | Monitor | 5 | 1500 |
| 2026-03-16 | North | Laptop | 2 | 2400 |
| 2026-03-23 | West | Laptop | 1 | 1200 |
| 2026-03-30 | East | Laptop | 3 | 3600 |

Ask "total revenue by region". Group by Region, then add up Revenue in each group:

```mermaid
flowchart LR
  A["12 rows"] --> B["Group by Region"]
  B --> C["East: 5 rows"]
  B --> D["North: 3 rows"]
  B --> E["West: 4 rows"]
  C --> F["Sum Revenue = 13200"]
  D --> G["Sum Revenue = 5400"]
  E --> H["Sum Revenue = 6900"]
```

| Region | Sum of Revenue |
|---|---|
| East | 13200 |
| North | 5400 |
| West | 6900 |
| Grand Total | 25500 |

Check East by hand: 2400 + 900 + 4800 + 1500 + 3600 = 13200. That is the whole trick.

## The same idea in SQL

If you have met databases, you have already seen this. The PivotTable above is this query:

```sql
SELECT region, SUM(revenue) AS sum_of_revenue
FROM sales
GROUP BY region;
```

`GROUP BY` is the grouping, `SUM` is the aggregate. A PivotTable is `GROUP BY` with a drag-and-drop interface and a built-in grand total. The "pivot" part is that you can also spread a second grouping across the columns, which SQL makes you write by hand. See [Querying Basics: SELECT and WHERE](/guides/querying-basics-select-where) for the SQL side.

> 💡 **Key point**
> A PivotTable never changes your data. It reads your rows and builds a separate summary. You can rearrange it a hundred times and the source stays exactly as it was.

## Shape the source data first

A PivotTable reads your range as a database table. It needs the shape a database table has:

- **One header row**, with a unique, non-blank name above every column. Those names become the field names you drag around.
- **One record per row.** One sale, one row. Not one region per row with a column per month.
- **No blank rows or blank columns inside the data.** When Excel guesses the range for you, a blank row can end the guess early, so rows below it silently vanish from the pivot.
- **No merged cells.** A merged cell holds its value in only one cell of the group, so the others look empty to the pivot.
- **No subtotal or total rows.** The pivot calculates totals itself. A subtotal row in your data is counted as one more record and double-counts everything.
- **One kind of thing per column.** Do not put numbers and text like "n/a" in the same column. Text in a number column changes how the pivot summarizes it (Phase 2), and in older Excel versions so do blank cells.

The "one column per month" layout is the most common offender. A sheet with columns Jan, Feb, Mar reads well for a human but works badly for a pivot to use, because the month is spread across headers instead of being a value in a column. Reshape it so Month is its own column and each month-region pair is one row. That tall, narrow layout is what pivots are built for.

## Turn the range into a Table

Select any cell in your data and press Ctrl+T (or choose **Insert > Table**). Leave **My table has headers** checked and select **OK**. Excel wraps the range in a Table, an object that grows automatically when you type in the row directly below it or the column next to it.

Rename it so you can recognize it later: select a cell in the Table, open the **Table Design** tab, and type a name such as `tblSales` in the **Table Name** box.

Why this matters: when you build a pivot from a Table, the pivot's source is the Table, not a fixed address like `A1:E13`. Add 500 new rows and the Table expands to include them. Build a pivot from a plain range and you have to widen the source yourself, which people forget.

## Your turn: spot the problem

You inherit a sheet with a "Total" row at the bottom that sums Revenue. You build a pivot and the Grand Total is exactly double what it should be. What happened? The pivot treated the Total row as one more record and added its revenue on top of the real rows. Delete the Total row and refresh the pivot.

Check yourself before moving on:

```quiz
[
  {
    "q": "In database terms, what does a PivotTable do to your rows?",
    "choices": ["Sorts them alphabetically", "Groups them by one or more columns and aggregates a number column", "Deletes duplicate rows from the source"],
    "answer": 1,
    "explain": "A pivot is a group-by plus an aggregate such as sum, count, or average. It never edits the source rows."
  },
  {
    "q": "Which source layout works best for a PivotTable?",
    "choices": ["One column per month (Jan, Feb, Mar) with one row per region", "One record per row, with Month as its own column", "Blank rows between regions to keep it readable"],
    "answer": 1,
    "explain": "Pivots need one record per row so that every attribute, including the month, is a value in a column they can group by."
  },
  {
    "q": "Why build the pivot from a Table instead of a plain range?",
    "choices": ["Tables make the pivot sort faster", "The Table grows when you add rows, so the pivot's source grows with it", "Plain ranges cannot be pivoted at all"],
    "answer": 1,
    "explain": "A pivot built on a Table points at the Table, which expands as data is added. A fixed range does not."
  }
]
```

## Recap

1. A PivotTable groups rows by column values and aggregates a number for each group, like SQL `GROUP BY`.
2. It reads your data and builds a separate summary. The source never changes.
3. Source data needs one header row, one record per row, no blank rows, no merged cells, no subtotal rows.
4. Reshape wide layouts (one column per month) into tall ones before pivoting.
5. Press Ctrl+T to make a Table, name it, and build the pivot from it so it grows with your data.

Next up, [Building and Reshaping a PivotTable](02-building-and-reshaping-a-pivottable.md): the four areas, and how Sum, Count, and Show Values As change the story.


---

# Building and Reshaping a PivotTable

You have the data in a Table. Now you build the summary, and then, the part that makes pivots addictive, you rearrange it in seconds to ask a different question. This phase covers the four areas where fields live, how to change what gets calculated, and how to bucket dates and numbers. Every number below comes from the twelve-row sales sheet in [Phase 1](01-what-a-pivottable-does-and-getting-your-data-ready.md), so you can check it by hand.

## Build your first one

1. Click any cell inside the Table.
2. Choose **Insert > PivotTable**. In the dialog, the **Table/Range** box shows your Table name. Pick **New Worksheet** and select **OK**.
3. A blank pivot appears with a **PivotTable Fields** pane. Tick **Region**, then tick **Revenue**.

Excel puts Region (text) in **Rows** and Revenue (a number) in **Values**, where it shows as **Sum of Revenue**. You get the table from Phase 1: East 13200, North 5400, West 6900, Grand Total 25500.

## The four areas

The field pane has four drop zones, and each one answers a different job:

| Area | What it does | In our example |
|---|---|---|
| **Rows** | Each distinct value becomes a row (a group) | Region |
| **Columns** | Each distinct value becomes a column, giving a second grouping | Product |
| **Values** | The number that gets aggregated in every cell | Sum of Revenue |
| **Filters** | Narrows the whole pivot to some values, shown as a dropdown above it | Product |

Drag Product from Rows or the field list into **Columns** and you get a two-way grid:

| Sum of Revenue | Laptop | Monitor | Grand Total |
|---|---|---|---|
| East | 10800 | 2400 | 13200 |
| North | 3600 | 1800 | 5400 |
| West | 4800 | 2100 | 6900 |
| Grand Total | 19200 | 6300 | 25500 |

East and Laptop: 2400 + 4800 + 3600 = 10800. Every cell is "group by Region and Product, then sum Revenue".

Now drag Product out of Columns and into **Filters** instead. A dropdown appears above the pivot. Choose Laptop and the pivot shows only laptop rows: East 10800, North 3600, West 4800, Grand Total 19200. Filters change which rows the pivot sees. Rows and Columns change how they are grouped.

## Sum, Count, Average

The Values area aggregates with Sum for numbers by default. Right-click any number in the pivot, choose **Summarize Values By**, and pick another function. The same Revenue field gives three different answers:

| Region | Sum of Revenue | Count of Revenue | Average of Revenue |
|---|---|---|---|
| East | 13200 | 5 | 2640 |
| North | 5400 | 3 | 1800 |
| West | 6900 | 4 | 1725 |
| Grand Total | 25500 | 12 | 2125 |

Count is how many rows are in each group, which is how many sales there were. Average is Sum divided by Count: East is 13200 / 5 = 2640. Note the Grand Total average, 25500 / 12 = 2125. It is not the average of the three region averages (that would be 2055), because East has more rows and counts for more. A pivot averages rows, not groups.

For more control, right-click a value and choose **Value Field Settings**. Its **Summarize Values By** tab also lists Max, Min, Product, and Count Numbers.

> ⚠️ **Gotcha**
> Excel picks Sum for a column of numbers but Count when the column holds text. If a number column has text in it, such as "n/a" or numbers stored as text, the pivot labels the field **Count of Revenue** instead of **Sum of Revenue** and gives you row counts where you expected dollars. Always read the field label after you add it to Values. (In Microsoft 365, blank cells no longer trigger this, a change Microsoft made in 2018. In older versions, a single blank cell does, so fill blanks with 0 or fix them at the source.)

## Show Values As

Sometimes the question is not "how much" but "what share" or "how far along". Right-click a value, choose **Show Values As**, and pick a calculation. The pivot recalculates what each cell displays without touching your data.

With Region in Rows and Sum of Revenue in Values, **% of Grand Total** gives each region's share of 25500:

| Region | Sum of Revenue | % of Grand Total |
|---|---|---|
| East | 13200 | 51.8% |
| North | 5400 | 21.2% |
| West | 6900 | 27.1% |

East is 13200 / 25500 = 0.5176, shown as 51.8%. The displayed figures add to 100.1% only because of rounding; the real shares sum to exactly 100%.

Other choices you will use: **% of Row Total** (each cell as a share of its row) and **% of Column Total**. With Region in Rows and Product in Columns, **% of Row Total** shows each region's laptop and monitor mix:

| Region | Laptop | Monitor |
|---|---|---|
| East | 81.8% | 18.2% |
| North | 66.7% | 33.3% |
| West | 69.6% | 30.4% |

East laptop: 10800 / 13200 = 81.8%. **Running Total In** accumulates down a field you choose, which needs dates, so we come back to it right after grouping.

To see the raw number and the percentage side by side, drag Revenue into Values a second time, then set Show Values As on only the second copy.

## Group dates

Real data has a date on every row, but you rarely want 4,000 individual dates. You want months or quarters. Drag **Date** into Rows. In current Microsoft 365 builds, Excel usually groups dates automatically, choosing levels such as Years, Quarters, or Months based on how far your dates span (you may see extra Quarters or Years fields in the pivot field list). If you see ungrouped dates, or levels you do not want, right-click any date in the pivot, choose **Group**, and in the **By** list select **Months** (and **Years** if your data spans more than one year, so January 2026 and January 2027 do not merge). Select **OK**.

Our three months:

| Month | Sum of Revenue |
|---|---|
| Jan | 4800 |
| Feb | 11400 |
| Mar | 9300 |
| Grand Total | 25500 |

February: 1200 + 3600 + 4800 + 1800 = 11400. Now **Running Total In** makes sense. Right-click a value, choose **Show Values As > Running Total In**, and choose the base field (the month field):

| Month | Running total of Revenue |
|---|---|
| Jan | 4800 |
| Feb | 16200 |
| Mar | 25500 |

February is 4800 + 11400 = 16200, and March lands on the grand total, 25500.

> ⚠️ **Gotcha**
> Grouping dates fails with the message "Cannot group that selection" when the date column contains blanks or text instead of real dates. Dates typed as text such as "5th Jan" are the usual culprit. Fix the source cells, then group again.

## Group numbers

You can bucket a number field too. Drag **Units** into Rows and Revenue into Values, right-click a Units value, and choose **Group**. Set **Starting at** 1, **Ending at** 6, **By** 2:

| Units | Sum of Revenue |
|---|---|
| 1-2 | 7800 |
| 3-4 | 12900 |
| 5-6 | 4800 |
| Grand Total | 25500 |

Excel labels the buckets 1-2, 3-4, and 5-6. The 1-2 group holds five rows (units 2, 1, 2, 1, 2 with revenue 2400, 1200, 600, 2400, 1200), which add to 7800. To undo a grouping, right-click a grouped item and choose **Ungroup**.

## Sort

Right-click any value in the pivot, choose **Sort**, then **Sort Largest to Smallest**. The regions reorder to East 13200, West 6900, North 5400. Sorting a pivot by its values answers "who is biggest" in one click.

## Your turn: reshape it

Starting from Region in Rows and Sum of Revenue in Values, which single change shows the average sale per region? Right-click a value, choose **Summarize Values By > Average**, and you get East 2640, North 1800, West 1725. No formula needed.

Check yourself before moving on:

```quiz
[
  {
    "q": "In the sales pivot, where does the Region field go to make one row per region?",
    "choices": ["Values", "Rows", "Filters"],
    "answer": 1,
    "explain": "Rows creates one row for each distinct value of the field. Values is for the number that gets aggregated, and Filters narrows what the pivot sees."
  },
  {
    "q": "Your pivot shows Count of Revenue where you expected Sum of Revenue. What is the most likely cause?",
    "choices": ["The Revenue column contains text, so Excel defaulted to Count", "The pivot needs refreshing", "Rows and Columns are swapped"],
    "answer": 0,
    "explain": "Excel defaults to Sum for numbers and Count when the column has text. A number column with text in it, such as n/a or numbers stored as text, flips the default."
  },
  {
    "q": "In our data, East has 5 sales totaling 13200 and the whole company has 12 sales totaling 25500. What is the Grand Total of Average of Revenue?",
    "choices": ["2055, the average of the three region averages", "2125, the total revenue divided by 12 rows", "25500"],
    "answer": 1,
    "explain": "A pivot averages rows. 25500 divided by 12 rows is 2125, which differs from the average of the region averages."
  }
]
```

## Recap

1. Insert > PivotTable on a cell in your Table, then drag fields into Rows, Columns, Values, and Filters.
2. Rows and Columns group, Values aggregates, Filters narrows. Each cell is a group-by of its row and column labels.
3. Right-click a value to switch among Sum, Count, and Average with Summarize Values By, and read the field label to catch Count-instead-of-Sum.
4. Show Values As turns the same numbers into % of Grand Total, % of Row Total, or a running total.
5. Group dates into months or years and numbers into ranges, which needs clean date and number cells.
6. Sort by right-clicking a value and choosing Sort Largest to Smallest.

Next up, [Slicers, Timelines, and Keeping a Pivot Fresh](03-slicers-timelines-and-keeping-a-pivot-fresh.md): point-and-click filters, refreshing, and the formula Excel writes behind your back.


---

# Slicers, Timelines, and Keeping a Pivot Fresh

A pivot is only useful if people can poke at it and trust it. Slicers and timelines turn filtering into clicking, which is why a pivot with slicers makes a decent mini-dashboard. Trust is the other half: the most common pivot bug is a summary that quietly shows yesterday's numbers. This phase fixes both, and defuses the formula that appears when you try to reference a pivot cell.

## Slicers: filter buttons you can see

A slicer is a panel of buttons, one per value of a field. Click a button and the pivot shows only that value. Unlike the Filters dropdown, a slicer shows at a glance what is selected.

1. Click any cell in the pivot.
2. Choose **Insert > Slicer** (the PivotTable Analyze tab has an **Insert Slicer** button too).
3. Tick **Region** and select **OK**.

A box with buttons East, North, and West appears. Click East and the pivot shows only East rows. Hold Ctrl and click West as well, and the pivot shows both: 13200 + 6900 = 20100 in total revenue. The funnel icon with an X at the top of the slicer clears the filter.

You can share one slicer across several pivots, as long as they use the same data source: select the slicer, open the **Slicer** tab, choose **Report Connections**, and tick the pivots to control. 

## Timelines: a slicer built for dates

A timeline is a slicer for a date field. It gives you a slider you can drag across time instead of a row of buttons.

1. Click a cell in the pivot.
2. Choose **PivotTable Analyze > Insert Timeline**.
3. Tick **Date** and select **OK**.

The timeline needs a field of real dates. Use the dropdown on its right edge to switch the time level among Years, Quarters, Months, and Days. At Months, click the February tile and the pivot shows only February: Sum of Revenue 11400. Drag across two tiles to select a range, such as February through March: 11400 + 9300 = 20700.

## The "my pivot didn't update" gotcha

Here is the trap. A pivot builds its summary from a snapshot of your data (Excel calls it the pivot cache), held inside the workbook. Classically it re-reads the source only when you tell it to, so if you edit a number or add a sale, the pivot keeps showing the old total. Newer builds can refresh some pivots for you (see Auto Refresh below), but you cannot count on it.

Suppose the Table gets a new row:

| Date | Region | Product | Units | Revenue |
|---|---|---|---|---|
| 2026-03-31 | North | Monitor | 4 | 1200 |

The Table grows to 13 rows. The pivot still says North 5400 and Grand Total 25500. Now refresh. Right-click anywhere in the pivot and choose **Refresh**, or select the pivot and choose **PivotTable Analyze > Refresh**, or press Alt+F5. The pivot rebuilds:

| Region | Sum of Revenue |
|---|---|
| East | 13200 |
| North | 6600 |
| West | 6900 |
| Grand Total | 26700 |

North is 5400 + 1200 = 6600, and the total is 25500 + 1200 = 26700.

A few more details to know:

- **Several pivots at once.** Choose the arrow under **Refresh** on the PivotTable Analyze tab and pick **Refresh All**.
- **Refresh on open.** Select the pivot, choose **PivotTable Analyze > Options**, and on the **Data** tab of the dialog, tick **Refresh data when opening the file**.
- **Auto Refresh.** Newer Microsoft 365 builds have an Auto Refresh setting (**PivotTable Analyze > Auto Refresh**) that updates pivots built on data in the same workbook. It does not cover external or Power Query sources, and whether you have it depends on your build and Excel version. Do not rely on it. Make refreshing a habit and check the number.
- **Keyboard.** Alt+F5 refreshes the selected pivot. Ctrl+Alt+F5 refreshes everything in the workbook.
- **Plain range as source.** If the pivot was built from an ordinary range and not a Table, new rows below the range are not part of it, and Refresh will not find them. Select the pivot and choose **PivotTable Analyze > Change Data Source** to widen the range. This is the second reason to use a Table.
- **Column widths jumping.** In the same options dialog, on the **Layout & Format** tab, untick **Autofit column widths on update** to keep your widths, and tick **Preserve cell formatting on update** to keep your formatting.

> ⚠️ **Gotcha**
> Refresh never changes your source, and it cannot see data outside the source range. "My pivot is missing rows" means either you have not refreshed, or the new rows sit outside the range the pivot reads.

## The GETPIVOTDATA surprise

You build a pivot, then in a cell outside it you type `=` and click the cell showing East's total of 13200, expecting `=B4`. Instead Excel writes something like this:

```text
=GETPIVOTDATA("Revenue",$A$3,"Region","East")
```

That is not a bug. GETPIVOTDATA asks the pivot for a value by name: the field to read (`"Revenue"`), any cell inside the pivot (`$A$3`), and then field and item pairs (`"Region","East"`). Excel writes it for you whenever you click a pivot cell while building a formula.

It is actually safer than `=B4`. If you re-sort or rearrange the pivot, East's total moves to another cell, but GETPIVOTDATA still finds "East" by name. The surprise comes when you drag the formula down expecting it to move through the rows. It keeps asking for East, because "East" is written into the formula. And if the item is not visible in the pivot (filtered out by a slicer, for example), the formula returns a #REF! error.

You have two choices:

- **Keep it** when you want a stable link to a named value.
- **Turn it off** to get plain cell references: select a cell in the pivot, choose **PivotTable Analyze**, open the **Options** menu in the PivotTable group, and untick **Generate GetPivotData**. Alternatively, type the cell address instead of clicking.

## Your turn: predict the pivot

A colleague adds two sales rows to the source Table, then looks at the pivot, which has not been refreshed. Does the Grand Total change? No. The pivot holds its earlier snapshot until it is refreshed. After Refresh, the Grand Total includes both new rows.

Check yourself before moving on:

```quiz
[
  {
    "q": "You added rows to the source Table and the pivot still shows the old totals. What is the first thing to try?",
    "choices": ["Rebuild the pivot from scratch", "Right-click the pivot and choose Refresh", "Re-sort the Region field"],
    "answer": 1,
    "explain": "A pivot works from a snapshot and re-reads the source when refreshed. Alt+F5 or right-click Refresh is the usual fix."
  },
  {
    "q": "Which statement about slicers is correct?",
    "choices": ["A slicer shows a button for each value of a field and filters the pivot when clicked", "A slicer changes the source data", "A slicer can only be used on date fields"],
    "answer": 0,
    "explain": "Slicers are filter buttons. They never edit the source data, and timelines, not slicers, are the date-specific control."
  },
  {
    "q": "Excel wrote a GETPIVOTDATA formula when you clicked a pivot cell. Which is true?",
    "choices": ["It is a bug caused by a corrupted pivot", "It reads a value by field and item name, and you can turn this off with Generate GetPivotData", "It always returns the Grand Total"],
    "answer": 1,
    "explain": "GETPIVOTDATA looks up a pivot value by name, and the Generate GetPivotData option controls whether Excel writes it automatically."
  }
]
```

## Recap

1. A slicer is a clickable filter panel for one field. Hold Ctrl to pick several values, and use Report Connections to share it between pivots on the same source.
2. A timeline is a slicer for a real date field, with Years, Quarters, Months, and Days levels.
3. A pivot is a snapshot. After the source changes, Refresh (right-click, or Alt+F5) rebuilds it. Do not count on Auto Refresh being present.
4. A pivot built on a plain range misses rows outside it. Use a Table, or Change Data Source.
5. Clicking a pivot cell in a formula writes GETPIVOTDATA, which looks values up by name. Turn it off with Generate GetPivotData if you want plain references.

Next up, [Charts That Tell the Truth](04-charts-that-tell-the-truth.md): picking the right chart for the question, PivotCharts, and the axis tricks that mislead.


---

# Charts That Tell the Truth

A chart is a claim. "East is much bigger than West" is a sentence, and the chart is that sentence drawn so it hits in a second. The danger is that a chart can say something louder than the numbers do, and Excel will happily draw the louder version if you let it. This phase is about choosing a chart that matches your question and checking the few settings that decide whether it is straight with the reader.

## Pick the chart for the question

Start with what you are asking, not with what looks nice:

| Your question | Chart | Why |
|---|---|---|
| Which category is bigger? | **Column** (or **bar** when labels are long) | Length from a common baseline is what the eye compares most accurately |
| How did it change over time? | **Line** (or columns for a few periods) | A line connects ordered points and shows direction |
| What share of a whole is each part? | **Pie**, only for a few parts | Slices are hard to compare, so use it only when the message is "this part is most of the whole" |

Our data gives one example of each:

| Question | Pivot | Chart |
|---|---|---|
| Which region leads? | East 13200, West 6900, North 5400 | Column, sorted largest to smallest |
| How did revenue move? | Jan 4800, Feb 11400, Mar 9300 | Line or column, months in order |
| How much is laptops? | Laptop 19200 of 25500 (75.3%), Monitor 6300 (24.7%) | Pie works with two slices |

Keep months in calendar order, not sorted by size, since time has a natural order. Sort category charts by value so the biggest bar comes first.

Pie charts have hard limits. Microsoft's guidance is that a pie shows one data series, cannot show negative values, and works best with no more than about seven categories that are all parts of one whole. Past that, switch to a sorted bar chart.

## PivotCharts: a chart tied to the pivot

A normal chart points at cells. A **PivotChart** points at a pivot, so it reshapes whenever the pivot does. Filter the pivot, click a slicer, or drag a field, and the chart follows.

To make one on Windows, click a cell in the pivot and choose **PivotTable Analyze > PivotChart**, then pick a chart type and select **OK**. You can also use **Insert > PivotChart** from a cell in your data. On Excel for Mac and Excel for the web, build the pivot first, then insert a chart from the **Insert** tab. It acts as a PivotChart when you change fields in the pivot field list.

Three things to know:

- **Not every type works.** Microsoft's documentation says treemap, statistical, and combo charts do not work with PivotTables. Column, line, pie, and radar do. If you need an unsupported type, copy the pivot values to cells and chart those, remembering they will not refresh.
- **Pair it with a slicer.** One slicer drives both the pivot and the chart, which turns your sheet into a clickable mini-dashboard.
- **The chart shows what the pivot shows.** A Grand Total row or a filtered pivot appears in the chart. Check the pivot before you trust the picture.

## Trap 1: the truncated axis

Column and bar charts show a number as the length of a bar. That only works if the bars start at zero. Excel chooses the axis range automatically unless you set it, so always look at where the value axis starts.

Take East 13200 and West 6900. With the axis starting at 0, East's bar is about 1.9 times West's, which matches the data (13200 / 6900 = 1.91). Now set the axis minimum to 6000. East's bar rises 7200 above the baseline and West's only 900:

| Axis minimum | East bar height | West bar height | East looks like |
|---|---|---|---|
| 0 | 13200 | 6900 | 1.9 times West |
| 6000 | 7200 | 900 | 8 times West |

Same data, a very different story. To check or change it, select the vertical axis, choose **Format > Format Selection**, open **Axis Options**, and look at the **Minimum** box. **Reset** returns it to Excel's automatic choice. The rule for columns and bars: start at 0. Line charts encode value by position and slope, so a zoomed axis is sometimes reasonable, but then say so on the chart.

## Trap 2: pies that cannot be read

A pie with ten slices of similar size cannot be compared by eye, and one with negative values cannot be drawn at all. Two slices (laptops 75%, monitors 25%) read fine. Four slices at 28%, 26%, 24%, and 22% do not. When the point is a comparison, use bars.

## Trap 3: two axes that invent a relationship

Excel can plot one series on a second vertical axis with its own scale. Slide that scale and you can make two unrelated series seem to rise together. Be careful with this and label both axes. If the point is how two measures relate, show them as two separate small charts stacked on the same time scale.

## Trap 4: an unfinished period

If you chart revenue by month on the 10th, the current month is one-third full and looks like a collapse. Our March has data through the 30th, so it is complete. Exclude partial periods, or mark them clearly.

## Trap 5: decoration that distorts

3D columns make the front bars look bigger than the back ones. A column's top edge sits at a different visual height from its value. Use flat 2D charts. Skip gradients and picture fills that add no information.

> 💡 **Key point**
> A chart's job is to show the pattern the data really has. Ask before sharing: if someone read only this picture, would they leave with the same conclusion as someone who read every number? For the wider habit of questioning metrics, see [Metrics That Lie](/guides/metrics-that-lie), and for putting charts into one view people use daily, see [BI Dashboards That Work](/guides/bi-dashboards-that-work) and [Power BI From Zero](/guides/power-bi-from-zero).

## Your turn: audit a chart

A teammate sends a column chart of region revenue with the axis starting at 6000 and a title "East dominates". The chart makes East look 8 times West. What do you tell them? Reset the axis minimum to 0. Now East is 1.9 times West, which is still a clear lead and a true one. You lose none of the message and keep the reader's trust.

Check yourself before moving on:

```quiz
[
  {
    "q": "Which chart type best answers 'how did revenue change from January through March'?",
    "choices": ["Pie chart", "Line chart with months in calendar order", "Column chart sorted from largest to smallest month"],
    "answer": 1,
    "explain": "Time has a natural order, so a line (or columns) in calendar order shows the direction of change. Sorting by size destroys the order."
  },
  {
    "q": "East is 13200 and West is 6900. If a column chart's value axis starts at 6000, how do the bars compare?",
    "choices": ["East's bar is about 1.9 times West's", "East's bar is 8 times West's, exaggerating the gap", "The bars are equal"],
    "answer": 1,
    "explain": "The visible heights are 13200 minus 6000 equals 7200, and 6900 minus 6000 equals 900. 7200 divided by 900 is 8."
  },
  {
    "q": "Which statement about PivotCharts is correct?",
    "choices": ["They are tied to a pivot, so filtering or using a slicer on the pivot changes the chart", "They support every chart type including treemap and combo", "They keep their values after the pivot is deleted"],
    "answer": 0,
    "explain": "A PivotChart follows its PivotTable. Microsoft documents that treemap, statistical, and combo charts are not supported."
  }
]
```

## Recap

1. Choose the chart from the question: columns or bars to compare, lines for change over time, pie only for a few parts of one whole.
2. A PivotChart follows its pivot, so slicers and filters change it. Supported types include column, line, pie, and radar, but not treemap, statistical, or combo.
3. Columns and bars must start at zero. Check the axis Minimum under Format Selection, Axis Options.
4. Avoid crowded pies, unlabeled dual axes, unfinished periods, and 3D effects.
5. Before you share a chart, ask whether someone looking only at the picture would reach the same conclusion as someone reading the numbers.

You have the full path now: shape the data, build the pivot, keep it fresh, and chart it straight. For formulas that pull from your data, go to [Excel Formulas That Do Real Work](/guides/excel-formulas-that-do-real-work), and for data models and Power Query, [Advanced Excel](/guides/advanced-excel).