header

Introduction to Excel

Objectives

Running Excel

Log-in to your user account and then double-click on the Excel icon Excel Icon.Depending on how your computer is set up, you may find the Excel icon on the desktop, the taskbar at the bottom of the screen, and/or the start programs menu.

Excel Worksheet

Conceptually, an Excel file is a workbook containing multiple worksheets. Each worksheet contains a rectangular grid of cells. A very small section of a worksheet is shown here:

Rectangular Cell Grid

The rectangular grid of cells consists of vertical columns and horizontal rows. The first column is A, the second is B, the third column is C, and so on. After the first 26 columns, the columns are identified by 2-letter sequences (AA, AB, AC, ... AZ, BA, BB, BC, ... BZ, CA, ..., and so on). Once you go past column ZZ, the columns are identified by 3-letter sequences (AAA, AAB, AAC, etc). The very last column is identified as XFD. (If you are curious, there are 16,384 columns in an Excel worksheet.)

The rows are numbered from top to bottom starting with row one at the top. There are 1,048,576 rows. (That means there are a total of 17,179,869,184 cells in an Excel worksheet but, I assure you, we will never use more than a tiny fraction of them.)

Each cell is uniquely identified by its column and row identifiers. The column identifier always precedes the row identifier. Cell A1 is the cell in the upper-left-most corner. Cell XFD1 is the cell in the upper-right-most corner. Cell A1048576 is the cell in the lower-left-most corner, and cell XFD1048576 is the cell in the lower-right-most corner.

You can manipulate the contents of a cell only if it is active. The active cell is indicated three different ways as illustrated below.

  1. It is surrounded by a dark border.
  2. Its column and row identifiers are highlighted.
  3. Its address is displayed in the cell address box above the workbook on the far left of the screen.

Indicating the Active Cell

You can move around the worksheet using the scroll keys or the mouse. If you want, you can also type a cell address in the cell address box and Excel will go directly to that cell.

Each cell on a worksheet can contain one of four things: nothing (it is empty), text, a number, or an expression. Cells containing text are often used as column headings or row headings.

Movie Icon

In the instructions below, the movie icon video indicates a link to a short video clip illustrating the procedure being discussed. Videos can take a while to download so be patient.

Setting Up a Worksheet

For this lab exercise, you will investigate the function y = x/(sqrt(x)+1).

1. When you are asked to enter text, the common convention is that you enter the text between the quotes but not the quotes. In cell B2, enter the text "x".

2. In cell C2, enter the text "y".

3. Starting in cell B3 and going down the column, enter the values 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, and 10. video At this point, your worksheet should look like this:

Worksheet 

4. Using a click and drag operation, highlight the range of cells B2:C13 (B2 through C13) and click the center alignment button. video

Center Text Button

5. Highlight Cells B2:C2 and add a bottom border to these two cells. (If you click on the little down arrow next to the border button, you'll see a whole list of border options.)

Border Button

6. In cell C3, enter the text "=B3/SQRT(B3+1)". When you tap the Enter key, you should get a value of zero.

This is an example of an Excel expression. The equal sign tells Excel that you are entering an expression rather than text. The occurrences of B3 in the expression, tell Excel to use the value found in cell B3 when performing the indicated arithmetic operations. In this expression, B3 is called a relative cell reference. Since the formula was entered in cell C3, the relative cell reference B3 refers to the cell that is "one cell to the left" (since B3 is one cell to the left of C3).

SQRT is an example of an Excel function and indicates the square root function. An argument to a function is the data the function needs to perform its task. The square root function needs a single numeric argument and the function returns the square root of that argument. In general, a function has zero or more arguments and these arguments are always enclosed by parentheses (even if there is no argument). If the function has two or more arguments, they are separated by commas. In this case, Excel will find the square root of the sum of the value in cell B3 and 1.

7. Click on cell C3 to make it the active cell. Double-click on the fill handle (the little black square on the lower right of the cell outline).

Fill Handle

 This is the fastest way to copy the contents of a cell down a column. Excel copies the cell contents as far down as the adjacent column (column B in this case). You can also copy the contents of a cell down (or across) by dragging the fill handle.

When Excel copies an expression, it automatically adjusts the relative cell references accordingly. In this case, for each row in the table, the expression in a given cell will be evaluated using the value immediately to its left. For example, if you look at the contents of cell C4, you will find the expression "=B4/SQRT(B4+1)".

8. Highlight cells C3:C13 and click the increase decimals button so that all of the values in the y column are displayed to two decimal places.

Increase Decimal Places Button

This button increases the number of displayed decimal places and the button right next to it decreases the number of displayed decimal places. Changing the number of displayed decimal places does not change the value of the number itself, just the way the number is displayed.

Your worksheet should now look like this:

 

Creating a Line Chart

A chart or graph is just a way of illustrating information visually. With practice, creating a chart is relatively easy.

Insert the Chart video

1. Highlight the column of y-values (C3:C13). 

 

2. Click the Insert tab at the top of the Excel window:

Insert Tab

3. In the charts section click on the Line chart button:

Line Chart Button

4. Select the basic line style button at the upper-left:

Basic Line Style Button

Excel creates a basic line chart based on the data you highlighted:

Notice that, by default, Excel has set the horizontal axis labels to integer values starting with one. This is incorrect because your data started at zero not at one. You'll fix this in step 5 below.

You should also notice that Excel has activated the Design tab in the Chart Tools section:

Design Tab 

Set the Horizontal Axis Labels video

5. On the Design tab, click on the Select Data button:

Select Data Button

6. Click the Edit button on the right side in order to edit the horizontal axis labels:

Edit X-Axis Values 

7. When the Axis Labels dialog appears, highlight the range of cells containing the x-values. These are the values that should appear as the horizontal axis labels.

Highlight Horizontal Axis Labels 

8. Tap the OK button on the Axis Labels dialog box and again on the Select Data Source dialog. The horizontal axis labels should now read 0 to 10.

Go to the Layout Tab

9. Click on the Layout tab of the Chart Tools section : 

Layout Tab 

Set the Chart Title video

10. Click the Chart Title button. Then, click the Above Chart option. Type in the chart title, Y = X / SQRT(X+1), and tap the Enter key.

Set the Horizontal Axis Title video

11. Click the Axis Titles button. Then, click the Primary Horizontal Axis Title button. Then, click the Title Below Axis button. Set the horizontal axis title to "X". 

Add Horizontal Axis Title 

Set the Vertical Axis Title video

12. Click the Axis Titles button. Then, click the Primary Vertical Axis Title button. Then, click the Horizontal Title button. Set the vertical axis title to "Y".

Set Vertical Axis Title 

Delete the Legend video

13. When you are charting only one set of values, you do not want a legend. Click the Legend button. Then, click the None button.

Turn off the Legend 

Position the Axis on Tick Marks video

14. Finally, click the Axes button. Then, click the Primary Horizontal Axis button. Then, click the More Primary Horizontal Axis Options button.

Axis Options 

15. Near the bottom of the Format Axis dialog, check the Position Axis on tick marks button: 

Position Axis on Tick Marks 

 Your completed chart should look like this:

 

Submitting Your Workbook via Email

1. Save your workbook as "Lab00.xlsx". You may save it in your user space or on a flash drive. If you are using your own computer you might want to save the file on your hard drive. If you are using a lab computer (in Hamann-Ray or in Kinlaw Library), do not save your work on the local hard drive because it will be erased when you log out.

2. Submit your workbook as an attachment to an email message sent to me at [email protected]. Enter "Lab0" as the subject of your email. The email message might read something like, "Here is my workbook for Lab 0."