How to Add a Marker Line in Excel Graph (3 Suitable Examples)

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.


Download Practice Workbook

Download this practice workbook below.


3 Suitable Examples to Add a Marker Line in Excel Graph

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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • Select cell D5 and enter the following formula:
=IF(MAX($C$5:$C$18)=$C5,1,"")

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • 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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • 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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • 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 is 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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • 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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

  • 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.

Adding Marker Line in Line Chart to Add a Marker Line in Excel Graph

Read More: How to Add a Vertical Dotted Line in Excel Graph (3 Easy Methods)


Similar Readings


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.

Marker Line Addition in Scatter Plot to Add a Marker Line in Excel Graph

  • 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.

Marker Line Addition in Scatter Plot to Add a Marker Line in Excel Graph

  • 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.

Marker Line Addition in Scatter Plot to Add a Marker Line in Excel Graph

  • After then, 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 Make a Line Graph in Excel with Multiple Lines (4 Easy Ways)


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.

Place a Marker Line in 2D Column 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.

Place a Marker Line in 2D Column Graph in Excel

  • 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.

Place a Marker Line in 2D Column Graph in Excel

  • 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.

Place a Marker Line in 2D Column Graph in Excel

  • 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.

Place a Marker Line in 2D Column Graph in Excel

  • Then select the dataset and right-click on it.
  • From the context menu, click on Select Data.

Place a Marker Line in 2D Column Graph in Excel

  • 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.

Place a Marker Line in 2D Column Graph in Excel

  • 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.

Place a Marker Line in 2D Column Graph in Excel

Read More: How to Make Line Graph in Excel with 2 Variables (With Quick Steps)


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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo