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:
-
In Excel, click Tools > Macros > Record New Macro.
-
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.
-
Press OK.
-
Go to Tools > Macros > Stop Recording.
-
Go to Tools > Macros > Visual Basic Editor.
-
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.
-
Double-click on Module1 to display the Code pane, which will open on the right side of the VBE window.
-
The VBA code that was created when you recorded a new macro isn't necessary, so select it all, then press Delete.
-
Next, select the VBA code in the text box below, press CMD+C to copy it, then paste it back in the Code pane with CMD+V. You can now exit the Visual Basic Editor and go back to Excel.
-
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.
-
When you quit Excel, make sure you choose to save changes in PERSONAL.XLSB so the macro will be available in the future.
-
Anytime you want to add or remove data labels from a chart, select the data labels and run the macro.
-
You can use Excel > Preferences > Ribbon & Toolbar to add a button to run the macro from the ribbon or toolbar.
VBA Procedure |
---|
|
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.