How To Add A Descriptive Label In Excel

The flick in a higher place shows a chart that has custom information labels, they are linked to specific cell values.

This means that y'all can build a dynamic nautical chart and automatically modify the labels depending on what is shown on the chart.

I accept demonstrated how to build dynamic data labels in a previous article if y'all are interested in using those in a chart.

In a post from March 2013 I demonstrated how to create Custom data labels in a chart.

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

Yous can't apply the same technique for an x y scatter chart, equally far equally I know.

What's on this page

1. How to apply custom data labels in Excel 2013 and later versions
   1. Video
   2. How to add together data label shapes
   3. How to rearrange data labels
   4. Get Excel file
2. Workaround for before Excel versions
   1. Excel Macro - Apply custom data labels
   2. Where to copy the code
   3. How to utilise macro
   4. Get Excel file

Luckily the people at Microsoft have heard our prayers.

They take implemented a feature into Excel 2013 that allows you to assign a cell to a chart information point label a, in an x y scatter chart.

I will demonstrate how to do this for Excel 2013 and later versions and a workaround for earlier versions in this article.

1.1 How to employ custom information labels in Excel 2013 and afterwards versions

This instance nautical chart shows the distance betwixt the planets in our solar system, in an x y scatter chart.

The first three steps tell you how to build a besprinkle nautical chart.

1. Select cell range B3:C11
2. Get to tab "Insert"
3. Press with left mouse push on the "scatter" button
4. Press with right mouse button on on a chart dot and printing with left mouse button on on "Add together Data Labels"
5. Press with right mouse button on on any dot again and press with left mouse push button on "Format Data Labels"
6. A new window appears to the right, deselect Ten and Y Value.
   
7. Enable "Value from cells"
   
8. Select prison cell range D3:D11
9. Printing with left mouse push button on OK

This is what the chart shows, equally you can run into you need to manually rearrange the information labels and add information characterization shapes.

Back to top

ane.1 Video

The following video shows you how to add information labels in an X Y Scatter Chart [Excel 2013 and after versions].

Back to top

Learn more

Centrality | Nautical chart Expanse | Chart Title | Axis Titles | Axis lines | Chart Legend | Tick Marks | Plot Area | Data Series | Information Labels | Gridlines

1.ii How to add data label shapes

1. Printing with correct mouse push on on a data label.
2. Press with mouse on "Change information label shapes".
3. Select a shape.

Back to top

1.3 How to alter data label locations

You can manually press with left mouse button on and drag data labels as needed. You can too allow excel change the position of all data labels, choose between eye, left, right, above and beneath.

1. Press with right mouse button on on a data label
2. Press with left mouse push on "Format Data Labels"
   
3. Select a new label position.

Back to pinnacle

Dorsum to top

Larn more than

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

two. Workaround for earlier Excel versions

This workaround is for Excel 2010 and 2007, it is best for a pocket-size number of chart information points.

1. Press with left mouse push on twice on a characterization to select it.
2. Press with left mouse button on in formula bar.
3. Blazon =
4. Employ your mouse to press with left mouse button on a prison cell that contains the value yous want to use.
5. The formula bar changes to mayhap =Sheet1!$D$iii
   
6. Echo pace 1 to five with remaining information labels.

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

The following animated motion picture demonstrates how to link a jail cell value to a specific chart data bespeak.

If your chart has many information points this method becomes chop-chop tedious and time-consuming.

I take automated these steps for you in a macro that you can read almost below, at that place is too an example workbook that y'all can get.

Learn more

Column | Bar | Line | Area | Pie | Doughnut | Besprinkle | Bubble |  Funnel | Stock | Candlestick | Surface | Radar | Map

Back to acme

2.1 Apply custom data labels (VBA Macro)

This macro adds a cell reference to each data label, the value in the referenced jail cell is then linked to the label. If y'all change the value in the prison cell the label value changes also.

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

Learn more

Waterfall | Treemap | Sunburst | Histogram | Pareto | Box & Whisker

Back to top

two.two Where to put the code?

1. Copy macro (CTRL + c)
2. Become to the VB Editor (Alt + F11)
   
3. Press with left mouse button on "Insert" on the meridian menu.
4. Press with left mouse button on "Module" to insert a code module to your workbook.
5. Paste code to the module. (CTRL + five)
6. Render to Excel.
7. Save your workbook as a macro-enabled workbook (*.xlsm file).
   If y'all don't the macro will be gone the next time y'all open the aforementioned workbook.

Back to elevation

Larn more

Pointer | Normal distribution | Equation | Comparing | Heat map | Gantt

two.iii How to employ macro

1. Select the 10 y scatter chart.
   
2. Press Alt+F8 to view a list of macros available.
   
3. Select "AddDataLabels".
4. Printing with left mouse button on "Run" push button.
5. Select the custom data labels y'all desire to assign to your chart.
   Make sure yous select every bit many cells as there are data points in your chart.
   
6. Printing with left mouse button on OK push.

Dorsum to pinnacle

Back to elevation

Recommended articles

Recommended articles

Recommended articles

Add pictures to a nautical chart axis
This article demonstrates how to insert pictures to a nautical chart axis, the flick above shows a cavalcade nautical chart with state […]

Recommended manufactures

Recommended articles

Dynamic Gantt charts
Today I am going to bear witness yous how to create a dynamic Gantt nautical chart in excel 2007. A Gantt nautical chart helps […]

Recommended manufactures

Latest updated articles.

More than 300 Excel functions with detailed data including syntax, arguments, render values, and examples for almost of the functions used in Excel formulas.

More than than 600 formulas organized in subcategories.

Excel Tables simplifies your piece of work with data, adding or removing information, filtering, totals, sorting, enhance readability using prison cell formatting, cell references, formulas, and more than.

Allows you lot to filter data based on selected value , a given text, or other criteria. Information technology too lets you filter existing data or move filtered values to a new location.

Lets you control what a user can type into a jail cell. It allows you lot to specifiy conditions and show a custom message if entered information is not valid.

Lets the user work more efficiently past showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a prison cell.

Lets you lot proper name one or more cells, this makes it easier to discover cells using the Name box, read and understand formulas containing names instead of cell references.

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision issues similar permutations and combinations.

An Excel feature that lets you visualize data in a graph.

Format cells or cell values based a condition or criteria, there a multiple built-in Provisional Formatting tools y'all can use or use a custom-made conditional formatting formula.

Lets you quickly summarize vast amounts of data in a very user-friendly mode. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA stands for Visual Basic for Applications and is a reckoner programming language adult by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

A program or subroutine built in VBA that anyone tin create. Use the macro-recorder to apace create your own VBA macros.

UDF stands for User Defined Functions and is custom congenital functions anyone can create.

A list of all published articles.

How To Add A Descriptive Label In Excel,

Source: https://www.get-digital-help.com/custom-data-labels-in-x-y-scatter-chart/

Posted by: johansenunly1998.blogspot.com

Share this post