Use Data Validation Drop Down as On Off Switches

Have you ever used a regular item for a non regular use? Let’s say using a bangle for opening a bottle of coke or tapping a tea spoon for making noise 😆 Funny indeed but these fixes at times are pretty robust.

On a similar note, let’s take a look at how can you unusually use Data Validation drop down feature for creating ON/OFF (show/hide) switches for your Charts / Calculations

 

Let’s Begin with this regular Data

We have 6 product sales for the last 7 years

 

Now let’s create the Switches..

In the column prior to Products. Create a Data Validation list feature

1. Select all the cells
2. Go to Data Validation in the Data Tab. (Shortcut is ALT A V V)
3. Choose List and in the source write ‘Show,Hide’
4. Now in all the cells you have a drop down with Show and Hide

 

Linking the Switches to the Data..

Now that we have out switches. We might as well connect that to our data by creating some dummy calculations

1. Note that I have simply copied the headers (Product names and the years)
2. A simple IF formula would do the trick =IF (Switch = “Hide”, NA(), Number) The formula will show an #N/A if the switch is turned to HIDE. And for your good info, #N/A does not appear in the Chart!
3. Now as soon as you change the switch you’ll see the effect rolling in your IF Calculations

 

Make a Line Chart from the Dummy Calculations

• Make a Line Chart from the dummy calculations,
• The Chart responds to changes made in the switches

 

Formatting the Chart

Although our chart is ready but needs a hell lot of formatting. Here are few points for you to consider right away

1. Give a title to the Chart
2. Remove unnecessary borders and lines
3. The horizontal axis should be linked to years
4. Add the legends

If you are wondering how did I manage to get the product names in the line chart ? Then read this – Add Direct Legends to the Line Chart 

Have you tried using Data Validation as Switches?

As of now we have added a bit of dynamism to a simple line chart but you can literally use this trick where ever you want to simulate a switch like feature. It works like a charm!

Let me know

1. If you are new to this then how are your going to implement this in your work or
2. How have you used this before ?

Other Dynamic Charts

1. Create a Check Button Chart – Very Similar to Data Validation Switches
2. Timeline Chart in Excel
3. DOT Chart in Excel – Interesting way to show rating data
4. Funnel Chart Visualization
5. Info graphic Chart Part 1
6. Info graphic Chart Part 2
7. Target Charts – Interesting Chart to show values against targets
8. Encircle Points in a Line Chart
9. Add a Total Bubble at the End of the Line Chart