Excel Mixed References

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, and part is absolute. We can use them to copy the formula across columns and rows, eliminating the manual need for editing. They are difficult to set up but make it easier to insert Excel formulasExcel FormulasThe term “basic excel formula” refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more. They reduce errors significantly as the same formula gets copied. For example, the dollar sign, when placed before the number, then it means that it has locked the Row. Similarly, when the dollar sign is placed before the alphabet, it has locked the column.

Striking the F4 key multiple times helps change the dollar sign’s position. It is also noted that we cannot paste mixed references into a table. We can only create an absolute or relative reference in a table. However, we can use the excel shortcutExcel ShortcutAn Excel shortcut is a technique of performing a manual task in a quicker way.read more “ALT+36” or “Shift+4” key for inserting the dollar sign in Excel.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Mixed References in Excel (wallstreetmojo.com)

How to use Mixed Reference in Excel? (with Examples)

Example #1

Example #2

Now let us take a look at a more complicated example. The table below shows the calculation of the derating of cables in the electrical power system. The columns provide the information of the fields as follows:

  • Let us write down the multiplication table, as shown below. The rows and columns contain the same numbers, which we will multiply. We have inserted the multiplication formula along with the dollar sign. The formula has been inserted, and we have copied the same formula in all the cells. We can easily copy the formula by dragging the fill handle over the cells we need to copy. Then, we can double-click on the cell to check the formula for accuracy. We can view the formula by clicking on the “Show Formulas” command in the “Formulas” ribbon. Looking closely at the formulas, we notice that column “B” and 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.

The rows and columns contain the same numbers, which we will multiply.

Looking closely at the formulas, we notice that column “B” and row “2” never change. So it is easily understood where we need to put the dollar sign.

  • Types of CablesCalculated Current in AmpereThe details of the types of cables as Rating in AmpereAmbient temperatureThermal insulationCalculated current in Ampere Number of cable circuit running togetherDepth of the burial of the cableThe moisture of the soil

Step 1: With the help of these data, we will calculate the true rating in ampere of the cable. These data are accumulated from the National Fire Protection Association of the USA, depending on the cable we will use. Firstly, these data are entered into the cells manually.

  • Rating in AmpereAmbient temperatureThermal insulationCalculated current in Ampere

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,” and “Calculated (Amps)” 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, we need to use mixed referencing to resolve this because it locks the specific rows and columns according to our needs.

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

As we can see from the snapshot above, row number 17, 19, and 21 is locked by using the ‘$’ symbol. If we do not use the dollar symbol, the formula will change if we copy it to another cell as the cells are not 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, as explained in the above examples in which relative or absolute referencing makes the data impossible to use.It helps us manage the data handling in a multi-variable environment where the distribution data is not uniform.

This article is a guide to Mixed References in Excel. We learned to use mixed references in Excel with some examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • What is 3D Reference in Excel?Cell Reference in ExcelVLOOKUP TutorialWhat are Excel 3D Maps