Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Mixed reference is a type of cell reference in excel 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.
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
- Select the cell in which the formula is to be applied.
- Type the equal sign (=) in the formula bar.
- 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:
- Relative Referencing
- Absolute Referencing
- 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
- 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 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.
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 –