|
|
|
Bar-Line Combination Chart.It's easy to combine a column chart and a line chart. But how do you combine a line chart and a bar chart, all on the same chart? The trick is to combine bar chart and XY scatter chart, then clean up the axes. In this example we will plot "Ideal" values on a bar chart, and see how the five leading brands measure up, with a line (XY) series for each brand. Start with your data arranged like this:
The {4.5, 3.5, . . . 0.5} values in column H below the blank cell are placeholder values which will be plotted on the vertical axis against the Brand 1 to 5 values which will be plotted on the horizontal axis. These placeholder values are in decreasing order here, because I am going to plot the attributes in reverse order. Select the range that contains the Attributes and the Ideal values, including the blank cell above the Attributes and the cell containing the label "Ideal". Use the chart wizard to make a standard bar chart, shown below. I have formatted the vertical axis and selected these options:
These choices list Attribute 1 at the top and Attribute 5 at the bottom, while keeping the horizontal axis below the chart. (If you do not want to reverse the category order, be sure the Y Axis values for the brand data in the table above are in ascending order.)
Select the cells containing "Brand 1" and the five values, then hold down the Ctrl key and select the blank cell and the cells containing the values 4.5 thru 0.5. Copy. Select the chart, and from the Edit menu select Paste Special, checking these options:
Okay, we just added another set of horizontal bars, not what you wanted. Let's fix it. Right click on this new set of bars (Brand 1), select Chart Type, and pick one of the XY Scatter types (not a Line); I chose the style with both lines and markers.
Well, there's still the matter of the blank legend entry. Click on the new series, and notice in the worksheet, some of the cells have a colored outline? The purple outlines the X values, the blue outlines the Y values, and the green outlines that blank cell.
With your mouse, drag the green outline until it outlines the cell containing "Brand 1". The legend updates this entry.
Repeat this process for the other brands:
For Brands 2-5, Excel remembers that you added Brand 1 as an XY style series, so it applies the same style (until you change it again).
In this example, the 0.5 through 4.5 values work fine for the Y values of the Brand X line series. If you only have 3 categories (attributes), however, or if you have more than about 7, the vertical spacing of the markers is not consistent with the spacing of the bars. To fix it, you need to display the secondary Y axis, apply an appropriate scale, and hide the axis again. Double click on the secondary X axis (top of the chart), and on the Scale tab, check Value (Y) Axis Crosses at Maximum Value, then click OK. This displays the axis on the right edge of the chart (previously this axis was along the left edge of the chart, but didn't appear because the space already contains the primary Y axis).
Double click the now-visible secondary Y axis, and on the Scale tab, set the min to 0 and max to 5 (0.5 below the smallest value in column H to 0.5 above the largest value). On the Patterns tab, select None wherever possible, to hide the axis ticks and labels Finally a little cosmetic formatting. Right click on the chart, and choose Chart Options. Go to the Axes tab, and unselect the Secondary X Axis checkbox. This removes the "extra" horizontal axis at the top of the chart.
|
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |
