MrExcel.com - Your One Stop for Excel Tips & Solutions

Use Formulas for Chart Labels in Excel

July 18, 2023 - by

Use Formulas for Chart Labels in Excel

Excel 2013 introduces a new feature where the chart labels can come from other cells on the worksheet. In the Figure below, formulas in column C build a label to identify the largest and second largest sales amount.

Sales rep names in A. Revenue in B. Two helper cells below the table: B12 shows the top value with =MAX(B2:B10). B13 shows the second largest value with =LARGE(B2:B10,2). The formula in C2 is =IF(B2=$B$12,"Top Rep",IF(B2=$B$13,"Second","")). Copy this formula to C2:C10. Use column C as the source of labels for the chart. Only Flo and Hank will have labels, Flo as the top rep and Hank as second.
Figure 1199. As the results change, the labels will move.

Here are the steps to assign the labels from cells:

1. Select the data and Insert, Recommended Chart, OK.

  • 2. Use the Plus icon to the right of the chart. Add a checkmark to Data Labels.

  • 3. Plus Icon, hover to right of Data Labels. Click Triangle, choose Data Callout.

  • 4. Plus, Data Labels, Triangle, More Options.

  • 5. Click the 3-Column chart icon in the Format Data Labels Task Pane.

  • 6. Click on Label Options.

  • 7. Checkmark Value From Cells.

  • 8. Excel will ask you to highlight the range. In this case, it is C2:C10.

  • 9. Uncheck Category Name and Value.

This article is an excerpt from Power Excel With MrExcel

Title photo by Helena Hertz on Unsplash