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

Best Black Friday Deals 2017

How modern book design was influenced by illustrated manuscripts

Best Black Friday Apple Deals 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)