EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Lookup & Reference Functions in Excel Mixed Reference in Excel
 

Mixed Reference in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 10, 2023

Mixed Reference in Excel

 

 

Mixed Reference in Excel (Table of Contents)
  • Introduction to Mixed Reference in Excel
  • Examples of Mixed Reference in Excel

Introduction to Mixed Reference in Excel

Mixed References in Excel is used to fix either column or row simultaneously. It refers to the only Column or Rows of the referred cell. By this, any of the references can be fixed.

For example, if we want to apply mixed reference in a cell, say A1, then we can fix the column of cell A1 by putting dollar(“$”) before the column name $A1 or to fix the row of cell A1 then we can put dollar before the cell number A$1. In short, when we put $ before anything, it locks it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Examples of Mixed Reference in Excel

Here are the examples of Relative and Absolute Reference work with Mixed Reference in Excel below.

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

Example #1 – Relative Reference

By default, the reference in Excel will consider a Relative reference. Reference is nothing if we input “=A1” in cell B1, then we are referring to A1 in B1. Let’s see an example to understand the relative reference.

Consider the below screenshot of earnings and expenses from Jan to May.

relative reference example 1.1

Now we want to calculate the cash left after the expenses paid from the earnings. We can get that with the simple formula of subtraction of expenses from the earnings. Just input the formula next to expenses to find the remaining monthly cash.

Observe the below screenshot I have input the formula next to expenses which you can find from the formula bar.

relative reference example 1.2

If we observe that the formula in D2 is B2-C2, D2 refers to the combination of B2 and C2. When we drag the formula to the bottom lines, it will take a similar to D2, which means D2 takes reference from B2 and C2, similarly for D3, it will take reference from B3 and C3, For D4 take reference from B4 and C4, and so on.

We can observe the related screenshots for other months. The below screenshot is D3 related, which refers to B3 and C3.

relative reference example 1.3

D4-related screenshot, which refers to B4 and C4.

relative reference example 1.4

That means the formula will always consider the same reference as the first formula related to that. I hope you got an idea of what is a relative reference where we can use it.

Example #2 – Absolute Reference

Consider the same example to understand Absolute reference also. Let us consider from the above screenshot we have earnings and expenses; let’s assume we have fixed rent expenses and the expenses available in the table.

absolute reference example 2.1

In the above Excel, we mentioned rent in only one cell, F2. Now we need to amend the formula in column D to subtract rent from the “cash left after expenses”.

absolute reference example 2.2

Observing the screenshot, we subtracted F2 from the result of B2-C2. Let’s drag the formula to the other cells, also.

absolute reference example 2.3

I dragged the formula, but the results did not change because, in cell D2, we subtracted F2 as explained earlier; by default, Excel will take relative reference, so it took F3 for the formula in D3, similarly F4 in D4. But we do not have any values in the cells F3, F4, F5, and F6; that is why it considers those cells zero and did not affect the results.

We can solve this without dragging rent to F3, F4, F5, and F6. Here Absolute reference helps to achieve the correct results without dragging the rent.

It is very simple to create the absolute reference; we need to click on the formula, select the cell F2, and click D2. Then the absolute reference will create.

absolute reference example 2.4

If $ is applied for column index F and row index 2, then the absolute reference is at the cell level. Now drag the formula in D3, D4, D5, and D6 and see how the results change.

absolute reference example 2.5

Observe the formula in cell D3; it is $F$2 only even after we dragged the formula. As it is locked at the cell level, it will take reference from F2 only. This is the use of absolute reference. This will take the reference from the locked index.

Example #3 – Mixed Reference

As mentioned earlier, we will use both relative and absolute reference in mixed reference. Let’s see how we can use both using the same example.

Consider the data in the below format, as shown in the screenshot.

mixed reference example 3.1

From the above table, we need to find the percentage of each month’s earnings against the “Total” of 5 months’ earnings, similarly, percentages for expenses and cash left after expenses.

If still unclear, we need to find 5000 percent out of the total 5 months’ earnings of 32100.

Now in the below empty table, we need to find the percentage for each cell.

mixed reference example 3.2

The formula for finding the percentage is divided by Jan’s earnings with a total of 32100.

mixed reference example 3.3

If you observe, I have used the absolute reference because all month’s earnings should divide by the total only. Now we can drag the formula until May.

mixed reference example 3.4

Now drag the formulas to the bottom also.

mixed reference example 3.5

If we observe still, the formula for expenses is taking a total of Earnings only, but actually, it should take expenses total of 15200. For that, we need to create a reference at the column level alone, not required at the row level.

mixed reference example 3.6

Observe that the formula $ symbol is available at N, not row index 11. Make the changes always to the first formula, then drag to the other cells.

mixed reference example 3.7

Everywhere the formula is picking correctly. If we observe, we performed the absolute reference in row operations and relative reference in column operations. I hope now it is clear how to perform mixed reference and in which areas it will be useful.

Things to Remember

  • A mixed reference is a combination of relative reference and absolute reference.
  • Relative reference is a default reference in Excel while referring to any cells in Excel operations. This other cell will take a similar reference as the first formula.
  • An absolute reference is another type that will take the reference as fixed where ever you paste the formula.
  • To create an absolute reference, select the cell reference and click on F4; then, the $ symbol will add to the row and column indexes.
  • Mixed reference combines both; hence, we may apply an absolute reference to the column or row indexes alone.
  1. $A1 – Means reference created for column index.
  2. A$1 – Means reference created for row index.
  3. $A$1 – Means reference created for both row and column indexes.

Recommended Articles

This is a guide to Mixed Reference in Excel. Here we discuss How Relative and Absolute References work with Mixed References in Excel, examples, and a downloadable Excel template. You may also look at the following articles to learn more –

  1. Cell References in Excel
  2. 3D Cell Reference in Excel
  3. Relative Reference in Excel
  4. How to Add Cells in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download Mixed Reference Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Mixed Reference Excel Template

EDUCBA

डाउनलोड Mixed Reference Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW