Charting in Excel 365

[sfos83]

Hi All,

Essentially, what I'm doing is very basic, but after multiple tutorials and trial-and-error, it eludes me. (my hair is beginning to fall out after 3 days of trying to create one simple chart)

I have 3 columns of data:

1. Date
2. Arrived (arrival time of an email into outlook) ) (SERIES 1)
3. Cutoff (latest time that the email should arrive) (SERIES 2)

I'm trying to chart the arrival of emails as they come in each day, and for the chart to accommodate the new data as a new day ticks over.
You can see that the 27th May is now blank, but as the email arrives, the cells have Vlookups which populate data in the 'Arrived' and 'Cutoff' columns.

My major issue is that Excel 365 does not seem to know how to treat series vs axes.
I'd like to have date on the x-axis and time on the y-axis, with the two series Arrival and Cutoff charted against each other - but it's never that simple with Excel.

Desired:

Further, I understand that dynamic charting requires named ranges that expand when a new date is added, but I cannot get that far, as no matter what columns I select, the chart is different every time.
Sometimes it even treats each time point as its own series, leading to 20+series per month! (screenshot)

For example, If I only select data in columns Arrival and Cutoff and insert chart, I get this:

Other times, selecting all 3 columns (Date, Arrival, Cutoff) will yield a good-looking chart, but include dates on the x-axis that are not in the range!
being Excel 365, you cannot link the min/max x-axis values with a formula.

I've tried converting the times to decimals and working like that, but as soon as you change the axis category to time, it turns the y-axis all to 12:00:00PM (screenshot)

Any help/links to tutorials working with time series would be greatly appreciated legends!

Steve

 

[GraH]

Seems you are after something like this:

It's simple Excel charting. You only have 2 series, arrived and latest. x-Axis is a date axis.

Dynamic ranges on the chart are used like this.

Formulas for reference:

Book1
	K	L	M	N	O	P	Q	R	S	T	U
1
2	Date	Arrived	Latest		rDates	rArrived	rLatest
3	2/05/2022	9:30	8:00		2/05/2022	9:30	8:00
4	3/05/2022	8:39	8:00		3/05/2022	8:39	8:00
5	4/05/2022	7:30	8:00		4/05/2022	7:30	8:00
6	5/05/2022	7:15	8:00		5/05/2022	7:15	8:00
7	6/05/2022	8:12	8:00		6/05/2022	8:12	8:00
8	9/05/2022	6:12	8:00		9/05/2022	6:12	8:00
9	10/05/2022	8:19	8:00		10/05/2022	8:19	8:00
10
11
12
13					rDates	=$K$3:INDEX($K3:$K360;COUNTA($K3:$K360))
14					rArrived	=$L$3:INDEX($L3:$L360;COUNTA($L3:$L360))
15					rLatest	=$M$3:INDEX($M3:$M360;COUNTA($M3:$M360))
16
Sheet1

Cell Formulas
Range	Formula
O3:O9	O3	=$K$3:INDEX($K3:$K360,COUNTA($K3:$K360))
P3:P9	P3	=$L$3:INDEX($L3:$L360,COUNTA($L3:$L360))
Q3:Q9	Q3	=$M$3:INDEX($M3:$M360,COUNTA($M3:$M360))
O13:O15	O13	=TRANSPOSE(O2:Q2)
P13:P15	P13	=TRANSPOSE(FORMULATEXT(O3:Q3))
Dynamic array formulas.

Y-Axis definition:

For charting tutorials: 1 address to start from is @Jon Peltier at Jon's Excel Page

 

[Jon Peltier]

When you have dates in the first column and other values in other columns, Excel uses dates as X values and the other values as Y values. But if your other values are also dates or times, Excel gets confused. Here's how to get around it and also make the chart dynamic.

Here is my data. I have selected it and converted it into a Table. I used the Ctrl+T shortcut, but you could Format as a Table on the Home tab or Insert Table on the Insert tab. The Table is a special range with data features built in.

Select the Arrived and Latest columns (including header) and insert a line chart (Chart 1). Since you have not specified X values, Excel plots the point number (1, 2, 3,...).

Select one of the series. The SERIES formula looks like this:

=SERIES(Sheet1!$C$2,,Sheet1!$C$3:$C$13,1)

Put the cursor between the two commas, which is where the X values should be specified, select the dates (not including the header), and click Enter. The formula changes to this, and the chart now shows dates (Chart 2).

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$13,Sheet1!$C$3:$C$13,1)

If you don't want to show weekends, double-click on the X-axis, and under Axis Type, choose Text Axis.

When you get another day's data, enter the date and the two times in the next row below the Table. The Table detects the new data and expands, and at the same time, the chart expands to include the new Table row.

 

[sfos83]

Hi G,

A thousand thankyous! My organisation only has Excel 2016 desktop or Excel online, so unfortunately the above does not apply to me - however, I have requested installation of the new version and I'll put your advice into action.

Cheers, Steve

 

[sfos83]

Hi Jon,

Also super helpful stuff!

As per my other post, I'll need Excel 365 desktop I imagine - the Excel online version doesn't have the SERIES function.
If my organisation grants a licence, I'll let you know how it goes

Thanks again
Steve

When you have dates in the first column and other values in other columns, Excel uses dates as X values and the other values as Y values. But if your other values are also dates or times, Excel gets confused. Here's how to get around it and also make the chart dynamic.

Here is my data. I have selected it and converted it into a Table. I used the Ctrl+T shortcut, but you could Format as a Table on the Home tab or Insert Table on the Insert tab. The Table is a special range with data features built in.

Select the Arrived and Latest columns (including header) and insert a line chart (Chart 1). Since you have not specified X values, Excel plots the point number (1, 2, 3,...).

Select one of the series. The SERIES formula looks like this:

=SERIES(Sheet1!$C$2,,Sheet1!$C$3:$C$13,1)

Put the cursor between the two commas, which is where the X values should be specified, select the dates (not including the header), and click Enter. The formula changes to this, and the chart now shows dates (Chart 2).

=SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$13,Sheet1!$C$3:$C$13,1)

View attachment 65932

If you don't want to show weekends, double-click on the X-axis, and under Axis Type, choose Text Axis.

When you get another day's data, enter the date and the two times in the next row below the Table. The Table detects the new data and expands, and at the same time, the chart expands to include the new Table row.

 

[GraH]

Hi G,

A thousand thankyous! My organisation only has Excel 2016 desktop or Excel online, so unfortunately the above does not apply to me - however, I have requested installation of the new version and I'll put your advice into action.

Cheers, Steve

Glad to help. My solution does work on 2016 too. The dynamic ranges made with index, like rDates, inside the name manager even work on very old versions. You do not need them on the sheet, that was just to showcase.

 

[Jon Peltier]

All suggestions made here work in Excel versions going back to Excel 2003 for Tables (they were called "Lists" back then) and even earlier for Names.

Excel for the Web doesn't have the ability to edit series names (yet), but it's coming along.