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.
   
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.
    
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.