Another easy Excel question?

  graham√ 09:27 18 Jun 2003

I have a photocell which switches on a floodlight on a tree in my garden, and have now started an Excel worksheet to track the time. To start, I have the date, 17 jun 03, in column A, the time as 9.20 in column B. I would like to have a graph of the data but the penny hasn't dropped yet, I suspect it's the time format. I have put in some dummy data for now.

A trivial subject maybe, but a nice graph! Anyone with Excel on their PC who have never used it, give it a try. I'm hooked!

  VoG® 09:46 18 Jun 2003

Well, you will need some data to plot. You could use Column C and enter 0 when it switches off and 1 when it switches on.

I also think that you will need to combine Columns A and B to show Date+Time.

  graham√ 10:02 18 Jun 2003

Thanks, wouldn't a different time on subsequent days be sufficient data? If the date was on the bottom (is that 'x' axis?) and the time was on the left (y?), then I would expect to see a change until 21st June, which is the longest day, then the trend should reverse as the days grow shorter.

  VoG® 10:14 18 Jun 2003

OK I think I've cottoned-on now.

Select the data in Column B and click the ChartWizard toolbar button.

Select chart type (presumably XY (scatter)).

Click the Next button.

Select the Series tab.

Click in the X Values box and click the funny red arrow thingy at the right hand side of the box (sorry if this is getting technical).

Select your dates in Column A.

Click the funny red arrow thingy again.

Follow the rest of the Wizard to complete the chart.


  seedie 10:28 18 Jun 2003

for years I've been typing in the date in excel, then a few days ago I dicovered this.

CTRL+; puts in the current date
CTRL+: puts in the current time
You can combine them in one cell


  graham√ 10:43 18 Jun 2003

Thanks from me, and all the people following this lesson!

VoG®, had to faff about with the date, it just numbered 0 - 10 at first, just what I wanted now. Only thing is, how do I 'refresh' the graph as I input the new time each day, add days on and extend the time range?

  VoG® 10:51 18 Jun 2003

To avoid faffing around the date, select Column A, Format/Cells and format as Date.

To get it to update you have two options:

a) Decide in advance how long this is going to run for, enter all of those dates and select the whole lot for your chart. To prevent it from plotting zeroes for missing times, enter


in the time values for future dates.

b) Select the "new" data (both Columns A and B). Select the chart, Edit/Paste special and select:

Add cells as new points

Values Y in columns

Categories (X values) in first column.

  Peverelli 11:03 18 Jun 2003

Ahhh, so that's how I get a chart to look the way I want it!!! Cheers VoG® & thanks graham√ for posing the question, and also to Seedie for the handy little tip.

  VoG® 11:05 18 Jun 2003

Just spotted an error:

b) Select the "new" data (both Columns A and B) and Copy. Select the chart, Edit/Paste special and select:

  graham√ 11:08 18 Jun 2003

Thankyou, perfect. Until the next time...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?