Skip over navigation
Guide and features
Guide and features
Science, Technology, Engineering and Mathematics
Featured Early Years Foundation Stage; US Kindergarten
Featured UK Key Stage 1&2; US Grades 1-5
Featured UK Key Stage 3-5; US Grades 6-12
Featured UK Key Stage 1, US Grade 1 & 2
Featured UK Key Stage 2; US Grade 3-5
Featured UK Key Stages 3 & 4; US Grade 6-10
Featured UK Key Stage 4 & 5; US Grade 11 & 12
Excel Technique: Composite Bar Charts
Stage: 3 and 4
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.
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.
Meet the team
The NRICH Project aims to enrich the mathematical experiences of all learners. To support this aim, members of the NRICH team work in a wide range of capacities, including providing professional development for teachers wishing to embed rich mathematical tasks into everyday classroom practice. More information on many of our other activities can be found here.
Register for our mailing list
Copyright © 1997 - 2014. University of Cambridge. All rights reserved.
NRICH is part of the family of activities in the
Millennium Mathematics Project