Excel Technique: Composite Bar Charts

Stage: 3 and 4 Challenge Level: Challenge Level:2 Challenge Level:2
In the Excel screen shot below, I selected my data (the 15 values in the grid), then chose Chart from the Insert drop-down menu. I chose Bar, but both Column and Bar charts have 100% stacked (composite) as their third chart sub-type option.

When I clicked Next to move on, I have to decide whether my data was to be taken as series in rows or columns.

Notes in the screen shot explain this option but it isn't that important to understand.

Series in Row or Columns
Choose either and a display shows the effect.

When I'm making up fraction bars, I normally want one bar split in the proportion of a group of numbers: So the "Series in Row/Column" question is asking whether the values are to share one bar (composite) or each to have their own bar. I choose rows then columns, and choose the one that gives a single bar with the right number of parts.

Clicking Next takes me to a dialogue box with tabs for : Titles, Axes, Gridlines, Legend, Data Labels, Data Table
  • Usually on the Axes tab I'll want both (Primary & Value(Y)) set to off.
  • Gridlines the same.
  • From Legend, I'll choose not to show a legend.
  • Then I'll click Finish.
Once I have my chart on the spreadsheet workspace, I right-click on Chart Area to get a menu: ( The Chart Area is just in from the edge of the box holding the chart - float there and Excel tells you where you are.)
From this menu I choose Format Chart Area:
  • In Patterns, I put "none" for both border and area colours
  • In Properties, I select "move but don't size with cells" . Then OK.
Back on the spreadsheet workspace, I right-click on an element within the bar, and choose: Format Data Series
  • Patterns tab lets me set the colours for border and area.
  • Options tab gives access gap width (set to 0 - this sets the whole bar)
I choose the object select tool ( white pointer arrow ) from the Draw menu, click on the Bar to select it as an object, then right-click and Format Object.
  • On the Size tab I can specify height and width, or scale by %. Then click OK.

Selecting the chart as a Draw object lets me control size/position, and align or group with other objects, including other charts, but once that's done I need to turn-off the object select tool ( white pointer arrow ) on the Draw menu, or press Esc, to return my spreadsheet to normal function.

A final tip : If the charts ever fail to display as set up, try changing one of the source data values, you can always change it back, this forces the display to refresh.