Spreadsheet Basics

If the word processor is an electronic replacement for the typewriter, spreadsheet programs are electronic ledger paper, allowing people to neatly arrange data and make calculations of all kinds. It was a spreadsheet called VisiCalc which helped small businesses first see the value of personal computers, but many users no longer appreciate the value of this simple gem. A spreadsheet allows you to arrange data into tidy rows and columns, then do amazingly consistent (or bewildering) things with that data.
This article is third in a series on program types, following our last article on word processing basics.

The Basic Interface

The most daunting thing about a spreadsheet to beginners is the relatively complex and intimidating interface. Unlike the inviting blankness of a word processor, a spreadsheet assaults us with a tight grid of lines. Seemingly designed to make the user feel rigidly constrained and sadly inadequate, these boxes are even called cells, as in a monastery or prison. Divided into horizontal rows (like in a garden) and vertical columns (like on a building) the spreadsheet’s basic element is this intersection, the cell. The contents of a cell are frequently hidden to the uninitiated, squirreled away behind the curtain in an unassuming “formula bar” or “entry bar” toward the program’s upper left, between the menu bar and the first few cells, which begin where column A intersects row 1 at cell A1. To edit a given cell one normally clicks on it (to select it) and then does actual entry in the spreadsheet’s “entry bar.”

Three Things Can Go Into a Cell

Some spreadsheets allow one to click directly into a cell and begin typing, while others require that one clicks or otherwise selects a cell to see or enter its contents into the formula or entry bar. Usually to the left of this bar are two or three crypic symbols: a checkmark (which says “yes”), a cross-out symbol (“x” for “cancel”) and perhaps a scary-looking function symbol. The second great mystery of spreadsheets (behind the division into cells) is the three things that can go into a cell

  • Numbers
  • Text or
  • A formula

Cells can be formatted to display numbers in various ways (as dates, times, currency or percentages) and with various colors and backgrounds, but understanding the basic truth of three things a cell contains is the first step in understanding the basic nature of the spreadsheet.

Numbers

Numbers in a spreadsheet can take the form of integers, percentages, decimals, currency, date or time, but the one thing they all share in common is that they represent a precise and discrete kind of data which can be expressed in digits and used in calculations. If you will be formatting a series of numbers as currency, percentages or whatever, the best way to do so is to select the entire cell, row or columnn you plan to format and then apply the formatting to the entire cell or group of cells, much as you would apply a word processing paragraph style. This will keep your data as “pure” and flexible as possible, without mucking it up with symbols and punctuation. Some programs will allow you to type your numbers in unmolested and some will “help” you by making assumptions and applying formats (dates especially!) without checking. Understanding how to format numbers as an attribute of a cell/row/column and how numbers differ from text is the main thing to understand. If you wish to format an entire cell or column, click on a column’s head letter (or row’s leftmost label number) to first select it in its entirety. To format an entire spreadsheet, click the “magic box” just up and left of cell A1.

Text

Text usually takes the form of typed labels or words, and putting text next to rows or columns to clarify what they represent is probably the best habits anyone who creates spreadsheets can acquire. A simple text label clarifies many things, and small bits of text placed in the corners of variously-sized and merged cells is also a handy way to create quick and passable forms to be printed and filled in later by hand. Sometimes if a spreadsheet won’t display your text the way you want it to, you can preced it with a quotation mark or format the cell/row/column to display as text rather than a number or calculation.

Formulas

A formula is a calculation which takes the content of at least one other cell and manipulates or changes it in some way. Sometimes this is something simple (such as a text concatenation which combines the data from a “firstname” field with a space and the data from a “lastname” field to display a full name) and sometimes it is a very sophisticated function, drawn from the obscure bowels of finance and a secret chamber deep within a program’s help file. Most of the time formulas are basic algebraic operations such as addition (“+”), subtraction (“-“), multiplication (“*”) and division (“/”) nested in various parentheses just as in basic algebra. Almost every spreadsheet invented begins any cell containing a formula with the equals sign (“=”). Among the more common functions are concatenation (for text) and sum or average for numeric data. If you want the result of a formula to display in a format such as currency or percentage, format the cell to do so, just as you would for a number.

Good Hygiene


  • Label your spreadsheet as clearly as possible
  • Always include a header and/or footer
  • Include no more worksheets than are necessary
  • Use comments, if available, to make your work clear
  • Try to make the spreadsheet fit logically on a page or two

Using Spreadsheets for Tables and Forms

In the real world most users use spreadsheets for extremely simple tasks: basic ledgers, tabular data or information-gathering forms. A spreadsheet can be arranged, for example, to visually resemble a printed form such as an IRS 1040-EZ or the HCFA-1500 medical billing form. Spreadsheets are often used to arrange simple tables of data for such things as address, shopping or inventory lists where one column represents a kind of data (what in database terminology would be called a “field” such as first name, last name or zip code) and each row represents a person or other item. Most useful workaday spreadsheets are rarely more complicated than a payroll sheet, a checkbook or an automobile log which automatically calculates miles-per-gallon, but the basic rules of a good spreadsheet still apply: include a descriptive header and footer, label columns and cells as clearly as possible, and consider including an automatic date and page number for any header which will be printed, potentially dropped or need filing. For most internal forms, a spreadsheet with hidden gridlines and an intelligent use of cell borders is far simpler than building the same thing in a word processor or page layout program, and although such forms are not as beautiful, they do encourage rapid deployment and quick revision.

Storing, Sorting and Summarizing

Arranging data in a table is one of the simplest ways to organize and review it, and some use spreadhsheets primarily as graph paper. Once arranged, entire rows of data can be selected and sorted quickly, and often summarized in cells near the top of a table with such simple formulas as “count,” “countA,” “countIF” or “average.”

Graphs and Charts

Another common and valuable use of spreadsheets is to use data in a table to chart or graph data with line charts, pie charts, histiograms and others. With a bewildering array of chart types available, most users would do well to use this feature sparingly and with only simple charts. The bewildering array of chart types and options in most spreadsheet programs can easily lead to numeric nonsense and statistical non-sequitors, especially with such tempting options as “3D” and “drop shadow” to lure naive users into extraneous chart junk.

Chart Junk

“Chart junk” is Edwart Tufte’s felicitous phrase to describe ink or visual objects which provide no data and cause visual confusion, in violation of what he calls the “data to ink ratio.” In his brilliant book The Visual Display of Quantitative Information Tufte takes this aesthetic to places of pristine beauty, but the main message for most people who use spreadsheets is to turn OFF a lot of the distracting “features” such as unnecessary lines and grids. The spreadsheet’s ability to generate fancy-looking graphs and charts is the place where this sort of junk is most egregious.

Almost every spreadsheet has a feature which lets the user select a series of cells and then “chart” that data as a pie chart, histiogram or line graph, usually with disastrous results. Although convenient for simplistic illustrations, the average user should avoid this function unless they understand something of the basic mathematics involved, and even the simplest of charts will be more effective if one takes the time to go in and turn off a lot of the “fritterware” window-dressing options such as extraneous grid lines and three-dimensional shading which add extra ink without conveying data. A basic diagram which shows the general trend and then has numbers nearby is almost always preferable to a thicket of lines and labels, so dense as to be illegible.

This is not to dissuade the user from judicious use of graphs and gridlines and shading where it increases legibility and clarity, but to encourage one to consider how much is enough. Of the data and ink on the page, how much increases insight and provides needed detail and how much clutters with crap or distracts with detritus.

Try It!

Whatever your current level of familiarity and comfort with spreadsheet programs, I encourage you to take a few minutes each week to stretch and learn a little bit more. Spreadsheets can be a very useful tool, and even a little bit of knowledge can go a long way. For more specific instruction, help or tutorials, please consider phoning Mac Rory at (360) 695-6929.

Links…


This entry was posted in Uncategorized. Bookmark the permalink.