Summary: Herein is shown how to work around the problems in Excel 2007 to create a multi-series, multi-axis, dynamic bar chart.
First, I’ll air my complaints about Microsoft Office 2007. I am not a fan of Microsoft Office, and I find the ribbon UI to be a lamentable step away from usability. Though I can appreciate that it makes some operations more discoverable to the new user, it makes complex uses frustrating as the selection of buttons and menus is constantly changing. The pretty pictures take up more of my precious vertical real estate than a menu would, and I can’t drag the ribbon to the side like I could with a toolbar (that one at least should be fixable). I also dislike the toolbar icons in the title-bar; they are further away, and I frequently have trouble dragging a window around as the status bar is mostly icons and thus non-selectable. The 2003 UI that OpenOffice copied is not great, but I don’t think the 2007 changes are improvements. I look forward to the day when my clients will save the money and let me work in OpenOffice all the time.
With that out of my system, I can explain my recent wrestle with Excel 2007. My client asked me to create a multi-series, multi-axis, dynamic range bar chart. My first reaction to the Excel 2007 charts was amazement—the default charts are very nice and simple to create. Like most of the “improvements” in Excel 2007, they look good until you buy the product and try to get some real work done. Then you realize that they broke all the power tools in order to make the initial experience glitzy. These charts were surprisingly difficult, which is as much due to the buggy application as to an awkward interaction.
The request was to create a bar chart with team member names along the bottom. Each team member would have two columns tracking different types of data: total number of actions, and total value of those actions. The total number of actions would be the value along the left vertical axis, and the total value would be along the right vertical axis. They want to be able to add and remove team members, and have the chart update dynamically.
So you can see what I’m talking about, here is a screen shot of the finished chart:
Get the data you want into a table:
Three columns: team member name, number of actions, value of actions.
Create a standard multi-series bar chart:
- Ribbon→Insert→Column Chart→2D Clustered Column
- Drag it where you want it.
- Right click on it and select “Select Data”
- Highlight the columns you want to include, plus two additional blank columns.
- The additional blank columns will be used to space the populated columns so that the two axes don’t overlap.
- In the “Select Data Source” dialog, make sure that the data categories (team member names) are the “Horizontal Axis Labels” and that the two sets of data values, plus the blank columns, are the “Legend Entries (Series)”. Order the blank columns (blank series) to be between the two sets of data. Then click OK.
- Excel will order the series correctly if you put the blank columns between your populated columns in the table. If you choose to then hide the columns, you will need to select “Hidden and Empty Cells” in the “Select Data Source” and click on “Show data in hidden rows and columns”. “Show empty cells as Gaps” should also be selected. We skip this step because when we put in the named ranges we can select a blank series anywhere we want, eliminating the need for hidden columns.
- You can manipulate most chart components by clicking directly on the chart to select the item, then right clicking and selecting “Format “. When that is not possible, such as for the blank series, go to the Ribbon→Layout and on the left side there is a drop down which displays the various chart components allowing you to select them and manipulate their attributes by clicking on “Format Selection”.
- Select Series 1 (Num Actions). Confirm that the series is being plotted on the Primary Axis.
- Select Series 2 (Blank Series). Confirm that it is also being plotted on the Primary Axis.
- Select Series 3 (Blank Series). Change it to be plotted on the Secondary Axis.
- Select Series 4 (Action Value). Change it to be plotted on the Secondary Axis.
- If the columns overlap in any way, then you have the blank series in the wrong relationship to the populated series. The primary axis needs a populated series on the left (Series 1) and a blank series on the right (Series 2). The secondary axis then needs a populated series on the right (Series 4) and a blank series on the left (Series 3). You can change the number of the series looking at the formula bar while that series is selected. It is the last number in the SERIES formula.
Clean up the chart formatting
- This chart will need lots of horizontal room, so I move the labels underneath the chart by selecting the legend as a whole, right clicking, selecting “Format Legend” and changing the position to “Bottom”. I also like to widen the legend to take up the whole bottom, so that the names are closer to their respective axes.
- Select the legend items for the blank series, and press the Delete key to remove them from the legend. Your first click should select the legend as a whole, then additional clicks will tell Excel you want the item within the legend. You can also right click on select “Delete” (not “Delete Series”).
- The total amounts can be pretty large, so I select the right axis, choose “Format”, and change the “Display Units:” to Millions.
Make the chart dynamic
- Ribbon→Formulas→Name Manager→New
- The Name must not start with a “C”. I was starting all my named ranges with “Chart_” to differentiate them from the lists I use for data validation. Excel would accept the range, and then not let me edit the chart ever again. Sometimes after save and re-open the chart would be unreliable. And it was not dynamic. After much experimenting and Googling I tried changing the prefix to “Graph_” and suddenly everything is functional and reliable. Arg.
- The Scope needs to be the sheet name (in my case “Team Unassigned”). When I used the Workbook as the scope, my SERIES formula would give a really generic error about something not being right. I don’t think it was able to find the named series. Changing the scope to the sheet name solved that error. This weird behavior, coupled with the buggy behavior listed above, gave me nightmares of confusion and anger.
- It is in the “Refers to” box that we get dynamic. We use the OFFSET formula to create our range. This formula gets long, and you must use the mouse when you want to move within the box, as the arrow keys will affect the table and mess everything up (insert curse here). The OFFSET formula is (reference, row offset, column offset, [height], [width]). The reference is the first cell in our range. Since our reference point is within our range, the offsets are both zero. The width should be 1. The smarts is in the height, which we dynamically calculate by counting the populated cells in the source range with COUNTA. Since my cells are populated from another sheet, COUNTA will see them all as having a value. So I need to subtract COUNTBLANK to get the actual number of cells in the charted range. The formula I used is:
=OFFSET('Team Unassigned'!$A$22,0,0, COUNTA('Team Unassigned'!$A$22:$A$340) — COUNTBLANK('Team Unassigned'!$A$22:$A$340),1)
- If you have any blank cells in your charted range, Excel will give a very generic error and refuse to graph anything. Double check your formula that it only includes cells with chartable data.
- Your other named ranges can be offsets from your first one, with formulas like this one:
- Whenever you want to use your named range, you use
"'<sheet_name>'!<named_range_name>". I only use one named range for both blank series, and I define it to be an unused columns on the far right of my data, like so:
This will use column AA for the range. My fourth named range is Graph_Unassigned_Value, selecting the total value of actions.
- Change each series definition in the chart to use the dynamic named ranges. This is where if you have any problems with your named ranges you’ll get really vague and non-helpful errors. You can see an example of to do this in the above image.
- Select the Series, and edit the SERIES formula in the formula bar to use the appropriate
named ranges. The SERIES formula is (label_name, range_for_categories, range_for_values, series_num). My first series is:
=SERIES('Team Unassigned'!$B$21,'Team Unassigned'!Graph_Unassigned_Name, 'Team Unassigned'!Graph_Unassigned_Num,1).
I leave off the label_name for the blank series:
=SERIES(,'Team Unassigned'!Graph_Unassigned_Name,'Team Unassigned'!Graph_Unassigned_Blank, 2).
The only difference between the two blank series is the series numbers. The final series definition only has to change the last named-range to the new value range (Graph_Unassigned_Value in my case). You can use the arrow keys to move between the series definitions.
- Select the Series, and edit the SERIES formula in the formula bar to use the appropriate named ranges. The SERIES formula is (label_name, range_for_categories, range_for_values, series_num). My first series is:
And that is all there is to it . Once you know how it’s done, it isn’t too bad.