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, simply, you need to understand how to create a formula on your own.
- It is also important for you to think about what you do mathematically and organize your thoughts into a formula that will work 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 may utilize relative cell references (information that changes when copied) and/or absolute cell references(information that stays the same when copied).
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.
Functions and Formulas Review
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 (just as all easy formulas):
- start with = (an equal sign),
- followed by the function name, and then
- 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! Make sure that it is the correct range of cells upon which you want to make the calculation.
Relative and Absolute Cell References
Relative
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.
Absolute
When you use cell references in copied formulas, you will sometimes want to refer to a specific cell, regardless of the formula's location and you don't want the cell address to change. There will also be times when two cell addresses are in a formula and you may want one of them to change relative to the new location in which it is copied while 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 video to see where to find the built-in functions and to see how relative and absolute cell referencing differ. While this video may use an earlier version of Excel to demonstrate, the features you need to know should be found in the same place and should function in the same way.