Creating a data series with two different formatting schemes

**skyscraperrrrrfan** · 07-05-2018, 10:45 AM

I'm trying to post a picture but I guess I can't since I just made this account. Basically I have a graph right now that is three distinct data series: an Original Forecast line which runs all the way from Jan - Dec, An "Actuals" line which runs from Jan - June, and a "Reforecast" line which runs from July - Dec. The point is to make the Actuals and Reforecast data series into one continuous line that can be compared to the Original Forecast. Right now they are two different data series that just start and end on the same number so the line looks continuous. The Actuals line is a solid colour, and the Reforecast is a dashed line so you can tell where the switch happens.

It looks pretty much exactly how I want it too right now, but the back-end is a bit messy and I can see it being a pain to readjust where the actuals end and the reforecast begins every month going forward (for approximately 12 divisions). I'm wondering if there is an easier way to make actuals and reforecast one data series where the formatting changes from solid line to dashed line at various points in the series. I guess if I do that though I may lose the separate legend entries for the two series which would be a different problem.

Thanks.

**than_gold** · 07-05-2018, 10:49 AM

Welcome to the forum.
Could you please provide the sample excel for better understanding of your issue & to assist you as well in better way.

**MarvinP** · 07-05-2018, 10:52 AM

Hi ssfan and welcome to the forum,

As I read your question I kept thinking a Conditional Formatting based on Today() was what you needed. Andy Pope is a Forum Guru here and is the expert graph person I know about. He has some examples on his own web pages at:

http://www.andypope.info/charts/conditionalline2.htm

See if his example helps with your problem.

**than_gold** · 07-05-2018, 11:01 AM

Thank you MarvinP for giving intro to Andypope, by the way, can you share your personal blog website pls..

**skyscraperrrrrfan** · 07-05-2018, 11:09 AM

Hi MarvinP, That conditional formatting looks pretty similar to what I would need except I would be switching it to test for a value (whatever month it is today) on the x axis instead of the y. I'll try playing around with it

**skyscraperrrrrfan** · 07-05-2018, 11:10 AM

forecast.PNG

The picture worked. this is what I have and basically exactly how I want it to look. The two green lines are actually separate data series that connect at the current month. It would be easier if they could become one series with different formatting.

I can upload the excel file if necessary, but it will take me some time to scrub all of the company-sensitive data out of it. this is where all of the data from the graph comes from:

forecast data.PNG

The last two lines are not data series, they're just the variance values that I use to label the difference between the two lines at the current month and the end of the year.

**MrShorty** · 07-05-2018, 11:17 AM

It would be easier if they could become one series with different formatting.

Can I ask why you say this? I ask because, in my experience, when you want a line with two different types of formatting, it is almost always easier to do it like you have now -- two separate data series.

**skyscraperrrrrfan** · 07-05-2018, 11:28 AM

My thought was that I would have to redefine the range of the two series every month as actuals now stretches one month further and reforecast becomes one month shorter. If there was an easy conditional formatting I could apply where I just said, "switch colours in August instead of July now" it would be easier to update.

Edit: I guess I don't have to redefine the range as long as I delete out the old value as the month changes. In July I just delete the reforecast number from May and June and with no values the graph shouldn't show anything.

**MrShorty** · 07-05-2018, 11:46 AM

Edit: I guess I don't have to redefine the range as long as I delete out the old value as the month changes. In July I just delete the reforecast number from May and June and with no values the graph shouldn't show anything.

That's the way I would do it, if the values for Actuals and Reforecast are hand entered. If they are determined by a formula, nest the existing formula inside of an IF() function that will return the N/A error when it should not be included in the series =IF(test for when to include this value in this series,current formula,NA()).

**skyscraperrrrrfan** · 07-05-2018, 11:52 AM

Ya I think I might just do that. I'll write an if test where I can enter the current month in one cell for all my graphs, and have any months < current go blank.

Thanks a lot.

**MrShorty** · 07-05-2018, 12:04 PM

Sounds like that might work. Just be very careful with what you mean by blank. Usually, that means return something like empty string "", which is a text string and Excel's charts will treat it as a 0. Excel's charts (usually) know how to ignore N/A errors, which is why I suggested returning NA(). A more detailed essay: http://peltiertech.com/mind-the-gap-...g-empty-cells/

Creating a data series with two different formatting schemes

LinkBack

Thread Tools

Rate This Thread

Display

Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Re: Creating a data series with two different formatting schemes

Thread Information

Users Browsing this Thread

Similar Threads

Creating Charts with Many Data Series'

[SOLVED] Design Advice for WorkSheets Color Schemes and Formatting

Creating many copies of a graph that DON'T all reference the same data series?

Creating multi-series X,Y scatter plot with data organized as (series name, x, y)

Creating a chart with data series from several worksheets

creating a series from alphanumeric data?

[SOLVED] Creating a data series using another worksheet?

Bookmarks

Bookmarks

Posting Permissions