LedgerExtra: Spreadsheets 101
Introduction to Excel
Ted Sherman and Padraic Cassidy
April 1997
A spreadsheet is simply a grid of boxes, or cells, set up in rows and
columns. Every cell has a unique address, corresponding to its row and
column location. For example, the cell in the first column and first row is
A1. The cell in the third column and four row would be C4. The computer can
find its way around the spreadsheet by using those addresses, and perform
functions on the numbers you enter in those cells.
It sounds complicated, but it's not. Here is a typical blank spreadsheet
grid:
Basically, we can use this grid to add, subtract, multiply and divide
numbers. We can do percentages. Columns or rows can be sorted
alphabetically, or numerically. That makes a spreadsheet a powerful tool
that will make your life easier whenever you must deal with large blocks of
information or numbers.
If you have a large group of names, for example, a spreadsheet allows you
to sort them or keep track of them. If you have a budget or financial data,
the program can help you analyze the numbers.
We've used spreadsheets here at The Star-Ledger on budget stories,
analyzing state education numbers, looking at crime statistics, keeping
track of non-profit groups, an examination of speeding tickets, stories on
payroll padding and breaking down aviation safety data.
Some editors also use it as a management tool, including keeping track of
expenses and maintaining sortable lists of names and projects.
Entering data
To keep things simple, we're going to look at a spreadsheet with just a few
columns and rows. With Excel Version 7, you actually have 16,000 rows and
columns to play with.
To enter a number or other information, just click in a cell with your
left mouse. Let's start in cell A3 with some data from the Roseland
municipal budget:
Now go to cell B3 and enter numbers the same way. Don't worry about putting
in commas, percent signs or currency signs. We can format the whole column
after we're finished:
Formatting Data
To format the column, click on the column header, which in this case is the
grey square with a B at the top. Click in the square with your left mouse
button and the entire column will be highlighted. Then go to the top of the
tool bar and click on Format, then click on Cells in the submenu, and click
on Number to format the numbers in the cells. Here, we will click on
Currency within the numbers submenu to put dollar signs and commas in the
appropriate places:
Once we do that, however, you see something has happened to the cells: You
get a #### in some of the cells. That's the program's way of telling you the
column isn't wide enough. To fix it, click and drag on the | between B and C
like we did earlier, and widen the column again.
Now let's add another column of numbers, again by simply clicking in the
cell and working down. We already have the 1997 budget figures. Let's enter
the 1996 numbers as a comparison:
Calculating Changes
Here's where we put the program to work. If you wanted to, you could take a
calculator and add up those columns yourself. You could then subtract the
difference between the two years of figures in each column, and get an idea
of where expenses are heading.
With Excel, the program will do it for you. Start by adding up each
column. Click in cell B9. What we want to do is tell the program what
numbers to add up. In Excel, we tell the computer we are about to enter a
formula by starting with an = sign. Remember, every cell has a unique
address. We want to add up cells B3, B4, and all the way to B8. We could do
that by entering the formula =B3+B4+B5+B6+B7+B8. That will work, but
it means entering a lot of numbers and the whole point of this program is to
save us work. So we can tell the program to simply add up the column by
entering =SUM(B3:B8) Another shortcut, and we love shortcuts, is to
click on the bottom of the column and then click on the Sigma, or
Summation sign in the top toolbar. That will enter the formula for you.
Just make sure there isn't a data at the top of the column, or the date will
be added to your total.
Do the same in both columns, and you get totals. Add the years to the top of
the columns, to identify them. We can calculate the difference between the
two years the same way by entering a formula in Column D. Click on D3. To
subtract last year's Administration expenses from this year's, just enter
the formula: =B3-C3.
Once you enter the formula, you can repeat the formula for other rows
without retyping it by clicking in the cell. You can copy the formula and
the spreadsheet is smart enough to change the cell addresses automatically.
To copy the formula in D3, click on the cell. Then look for the small square
in the lower right corner of the cell border. This is called a Fill
Handle. Click on the square and hold, and the drag it down through D9:
That tells us more than the town did when they gave us the budget, but let's
look at it further. What is the percentage increase or decrease between the
two years? Again, this is pretty simple to figure out. Enter a formula in
the next column the same way. A percentage is a simple ratio. Here, we want
to know the percentage increase, so the ratio would be the difference
between the two years, over what the budget number had been. We've already
calculated the difference in Column D. The budget figure for the previous
year had been the number in Column C. The percentage, then, would be D3/C3.
We format the box as a percentage, the same way we formatted earlier as
currency.
The next step is to pick out the area of greatest change. With a small
spreadsheet like this example, it's pretty easy. But what if there were
hundreds of numbers. Excel will handle that automatically with the sort
function. To use it, highlight all the rows and columns you want sorted.
Then click on Data in the top toolbar, and then Sort in the submenu. The
program will prompt you, asking what column you want sorted, and whether it
should be in ascending or descending order. Her, we will sort by Column D to
find who got the biggest spending increase last year:
Charting
Finally, there is one other tool in Excel's bag of tricks. You can chart
numbers. This could help in analysis because it graphically shows you things
you might miss if all you are doing is looking at numbers. This is also
pretty simple to do. Simply highlight the cells you want charted, and then
click on the upper toolbar on ChartWizard, a little icon that has a chart on
it. The program will then guide you though the making of a chart. That's all
there is to it. If we take this spreadsheet and chart the numbers, here is
what we get:
Some last pointers
- Inserting columns. Click on the header where
you want a column to be inserted. Then go to Insert in the upper
toolbar, Column and presto, a Column appears. Do the same thing to put
in a new row.
- Saving your spreadsheet. Go to File in the
upper toolbar. Then Save As, name your spreadsheet and click OK
- Printing. Go to File, Print Preview, and then
check how the spreadsheet looks. Then click on Print.