Articles and Information
by Members for Members

Spreadsheet Basics

by Lee Alexander

What is a Spreadsheet? In present terms, it is a software application that mimics that oversized accountant's pad you had to buy for Accounting 101, from which we get the appellation – spreadsheet. Microsoft provides two versions: Excel, which is part of Microsoft Office, and one in Works. The major difference between the two is Excel is a Workbook – each file with its .xls extension can hold multiple Worksheets- while the Works file, extension .wks, can hold only one Worksheet.

When you open a blank worksheet, you are presented with a grid of rectangular boxes – these are the CELLS. The first ROW of CELLS is identified by letters from A to IV for a total of 255 COLUMNS. The very first column, without a letter, is filled with the numbers 1 through 65,536 – the ROW numbers. Think of each ROW as a record and each COLUMN as a category or field. What can we put in the cells? Anything! It can be a number, a single character, a picture, a sound, or, most significantly, a formula.

But the cells are so small! Not really, in Excel you can FORMAT the ROW height from 0 to 409 pixels and the COLUMN width from 0 to 255 characters. And that is just their physical appearance on the screen. If a photo is inserted in a cell, it can be a file that is MB's in size. Additionally, cells can be merged; therefore, cell “size” is not likely to be a limitation on your data. This brings up a very important point – new users of spreadsheet applications try to adjust cell size in advance, prior to entering their data. Big mistake! You waste a lot of effort when the program can AUTOMATICALLY adjust, or you can MANUALLY, set cell parameters after you make your entries. Rule One – get the data in and worry about their appearance later.

Think of the cells as places to store data elements. Now if you have a PLACE, you must know how to get to it. Each cell has an address designated by its placement within the worksheet. The first part of that address is the column the cell is in and the second part is the row of the cell, such as B6 – JUST LIKE BINGO! There is one little quirk to this addressing scheme, it has a split personality. B6 is a RELATIVE address, but $B$6 is an ABSOLUTE address. The latter does not change as you move around in the worksheet. This is a very powerful feature of the spreadsheet – it allows us to replicate a formula by simply filling in cells rather than having to retype the formula for each cell. . Typically, we use Row 1 as the HEADER row, identifying the contents of the cells in each column. Unless you are a hermit, we are often ruled by clock and calendar. DATE is a frequent and early entry in the header row. STREET, CITY, STATE, and ZIP are bound to be found in an Address worksheet. In an Inventory worksheet you would probably use ITEM, BRAND, SERIAL NUMBER, PRICE, PURCHASE DATE, LOCATION, etc.

What about just using a Table in Word for the types of files mentioned? It is true that latest versions of Word and other word processors have amazing abilities built in. However, a spreadsheet has a few tricks that add a great deal of versatility other than formula manipulation and number crunching. Primary is the ability to sort and select records (those are the numbered rows) according to very specific criteria. For example, in the case of an address spreadsheet, you could elect to show only the entries from a specific STATE or ZIP. By incorporating a CATEGORY column with appropriate entries such as MED, INS, FAM, MAG, etc. you could pare down the list for a specific purpose. Suppose there is news you wish to announce to all the family members in your address spreadsheet. Click on the menu item Data, then Filter and select AutoFilter. Select FAM from the drop down list in CATEGORY and only those records so identified will appear on the spreadsheet. If you then select that abbreviated list, copy it, and paste it to a blank spreadsheet it can serve as the data source for a word processor's Mail Merge.

Another example could be a spreadsheet to track your medical events and expenses. The Column Headings could be:

DATE, PROVIDR, REASON, D.O.S., PAID, CHK, BILLED, M-C APR, M-C PAID, OTHER INS. PD. , CASH RCVD, COMMENT, miles.

Of particular note is D.O.S., which stands for Date Of Service. As a recipient of Medicare and secondary insurances, I find it crucial to monitor carefully the benefits and denials of the plans. Using DATE , as the first column entry point presents the data in a chronological form. Offices of some Providers can be a little careless when it comes to double billing, even for services performed a year or more past. A statement from an insurer that the claim has been previously dealt with allows me to scan, quickly and easily, all records relating to the instance on the basis of the D.O.S. Using AutoFilter, select the D.O.S. from the drop down list to give an abbreviated spreadsheet so you do not have to jump around locating each entry on the parent spreadsheet.

The M-C PAID and M-C APR columns refer to Medicare. The last column, M – miles , records the travel distance for medical services. When tax time rolls around, it is a simple matter to have the spreadsheet sum up this column within the appropriate dates. The second spreadsheet in this Workbook, Dental, is laid out slightly different. Even if you do not have Dental Insurance, it would be a logical way to keep ongoing records for family members. Again, at Tax Time, all the numbers for medical expenses are before you in a tight package.

Investex.xls is a more typical use of a spreadsheet program. It is a fictitious portfolio generated with random numbers and in no way conforms to the present markets. Looking at a page full of sterile numbers is not the best way to get a feel of how things are going – the movement and trend of data points; far easier to see a chart of the ups and downs. The spreadsheet Chart1 is a simple bar chart of selected data points from the spreadsheet Data. Chart2 is a line chart that shows a magnified view of certain data points in another format. There are a multitude of features in Microsoft's Excel for composing many varieties of graphic representations of data –beyond the scope of this article.

I have tried to give you a quick and brief peek at the powers of a program you probably did not intentionally seek. More likely, it came in a package with your computer, purchased or inherited. Think twice before you dump it for a bit more space on your hard drive. It can be truly useful for more than plain and fancy accounting.