0

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

Improve this question

1 Answer 1

Reset to default
1

You can achieve this by:

  • adding a calculated column where you insert whether the date is in the past:
    (=TODAY()>A2)
  • 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

Improve this answer
4
  • 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, 2016 at 14:52
  • Have you tried adding both series1 and series2 to values? It should work.
    – Máté Juhász
    Feb 17, 2016 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, 2016 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, 2016 at 4:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .