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:

As you can see, the cell is not large enough to fit the words. But we can make the columns larger by moving the mouse to the line between columns.

You then click and hold on the line and drag it out to make it larger :

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.