How To Create Excel Charts With Horizontal Bands

If you want to quickly figure out whether your data series are in the regular data range, you can use the excel band chart. The band chart is a chart with horizontal bands ( which point out the regular or correct data wave range ) and lines with markers of your data series. This article will tell you how to create excel charts with horizontal bands.

1. How To Create Excel Charts With Horizontal Bands.

1.1 Example Data Cells.

  1. Below are the example source data cells. It contains one person’s 24 hours blood pressure data.
    A B C D E F
    1 Time Low Blood Pressure High Blood Pressure Low ( < 80) OK ( 80 – 120 ) High ( > 120 )
    2 0:00 70 100 80 40 40
    3 1:00 75 106 80 40 40
    4 2:00 65 110 80 40 40
    5 3:00 69 100 80 40 40
    6 4:00 67 115 80 40 40
    7 5:00 75 120 80 40 40
    8 6:00 80 128 80 40 40
    9 7:00 85 130 80 40 40
    10 8:00 88 135 80 40 40
    11 9:00 90 140 80 40 40
    12 10:00 95 145 80 40 40
    13 11:00 90 139 80 40 40
    14 12:00 85 130 80 40 40
    15 13:00 80 125 80 40 40
    16 14:00 82 120 80 40 40
    17 15:00 85 128 80 40 40
    18 16:00 88 133 80 40 40
    19 17:00 90 139 80 40 40
    20 18:00 98 140 80 40 40
    21 19:00 90 130 80 40 40
    22 20:00 85 128 80 40 40
    23 21:00 80 120 80 40 40
    24 22:00 75 110 80 40 40
    25 23:00 65 100 80 40 40
  2. Column A is the 24 hours time of one day, column B is the low blood pressure value, and column C is the high blood pressure value of each hour.
  3. Column D is the standard low blood pressure value (80). Column E is the standard high blood pressure value ( 120 = 80 + 40). Column F is the irregular high blood pressure ( 160 = 80 + 40 + 40 ).

1.2 How To Create Excel Charts With Horizontal Bands Steps.

  1. Select the cell range A1:F25, then click the Insert tab on excel top menu bar.
  2. In the Charts group, click the Insert Combo Chart icon to pop down the Combo drop-down list.
  3. Click the first item Clustered Column – Line to insert the Column-Line combo chart.
  4. Click the chart title and change the title to One Day Blood Pressure Data.
  5. Click to select the excel combo chart on the worksheet, then click the Chart Design tab on excel top menu bar.
  6. Click the Change Chart Type item in the Type group to open the Change Chart Type dialog window.
  7. Select the chart type Line with Markers for the Low Blood Pressure and High Blood Pressure data series.
  8. And select the chart type Stacked Column for the Low, OK, and High data series.
    excel-band-chart-change-chart-type-dialog-window
  9. Click the OK button to save the settings.
  10. Right-click any stacked column bar in the chart, then click the Format Data Series… menu item in the popup menu list.
  11. Then it will show the Format Data Series pane on the excel right side.
  12. Slide to change the value of Gap With to 0% in the Format Data Series pane, then you will get the below chart.
    excel-band-chart-column-gap-width-0
  13. In the above chart, the vertical axis value does not match the blue band exactly.
  14. We can right-click the vertical axis and then click the Format Axis… menu item in the popup menu list.
  15. It will show the Format Axis pane on the excel worksheet’s right side.
  16. Go to the Axis Options —> Bounds section, you can change the vertical axis minimum & maximum bound value in the related text box.
  17. Input the number 50 in the Minimum text box and the number 160 in the Maximum text box.
  18. Click to select the left first column of the blue band in the chart (select only the first column not many).
  19. Then click the Chart Design tab —> Add Chart Element item in the Chart Layouts group.
  20. Click the Data Labels —> Center item to add a data label at the center of the left first column.
  21. Right-click the left first blue column and click the Format Data Label… menu item in the pop-up menu list to open the Format Data Label pane on the excel worksheet’s right side.
  22. In the Label Options —> Label Contains section, check the checkbox Series Name and uncheck the checkbox Value, if you uncheck the Value checkbox first, then you can not find the Series Name checkbox because the data label is removed.
  23. Do the same to the other 2 bands ( yellow band and grey band ).
  24. Right-click the yellow line or blue line, then click the Add Data Labels —> Add Data Label to add the data label value in the line marker points.
  25. Select any one and only one marker point in the line graph and right-click it, then click the Format Data Label… menu item in the pop-up menu list.
  26. Check the checkbox Series Name on the excel right side Format Data Label pane, then you can see the below excel band chart.
    excel-horizontal-band-chart-example

Subscribe to learn more excel tricks.

We don’t spam!

close

Subscribe to learn more excel tricks.

We don’t spam!

0 0 votes
Article Rating
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x