Learn

 

 

 

Unique Spreadsheet Features

This lesson is designed to help you learn several new features in Excel. These features are easy to use and helpful. You may want to know why you didn't start out using them in the beginning. The reason for not beginning with these features is due to the fact that you need to understand how to create a formula on your own. To think about what you want to do mathematically and organize your thoughts in a formula so that it works correctly.

Not all formulas can be created by using a function or the AutoSum button. Some formulas have to be created to perform specific mathematical functions you want to use. Formulas that will utilize the Absolute Cell Reference have to be created manually, but once created, they can be copied to other cells by using the fill handle. You will learn how to create and use these features by the end of this lesson.



→ »

Formulas and Functions

 

Let's review for a moment. You first learned about easy formulas that can be entered into spreadsheet cells. It is a simple mathematical operation where you enter an equal sign followed by a cell address, a symbol for the math operation (+, -, *, or /) and then the second cell address. Then you learned about functions. A function is a more advanced mathematical operation but they are already built into the spreadsheet ready for you to use. All functions of course start with = (an equal sign), followed by the function name, followed by arguments, which are cell or range references in parenthesis that tell the spreadsheet which values to use to calculate the function results. This range of cells is a logical group of cells upon which the function will be applied. But always check that logical group of cells appearing in parenthesis to make sure that it is the correct range of cells upon which you want to make the calculation.



« »

Relative and Absolute Cell References

When you enter a cell reference (also called cell address, such as A2, which is Column A, Row 2) in a formula, the spreadsheet program automatically makes it a relative reference. A relative reference adjusts when you copy down a formula into a new location using the fill handle. For example, if you enter =A1+B1 in Cell C1, then copy the formula to Cell C2 using the fill handle, the copied formula will automatically read =A2+B2. It will change cell references from Row 1 to Row 2 because you copied the formula from Row 1 to Row 2. In other words, it changes the Column letter and Row number relative to its new location.

When you use cell references in copied formulas, you will sometimes want to refer to a specific cell, regardless of the formula's location—you don't want the cell address to change. Two cell addresses are in formulas—you may want one of them to change relative to the new location in which it is copied and you may want the second cell reference to refer to a cell that does not change. In these cases, you use an absolute reference, which uses dollar signs before the column letter and row number. For example, the formula =A2*$A$1 will change the A2 relative to its new location but will always refer back to the value in Cell A1, no matter where the formula is copied to or located.

When you want a cell address to change when it is copied, leave as is. If you do not want a cell address to change when it is copied, add a $ before the column letter and a $ before the row number. Watch this instructional video to see where to find the built-in functions and to see how relative and absolute cell referencing differ.



« ←