While creating a chart in Excel, you can use a horizontal line as a target line or an average line. Go To Insert → Charts → Column Charts → 2D Clustered Column Chart. You’ll get a chart like below. Now, you have to change the chart type of target bar from Column Chart to Line Chart With Markers.
Combo charts combine more than one Excel chart type in the same chart. One way you can use a combo chart is to show actual values in columns together with a line that shows a goal or target value. In the chart shown in this example, daily sales are plotted in columns, and a line shows target sales of $500 per day. This example uses a combo chart based on a column chart to plot daily sales and an XY scatter chart to plot the target. The trick is to plot just one point in the XY scatter chart, then use error bars to create a continuous line that extends across the entire plot area. Data The data used to build this chart is shown below: How to create this chart.
Select the sales data and insert a Column chart. Select target line data and copy.
Then select chart paste special:. Column chart after pasting target line data:. Right-click chart, then change chart type to Combo Chart:. Make Target Series an XY Scatter Chart. Select target data point, then add error bars to chart. Select X (horizontal) error bar; set Fixed value = 1.
Select Y error bars, then press delete to remove:. Current chart after adjusting X error bars and removing Y error bars:.
![Excel chart change line to bar Excel chart change line to bar](/uploads/1/2/5/6/125632571/478385273.jpg)
Select secondary value axis, then set Maximum bounds = 1, and Label Position = 'None':. Select and delete secondary vertical axis. Select horizontal error bar and set end style = 'No Cap':. Set Target data series marker to 'None':. Current chart with sales in columns and target as edge-to-edge line: From this point, you have the basic column chart with edge-to-edge target line.
You can now format the chart as you like: add a title, set color and width for the target sales line, add data labels, etc. Note: I learned the error bar approach from.
![Excel chart change line to bar Excel chart change line to bar](/uploads/1/2/5/6/125632571/571245772.png)
Year Expenses Sales Profit 2002 0 9% 2003 0 4% 2004 0 6% 2005 0 6%. Make sure that the year column is in text format. You cannot choose number format and convert to text. You need to place a single quotein front of each year to make the number into text.
Notice that the numbers are left aligned. Profit: =(sales-expenses)/sales. Highlight the entire table and Insert Column Chart and choose 2-D Column. It MUST be this format or the combination option (secondary axis) will not be available.
Move chart to a new sheet. Profit series are visible (they are the thin lines on the chart). They are right of the red bars.
They are this way because the other numbers are larger than the percentages. Right Click on Profit series and choose Format Data Series and choose Secondary Axis and Close. Now the percentage numbers are on the right side of the chart and the flat lines are bars. They are on the secondary axis now. This chart sits on the top of the original chart. Right Click on the Profit Series and change chart type to a line graph.
And there you have it–a COMBINATION chart!!!