Excel Tutorials
Slicers turn a PivotTable into something anyone can filter without knowing how PivotTables work, and Show Values As turns raw numbers into shares of the total without a single formula. Together they are how a plain summary becomes a dashboard. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
A slicer is a visual filter: a floating box of clickable buttons, one for each value in a field. Click Northeast and the connected Table, PivotTable and charts all filter to Northeast instantly. Functionally it does the same thing as the filter dropdowns, but with one difference that matters enormously on a shared report, the current selection is visible at a glance without anyone opening a menu.
Percent of total is a PivotTable setting rather than a formula. Add a value field to your PivotTable a second time, right-click it and use Show Values As, and Excel recalculates that column as each row's share of the total. No division, no absolute references, no $B$6 to get wrong.
The reason they belong on the same page is what happens when you combine them. Because Show Values As always calculates against whatever data is currently visible, clicking a slicer recalculates every percentage against the new filtered total. A hard-coded formula cannot do that, which is exactly why hand-built percentage columns start reporting nonsense the moment someone applies a filter.
Step-by-step, matching the video above.
Slicers only attach to an Excel Table or a PivotTable. Select any cell in your data and press Ctrl+T to convert a plain range into a Table, confirming that your data has headers.
With a cell inside the Table or PivotTable selected, go to Insert > Slicer (or the Table Design / PivotTable Analyze tab) and tick the fields you want to filter by, such as Region, Category or Event Type.
Each slicer button is one value from that field. Clicking it filters the Table or PivotTable instantly, and every connected chart updates with it.
Hold Ctrl and click to add values to the selection, or click the multi-select icon in the slicer header to toggle values on and off without holding anything down.
The funnel-with-a-cross icon in the top right of the slicer clears it, as does Alt+C while the slicer is selected. Greyed-out buttons mean that value has no data under the current filters.
On the Slicer tab, set Columns to lay buttons out in a grid rather than a long single column, and use Size to fit the slicer neatly beside your report.
Right-click the slicer and choose Report Connections, then tick every PivotTable it should control. One click then filters your whole dashboard at once instead of each table separately.
Right-click, choose Size and Properties, and set it not to move or size with cells so the layout survives people filtering, sorting and resizing columns.
You do not need a formula for this. Excel calculates it from a value field you have already added.
Drag your value field, say Amount, into the Values area a second time. You now have one column showing the raw number and a second one ready to become the percentage.
Right-click any cell in that second column and choose Show Values As. This is where Excel turns a raw total into a comparison against something else.
Each row now shows its share of the overall total, and the column adds up to 100%. This is the standard percent-of-total view.
% of Column Total makes each column sum to 100%, which is what you want when comparing the internal mix of one region against another. % of Row Total does the same across each row.
When you have two fields stacked in Rows, this shows each item's share of its own subtotal rather than the grand total, which is usually the more meaningful number in a hierarchy.
Excel labels it something like Sum of Amount2. Click the header and type a proper name such as % of Total. It cannot be the exact name of the source field, so add a space if Excel objects.
| Category | Amount | % of Grand Total |
|---|---|---|
| Venue | 4,500 | 45.0% |
| Catering | 3,000 | 30.0% |
| Marketing | 1,500 | 15.0% |
| Staffing | 1,000 | 10.0% |
| Grand Total | 10,000 | 100.0% |
The third column is the same Amount field added to Values a second time with Show Values As > % of Grand Total applied, not a formula. That distinction matters the moment you click a slicer: filter this PivotTable to a single region and both the amounts and the percentages recalculate against the filtered total, so the column still sums to 100%. A hard-coded =B2/$B$6 formula would keep dividing by the old unfiltered total and quietly report the wrong share.
This is the combination that makes a one-page dashboard genuinely useful.
Because Show Values As recalculates against the visible data, a slicer click reframes every percentage in the report around whatever subset you selected. That is the behaviour you want and the reason not to hard-code the divisor.
Keeping both columns visible answers two questions at once: how big is this, and how big is it relative to everything else. Percentages alone hide the fact that a 40% share might be 40% of very little.
Insert > Timeline gives you a slider for date fields, letting people scrub through months or quarters. It works alongside slicers and honours the same Report Connections.
Once a slicer excludes a category, it no longer contributes to the total, so the remaining percentages get larger. That is mathematically correct but worth labelling clearly so nobody misreads a filtered view as the full picture.
Insert > Slicer is greyed out on ordinary cells. Press Ctrl+T to convert the range into a Table first, or build a PivotTable from it.
A formula like =B2/$B$10 keeps dividing by the unfiltered total, so the percentages stop adding to 100% as soon as a slicer is clicked. Use Show Values As so Excel recalculates against the visible data.
A slicer only controls the PivotTable it was created from until you right-click it and tick the others. Half-filtered dashboards, where one chart updates and the rest do not, are almost always this.
With grouped data, % of Grand Total measures every cell against one overall figure. If you want each region's internal mix to sum to 100%, you need % of Column Total instead.
A greyed button means that value has no data under the current filter combination, not that it is broken. Clear the other slicers and it becomes available again.
By default slicers shift and resize along with the cells underneath them. Set Size and Properties to don't move or size with cells so your layout holds together.
Leaving the header as Sum of Amount2 makes the report look unfinished and unclear. Click the header and type a real name like % of Total.
A slicer is a great filter. It is a lot of work when the dashboard could just already be there.
Free 14-day trial. No credit card required.
A slicer is a visual filter made of clickable buttons that filters a Table or PivotTable. Unlike a filter dropdown, it shows the current selection on screen without anyone needing to open a menu.
Slicers only work on Excel Tables and PivotTables. Select a cell in your data and press Ctrl+T to convert the range into a Table, and the option becomes available.
Hold Ctrl while clicking, or click the multi-select icon in the slicer header, which lets you toggle values on and off with single clicks.
Click the clear filter icon in the top right corner of the slicer, or press Alt+C while the slicer is selected.
Those values have no matching data under the filters currently applied by your other slicers. Clearing the other filters makes them selectable again.
Right-click the slicer, choose Report Connections, and tick every PivotTable it should filter. One click then updates the whole dashboard.
Add the value field to the Values area a second time, right-click a cell in that column, choose Show Values As, and pick % of Grand Total.
% of Grand Total measures every value against one overall total so the whole table sums to 100%. % of Column Total makes each individual column sum to 100%, which is better for comparing the internal mix of each group.
When you have nested fields in the Rows area and want each item shown as a share of its own subtotal rather than the grand total.
Yes. Show Values As recalculates against the currently visible data, so filtering with a slicer reframes every percentage around the new filtered total.
A slicer filters any field using buttons. A timeline is a slider designed specifically for date fields, letting you filter by day, month, quarter or year.
Yes. Any range converted to a Table with Ctrl+T supports slicers, though Show Values As percentages require a PivotTable.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →