WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Mixed References in Excel

Mixed References in Excel

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 for editing. They are difficult to set-up comparatively but make it easier to enter Excel formulas. They reduce errors 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.

Locks rows and 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.

Mixed References in Excel

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

You can download this Mixed References Excel Template here – Mixed References Excel Template

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.

Mixed Reference Example 1

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.

Example 1-1

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Mixed Reference Example 1-2

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

Mixed Reference Example 1-3

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 Example 1-4

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

Example 2

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.

Mixed Reference Example 2-1

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.

Mixed Reference Example 2-1

As we can see from the snapshot above, row number 17, 19, 21 is locked by using the ‘$’ symbol. If we don’t use the 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 a downloadable excel template. You may learn more about excel from the following articles –

  • What is 3D Reference in Excel?
  • Cell Reference in Excel
  • VLOOKUP Tutorial
  • What are Excel 3D Maps
16 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Mixed References Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More