Bar charts and hidden columns

I have a worksheet that contains students' test data with a column for their first name, a column for their surname and a column for their total mark out of 50. I have worked out a way of producing a bar chart that colour codes various ranges to show their results, ie 0-25, 26-37, 38-50. This involved producing three additional columns, one for each range so a student would have a mark in the appropriate column and nothing in the other two. Using a stacking bar chart for all three columns, the columns with no marks in therefore didn't show and I could colour code each series. I hope that makes sense! I get a nice chart with individual blue, yellow and green bars. Now the problem - I want to hide the three columns (AA, AB & AC) with the intermediate data as they are not relevant to the user. I know how to do this from the Show data in hidden rows and columns but I have one other column (E) hidden on the sheet and when I check the Show hidden data this comes up on the bar chart as well. Definitely not required.

It sounds as though your best option might be to open the Select Data Source dialog and change the values there. Adjusting the Chart data range setting to only cover the cells you want to be charted should allow you to chart just the data you want.

I hope this helps, but feel free to reply if I can be of more assistance. You can attach a copy of your workbook if you'd like me to take a look at it. For instructions on attaching files, see this thread.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

I have attached the relevant workbook. As it stands the data in columns C & D are concatenated in column E and then column E is hidden.

In order to apply different colours to the bars in the chart, I have produced three new columns AA to AC that sort the data from the total in column G into three ranges. The ranges are then plotted as separate series and different colours applied. The data itself in columns AA - AC are currently in a white font in order not to show. One of the series is plotted against a secondary axis to allow for the same scale at the right hand end of the chart but even when the worksheet is protected this is not ideal.

In essence the effect is what I want and will probably suffice for its purpose for use by teachers but it would be better if it could be done 'properly'. Any help or comments most appreciated.

I see the issue now - you're not just trying to set up different data series in a chart, but trying to do the equivalent of applying 'conditional formatting' to the chart so that the bars appear in different colours according to their value.

The approach you've taken is currently the only way to do this in Excel; there isn't a more 'proper' way to do this. You could achieve the same result by using a Pivot Table and Pivot Chart, but there's really no advantage to that approach in this situation. Is there something specific that isn't ideal about the chart?

For other users trying to achieve the same effect, this is done by creating separate columns for each of the different colored bars you need and then placing the appropriate values in each column. To filter the values into the correct columns, you might use either an IF formula or a Pivot Table with calculated fields.

Once the data is filtered into columns, you can create a Stacked Column chart from the data. This has the effect of creating a single bar for each data series, instead of separate bars for each column.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Thanks for the response. I am happy with the chart as is but as you can see above I can't use the show hidden columns approach as you get the mess along the bottom just above 'Pupils'. I will have to stick to the method I have of formatting the extra data columns in white and perhaps move them further away.

Just to add to Jonathon's response. I can see that you are using Excel 2010 and so will not have seen a different presentational solution to exactly the same problem using a new Excel 2013 feature. You will find this lesson interesting (though you cannot use this solution with Excel 2010):

That's for the extra information. Unfortunately my role is to provide a version that works in Excel 2007 as well as Excel 2010 because our schools still have old versions! The functionality is not backwards compatible but it is really interesting to know how the things move on. Maybe our schools will catch up eventually. Also my publishers have a thing about putting in colour for the teachers! I work within their limitations.