By Joseph F. Lomax with the assistance of ENS Lora Anderson,
revised from the tutorial by Wayne Pearson and John Schultz: all of
the Chemistry Department, USNA.
If you work with both your browser (Netscape) and the
spreadsheet file (xls) open at the same time, you might find it
easier to follow the instructions. If you are not comfortable
doing this, just print out the Web file and use it off of paper.
Important: Format your printer to print the Web file in landscape
mode!!!
Excel 2000/XP - Part I: Getting Started
I. Introduction
What is Microsoft Excel 2000/XP?
Microsoft Excel is a spreadsheet program. There are two version that are
covered in this tutorial, Excel 2000 and Excel XP. For our purposes they
behave the same and will give the same screen views. The function of a
spreadsheet is to store and manipulate data, in particular
numerical data. Once this is done, this data can be output in
various useful forms such as tables and graphs. Though originally
made for business, spreadsheets are widely used in scientific and
engineering applications, too.
Why is spreadsheeting important to you?
As computer programs go, spreadsheets are not particularly fast
number crunchers, and the programming you can do with them tends
to be fairly elementary. However, when dealing with computers a
large part of your time is spent not on crunching numbers or
programming, but on formatting your output. This is where
spreadsheets work particularly well. They are capable of taking
your data, performing mathematical manipulations, and making a
clear presentation of the data in graphical form. Thus, they
provide a convenient method for analyzing all types of data
(financial, inventory, laboratory, etc...) and producing high
quality graphics. In addition, if you desire, you are able to make
a 'quick and dirty' graph to check on the data input, the quality
of data, and the strength of your mathematical relationship.
'Quick and Dirty': I like that in a program.
You will find that spreadsheets are often used in the Navy. As
an JO, you might find that you are called upon to use a
spreadsheet for training records, inventories or as a division
officer's notebook. Those of you who are exploring engineering
careers might like to know that a LCDR I talked to from the EWS
Division related to me the results of a survey that he performed.
90% of engineers surveyed chose spreadsheet programs as the number
one program they use (ahead of word processors).
The first step in learning to use your new software is to start
(or in computer parlance: launch) the Excel Program. You launch Excel by 1) SELECTING the
Windows Start button; this will bring up a set of choices
in a menu. 2) Drag your cursor over, Programs. Another
menu will appear to the right. 3) Drag your cursor over to Microsoft
Excel and SELECT on it, you will launch Excel.
As each file made by Excel has the extention .xls. For example,
in Book1.xls, we will describe files, as 'xls files'.
The initial xls window may not fill your whole screen. This
size is very useful if you want to use more than one application
simultaneously (such as a Web Browser), however, often, it is
desirable to have a larger working window (also called working
environment) in Microsoft Excel.
The size of the working window is controlled by two sets of
three small buttons on the top right of the window (circled with
red and yellow). The ones on top (the Title Bar) control the whole
Microsoft Excel program environment and the ones on the line that
starts on the far left with File (the Menu Bar) control the
environment of the particular xls file. If you have more than one
file up at a time, each will have these three buttons on their own
File Title Bar. 
The left-most looks like an underline symbol. If you click on
the box, the program/file gets small. This is called minimizing.
From the Title Bar, the underline symbol minimizes the program
to the Task Bar on the bottom of the screen. The name of the task
always resides there even while the program is large. You will see
it as the name of the program (Microsoft Excel) and a
shortened version of the file name. When minimized, by SELECTING
this Task, it will return the xls back to its previous size. This
is called maximizing. Try minimizing, then maximizing the program.
If you minimize the file by SELECTING the underline on the
second line (or the File Title Bar), this minimizes the file
to a Task Bar within the Microsoft Excel program, this time as
just a shortened version of the file name. Try minimizing, then
maximizing the file.
The center symbol is either a square with a heavy line on the
top of the box or two smaller overlapping versions of this box. If
the single box shows, SELECTING it will make the environment
larger. If the two boxes show, SELECTING it will make the
environment smaller. It is a toggle; give it a try back and forth.
If you were to SELECT the 'X' on the right, the program would
close. It is unnecessary to try this one right now. If your file
has been changed since it was last saved, it will ask you if you
wish to save the changes. If you have saved it or have not done
anything to the file, it will close the program immediately.
The following is data from an experiment performed to examine
how the pressure of an automobile tire changes as a function of
the temperature of the tire.
| Tire Pressure vs. Temperature |
|
| Pressure (psi) |
Temperature (Fahrenheit) |
| 32.0 |
75. |
| 33.3 |
100. |
| 34.5 |
125. |
| 36.0 |
150. |
| 37.1 |
175. |
| 38.4 |
200. |
Part A
Each spreadsheet program, creates a 'Book' which is made of a
number of 'Sheets'. The default has Sheet 1 - Sheet 3 available.
You will be inputting data into what are called cells.
Cells are addresses for information. In a spreadsheet book these
address are defined by three parameters: 1) the page, 2) the
column and 3) the row. When you open a new spreadsheet, it will
default to Sheet 1, cell column A, cell row 1. In the image above,
the cell A1 is boxed and is identified by its address just above
the spreadsheet on the left. The book is on Sheet 1 (you can see
the button sheet 1 highlighted on the bottom). There is a
spreadsheet on each of the three sheets. Each spreadsheet has 256
columns and 65,536 rows. Clearly, we do not expect you to use all
of these at once.
In these exercises, we will be working on only sheet 1. Assume
that all cell address refer to this page. First, we will create
labels in order to make your spreadsheet easy to interpret. SELECT
the appropriate cell using your mouse and type in the labels
If you are within a cell, if you just start typing and the
label will appear in the cell. It will not record what you type
until you tell it you are done. You can do this by 1) SELECTING
another cell with your mouse. 2) You can hit the 'Enter' key on
your keyboard. It will records what you have typed, and will move
you to the cell just below in the same column. 3) Hitting an arrow
key will record and move you to the corresponding active cell. If
you type "Tire Pressure vs Temperature" into cell A1
then hit the 'Enter' key, you will be all ready to type "Pressure
(psi)" into cell A2.
Cell Label
A1 Tire Pressure vs Temperature
A2 Pressure (psi)
D2 Temperature (degrees Fahrenheit)
Note that some labels appear to extend over more than one cell.
Navigate to cell B1 by SELECTING it with your mouse or using the
arrows on your keyboard. Try entering your alfa code into B1. You
will notice two things: 1) the leading zero does not show, and 2)
the remaining digits overwrite the A1 entry in the spreadsheet
area. The first of these shows the difference between the idea of
a label and a value. When you typed in your alfa, for example:
034567 , you may have expected to see, 034567, but
you got 34567. In a numerical value (shortened to value
in spreadsheet lingo), the leading zeros have no function and are
left out. However, anything that can be typed can be part of a
label, even numbers. Therefore, if you make your alfa code a label
by putting an apostrophe (') as the leading character. Now, the
leading zero will show.
You may wonder if your typing into cell B1 has erased some of
cell A1. Arrow over to A1. You will notice that the whole text of
the label shows again. You never lost it, it was just hidden on
the spreadsheet window by cell B1. If you move back to B1 and
press the Del key, the alfa code will be removed. As cell B1 is
again empty, the full contents of cell A1 will reappear in the
window.
Part B
Place the pressure data in the A column starting at cell A3.
Remember, you can record the data and move down, both, by the
'Enter' key. Place the temperature data in the D column starting
at cell D3.
Note that the trailing zeros have been dropped from some of the
data entries. In order to show these zeros your will need to
change the numeric format of the data blocks.
Part A
Highlight the block of data from A3 to A8 by placing the mouse
cursor on A3. Hold down the left mouse button and DRAG the cursor
down to A8. The block of data should now be highlighted.
Part B
Hold your cursor over the highlighted block and click the RIGHT
mouse button. A menu should appear. This is the Object Menu.
Contained in this menu is "Format Cells". SELECT this.
The window that pops up is titled Format Cells. Since you
have highlighted the set of cells from A3 to A8, inclusive, on
sheet 1, the changes you make wiill only affect these cells.
Part C
The Format Cells window has six folders with a menu on each.
Each of the folders is labeled with a tab at the top. Number
is the top folder. In this folder there is a list of categories.
SELECT "Number" from this list. This will allow you to
set the number of decimal places in your data. You will notice in
the upper right of the Sample preview box is 32.00.
We wish to have one place after the decimal, so change the value
of decimal places to 1 in the box that comes up towards
the top on the right. This can be done by typing 1 in or
hitting the down arrow to get to 1. The preview box should
now display 32.0(see below). 
Part D
SELECT the OK button on the Format Cellsmenu to
return to the main spreadsheet. The entries 32.0 and 36.0
are now properly displayed. Any value which is entered into this
block will be displayed with one digit beyond the decimal point.
This may seem an esoteric example in changing a format, but for a
chemist, we are now satisfied that all of our pressures have the
same number of significant figures after the decimal point.
SELECT the third icon from the left on the Toolbar (it is
supposed to look like a floppy disk). If you prefer, SELECT File
on the Menu Bar and then choose Save As from the menu. You
will arrive at the same menu if you choose the Save icon, or go
through the File menu. Now, choose the Save As
commands.
At the top it (most likely) will have: Save File. SELECT
the box on the left lableled Desktop. Then SELECT the icon
on the top (a yellow folder). This allows you to create a new
folder. At the top give the folder the name Chem. Then at
the bottom of this window enter the file name as "tutor1"
and SELECT Save This is the working title for your
spreadsheet book. Always change this title to something that will
help you to remember what you were working on.
Note that the Notebook Title Bar (at the top of the Microsoft
Excel window) indicates the filename along with the .xls
extension which identifies it as a Excel file.
(a) If you have used the file recently, the quickest way to
retrieve it is to go through Documents. However, Windows
will not allow too many files to accumulate in Documents,
and will purge address after a while. What 'a while' means is
dependent upon usage.
SELECT the Windows Start icon. Select Documents
and you should find tutor1.
(b) At the top left of the Windows environment is an icon titled
My Computer. If you Double-click this (SELECTING twice the
same icon in quick succession) you will find, among others, (C:).
You will find the folder Chem in the window that comes up,
and double-clicking on it will reveal the contents. You should
find tutor1.
(c) A final method of finding your file is to go to Windows
Start. Go up to Programs over to Windows Explorer,
within (C:), you will find the folder Chem in the
left-hand window. Double-clicking Chem , will bring up in
the right-hand window that directory's contents. You should find
tutor1.
Exercise 6: Closing and Opening a File
There are two common methods to close a file. In the course of
closing the program, any file you have open will be closed. Or you
can close a file without closing the program. These two actions
are represented by the two X's in the upper right corner. The X in
the very top right (in the Title Bar) will close the program,
Microsoft Excel. If you have not saved the file since you have
made any changes, it will ask you if you wish to save the file.
The other X (in the Menu Bar or the File Title Bar) will close the
file, but not the program. It will prompt you to save the file you
have been working on.
Opening your file uses the same methods as finding the file. We
can do this because Windows recognizes that any file with the
extension .xls is a Microsoft Excel file. If we choose a
.xls file, be it from Documents, My Computer or
Windows Explorer, Windows will recognize that it is
associated with Microsoft Excel. It will launch the program while
opening the file.
Exercise 7: Making a Chart (a.k.a. Graph) in
Microsoft Excel
For those of you who have made graphs in spreadsheets, making a
graph in Microsoft Excel is both easier and a bit more obtuse
(i.e. obscured by language, much like using the terms: obtuse and
i.e.). The easier part is that it is made more clearly stepwise.
This clarity, however is sometimes thwarted by curious
terminology. I will try to give you ample warning when such
problems may occur.
To make a chart (or graph), SELECT some data. In our case,
SELECT A3..A8. As before, put your cursor over the highlighted
cells and click on "Insert" at the top of the
xls window and SELECT Chart or click on the picture of the
chart located on the icon bar. Now you are in the Chart Wizard
Window. This window allows you to chart your data in four steps.
Step 1 is selecting the chart type. You will notice that
the top of this window reads "Step 1 of 4-Select Chart Type".
For this particular set of data we want an XY chart.
SELECT the chart XY(scatter) from the menu. SELECT Next.
Beware: the default Chart Type is NOT what we
want. We want an XY Chart. This is NOT A COLUMN CHART
(yes, I know I am yelling!)! 
Step 2 is selecting the data for each axis (x-axis and
y-axis) of your chart. The window that appeared after you selected
Next is the "Chart Source Data" window. SELECT
the tab labeled "Series". As you can see, the
data you highlighted will be in the row titled "y-values".
This will be our first set of dependent variables. You must
designate the independent variables (x-values) for your graph,
also. There can be many dependent variables for any one set of
independent variables . Microsoft Excel calls each set of
dependent vairables a "series". The bottom left of the "Chart
Source Data" window shows that you only have one set of
dependent values called "Series1". You can add more
series by selecting the "add" or "remove"
buttons. In our example, the tire pressure is our only value
dependent upon the temperature.
There is a very simple method to enter the values for the
x-variables. First, you choose a text block by SELECTING the arrow
to the right of it (dark blue). At this point the Source Data
window will minimize to a Title Bar (sometimes hard to see)
and you will need to SELECT a block much like you did when you
changed the numeric format (by SELECTING the first cell and
DRAGGING to the last cell). When the block is highlighted, hit the
maximize button on the Source Data Title Bar. The Source
Data window will reappear with the series text block filled
in.
After you input both Pressure and Temperature, your window
should look like the image below. 
Once the Series have been set, we move to the next
window. SELECT Next.
Step 3 is labeling the chart. The next window is called
the "Chart Options" Window. The first tab is called "titles"
and will be selected by default. This is where you enter titles
into each of the text blocks. A typical Chart Title would
be the name of the experiment. In the Axis titles be sure
to put both the measured quantity as the title and the units of
the measurement parenthetically (see examples). Once you have done
this SELECT Next. 
The final step, step 4, allows you to choose where you
want your chart to be displayed. In the "Chart Location"
Window you have the choice of selected whether you want the chart
on a new sheet or on your existing sheet with your data.
Typically, in Chemistry classes, we ask for both the
spreadsheet and the graph. As difficult as it may be to believe,
people sometimes make incorrect graphs. This may be because of
incorrect data input or analysis, incorrect choice of axes or
simple spreadsheeting mistakes. Without the spreadsheet,
suggestions, correction and partial credit are difficult to
accomplish. It is much like trying to fix a car based on hearing
someone describe a noise in the engine. You may get an idea from
the noise, but it helps to open the hood.
So, to have both your speadsheet and graph on the same sheet
SELECT "As Object in:" and "Series 1"
will already appear since we only have one series. SELECT Finish.
Now your chart will appear on your spreadsheet. You can change
the location of your chart by clicking on it and dragging it to
your desired location.
Excel give you the opportunity to set your own graph size
rather than insisting that you use their defaults. The scale on
this graph is a little clumsy, the x-axis starting at zero and
ending at 200. Starting at 25 and going up by 25's to 250 would
give a more pleasing look. As spreadsheets are all about ease of
formatting, we might as well make the graph look good. We can
adjust the attributes of the X-axis and, later, the Y-axis by
calling up a window for each. We call up the X-axis window by
putting our cursor over the numbers along the X-axis in the graph
and clicking on the right button on the mouse. You will get a
drop-down menu with "Format axis" at the top. SELECT "Format
axis". A window will come up with a number of tabs (see
below). SELECT the Scale tab. Change the minimum from 60
to 25, the maximum from 200 to 250, and the major and minor units
to 25. SELECT OK.
In the same way, move your cursor over the numbers on the
Y-axis and right-click. SELECT "Format axis" then Scale
tab and modify the minimum to 30, the maximum to 40, and the major
and minor units to 2. SELECT OK 
The final graph with no line and the modified X-axis and Y-axis
scales should look like: 
If you want to save your printer cartridge a bit, you can take away
the background shading in the plot area. Having shading does look pleasant and you can make quite a number of effects, but
it is a cartridge killer. To remove the background shading, double-click on the background and the Format Plot Area
window will come up. On the right side is Area. Change it from Automatic to None.
To print the graph and spreadsheet data, highlight the spreadsheet data and the cells behind the graph.
The graph, itself, is not in the cells but "above" it in
a overlapping window (see below). If you print this now, it will
print both the spreadsheet and the graph. The easiest way to
access the Print window is to SELECT the Print
icon from the Button Bar (red circled object below). 
A Print window will appear. For our purposes, you can
SELECT the Print command. If you printer is properly
installed, on and functioning, you will get a printout. You may
preview what it should look like by SELECTING Print Preview.
You can play with these and other parts of the Print window
at your leisure.
This is the end of this Quick Start. You may want to save your
file and exit Microsoft Excel.
Return to the Excell 2000/XP Tutorial Homepage.
This page was last revised on 4 Sep 2001.
If you have any problems or questions about the program, contact
Joseph F. Lomax.
|
