Home>Blogs>Charts and Visualization>Dynamic Variance Arrows Chart with Slicers in Excel
Dynamic Variance Arrows Chart with Slicers in Excel
Charts and Visualization

Dynamic Variance Arrows Chart with Slicers in Excel

A dynamic variance arrows chart with slicers compares this year with last year month by month, draws a green up arrow or a red down arrow over each pair of columns, and lets you pick the market with a stylish slicer instead of check boxes. Click one market, or hold Ctrl and click several, and the columns, arrows and percentage labels all update. This is Part 2 of PK’s variance chart series: you keep the same chart and helper formulas, feed them from a pivot table, connect a Market Name slicer and give it a custom slicer style.

Dynamic variance arrows chart with slicers in Excel showing Germany selected, 2017 and 2018 monthly sales and green and red variance arrows
The finished chart: Germany is selected in the slicer, and each month shows the change from 2017 to 2018.

Watch the Video Tutorial

PK replaces the check boxes from Part 1 with a pivot table and a Market Name slicer, then designs a custom slicer style that blends into the dashboard.

What You Will Build

  • A pivot table that totals sales by month and year for whatever market is selected.
  • VLOOKUP links that pull those totals into the Support sheet that drives the chart.
  • A Market Name slicer laid out in one row of five buttons, without a header.
  • A custom slicer style with a matching background, a bold underlined selected item and no white boxes.
  • The same variance arrows and percentage labels as Part 1, now in navy and light blue.

Before You Start: The Check Box Version

This tutorial builds on the dynamic variance arrows chart with check boxes. That post walks through the chart in detail: the error-bar arrows, the secondary axis and the Value From Cells labels. The Support sheet here uses the same columns and formulas, so here is a quick recap:

ColumnHeaderFormula in row 2
AMonthJan to Dec
B, CYear 2017, Year 2018From the pivot table (Step 3)
DMax Left=MAX(B2:C2)
EMax Right=D2
FUp Error Bar=IF(C2>B2,C2-B2,NA())
GDown Error Bar=IF(C2<B2,B2-C2,NA())
H, IUp / Down Data LabelTEXT of the change divided by 2017, with a plus or minus sign

The chart plots A1:E13 as clustered columns, with Max Left and Max Right on the secondary axis, no fill, and custom minus error bars (green begin arrow and red end arrow) whose sizes come from columns F and G. If you have not built it yet, do Part 1 first, then come back.

The Sales Data

The Data sheet is the same four-column list: Market Name, Year, Month and Sales, with one row for each market, year and month.

Sales data in Excel with Market Name, Year, Month and Sales columns used for the pivot table and slicer
The Data sheet: one row per market, year and month.

Feed the Variance Arrows Chart with Slicers Through a Pivot Table

Step 1: Copy the chart to a new sheet

Insert a new sheet for the slicer version of the dashboard and copy only the chart onto it. The chart still points at the Support sheet, so nothing about the arrows changes.

Step 2: Create the pivot table

On the Support sheet, delete the old 2017 and 2018 formulas in columns B and C. Then select the data on the Data sheet and choose Insert → PivotTable → New Worksheet. In the PivotTable Fields pane:

  • Drag Month to Rows.
  • Drag Year to Columns.
  • Drag Sales to Values (Sum of Sales).

You now have a small grid: months Jan to Dec down column A, the 2017 total in column B and the 2018 total in column C, with a Grand Total on the right. Excel sorts the month names in calendar order because Jan to Dec is a built-in custom list.

Step 3: Pull the totals with VLOOKUP

In Support!B2 enter:

=VLOOKUP(A2,Sheet2!A:C,2,0)

and in Support!C2:

=VLOOKUP(A2,Sheet2!A:C,3,0)

Fill both down to row 13. Here Sheet2 is the sheet that holds the pivot table; use your own sheet name if it is different.

How the formula works

  • A2 is the month on the Support sheet, for example Jan.
  • Sheet2!A:C is the pivot table area: month labels in column A, 2017 in column B and 2018 in column C.
  • The column number 2 returns the 2017 total and 3 returns the 2018 total.
  • The last argument 0 forces an exact match, so Jan only ever matches Jan.

Because the pivot table is filtered by the slicer, these two columns always hold the totals for the selected markets, and every helper column and the chart follow automatically. In PK’s finished file the same pivot table sits on the Support sheet itself, to the right of the helper columns.

Add and Style the Market Slicer

Step 4: Insert the Market Name slicer

Click inside the pivot table, go to PivotTable Analyze (called Analyze or Options in older versions) and choose Insert Slicer. Tick Market Name and click OK. Cut the slicer with Ctrl+X and paste it on the dashboard sheet above the chart.

Step 5: Lay the buttons out in one row

Select the slicer and on the Slicer → Options tab set Columns to 5, so Germany, India, Poland, UK and US sit in a single row. Turn off gridlines on the dashboard sheet with View → Gridlines.

Step 6: Hide the slicer header

Right-click the slicer, choose Slicer Settings and untick Display header. A rectangle with your own caption works better here. Insert a rectangle shape, stretch it across the top of the chart, right-click and choose Send to Back, and give it a light fill colour.

Step 7: Create a custom slicer style

With the slicer selected, open the Slicer Styles gallery, click the More arrow and choose New Slicer Style. Format three elements:

  • Whole Slicer: Format → Fill, the same light colour as the rectangle, so the slicer disappears into the banner.
  • Selected Item with Data: Format → Font, bold, a dark blue colour, size 12 and a strong font such as Arial Black. PK also adds a single underline so the selected market stands out.
  • Unselected Item with Data: leave the font as it is.

Click OK, then open the Slicer Styles gallery again and pick your new style from the Custom group.

Step 8: Remove the white button boxes

The buttons still show a white background. Right-click your custom style in the gallery and choose Modify. Give Selected Item with Data and Unselected Item with Data the same fill colour as the rectangle and click OK. Now the slicer looks like plain text links: the selected market is bold and underlined, the rest are regular.

Step 9: Test it

Click India, then Poland. To compare two or more markets together, hold Ctrl and click each one. The pivot table filters, the VLOOKUPs pick up the new totals, and the arrows and percentages redraw.

Why Use a Slicer Instead of Check Boxes?

PK shows the big benefit at the end of the video. In the Data sheet he renames US to USA, then clicks Data → Refresh All. The slicer button changes to USA on its own, and the chart keeps working.

  • No formula edits for new markets. With check boxes, each market needs its own box, cell link and IF block. With a slicer, a new market becomes a new button after a refresh.
  • Shorter formulas. Two VLOOKUPs replace the long SUMIFS formula from Part 1.
  • Multi-select is built in with Ctrl+Click, and the filter icon on the slicer clears the selection.

The trade-off: a pivot table does not update by itself when the data changes, so remember to refresh.

Modern Excel Alternative

This section is an addition and is not shown in the video. If your data is formatted as an Excel Table (Ctrl+T), you can connect a slicer to the table directly in Excel 2013 and later. The slicer then hides rows instead of filtering a pivot table, and you can total only the visible rows with SUBTOTAL or AGGREGATE. The pivot table method used in the video is simpler for monthly totals and works in Excel 2010, so it is still the recommended route here.

Common Errors and Fixes

The VLOOKUP returns #N/A for some months

The selected market has no rows for that month, so the month is missing from the pivot table. Wrap the formula in IFERROR(…,0), or set the Month field to show items with no data in the pivot’s Field Settings.

The 2017 and 2018 columns are swapped

The VLOOKUP column numbers assume 2017 is the first year column in the pivot table. If you add more years, check the pivot layout and update the column number.

The chart does not change when I click the slicer

The slicer must be connected to the pivot table that the VLOOKUPs read. Right-click the slicer, choose Report Connections and make sure that pivot table is ticked.

Tips

  • Use Data → Refresh All after any change to the source data.
  • Save your slicer style once and reuse it in every dashboard. The guide on custom slicer styles in Excel shows how to set one as the default.
  • Hide the pivot table and the Support sheet once everything works, so users only see the slicer and the chart.
  • Microsoft’s article Use slicers to filter data covers every slicer setting used here.

Want It Ready-Made?

If you want the finished workbook with the pivot table, slicer style and chart already set up, get the Dynamic Variance Arrows Chart with Slicers template on NextGenTemplates.com, or browse more Excel templates. To master pivot tables, slicers and dashboards step by step, see the course Excel Pivot Tables and Dashboards.

Related Tutorials

Frequently Asked Questions

How do I select more than one market in a slicer?

Hold Ctrl and click each market. In newer versions you can also turn on the Multi-Select button at the top of the slicer.

Can a slicer control a normal chart that is not a pivot chart?

Yes. The slicer filters a pivot table, helper formulas read the pivot table, and the normal chart plots the helper cells. That lets you use error bars and other formatting that pivot charts do not allow.

Why does the chart not show new data?

Pivot tables only update when you refresh them. Use Data, Refresh All after you change or add data.

How do I hide the slicer header?

Right-click the slicer, open Slicer Settings and untick Display header.

What is the difference between this chart and the check box version?

The chart and arrows are the same. The check box version sums the ticked markets with SUMIFS, while this version filters a pivot table with a slicer and reads it with VLOOKUP.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

A dynamic variance arrows chart with slicers keeps everything you liked about the check box version, the arrows, the percentages and the year-on-year columns, and adds a cleaner way to choose markets. A pivot table does the filtering, two VLOOKUPs feed the Support sheet, and a custom slicer style makes the selector look like part of the design. Build it once, refresh when the data changes, and your dashboard is ready for any market you add.

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com