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

Dynamic Variance Arrows Chart with Check Boxes in Excel

A dynamic variance arrows chart with check boxes shows this year against last year for every month and puts a green up arrow or a red down arrow on top of each pair of columns, with the percentage change written beside it. Tick one or more market check boxes and the whole chart recalculates for just those markets. In this tutorial you build it from a plain sales table with SUMIFS, two error-bar helper columns and Form Control check boxes. No VBA is needed, and it works in Excel 2013 and later.

Dynamic variance arrows chart with check boxes in Excel comparing 2017 and 2018 monthly sales with green and red arrows and percentage labels
The finished chart: India and US are ticked, so each month shows their combined 2017 and 2018 sales with the variance arrow and percentage.

Watch the Video Tutorial

PK builds the chart from a blank workbook: check boxes, SUMIFS formulas, error-bar arrows and percentage labels.

What You Will Build

  • A clustered column chart with black columns for 2017 and gold columns for 2018, month by month.
  • Green up arrows and red down arrows drawn with custom error bars, sized exactly to the change between the two years.
  • Percentage labels such as +19% and -9% pulled from cells with Value From Cells.
  • Five market check boxes (India, US, UK, Germany and Poland). Tick any combination and the chart adds those markets together.

The Sales Data

Everything starts from one flat table on a sheet called Data: Market Name (A), Year (B), Month (C) and Sales (D), for five markets, 2017 and 2018, and twelve months.

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

Add two more sheets: Support for the helper calculations and Chart for the final dashboard. On the Chart sheet, turn off gridlines (View → untick Gridlines) so the chart sits on a clean background.

Part 1: Add the Market Check Boxes

Step 1: Set up the Support sheet

On the Support sheet type Month in A1 and Jan to Dec in A2:A13 (type Jan and drag the fill handle down). Put Year 2017 in B1 and Year 2018 in C1.

Step 2: List the unique market names

Copy the Market Name column from the Data sheet, paste it into column Q of the Support sheet with Paste Special → Values, then use Data → Remove Duplicates. You are left with India, US, UK, Germany and Poland in Q1:Q5.

Step 3: Insert five Form Control check boxes

Go to the Chart sheet and choose Developer → Insert → Check Box (Form Control). Draw the first one and rename it India. Copy and paste it four times and rename the copies US, UK, Germany and Poland. Select all five and use Format → Align → Align Top and then Distribute Horizontally so they sit in a neat row.

Step 4: Link each check box to a cell

Right-click the India check box, choose Format Control, and in the Cell link box click Support!R1, the cell beside India. R1 now shows TRUE when India is ticked and FALSE when it is not. Repeat for the other four: US to R2, UK to R3, Germany to R4 and Poland to R5. The order must match the names in Q1:Q5.

Part 2: Sum Sales for the Ticked Markets

Step 5: The 2017 formula

In Support!B2 enter this formula (it is one formula, split over lines here so you can read it):

=IF($R$1,SUMIFS(Data!D:D,Data!A:A,Support!$Q$1,Data!C:C,Support!A2,Data!B:B,2017),0)
+IF($R$2,SUMIFS(Data!D:D,Data!A:A,Support!$Q$2,Data!C:C,Support!A2,Data!B:B,2017),0)
+IF($R$3,SUMIFS(Data!D:D,Data!A:A,Support!$Q$3,Data!C:C,Support!A2,Data!B:B,2017),0)
+IF($R$4,SUMIFS(Data!D:D,Data!A:A,Support!$Q$4,Data!C:C,Support!A2,Data!B:B,2017),0)
+IF($R$5,SUMIFS(Data!D:D,Data!A:A,Support!$Q$5,Data!C:C,Support!A2,Data!B:B,2017),0)

Fill it down to B13. PK writes the first IF block, then pastes it four times, changing only the 1 in $R$1/$Q$1 to 2, 3, 4 and 5.

How the formula works

  • SUMIFS(Data!D:D, ...) adds the Sales column where the market equals the name in Q1, the month equals the month in A2 and the year equals 2017.
  • IF($R$1, ..., 0) only keeps that total when the India check box is ticked (R1 is TRUE). Otherwise it adds 0.
  • The five IF blocks are added together, so ticking India and US gives India plus US, and ticking all five gives the total of every market.

Step 6: The 2018 formula

Copy B2:B13 to C2:C13. Then, with C2:C13 selected, press Ctrl+H and replace 2017 with 2018. Only the selection is searched, so column B stays on 2017.

Part 3: Helper Columns for the Variance Arrows

The arrows are error bars on two invisible series. Add four helper columns to the Support sheet:

ColumnHeaderFormula in row 2
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())

Fill all four down to row 13. With India and US ticked, January goes from 1455 to 1731, so the Up Error Bar is 276; April falls from 1690 to 1543, so its Down Error Bar is 147.

Why these helpers work

Max Left and Max Right sit at the height of the taller column. A minus error bar of the size of the change runs from there down to the shorter column. NA() matters: a #N/A error amount draws no error bar, so each month gets only one arrow.

Part 4: Build the Variance Arrows Chart with Check Boxes

Step 7: Insert the column chart

Select Support!A1:E13 and choose Insert → 2-D Clustered Column. Right-click the chart, choose Select Data, and use the arrows to move Max Left to the top, so the order is Max Left, Year 2017, Year 2018, Max Right.

Step 8: Add custom error bars

Select the Max Left series, click the + (Chart Elements) button and tick Error Bars. Do the same for Max Right. Then select the Max Left error bars and in Format Error Bars set:

  • Direction: Minus
  • End Style: No Cap
  • Error Amount: Custom → Specify Value, Negative Error Value = =Support!$F$2:$F$13 (the Up Error Bar column)

Give the Max Right error bars the same settings, with the Negative Error Value set to =Support!$G$2:$G$13 (the Down Error Bar column).

Step 9: Hide the helper columns and draw the arrows

Set Max Left and Max Right to Fill → No fill, so only their error bars remain. Then format the error-bar lines:

  • Max Left error bars: Solid line, green, width 1.5 pt, and a Begin Arrow type. This gives the up arrow.
  • Max Right error bars: Solid line, red, width 1.5 pt, and an End Arrow type. This gives the down arrow.

Step 10: Secondary axis, overlap and colours

Open Format Data Series for Max Left and choose Secondary Axis, then do the same for Max Right. Set Series Overlap to about 20 to 25% and Gap Width to about 75% on both the primary series (2017) and the secondary series. Fill the Year 2017 series with solid black and Year 2018 with solid gold.

Step 11: Clean up

Delete the secondary vertical axis, click the Max Left and Max Right legend entries one at a time and press Delete, and untick Gridlines. The legend now shows only Year 2017 and Year 2018.

Part 5: Add the Percentage Labels

Step 12: Label helper columns

Add two more columns. In H1 type Up Data Label and in H2:

=IF(ISERROR(F2),"","+"&TEXT(F2/B2,"0%"))

In I1 type Down Data Label and in I2:

=IF(ISERROR(G2),"","-"&TEXT(G2/B2,"0%"))

Fill both down to row 13. The change is divided by the 2017 value because last year is the base. TEXT(...,"0%") turns 0.19 into 19%, and the plus or minus sign is joined in front, so you get +19% and -9%. ISERROR returns an empty string for months where that arrow does not exist.

Step 13: Link the labels with Value From Cells

Select the Max Left series. Because it has no fill it can be hard to click, so use Format → Current Selection and pick Series “Max Left” from the drop-down. Right-click, Add Data Labels, then open Format Data Labels, tick Value From Cells, select Support!H2:H13, and untick Value and Show Leader Lines. Make the labels green, bold and a little larger from the Home tab.

Repeat for Max Right with Support!I2:I13 and colour those labels red.

Step 14: Move the chart to the dashboard

Cut the chart (Ctrl+X) and paste it on the Chart sheet under the check boxes. Add a light rectangle behind them (Send to Back), then select everything and Group it. Tick a few markets and watch the columns, arrows and percentages update.

Modern Excel Alternative

This section is an addition and is not shown in the video. In Excel 365 and 2021 you can replace the five IF blocks with one formula that reads the check-box cells directly:

=SUM(SUMIFS(Data!$D:$D,Data!$A:$A,FILTER($Q$1:$Q$5,$R$1:$R$5),Data!$C:$C,A2,Data!$B:$B,2017))

FILTER returns only the ticked market names, SUMIFS returns one total per market, and SUM adds them. If no box is ticked, FILTER returns an error, so wrap the formula in IFERROR(…,0) if you want 0 instead.

Common Errors and Fixes

The arrows sit beside the columns instead of on them

Max Left and Max Right must be on the secondary axis, and the overlap and gap width need to be similar on both axes. Also check the series order in Select Data: Max Left first, Max Right last.

Every month shows two arrows

The error-bar helper must return NA(), not 0 or an empty string, when that direction does not apply. Check columns F and G.

The chart shows zero for every month

No box is ticked, or a box is linked to the wrong cell. Check that the links run R1 to R5 in the same order as Q1:Q5.

Tips

Want It Ready-Made?

If you want the finished workbook with the check boxes, formulas and formatting already done, get the Dynamic Variance Arrows Chart with Check Boxes template on NextGenTemplates.com, or browse more Excel templates. To learn charts, slicers and complete dashboards step by step, see the course Excel Pivot Tables and Dashboards.

Related Tutorials

Frequently Asked Questions

How are the variance arrows drawn in an Excel chart?

They are custom minus error bars on two invisible column series that sit at the height of the taller column. The error amount equals the change between the years, and the line uses an arrow head, green for up and red for down.

Why use check boxes instead of option buttons?

Option buttons allow only one choice. Check boxes let you tick several markets at once, and the SUMIFS formula adds every ticked market together.

How do I show the percentage change on the chart?

Calculate the label text in a helper column with the TEXT function, add data labels to the invisible series and use Value From Cells to point the labels at that column. Step 12 shows both formulas.

Which Excel version do I need?

Excel 2013 or later, because Value From Cells for data labels was introduced in Excel 2013. The formulas and error bars also work in Excel 2010.

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 check boxes turns a flat sales table into a year-on-year story your audience reads in seconds. Linked check boxes feed SUMIFS, two helper series carry error bars shaped as arrows, and Value From Cells adds the percentages. Build it once on the Support sheet, group it on a clean dashboard, and every tick of a market check box updates the columns, arrows and labels together.

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