The workbook is called Sales_Dashboard.xlsx. One sheet holds twelve months of order lines, one holds six PivotTables, and on top of them sits a single Region slicer with six buttons on it. Click North and the dashboard becomes the North dashboard. That is the entire promise of a slicer, and it is the reason a slicer is on the sheet instead of six separate filter dropdowns nobody would keep in step.
On the morning the North expansion was signed off, the dashboard reported this:
| On the dashboard | The truth for North | |
|---|---|---|
| Revenue | 412,860 | 412,860 |
| Orders | 1,284 | 1,284 |
| Average order value | 321.54 | 321.54 |
| Gross margin % | 31.16% | 22.69% |
| Credit notes | 55,145 | 28,940 |
Three tiles were right, two were wrong, and every one of them sat under a slicer with North highlighted in brand green. The Region slicer was connected to four of the six PivotTables on the sheet. The other two — margin and credit notes — had been added a fortnight later, from a second PivotTable built directly off the source range, and nobody opened Report Connections afterwards, because nothing about the sheet suggests there is a dialog to open.
So the margin tile did what it had been asked to do all along: report the gross margin of the whole company, 516,480 on 1,657,710, which is 31.16%. Beside a revenue tile reading 412,860, it read as North's margin. North's margin was 22.69%. The gap is 8.47 percentage points, and against North's own revenue it is 34,951.63 of gross profit that was never there.
What this covers. Slicers are Excel 2010 and later for PivotTables, Excel 2013 and later for Tables; timelines are Excel 2013 and later and need a genuine date field. Everything else here —
SUMIFS,COUNTIFS,SUMPRODUCT,SUBTOTAL,AGGREGATE,GETPIVOTDATA,INDEX/MATCH,IFERROR— works in every version this decade, andXLOOKUP,LET,UNIQUE,FILTERandSORTneed 365 or 2021. Excel for the web displays and operates slicers and timelines, and can create them on Tables and PivotTables in current builds. Excel for Mac has all of it. The example is a sales dashboard because that is where slicers live, but the failure is the same on a plant-availability board, a headcount pack or a KPI sheet: a control that looks like it governs a sheet and in fact governs a list.
1) Six Tiles, Four Filtered
Here is the month, by region, as the order lines actually add up. Every figure quoted anywhere in this article comes out of this table.
Six Regions, Six Tiles, Two of Them Reporting the Bottom Row
The month behind the dashboard. Rows 2 to 7 are the six regions as the order lines actually add up; row 8 is the company. With North selected on the slicer, the revenue, orders and average-order-value tiles reported the North row — correctly — while the margin and credit-notes tiles reported the All regions row, because those two PivotTables were never in the slicer's connection list. That is the entire failure: 31.16% and 55,145 read beside 412,860 and 1,284 as though all five described the same region. North's own margin is 22.69% and its own credit notes are 28,940, which is 7.01% of its revenue and 52.48% of the company's returns in a month where no other region exceeded 2.23%. Every figure in the article is computed from this table.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Read down the margin column and North is visibly the odd region: 22.69% where every other region sits between 33.40% and 35.00%. Read down the credit notes column and North is worse than odd — 28,940 of returns against 412,860 of revenue is 7.01%, where nowhere else exceeds 2.23%. North is a quarter of the company's revenue and 52.48% of its credit notes.
None of that was hidden. It was on the dashboard, in two tiles, in a slicer-shaped sheet, in aggregate form, all month.
🎯 Scenario: Before trusting a filtered dashboard, pick the one number you would expect to move most when the filter changes — a rate, a ratio, a percentage — and watch it as you click through every slicer button. A revenue tile that changes proves the slicer works. Only a tile that changes proves that tile is connected.
2) What a Slicer Actually Is
A slicer is not a sheet-level filter. It is a visual filter control bound to a specific set of PivotTables, PivotCharts or Tables, and the binding is a list, maintained by hand, that lives in a dialog most people have never opened.
That is the whole article in one sentence, so it is worth being precise about the three kinds:
| Slicer on | Filters | Created from | Affects worksheet formulas? |
|---|---|---|---|
| A PivotTable | every PivotTable in its connection list sharing the same cache | PivotTable Analyze ▸ Insert Slicer | No |
A Table (Ctrl+T) | the rows of that one Table, exactly like an AutoFilter | Table Design ▸ Insert Slicer | Only SUBTOTAL / AGGREGATE |
| The Data Model | every PivotTable built on that model, through the relationships | any model PivotTable, or Power Pivot | No |
The second row is the one that surprises people who have only ever used the first. A Table slicer is an AutoFilter with buttons — it hides rows. A PivotTable slicer hides nothing; it changes what the PivotTable summarises.
🎯 Scenario: Ask of every control on a dashboard: what is the list of objects this thing is attached to, and where is that list written down? If the answer is "it's just there", the sheet has a control whose behaviour nobody has checked, and a control nobody has checked is a control that is probably wrong by now.
3) Why Nothing Looked Wrong
A slicer has exactly one state indicator, and it is on the slicer.
Click North and the North button highlights. That highlight is a statement about the slicer, not about the sheet: it says this slicer's selection is North. There is no tick on a connected tile, no mark on an unconnected one, no colour, no border, no warning, nothing in the status bar. A PivotTable filtered to one region and a PivotTable summarising six regions are, visually, the same object.
The one honest signal Excel gives you is the filter funnel on the PivotTable's own Row or Filter field — and it appears only if the field is in the PivotTable's layout, which on a single-number tile it usually is not. A tile built as one cell of Sum of Revenue with nothing in Rows has nowhere to put a funnel.
So the dashboard's two lies were told in the same typeface as its three truths, under a control that was genuinely, verifiably working.
🎯 Scenario: The fastest way to see the connection list is to right-click the slicer and choose Report Connections (PivotTable Connections in Excel 2010). Every tick is a PivotTable this slicer filters; every unticked line is a PivotTable it does not. On this dashboard four boxes were ticked and two were not, and that dialog is the only place in the entire workbook where that fact was written down.
4) The Number That Was Right and Wrong at the Same Time
The margin tile was not broken. It was a correct, well-built PivotTable reporting the correct answer to the question it had been asked, which was "what is the gross margin of everything in the source range?"
Gross margin % = 516,480 / 1,657,710 = 31.16% ← what the tile said, all month, every month
North's margin = 93,680 / 412,860 = 22.69% ← what the reader believed it said
This is the specific danger of a partially connected dashboard, and it is worse than a broken formula. A #REF! announces itself. A blank tile announces itself. A tile that stops responding to a filter goes on producing a number that is plausible, stable, recalculating correctly, and of a different population than the tile beside it.
The business consequence follows arithmetically. Applied to North's revenue, the company rate implies gross profit of:
412,860 × 31.16% = 128,631.63
412,860 × 22.69% = 93,680.00 ← actual
───────────
Overstatement 34,951.63
Two additional field sales roles were approved against the first number.
🎯 Scenario: Any ratio on a dashboard — margin %, conversion %, defect rate, utilisation — is a fraction with a numerator and a denominator, and a partially connected slicer will happily give you one of them filtered and the other not. When a rate refuses to move while the volumes underneath it move, that is not stability. That is a disconnected tile.
5) Report Connections, the Dialog That Decides Everything
There are two doors into the same list, which is part of why neither gets opened:
- From the slicer: right-click it ▸ Report Connections (Excel 2013 and later; it is called PivotTable Connections in 2010). You see every PivotTable the slicer could drive, with a tick against each one it does.
- From the PivotTable: PivotTable Analyze ▸ Filter Connections. You see every slicer and timeline that could drive this PivotTable, with a tick against each one that does.
Both dialogs list only the candidates — and the candidate rule is the thing that catches people:
A slicer can connect to another PivotTable only if that PivotTable shares its PivotCache (or is built on the same Data Model).
Two PivotTables built one after the other off the same worksheet range do not necessarily share a cache. In current Excel, inserting a second PivotTable from the same range usually reuses the existing cache; choosing Add this data to the Data Model, pointing at a slightly different range, or building it from a different Table does not. When the caches differ, the PivotTable simply does not appear in Report Connections at all. Not greyed out. Absent.
That absence is the failure mode on this dashboard. The person who added the margin tile a fortnight later did open Report Connections, found four PivotTables listed and all four already ticked, concluded that everything was connected, and closed it. The tile they had just built was not in the list to be ticked.
🎯 Scenario: Count the tiles on the sheet, then count the lines in Report Connections. If the dialog lists fewer PivotTables than the dashboard shows, the missing ones are on a different cache and no amount of ticking will ever reach them — they need rebuilding, not reconnecting.
6) The Cache Rule, and the Three Ways Out
Once you know two tiles are on different caches, there are exactly three honest fixes.
1. Rebuild the stray PivotTable as a copy. Select an already-connected PivotTable, copy it (Ctrl+C), paste it where the stray one was, and reshape the copy into the tile you wanted. A pasted PivotTable inherits the cache it was copied from, so it appears in Report Connections immediately. This is the fastest fix and the one worth reaching for first.
2. Put everything on one source and one cache. Convert the source range to a Table (Ctrl+T), then build every PivotTable from that Table. Same source, same cache, and a Table grows when rows are appended, which fixes the other half of the dashboard problem nobody mentions.
3. Move the dashboard to the Data Model. Tick Add this data to the Data Model when creating the PivotTables, and slicers connect across every model PivotTable — including across multiple related tables, which the cache route cannot do at all. This is the right answer for a dashboard that reports on orders, returns and targets from three separate lists.
What is not a fix is adding a second slicer for the same field and pointing it at the stray tile. Two Region slicers on one sheet can hold two different selections — North on one, All on the other — and the sheet will show you both without comment. That is the original bug with a second control added to make it harder to see.
🎯 Scenario: A dashboard should have exactly one control per field. If you find two slicers on the same field, do not tidy them by aligning their selections; find out why the second one exists, because it almost certainly exists to reach a tile the first one could not.
7) Timelines: The Slicer That Only Speaks Dates
A timeline is a slicer for a date field, with a scrollbar and a granularity switch — Years, Quarters, Months, Days. It filters the same way through the same Report Connections list, and it fails in one additional way that costs an afternoon every time.
Insert ▸ Timeline on a PivotTable whose dates are text gives you:
We can't create a Timeline for this report because it doesn't have a field formatted as Date.
The dates look like dates. They came out of an export as 01/03/2026, left-aligned, as text — and a text date is invisible to a timeline, to grouping, to EOMONTH and to every date function in the file. The fix belongs at import: type the column as Date in Power Query, or convert with =DATEVALUE(...) and paste the values back, then refresh. There is no timeline setting that reads text.
Two more things about timelines worth knowing before you put one on a board:
- A timeline is always a contiguous range. Start and end, nothing else. "March and October but not the six months between" is a slicer's job, not a timeline's.
- The granularity switch changes what the filter means, not just how it looks. Set to Months and drag across one block and you have selected the whole month, including days nobody has entered yet — which is exactly right for a month-to-date tile and exactly wrong beside a tile that stops at yesterday.
🎯 Scenario: =SUMPRODUCT(--ISTEXT(A2:A5155)) on the date column answers the timeline question in two seconds, and answers several other questions at the same time. Anything other than 0 means the column is not dates, whatever it looks like, and every date-shaped thing in the workbook is already lying.
8) Slicers on Tables, and the Formula That Ignores Them
Put a slicer on a Table and it filters rows — genuinely hides them, exactly as the AutoFilter dropdowns do. Which raises the question that costs more money than anything else in this article:
What happens to the formulas beside the Table?
Nothing. Absolutely nothing.
=SUM(Orders[Revenue]) → 1,657,710 ← ignores the slicer, always
=SUBTOTAL(109, Orders[Revenue]) → 412,860 ← respects the slicer
=AGGREGATE(9, 5, Orders[Revenue]) → 412,860 ← respects the slicer
=SUMIFS(Orders[Revenue], Orders[Region], "North") → 412,860 ← respects the CELL, not the slicer
SUM, AVERAGE, COUNT, SUMIFS, COUNTIFS, SUMPRODUCT, XLOOKUP, INDEX/MATCH — none of them know a filter exists. They read the range. A summary block built from SUM sitting above a slicer-filtered Table is a permanent, silent, whole-population number wearing a filtered dashboard's clothes, and it is the Table-side version of exactly the bug this article opened with.
SUBTOTAL and AGGREGATE are the two exceptions, and they respond to filtering. SUBTOTAL(109, …) and AGGREGATE(9, 5, …) additionally exclude manually hidden rows; SUBTOTAL(9, …) and AGGREGATE(9, 3, …) do not. A Table's own Total Row uses SUBTOTAL, which is why the Total Row moves with a slicer and a hand-written SUM two cells above it does not.
🎯 Scenario: If a summary must follow a slicer, it is SUBTOTAL or AGGREGATE or a PivotTable — there is no fourth option. If a summary must not follow the slicer, say so in the label: "Company total (unfiltered)" costs eleven characters and removes the entire ambiguity.
9) The Other Way a Margin Tile Lies: Averaging Percentages
Suppose every tile on this dashboard had been connected properly. There is a second, independent way the margin number goes wrong, and it is in the PivotTable's own field settings.
Drop a pre-calculated Margin % column into a PivotTable and set it to Average, and Excel averages six percentages:
(22.69 + 34.01 + 33.40 + 34.00 + 35.00 + 33.82) / 6 = 32.15% ← wrong
516,480 / 1,657,710 = 31.16% ← right
Both are averages of the same six regions. They differ by 0.99 points because the first one gives Scotland's 143,720 of revenue the same vote as North's 412,860. A percentage is a ratio, and ratios do not average — they have to be recomputed from their parts at every level of the report.
The fix inside a PivotTable is a calculated field: Margin % = Margin / Revenue, which Excel evaluates as SUM(Margin) / SUM(Revenue) at each cell, giving 31.16% at the total and 22.69% at North, both correct. Outside a PivotTable, the same discipline is =SUMIFS(margin,…)/SUMIFS(revenue,…), never =AVERAGE(pct_column).
🎯 Scenario: Any percentage in a report should be traceable to a division you can point at. If the only place a rate exists is a column of rates that got averaged, the total is wrong by an amount that depends on how uneven the rows are — which means it is smallest in the test data and largest in the real data.
10) Making the Dashboard State Its Own Filter
The whole failure is invisible because the sheet never says out loud what it is showing. Three cheap habits fix that permanently, and none of them takes longer than the meeting that discovers the problem.
Put the selection in the title, from the data. A one-cell helper PivotTable on the same cache, or a GETPIVOTDATA against a connected tile, gives you a value that moves with the slicer:
="Regional performance — " & IF(COUNTA(visible_regions)=6, "All regions", TEXTJOIN(", ", TRUE, visible_regions))
If the title still reads "All regions" while a slicer button is lit, the title's source is not connected — and a wrong title is a great deal easier to notice than a right-looking margin.
Cross-check one tile with a formula that cannot be filtered. Put =SUMIFS(Orders[Revenue], Orders[Region], $B$1) beside the dashboard, where B1 holds the region you believe you selected, and compare it with the revenue tile. Two numbers that agree mean the tile is filtered to the region you think it is. This is the check that would have caught the margin tile on day one, because the margin equivalent — =SUMIFS(margin…)/SUMIFS(revenue…) — returns 22.69% against a tile showing 31.16%.
Count the connections whenever a tile is added. Adding a tile is the only event that breaks this. Make the last step of adding one "open Report Connections and confirm the count", and the failure cannot survive to month end.
🎯 Scenario: A dashboard that cannot tell you what it is filtered to is not a dashboard, it is a picture. The label is not decoration — it is the only part of the sheet capable of contradicting the tiles.
11) Five Checks
1. Does every tile move when the slicer moves?
Click through all six buttons and watch every tile. Any tile whose value never changes is either disconnected or genuinely constant, and there is no third possibility. On this dashboard, two tiles never moved across six clicks.
2. Does Report Connections list as many PivotTables as the sheet shows?
Tiles on the sheet: 6 Lines in Report Connections: 4
Fewer lines than tiles means a second cache, which means reconnecting is impossible and rebuilding is required.
3. Do the rates agree with a formula that ignores the slicer?
=SUMIFS(margin, region, $B$1) / SUMIFS(revenue, region, $B$1) → 22.69%
Margin tile → 31.16%
Any disagreement is a disconnected tile, a different filter, or an averaged percentage — all three of which are in this article.
4. Is the date column actually dates?
=SUMPRODUCT(--ISTEXT(date_column)) → 0 is the only acceptable answer
Non-zero means no timeline, no grouping, and every date calculation in the workbook is suspect.
5. What was the file saved with selected?
A slicer selection is saved in the workbook. Whoever opens it next sees the filter the last person left, with no prompt and no reset. Clear the slicers (Alt+C with the slicer selected) before the final save, so the dashboard opens showing everything, and anybody who wants a region has to choose one deliberately.
12) Twelve Traps
- A slicer filters a list of objects, not a sheet. The list is in Report Connections, it is maintained by hand, and nothing on the dashboard displays it.
- A tile added later is not connected by default. The dashboard's whole failure mode is the tile built a fortnight after the slicer.
- A PivotTable on a different cache is absent from Report Connections, not greyed out. Absence is easy to read as "nothing left to connect".
- Copy an existing PivotTable to create a new one and it shares the cache, so it appears in Report Connections immediately. Building a fresh one from the range may not.
- Two slicers on one field can disagree. The second selection is invisible next to the first, and the sheet will report both populations at once.
- Worksheet formulas ignore slicers completely.
SUM,SUMIFS,COUNTIFS,SUMPRODUCTand every lookup read the whole range, filtered or not. SUBTOTALandAGGREGATEare the exceptions, and only for filtered rows — the10xcodes (109,103) andAGGREGATE's option5also drop manually hidden rows.- A Table's Total Row follows the slicer; a hand-written
SUMabove it does not. Both can be on screen at once, disagreeing, both correct. - A timeline needs a real date field. Text dates produce a refusal dialog, and they break grouping and every date function in the workbook at the same time.
- A timeline can only select a contiguous range. Two separate months is a slicer's job.
- Averaging a column of percentages is not the percentage of the total. Use a calculated field (
Margin / Revenue) in a PivotTable, orSUMIFS/SUMIFSoutside one. - The slicer selection is saved with the file. Clear it before the last save, or the next reader inherits a filter they never chose and never see.
Nobody on this dashboard did anything careless. The person who built it put a single control on the sheet instead of six dropdowns, which is the correct instinct and the reason slicers exist. The person who added the margin tile a fortnight later opened Report Connections, saw four PivotTables with four ticks, and closed a dialog that was telling the truth about everything it listed. The person who read 412,860 beside 31.16% and approved two field roles did what a dashboard is built to let them do: read two numbers side by side and believe they describe the same thing.
What made it expensive is that a slicer is a control that looks like it governs a sheet and in fact governs a list — and the list is kept somewhere the dashboard never shows. Every other broken thing in a workbook eventually announces itself: a bad reference goes #REF!, a stale link prompts on open, a broken lookup goes #N/A in colour. A disconnected tile produces a correct number, of a different population, in the same font, under a button glowing green.
So the discipline is small and it is not about slicers. Make the sheet say what it is filtered to, in words, from the data. Put one formula somewhere on the dashboard that answers the same question by a completely different route, and look at it once a month. Treat adding a tile as the dangerous act it is, because it is the only act that breaks this. And when a rate refuses to move while everything underneath it moves, do not read that as a stable business — read it as a number that is not listening.