Module 2 notes
2.0 Introduction to Spreadsheets
Programs to manipulate spreadsheets were among the first applications to earn the title “killer app,” since
they demonstrated that computers could be used effectively by those who were not expert computer
scientists. The first successful spreadsheet program was VisiCalc, invented by Dan Bricklin and Bob Frankston
(Harvard Business School) in 1978.
Photo from D. J. Power, “A Brief History of
VisiCalc provided automation for common paper-based accounting practices, as do all spreadsheet programs
that came after it. Therefore accountants and other financial officers were familiar with the user interface
and found it natural to enter data and formulas and interpret the output that was presented. Soon others
found the simplicity of working with rows and columns of data convenient for many diverse applications, and
spreadsheet programs grew in popularity. Today spreadsheet software is a part of every collection of
productivity tools. Some examples of spreadsheet software are Excel (from Microsoft), Quattro Pro (from
Corel), Lotus 1-2-3 (from IBM), and Calc (from OpenOffice.org).
In CS 100, you will use Excel or Calc, both of which can work with .xls files. Throughout these notes,
examples will be displayed from Excel, but they would appear essentially identically in any other
spreadsheet application. There are many popular books that describe how to use Excel; the sections that explain the workings of the
spreadsheet itself can help you understand how to use any spreadsheet software, but the sections that
explain how to use the menus and other support functionality are peculiar to the specific software and may
not carry forward even within the Excel product line. Therefore the first thing to learn is how to use the
help features of the software you are going to use. Whenever you cannot find a particular feature on the
system you are using or whenever you need extra examples of some of the spreadsheet functions, you should
consult the online Help documentation provided with your system.
2.0.1 Using Excel for the Mac
The CS 100 Lab’s Macs all have Excel pre-installed; if you are using your own Mac, you need to install either
Excel or Calc. The standard toolbars for Mac’s Excel are shown below. Note the Help menu at the top of the
window, as well as the Help button (the yellow icon including a black question mark) on the right side of the
standard toolbar. Notice also the formula bar, which is one of the major input areas for Excel.
2.0.2 Using Excel for Windows
There are two standard versions of Excel available on PCs. Excel 2003 is part of Office 2003, and uses
standard toolbars similar to many other applications on Windows. If your software is more than a couple of
years old, you will likely be using Excel 2003. Again notice the Help menu at the top, the Help button at the
right of the list of icons beneath it, and the formula bar on the following figure:
In an attempt to make menus more natural to users, Microsoft created a new method for presenting menus
as part of Office 2007, and therefore Excel 2007 has quite a different look and feel. In place of traditional
drop-down menus, each menu behaves like a folder’s tab, opening a display of icons on the so-called Ribbon when it is clicked. There is no Help menu, but there is again a Help button on the right side near the top of
the window. The formula bar, however, has not changed.
2.0.3 Using Calc
As an alternative to Microsoft’s proprietary software, Sun Microsystems developed an “open source” suite of
applications based on the Open Document Format and available from OpenOffice.org. Open Office can be
downloaded for no charge as follows:
1. In your browser, open the page at http://www.openoffice.org/.
2. Navigate to Open Office’s download site.
3. Click “Download…” to get the software.
4. Execute the file you download to install the software.
The installation provides not only Calc for manipulating spreadsheets, but also applications for working with
documents, presentation slides, images, and databases. We will not explore these other applications in CS
100, but you might find them extremely useful outside of this course.
Calc uses a menu system much like other spreadsheet applications. Notice again the Help menu, the Help
button, and the formula bar.
2.1 Spreadsheet Basics
An electronic spreadsheet is similar to a traditional paper spreadsheet, which deals mainly with numeric
data (e.g., budgets, taxes, inventory, etc.). An electronic spreadsheet, however, is much more powerful and
versatile. For example, an electronic spreadsheet can automatically perform calculations and recalculations
when data is updated. The following figure shows a sample worksheet with gridlines, row headings (1, 2, 3,
…) and column headings (A, B, C,...). The workspace for a spreadsheet file is called a workbook. Each workbook includes one or
more worksheets that are large grids of rows and columns as in the figure above. Each column (vertical) is
represented by one or more letters, and each row (horizontal) is represented by a number. The intersection
between a row and a column is called a cell. Each cell is identified uniquely by its location within the grid,
and this unique combination is called the cell’saddress. An active cell’s address is shown in the Name Box in
the Formula Bar. In the above figure, the active cell (outlined in black) is found in column D and row 2 (as
indicated in orange); its address is therefore D2, which is displayed in the Name Box.
It is often convenient to refer to a rectangular region of cells, known as a cell range. Cell ranges have two
addresses separated by a colon. Ranges can span multiple columns and rows (e.g., D4:H8 is highlighted in
the figure above), part of one column (A6:A15), part of one row (E11:G11), or a single cell (B3:B3). The
range notation is also extended to be able to define a complete column (J:J) or a complete row (17:17),
even though these range descriptors do not involve a pair of valid cell addresses. (Be sure that you
understand in what way these are not pairs of valid cell addresses.)
2.1.1 Kinds of Cell Entries
In a worksheet, every cell can contain a value and can be displayed using a suitable format. There are three
kinds of entries that can be placed in a cell:
o Labels (e.g., Cost, 12 Arbour St.); o Numeric values (e.g., 34, 22.45);
o Formulas (e.g. =B3/A2, =10*B3).
A label can contain letters, punctuation, digits, and other characters; it can be formatted using any of
various fonts and sizes. (Labels are often called text strings, character strings, or just strings in other
applications.) A numeric value can contain a number only, including positive and negative values, with
several decimal places. Note that a phone number such as 555-1234, however, is a label because it contains
both text (the hyphen representing something other than negation) and digits (5551234).
Numeric values can also be used to represent financial information, and these can be displayed using
common financial notations. For example, a value in dollars (e.g. $22.50) is a number even though it is
displayed with a non-numeric character ($). If such a dollar amount were to be represented in a cell, the
value would be the number 22.50 but the cell would be formatted to look like currency. Other common
number formats include scientific (e.g. 1.23E+05), date (e.g. 17-May-03), time (e.g. 12:26:53PM),
percentage (e.g. 15%), and fraction (e.g. 5/8).
Note that if you are using Calc on a Mac, by default two or fewer decimal places will be displayed in all cells
containing numeric values. This behaviour differs from Excel and other versions of Calc. To ensure that you
will see the same results for your calculations as Excel users do:
1. In the preferences for Calc (menu OpenOffice.org > Preferences...), select OpenOffice.orgCalc >
2. Change the number of decimal places displayed to 10.
Unlike numbers and text, when a formula is entered, it must begin with an equals sign. We shall examine
formulas in more detail shortly.
1. Consider the following spreadsheet: 2. What is the value in cell B19? What type of value is this?
3. What is the range of addresses that are enclosed in the box that includes the cell with the label
4. What is the range of addresses for the cells that are displayed in a Currency format?
5. How many labels are stored in cells in the range B6:C13?
2.1.2 Entering and Modifying Values
To enter a value in a worksheet, click on the cell in which you want the value to appear and then type the
entry. The spreadsheet software changes to entry mode as soon as you begin to type. Entry mode is
distinguished by the next to the formula box in the formula bar. While in entry mode, clicking
on cancels the current changes being made (like Undo) and clicking on completes the cell entry.
Another way to complete the cell entry is to press the Enter or Return key. Once the formula is completed
(e.g. by clicking the check mark), the worksheet is no longer in entry mode.
There are three ways to change to entry mode. To replace the contents of the cell or to add contents to an
empty cell, just begin to type the new value: you are switched to entry mode automatically. If, however,
you need to modify the current cell’s contents, click in the formula box or double-click on the cell to switch
to entry mode.
1. Download the file Photocopy.xls and open it.
2. Click in cell B16 to make it active.
3. Change the value in cell B16 to be 30. 4. Change the value in cell D10 to make it display $0.20.
5. Make cell A3 active. What value is stored in that cell? What value is stored in cell B3? Notice that the
display for labels can extend outside of the cell boundaries. Enter the value 0 into cell B3. Notice the
effect on the display of the value in cell A3.
6. Not all cells need to be the same size. Extend the width of column A as follows:
Hover your cursor over the line dividing columns A and B at the top of the spreadsheet window so
that it displays as a double-headed cursor. Then click and drag to the right to get an appropriate
width for viewing all of the data in cell A3.
7. Increase the height of row 11 by similarly dragging down the boundary between row 11 and row 12.
8. Save the file in your CS 100 folder under the name NewPhotocopy.xls. (The menu item you need
might be labeled Save As…; if you do not know how to save a file, find the information in the Help
documentation for your system.)
9. Decrease the width of column D by dragging its right boundary to the left until it is approximately at
the slash in the label cost/sheet. Note that the numeric values are displayed as #### and the label
in cell D6 is truncated. Such a sequence of number signs is displayed whenever a cell is too small to
show its numeric value, and the label in cell D6 is truncated rather than extending into E6 because
its format is set as “Wrap text.” Fix the column widths so that all cells are properly displayed again.
The primary function of an electronic spreadsheet system is to create formulas to perform calculations that
summarize data. When entering a formula, you must begin with an equals sign to distinguish it from a label
or a number. Formulas can contain any combination of values, cell references (see Section 2.2.2), cell
names (see Section 2.3) or functions (see Section 2.4). Here are some examples of spreadsheet formulas:
= 25.4 + 0.15 adding two numbers
dividing two values located in two different
= A4 / B3
= SUM(A5:A6) using a function to add the values in a range
= 12 * Tax_Rate multiplying two values, one of which is
located in a named cell 2.2.1 Order of Operations
When two operators appear in an expression, which one is done first? For example, is 4+5*2
equal to 18 (the addition before the multiplication) or 14 (the multiplication before the
addition)? Spreadsheet formulas follow the same order of operations used with standard
mathematical formulas, just like you learned in school: multiplication is always performed
before addition, unless brackets group things together to indicate otherwise. The phrase
used to remember the order is BEDMAS, which stands for Brackets, Exponents, Division &
Multiplication and Addition & Subtraction. Remember that within their order group, the
division and subtraction operators are executed left to right. For example, 1-2+3 = (1-2)+3
= 2 is not the same as executing the addition first 1-(2+3) = -4. Similar reasoning is required
when we will deal with Boolean expressions for search queries later in the course.
In most computing applications, superscripts cannot be used to write an exponent (e.g. 2 to indicate 2*2*2)
and so the caret character is used instead (e.g. 2^3). Note that the caret is entered by holding the shift key
and typing 6.
1. What answer would the following spreadsheet formula produce?
= (2 * 16)/2^2+16-4
2.2.2 Cell References
Much of the power of spreadsheets comes from using formulas that include one or more cell references
rather than using numbers only. For example, the formula =5*A3 produces the value equal to five times
whatever is currently in cell A3: if A3 contains the value 2, then the formula produces 10, but if the value
in A3 is subsequently changed to 3, the formula automatically produces the value 15. Thus cell references in
formulas play the same role as variables in conventional mathematical formulas. Just as a mathematical
formula can contain several variables, a spreadsheet formula can contain several cell references.
1. Complete the following table: for each row, show the result of the formulas at the top of each
column when the cells referenced have contents as shown in the first three columns. For example,
the first formula produces 18 when cells G7, G9, and H7 have the values 15, 6, and 3, respectively;
similarly the second formula produces 4.5 when those cells have the values 13.4, 3, and 1.5,
respectively. G7 G9 H7 =G7+3 =G9*H7 =(G9-1)*(G9-H7)
15 6 3 18
-20 4 5
13.4 3 1.5 4.5
2. Open the file NewPhotocopy.xls again. In cell E8, enter the formula =D8/C8, which reflects the cost
per page for Double-sided copying. You should see the value $.06 displayed in cell E8. Click on that
cell and look in the formula bar (above the top of the spreadsheet) to verify that the cell’s content
is still actually a formula, not a numeric value. Now change the contents of cell D8 to $0.14 and
observe that the content of cell E8changes automatically.
3. Still using NewPhotocopy.xls, copy the contents of cell E8 (click in the cell, and then on a PC
enter Ctrl-C by holding down the Ctrl key while pressing C, or on a Mac enter Command-C by holding
down the Command key while pressing C). Paste the contents into cell E9 (click in that cell and then
enter Ctrl-V on a PC or Command-V on a Mac). Observe the value displayed in that cell; look in the
formula bar too.
In this last exercise, the formula =D8/C8 from cell E8 was changed to =D9/C9 when it was pasted
into cell E9. Notice the pattern. What would happen if E8 were copied again and pasted into
cell E10? What if it were pasted into cell D16? Try it to check your hypotheses.
A formula such as =D8/C8 is said to use relative addresses, because the cell addresses involved are
considered to be relative to the cell in which the formula is initially entered. In cell E8, the
address D8 refers to “the cell in the same row and just to the left of this cell,” and the
address C8refers to “the cell in the same row and two columns to the left.” Therefore when the
formula in cell E8 is copied to cell E9, it retains that same meaning relative now to E9, and when
it’s copied to cell D16 it again retains the relative semantics.
1. Complete the following table by showing what formula would appear in each of the given cells if the
formula shown were copied from the first cell shown. For example, if formula 1 were copied from
cell G6 to cell H7, then =H3+2 would appear in cell H7.
In cell Formula 1 Formula 2 Formula 3 G6 =G2+2 =F6*G1-1 =J10-E5
2. How can you override this “rewriting” behavior of cell addresses if you don’t want the
formulas to change when copied from cell to cell? Spreadsheet systems provide three types of cell
references: relative (e.g. A5), absolute (e.g. $A$5) or mixed (e.g. A$5 or $A5). The presence of the
dollar sign in an address indicates that the following row or column indicator is not to be changed
when copying the formula to a different cell. A quick way to insert a relative reference into a
formula is to click once on the cell while in entry mode. Other references must be entered
manually, or they can be changed from relative to absolute or mixed addresses by using function
key F4 in Excel or shift-F4 in Calc (look up “absolute address” in the Help pages for your system to
1. Complete the following table by showing what formula would appear in each of the given cells if the
formula shown were copied from the first cell shown.
In cell Formula 1 Formula 2 Formula 3
G6 =$G$2+2 =$F6*G$1-1 =$J$10-E5
2. Similarly to using Copy and Paste, a formula can be moved from one cell to another
using Cut (Ctrl-X on a PC or Command-X on a Mac) and Paste. Note, however, that when a formula
is moved rather than copied, it is not rewritten; instead all addresses in the formula remain
unchanged in the new cell. Cut, Copy and Paste are common tools that can be found in most
applications. 2.2.3 Filling Cell Entries
Often it is valuable to be able to copy a formula or value into several cells quickly. Spreadsheet systems
provide the Fill operation to enable users to “fill” information into adjacent cells. Information can be filled
up or down within the same column or right or left within the same row. In general, to fill values from a
1. Make the source cell active.
2. Click on the fill handle (a small button in the lower right corner of the active cell) and do not
release the mouse button.
3. Drag across the row or across the column across all the cells in which you want to fill the same
4. Release the fill handle in the last cell in the row or column in which you wish the formula to appear.
For these exercises, download and open the file Relative and Absolute.xls.
1. Click on cell D4 to make it active.
2. Fill down through the shaded boxes, ending on cell D9. Notice the effect on the formulas that have
been copied from cell to cell.
3. Similarly fill up from cell J9 to cell J4. Again notice the effect on the formulas.
4. Finally fill right from E14 to I14.
2.2.4 Formula Errors
An error message will appear in a cell if there is a syntax error in the formula stored in that cell.
#N/A No value is available/no
#NAME? A name used
#NUM! There is a
the one used
#REF! The formula
refers to a
cell that is
#VALUE! You are
types (e.g. adding text
Of course, just because no error message appears, you cannot conclude that the spreadsheet is correct. A
formula might specify E5 when it should have specified F5, E6 or J23; it might specify adding two numbers
when it should have multiplied them; and so forth. Unfortunately, there is no way for a spreadsheet to
report the existence of a logic error, where you erroneously wrote the wrong expression due to a typo or a
“thinko” you must proofread your spreadsheet to determine the validity of each cell’s contents.
2.3 Addressing and Naming Cells
Download and open the file Simple Sales.xls. This spreadsheet is intended to record the monthly
subscriptions for a film supplier, who charg