Data visualisation is fast becoming the norm for effective business intelligence. Having visuals that tell the right story at a glance go a long way in achieving this. Microsoft® Excel® provides you with a multitude of ways to enhance the visuals that you create. In this tip, I’ll show you how to format a basic bar chart to dynamically highlight the maximum value in the series.
- I have a data series showing total sales values for each quarter in my financial year. I’ve created a basic bar chart to display this and I’ve widened each bar in the chart for greater emphasis. You can achieve this by decreasing the bar gap width under the Format Data Series options. To get to these options, right click on a bar and select Format Data Series.
- To determine the maximum value, I’m going to add another series to the chart which I’ll define in the column to the right of my total sales. This column uses an IF and MAX function to return either the total sales value if it is the maximum or else zero. Use the formula displayed below.
- I’m now going to add the new series to the chart. You can do this by right clicking on the chart and choosing Select Data > Select Add from the Select Data Source dialogue, and then pick the right cells for your series name and series values.
- The new series is now placed to the right of the existing series. To get it to appear in place of the existing series you need to overlap it. You can do this by setting the Series Overlap amount in the chart properties to 100%.
Take note that as your series values change, the chart updates accordingly.
If the highlight isn’t displaying, go back to the Select Data Source dialogue and make sure that your highlight series is positioned after your main values series.
As mentioned, Excel provides a multitude of ways for you to enhance your visuals. It’s up to you to explore the possibilities.
The post How to format a chart in Excel to dynamically show its maximum value appeared first on Sage Intelligence.
Source: Excel on Steroids