"A spreadsheet is a computer application that simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains alphanumeric text, numeric values or formulas. A formula defines how the content of that cell is to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Spreadsheets are frequently used for financial information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made. A pseudo third dimension to the matrix is sometimes applied as another layer, or layers/sheets, of two-dimensional data."
Another definition: a spreadsheet is a bunch of information organized in tables of columns and rows. This
information, commonly numbers, text, and formulas
can be be utilized quite powerfully in science and engineering.
The Windows operating system's program Excel is a tool we'll explore over the next several
lectures. There are many, many texts available for getting the most out of Excel for science and
engineering purposes. "Excel for scientists and engineers" by William Orvis from
Amazon is a nice book, and there are dozens of others.
The web is full of resources on spreadsheets. From the very basics of
simple video introduction to humor
(spreadsheet jokes). Our
local community colleges offer resources as well (e.g., an
You may ask, "Why Excel?"
Excel is quite powerful, as is. Note also that you can find basically the equivalent functionality in Open Office and it is free and runs on Mac, Windows, and Unix/Linux. And there are spreadsheets in GoogleDocs. So when we say "Excel" in this lecture and subsequent ones, we mean "spreadsheets."
Cells: each one holds a single datum which can generally be a number, text, a variable, or a reference to another cell.
Sheet or worksheet: Array of cells
Automatic recalculation and redrawing: the ability of the spreadsheet to recalculate everything each time there is a change and draw the update is a great advantage.
Cell contents can be formatted in numerous ways
Cell referencing: relatively letter_number of cell (=A1) or absolutely ($letter$_number of cell) (=$A$1); can also point at a different sheet: =SHEET2!A1 (the first cell in sheet 2 of the same spreadsheet)
Formulae: =expression, where the expression can be any combination of logical or mathematical operations on constants, variables, or cell references
Charts: can make many graphics that depend on cell contents
Sometimes hard to audit and error check
Hard to change dimensionality once you start (how big should the array be?)
Limited size (Many versions are limited to < 64,000 cells)
How are spreadsheets used
Answering this question is quite similar to answering: "how are computers used in science?"
This owes to the fact that Excel is capable of doing many of the things that are
accomplished with complicated computer programs on large computers. Thus spreadsheets
are used in a wide variety of applications in science, as we will shortly see.
Entering Numeric Values. Very easy:
click the cell where you want to enter data and type the data and press ENTER or TAB.
Entering String Values.
With text functions, you can manipulate text strings in formulas. For example, you can
change the case or determine the length of a text string. You can also join, or concatenate,
a date to a text string.
Click the cell in which you want to enter the formula, type = (an equal sign). Enter the formula.
Naming cells, formulas, constants.
In Microsoft Excel, you can name a single cell or a range of cells to make formulas easier
to read and remember. If you have row and column labels on a worksheet, you can refer to
them directly in a formula, or you can use them as names that represent the associated cells.
You can also create a name that represents
the same cell or range of cells on more than one worksheet. Use the Insert->Name->Define to define a variable and use Insert->Name->Paste to use it.
Referring to other cells
In Excel you can refer to a cell or a range of cells on a worksheet and tell Excel where
to look for the values or data you want to use in a formula. With references, you can
use data contained in different parts of a worksheet in one formula or use the value
from one cell in several formulas. You can also refer to cells on other sheets in
the same workbook, to other workbooks, and to data in other programs. References
to cells in other workbooks are called external references.
Absolute vs. Relative References
A given cell can contain a formula that references either relative locations to the cell with
the formula (e.g., two cells to the right and one cell up), or an absolute reference to some
specific cell -- irregardless of the position of the cell containing the formula. If cell C3,
for example, contains a formula with reference to E9, then that is a relative reference. If
this formula is copied to cell C4, then it will refer to E10. For absolute references, the same
cell C3 pointing "absolutely" to E9 would have the syntax $E$9. Now if the formula is copied,
it will still point to that specific cell E9.
Inserting a function
Excel has many built in functions (like trigonometry, summation, etc.). Find them under Insert->Function.
For the following, highlight the cells for which you wish to make a plot, then
click on the
The following bullets will make sense as you choose different options.
Creating Bar and Pie Charts BarChartExample.xls. Highlights very basic plotting and also spreadsheet and plot formatting.
Creating X-Y Charts XYChartExample.xls.
Highlights basic computations including the use of names (variables), filling cells, and check the result by fitting a trendline.
and QuadraticFormula.xls. Highlights basic computations, including the use of names (variables) and filling of cells.
Creating contour and surface
charts EllipticParaboloid.xls. This one shows semi-relative referencing and a surface plot.
Changing the axis units
Creating a title
Creating a legend
Creating axis labels
Parsing Text Files and converting text to columns in Excel
What does parsing do? This is
the action of inserting or replacing a character (or characters)in a table of information. This
is useful: you can copy a table of text from your browser window, paste it into Excel, then tell
Excel to replace spaces of commas with tabs, or to use the commas in a file (for example) as
the character for which columns are to be created. (see data, text to columns).
Some examples The U.S.G.S.
has many tables of information about earthquakes that we can play with. The links below
are for past historic California earthquakes, and number of earthquakes greater than magnitude
7 for this century.