in Excel
- In Excel, each worksheet consists of a number of cells that are made up of rows and columns.
- Each cell has a unique reference, which enables users to quickly access and address the required cell (or cells) within the functions.
- In Excel, cell references are crucial, particularly when working with huge data sets in functions and formulas.
- We can access the value of any cell with the help of cell references in excel. Due to this when we change the value of one cell then in calculation part where we access that cell the result will automatically change.
- with the help of cell address, we can easily identify all the cell or each cell in the worksheet uniquely.
What is a Cell Reference?
An Excel cell reference, also known as a cell address, is a mechanism that defines a cell on a worksheet by combining a column letter and a row number. We can refer to any cell (in Excel formulas) in the worksheet by using the cell references.
Or
The address of a cell is known as cell reference.The cell address is made up of column letter and row number.
Example: A1: Comuln A and row 1
For example:
Here we refer to the cell in column A & row 2 by A2 & cell in column A & row 5 by A5. You can make use of such notations in any of the formulas or copy the value of one cell to another cell (by using = A2 or = A5).
Types of Cell Reference in Excel
Understanding various cell references primarily makes it easier for us to use Excel formulas and avoid unexpected formula errors. When copying and pasting Excel formulas, this is quite useful. Based on various use situations, Excel offers three main types of cell references, including:
- Relative Cell Reference
- Absolute Cell Reference
- Mixed Cell Reference
- Circular Cell Reference
- 3-D Cell Reference
1. Relative Cell Reference
- In Excel, a relative cell reference is used by default.
- Excel uses a relative reference whenever we insert a cell reference or a range within a formula.
- The relative references, which commonly reflect the combination of column name and row number, are used normally with the associated cell references.
- There is no dollar ($) sign in the relative reference for the cell.
When to Use Relative Cell References in Excel
- When you need to develop a formula for a set of cells and the formula needs to make a reference to a relative cell reference, relative cell references come in handy.
- When this occurs, you can create the formula in one cell and copy it before pasting it into every other cell.
How to Use Relative Cell References in Excel
Example 1:
If you refer to cell B1 from cell E1, actually you would be referring to a cell that is 3 columns to the left (E-B = 3) within the same row number 1.
When it is copied to other locations present in a worksheet, the relative reference for that location will be changed automatically. (because relative cell reference describes offset to another cell rather than a fixed address as In our example, offset is : 3 columns left in the same row).
Example 2:
If you copy the formula = C2 / A2 from the cell “E2” to “E3”, the formula in E3 will automatically become =C3/A3.
2. Absolute Cell Reference
When copying or using AutoFill, there are times when the cell reference must stay the same. A column and/or row reference is kept constant using dollar signs. So, to get an absolute reference from a relative, we can use the dollar sign ($) characters.
To refer to an actual fixed location on a worksheet whenever copying is done, we use absolute reference. The reference here is locked such that rows and columns do not shift when copied.
How to Use Absolute Cell References in Excel
Below is an example depicting how to use Absolute Cell References in Excel.
Example 1:
When we fix both row & column – Say if we want to lock row 2 & column A, we will use $A$2 as:
G2 = C2/$A$2, when copied to G3, G3 becomes = C3/$A$2
Note: C3 is 4 columns left to G3 in the same row.
Here, original cell reference A2 is maintained whenever we copy G2 to any of the cells. So I3 = E3/$A$2 because E3 comes from the relative reference (4 columns left to the current one) & /$A$2 comes from the absolute reference.
Therefore, I3 = E3//$A$2 = 12/10 = 1.2
What Does the Dollar ($) Sign Do?
When the row and column numbers are preceded by the dollar symbol ($), it becomes absolute (i.e., stops the row and column number from changing when copied to other cells). Dollar ($) before the row fixes the row & before the column fixes the column.
When to Use Absolute Cell References in Excel
Absolute cell references in Excel are useful when you want to lock a specific cell reference within a formula so that it doesn’t change as you copy the formula to other cells. Unlike relative cell references, which adjust when copied to different cells, absolute references remain constant.
Key Situations to Use Absolute Cell References
1. Fixed Values in Formulas
o When you need a formula to refer to a fixed value in a specific cell, such as a tax rate or constant discount percentage, absolute references are ideal.
o Example: In a sales calculation, if cell A2 contains a price and B1 contains the tax rate, use =A2 * $B$1. This ensures that every calculation references the fixed tax rate in B1.
2. Setting Up a Table with Fixed Multipliers or Factors
o When calculating values that rely on a single multiplier across rows or columns, an absolute reference keeps the multiplier constant as the formula is copied.
o Example: For converting prices in one currency to another using a fixed conversion rate in cell C1, a formula like =A2 * $C$1 will always reference the conversion rate in C1.
3. Calculating Totals and Averages Against a Fixed Reference
o In cases where you’re comparing each row's or column’s value to a total or average located in a single cell, absolute references are needed to avoid changing the reference.
o Example: To calculate the percentage of total sales for each item, if the total sales amount is in E10, use =D2 / $E$10 to maintain the reference to E10 for every item.
4. Copying Formulas Across Rows and Columns
o If you’re building a formula that will be copied across both rows and columns, use absolute references for cells you want to remain constant, even as other parts of the reference may change.
o Example: In a budget or forecast sheet, where certain values like fixed costs are used across multiple scenarios, keeping specific cell references constant with $ symbols ensures consistent calculations.
Example of Absolute vs. Relative References in Practice
Formula | Relative | Absolute |
=A2 * B1 | Changes based on cell position. | $A$2 * $B$1 remains constant. |
Absolute cell references are essential for reliable calculations in Excel where consistent reference to specific data is needed across multiple cells and formulas.
3. Mixed Cell Reference
An absolute column and relative row, or an absolute row and relative column, is a mixed cell reference. You get an absolute column or absolute row when you individually put the $ before the column letter or before the row number.
Example: $B8 is relative to row 8 but absolute for column B, and B$8 is absolute for row 1 but relative for column A.
Here, the Dollar ($) before the row number fixes/locks the row & before the column name fixes/locks the column.
Mixed cell references in Excel are useful when you want to lock either the row or the column within a formula, but not both. This allows part of the reference to adjust as you copy the formula while keeping the other part constant. Mixed cell references are especially helpful when you need a formula that involves both static and dynamic references.
Key Situations to Use Mixed Cell References
1. Creating Multiplication Tables or Grids
o In multiplication tables or any calculation grid where you want to multiply rows and columns (like a sales commission table), mixed references can lock a row or column to ensure that the multiplication uses the correct factors.
o Example: In a table where row 1 has prices (A1, B1, etc.) and column A has quantities (A2, A3, etc.), using a formula like =$A2 * B$1 allows the row to adjust as you move down but keeps the references in the row and column headers constant.
2. Applying a Fixed Factor to Rows or Columns
o When applying a specific factor across an entire row or column but allowing the other dimension to change, mixed references are ideal. This is common when calculating values with a fixed rate or unit price per row or column.
o Example: For a budgeting sheet where each column represents a month and each row represents a category, if you want to keep the monthly budget constant (like $B$1 for all items), you can use a mixed reference to ensure the calculation is consistent across that row.
3. Building Formulas That Combine Constants with Variable Values
o In cases where you want to keep one aspect constant (like a specific row or column) while allowing other parts to vary, a mixed reference enables this flexibility.
o Example: When calculating year-over-year growth rates for different products, if each product has a unique growth rate but you want to apply it consistently across years, using B$1 * $A2 can fix the rate or product reference while allowing year-specific adjustments.
4. Creating Dynamic Calculations in Data Tables
o In data tables that involve comparing data across categories (like monthly totals by department), mixed references enable the table to dynamically reference fixed rows or columns while adjusting for other variables.
o Example: To calculate expenses where a monthly factor is applied across departments, using B$1 * $A2 ensures the month and department remain anchored while other values adjust.
5. Using Lookup Tables and Indexing
o Mixed references can simplify complex calculations that rely on lookup tables or indexing functions. When performing lookups across a range, mixed references lock either rows or columns as needed for consistent calculations across tables.
o Example: In a commission calculation, where commission rates are organized in a table with departments on one axis and months on the other, using $A2 or B$1 enables quick reference within the table.
Example
When we fix the only row: If we have G2 = C2/A$2 then :
We used $ before the row number, so we are locking the only row here. When G2 is copied to G3, G3 = C3/A$2 (not C3/A3) because the row has been fixed already.
Here, whenever we copy G2 to any other cell, always the divisor will refer to a fixed row 2 (column vary according to the concept of relative reference)
So, when G2 is copied to I3, I3 = E3/C$2 because E3 comes from the relative reference (4 columns left to the current one) & C$2 comes from the absolute reference for row & relative reference for Column (6 Columns left to the current one)
4. Circular cell references in Excel
Circular cell references in Excel occur when a formula refers to its own cell directly or indirectly, creating a loop. While they are typically a sign of an error, circular references can be intentionally useful for iterative calculations where the result depends on previous values in the same cell. Here are some situations where you might want to use them:
Key Situations to Use Circular References
1. Creating Iterative Calculations (Loan Amortization or Interest Calculations)
o In financial models, such as loan amortization schedules, interest calculations often need to be compounded based on prior values in the same cell.
o Example: Calculating compound interest, where interest for each period is added to the principal, and this new principal becomes the basis for the next calculation. Circular references allow each new calculation to build on the previous result.
2. Running Totals or Accumulated Values
o When you want a cell to accumulate or update a value based on its prior content, circular references allow you to keep a running total or perform cumulative calculations.
o Example: Keeping track of inventory levels by adding new stock to the previous total. Each time stock is added, the cell updates the previous value with the new total.
3. Simulating Time-Based Data Changes (Simulations or Forecasting)
o Circular references can help model scenarios where each value builds upon the last, such as forecasting future values based on the previous time period’s output.
o Example: Predicting population growth, where each year’s population depends on the previous year’s data.
4. Self-Correcting Calculations for Specific Targets
o Circular references are sometimes used to create self-correcting formulas that converge on a specific value.
o Example: In a goal-seek scenario where you want to adjust a value in a cell to reach a target, a circular reference can iteratively adjust until the target is met.
Setting Up Circular References in Excel
To use a circular reference intentionally:
1. Enable Iterative Calculations:
o Go to File > Options > Formulas.
o Under Calculation options, check Enable iterative calculation and set the Maximum Iterations and Maximum Change.
§ Maximum Iterations controls the number of times Excel recalculates (for limiting processing time).
§ Maximum Change controls the accuracy of the result.
Example of Circular Reference Use Case
Imagine you want to calculate the final balance of an investment that grows each year based on interest compounded annually. You can set up a circular reference where the final balance depends on the previous year’s balance, compounding the interest annually.
- Formula: In cell A1, =A1 * (1 + Interest Rate), where the cell constantly updates based on the last balance.
- Iterative Calculation: With iterations enabled, Excel will continue updating the balance until the set accuracy or iteration limit is met.
Precautions with Circular References
Circular references can sometimes cause Excel to become slow or produce unintended results, so they should be used sparingly and only when necessary. Setting limits on iteration and precision helps control these issues.
Example of using circular references to calculate compound interest on an investment, where each year's ending balance becomes the next year's starting balance. We'll use an iterative approach that requires a circular reference.
Scenario
Suppose you want to invest $1,000 with an annual interest rate of 5%, compounded yearly. You want to calculate the balance after each year by compounding the interest on the previous year's balance.
Example Setup in Excel
1. Enable Iterative Calculations:
o Go to File > Options > Formulas.
o Under Calculation options, check Enable iterative calculation.
o Set Maximum Iterations to a value like 100 and Maximum Change to a small number (e.g., 0.01) for accuracy.
2. Excel Table Structure:
Year | Balance |
1 | $1,000 |
2 | Formula |
3 | Formula |
... | ... |
N | Formula |
3. Let's assume we're using cells A2 for Year and B2 for Balance.
4. Formula for Circular Reference:
o In cell B2, input your initial investment, $1,000.
o In cell B3, enter the formula =B2 * (1 + 5%).
Explanation of the Circular Reference
- How It Works: Each cell in the Balance column refers to the previous cell to calculate its new balance with interest. Cell B3 contains the formula =B2 * (1 + 5%), meaning it multiplies the previous year's balance by 1.05 (100% + 5% interest).
- What It Does: When Excel calculates, it uses the previous balance iteratively, adjusting each year’s balance based on the compounded interest.
This setup requires a circular reference because each cell in the Balance column builds on the previous one, effectively creating a loop where each year’s ending balance is recalculated from the previous year.
Detailed Walkthrough of Calculations
Year | Formula in Balance | Calculated Balance |
1 | Initial Value: $1,000 | $1,000 |
2 | =B2 * (1 + 5%) | $1,050 |
3 | =B3 * (1 + 5%) | $1,102.50 |
4 | =B4 * (1 + 5%) | $1,157.63 |
5 | =B5 * (1 + 5%) | $1,215.51 |
Benefits of Using Circular Reference Here
Using a circular reference with iterative calculations allows Excel to recompute each year’s balance by compounding interest without manually adjusting each cell. This setup saves time and minimizes errors, as Excel iteratively refines the result to converge on an accurate compounded balance.
Important Note
Circular references should be used cautiously, as they can slow down calculations or cause errors if not set up carefully. Always ensure that iterative calculations are enabled and appropriately configured to prevent endless loops.
Suppose you want to create a running balance for a savings account where each month’s balance depends on the previous month’s balance plus interest. The interest is calculated monthly, so each new balance references the previous balance, creating a circular reference.
Example Setup
You’re investing $1,000 initially, with a monthly interest rate of 2%. You want Excel to automatically update the balance each month by adding this 2% to the last month’s balance.
Step-by-Step Guide to Setting Up the Circular Reference
Step 1: Enable Iterative Calculation
Because circular references can lead to endless loops, Excel typically disables them by default. We need to enable Iterative Calculation to allow controlled circular references.
1. Go to File > Options > Formulas.
2. In the Calculation options section, check Enable iterative calculation.
3. Set Maximum Iterations to 100 (for accuracy, but it doesn’t need to be exact).
4. Set Maximum Change to 0.01 (to limit the recalculations to when a significant change happens).
Step 2: Set Up Your Worksheet
Create a basic layout with the following columns:
Month | Balance |
1 | 1000 |
2 | |
3 | |
… | |
N |
Explanation: Here, each row represents a month, and each cell in the Balance column will display the savings account balance at the end of each month.
Step 3: Enter the Starting Balance
1. In cell B2, enter your starting balance: 1000.
2. In cell B3, we’ll set up a formula that references B2 (the previous month’s balance) and adds 2% interest to it, creating the circular reference.
Step 4: Enter the Circular Reference Formula
1. In cell B3, enter this formula:
=B2 * 1.02
This formula means that the balance in B3 will be the previous month’s balance (B2) multiplied by 1.02 (which is 100% + 2% interest).
2. Now copy the formula from B3 down the Balance column (e.g., B4, B5, and so on) to calculate balances for each subsequent month.
3. With iterative calculations enabled, Excel will automatically update each cell, and you’ll see each month’s balance increase based on the previous month’s balance plus 2% interest.
Explanation of the Circular Reference Formula
In this setup:
- B2 holds the initial balance of $1,000.
- B3 calculates the balance for Month 2 by multiplying B2 by 1.02 (adding 2% interest).
- B4 then takes the balance from B3 and multiplies it by 1.02 again, and so on.
Final Result:
Monthly Balance Table
After dragging the formula down through several rows, your table will look like this:
Month | Balance |
1 | $1,000.00 |
2 | $1,020.00 |
3 | $1,040.40 |
4 | $1,061.21 |
5 | $1,082.43 |
6 | $1,104.08 |
... | ... |
Each balance automatically references the previous month’s balance with interest added, and Excel iteratively calculates these values because of the circular reference.
Why This Works
Circular references let you reference a value that depends on itself, useful when calculating dependent values like compound interest, running balances, or iterative adjustments. With iterative calculations enabled, Excel stops recalculating once the values stabilize or meet the maximum change threshold, avoiding an infinite loop.
Key Points to Remember
- Circular references are typically not used in basic calculations but can be helpful for financial projections, balancing accounts, or iterating calculations.
- Iterative calculations must be enabled to avoid Excel errors.
- Use circular references sparingly, as they can slow down calculations or cause errors if set up incorrectly.
This example of a running balance shows how Excel can handle dynamic calculations using circular references, ideal for financial and growth modeling tasks.
5. 3-D cell reference in Excel
A 3-D cell reference in Excel refers to the same cell or range across multiple worksheets, allowing you to perform calculations across several sheets without manually selecting each one. This is especially helpful when working with data organized in similar ways across multiple sheets, like monthly financial data, departmental budgets, or regional sales figures.
Situations to Use 3-D Cell References
1. Consolidating Data Across Similar Sheets (e.g., Monthly Reports)
o When you have separate worksheets for each month or quarter (e.g., "Jan," "Feb," "Mar") with identical layouts, 3-D cell references make it easy to sum, average, or find the maximum value across all months.
o Example: Calculate the total expenses for the entire year when each month has its own sheet.
2. Budget Summaries for Multiple Departments or Locations
o If each department or location has a dedicated worksheet, and you want to compile totals or averages, 3-D references can pull data from each worksheet without needing to reference them individually.
o Example: Calculate total salaries across departments with each department's budget on a separate sheet (e.g., "Marketing," "Sales," "HR").
3. Consolidating Forecasts or Projections Across Time Periods
o For businesses that project sales or performance across multiple periods (e.g., quarters or years), each period can be on a separate worksheet with a 3-D reference used to combine the data into an overall forecast.
o Example: Sum projected sales across quarterly sheets to determine annual sales projections.
4. Data Comparison Across Different Scenarios
o If you’re running scenarios in different sheets (e.g., "Best Case," "Expected," and "Worst Case"), you can use 3-D references to easily compare or aggregate results across these scenarios.
o Example: Calculate the average revenue across all scenarios to see the expected outcome.
How to Use 3-D Cell References
A 3-D reference syntax is structured as:
=Function(Sheet1:SheetN!CellRange)
Here:
- Function is a function like SUM, AVERAGE, MAX, etc.
- Sheet1:SheetN specifies the range of sheets.
- CellRange is the range of cells within each sheet you want to reference.
Example in Practice
Suppose you have a workbook with sheets named "Jan," "Feb," "Mar," each containing total sales in cell B2. To get the total sales for the quarter, use a 3-D reference formula in a summary sheet:
1. In the summary sheet, use:
=SUM(Jan:Mar!B2)
2. This formula will sum the value in cell B2 across all sheets from "Jan" to "Mar," giving you the total sales for the quarter.
3-D cell references streamline analysis across multiple sheets, making it easier to maintain and update data structures across similar datasets without repetitive referencing.
Scenario
Suppose you’re a sales manager, and you have monthly sales data across three separate sheets for January, February, and March. You want to calculate the total quarterly sales in a summary sheet by combining data from all three months.
Each sheet (January, February, March) has the following setup:
Item | Sales |
Product A | $500 |
Product B | $300 |
Product C | $200 |
Your task is to calculate the total sales for each product over these three months using a 3-D reference in a summary sheet.
Step-by-Step Guide to Setting Up the 3-D Reference
Step 1: Set Up Monthly Sheets
1. Open a new Excel workbook.
2. Create three worksheets and name them "Jan", "Feb", and "Mar" for each month.
Step 2: Enter Monthly Sales Data
In each sheet (Jan, Feb, and Mar), set up the same data table:
- In cell A1, type "Item".
- In cell B1, type "Sales".
- In the rows below, enter data as follows:
- A2: "Product A", B2: 500
- A3: "Product B", B3: 300
- A4: "Product C", B4: 200
Repeat this for each of the three sheets.
Step 3: Create a Summary Sheet for Quarterly Sales
1. Add a new sheet and name it "Summary".
2. In the Summary sheet, set up a table to calculate total quarterly sales:
o In cell A1, type "Item".
o In cell B1, type "Total Sales".
o In the rows below, list the products:
§ A2: "Product A"
§ A3: "Product B"
§ A4: "Product C"
Step 4: Use a 3-D Cell Reference to Calculate Total Sales
1. In cell B2 of the Summary sheet, enter the following formula to calculate total sales for Product A:
=SUM(Jan:Mar!B2)
o This formula will add up the values from cell B2 in each of the sheets "Jan," "Feb," and "Mar."
2. Press Enter, and you should see the result 1500 (since Product A has $500 sales each month, and 500 + 500 + 500 = 1500).
3. Copy the formula in cell B2 down to cells B3 and B4 to calculate total sales for Product B and Product C:
o In B3, you’ll get 900 (300 + 300 + 300).
o In B4, you’ll get 600 (200 + 200 + 200).
Final Summary Sheet Layout
Your Summary sheet should look like this after entering and copying the formulas:
Item | Total Sales |
Product A | $1500 |
Product B | $900 |
Product C | $600 |
Explanation of the 3-D Reference Formula
- =SUM(Jan:Mar!B2):
- SUM is the function used to add numbers.
- Jan specifies the range of sheets (from Jan to Mar) that the formula should look across.
- B2 is the cell in each sheet that contains sales data for Product A.
Why This Works
The 3-D reference allows Excel to pull data from the same cell (B2) across multiple sheets in a single formula. This approach saves time and ensures consistency, especially when working with similar data across multiple sheets. If you need to update the data, you only have to change it in one of the monthly sheets, and the Summary sheet will automatically update.
Summary of Benefits
- Saves time by referencing multiple sheets with a single formula.
- Minimizes errors by eliminating the need to manually add each sheet.
- Updates automatically if any monthly sales data changes.
This approach is especially useful for consolidating data across periods, departments, or similar categories in Excel.
Some Other Ways of Using Cell References With Examples
Now that we are familiar with the basics of using Cell References in Excel, let’s see some other ways of using cell references.
Relative and Absolute Cell References for Calculating Dates
We can use relative and absolute cell references to calculate dates.
Example:
To Calculate the Date of Delivery online from the given date of the order placed & no of days it will take to deliver :
Here, We calculate the Date of Delivery by = Order Date + No of days to deliver. We used Relative cell reference so that individual product delivery dates can be calculated.
Absolute cell references for calculating dates :
Example: To Calculate the Date of Birth When the age is known is a number of days using Current date can be done by making use of absolute reference.
Here, We calculate DOB by = Current Date – Age in days. The Current date is contained in the cell E2 & in subtraction, we fixed that date to subtract from the days.
Whole Column Reference
You will want to refer to all the cells inside a particular column when operating with an Excel worksheet with any number of rows. Simply type a column letter twice with a colon in between to refer to the entire column B, for example, B:B.
Example:
You may want to find the sum of a column of data in certain cases. While you can do this with a regular cell range, such as =SUM(B1:B10), you will need to change the cell range if your spreadsheet grows in size.
Excel, on the other hand, has a cell range that does not require the row number and takes all the cells in the column in action. If you wanted to find the sum of all the values in column B, for example, you would type =SUM (B:B). You can add as much data as you want to your spreadsheet without having to change your cell ranges if you use this type of cell range.
Whole Row Reference
You will want to refer to all the cells inside a particular row when operating with an Excel worksheet with any number of columns. Simply type a row number twice with a colon in between to refer to the entire row, for example, 2:2.
Example:
You may want to find the sum of a row of data in certain cases. While you can do this with a regular cell range, such as =SUM(A2 : J2), you will need to change the cell range if your spreadsheet grows in size.
Excel, on the other hand, has a cell range that does not require the column letter and takes all the cells in the row in action. If you wanted to find the sum of all the values in row 2, for example, you would type =SUM (2:2). You can add as much data as you want to your spreadsheet without having to change your cell ranges if you use this type of cell range.
Refer to an Entire Column, Excluding the First Few Rows
To refer to the entire column excluding the first few rows, you need to specify the range as we give in a normal fashion. We know that the Excel worksheets can have only 1,048,576 rows. (To check this, go to an empty cell & press: Ctrl + Down arrow Key)
So, we can do the sum of the entire column B except for the first 5 rows by = SUM(B6:B1048576).
Using a Mixed Entire Column Reference in Excel
You can also create a mixed entire-column reference, say for example $B:B. But, practically, it is difficult to find a situation where it would be used.
Example :
How to switch between Absolute, Relative, and Mixed References
The $ sign can be manually typed in an Excel formula to adjust a relative cell relation to absolute or mixed. You can also speed things up by pressing the F4 key. You must be in formula edit mode to use the F4 shortcut. The steps are :
Firstly, choose the cell that contains the formula. Then, by pressing the F2 key or double-clicking the cell, you can enter Edit mode. Select the cell reference in which you want to make changes. Then, switch between four-cell reference forms by pressing F4.
Example:
When you select a cell having only relative reference (i.e., no $ sign), say = B2:
- The first time when you press F4, it becomes =$B$2
- The second time when you press F4, it becomes =B$2
- The third time when you press F4, it becomes=$B2
- The fourth time when you press F4, it becomes back to the relative reference=B2
B2 –Press F4–> =$B$2 –Press F4–> =B$2 –Press F4–> = =$B2 –Press F4–> =B2
So, using F4, you do not require to manually type the $ symbol.
Cell Reference to Other Worksheets
Cell reference in Excel are not limited to just the current worksheet. You can also reference cells from other worksheets within the same workbook.
Let’s walk through an example to understand how to create these references.
Suppose we have two worksheets : “Sheet1” and “Sheet 2”. In “Sheet 2” we have value Student name that we want reference in “Sheet 1”.
Follow the below steps to create direct reference to “Sheet 2” from “Sheet 1”:
Steps are as follows:
Step 1: Activate a cell in “Sheet 1” where you want to display the referenced value.
Step 2: Type an equal sign(=) in that cell to start a formula or reference.
Step 3: Switch to “Sheet 2” by clicking on its tab at the bottom of the Excel window.
Step 4: Locate the Specific cell that contains the desired value.
Step 5: Click on that cell to include it in the reference.
Step 6: Press Enter to complete the reference.
Once you press Enter, a reference is created in “Sheet 1” that points to the corresponding cell in “Sheet 2”. This means that any changes made to the referenced cell in “Sheet 2” will automatically reflect in the referenced value within “Sheet 1”.
Important Points to Remember
- One of the crucial components for Excel functions or formulas is the cell reference.
- Excel formulas that employ relative cell references automatically change the references to match the correct row and column.
- When copying formulas into non-relative cells, absolute cell references are advised. Excel keeps the absolute cell references constant.
- According to the specifications, a mixed reference only locks one of the references, either the row or the column. Not both are locked.
FAQs on Excel Cell References
What is Cell Reference in Excel?
Cell Reference in Excel is a alphanumeric value that means it consists of combination of column letter and a row number that identifies a specific cell on a worksheet. It is used to refer to or manipulate data within that cell.
How we can create a cell reference in Excel?
To create a cell reference, you can simply type the column letter followed by the row number in a formula or function.
When we can use mixed cell reference in Excel?
Mixed cell references are useful when you want to fix either the column or the row while allowing the other part to adjust when copied.
