|
Spreadsheet programs are powerful number crunchers. They're also an excellent place to
store, organize and manage a limited amount of data.
For even more power and flexibility, you can link
the spreadsheet data to a word processor,
presentation, or database application program.
Unless you happen to be an accountant, you may find the idea of a spreadsheet a bit daunting. When
a word processor presents you with a blank page,
it's fairly intuitive that you need to start typing for
anything to happen. But what do you with all those
little boxes in a spreadsheet? Well, it's no different:
you type into them too.
The little boxes are called cells. A cell is the
intersection of a column and a row. Columns are
named with letters, and rows are named with numbers, so
a cell gets it's name from its column and row
location. For example, the cell highlighted in Figure 1 is at
the intersection of the third column and second row, so
it is named C2.
A membership list is practical example of how
you can structure, format, and manage data in a
spreadsheet. A membership list can track names,
addresses, and membership renewals. Based on the data,
you can determine the number of members, total membership paid to date, and the number of years a
person has been a member. You also can graphically show membership development trends and use
the data to create mailings and address lists by merging
it into a document in your word processor. But that's
a lot for just one article, so I'll start by showing
you how to enter your data in the list and format it.
Organize your data
When you start to work with data, think about
how you can break it down into smaller pieces. If
you separate pieces of data (such as City and State,
for example), you can search and recombine the
information more easily and flexibly later. Figure 2
shows a typical break down of address information.
The first row contains a name for the category of
information it contains. In database-speak, these
categories are called field names. To make it easy to share
the data in other applications, the field names should
not contain spaces or other punctuation. An
underline can substitute for a space as you can see in the figure.
Type each member's information in its own row. The rows are called the data
records. If you have no information for a field, leave it blank. Move from
cell to cell by clicking on them with the mouse or by
using the arrow keys.
If you make a mistake, you can just go back to
the cell and fix it. If you press Delete, the contents of
the cell are removed so you can start over. To edit
the text within a cell, most programs let you
double-click to enter edit mode. Some older programs may
require you to press F2 or click in the Formula
bar and do the editing there.
Format the list
At the moment, your list probably doesn't
resemble the one in Figure 2: everything looks the same,
which makes the information difficult to grasp at a
glance. Adding formatting structures the information, so
it's easier to read. Select the cells you want to format
by clicking and dragging over them with the mouse. Then use your program's formatting tools (on
the Format menu or Formatting toolbar in Microsoft
Excel) to specify font, font size, bold, italics,
underline, color, alignment, number formats, borders, or
fills. In Figure 2, I applied bold formatting to the
field names in the top row and to the totals labels at
the bottom. I also added gridlines to the entire list with
a thick border around it. I centered the data in the
M-years columns and applied currency formatting to
the dues information. I added the formatting by
selecting cells and clicking the tool in the Formatting toolbar.
As you enter the data, you may find a column is not wide enough to display everything you want
to see. The data is still there, you just can't see it.
Don't worry about losing this hidden data. All you have
to do is widen the column. Place your mouse
pointer carefully over the divider between two column
headers, as shown in Figure 3. Click and drag the bar
to the right, which increases the size of the column
on the left without affecting the width of the column
on the right. You always have to drag the right side
of the column you want to widen to the right.
Dragging it to the left makes the column narrower—it
doesn't widen the column to the right of the cursor.
Manage the data
Most modern spreadsheet programs have data management tools that let you sort, filter, and
perform searches on the information. You'll find
the program's Help files have lots of information on
the tools and how to use them. Microsoft Excel, for
example, has an entire Data menu with commands devoted to working with data. For example, you
choose Data|Sort to sort your data, so you could view
your membership records by Name, City, or Zip.
Another useful feature is the ability to
freeze rows and columns. With time, as your list grows, you
will no longer be able to view all the records and
field names at one time on the screen. You can freeze
the top row, and one or two columns, so you can see
to whom the record belongs, or to which field the
data belongs. In most programs, all you need to do is
select the cell below and to the right of the row or
column you want to freeze and select the command (usually in the Window menu).
If you made it this far, you have a lot of things
to try out. For one thing, typing that membership
list will take some time! After you get more
comfortable working with spreadsheets, you can learn how to
perform simple calculations and use a few common functions to generate membership statistics.
Figure 1. A spreadsheet consists of rows and columns.
The intersection of a column and a row is called a cell, and it
derives its name from the names of the column and row.
Figure 2. A membership list in Excel that counts the
number of members and dues paid.
Figure 3. Drag the column divider to the right to change
the column width.
|
