For the purpose of better data visualization, we could easily add a marker line. To highlight different types of important information, a marker line is a very wise choice. If you are curious to learn how to add a marker line in an Excel graph, this article may come in handy for you. In this article, we are going to discuss, how you can add a marker line in Excel with elaborate explanations.
How to Add a Marker Line in Excel Graph: 3 Suitable Examples
Adding a marker line could literally make the data visualization easier. We will use the IF, MAX, and AVERAGE functions to create those marker lines.
Example 1: Add Marker Line in Line Chart
Using a helper column, we can add a marker line in the line chart. We will also use the IF and the MAX functions.
Steps
- We have the information in which we are going to add the marker in the line chart.
- Select cell D5 and enter the following formula:
=IF(MAX($C$5:$C$18)=$C5,1,"")
- Then drag the Fill Handle to cell D18.
- Doing this will search for the maximum value and compare each cell value with the maximum value.
- After that, it will put 1 on the side of the maximum value.
- Then select the whole data range B4:D18 and then go to Insert Tab > Charts group.
- From there, click on the Recommended Charts.
- After that, a new window will open. In that window, select the Clustered Column chart as shown in the image below.
- Click OK after this.
- You will see a new chart with both the Columns (Revenue) and Line (Column 1) present.
- Right-click on the chart and select Change Chart Type.
- Another new window will open and from that window, select the Chart Type for the Revenue as Line with Markers.
- And set chart type for Column 1 as Clustered Column.
- Click OK after this.
- Then you will notice that the chart is now modified according to our settings.
- Now right-click on the rightmost axis.
- And from the context menu, click on the Format Axis.
- In the Format Axis side panel, go to the Axis Options and then set the Maximum value as 1.
- After setting the Max value to 1, we will see that the column has now touched the ceiling of the chart.
- Next, click on the plus sign on the side of the chart and then Error Bars > More Options.
- After clicking the More Options, we will see a new dialog box named Add Error Bar.
- From that window, click on Column 1 and then click OK.
- A new side panel will open on the right side of the sheet.
- From that panel, select Minus from the Vertical Error Bar.
- And set the percentage to 100% in the Error Amount.
- The chart is now having a vertical line to the top of the ceiling.
- Now select the column, and right-click on it.
- From the context menu, click on the Format Data Series.
- In the side panel, select No Fill in the Fill Options.
- And No Line in the Border options.
- Finally, you will see the marker line added for the maximum value in your Excel graph.
Read More: How to Draw Target Line in Excel Graph
Example 2: Add Marker Line in Scatter Plot
We can plot the maximum value of datasets and mark them using error bars. We will use the MAX function to estimate the maximum value.
Steps
- In the beginning, select the range of cells B5:D18, and then create a scatter plot out of it.
- The scatter plot will look like the below image.
- Now select cell G5 and enter the following formula:
=MAX(C5:C18)
- Then select cell H5 and enter the following formula:
=MAX(D5:D18)
- Then right-click on the chart and from the context menu, click on Select Data.
- Then select the cell G5 in the Series X values.
- And select cell H5 in the Series Y values.
- Click OK after this.
- After this, you will see the data point is now showing in the chart. In orange color.
- Select the newly added data point and click on the Plus Sign.
- Then go to Error bars > Percentage.
- Two error bars, one in the vertical direction and another one in the horizontal direction are shown on the data point.
- Click on the Horizontal Error bar and then right-click on it.
- From the context menu, click on the Format Error Bar.
- There will be a new side panel named Format Error Bar. From that panel, select Both in the Direction options.
- And set the Percentage to 0% in the Error Amount.
- Then select the Vertical Error bar without closing the side panel.
- Select Minus in the Direction option.
- And set the Percentage to 100% in the Error Amount.
- After that, you will see the marker line in the chart which marks the Maximum values of both the Unit Price values and the Revenues.
Read More: How to Draw a Horizontal Line in Excel Graph
Example 3: Add Marker Line in 2D Column Chart
We can estimate the average of a range of data and mark it in a 2D column chart with the help of the errors bar. We will use the Average function to achieve this.
Steps
- We need to mark the average value of the products listed here in the chart.
- To do this, first select cell E5 and enter the following formula:
=AVERAGE(C5:C18)- Repeat the same process for cell E6.
- And input 0 in cell G5 and input 1 in cell G6.
- Then select the range of cells B4:C18, and then go to Insert tab > Charts group.
- From there click on the 2-D Bar.
- Then you will notice that there is a 2-D bar chart about the price of products.
- After selecting the 2-D chart, we will see that there is a chart demonstrating the price of the products.
- Select the chart and right-click on it.
- From the context menu, click on Select Data.
- Then click on the Add Button in the following dialog box.
- Then in the Edit Series dialog box, select E5:E6 in the Series Values range box.
- Click OK after this.
- Then you will see the Average value in the chart.
- Select the average data bar and right-click on it.
- From the context menu, click on the Change Series Chart Type.
- Then in the change chart type window, select Clustered Bar in the Unit Price.
- And then select Scatter with Straight Line in the Average.
- Click OK after this.
- Then select the dataset and right-click on it.
- From the context menu, click on Select Data.
- There will be a new dialog box named Select Data Source.
- On that box, click on the previously created Average.
- Then click on Edit.
- Then on the Edit Series dialog box, select E5:E6 in the Series X values.
- And then select G5:G6 in the Series Y values.
- Click OK after this.
- After clicking ok, you will see that there is an orange line marking the Average of the Products Unit Prices showing.
- Right-click on the axis and from the side panel, click on the labels
- Then from the position of the label, select None from the drop-down menu.
- After some modifications, we got the marker line that will denote the Average value of the Product prices.
Read More: How to Shade Area Between Two Lines in a Chart in Excel
Download Practice Workbook
Download this practice workbook below.
Conclusion
Here, we created three different types of marker line addition examples in the 2D chart, Scatter plot, Line chart, etc. We generally marked Average values and Maximum values.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section.
