Excel - Part I: Getting Started

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.