Microsoft

Two dynamic chart enhancements using Excel

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.

Y-axis order

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:

  1. Select the Y-Axis. Click in the axis area. If you can't get it, choose Vertical (Category) Axis from the mini toolbar.
  2. Right-click the selected axis and choose Format Axis from the resulting contextual menu.
  3. In the resulting dialog, check the Categories In Reverse Order option.
  4. 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:

  1. Select and right-click the X-axis. Choose Format Axis from the resulting submenu.
  2. In the Axis Options section, choose High from the from the Axis Labels dropdown.
  3. Click Close.

Conditional format

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:

  1. Select the source data. In this case, that's B3:E6.
  2. 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.
  3. 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.
  4. Enter the formula =B$2=$I$8.
  5. Click the Format button and then click the Fill tab.
  6. Choose a highlighting color. I chose light blue to match the chart.
  7. 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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
nick.harrison
nick.harrison

Hello Susan, For a complete Excel nob like me that was a very helpful article. I may have made a mistake somewhere but I found that the negative value for Hancock in 2010 does not perform a reverse chart plot and looks a little misleading. Is this an Excel issue or me? Nick

ssharkins
ssharkins

Nope, it isn't you. I wish I'd noticed this before -- but it's the discrepancy between the values. All the other values in in the 100k -- this one's a measly $10,000 -- try changing any of the values to $10,000 and they won't show up either -- the negative isn't the problem. It's the axis units. Sometimes you can enlarge the actual chart object, but in this case, that's not going to be of much help. I think changing the units is the only viable option to catch that. Thanks for bringing it up!