how to change data point shape in excel

The picture above shows a nautical chart that has custom data labels, they are linked to specific cell values.

This means that you can build a dynamic nautical chart and automatically alter the labels depending on what is shown on the nautical chart.

I have demonstrated how to build dynamic data labels in a previous article if you lot are interested in using those in a chart.

In a post from March 2022 I demonstrated how to create Custom information labels in a chart.

Unfortunately, that technique worked merely for bar and column charts.

You lot can't apply the same technique for an x y scatter nautical chart, as far as I know.

What's on this page

How to apply custom data labels in Excel 2022 and afterward versions
1. Video
2. How to add data characterization shapes
3. How to rearrange information labels
4. Get Excel file
Workaround for before Excel versions
1. Excel Macro - Apply custom data labels
2. Where to copy the code
3. How to use macro
4. Get Excel file

Luckily the people at Microsoft take heard our prayers.

They take implemented a feature into Excel 2022 that allows yous to assign a cell to a chart data point characterization a, in an x y scatter chart.

I volition demonstrate how to do this for Excel 2022 and afterwards versions and a workaround for earlier versions in this article.

1.i How to apply custom data labels in Excel 2022 and later versions

This example chart shows the altitude between the planets in our solar organisation, in an x y scatter chart.

x y scatter chart - custom data labels3

The beginning 3 steps tell you how to build a scatter nautical chart.

Select cell range B3:C11
Become to tab "Insert"
Printing with left mouse button on the "besprinkle" button
Press with correct mouse button on on a nautical chart dot and press with left mouse button on on "Add Information Labels"
Press with correct mouse button on on any dot over again and press with left mouse button on "Format Information Labels"
A new window appears to the correct, deselect X and Y Value.
Enable "Value from cells"
Select prison cell range D3:D11
Press with left mouse push button on OK

This is what the chart shows, as you can meet y'all need to manually rearrange the information labels and add data label shapes.

x y scatter chart excel 365

Dorsum to summit

1.i Video

The following video shows you how to add data labels in an X Y Scatter Nautical chart [Excel 2022 and afterwards versions].

Dorsum to acme

Learn more

one.2 How to add information characterization shapes

Press with correct mouse button on on a data characterization.
Press with mouse on "Alter data characterization shapes".
Select a shape.

Back to acme

1.3 How to change data characterization locations

You can manually press with left mouse push on and drag data labels every bit needed. You can likewise let excel change the position of all data labels, choose between center, left, right, in a higher place and below.

Press with correct mouse button on on a data label
Press with left mouse button on "Format Data Labels"
Select a new label position.

Back to elevation

Back to height

Learn more

Secondary Axis | Linear trendline | Logarithmic Trendline | Moving Average | Error Bars

2. Workaround for earlier Excel versions

This workaround is for Excel 2022 and 2007, it is all-time for a small number of chart data points.

Press with left mouse button on twice on a label to select it.
Press with left mouse button on in formula bar.
Type =
Utilise your mouse to press with left mouse push on a jail cell that contains the value you want to use.
The formula bar changes to maybe =Sheet1!$D$3
Echo step i to 5 with remaining data labels.

Change the value in prison cell D3 and see how the information label on the chart instantly changes.

The following blithe picture demonstrates how to link a cell value to a specific chart data bespeak.

Add custom data labels in a x y scatter chart1

If your nautical chart has many data points this method becomes quickly wearisome and time-consuming.

I accept automated these steps for you in a macro that you lot can read nearly below, there is likewise an case workbook that you can get.

Learn more

Dorsum to meridian

2.one Apply custom data labels (VBA Macro)

This macro adds a jail cell reference to each data characterization, the value in the referenced cell is then linked to the label. If yous change the value in the jail cell the characterization value changes as well.

'Name macro Sub AddDataLabels()  'Enable error handling On Error Resume Next  'Display an inputbox and inquire the user for a cell range Ready Rng = Application.InputBox(prompt:="Select cells to link" _ , Title:="Select information label values", Default:=ActiveCell.Address, Type:=viii)  'Disable error handling On Error GoTo 0  With ActiveChart          'Iterate through each series in chart     For Each ChSer In .SeriesCollection          'Save chart point to object SerPo         Set SerPo = ChSer.Points          'Save the number of points in chart to variable j         j = SerPo.Count          'Iterate though each point in current series         For i = 1 To j              'Enable information label for current chart point             SerPo(i).ApplyDataLabels Type:=xlShowValue              'Save cell reference to chart betoken             SerPo(i).DataLabel.Formula = "=" & ActiveSheet.Name _             & "!" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)         Next     Next End With End Sub

Larn more

ii.2 Where to put the code?

Re-create macro (CTRL + c)
Go to the VB Editor (Alt + F11)
Printing with left mouse button on "Insert" on the elevation menu.
Press with left mouse button on "Module" to insert a lawmaking module to your workbook.
Paste code to the module. (CTRL + five)
Return to Excel.
Save your workbook equally a macro-enabled workbook (*.xlsm file).
If you don't the macro will be gone the next fourth dimension yous open the same workbook.

Back to acme

Larn more

2.3 How to use macro

Select the x y scatter nautical chart.
Printing Alt+F8 to view a listing of macros available.
Select "AddDataLabels".
Press with left mouse button on "Run" button.
Select the custom data labels you lot want to assign to your chart.
Make sure you select as many cells as there are data points in your chart.
Press with left mouse button on OK button.