Chapter 17 · 17.23
A small dashboard with sparklines and slicers
An overview with twelve months of revenue per product is a wall of numbers. With sparklines, you add a mini chart in a single cell after each row, so you can see the trend at a glance. With a slicer, you get buttons to filter quickly by category. Together they form a small dashboard, without complicated charts.
As an example, we use Bloemisterij Het Zonnetje, with a category (column B) for each product (column A) and the revenue from January to December (columns C to N), in rows 2 to 8.
Step 1: insert sparklines
- Select the cells where the mini charts should go: O2:O8.
- On the Insert tab, click Sparkline. Three images appear: columns, a line and win/loss. Choose the middle one, the line.
- In the Create sparklines window, enter the numbers under Source data range: C2:N8. The location (O2:O8) is already filled in.
- Click OK.


Each product now has a small line showing its trend over the year. Make the column a little wider and the rows a little taller to see more.
Step 2: highlight the highest and lowest month
Click a sparkline. The sparkline settings appear on the right, with options such as High point and Low Point under Show. Tick them and pick a colour if you like. You can see straight away in which month each product peaked. All sparklines you created together form a group and change along with it.

Besides Line, there are two other types. Column shows a small bar for each month. Win/Loss only shows whether a number is positive or negative: handy if you have a helper row with revenue minus target.
Step 3: buttons to filter with a slicer
A slicer works on a formatted table or a pivot table.
- Click in the data and turn it into a table: on the Home tab, choose Format as table template and a style, then click OK (see also A formatted table: column names in formulas, a total row and slicers).
- Click in the table and on the Insert tab, choose Slicer. You can also click Slicer on the Table Design tab.
- Tick Category and click OK.
A box appears with a button for each category. Click a button and the table shows only those products, including their sparklines. At the top of the slicer there is a multi-select button, which lets you turn on several buttons at once, and a button to clear the filter.


Pitfalls
- Deleting a sparkline with Delete does not work: the line simply stays. Right-click the cell and choose Sparklines; there you will find Clear selected sparklines and Clear selected sparkline groups.
- A slicer filters for everyone: if you are working on the file with others, your colleagues will also see only the selected category. If you want to filter just for yourself, use a sheet view (see Filtering without bothering your colleagues).
- You cannot use a slicer on an ordinary list. Turn it into a table first.

