As part of episode 3 “From Excel Zero to Excel Hero” series, Lynsey will answer the $64,000 Question – What are the dollars for in Excel?
Dollars ($) can be added to cell references in formulas and functions. When the reference must not change on copying the formula or function to another cell, dollars must be added.
Copying
It’s important to remember that dollars ($) only need to be considered if the formula or function is to be copied to other cells. Typically copying it down the column or across the row.
Relative and Absolute Referencing in Excel
This is a very important concept in Excel. It is referred to as Relative and Absolute Referencing. By default, cell references in formulas or functions are relative. This means the references change when the calculation is copied to other cells. If the references must not change then dollars must be added.
Relative Referencing in Excel
How does relative referencing work? In this example there is a formula =B2+C2 in cell B6.
First, copy the formula across one column to the cell on the right. All the references in the formula also move across one column to the right. =B2+C2 becomes =C2+D2.
Next, copy the formula down one row to the cell below. All the references in the formula also move down one row. =B2+C2 becomes =B3+C3.
Then, copy the formula across one column to the cell on the left. All the references in the formula also move across one column to the left. =B2+C2 becomes =A2+B2.
Lastly, copy the formula up one row to the cell above. All the references in the formula also move up one row. =B2+C2 becomes =B1+C1.
Absolute Referencing
This is often referred to as Fixed referencing which, to many people, makes more sense than Absolute. In this example the formula =A1*D1 is inserted in cell B1. Copy the formula down the column by double clicking on the little black cross in the bottom right hand corner of the cell. The formula changes to =A2*D2, =A3*D3 etc. This is not the correct results.
Handy Keyboard Shortcut in Excel
To get the correct result, add dollar signs to the reference D1 in the formula. Use the handy keyboard shortcut which is F4 (find it along the top row of the keyboard). In this example, after clicking on cell D1 to add it to the formula, press F4 and the dollars are added to the reference. Once the formula has been copied down the column, the results are correct.
Mixed References in Excel
Notice in the example above that two dollar signs are added to the reference D1, one before the D and one before the 1 resulting in the reference $D$1. This means that the column and row part of the reference are both fixed. Sometimes only one part of the reference should be fixed. In the following example the numbers in row 1 should be multiplied by the numbers in column A. Let’s see what the results look like with no dollars added. Clearly these are incorrect answers. 5 multiplied by 5 should be 25 not 6.37E+42. Look at the formula bar, in cell F6 the formula is =E6*F5 which is =2.52E+21*2.52E+21
If dollars are added to both parts of the references in the first calculation =$A$2*$B$1 this results in the answer being the same in all the cells as both references are fixed.
In this scenario, the formula needs to refer to the references in column A and row 1 so the column part of the A2 reference needs to be fixed e.g. $A2 and the row part of B1 needs to be fixed e.g. B$1. Now the formula in cell F6 is $A6*F$1. The column part of the first reference hasn’t changed but the row has. The column part of the second reference has changed but the row hasn’t.
Dollars in Action
Looking back at Episode 1 of our “From Excel Zero to Excel Hero” series, at the section SUMIF function with a Formula most of the references include dollars. Importantly, the function and formula were copied from cell F2, so dollars should be considered. The SUMIF function needed to refer to the same range of cells for each person so dollars are required for the ranges. However, the criteria is different for each person so no dollars are required on E2. Dollars are required on reference I1 as each person earns the same commission.
However, looking back at Episode 2 Excel Formulas and BODMAS none of the calculations were copied to other cells so dollars were not a consideration.
From Excel Zero to Excel Hero – EXCEL courses in Aberdeen
Solab have been offering businesses IT Application Training since 1994. We consistently receive positive feedback and excellent evaluation from clients and individual delegates. We’re flexible, too – not only in the diversity of our course programme, but in shaping a training solution that precisely matches you and your business requirements. We can host your training at our training school in Aberdeen city centre, at your premises or in the cloud (for course delivery methods and options, please visit: https://www.solab.co.uk/training/delivery-methods/).
