The $64,000 Question – What are the dollars for in Excel?

January 29th, 2019

Training

Lynsey


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.

 

The $64,000 Question – What are the dollars for in Excel? RELATIVE REFERENCING IN EXCEL; Excel Training in Aberdeen, Solab IT Services

 

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.

 

The $64,000 Question – What are the dollars for in Excel? Absolute REFERENCING IN EXCEL; Excel Training in Aberdeen, Solab IT Services

 

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.

 

The $64,000 Question – What are the dollars for in Excel? Handy Keybord Shortcut, Excel Training in Aberdeen, Solab IT Services

 

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

The 64 Thousand Dollar Question - What are Dollars for in Excel? Microsoft Excel Training in Aberdeen, Solab IT Services

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.

The 64 Thousand Dollar Question - What are Dollars for in Excel? 02 Microsoft Excel Training in Aberdeen, Solab IT Services

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.

The 64 Thousand Dollar Question - What are Dollars for in Excel? 03

 

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/).

Excel 2016 Advanced

Excel 2016 Intermediate

Excel 2016 Foundation

Excel 2013 Advanced

Excel 2013 Intermediate

Excel 2013 Foundation

Excel 2010 Advanced

Excel 2010 Intermediate

Excel 2010 Foundation

Excel 2007 Advanced

Excel 2007 Intermediate

Excel 2007 Foundation

Training Calendar View Dates Solab

Sign up to our newsletter