The Spreadsheet Continued

What is a spreadsheet?

Spreadsheet is a generic term that is the computerized equivalent of an accounting ledger.

A spreadsheet is composed of a rectangular matrix (grid) of columns and rows.

A useful feature of a spreadsheet is that it provides us with the ability to make changes and have software carry out recalculations faster and more accurately than we can by hand.

Microsoft Excel

The spreadsheet which we will use in this program is Microsoft Excel 97. It is part of the Office 97 package from Microsoft.

Parts of the Microsoft Excel window

There are several important parts of the Microsoft Excel window.

Title bar containing the Excel Icon, the Workbook name, the minimize, maximize/restore, and close buttons.
Menu Bar.
Standard Toolbar.
Formatting Toolbar.
Name Box.
Formula Bar.
Cells Area.
Horizontal Scroll bar.
Status bar.

Some basic definitions

Columns in an Excel file are vertical collections of data and are labeled by letters. A Microsoft Excel spreadsheet file can contain columns labeled from A to IV.

Rows in an Excel file are horizontal collections of data and are labeled by numbers. A Microsoft Excel spreadsheet file can contain rows labeled from 1 to 16,384.

A cell is the intersection of a row and a column in a spreadsheet. Whenever we speak of a cell we identify it by first its column letter and then its row number. For example, the first cell in a Microsoft Excel spreadsheet file is cell A1.

Microsoft Excel allows us to store multiple sheets in the same file. Each individual sheet is called a Worksheet. The collection of the sheets is called a Workbook.

Inputting data into our spreadsheet

When we place data into our spreadsheet file we must first select in which cell we will place the data. The data we type will be placed in the active cell. The active cell is indicated by a heavy border. In the following image, the active cell is C8.

There are three different types of data that can be placed into a cell.

Values such as actual numbers.

Notice that when the active cell has values in it, the Name Box contains the name of the active cell, and the Formula Bar contains the contents of the cell.
Labels which are called constants.
Formulas which begin with the = sign.

The ability to have the contents of a cell be based on a formula is one of the reasons that spreadsheets are so powerful. There are many different ways we can use formulas. One of the most straightforward ways is to place a formula in a cell which contains arithmetic combinations of the data in other cells in the spreadsheet.

As an example of this, suppose we have a list of numbers in one column and we wish to perform some arithmetic operation on these numbers and place the results in the second column.

When the active cell is B1 type the following =sqrt(A1).

This a built-in function to find the square root of a number. We will talk later about built-in functions.

You may also use simple arithmetic. That is you may use the standard arithmetic operators, + (addition), - (subtraction), * (multiplication), / (division), ^ (exponentiation), and paretheses.

The hierarchy of these operations is as follows.

Parentheses

Exponentiation

Division and Multiplication

Addition and Subtraction

To understand this hierarchy you may try the following exercises and check your answer with Microsoft Excel.

1-3*2^5-3^2*4
2+7*4/3+2-1*4+3
3^2*4/5+3*2
3^2^2

Arithmetic Operations with existing data

We can also perform arithmetic operations on data that exists in our spreadsheet.

For example, suppose we have already input several numbers into column B which represent the amount of money we have paid for various items which have been placed into column A.

We wish to place in column C the amount of tax that we paid on the item. The amount of tax that is paid on each item is 15%, but that is paid only after a 10% discount on the price has been given. We place this formula into cell C1 to write the amount of tax that we pay on the magazine.

Instead of having to type the same formula over again in cells C2, C3, and C4, we can take advantage of the drag-and-copy feature of Microsoft Excel. We simply move the mouse pointer to cell C1 and make it the active cell.

Notice that the formula that I typed in is displayed in the Formula Bar.

Move the mouse pointed to the lower right hand corner of the active cell until the mouse pointer turns into a plus sign. Then hold down the left mouse button and move the mouse pointer until cells C2,C3, and C4 are included, and then let up on the left mouse pointer.

Now let us place into column D the actual amount that we paid on each item.

Now in row 5 suppose we want to place the total for our items. In cell B5 we want to place the total original amount of the items. So we go to cell B5 and click the equal sign to the left of the Formula Bar. Then click Sum, and you will see the following.

Sum is an example of a built-in function. In the box Number1 we can place a range of cells.

A range is a rectangular group of selected cells. We indicate a range by using a colon. Therefore, the range which consists of cells B1,B2,B3, and B4. We identify the range by B1:B4.

Once we click OK the sum of the contents of cells B1 through B4 are placed into cell B5.

Now we may copy this formula to cells C5 and D5 by using the procedure we used before.

Notice one odd thing. The formula that is placed into cell B5 is =SUM(B1:B4), and when we copied this formula to cell C5, the formula became =SUM(C1:C5).

The reason for this is that when we typed the formula into cell B5 we used relative addressing.

Relative Addressing means that when we reference a cell in our formula, the position of that cell relative to the active cell is stored. When we copy that formula, then in the new location the cell's contents which are actually used is the cell which is at the corresponding position to the new cell.

For example, suppose in cell A1 we place the formula =C1+D1. Notice that relative to cell A1, cell C1 is 0 rows away and 2 columns to the right, and relative to cell A1, cell D1 is 0 rows away and 3 columns to the right. Then if we copied the formula from cell A1 to cell B1, then the formula that appears is the sum of the cell 0 rows down and 2 columns to the right and the cell 0 rows down and 3 columns to the right. So the formula that would appear in cell B1 is D1+E1.

Absolute Addressing means that we use the actual cell which is referenced no matter what the position is relative to the active cell. To indicate an absolute reference we use a dollar sign $. Keep in mind that if we want to make an absolute reference, then you are able to make either the row or column absolute. You can also make one be absolute and the other be relative if you wish.

We may copy formulas from one cell to another even if they are not contiguos. To accomplish this we use the manual cut/copy/paste operations. We can find these options under the Edit Menu, and there are shortcut icons on the Standard Toolbar.

For example, suppose we place the formula =$B1+C$2-D2+E3 in cell A1, and then copy this formula to cell C3. What will be the formula that appears in the Formula Bar when C3 is the active cell?

Other Features of the Spreadsheet

There are many other features that Microsoft Excel has. We will be looking at a few of them this semesters. Some of the ones that are important to us are

Changing the size of a row or column. We have the ability to increase the column width and the row height. This is necessary for example if we are typing a label into a cell which is longer than the cell can contain. While we are typing the excess text spills into the adjacent cell to the right. It is a simple matter to increase the column width so it can contain the entire label.

Simply go to the Format Menu and select Column and then in the list that appears select Width. Then choose the new Width. We can also select Row and in the list that appears select Height to change the row height.
If we wish to clear an area of the spreadsheet, then we simply select (highlight) the area, go to the Edit Menu, and then select Clear, and then select All.
We can also change the style of labels in the spreadsheet with the Formatting Toolbar.
There are many built-in functions which can be applied to data ranges. You can see a list of these if you go to the Insert Menu and select Formula.

Templates

You will be using templates in your Spreadsheet Assignment. A template is simly a boilerplate spreadsheet in which you will later place data. We will look more closely at this idea in the next laboratory. For the remainder of this laboratory, you may experiment with this notion. Try creating a simple spreadsheet in which one cell will contain the output of some function based on some of the other cells in the spreadsheet.

Editing our spreadsheet

When placing data into our spreadsheet, we may find the need to perform some modifications to it. Some of these modifications include

Changing the contents of a particular cell.
1. Move the mouse pointer to the cell and make it active.
2. To totally remove the contents you may hit the space bar and enter, or go to the Edit Menu and select Clear and All, or hit the DEL key.
3. To edit the formula contained in the cell, go to the Format Bar and make appropriate changes.
You may copy a formula from one cell to another by moving to the cell you want to copy and then going to the Edit Menu and selecting Copy. The cell will be highlighted Las Vegas style. Move to the cell where you want to place the formula. Go to the Edit Menu and select Paste.
If for some reason you need to perform some operation on noncontiguous cells, hold down the CTRL button and then highlight all cells that you want. Then you can perform your operation. You are not allowed to do a copy on multiple ranges, but you can perform a Clear.
We can also delete entire rows or columns by going to the Edit Menu and selection Delete...
The default column width is 8.43 points. If you type a formula or label in a cell and it is too long for that cell to contain, then it will spill over into the next columns if they do not contain data. If you wish to adjust the width of the column so that it will be large enough to contain the entire label, then go to the Format Menu and select Column and then select Width and then enter the width you desire.
Recall that labels which you enter into your spreadsheet can be formatted just like they were in the word processor. On the formatting toolbar you have the option to change the font, font size, and appearance of the text. If you increase the font size of the text, then the row height will be automatically adjusted. You may also manually do this by going to the Format Menu and selecting Row and then selecting Height and then entering the desired Height.
You may also adjust row height and column width by using the mouse pointer. Move the mouse pointer to the seperation between column letters or row numbers, hold down the left mouse button and then move the mouse until you have adjusted the width of the column or height of the row.
Suppose you have entered your entire spreadsheet, but then realize that you forgot to include a column or row. Move the mouse pointer to the place where you want to insert the column or row and then go to the Insert Menu and choose either Rows or Columns and a row or column will be inserted.

Number Formats

General

General format cells have no specific number format.
Number

Number is used for general display of numbers. For example, when you type =1/5, 0.2 will be displayed in the cell under the Number format.
Currency

Currency formats are used for general monetary values.
Accounting

Accounting formats line up the currency symbols and decimal points in a column.
Date

Date formats display date and time serial numbers as date values.
Time

Time formats display date and time serial numbers as time values.
Percentage

Percentage formats multiply the cell value by 100 and displays the result with a % symbol.
Fraction

Fraction displays numbers as proper fractions.
Scientific

Scientific displays numbers in scientific notation.
Text

Text displays numbers as text instead of numbers. So the numbers are displayed exactly as they are input.
Special

Special displays numbers in special formats such as zip code, phone number, or social security number.
Custom

Custom displays numbers in a format chosen by the user.

You may double click on the right boundary of a column and it will be made wide enough to contain the longest label in the column.

In order to view cell formulas, either go to the Tools Menu and select Options... and then select the View tab and then check Formulas under Window options.

If you want to be able to use your workbook with Microsoft Excel 5.0 95, then go to the File Menu and select Save As... and then select Microsoft Excel 5.0/95.

The Office Assistant is a button on the Standard Toolbar which allows you to ask questions. It will answer them and suggest tips for making Excel easier to use.

Easy ways to change the active worksheet are by clicking in the window with the mouse or by clicking the worksheet tab at the bottom of the current workbook.

Recall that we may select noncontiguous ranges by using the CTRL button.

You may choose an automatic format for your spreadsheet by selecting a range and then going to the Format Menu and selecting Autoformat... Then choose the format you want.

If a number is too large or too small to be displayed in the cell, it is expressed in scientific notation.

Some Special Functions

When you click the bold equals sign to the left of the Formula Bar you will see the formula palette which describes each argument which is input into the function, and the name box will turn into a button with the name of a function on it. Click the down arrow to the right of the button, and you will get a list of all functions available. There are a few functions which might be useful in everyday life. We consider some of them now.

SUM will find the sum of a range of cells.

MIN will find the minimum value in a set of values.

MAX will find the maximum value in a set of values.

AVERAGE will find the average value of its arguments.

TODAY will display today's date.

COUNT will count the number of cells in a range that contain numbers.

Copying Worksheets

In your Spreadsheet Assignment, you will find it convenient to be able to use one Workbook to contain all spreadsheets you will use. Some features of Microsoft Excel which will make your assignment easier are as follows.

Recall that when you start a new Microsoft Excel workbook there are initially 3 worksheets. You may insert more worksheets by going to the Insert Menu and choosing Worksheet.
If the worksheets are numbered Sheet1, Sheet2, ..., then the new sheet will be inserted out of order. You may move a sheet by holding down the left mouse button on the worksheet tab and moving the mouse pointer to a new location and then letting up on the mouse button.
You may also change the default names of the worksheets by either going to the Format Menu and selecting Sheet and then selecting Rename or by double clicking on the worksheet name.
In order to copy a worksheet, highlight the area of the worksheet you want to copy, go to the Edit Menu and select Copy. Then go to the destination worksheet, go to the Edit Menu and select Paste. The contents, including formulas, of the source worksheet will be copied.
An interesting aspect of Pasting (which you may or may not want to use in your assignment. I mention it becausing it is an interesting operation of Microsoft Excel) is that you can establish a link to an existing worksheet. You can do this by using Paste Special... and then clicking Paste Link. If you make a change in the source worksheet, then the change will show up in the destination worksheet. This does not happen if you simply select Paste on the Edit Menu.
You will also need to be able to copy a column of numbers from one worksheet to another. You can do this by first selecting the range of celss you wish to copy in the source worksheet, going to the Edit Menu and selecting Copy. Then go to the destination worksheet. Place the mouse pointer in the first position you want filled in the destination worksheet , and then go to the Edit Menu and select Paste Special... and select Values, and then click OK.