Excel – Displaying data in Excel graph only up to current date

chartsconditional formattingdate timemicrosoft excel

I have an Excel chart that displays 5 sets of data in a single scatter plot, where the X axis is the date of occurrence. In my table, I have both actual values from previous dates and projected values for the upcoming few months. Is there a way using conditional formatting to:

Display the data in different shades? Actual data from previous days would be full color, while projected data for upcoming dates are in a shaded color? The graph should adjust automatically as days progress and actual data is input (if it differs from the projected value).

Best Answer

You can achieve this by:

  • adding a calculated column where you insert whether the date is in the past:
  • insert a pivot chart and set:
    • Legend (series): series and past (column D)
    • axis (categories): date
    • values: value
    • What you can't do is to make the line continuous across time, Excel consider them as different series

enter image description here