- #Excel pivot chart swap axis with secondary how to#
- #Excel pivot chart swap axis with secondary series#
Right-click the chart, select "Change Chart Type" from the pop-up menu to open the "Change Chart Type" dialog box. Change the bar that represent the "Pass Rate" to a line chart. Select any cell in the table(such as A2), press Ctrl + A to select the entire table, select the "Insert" tab, click the "Insert Column or Bar Chart" icon, and select the first "Clustered Column" under the "2-D Column" in the expanded style, the selected chart is generated from the data of table to be selected and moved to the right of the table the operation process steps are shown in Figure 1:Ģ. If you want to make a dual Y axis chart of Production, Quantity Passed, and Pass Rate, one Y axis represents the Production and Quantity Passed, and the other Y axis represents the Pass Rate.
#Excel pivot chart swap axis with secondary how to#
I, How to make Excel chart with two y axis case 1: Make a bar chart and line chart with two axis In addition, the double Y axis can also set the coordinate axis scale and direction.
#Excel pivot chart swap axis with secondary series#
Since the column bars belong to different Y axes, only adjust the "series overlap and Category distance" is not implemented, and a blank series placeholder needs to be added. How to make dual axis chart in excel? There are generally two ways to make a double Y axis chart, one is a combination of a column chart and a line chart, and the other is a combination of a column chart and a column chart the former is relatively simple, and the latter often appears the problem of overlapping column bars needs to be separated and well spaced. For example, to make a chart of product output and pass rate, one Y-axis represents the output and the other Y axis represents the pass rate. The axis options area in the Format Task pane includes all standard value axis settings.If there are multiple columns of values in the table, especially in the case of percentages, it is usually necessary to make a dual axis chart, one Y axis represents the specific value, and the other Y axis represents the percentage in Excel. You can see this is a much simpler way to add a secondary axis.įinally, notice the secondary axis is an ordinary value axis. Excel already suggests a line, so the only thing I need to do is tick the secondary axis box. Then I'll right-click, select change chart type, and select combo. Let's try that out.įirst, I'll undo back to the point where I first added the conversion data series. So, as you're watching this, you might guess that changing the chart type is a better way to do this from the start. So I just need to change conversion to a line. By adding the secondary axis, the chart has already changed into a combo chart. So, right click the chart and choose change chart type. The conversion columns sit directly on top of the orders.įor a chart like this, you'll probably want to use a line instead. Now you can change the series to the secondary axis. Now if the Format Task pane isn't open, select the conversion series, then use control 1 to open the Format Ttask pane. Then visit Series Options in the Format Task pane. Its hard to select because it's so small, so you can right-click the chart and use the mini toolbar. To do this, start by selecting the conversion series. Now, I want to point out that you can add a secondary axis with the format task pane, though it's not ideal, for reasons I'll explain. The solution is to plot this data on a secondary vertical axis, configured for percentages. The conversion rate is measured as a percentage, where all numbers are less than 1, while the orders are much bigger numbers.Īs a result, the conversion rate columns are too short to see. I can easily do that by selecting the chart and dragging the data area to include conversion.īut now we see a problem. Now, let's say I'd like to also add the conversion rate into this chart. Let me first insert a column chart to plot orders per channel. Here we have some website data that includes orders and the conversion rates for five different channels. In this video, we'll look at how to add a secondary axis to chart.