Are you looking to add an average line to your charts in Google Sheets? It’s easier than you think! In this article, we’ll show you step-by-step how to create an average line in both column and line charts. So let’s get started!
Average Line in Column Chart in Google Sheets
To begin, let’s assume we have a set of sales data that we want to plot in a column chart. But we also want to add an average line to visualize the overall average sales. Here’s how you can do it:
-
Find the average of the sales data. Let’s say the average is 36950.00. You can use the formula
=average(B2:B13)to calculate the average. -
Instead of using the formula directly, we will use it as an Array Formula in an infinite range. Enter the formula
=ArrayFormula(if(len(A2:A),average($B$2:$B),))in cell C2. This will automatically fill the entire column C with the average value, as long as column A is not blank. -
Select the entire range of columns A, B, and C by clicking on the column headings.
-
Go to Insert > Chart.
-
In the Chart editor sidebar panel, select the chart type “Combo chart”.
-
Under the “Customize” tab, go to “Series” and make sure the following settings are selected:
- Sales Volume: Column
- Average: Line
That’s it! You’ve successfully added an average line to your column chart in Google Sheets. You can also customize the color of the columns if you want to. For more information on customizing data point colors in charts, check out this article.
Average Line in Line Chart in Google Sheets
The average line can also be added to a line chart in Google Sheets. Here’s how:
-
Follow the same steps as above to create a combo chart with the sales data.
-
In the “Customize” tab, click on “Series”.
-
Select the series “Sales volume” and change the “Type” to “Line”.
And there you have it! You’ve successfully added an average line to your line chart in Google Sheets.
Adding an average line to your charts can provide valuable insights into your data. It helps you visualize the overall trend and compare individual data points to the average. So give it a try and see how it enhances your charts.
For more tips and tricks on working with charts in Google Sheets, check out Crawlan.com. They offer a wealth of resources to help you make the most out of Google Sheets.
Happy charting!
