Dynamic Charts using Excel Filters

It’s been a while since my last post.  Mostly due to the fact that I have a new position.  I’ve changed jobs, changed countries and well completely overhauled my life!  Now that I’ve settled in (somewhat), I thought it was time for me to start posting again.  That said, I’ve received a lot of questions over the past couple of months, around Excel and Charts, so I’ll try to answer those first!

This first post came to me from a friend in France, who wanted to know a “quick and dirty” way to have a chart on a dashboard that updated when the user selected the data they wanted to view.  Obviously this can be done with pivot tables and charts, however, my friend wanted something simpler and cleaner.  My suggestion?  Use the built in Excel data filters.

I’ve uploaded the final version for you to view here.

 

Step 1: Set up your data

In the uploaded example, I’ve put the data on Sheet 2, but in theory you can put the data wherever you want it to.

 

Step 2: Create a chart

In this example, the plan is to have 5 separate charts, one for each row of data, but I jump ahead…

Highlight the first row of data and then click on Insert > Column > Clustered Column

 

This will create a column chart on the same page as your data.  Format the chart how you want the final chart to look.  I’ve removed the legend, the horizontal lines and I’ve widened the bars.  I’ve also removed the fill colours,

Step 3: Create your dashboard

In the first cell (A1) type in the words “Select Chart”

Underneath that paste the titles for your charts.  Mine shows the names of the girls.  Increase the height of each row to slightly larger than the size of the chart you want to display.  Then increase the width of column B so that it is wide enough to fit your chart.

Then copy / paste the chart you have created into cell B2.  Make sure that it fits inside the cell B2.  Make further adjustments to the chart as you wish.

Step 4: Create the other charts

Copy the chart in B2 and paste it in cell B3.  Update the chart so that it shows the data in the second row of the table.  In this case the row that corresponds to the name “Jennifer”.  Change the format of the chart as you wish, so that it is obvious that it is showing the data for a different dataset.  I’ve changed the fill colour.  You could also change the colour of the names in column A to match the fill colour.

Repeat until you have a chart in each row.  Note: This whole procedure will not work unless the chart is nestled securely within 1 cell.

Step 5: Add the filter

First merge cells A1 and A2.  This prevents the user from seeing two filter drop downs.

Then, with the merged cells A1:A2 selected, click on Data > Filter.  A drop down menu button should appear.  Click on this button and choose to show only 1 result e.g. “Stephanie”.

Press the OK button.  The whole table should update so that you can see just one name and 1 chart.  And there you have it, a very simple dynamic chart, which can be used on any dashboard!

Enjoy

+Alesandra Blakeston

 

 

Related articles

• Adding Charts (traininoffice.wordpress.com)