Plotting Light Curves in Excel

Copyright 1999 by Lewis M. Cook

This tutorial is a step by step method of making a light curve chart from a text data file (usually a list of times and magnitudes separated by tabs or spaces, one observation to a line) but anything in an Excel spreadsheet can be plotted, and here's how to do it.
This is ONE WAY to do it. It is not the only way!

This is probably the ugliest web page you'll ever see. Nevertheless, here it is ---

It is complicated, but flows through well if you strictly follow the steps. If you want to get creative, fine, but do it after you've gone thru this process at least once.

Print this out and follow the steps 1,2,3,... until you're done. This is written for WIN 95, but Win 3.1 is very similar. Win 98 ought to be set up like this, but let me know if this is messed up for WIN 98. --


Import the data into an Excel Spreadsheet

If the data is in a text file (lightc.txt), just open the text file in Excel and the import wizard will guide you thru the process to get the data into columns.

Click Open,
click the "Files of type" drop down arrow and choose "Text files".
Set the column boundaries to match the data in the window if they aren't guessed right by the software. Click NEXT until Finished.

Look over the columns to make sure the data has been inserted properly, editing the column data to be sure the data is numeric (no alphabet characters!) - delete anything that isn't a number or a minus sign.

If the data is not arranged with the time in the left column, copy the time column to another sheet (or other columns in this sheet) and copy the data columns to the right of the time column.

Highlight the data columns you want to plot (start in the upper left corner and use the mouse button - or hold the shift key and move down and to the right with the arrow keys - until all the data you want to plot is highlighted (black cells).

On the menu bar click INSERT (or hold the ALT key and press the I key).

Click Chart.

That brings up a window -
Click - XY (Scatter) for the type plot
Click NEXT
A tiny graph with your light curve (upside down) will appear.
Click NEXT
Another window: Type in the chart title and x & y axis labels.
Click NEXT
Another window: Click "As New Sheet"

There is your plot, but it is upside down and gray and the Axis labels and tick marks are in the wrong places.

Make the background white:

Put the mouse cursor over an empty gray spot. RIGHT CLICK there (click the RIGHT mouse button)
A Menu window pops up.
Another window with pretty little colored squares. Look at the right part of the window in the "Area" frame.
The gray square in the lower right is highlighted.
Click the white square in the lower right just below the gray square.
Click OK.
The graph now looks white with blue dots (diamond shaped).

It is still upside-down.

Place the mouse cursor over one of the Y axis numbers.
RIGHT Click on the number
Click FORMAT AXIS in the Window
Another window pops up.
Click on the top tab marked "Scale"
On the bottom of the frame are 2 boxes to click:
Values in reverse order
Value (X) axis crosses at maximum value

Now click on the top tab marked "Patterns"
In the "Major tic mark type" frame
click the button labeled "Inside"

If the scale numbers were funny, you can click the Number Tab and click NUMBER in the list and adjust the number of decimal point digits. You can adjust this later if needed.

Click OK

There. Almost Perfect.

Now put the mouse cursor over the numbers at the bottom of the X axis.
RIGHT click over one of the axis numbers.
Click Patterns tab
Click Inside button on the Major Tick Mark type frame
Click OK

Done. Almost.

See the little box on the right that says "Series 1"? Right click it and click Clear to make it go away.


If you imported 2 or more sets of data (say V magnitudes and B-V magnitudes), they both will show up in the box as "Series 1" and "Series 2". Well, having Series 1 and Series 2 on your published charts is going to make you look stupid, so change the labels this way:

Hold the mouse cursor over a data point.
RIGHT click.
Click the Series tab in the window
Highlight the V data series name in the frame on the left (probably Series 1)
In the box to the right labeled NAME type "V"
See- the name changes on the left to V

Now Click Series 2
Type B-V in the Name box

If you've got U-B data, click it's series, too, and rename it.
Same for R-I or X-ray magnitudes.
You can drag the box with the point identification into the plot area, too if you want.
Don't want the grid lines? Right click the mouse when the cursor is on a gridline and nothing else.
Click Clear - they're gone
=====SAVE THE FILE!!!=====
Now print the chart, put it in a frame and hang it on the wall, admiring your work in getting the data and making it print out so nicely.

Work with the Scale tab on the FORMAT AXIS command to scrunch the scale down so the data looks more precise than it really is or expand the scale if it is someone else's data that is scattered and looks bad by comparison (just kidding!).

Final trick: make .gif files for web pages:

Click EDIT
Click MOVE OR COPY Sheet.
Click "Create a copy"
Click Move selected sheets to (new book)
Click OK
Click FILE
Click Page Set up
Click the Page tab
Click Portrait in the orientation frame
Click the Chart tab
Click Scale to fit Page
Click File
Save this as "Lewschrt.xls" (or whatever you want)
For Win 95/Office 97:
Click Create from File
Click Browse
Navigate thru your files until you find "Lewschrt.xls" (or whatever) and click it
Click OK
Click File
Click Save as HTML
Your plot has been saved as a file called something like Image21.gif. Change the file name if you like and upload it to your web page. It will load onto a web page perfectly.
Check out for an example plot.