Software

Set up minimum and maximum markers on Excel line charts

When working with an Excel chart, you may wish to emphasize which month had the most sales and which month had the least sales. You could manually add a text box or other graphic to the corresponding data points -- or, you could set up your spreadsheet so that Excel charts the minimum and maximum data points for you. Here's how.

Let's say that at the end of each month you provide your sales personnel with a line chart that charts year-to-date sales by month. For the June report, you would like to emphasize which month had the most sales and which month had the least sales. You could manually add a text box or other graphic to the corresponding data points. Or, you could set up your spreadsheet so that Excel charts the minimum and maximum data points for you. To do so, follow these steps:

  1. Enter Month in A1.
  2. Enter January in A2.
  3. Enter February in A3.
  4. Enter March in A4.
  5. Enter April in A5.
  6. Enter May in A6.
  7. Enter June in A7.
  8. Enter Sales in B1.
  9. Enter 1000 in B2.
  10. Enter 500 in B3.
  11. Enter 800 in B4.
  12. Enter 900 in B5.
  13. Enter 1100 in B6.
  14. Enter 300 in B7.
  15. Enter Max in C1.
  16. Enter Min in D1.
  17. Select C1:C7.
  18. Enter this formula: =IF(B2=MAX($B$2:$B$7),B2,NA()).
  19. Press [Ctrl][Enter].
  20. Select D1:D7.
  21. Enter this formula: =IF(B2=MIN($B$2:$B$7),B2,NA()).
  22. Press [Ctrl][Enter].
  23. Select A1:B7.
  24. Click the Chart button.
  25. Select Line and then click Finish.
  26. Select C1:D7.
  27. Go to Edit | Copy.
  28. Click the chart.
  29. Go to Edit | Paste Special and then click OK.
  30. Double-click the Max Data Point shown on the chart.
  31. Under the Patterns tab, set Line to None.
  32. Choose a large circle from the Style drop-down list.
  33. Enter 20 in the Size box.
  34. Click the Background drop-down list and select No Color.
  35. Click the Data Labels tab, select the Series Name check box, and click OK.
  36. Double-click the Min Data Point shown on the chart.
  37. Under the Patterns tab, set Line to None.
  38. Choose a large rectangle from the Style drop-down list.
  39. Enter 20 in the Size box.
  40. Click the Background drop-down list and select No Color.
  41. Click the Data Labels tab, select the Series Name check box, and click OK.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks