Here is a Highlight Chart that allows highlighting certain data points by using on-chart controls. In this post, I will explain how to build one. You can also download the example workbook and check/modify it yourself.
If you are a follower of this blog, you are by now well aware that you can make almost all kinds of interactive charts in Excel. All you need to know is a decent bit of knowledge on charts and formulas. Below, I will show you how to build a Highlight Chart using Formulas and Form Controls.
Here is the data for our chart.
Select this data (A1:B13) and insert a column chart. Here is how it should look like:
Now insert 5 option buttons from Form Controls (you can find them under Developer Tab). Edit text labels of your option buttons as “All Quarters”, “1th Quarter”, “2nd Quarter” and so on. Then set cell link to cell D1. When you do that, value of cell D1 will change from 1 to 5 with each selection you make using option buttons.
Now we need an additional data series for our chart which will provide interactivity. Setup column C as shown below:
This setup will allow “Quarter” series to get values depending on the value of cell D1. So depending on your selection of option buttons, cells on column C will get either the corresponding value from column B or #NA.
Now add this series to the chart. İt will look like this:
Here the chart shows Sales series with blue color and Quarter series with orange color. Make the following adjustments on the chart:
- Set series overlap as 100%
- Set gap width as 100%
- Set fill color of Quarter series to a dark green color
- Set fill color of Quarter series to a lighter green color
- Add a chart title (Sales Overview for example)
- Resize plot area to open some space at the bottom of the chart for option buttons.
Here is how it is supposed to look at this stage:
Now drag your option buttons and place them on the bottom of your chart. And your Highlight Chart is finished. Now you can highlight quarters on the chart by using option buttons. Here is the final look of your Highlight Chart:
You can download this Highlight Chart from the link below: