Mixed References in Excel

Excel Mixed References

In Microsoft Excel, a cell reference means the use of a cell or a range of cells in a formula to figure out the data or values that the user wants to calculate using that formula. In simple words, cell reference means the cell to which another cell refers.

Cell References can be used to refer to:

• The data from a single cell on the worksheet.
• The data contained in various areas of a worksheet.
• The data in the cells of other worksheets in a single workbook.

Creating Cell References

1. Select the cell in which the formula is to be applied.
2. Type the equal sign (=) in the formula bar.
3. To create a cell reference, pick a cell or a range of cells on the worksheet as shown below.

Drag the border of the selected cell to move it or select the corner of the selection to expand it.

We can also type in a name by pressing F3 and then selecting that name in the ‘Paste Name’ box to create a reference to a defined name.

There are three main types of cell references:

1. Relative Referencing
2. Absolute Referencing
3. Mixed Referencing

Relative Referencing: Relative excel references change when a formula is copied to another cell. It is the most common sort of cell referencing. All cells are relative ones by default. These changes based upon the relative positions of the rows and columns when these are copied across multiple cells. Relative references are beneficial when there is a need to repeat the same calculation across various rows or columns.

For Example, a formula =C3+D3 changes to C4+D4 when it is copied from row 3 to row 4.

Absolute Referencing: Absolute excel references remain the same when a formula is copied to another cell irrespective of where they are copied. They do not change when copied to other cells. It is written with the addition of a dollar symbol ($). The dollar symbol can be written before the row reference, column reference or both.

Mixed Referencing: Mixed references are tricky referencing. A dollar sign is used before the row or the column for mixed reference. Excel Mixed reference locks the column or the row behind which the dollar sign is applied. Mixed reference locks just one of the cell but not both. In other words, part of the reference in mixed referencing is a relative & part is absolute. They can be used for copying the formula across rows of columns and rows eliminating the manual need of editing. They are difficult to set-up comparatively but makes it easier to enter Excel formulas. They reduce error significantly as the same formula gets copied. The dollar sign when placed before the letter then it means that it has locked the Row. Similarly when the dollar sign is placed before the alphabet then it means it has locked the Column.

Striking the F4 key multiple times helps in changing the position of the dollar sign. It is also to be noted that mixed
reference cannot be pasted into a Table. We can only create an absolute or relative reference in a table. We can use the excel shortcut ALT+36 or Shift+4 key for inserting the dollar sign in Excel.

How to Use Mixed Reference in Excel?

Below are the examples of Mixed reference in excel.

Mixed Reference Excel Example #1

The easiest & simplest way of understanding mixed reference is through a multiplication table in Excel.

Step 1: Let’s write down the multiplication table as shown below.

The Rows & Columns contain the same numbers which we are going to multiply.

Step 2: We have inserted the multiplication formula along with the dollar sign.

Step 3: The formula has been inserted and now we have copied the same formula in all the cells. You can easily copy the formula by using dragging the fill handle over the cells we need to copy. We can double click on the cell to check the formula for accuracy.

You can view the formula by clicking on the show formulas command in Formulas ribbon.

Taking a closer look at the formulas we can notice that the Column ‘B’ & Row ‘2’ never change. So it is easily understood where we need to put the dollar sign.

The Result of the Multiplication Table is shown below.

Mixed Reference Excel Example #2

Now let us take a look at a more complicated example. The table below shows the calculation of Derating of
Cables in Electrical Power System. The Columns provide the information of the fields as follows

• Types of Cables
• Calculated Current in Ampere
• The details of the types of cables as
  1. Rating in Ampere
  2. Ambient temperature
  3. Thermal insulation
  4. Calculated current in Ampere
• Number of cable circuit running together
• Depth of the burial of the cable
• The moisture of the soil

Step 1: With the help of these data we are going to calculate the true rating in Ampere of the cable. These data are accumulated from National fire protection association of USA depending upon the cable we are going to use. Firstly these data are entered into the cells manually.

Step 2: We use a mixed cell reference for entering the formula in the cells from D5 to D9 and D10 to D14 independently as shown in the snapshot.

We use the drag handle to copy the formulas in the cells.

We need to calculate the True Rating (amps), from a given coefficient of Ambient Temperature, Thermal Insulation & Calculated currents. Here we need to understand that we cannot calculate these by relative or absolute referencing as this will lead to miscalculations due to non-uniform data distribution. Hence to resolve this, we need to use mixed referencing because it locks the specific rows and columns according to our need.

Step 3: We have got the calculated values of a true rating of the cable in ampere without any miscalculation or errors.

As we can see from the snapshot above, row number 17, 19, 21 is locked by using the ‘$’ symbol. If we don’t use them dollar symbol, the formula will change if we copy it to another cell as the cells aren’t locked which will change the rows and columns used in the formula.

Applications of Mixed Referencing in Excel

• We can use mixed referencing for efficient data handling for our relevant projects such as explained in the above examples in which relative or absolute referencing makes the data impossible to use.
• It helps us in managing the data handling in a multi-variable environment where the distribution data is not uniform.

Recommended Articles

This has been a guide to Mixed References in Excel. Here we learn how to use Mixed References in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –

• Running Total with Sum Formula in Excel
• Excel Bullet Points
• Insert PI Formula in Excel
• Combine Match Formula with VLookup
• Use 3D Reference in Excel
• What is Cell Reference in Excel?
• VLOOKUP Tutorial
• 3D Maps in Excel