Excel Mixed References
Mixed reference in excel is a type of cell reference which is different from the other two absolute and relative, in mixed cell reference we only refer to the column of the cell or the row of the cell, for example in cell A1 if we want to refer to only A column the mixed reference would be $A1, to do this we need to press F4 on the cell two times.
Explanation
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 cells 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? (with Examples)
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.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
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.
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
- Rating in Ampere
- Ambient temperature
- Thermal insulation
- 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 the 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 needs.
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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
