Microsoft Excel Whiz 2002 2003

2017-07-07 02:10:07

Lesson 2: Use References (Absolute and Relative) and Autosum

A reference identifies a cell or range on a worksheet and tells MS Excel where to look for the values or data you want to use in a formula. There are three kinds of references: relative, absolute, and mixed.

Relative References

A relative reference stores the exact location of the cells to which the formula refers while taking careful note of where those cells are located in relation to the cell that contains the formula. Using a relative reference allows Excel to automatically adjust the cell references to reflect their position to the new location as you copy or move a formula.

Absolute Reference

An absolute reference does not adjust the cell reference when you move or copy a formula. To specify an absolute reference, use dollar sign ($) within the cell address. For example, when you type $A$1 as part of a formula, Excel will look for the value on cell A1 even if you move or copy the formula to another location.

Mixed Reference

These are combinations of relative and absolute reference such as relative column and absolute row or absolute column and relative row.

The following Exercises will help you understand Absolute and Relative References

Exercise 1

Scenario: Suppose you are a student assistant in the Registrar's Office and you are advised to get the total number of high school enrollees from 1996-2000. Create a worksheet having the following data: Year Level, School Year, and Total Number of Enrollees. Refer to the sample given below:

Compute the total number of enrollees from First Year to Fourth Year for the school year 1996. Thus, your formula will be =B5+B6+B7+B8.

Copy the formula in cell B10 to cell C10 to get the total number of enrollees for the SY 1997.

Note
If you copy the formula in cell B10 to C10, Excel assumes you want to total the numbers in column C, so it adjusts the formula accordingly, to =C5+C6+C7+C8.

Move the formula to the right using the fill handle to get the total number of enrollees in all school year. Thus, a reference changes from C10 to F10.

Save your work as Enrollees (relative).xls in the Excel-Activities folder.

Exercise 2

Scenario: Suppose you love to collect CD's and you save money for them every month. You want to know how much money you have spent or saved from your monthly allowance. Create a worksheet that includes the following data: Price of CD, Months, Monthly Allowance, Expenses, and Savings. Refer to the example given next page:

Compute your expenses for the month of January. Your formula should be =SUM($C$4,C7,D7)

Copy the formula in E7 to compute the expenses for the month of February.

Using fill handle, copy the expenses for the remaining months.

Compute your Savings for the month of January by subtracting Expenses to Monthly Allowance.

Using the fill handle, compute the savings for the rest of the months.

Save your work as Financial Statement(absolute).xls in the Excel-Activities folder

Note

When you type $C$4 as part of the formula, Excel looks for the values in cell C4 even if you move the formula or copy it to another location.

Using AutoSum

AutoSum allows you to quickly add a range of cells together. You can use AutoSum in a single cell, a row or column of cells, and in a range.

Use AutoSum in One Cell

Select the cell where the formula will be placed. Usually, this will be below a column of numbers or to the right of the column numbers.

Do one of the following:
- Click AutoSum.
- Press Alt+= (equal sign)
Excel might suggest a range of numbers by displaying a marquee. It also automatically adds an equal sign (=), SUM, and an open and close parenthesis around the range.

If the suggested range is not correct or does not display, drag the mouse pointer to select the range or type in the cell reference.

If the selected cells are correct, Press Enter or click .

Using AutoSum in a selected rows or columns

To use AutoSum in selected rows or columns, follow these steps:

Select the rows below or the column to the right of data.

Click AutoSum.

Using AutoSum in a selected range

You can get both the sum of rows and columns if your range is in a rectangular form.

Select the data, a blank row below, and a blank column to the right.

Click AutoSum.

Extended AutoSum Functionality

The functionality of AutoSum has expanded to include a drop-down list box of the most common functions.

For example, you can click Average from the list to calculate the average, or connect to the Function Wizard for more options.

To use the expanded functionality of AutoSum, follow these steps:

Select the cell/s where you want the result of the formula to be displayed.

Click the drop-down list box beside the AutoSum button.

Choose the desired function from the available options.

Check if the selected cells are correct for the computation.

Press Enter.

Whiz Words

Absolute Reference	Relative Reference
Fill Handle	Reference
AutoSum	Alt+=

Lesson Summary

Relative Referencing means that when a formula is copied, the cell address in the formula changes to its new location.

Absolute Referencing means that when a formula is copied, the cell reference remains constant.

AutoSum allows you to quickly total a range of cells together. You can use AutoSum in a single cell, a row or column of cells, and in a range.

Study Help

What do you mean by reference?

What is the difference between absolute and relative reference?

What is AutoSum?

Activity 1

Create a worksheet that would show your savings for a week.

Enter your daily allowance at cell A2.

Use the illustration below as your guide.

Using the AutoSum, get the total amount of your expenses per day.

On cell B9, subtract the total amount of expenses from your allowance.

Make the first cell address of the formula on cell B9 absolute so that you can copy the formula using Auto fill.

Compute for the Total Amount Saved to get your savings per week.

Save your work Allowance.xls in the Excel-Activities folder.