- Spreadsheet software allows you to keep records and make calculations
- 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.
- 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.
Let's say you have a list of numbers you want to add. To make a calculation to add them, you could write a formula which looks like this:
However, the numbers in the formula (B8) are separate from the numbers in the cells (B2:B6). 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 2 to 20, the formula will "see" that B3 has changed, and will automatically add that change to the calculation.
Now let's learn a very useful tool: the FILL feature.
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.
The FILL feature is most useful for repeating formulas.
Let's say you have a column of numbers and you want to multiply each number by 2. Creat a new formula in D2 to do this:
But now, you want to do this for each of the other cells. Usually, you would type a new formula into each one. However, if you FILL the first formula from the top to the bottom:
It will copy the FORMULA into the new cells!
That will give you the two times the number in the other column!
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.
Copy and pasting can get complicated since you may be copying the equations in a cell rather than the solution to the equations. If you want to copy and paste the results from one cell to another it is easiest to simply refer to the cell that you want to copy:
Watch this video for information on graphing data sets in excel:
Now you know most of the basics for Excel. There are many, many more FUNCTIONS in Excel that you can learn; we just learned a few.