Charts and Dashboards: Charting Example – Extended Case Study Part 5

Welcome back to our Charts and Dashboards blog series.  This week, we’re going to continue to look at the example chart and explain why we’ve chosen to use Combo Boxes over Data Validation.

We’ve covered how to insert and link up Combo Boxes.  This does beg the question, why not use Data Validation instead?

Data Validation offers some benefits over Combo Boxes.  For example, you may link to both horizontal and vertical lists.  This would mean that if we used Data Validation, we wouldn’t have had to transpose the date data as we covered last week.

Combo Boxes also require that macros are enabled, whereas Data Validation may be used regardless.  Data Validation will also straight away give us the value that we select, whereas Combo Boxes give us the number of the value in the list, meaning that we would need to make use of the INDEX or OFFSET function to find the original value selected.  However, having the position of the selection in the list can be useful, allowing us to forego the use of the MATCH (or other) function to lookup data and jump straight into the INDEX, OFFSET or your method of choosing.

Data Validated lists are not perfect either: once you click off the cell, it is not easy to spot that the cell contains a dropdown list (this is why they must always be formatted).  But this is a minor point.

With this all borne in mind, it may seem like Data Validation should be the way to go but Combo Boxes have one very strong advantage.

Combo Boxes can be moved!  This allows us to place them on top of our graph (however, if the graph is created after the Combo Boxes, you will need to right-click on it and select ‘Send to Back’, else they’ll be beneath the chart and therefore hidden) creating a cohesive variable graph.

That’s it for this week.  Come back next week for more Charts and Dashboards tips.