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).


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

  • Is there a way to do it without having to use the "series" column with the A or B between different data sets? Right now, my columns are: Date, Series 1, Series 2, Series 3, Past – juggling_pro Feb 17 '16 at 14:52
  • Have you tried adding both series1 and series2 to values? It should work. – Máté Juhász Feb 17 '16 at 15:06
  • But for your picture, you had the "value" column holding both sets of data, and separating the two by the "series" column with A and B. If I added both series1 and series2 to values, what would I have in my "series" column? – juggling_pro Feb 17 '16 at 16:35
  • Then you don't need to have a "series" column. I've done my best guess to answer your question based on the data you've provided. Please play around a bit with the pivot chart and I'm sure you'll find the solution. – Máté Juhász Feb 18 '16 at 4:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.