Opening the Tutorial Workbook

Excel Tutorial

Geography 102/202

(Phil Dennison)

Winter 2006

This tutorial will teach you how to use Microsoft Excel to solve problems in this class. The tutorial and accompanying Excel workbook are designed to be used with Excel 2003. Part 1 covers basic spreadsheet skills. Part 2 explores the use of formulas and functions. Part 3 briefly describes sorting, and Part 4 looks at graphing in Excel. Finally, Part 5 gives two examples of how Excel can be used for some of the complex calculations we will deal with in this class. The tutorial assumes a basic knowledge of the Windows environment, including using a mouse and a menu interface. Those who already have some familiarity with Excel may want to skip the first few parts of this tutorial.

Opening the Tutorial Workbook

This tutorial is designed to be used with the "Excel Tutorial.xls" workbook. Download this file, and open it by double-clicking the workbook icon.

Part 1: Essential Skills

The blank spreadsheet you see in front of you is called a worksheet (Figure 1). One or more worksheets can be saved together in a file called a workbook. Different worksheets within the same workbook can be displayed by clicking the tabs just below the workspace. The "Part 1" tab should be white, indicating that you are viewing the Part 1 worksheet. Take a moment and flip through the different worksheets in the tutorial workbook. When you are finished, return to the worksheet labeled "Part 1".

Each worksheet is made up of many cells. Cells are addressed by the letters and numbers at the top and to the left of the workspace. Letters refer to the column of a cell, and numbers refer to the row of a cell. Together the letter and number make up an address, which is unique for each cell. For example, the address of the first cell in the upper-left corner of the Part 1 worksheet is A1.

To edit the value of any cell, the cell must be highlighted by the cursor. There are many ways to navigate around the worksheet using our cursor. Clicking the mouse on a cell will highlight it with the cursor. The keyboard arrows can be used to move the cursor in any direction. The "Enter" or "Return" key moves the cursor down, while the "Tab" key moves the cursor to the right. "Page Up" and "Page Down" move the worksheet view, but not the cursor. The scroll bars at the bottom and right of the workspace also move the worksheet view, but not the cursor.

Figure 1. A blank worksheet.

Copying and Moving Values

One of the most common tasks in Excel is moving information around the workspace. Let's create some values that we can duplicate. Enter a value in cell A1 by typing a number while the cursor has the cell highlighted. Press the "Enter" key once you have typed the number. Type numbers in the four cells below A1, and then return to cell A1 using the up arrow.

We can highlight a single cell by having the cursor over it. We can highlight a range of cells by clicking on the first cell in the range, holding down the mouse button, dragging the cursor to cover multiple cells, and then letting go of the mouse button. Highlight your 5 cells with values in them (Figure 2). We can also highlight entire columns (Figure 3) or rows (Figure 4) by clicking on the gray column or row header at the top or left edge of the workspace.

Figure 2. A range of selected cells.

Figure 3. A selected column of cells.

Figure 4. A selected row of cells.

We can copy our highlighted range by clickingEdit, Copyin the Excel menu. The range of cells should now have a moving dashed line around it. We can now duplicate these values in a different range of cells. Click on the first cell in the range you want to copy to, so that it is highlighted by the cursor. Then click Edit, Paste. Your cells should be duplicated in the new range.

If we don't want to copy a range of cells, but merely move them to a new location, we can easily do so. Clear the newly copied range by highlighting it, and then clicking Edit, Clear. You can also hit the "Delete" key while the range is highlighted. Highlight your original five cells, and then click Edit, Cut. The same dashed line should appear around your range of cells. Go to the new range, and click Edit, Paste.

Let's move our values back using a second method. First, highlight the range of cells. Next, while holding down the mouse button, click on the black border of the highlighted range. While still holding down the mouse button, drag the border back to the old range, and then release the mouse button.

Filling

Excel allows us to "fill" in a range of cells by only typing the first few numbers in a regular series. Clear all the cells on the current worksheet. Type a 0 in cell A1. Next, highlight the cell. While holding down the mouse button, click on the small square in the lower right corner of the cursor box. Pull the square down using the mouse until the box covers a range of 5 cells, then let go of the mouse button (Figure 5). Notice that Excel assumes we want to fill the entire range with the same value. Excel tries to guess what kind of series you are trying to construct based on the first few values you supply it. Clear column A, and type 0 in cell A1 and 1 in cell A2. Highlight both cells, and then drag the box to cells A3 through A5. What rule did Excel use to fill your range? How could we get all values in the range to separated by 2 (0,2,4,6,8)?

Figure 5. Filling a range of 5 cells.

Part 2: Formulas and Functions

We have already seen that cells can hold numeric values. Cells can also hold text values or formulas. Flip to the worksheet named "Part 2" using the tabs at the bottom of the workspace. We will use this worksheet to calculate some simple formulas.

Formulas can be typed in just as numbers can, with the cursor highlighting the cell we want the formula to go into. To give Excel the ability to differentiate between normal text and formula text, we must begin each formula with a "=". In cell C1, type the following formula: =A1+B1. The resulting value should be the sum of the values in cells A1 and B1.

If you haven't already noticed, there are red triangles in some of the cells of this worksheet. Moving your mouse over these triangles will reveal hints.

Click on C1. Use the box in the lower right corner of C1 to fill in the range C2 through C5. Notice that each cell in column C contains the sum of the cells in that row (Figure 6). By default, cell references in an Excel formula are relative. This means that the formula does not refer to "cell A1", but to "a cell two cells to the left of the cell this formula is in".

When we use the fill function or copy and then paste, Excel will automatically change the function to reflect its new address. For example, our function originally resided in row 1 and used two row 1 cells as its inputs. If we copy the cell to row 2, Excel changes the formula so that it requires two row 2 inputs.

Figure 6. The sum function in originally in C1

has been filled to cover the range C1 through C5.

Sometimes, we want a formula to contain an absolute reference, one that doesn't change as we move it around the worksheet. An example would be if we wanted to use a constant term in each formula. We use a "$" to make a cell address reference absolute. Highlight C1, and use the Formula Bar, just above the center of the workspace, to edit the equation in C1. Change =A1+B1 to =$A$1+B1. Fill the range C2 through C5 using the new formula. The formula should add 1 to the values in column B. We could copy this formula anywhere in the worksheet, and it would always add the value in cell A1 to the value in the cell to the left of the formula cell.

Besides "+" for addition, Excel uses the following standard operators:

Experiment with these operators on the Part 2 worksheet.

Functions

Obviously, we need to be able to do more than add, subtract, multiply, and divide. Excel supplies a wide varied of functions that can be used to do more complex math and statistics. The Function Wizard gives you the ability to use functions with out having to know all the necessary inputs. Start the function wizard by clicking Insert, Function. Browse through some of the available functions. The function wizard will provide boxes for each input the function requires (Figure 7). You can enter the inputs by typing them in, or by clicking on the input box and then highlighting the range you want on the worksheet with the cursor.

You will use some functions so often that you will probably prefer to type them in directly. The two most common functions are sum and average. Both functions require an input of the range of cells you want to sum or average. We will use the sum function on the values in the range A1 through A5. In cell A6, type =sum(A1:A5). Notice that we used a colon to express that we want to include all the cells between A1 and A5. If we want to sum our entire range except the value in cell A3, we use a comma to separate our input cells. Two ways of retyping our formula would be =sum(A1,A2,A4,A5) and =sum(A1:A2,A4:A5). Both will return the same output.

The average function also requires a range of cells as an input. Find the following averages:

1. Average of all the cells in the range B1 through B5.

2. Average of B1, B3, B4, and B5.

3. Average of B1, B3, and B5.

Figure 7. The Function Wizard for the sum function.

Trigonometric Functions

Trigonometric functions in Excel require angle inputs to be in radians, rather than degrees. Trigonometric functions in Excel include cos, sin, tan, acos, asin, and atan, where the "a" stands for "arc". To convert from radians to degrees, multiply the angle by /180. To convert from radians to degrees, multiply the angle by 180/. Incidently,  has its own function in Excel. Rather than enter the value of , you can type =pi(). Try some of the following practical examples:

1. Cosine of 90 degrees=cos(90*(pi()/180)

2. Sine of 2 radians=sin(2)

3. Arctangent of 0.5, in degrees=(180/pi())*atan(0.5)

4. Sine of 45 degrees

5. Arccosine of 0.3, in degrees

6. Cosine of 30 divided by sin of 30

Part 3: Sorting

Flip to worksheet "Part 3". You will see a table of the optical qualities of water at different wavelengths. The two constants shown are the index of refraction (n) and the extinction coefficient (k). Look at the numbers in column D. The E reveals that these numbers are in scientific notation. 7.69E-11 is the same as 7.6910-11. To sort this table by any one of its columns, first highlight the entire table beginning with cell A2. Next, click Data, Sort. A new window will pop up with sorting options (Figure 8). First look at the bottom of the window. It should have the box marked "Header Row" checked. If "No Header Row" is checked, then our table headers will be sorted with the rest of the data. We can sort by up to three columns. First, sort by index of refraction by choosing "n" in the first box. Then click OK. Is the order of the wavelengths now reversed? Try sorting by color (first box) and extinction coefficient (second box). Are the near infrared (NIR) wavelengths now in the correct order?

Figure 8. The sort window.

Part 4: Graphing

Flip to worksheet "Part 4". You will see a series of solar insolation calculations for different times of day. Highlight the entire table, including the table headers. Next, click Insert, Chart. You will see a multitude of graph styles you can choose from. Most of the graphs we will do in this class are XY plots. A XY plot or scatterplot assumes the left-most column contains x values and that all other columns are y values. Click XY (Scatter). You should now see several different types of scatter plot. Select the scatterplot with straight lines connecting points. Then click Next. A rough version of your graph will appear in the window.

Verify the data range is correct, and that the Columns button is checked. Once you have, click Next. The new window should have tabs for several different format options on it. Click on the Titles tab. Add a title and axis labels to your graph. Click on Gridlines, and turn off the Y axis major gridlines. Click on Legend, and put the legend at the bottom of the graph. Don't worry if your graph looks funny at this point. Click on Next when you are finished with the formatting options.

The final step is to determine whether you want to put the graph on the current worksheet, or place it on a new worksheet. Leave the second option checked and click Finish. The graph should appear on your worksheet. To change any part of the graph, you must make that graph element active. For example, click on the graph once. Squares should appear at the corners of the graph window. You can change the size of the graph by clicking and dragging one of these squares. Enlarge the graph window so that it fills most of the workspace.

Notice that there is a great deal of space between the Y-axis and our plotted points. We can change this by making the X-axis active and changing its minimum value. Click the X-axis once to make it active and twice to edit its properties. You can now edit the thickness and color of the axis line, the properties of the value labels on the axis, and the maximum and minimum values on the axis. Click on Scale. In the box to the right of Minimum: click and type 11:30. In the box to the right of Maximum: click and type 17:00. Then click OK. You graph should look like the graph in Figure 9.

For some graphs, you may want to plot a linear regression of the data. Click on one of the graph lines. Then click Chart, Add Trendline. Choose the Linear option, and then click the Options. The last two checkboxes in the window allow you to display the equation of the line and the R2 value on the chart. Turn both options on, and then click OK. The regression line and a text box containing the equation of the line and R2 value should appear on the graph. You can move the text box out of the way by clicking on it once and then dragging it to a new location.

Figure 9. Graph from Part 4.

Part 5: Examples

This section contains practical examples of how Excel can be used to do calculations in this class.

Example A

Flip to worksheet "Part 5a". The first problem we will look at is Lambert's Cosine Law. This equation states that the surface irradiance is equal to the initial irradiance times the cosine of the incident angle:

E = E0 cos 

We will calculate irradiance for 5 different incidence angles, and then graph the results. Cell B4 contains a constant, E0. In column A, you have already been given incidence angle in degrees. Convert degrees to radians by multiplying the values in column A by /180. In column C, take the cosine of the values in column B. Finally, in column D multiply the values in column C by your constant. Remember to use a "$" in your cell reference to make it absolute. Next, graph the 5 data points using a XY plot with angle in degrees on the x-axis and irradiance on the y-axis. To highlight separate columns, highlight your first column, hold down the Ctrl key, and then highlight the next column. After you have completed your graph, your worksheet should look something like Figure 10.

Sometimes, you may want to print your results onto paper. To print a graph, click on the graph so that it becomes active and then click File, Print, OK. To print the values on a worksheet (as you see the worksheet when no cells are being edited), click File, Print, OK. If you only want to print part of the worksheet, highlight the portion that you want to print and then click File, Print Area, Set Print Area. Then use File, Print, OK. To get an idea of what your worksheet will look like before you print it, click File, Print Preview. To close the Print Preview window, click the Close button just above the view.

If you need to print formulas rather than the values they produce, you must first make formulas the view option. Click Tools, Options, View. Then select the check box under the Window Options header that says Formulas (Figure 11). The active worksheet will then display formulas. You can print it as you would any other worksheet. Try printing out your graph and your formulas.