Overview
Excel Excel is a "spreadsheet" program. Spreadsheets are often used for financial or business information such as budgets, and for any data in the form of a table of rows and columns. You can label and format data to present it as information. Formulas can calculate new data using data already entered. You can also use Excel to make charts such as pie charts and bar graphs, based all the data, that which is entered directly, and that which is calculated using formulas. 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. I. Worksheet - a series of cells arranged in rows and columns, with data in the cells A. Layout is freeform - you design it. B. Rows are labeled with numbers and column with letters, cell is labeled by row and column, e.g. A1, B3. 1. Cell label appears in Name Box in upper left
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. C. What can you put in a cell? 1. Usually numerical data is the most important cell content (budget) 2. Can also be text - labels 3. Other... D. 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. 1. Much less common but can be useful: text formulas, for example to combine "first name" and "last name" into "name" II. Selecting - act on the selection as a whole, usually for formatting A. Click in a cell to select it B. Click on a column or a row label to select the whole row or column C. Drag over a range of cells, rows or columns to select the whole group, say B3:F6. D. Hold down <Ctrl> and click to add to selection (or, if it is already selected, to remove it from the selection)
2.
Page 1 of 10
Excel Overview E. Selected cells are indicated by: 1. Heavier outline 2. Row and column labels highlighted 3. First cell in a group is shown in the Name Box at the upper left of the Excel window III. Entering data in a cell. A. (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.
B. Finish entering the data as in the figure above. (Enter $250,000 into G6.) C. Finishing data entry - just typing the data does not finish entering it in a cell, you must finish the data entry by one of: 1. Tap the <Enter> key 2. Click on another cell 3. Tap an arrow key to move to another cell. NOTE: when you edit a cell or a formula, the arrow keys will be used for keyboard editing, not for finishing entry, so you cannot always rely on this method of finishing entry. D. Confirming data entry - you can confirm that data is entered when the data is displayed but the cell is not selected. E. Complete entering data as shown above F. Column F, cells 1 through 6 and cell G1 are labels; cells 2 through 6 in Column G are numbers. Both are data to Excel - it doesn't "know" that "April" is a label for "$200,000.". G. If you are following along, for safekeeping, save this Excel spreadsheet to your floppy diskette. IV. Saving Excel files A. Use menu item File > Save As... Save and Save As... work the same way as in Word B. File name should remind you of what is in the file C. Default for extension in File / Save As... is .xls - do not change this
Page 2 of 10
Excel Overview D. Will also Save As HTML... - HTML table. Lose formulas, preserve displayed values, though V. Formatting a cell A. Formatting text as in word - font, font size, bold, italics, left right and center. Formatting applies to a whole cell, cannot change formats within a cell. B. Format numbers - different types. Choose menu item Format / Cells... C. Auto formatting done when you type in a number for %, $, / (date), : (time) but you can override these using Format / Cells... D. Format Painter works as in Word - copy formatting from one cell to another by: 1. Click in the cell with the right formatting to select it 2. Click on the Format Painter icon
Click in the cell that you want to format, to transfer the formatting using Format Painter 4. To transfer formatting to more than one cell, double-click on Format Painter, then use <esc> key to stop formatting VI. Editing (changing) a cell A. Can replace contents of a cell by clicking in it to select it and then typing the new content just as if you were entering data for the first time. B. Can remove all content from a cell by selecting it and tapping the <delete> key. C. Editing the contents of a cell: click in the cell, type <F2> - see the insertion point where typing will appear 1. Keyboard editing with arrow keys, <delete>, <backspace> 2. Finish editing with <Enter> or by clicking in another cell, since the arrow keys are used for keyboard editing VII. Formulas - calculate result in a cell based on numbers in other cells, for example adding or multiplying two cells A. ALL EXCEL FORMULAE BEGIN WITH "=". Could this be important on a test? B. Sum 1. Enter Total in cell F7 as a label. 2. Click in cell G7 to select it 3. Type =sum(
3.
Page 3 of 10
Excel Overview 4. With the mouse, drag over the numbers you typed, from G2 to G6. Do not include any other cells.
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 C. 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).
5.
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
Page 4 of 10
Excel Overview numerical result is in shown 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
Page 5 of 10
Excel Overview entire cell, not part of one.
D. 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.
Page 6 of 10
Excel Overview
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.
Page 7 of 10
Excel Overview 3. Now repeat the Edit > Fill > Down and it will work, as shown below. Notice that the percentage formatting is copied also.
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. 5. Save your worksheet. E. Auditing cells. It can be difficult to check that a formula is correct. Excel's auditing tool helps out here. Use auditing by 1. Clicking in the cell whose formula you want to check, to select that cell 2. Choosing the menu item Tools > Auditing > Trace Precedents (Precedents are the cells used in a formula). Blue arrows show which cells appear in the formula in the selected cell (see figure below, but you would not see the menus and arrows at the same time)
4.
VIII. Charts - Excel will make many different types of charts from the data in the worksheet A. 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.
Page 8 of 10
Excel Overview B. 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!)
C. 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.
D. Change one of ...