These two Excel chart enhancements are easy to implement and will improve readability.
Charting is a huge subject for users and our best efforts can usually be improved. For most of us, it's impossible to know all the possibilities. Today, I want to show you two easy enhancements for a dynamic chart (dashboard) display.
We'll be working with a dynamic chart from an old blog, Create a dynamic Excel chart and make your own dashboard. You can work with most any chart that you've embedded alongside the data. Or, you can download the example workbook. As is, the dynamic chart is Okay, but we can make it even better, with very little effort:
- We'll rearrange the y-axis labels so they match the order of the source data.
- We'll use conditional formatting to highlight the chart's current data.
The order of the Y-axis labels in this example isn't critical. However, because the chart is visible alongside the source data, you can quickly see that the order doesn't match the data. It's not a big deal, but for the sake of consistency, you can change the chart's order as follows:
- Select the Y-Axis. Click in the axis area. If you can't get it, choose Vertical (Category) Axis from the mini toolbar.
- Right-click the selected axis and choose Format Axis from the resulting contextual menu.
- In the resulting dialog, check the Categories In Reverse Order option.
- Click Close.
This small change isn't something users will notice, unless you don't make it!
You might have noticed that this change also moved the X-axis. Most likely, you'll want the axis to appear at the bottom of the chart, where users will expect to see it. Fortunately, you can put it back where it belongs as follows:
- Select and right-click the X-axis. Choose Format Axis from the resulting submenu.
- In the Axis Options section, choose High from the from the Axis Labels dropdown.
- Click Close.
You might not think to add a conditional format to this display, but doing so can enhance the user's experience by visually relating the charted data to the source data. Let's add a conditional format that highlights the charted data in the source range, as follows:
- Select the source data. In this case, that's B3:E6.
- Click Conditional Formatting in the Styles group (on the Home tab) and select New Rule. In Excel 2003, choose Conditional Formatting from the Format toolbar.
- Highlight the Use A Formula To Determine Which Cells To Format option in the top pane. In Excel 2003, choose Formula Is from the first dropdown.
- Enter the formula =B$2=$I$8.
- Click the Format button and then click the Fill tab.
- Choose a highlighting color. I chose light blue to match the chart.
- Click OK twice to return to the sheet.
Immediately, the new format is apparent. Feel free to change the year (click the scrollbar's arrows) to update the chart and the source data format, accordingly.
Thanks to reader ronwwallace for suggesting both of these enhancements.