Excel Tutorial

Quick Index

Getting help in Excel

To enter data into a cell

To select Cells in contiguous and non-contiguous regions

Enter a formula into a cell

Make plots of data in cells

Practice what you have learned

1. To enter data into a cell
1. Select a cell. When you click on a cell, it is "open". When you click on another cell, or press tab or enter, the cell becomes "closed". Be sure you close the cell you are working on before entering anything into another cell.
2. Select a cell, enter a number, then press return. The next cell below is now opened and ready for data entry.
3. Select a cell, enter a number, then press the tab key. The adjacent cell to the right is now opened and ready for data entry.
4. You can also move from one cell to another using the arrow ¬ ® ¯ keys.
5. To enter a number into a range of cells - Select a cell, and enter a number. Then click on the small box at the bottom lower right of the selected cell and drag vertically or horizontally to fill a range of cells. Notice that all the cells you select will be filled with the number. OR Select a cell, and enter a number. Then highlight the cell with the number and the cells to be filled. Then
1. go to Edit and select Fill and the direction of the cells to be filled from the original one (for example, down) or
2. press and hold Alt then press e. Release Alt and press i followed by one of u, d, r or l.
3. Each cell has a row and column "address". The upper leftmost cell has the address A1. Columns are letters and rows are numbers.

Enter a "1" into cell A2. Select cells A2 to A10. Then go to the edit menu and choose "Fill ---> Down". Notice that all of the selected cells get a "1" in them. Ok, marginally interesting. Enter a "1" into cell A12. Select cells A12 to A20. Then go to the edit menu and choose "Fill ---> Series". A dialog box comes up and you fill in the step value and the stop value. Put 2 into the step value and 20 into the stop value. When you click "Ok", the cells fill down as integers stepping by 2. Only the selected cells fill.

Experiment with this, and the other fill options.

2. To select Cells in contiguous and non-contiguous regions
1. To select contiguous cells - click in a cell and drag to select adjacent cells. To practice: select a row of cells, a column of cells, and a block of cells.
2. To select non-contiguous cells - select one cell or a non-contiguous block of cells. Then hold down the Ctrl key and click on another cell. If you are selecting another block of cells, hold down the Ctrl key and drag over the cells to be selected. Any number of non-contiguous areas may be selected.
3. Enter a formula into a cell

A formula is an equation that analyzes data on a worksheet. Formulas perform operations such as addition, multiplication, and comparison on worksheet values; they can also combine values. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. The following example adds the value of cell B4 and 25 and then divides the result by the sum of cells D5, E5, and F5.

=(B4+25)/(D5+E5+F5)

1. All formulas start with the = sign. This is the message that the following items are a formula, not just a string of characters. You can either type = or click on the = sign on the tool bar just above the body of the spreadsheet.
2. First, select a cell, then enter "=3" (omit the quotes). You have set the cell equal to 3. Big deal. But, try "=3*5". The cell contents are now 15. You can do calculations this way.
3. Type the formula: "= sqrt(45)" (omit the quotes).
4. Click on the = sign on the tool bar. You will then see a down arrow( Ñ ) button at the left of the tool bar. Click on it and you will see names of common formulas. Try using one. If you need help click on the ? button and a wizard will appear. If you do not see a formula you want, click on the words "more functions" and you will see an index of all formulas available. Select the one you want.
5. Enter a formula into a range of cells. Select a cell, and enter a formula. Then click on the small box at the bottom lower right of the selected cell and drag to fill a range of cells. This is the same procedure as used for filling cells with a number, but there is a twist.

Fill a series starting at 1 and counting to 10, in cells A1 to A10. In cell B1, type "=A1". Click and drag the expansion "handle", to select B1 to B10. Now you should have the value of the number in the A column in the corresponding cells of the A column. Click on any of the cells in the B column and notice, for example that B3 will have the equation "=A3" in it. The formula in each cell has been changed so that each B cell refers to the corresponding cell to the left. This property is quite handy, and occurs during may Excel formula expansion operations.

BUT what if you do not want to change the cell reference in the formula? This might happen if you want to have a series of formulas that refer to a cell that has a constant value in it.

6. Refer to cells in relative and absolute forms (in equations).

Try this: Into cell B1, enter the formula "=A1*$C$1". Then, enter "5" into cell C1. Then drag the "expansion handle" of B1 to fill down toB10. Notice that the formula in each of the B column cells advances in the first term, but still refers to $C$1. The dollars signs force Excel to make an "absolute reference" to the cell C1. Note that you could have put in C$1 or $C1, which would only have held the row (the "1") or the column (the "C") constant as the formula was expanded. You will see many uses for this interesting behavior of Excel formula expansion as you become more familiar with it.

7. Fill cells with series and random data
1. Clear your spreadsheet. Into cell A1, enter "=rand()*100". Then, expand the formula down to A20. This puts a random number into each of the A1 to A20 cells. This random number is equally likely to occur between a lower and upper limit. Can you guess what the limits are?
2. Into cell A21, put "=sum(A1:A20)". This is a formula that adds cells A1 to A20 together. Notice the answer.
3. Now, change the formula in A21 to "=sum(A1:A20)/count(A1:A20)". The "count" function simply counts the number of cells that contain a number, so it's value should be 20. This is the average value of the numbers in the column. Press the keys: Ctrl= (On a Mac Apple=). That is, hold down on the Ctrl (Apple) key while pressing the equals sign "=". Notice that the numbers change. This command forces Excel to redo all calculations on the spreadsheet. Since the numbers are random, new random numbers are computed. Notice that the average value changes also.
3. Make plots of data in cells.

1. Click on the Insert at the top of the Excel toolbar and then on chart. You should see a Chart Wizard, to help you design your chart.
2. Select a plot that is a series of vertical bars representing the values of your random numbers in the range A1:A20.
3. Click on Next then choose the area on the spreadsheet that includes your data. You can either type the range into the data range window or click on the red arrow which sends you to the spreadsheet. There you can highlight the range of data to be plotted and then hit enter. Note you should also be sure to indicate whether your data sets (if more than one) are in rows or columns. When you have set the correct range, click Next.
4. The third page of the Wizard provides many options for enhancing your chart. Experiment with these to see what they do. Then click Next.
5. The fourth page of the Wizard gives you the option of either plotting the chart on the same page as the data, or of putting it on a separate page in the workbook.
6. Press Ctr= (Apple =) and watch the chart change as the data in the random number column changes. You can see that the chart reflects whatever is in the cells selected for plotting. If these data change, so does the chart.

Practice what you have learned

Expand the formula from A1 to E1, then expand each column down to the 20'th row (e.g. C1 would be expanded down to C20, etc). You should end up with a block of random numbers. Examine the formula in several cells to convince yourself that this is so. Expand A21 to E21. Examine the formulae to make sure that it is computing the average value of the random numbers in the column above it.

Make a bar chart of the 5 average values contained in A21 to E21. Set the vertical axis to a minimum of 40 and a maximum of 60. You do this by clicking on the vertical axis of the chart until a dialog comes up that lets you select the "Scale". You may have to experiment a bit. Explore. Press Ctr = (Apple = ) and watch the bar chart change.

5. Getting help in Excel

Click on the help icon at the top of the Excel toolbar. Browse through the help index. Most of your questions can be answered using this help feature. Be sure you become familiar with how to use it. It will save you much time.