• Spreadsheet software allows you to keep records and make calculations
• Automatic recalculation is so convenient that Spreadsheet software became a "killer app" for computers in the late 1970's
• Spreadsheets have columns and rows, made up of cells
• Each cell has an address, made up of the column letter and row number
• Many cells together are called a range; the range address is made up of the top/left cell's address and the bottom/right celll's address, separated by a colon (for example, B2:D5)
• You can put many things in cells, including text, numbers, times, dates, amounts of money, etc.
• To do a calculation, you must begin with an = sign; this calculation is called a formula.
• Formulas remain on a "lower layer," while the information and results are on the "top" visible layer
• Complex calculations can be done easily with functions
• Functions contain arguments (information about how to do the calculation); arguments are inside paragraphs; if there is more than one argument, they are separated by commas
• If a cell or range is seen inside a formula, it is called a reference--for example, a cell reference, or a range reference

Now, let's look at how we can use formulas with functions.

However, the numbers in the formula (B7) are separate from the numbers in the cells (B2:B5). They are not connected. If you change the numbers in the cells, the formula will not change. If you only change the numbers in the formula, then the numbers in the cells will not match the result of the formula. In order to make a change, you would have to change both the numbers in the cells and the numbers in the formula. This is not a good way.

Instead, we can use references. Instead of writing the numbers in the formula, we will instead tell the formula to get the numbers from the cells. That way, when you change the numbers in the cells, the formula will change too:

For example, in the case above, if you change the number in B3 from 34 to 35, the formula will "see" that B3 has changed, and will automatically add that change to the calculation.

However, the formula above is not convenient: in this case, we added each cell reference (B2, B3, B4, B5.). Let's say that you want to add 100 numbers; the formula would be very long if you included each cell's reference like you see above. Instead, you should use a range reference:

So you can see that using cell and range references can connect the cells to the formula.

Let's use the example above. You have the four numbers in B2:B5. Now you want to type the formula.

First, type =SUM and begin with the left parenthesis:

Now you want to add the reference. Take the mouse, and put it over B2. Click and drag down to B5. The range B2:B5 should be highlighted, and the range reference should appear in the formula:

In Excel, it is not necessary to type the right parenthesis at the end of a formula; just hit the "Enter" key, and Excel will add the parenthesis for you.
So, instead of typing the references, we just click or click-and-drag. It's usually easier.

AVERAGE will get you an average of all the numbers. It is like adding x number of cells and dividing by x. AVERAGE will ignore empty cells in the range and not include them in the calculation. Example:

Notice that the following two formulas have the same answer (see the formula in the formula bar):

Notice that in the first picture, the range (shaded pink) is filled with numbers. But in the second picture, there is a blank cell. However, the answer to both formulas is the same. The AVERAGE does not count how many cells, it counts how many numbers.

COUNTIF

Here's another function:

COUNTIF will find how many times a certain number or text string appears in a range. Let's say you have 25 cells filled with numbers, and you want to find out how many times a certain number appears. COUNTIF will find it.

COUNTIF needs two arguments: the first argument is where to search, and the second argument is what to search for. Remember, each argument is separated by a comma. So you would have the range reference B2:F6 first, then a comma, then the thing to search for:

Here is what it might look like in Excel:

Notice that this formula has quotes around the number. For a number, that is not necessary (just like in HTML). It works with or without quotes.

You can also use COUNTIF to look for text strings, or words:

In this case, we are looking for text; quote marks are necessary!

Other Functions

Excel has over 200 other functions. Categories include Financial, Date & Time, Math, Statistical, Lookup & Reference, Database, Text, Logical, and Information. For example, RAND will give a random number; CONCATENATE will add together any number of text strings; ROUND will change a decimal into a whole number; and so on. To see a whole list with explanations for each one, go to the INSERT menu and select the "Function..." command.

When you select a cell, notice the outline:

Notice the little box at the lower right corner. This is called the fill handle. You can use it to fill other cells.

When your cursor is over normal cells, it is a fat, white plus-sign. But when you put the cursor over the fill handle, it becomes a thin, black plus-sign:

When you have the thin, black plus-sign, you can click and drag. This will fill the new cells:

So filling new cells will repeat the original cell. This works with numbers, text, or anything in a cell. Try typing any words or numbers and filling them.

Filling Patterns

You can also create special fill patterns. This time, type "1" in one cell, and then below it, type "2", like this:

Select both cells. You will see the fill handle at the bottom right of the lower cell. Click and drag the fill handle, and see what it makes:

You can see that it did not just copy the two cells! Instead, it noticed a pattern: 1, 2. It then decided to continue the pattern: 1, 2, 3, 4, 5, 6.

Try this with other numbers: "2, 4" for example. It will fill into "2, 4, 6, 8, 10".

Be careful, though: the FILL will only take the difference between the first two numbers. For example, if you type "1, 10" then Excel will NOT fill it to "1, 10, 100, 1000". Instead, it will fill it to "1, 10, 19, 28". Why? Because the difference between 1 and 10 is 9. So it will keep adding 9 to each new number.

Now try typing days and months--for example, "Monday" or "June". If you are on an English-language MS Word, it will fill the other days or months. If you are on a Japanese-language MS Word, try "月曜日" or "六月" and it should work.

You can make custom lists by going into the TOOLS menu and selecting OPTIONS. Select the CUSTOM LISTS tab. You will see the days and months lists here. You can use this to make your own special lists, if you like.

Let's say you have three columns of numbers:

Now make a formula to add the numbers in the first column:

That gives the answer:

But now, you want to add the numbers of the other two columns. Usually, you would type a new formula into each one. However, if you FILL the first formula from the left to the right:

It will copy the FORMULA into the new cells!

That will give you the sum of the numbers in the other two columns!

This is a quick and easy way to repeat formulas when you have many identical calculations. The Formula Fill can be done left-and-right, or up-and-down.

Next, we will learn how to format cells, and to create charts from information you put into Excel.