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.
For instructions on using the IF function, see: Expert Skills Lesson 3-5 Use the IF logic function.
For instructions on creating Pivot Tables with calculated fields, see: Expert Skills Lesson 5-17 Add a calculated field to a pivot table.
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.
For instructions on creating charts, see: Essential Skills Lesson 5-2 Create a simple chart with two clicks.
Once the chart is created, you can modify the color of the bars to your liking. For instructions on formatting chart elements, see: Essential Skills Lesson 5-7 Manually format a chart element.
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.