On the Series Options tab, under Plot Series On, click Secondary Axis, then Click Close.On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Series "Average".Select the chart, this displays Chart Tools.In the Change Chart Type dialog, choose a basic Line chart.Right-click any of the columns in the Average series on the chart, In the quick menu, click Change Series Chart Type….Under the Insert menu tab, in the Charts group, click the Column button and choose Clustered Column in 2-D Column.In Column C2, type this formula: =AVERAGE($B$2:$B$7) We'll use Average function to calculate the average of entire group. We want to display the average sales for the group in our chart, so we need to insert a new Helper Column that we called Average. You may copy A1:C7 to a new Worksheet to start this tutorial. In this example, we have a group of sales data (range A1:B7).
In this tutorial, I'll show you how to add an Average Line to Column Chart in Excel 2010. Use the sliders in the task pane to achieve this.When you create a chart to analyze the trend of the data, it is useful to add a horizontal Average Line across the chart, so that you can compare the data clearly and easily. You may want to experiment with the gap between the two pies as well as the size of the second plot (Microsoft’s jargon for the second pie). Custom – allows you to manually select a slice and then designate a plot.Percentage value – like value, but here you specify a % threshold.Value – this allows you to specify a threshold under which values are moved to the second pie.In the Split Series menu you will find three more options: Position is not the only way Excel allows you to specify which categories are placed in the second pie. In your task pane you can now change the Values in second plot setting. In Excel 2013, right-click on one of the pies and select Format Data Series. You can change the number of categories that move to the second pie. In my chart data (opposite), I’ve sorted the sales values in descending order so that worst performers end up in the second pie. So how does Excel decide which categories of data to put in the second pie? By default it selects based on position, selecting the last three categories in your chart data. “Other” represents the combined share of the categories in the second pie. Notice that the main pie includes a slice called “Other”. Their slices would have looked tiny on the main pie chart, but moved over to the second pie – they are much easier to read. In the example below Bert, Brenda and Barbara ended up getting the smallest share of sales for 2013. The Pie of Pie chart type is in this menu. To create a Pie of Pie, click into your chart data and on the Ribbon’s INSERT tab click on the Pie button in the Charts group. A pie of pie chart will enable you to move these small slices to a separate pie chart so that they are easier to read. Pie of Pie charts are useful if you have several categories of data that end up representing small slices in your pie chart.