Dashboard 101 – Dynamic Charts in MS Excel

Dynamic Charts in MS Excel

Pharma industry is a data rich industry and charts are an excellent way to represent  numeric data. Most of the charts made for presentations/reports are generally static in nature i.e. their view does not change.  A dashboard with charts requires them to be dynamic so that they represent different scenarios in line with the parameters chosen by the user.

There are certain provisions in MS Excel that allow dynamic charting and then there are some simple but amazing tricks that can add great value to the analysis. We are going to cover all of them in this section.

Broadly, three kinds of dynamic charts can be created in MS Excel. They have been described in detail in the section below.

1. Dynamic charts using variable data

These are the most common type of dynamic charts that can be created in MS Excel. The logic that goes into making them is that since the source data of the chart is linked to the chart, when the source data changes, the changes are reflected on the charts as well. Using this logic and Excel formulas, the data on the chart can be linked to as many inputs as the user wants.

For example, the inputs for a KPI tracking dashboard can be Sales (value/volume), time period, currency, etc.The figure below represents the process of creating a chart with dynamic data. A simple VLOOKUP formula has been used to make the data dynamic by looking up the selected product from the dataset.

 

2. Dynamic charts using named ranges

Source data for charts in MS Excel can be set using Named Ranges. And we know that named ranges can be variable and be defined using formulas (for more details see named range section in Dashboard 101). If a variable range is assigned as the source data for a chart, the chart would reflect the changes every time the range changes.

This feature can be used to make the axis of the charts in MS Excel dynamic. For example, say we have an IMS sales dataset for four years and we want to be able to select the different time period for which data on the chart can be viewed. Based on the year selected, we can define named ranges which will change with the years chosen.

See the figures below for detailed explanation. We will add this feature to the example that we had taken in the above section.

Here, we have created named ranges for the axis and the series values and set them as source data for the graph. So, every time the year selection changes, the named range changes and the chart gets updated automatically.

3. Dynamic chart-type trick

This is a very useful trick (you can see it in action in CompTrax). MS Excel provides the option to set the chart type for individual series on a chart. So a chart can have a series as column chart and another series as line chart. Using this property, we can make our chart dynamic in a way that the user can select the type of chart he wants to see his data represented in.

Let’s continue and add this feature to the chart as we left it in the previous section. In simple terms, we’ll make copy of the chart data and set them as separate series on the chart. We’ll then change chart type of one series to a line chart and link the series values to the option button selection. Sounds complicated? It’s actually very easy. Just see the steps below

You can download Dynamic Chart Final (a sample file), where the three methods discussed above have been used in combination with each other to make the chart dynamic.

Feel free to reach out to us at query@crabsheet.com in case you face any difficulties in understanding the process of creating dynamic charts and we would be happy to address your queries.

2 thoughts on “Dashboard 101 – Dynamic Charts in MS Excel

Have a question? just leave a comment and we will answer A.S.A.P