Last updated: 2/24/03
Link back to course Welcome...

Excel is a "spreadsheet" program. Spreadsheets are often used for financial or business information, and for any numerical information in the form of a table of rows and columns. Excel can also make charts such as pie charts and bar graphs, based on the information. In Excel, a table of rows and columns is called a worksheet. This comes from accounting, where these tables are used for budgeting and other types of financial analysis. A *.xls file is a workbook, which can contain several worksheets.

This in-class exercise will not be enough for you to learn Excel. This is only an introduction. The lab assignments will take you through the details.

1. Worksheet - a series of cells arranged in rows and columns, with content in the cells
   1. Layout on information is freeform - you design it.
   2. Rows labeled with numbers and column with letters, cell is labeled by row and column, e.g. A1, B3.
      1. Label appears in Name Box in upper left
         
      2. When we get to the database Access, you will be able to change the column headings, but in Excel they are fixed as A, B, C, etc.
   3. What can you put in a cell?
      1. Usually numbers are most important cell content (budget)
      2. Can also be text - labels
      3. Other...
   4. Can be formula to calculate a result from other numbers. In a formula, you use one or more cell references (references to other cells) and mathematical symbols to calculate a new value based on the data in other cells.
   5. Editing the contents of a cell: click in the cell, type <F2>
   6. Can also replace contents of a cell by clicking in it and typing the new content
2. Selecting - act on the selection as a whole, usually for formatting
   1. Click in a cell to select it
   2. Click on a column or a row label to select the whole row or column
   3. Drag over a range of cells, rows or columns to select the whole group, say B3:F6.
   4. Hold down <Ctrl> and click to add to selection (or, if it is already selected, to remove it from the selection)
3. Entering data in a cell.
   1. (See illustration below for entering information into cell G6.) Click in cell F1 to select it, type Month and tap <Enter> or arrow down to enter the information. Notice the insertion point in cell G6 in the figure - data entry is still going on; the final 0 has not been entered yet.
      
   2. Finish entering the data as in the figure above. (Enter $250,000 into G6.)
   3. Column F, cells 1 through 6 and cell G1 are labels; cells 2 through 6 in Column G are data or numbers.
   4. For safekeeping, save this Excel spreadsheet to your floppy diskette.
4. Formatting a cell
   1. Formatting text as in word
   2. Format numbers - different types. Choose menu item Format / Cells...
   3. Auto formatting done for %, $, / (date), : (time) but you can override these
5. Default for extension in File / Save As... is .xls - do not change this
   1. Will also Save As HTML... - HTML table. Lose formulas, preserve displayed values, though
6. Formulas - calculate result in a cell based on numbers in other cells, for example adding or multiplying two cells
   1. ALL EXCEL FORMULAE BEGIN WITH "=". Could this be important on a test?
   2. Sum
      1. Enter Total in cell F7 as a label.
      2. Click in cell G7 to select it
      3. Type =sum(
      4. With the mouse, drag over the numbers you typed, from G2 to G6. Do not include any other cells.
         
      5. Type ) (just the close parenthesis) to close the parentheses and tap the <Enter> key to finish entering the formula.
      6. The sum should equal $835,000.
      7. Also many other formulae - see textbook
      8. Excel will usually guess the formatting correctly, but you can override this by formatting the cell directly
   3. Ratio and percent
      1. Now we will calculate what percentage each month is of the Sum or Total (Sum and Total mean the same thing here). The percentage is the part (sales for month) divided by the whole (Total), formatted as a percentage (if you are not in Excel, divide part by whole and multiply by 100 to find percent).
      2. Click in cell H1 and type the label Percent.
      3. Click in cell H2 and start a formula by typing =. Then click in cell G2 to put the cell reference for G2 into your formula (see figure below).
         
      4. Finish the formula by typing the slash (/) for division, then click in cell G7 to put the reference to Total in the formula. Finally, type ) and tap <Enter> or an arrow key to enter the formula. The result should be as shown in the figure below. Notice that the formula bar shows the formula, but the numerical result is shown in the cell.
         
      5. Format as percent. With cell H2 selected, choose the menu item Format > Cells > Percentage with 0 decimal places.
         
         The result should be as shown below. Note: Labels in Excel can be formatted just as you would in Word, except that you can only format an entire cell, not part of one.
         
   4. Copying formulas - relative and absolute addressing. Now we want to fill in the formulas for the other months. Once you have entered one formula in a series, Excel makes it easy to copy and paste, for fill formulas into other cells in the series. Normally this works well, but here there will be a glitch, which will illustrate relative and absolute addressing in Excels.
      1. To fill in the formulas, you can copy and paste using the clipboard, but here we will use Edit > Fill to do it in bulk. Click in cell H2 and drag down to H6 (not H7). then choose Edit > Fill > Down as shown in the figure below. Finish the formula fill by clicking on the Down sub-menu item.
         
      2. Oops! See the whole column of #DIV/0!. This means that in the lower cells, Excel has a divide-by-zero error. Dividing by zero is illegal, as the answer is infinity. To see what the problem is, click in the first cell with the problem, H3, as shown in the figure below.
         
         The problem is that the formula in cell H3 says to divide cell G3 by G8, and we have nothing in cell G8, so Excel takes that as zero, giving the error. This is Excel's normal "Relative Addressing": since cell H3 (the cell we are copying to) is down one location from H2 (the cell we are copying from), then all the cell references in the formula are adjusted down one location. In the numerator, G2 is adjusted down to G3, which we want to have happen, but also cell G7 is adjusted down to cell G8, which we don't want to have happen, since the total is always in Cell G7. Relative addressing works for the numerator, but not for the denominator. We can, however, tell Excel not to adjust the cell reference to G7 by going back to cell H2 (click on cell H2) and typing a $ in front of the 7. This directs Excel to use "Absolute Addressing" for the 7; in other words, don't adjust it if copying to other cells. See the figure below. Tap <Enter> to enter the change. NOTE: You can insert the $ by either (a) clicking just before the 7 in the Formula bar as shown, or by tapping <F2> to edit the cell, and then using either the cell itself or the formula bar.
         
      3. Now repeat the Edit > Fill > Down and it will work, as shown below. Notice that the percentage formatting is copied also.
         
      4. Now copy and paste the sum formula from G7 to H7, to add up all of the percents. Since this copies the dollar formatting from G7, reformat cell H7 as a percentage with zero decimal places, as in C.5 above. The sum in H7 should be 100% after the formatting.
         Save your worksheet.
7. Charts - Excel will make many different types of charts from the data in the worksheet
   1. Drag over the Month and Sales figures in the worksheet above (cells F1 to G6) to select the data and the labels. See the figure below to double-check your selection.
   2. Click on the chart toolbar button to get the Chart Wizard as shown. (A Wizard is a series of dialogs with numbered steps on title bar - definition could appear on tests!)
      
   3. Select Pie as the Chart type (highlighted) and the second example in the top row of Chart sub-types (highlighted), then click the Finish button to insert your chart on the worksheet. If a dialog appears, click its Close Box. Your chart should look like the one below.
      Save your worksheet.
      
   4. Change one of the dollar figures by
      1. Click in the cell to select it
      2. You could just type a new number, but instead tap the <F2> key to edit the cell. You can use the arrow keys, Home, End, Delete and Backspace to position the cursor and delete digits, and then type in new digits. Make a big change; for example, double the figure for March. Tap the <Enter> key to accept your changes.
      3. Notice that the total sales, the percentages and the chart all change to reflect the new value!
   5. Save your worksheet.
8. Copying and pasting between Office applications (and other applications)
   1. Copying
      1. Your chart should still be selected (small black squares at the corners and the midpoints of each side). If not, click anywhere in the chart to select it.
      2. Copy the chart to the clipboard using any one of the the keyboard (<Ctrl>C>, toolbar button () or menu (Edit > Copy) methods.
   2. Pasting into Word (If your computer has less than 32 MB RAM, you may want to close the first application - Excel -  at this point, before opening the second - Word. The lab computers are fine, but your home computer may not be.)
      1. Open Word and type a simple letter explaining that here is the chart showing the monthly sales figures. Save your Word file to your floppy diskette.
      2. Put the insertion point in your Word document where you want the chart to go and paste the chart in using any one of the keyboard (<Ctrl>V), toolbar button () or menu (Edit > Paste) methonds.
      3. Save your file.
   3. More on pasting.
      1. What you did above pastes the picture itself into the file. Suppose that next month you wanted to add June to the chart and write the memo again (this might be a monthly task for you). You would have to repeat the whole process.
      2. Alternately, when you pasted the chart into Word, using the Menu method, you could choose Paste Special... and paste a link into the Word file. The chart would appear on the screen exactly as before, but the chart itself would be pulled from the Excel file. If you now change the chart in Excel, when you open the Word document, it will pull in the changes. These links can be "fragile" however, since if you email the Word document to someone else, they won't have the Excel file and so the chart will not appear. The other person will think that you made a mistake. So, if you are going to email file or transfer them to other people, pasting the graphic directly is a better choice, instead of pasting a link.
9. More on charting in Excel. Excel charting is very flexible, You can choose many different types of charts in the Wizard, and many different sub-types. Also, after finishing with the Wizard, you can click on the chart to edit it in many more ways that the Wizard gives you access to.