Set custom data labels in a chart

The following VBA (Visual Basic for Applications) procedure will let you use cell values as data labels in your charts in Excel for Mac. To utilize the procedure do the following:

  1. In Excel, click ToolsMacros > Record New Macro.

  2. In the Store Macro in: drop-down box, select Personal Macro Workbook.

    Note: By storing the VBA procedure in the Personal Macro Workbook, you can then use it in any instance of Excel. This means it's not just limited to use in a single workbook.

  3. Press OK.

  4. Go to Tools > Macros > Stop Recording.

  5. Go to Tools > Macros > Visual Basic Editor.

  6. The Visual Basic Editor (VBE) will open above Excel. In the navigation pane on the left, expand the VBAProject (PERSONAL.XLSB) folder to show Module1.

  7. Double-click on Module1 to display the Code pane, which will open on the right side of the VBE window.

  8. The VBA code that was created when you recorded a new macro isn't necessary, so select it all, then press Delete

  9. Next, select the VBA code in the text box below, press Ctrl+C to copy it, then paste it back in the Code pane with Ctrl+V. You can now exit the Visual Basic Editor and go back to Excel.

  10. Test the macro by creating a chart with data labels. Next, select any data label on the chart, and run the macro by clicking Tools > Macros > Macros. Select SetCustomDataLabels, then press Run. You will automatically be prompted to select your data label range, so do that, then press OK.  

    Note: VBA procedures can't be undone, so make sure to try this on a copy of a workbook. If you do run the code and don't want to keep the results, you'll need to close the workbook without saving it.

  11. When you quit Excel, make sure you choose to save changes in PERSONAL.XLSB so the macro will be available in the future.

  12. Anytime you want to add or remove data labels from a chart, select the data labels and run the macro.

  13. You can use Excel > Preferences > Ribbon & Toolbar to add a button to run the macro from the ribbon or toolbar.

VBA Procedure

Option Explicit

Sub SetCustomDataLabels()
' make sure a series is selected
    If TypeOf Selection Is DataLabels Or TypeOf Selection Is Point Then
        Selection.Parent.Select
    ElseIf TypeOf Selection Is DataLabel Then
        Selection.Parent.Parent.Select
    End If

    If TypeOf Selection Is Series Then
        Else
        MsgBox "Select a chart series and try again."
        Exit Sub
    End If

    If Selection.HasDataLabels Then
        'If the data labels from cells are already showing, stop showing them and exit.
        'If labels include other info (e.g., values or categories) this will still appear
        If Selection.DataLabels.ShowRange Then
            Selection.DataLabels.ShowRange = False
            Exit Sub
        End If
    End If
 
'Use the InputBox dialog to set the range for the data labels
    On Error Resume Next
        Dim rng As Range
        Set rng = Application.InputBox(Prompt:="Select data label range.", Title:="Data Label Range", Type:=8)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub ' clicked cancel
        If Selection.HasDataLabels Then
            'This will include the new text from cells into existing data labels
        Else
        'Otherwise add data labels (empty labels)
            Selection.HasDataLabels = True
            Selection.DataLabels.ShowValue = False
        End If
    
    'Create a string that includes the sheet name and range reference.
    Dim rngAddress As String
        rngAddress = "='" & rng.Worksheet.Name & "'!" & rng.Address(RowAbsolute:=True, ColumnAbsolute:=True, External:=False)
        Selection.DataLabels.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, rngAddress, 0
        Selection.DataLabels.ShowRange = True

End Sub

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×