Partners filmexxx.link, sextotal.net, ahmedxnxx.com, https://xnxx1xvideo.com, russianxnxx

Use Data Validation 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

Use Data Validation as On Off Switches 1

We have 6 product sales for the last 7 years

 

Now let’s create the Switches..

Use Data Validation as On Off Switches 2

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..

Use Data Validation as On Off Switches 3

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

Use Data Validation as On Off Switches 4

  • 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

Use Data Validation as On Off Switches 5

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 ?

    ⬇️ Pop in your Name & Email to get the file!


    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

     



    Topics that I write about...