Help making a trend graph with multiple values?

Hello Excel Forum!

So I just started a new job and I was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this). Now I had taken an excel class in college...like 5 years ago and really do not remember much. So a little direction would be very helpful! I am not really sure what the best way to display this is as well so if anyone has any ideas my future career would be very grateful! :P

EXCEL EXAMPLE.PNG

It's kind of difficult to show trends with such limited data especially with no context around it.
One of your cells is "Month to Date". Well how far are we into the month?
One is "Year to Date". Well which month are you looking at? Are you using a financial/fiscal year or a calendar one? When does the year start?
The "Total" one is ambiguous too. Total since when??

Setting up graphs with trend lines is simple, but you have to get the data right first.

Hello BadlySpelledBuoy!

Thanks for responding so fast! But we were not sure about how to arrange the data in the excel sheet. I will try to give some context and maybe that will help!

We receive an activation/de-activation report everyday and I need to log it into this excel file and have it adjust the graph. There are also multiple Brands, each having their own activation's and de-activation's. So my boss wants to see the trends over time. Please let me know if I am going about this wrong!

Have a look at the attached. Is this anything like you're trying to do? (Obviously the data is just random numbers)

The idea would be that you would put your daily Deactivations and Activations numbers for each brand in columns B:K against the appropriate dated row.

The table to the right just consolidates those daily numbers into monthly figures.
Same could easily be done for weekly or yearly or anything else you may need.

The graph is then plotted from those.

I might be completely off course with this interpretation, but the brief was limited so I'm clutching at straws a little.

BSB.

Yes! I that looks like exactly what I need to make! Thank you so much! I'll apply it to my values and post back when I have finished....just to make sure I didn't mess anything up :P

Thanks again!

No problem. Shout if you need more help with it.

Hey! I looks like I do need a little more help :P

I will not be able to enter values for daily activation's/de-activation's. The reports I will be receiving are monthly. In the document you previously provided if I entered daily values it would alter the monthly values and therefor the graph. Could you possibly show me or tell me how to make it so I only enter monthly values to alter the graph? Also, within excel is there a way I could have it automatically subtract the monthly de-activation's from the monthly activation's giving me a total user count? I drew and very bad example graph in paint. The graph you provided me with is quite nice but in retrospect I think it has more information than my boss was looking for.

Thank you BSB!!!!

example graph.png

You mentioned earlier multiple brands. Are you wanting to show those separately or consolidate everything into one overall set of figures and show just that on the graph?

Yes, there would be multiple brands but I would also like to include a "Grand Total" line of all subscribers.

Do you want to show all the brands on the graph or just the grand totals?

I think it would end up being two separate graphs. One with all the brands and one with the total of all the users...across all the brands. If that makes sense. I am figuring this all out as I go :P

Have a look at Sheet2 in this version.

One table, two graphs.
Graph 1 = Overall deactivations and "net users" only. Net users being total activations minus total deactivations.
Graph 2 = The same but detailing each brand.

Is that of any use to you?

BSB.

Hello BSB!

I had a few more questions about that graph/excel sheet you made for me. I was wondering if I could ask a few questions and have you look at what I have done to your sheet you provided. I need help figuring out a good way to organize it!

Let me know if that would be okay!

Thank you!

Here to help so fire away

First off! Thank you for the help I really appropriate it!

So I made two terrible drawings in paint that should explain what I am going for but feel free to tell me to elaborate if needed. What you made was exactly what I asked for, however, I need to display the data a little differently. Figure 1 - Shows an ideal graph but I am not even sure you can do that in excel...if not just let me know aand we can figure out a better way! FIgure 2 - just shows the ideal way the graphs/ data would be organized in the excel sheet. And finaly I will attach the excel document you origionaly sent me...but I changed it a little (adding data and a total section at the bottom)

Let me know what you think! and be critical :p

Seems simple enough. I'm about to leave work so will put it together for you when I get home in about 2hrs.

You wont see this until you are home from work but I think I may have figured out most of it. I just cant figure out how to get the line chart included in the bar graph. I will post the most recent version of the graph!

Plot the series you want as a line on the bar graph too, then right click it and select "change chart type". You can then specify "line graph" for just that series.

I'd do it for you but replying from an ipad and it's not great for doing anything with Excel.

I'll check in when I'm home and in front of a laptop to see how you got on.

I made some more graphs! If you can give me some notes on the latest version it would be greatly appreciated!

Thank you BSB!!

Apologies for not getting around to looking at this yesterday. The pier in my home town caught fire so had to go take photos of that.

The graphs look good to me. I'm a bid of a pedant (perhaps bordering on OCD!) so there are two things I'd alter. One is holding Alt when you're moving or resizing your charts will 'snap to grid' so you can line them up neatly with the cell borders.

The other thing is with line graphs for future months where the value is zero drags the line down and kind of distorts what you're trying to show.
You can get around that by creating a mirror table that plots #N/A for zero values. These errors will not be plotted on the graph so things look neater.
If you look at Sheet1 in the latest attachment and move the chart away from its current position you will see what I mean.