An Easy Way to Explain the Difference Between a Relative & an Absolute Formula in Excel
Using formulas in Excel can be less than intuitive for some people. Explaining the difference between relative and absolute references becomes clear when your trainee can see the effects of each type of reference in a simple spreadsheet. Create a spreadsheet exercise that demonstrates relative and absolute references before granting your worker edit access to your small business spreadsheets.
Create an Exercise Spreadsheet
Open a blank spreadsheet in Excel. Label cell A1 "Daily Sales." Label cell B1 "Last 2 Days." Label cell C1 "Running Total," and then set column width to 15 for these three columns. Change the color of cells B2 and C2 by selecting them and using the Fill Color icon. If you wish, you can format and protect these cells your worksheet. Save the spreadsheet with a name and in a place for easy retrieval.
Create Instructions for Your Trainee
Provide your trainee with the following instructions to complete the spreadsheet with formulas and data. In cell B3, enter the formula "=SUM(A2:A3)". In cell C3, enter the formula "=SUM($A$2:A3)". Enter the value "100" in each cell from A2 to A11, representing 10 days of sales. Highlight cells B3 and C3, right-click and select "Copy." Select cells B4 to B11, right-click and select the leftmost icon under "Paste Options." The spreadsheet will fill with calculated values.
Explain the Relative Formula
Direct the trainee to column B. All values from B3 to B11 are 200. Have him click on each cell in turn to see that the cell numbers referenced in the formula changed with each pasting. These changes point to two cells in column A in relation, or relative to, where the formula is pasted. This is a relative formula.
Explain the Absolute Formula
Direct the trainee to column C. Values from C3 to C11 climb each entry by 100, from 200 to 1000. Have him click on each cell in turn to see that only the second cell number changed with each pasting. The first number, $A$2, remained constant, or absolute, through all cells. Explain that Excel uses the $ character in formulas to set and denote an absolute column or cell reference.
- Stockbyte/Stockbyte/Getty Images