A spreadsheet application

  E x c e l  

CPT 105 · Franklin College · Erich Prisner · 2002-2007

Contents:


Basics about Excel

1. Worksheets, Rows, Columns, Cells, Addresses

Excel is a spreadsheet program. An Excel file is called workbook. It contains one or several pages, called worksheets. Each worksheet is divided into columns and rows. The small rectangles (intersection of rows and columns) are the cells. Columns are labeled by "A", "B", "C", ... rows by "1", "2", 3", ... You can read off the address of the cell by its coordinates, as A4, B4, and so on. 

2. Cells

Cells have a certain content---what is showing---and a certain formatting---how it is displayed. Note that the content of a cell is different from its address.

Into cells you can type one of the following:

Example: Download the Excel file, save it into your "My Documents" folder and open it from there (see also the picture above). It is a simple sheet displaying how your money grows on the bank.The yellow cells are the main input cells (but B5, B6, ... B19 are input cells too), the orange ones are the output cells. Type something deifferent in the yellow cells (choose a different interest rate and a different principal) and see how the displayed values of the output cells change automatically.

What you should be able to do

3. The Menus, the Standard Toolbar, undo,

Please check the menus and submenus. As usual with Office programs, you can copy and paste contents of cells using "Ctrl-c" or the copy button and "Ctrl-v" or the paste button . Use also the cut button or "Ctrl-x". You can also copy whole blocks. Note that the widths and heights (in number of cells) of the blocks must coincide. When you copy, both contents and formatting are copied. (If you want to avoid this, try the menu "Edit|Paste Special" and check what you want to paste.)

Very important is the Edit|Undo menu or Undo button . Use it!

4. Selecting cells, navigating,

Click on any cell. A bold border around the cell shows, to indicate that the cell is selcted. Now type something. The text or number is shown in the cell and in the formula bar (see the upper "Hello" in the example. The cancel and enter buttons are visible when in edit mode. You leave the cell by pressing the "enter" button, or by pressing the "enter" key.

You can navigate by clicking with the mouse somewhere else, or by using the arrow keys.

If you want to edit text or numbers in cells, you have to select it again, and then go with the mouse in the formular bar to get a cursor. No cursor will show if you move the mouse over the selected cell.

You select a whole row by clicking on its row number (in the gray border area). Likewise columns are selected by clicking on the column letter in the grey area.

5. Data Types:

Let's assume you type the number 0.654321 into a cell. Depending on the formatting of the cell, it may displayed as 0.654321 or 0.65 or 65% or 0.65$.

6. Formatting Cells:

To format cells, you should use one of the following:

7. Formatting the Whole File

8. Conditional Formatting

The format of the cell changes, depending either on the value of the cell itself ("cell value is"), or depending on the value of other cells ("formula is"). Click the menu "Format|Conditional Formatting". Fill out the form, and the click "Format" to choose the format you want in case the condition is fulfilled.

9. Formulas

Formulas or function almost always depend on the values of other cells. These other cells are referred to by their address. With formulas, the values of some cells are used to compute the value of the present cell (in which the formula is written). Note that the formula is written in this cell, but what is displayed is the value of the cell, the result of this formula. Formulas always start with an "="-sign.

Formulas should almost always contain the addresses instead of the data (numbers)! The reason is that these input data numbers may change. The output values will only change automatically if the formula refers to the corresponding cell by its address. Type cells address into formulas by clicking on the corresponding cell or by typing in the cell address.

10. Basic Mathematical operations:

For addition, subtraction, multiplication, division, simply use +, -, *, /. Don't forget to use parantheses. The formulas "=A2+A3*A4" and "=(A2+A3)*A4" have different results. Since dates are (internally) numbers, you can also add, subtract, multiply, or divide them.

In the "Money on the Bank" example above, the formula in cell D5 is "=D3*(1+D2)", and the formula in cell D6 is "=D5*(1+D2)".

11. Copying Formulas or Functions:

If you copy a formula to some other location, the formula changes. Let's assume you have numbers in the cells A1, A2, A3, B1, B2, B3, and the formula "=A1+B1" in cell C1. Then the displayed value in cell C1 is the sume of the numbers in A1 and B1. If you copy the formula in cell C1 into cell C2, the formula changes into "=A2+B2". Excel makes kind of intelligent changes here, it assumes that C1 relates to A1 and B1, just as C2 relates to A2 and B2. In other words: Excel computes the relative position of each reference, and tries to keep these relative positions.

You can also copy one formula to a whole block. Another fast way of copying numbers or formulas to a column or row is the fill down and right feature. You select the cell which you want to copy. Then you grab the small black square in the lower right corner, the fill handle, and drag it down or to the right,

In the "Money on the Bank" example above, if you write the formula "=D5*(1+D2)" into cell D6 and copy it down to D7, D8, ... the formulas change into "=D6*(1+D3)", "=D7*(1+D4)", ... which don't make sense at all. Changing the address D5 to D6 and D7 makes sense, here relative referencing is appropriate. But the interest rate is always in D2, so this address should never change. Therefore, we rather type the formula "=D5*(1+$D$2)" into cell D6. When copying this version down to cells D7, D8, ... we obtain the correct formulas "=D6*(1+$D$2)", "=D7*(1+$D$2)", ....

12. Functions as prepackaged formulas.

Functions are abbreviations for (more or less complicated) formulas. For instance, instead of typing in "=A1+A2+A3+A4", you could use the function "=SUM(A1:A4)". Instead of the formula "=(A1+A2+A3+A4)/4", you could use the formula "=AVERAGE(A1:A4)". Functions consist of function name (like "SUM" and arguments in parantheses. You can type the function, or use the function wizard to create the text, in case you don't remember the function name or the syntax.

In the "Money on the Bank" example above, we need the exponential function EXP() for F5, F6, ... We type "=$D$3*EXP($D$2*B5)" into cell F5 and copy it down to F6, F7, ... to get "=$D$3*EXP($D$2*B6)" and "=$D$3*EXP($D$2*B7)" there. For refering to interest rate and principal we use absolute referencing, but to time (B5) we refer relatively.

13. Important Functions

13b. More Functions

14. Sorting

You can sort the rows of some table, depending on the values in some column. Just highlight the whole table, and choose the menu Data|Sort. Decide based on which column you want to sort, and do it. If it's just one column, you can also use these buttons   on the standard toolbar.

15. Charts

Highlight a table, preferably including labels, and click on the Chart Wizard button . Select Bar, Line, or Pie Chart, and select whether you want to view the rows or the columns as independent variable.

To insert a data series (row or column, depending on your chart) to your existing chart, you select (highlight) the data celss to include, copy the whole part, click on the chart and paste.

To insert labels on the x-axis, you select the row or column containing the labels, copy it, click on the x-axis on the existing chart, and paste.

16. Macros


Some more features

Statistics

 Go to Tools|Data Analysis and choose. 

Several Worksheets

Each file contains several pages (sheet1, sheet2, ... visible in the left bottom corner. Click on that, on you turn pages to some new sheet. You may also rename the sheets, shuffle them, and you may, of course, reference between sheets.

Filter

are similar to queries in databases.

Comments

To include a comment to a cell, rightclick on the cell and choose "insert comment" from the menu. Type your comment in the small yellow rectangle. You can view the comment by holding the pointer over the cell.

Protection

Sometimes you want to ...