Data label only to the last data point

[sharshra]

I want to display the data point only at the end of line graph. At present, I´m using few calculations in a separate column to find the value of the last cell in a column. Are there any better ways to achieve the same without having an additional column just find out the last cell?

 

[Domenic]

Try the following code...

Sub test()

    Dim targetChart As Chart
    Dim currentSeries As Series
    Dim lastPoint As Point
    
    Set targetChart = ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart 'change the sheet and chart names accordingly
    
    For Each currentSeries In targetChart.SeriesCollection
        With currentSeries
            Set lastPoint = .Points(.Points.Count)
            lastPoint.ApplyDataLabels
            lastPoint.DataLabel.Position = xlLabelPositionAbove
        End With
    Next currentSeries
    
End Sub

Hope this helps!

 

[sharshra]

Thanks, @Domenic. This VBA code is very useful

In this case, I´m looking for any solution thru excel formula or setting, without using VBA. Are there any options?

 

[felixstraube]

Do you always have the same amount of data points?

 

[Domenic]

See if the following link helps . . .

https://superuser.com/questions/1285179/adding-data-label-only-to-the-last-value#:~:text=Select%20just%20the%20single%20point,one%20point%20you've%20selected.

 

[sharshra]

@felixstraube, the data points can vary. It will not remain the same but can grow.

@Domenic, Thanks for sharing the link. It suggests about doing it manually. I´m looking for having something without manual intervention. At present, I have done this, but only after adding an additional column just to get this done. Looking for something if the same result can be achieved without the additional column.

 

[felixstraube]

In that case try this:

My example works with these 2 columns of data:

LineChart.xlsx
	A	B
1
2	Data Col 1	Data Col 2
3	1
4	2
5	5	4
6	6	5
7	10	7
8	11	8
9	12	9
10		13
11
Sheet3

Then create these named ranges:

Name: "Serie1"

=LET( d,OFFSET(Sheet3!$A$3,0,0,MAX(ROW(Sheet3!$A$3:$A$100)*(Sheet3!$A$3:$A$100<>""))-ROW(Sheet3!$A$2)), IF(d="",NA(),d) )

Name: "Serie2"

=LET( d,OFFSET(Sheet3!$B$3,0,0,MAX(ROW(Sheet3!$B$3:$B$100)*(Sheet3!$B$3:$B$100<>""))-ROW(Sheet3!$A$2)), IF(d="",NA(),d) )

Name: "Serie1_Last"

=VSTACK(DROP(EXPAND("", ROWS(Serie1),,NA()),1), TAKE(Serie1, -1))

Name: "Serie2_Last"

=VSTACK(DROP(EXPAND("", ROWS(Serie2),,NA()),1), TAKE(Serie2, -1))

Then you create a line chart, and define the data as follows:

Then in the Chart format you set no marker for the first two series, and the marker of your liking for series 3 and 4.

So for example you would get:

And after adding some values:

Here is a working file for you to try:

LineChart.zip

Let me know if this works for you.

 

[sharshra]

Thank you very much, @felixstraube Your example file is very useful

 

[felixstraube]

Thanks for the feedback. Happy to help.