Revised from the tutorial
by Prof. Joseph Lomax, United States Naval Academy.
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 - Part I: Getting Started
I. Introduction
What is Microsoft Excel?
Microsoft Excel is a spreadsheet program. 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?
When analyzing experimental data, a large part of your time is spent
doing repetitive calculations. For example, a chemist at a water
treatment plant might collect 100 different water samples, titrate all
of them separately, and then calculate the amount of dissolved phosphorous
in each sample. The calculations on each of the 100 samples are essentially
identical, although the particular numerical values are different.
This is where spreadsheets work particularly well. A spreadsheet enables
you to perform the data analysis with only one sample, and then transfer
the data analysis calculations to all the other samples. Spreadsheets
also enable you to make clear presentations of data in graphical form.
Thus, spreadsheets 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. You will find that spreadsheets are
used in many NCC science lab classes for analyzing and presenting data.
Many scientists and engineers use spreadsheet programs as often as they use
word processing software.
EXERCISE 1 : Starting the Program
The first step in learning to use your new software is to start (or in
computer parlance: launch) the Excel 2000 Program. The easiest way to launch
the program is from the Microsoft Excel icon on your computer desktop screen.
You will SELECT this icon. The term SELECT means to drag your mouse
so that the cursor (the moving arrow on the screen) is at the appropriate
spot on your screen and click the left button. You will see SELECT many
times in this document. Remember, SELECT means MOVE your cursor over the
object and CLICK the mouse.
Each file made by Excel has the extention ".xls", for example,
"Book1.xls". We will describe Excel 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.
Exercise 2: Entering Information
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.
Exercise 3: Formatting
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 Cells menu 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.
Exercise 4: Saving a Spreadsheet
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 labeled (F:). Then SELECT
the third yellow folder icon on the top. This allows you to create a new
folder. At the top give the folder a descriptive name, such as 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 an Excel file.
Exercise 5: Finding Your File
(a) If you have used the file recently, the quickest way to retrieve
it is to go through Documents. However, Win95 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 Win95 Start icon. Select Documents
and you should find tutor1.
(b) At the top left of the Win95 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, (F:).
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 Win95
Start. Go up to Programs over to Windows Explorer, within
(F:), 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 Win95 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,
Win95 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 bottome 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.
In NCC Chemistry classes we typically 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.
Exercise 8: Modifying a Graph
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 60 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:
Exercise 9: Printing
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 Part I: Getting Started. You
may want to save your file and exit Microsoft Excel.
Return to the Excel Tutorial Homepage.
This page was last revised on 24 April 2003.
If you have any problems or questions about the program,
contact David Horner.
|
