Spreadsheet basics

Robert P. Webber, Scott McElfresh, Don Blaheta, Longwood University
Why are we working on this? Spreadsheets provide a way to communicate both data and some of the algorithms and computations used to process that data in a variety of academic, industrial, and business contexts. Skills in this section: Express simple computations using spreadsheet cells Concepts: Algorithm representation

A spreadsheet is a rectangular grid of cells that is used to organize data and specify computations that should be performed on that data. A cell is a single location in the grid that can contain either a plain value (which can be a number, like 5 or 3.7, or other kinds of values like words or dates), or an expression that represents a computation that produces a value. When a cell contains an expression, it is the expression itself that is stored internally, but it is the value of the expression that is normally displayed on the screen. An expression can refer to values in other cells; and when those other cells change in some way, the expression is re-evaluated and the updated value is displayed. For this reason, spreadsheets have become a powerful tool for performing "what-if" analyses and computations that need to be performed repeatedly or on a regular basis with updated inputs.

Simple values

To enter a number into a cell, click on the cell and type the number. When you hit Enter or an arrow key or click the mouse on another cell, the spreadsheet will notice that it's a number and just display it. Most spreadsheets will align the number to the right-hand side of the cell, but otherwise nothing fancy is going on here.

If you type a word into a cell, the spreadsheet will see that it's not a number and will store it as text. Most spreadsheets align text to the left-hand side of the cell, but again, nothing fancy. This is useful for using cells as labels for data.

There are a few other types of simple values (dates and times, mostly). If you have some information you mean to be text but the spreadsheet is guessing that it's a time or a number or something else, you can force it to be plain old text by preceding it with a single-quote (or apostrophe).

Expressions

Spreadsheets can perform numeric computations, and the basic language for specifying them is what we've been calling "in-line notation". But if you just type in one of those expressions, it displays it back at you:

2*3

That's because it's not a plain old number, so it assumes it's text. If you want to tell the spreadsheet that you're typing an expression to be evaluated, precede it with an equal sign =. So if you type this in the cell:

=2*3

Then when you hit enter or otherwise leave the cell, it will evaluate the expression and display the result:

6

If you click on the cell again, it will continue to display the value (in this case 6) in the cell itself, but if you look at the top of the window there is a field (variously called the "formula bar" or "expression bar" or just "input line") that displays the expression that is stored for that cell (in this case =2*3). To edit the expression in a cell, you can click on the cell and then click in the expression bar, or just double-click the cell. When you hit enter, the modified expression will be evaluated and the new result displayed.

The expressions you can type into the spreadsheet can get arbitrarily complex, and you can make use of all the symbols and operators we've learned for our in-line notation. Try it!

References to other cells

Expressions get most interesting when they can refer to values outside of themselves, that have been entered into other cells. For this to work, we have to have a way to refer to those other cells. So, a cell is identified by its column (a letter) and row (a number). For instance, cell B3 refers to the cell in column B and row 3, highlighted in this spreadsheet:

A B C D
1
2
3
4

In just the same way that algebraic notation uses simple letters like x or n in an expression to refer to a value that can vary or that is specified elsewhere, spreadsheet expressions can use these letter-number coordinates. For instance, suppose you enter some numbers in cells A1, B1, and C1, and start typing the expression =A1+B1*C1 into cell C2:

A B C D E F
1 4 −610
2 =A1+B1*C1
3
4

As soon as you hit enter, the displayed expression is replaced with its computed value:

A B C D E F
1 4 −610
2 −56
3
4

If you change one of the values, say C1 goes to 11, as soon as you hit enter, the displayed value in C2 is automatically updated:

A B C D E F
1 4 −611
2 −62
3
4

You can verify that the expression stored in C2 has not changed by clicking on the cell and looking up at the expression bar.

Exercises

In the first group of exercises, use a spreadsheet to compute the value of the expression, written in standard algebraic notation:
  1. 25 + 23 + 21
  2. 27 + 26 + 22
  3. 8 −
    5 − 3
    3
  4. 3
    3 + 7
    + 4
  5. 3.0625 × 103
  6. 1.75 × 10−2
In the second group of exercises, use a spreadsheet to perform the computations specified.
  1. Enter numbers into the cells G1, H1, and I1; in another cell type an expression that will compute their sum.
  2. Enter numbers into the cells C3, C4, and C5; in another cell type an expression that will compute their product.
  3. Pick two cells in a row and enter the labels x and y; and then enter numbers in the cells below the labels. In another cell, enter an expression that will compute the value of x2 − 2xy + y2 based on those numbers.
  4. Pick three cells in a row and enter the labels P, r, and t; and then enter numbers in the cells below the labels. In another cell, enter an expression that will compute the value of P(1 + r)t based on those numbers.
  5. Pick three cells in a row and enter the labels a, b, and c; and then enter numbers in the cells below the labels. In another cell, enter an expression that will compute the value of b2 − 4ac based on those numbers.
  6. Pick three cells in a row and enter the labels a, b, and c; and then enter numbers in the cells below the labels. In two other cells, enter expressions that will compute the values of the roots of the corresponding quadratic equation, making use of the quadratic formula: x =
    −b ± √b2 − 4ac
    2a

Credits and licensing

This article is by Robert P. Webber, Scott McElfresh, and Don Blaheta, licensed under a Creative Commons BY-SA 3.0 license.

Version 2016-Jan-29 04:30