Hendon Publishing - Article Archive Details

Spreadsheet Basics

Written by Stephenie Slahor

It used to be that folks kept financial information in ledgers. Columns and rows kept track of descriptions, numbers and time. Today, that same information (now called “data”!) goes into computer spreadsheets that save time and ease the task of correcting errors, or making additions or changes without re-doing all the calculations formerly logged in a ledger. The computer does the calculations based on the formulae you want it to use or the “what if” calculations you might want to make. Microsoft Excel is the program most often used for spreadsheets.

Spreadsheet columns (the vertical spaces) are labeled by letters; rows (the horizontal spaces) are labeled by numbers. A “cell” is where a column intersects with a row. A cell is designated by, first, its column letter, and second, its row number (e.g. “C3”). A cell can hold a label, which is text with no numbers; constants, which are numbers such as dollars, percentages or units of time; or formulae, which are the equations that calculate. (Note that all formulae begin with “=” (the equal sign).)

A list of Excel functions is found on the computer under the Menu INSERT; then drop down to FUNCTION. When entering formulae, refer to as much data as possible instead of typing data into equations. This saves you from having to type in data again and again, and from having to keep changing the equations.

Use the mouse or keyboard to select a starting and a stopping cell. That will select all the cells within the block you designated. If you want to work with cells that are not adjoining (non-contiguous cells), use a comma to separate the cells, or hold the control key (command key on a MAC) to select or separate the data. So, if you were trying to use data from, say, cells B9 and E4, you would click on B9, type in a comma or hold the control/command key, and click on E4. Other methods include clicking on one cell and, with the button down, dragging over to the other cell you want to work with.

Functions make your data work for you. The Sum function (“=SUM”) calculates totals. The Average function (“=Average”) calculates averages. The Max function (“=Max”) notes the maximum value in the cells. The Min function (“=Min”) returns the minimum value in your selected cells. The Count function (“=Count”) counts each cell with number data in your selected cells, so “=CountA” counts each cell with number or text data. The IF function (“=IF”) can return either numbers or text and checks the truth or logic of a value. The PMT function (“=PMT”) is for calculations usually involving loans, annuities, monthly payments, etc.

More information about the functions available can be found at the Function Wizard in Excel, located in the Toolbar with an italicized-looking “fx,” or by going to the Menu and selecting INSERT and going down to FUNCTION.

The “copy and paste” command or the “Fill Down” will help if you have to repeat the same formula for different cells. “Absolute Positioning” will lock the cell location to a certain, fixed position. “Fill Right” will select a cell and the cells to its right. You can change the format of your font and your numbers if you need to set consistency such as dates, time, percentages, fractions, currency, etc. Just use the Menu FORMAT and click on the appropriate selection for your need. Format will also adjust column width. To add a column or row into an existing spreadsheet, click the column letter and choose Columns (or Row) from the INSERT menu.

Practice your skills. Read tutorials offered in Excel, on Web sites, or at your library or bookstore. And remember that even though, as your expertise improves, spreadsheets will become easier for you, you might need to share information with those who are not so knowledgeable. For them, a bar or pie graph may be easier to understand, and your Excel Chart Wizard will take you through the steps to create a chart from your data.

Stephenie Slahor, Ph.D., J.D., writes in the fields of law enforcement and security. She can be reached at drss12@msn.com.