Bar charts are often used to display categorical data using height or length of the bar to encode numerical values. Horizontal bar graphs are especially useful for ranking purposes—that is, when sorting categories from largest to smallest, or vice versa, is required.

In their standard form, bar charts represent two variables. This low dimensionality can be improved by adding more variables and hence bringing them up on the echelon of graphic excellence using Edward Tufte’s own words, “Graphical excellence is nearly always multivariate.”

One of the design strategies to enhance the dimensionality of bar charts is to use color to encode another dimension in the data. For example, the chart below shows US traffic fatalities by state in 2015—ranked from highest to lowest—represented by the length of the bars. At the same time, a sequential color scheme encodes traffic fatality rates per 100,000 people.

Another design strategy is to use a symbol to add a fourth dimension indicating those states with automobile safety inspections.

Let’s build this chart in Excel.

Here is the dataset for the graphic in the range B2:E56 as per the screenshot below:

2. Next, we will create 4 different series, one for each band defined above. Band 1 will be for values below 9. Band 2 for values between 9 and 14. Band 3 for values between 14 and 19. And Band 4 for values above 19. For that, add the following formulas into the respective cells:
F4 --> = IF(D4 <= $M$4,C4,NA())
G4 --> = IF(AND(D4 <= $M$5,D4 > $M$4),C4,NA())
H4 --> = IF(AND(D4 <= $M$6,D4 > $M$5),C4,NA())
I4 --> = IF(D4 > $M$6,C4,NA())

3. Select the range F4:I4, press Ctrl + C to copy the cells, then select cells F5:I54 and click Home > Paste and pick the Formulas paste option.
4. Select range F4:I54 and then click on the Align Right icon in the Alignment group on the Home tab. The traffic fatalities are now segregated into the 4 different bands and the worksheet should look like this:

HOW TO CREATE A BAR CHART WITH COLOR RANGES IN EXCEL

5. Highlight the range F3:I54 and then click Insert > Insert Column or Bar Chart > Stacked Bar. The chart should look like this:

6. Right-click the chart and then, on the shortcut menu, click Select Data.
7. In the Select Data Source dialog box, add the states labels by clicking the Edit button under Horizontal (Category) Axis Labels.
8. In the Axis Labels dialog box, edit the Axis label range to include cells B4:B54 as follows:

9. Press on the Ok button twice to close the Axis Labels and the Select Data Source dialog boxes. The chart should look like this:

10. Right-click the Vertical (Category) Axis and then, on the shortcut menu, click Format Axis.
11. In the Format Axis pane, with Axis Options selected, turn on the Categories in reverse order.
12. Under Labels, set the Interval between labels to Specify interval unit and keep the default value of 1.
13. Select any series in the chart and then, in the Format Data Series pane, under Series Options, set the Gap Width to 0%.
14. Click on the Fill & Line icon and then set the series Border to Solid line and its Color to white. Do the same for the remaining three series. The chart should look like this:

15. It’s time now to bring some order to the data and rank the states by fatalities from largest to smallest. Select the range B3:E54.
16. On the Data tab, in the Sort & Filter group, click Sort.
17. In the Sort dialog box, under Column, in the Sort by box, select Fatalities from the dropdown list.
18. Under Order, select Largest to Smallest from the dropdown list. Click the Ok button and the chart should look like this:

19. Change the Fill color of each data series using a sequential color scheme with increasing intensity from Band 1 to Band 4.
20. Delete the Chart Title and the Legend.
21. Turn off the border of the Chart Area by setting the Border to No line.
22. Change the line Dash type of the Horizontal (Value) Axis Major Gridlines to Dash.
23. Right-click the Vertical (Category) Axis and then, on the shortcut menu, click Format Axis. In the Format Axis pane, with Axis Options selected, click the Fill & Line icon. Set the Line to No line.
24. Add a chart title and data source. Change the font, size and color of the text labels in the chart. The chart should look like this:

25. Create a legend using Rectangle shapes and Line shapes which you’ll find by clicking on the Shapes button in the Illustrations group under the Insert tab.
26. Change the Rectangle shapes border color to grey and their fill color to match that of the respective data series.
27. Add the values of the boundaries of the legend using Text Box shapes. Link these text boxes to the respective cells in the range M4:M6—as defined in step 1 above—to make them dynamic. The way to do it is to select the Text Box and then, in the Formula Bar, type the equal sign followed by a mouse click on the respective cell. The chart should look like this:

28. Next, we’re going to create a dummy series to represent states with safety inspections using the symbol ‡. For that add the following formula in cell J4:
= IF(E4 = "Yes",0,NA())
29. Select cell J4, press Ctrl + C to copy the cells, then select cells J5:J54 and click Home > Paste and pick the Formulas paste option.
30. Select range J4:J54 and then click on the Align Right icon in the Alignment group on the Home tab.
31. Add the character ‡ in cell J3 as a column header. The worksheet should look like this:

32. Right-click the chart and then, on the shortcut menu, click Select Data.
33. In the Select Data Source dialog box, add a new data series by clicking the Add button under the Legend Entries (Series).
34. In the Edit Series dialog box, edit the Series name edit box to refer to cell J3. Next, edit the Series values to include cells J4:J54 as follows:

35. Click on the Ok button twice to close the Edit Series and the Select Data Source dialog boxes.
36. Select the chart. Click on the Format tab under Chart Tools and then select the Series “‡” from the Chart Elements dropdown list as shown in the screenshot below:

1. First, lets define 4 bands for the fatalities per 100,000 inhabitants. From the data set we see that these range from 3.4 to 24.7. Hence, add the boundaries of the ranges in cells M4:M6 as per the screenshot below:

37. While the Series “‡” is still selected, click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Inside Base option under Data Labels, as per the screenshot below:

38. Right-click on the Series “‡” Data Labels and then, on the shortcut menu, click Format Data Labels.
39. In the Format Data Labels pane, with Label Options selected and under Label Contains, turn on the Series Name and turn off the Value and Show Leader Lines.
40. Click on the Size & Properties icon and then, under Alignment, set all the margins to zero.
41. Finally, add a note describing what the symbol ‡ represents and the chart should look like this: