Software

Format Excel column charts to match data series values

It would be great if you could change the color of certain Excel columns so they would stand out; unfortunately, Excel does not support conditional formatting of data series objects. Instead, here's how to set up your spreadsheet so that Excel will format those months as a separate data series with a different color.

Let's say that each month you provide your sales team with a column chart that compares year-to-date monthly sales. You want to draw attention to the columns representing the months when the salespeople did not meet their goals. It would be great if you could change the color of those columns so they would stand out; unfortunately, Excel does not support conditional formatting of data series objects. You could set up your spreadsheet so that Excel will format those months as a separate data series with a different color. 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 Meets Goal in C1.
  16. Enter Below Goal in D1.
  17. Select C2:C7.
  18. Enter this formula in C2: =IF(B2>=600,B2,NA()).
  19. Press [Ctrl][Enter].
  20. Select D2:D7.
  21. Enter this formula in D2: =IF(B2<600,B2,NA()).
  22. Press [Ctrl][Enter].
  23. Select C1:D7.
  24. Press [Alt][F1].
  25. Right-click any column and select Format Data Series.
  26. Under Options, change the Series Overlap to 99% and the Gap Width to 0.

As you add rows to the chart, the column where the data automatically falls will determine its series color in the chart.

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