How to use absolute cell reference in excel (Examples and Guide)

When we wish to fix the position of the selected cell in a formula so that its value won't change whenever we move the cell or copy the formula to other cells or sheets, we use absolute references in Excel. The dollar ($) sign should be used both before and after the column name of the chosen cell to do this. Alternatively, we can type the F4 function key, which will immediately cover the alphabet of the column with the dollar. For instance, cell A1 will appear as =$A$1 if it has to be fixed.

In Excel, a cell address with a dollar sign ($) in it is referred to as an absolute cell reference. The column reference, the row reference, or both may come before it. In Excel, we can maintain a constant row, column, or both with an absolute cell reference. When transferred to additional cells, it remains unchanged.

How to use absolute cell reference in excel online, 2016 and 2019

 The names of some employees and the matching number of hours they worked are listed in the data below. Everybody receives the same fixed hourly pay.

Using example 1

1. The gross salary must now be determined based on the number of hours worked.

2. Hourly Wage Rate = $10.00We have a fixed hourly wage of $10.00. We will now figure up the gross compensation for each employee.

3. As shown in the screenshot above, to get the gross pay for employee John, we will multiply the number of hours worked (B20) by the fixed-wage hourly rate (B16) in cell C20. The outcome is:

4. As shown in the screenshot below, if we drag this formula for the remaining employees, the results will be inaccurate.

5. The value of a set hourly wage rate may be seen fluctuating for each employee in the screenshot above. However, all employees receive the same fixed wage rate. Cell B16 will serve as an absolute cell reference going forward. Please view the image below:

6. The column name and the row number will both be preceded by the dollar sign $, as in $B$16.The result is:

7. By doing this, we secure B16's value for all employees. Now, when we drag this formula for the remaining employees, it will produce the right results.By dividing the total number of hours put in by all employees by the hourly rate for a set wage, the gross compensation is calculated. Please look at the image below:

Absolute reference in excel using example 2

1. Ritika goes to a store and purchases some goods and cosmetics. She selects the following around 15 items:

2. The Goods and Services Tax (GST) rate, which is 10%, is applied to each item. We must figure out the tax rate for each item in order to determine the Net Amount.By multiplying the quantity of each item by the appropriate product price, we will first determine the total cost of each item.

The result is:

3. For the remaining goods, drag this formula.

4. We must now multiply the total price by the GST rate in order to determine the tax rate for each product.GST is charged at 10%. Since GST is fixed for each item, we shall utilise an absolute cell reference in this case. Consequently, we will use key F4 to create cell E33 as an absolute cell reference.

The result is:

5. Drag this formula now to the remaining objects.

6. The total bill can be obtained by adding the total cost and GST.

7. So, according to the screenshot below, the result is Rs. 300.60:

How to create Absolute Reference in Excel?

Steps

Step 1: Select the cell or range of cells that you want to reference.

Step 2: In the formula bar, click on the cell reference you want to make absolute.

Step 3: Add dollar signs ($) before the column letter and row number to lock the reference.

Step 4: Press Enter or click outside the formula bar to apply the absolute reference.

Now, when you copy or fill the formula, the locked cell references will remain unchanged, ensuring data integrity.

Pros:

• Easy to understand: Absolute references in Excel are straightforward to comprehend, allowing for consistent referencing by adding dollar signs to cell references.

• Data integrity: Absolute references ensure the accuracy and integrity of data by locking cell references, preventing unintended changes during copying or filling formulas.

Cons:

• Slower process: In certain cases, using absolute references for every cell reference can slow down operations, particularly when dealing with large datasets or complex formulas.

How to use F4 Key to quickly switch between absolute and Relative Reference?

Steps

Step 1: Open the Excel worksheet and begin typing or editing a formula that involves a cell reference. Identify the specific cell reference that requires an absolute reference.

Step 2: Instead of manually inserting dollar signs ($) before the column letter and row number, simply press the F4 key. This will automatically add both dollar signs to the cell reference.

Step 3: If needed, press F4 again to cycle through different types of cell references. This can be useful to lock the row or column individually or in combination.

Step 4: Complete the formula by finishing your input or edits as necessary.

Step 5: To copy the formula to other cells, double-click the fill handle located at the bottom-right corner of the selected cell. This will automatically apply the formula to the desired range.

Step 6: Verify the formula bar to confirm that the absolute reference is functioning correctly in all copied formulas.

By following these steps, you can conveniently create absolute references in Excel and ensure that the referenced cells remain unchanged when copying or filling formulas to other cells.

Pros:

• Shortcut: Absolute references provide a quick and efficient way to lock cell references in Excel formulas.

• Easy and quick to operate: Adding dollar signs to cell references is a straightforward process, making it easy to implement absolute references.

Cons:

• Potential for errors: If you are not familiar with using shortcuts or absolute references, there is a higher chance of making mistakes or errors in the formula.

• Dependence on familiarity: The effectiveness of absolute references relies on the user's knowledge and familiarity with using shortcuts, which may pose challenges for those less experienced

How to avoid circular reference with goal seek in Excel?

The purpose of this section is to guide you through the usage of the Goal Seek function, which is a powerful tool for performing what-if analysis in spreadsheet applications. By using this function, you can determine the input value needed to achieve a desired outcome in a formula.

To demonstrate the functionality of Goal Seek, let's consider a simple data set and scenario:

To determine the number of items you need to sell in order to make $1,000, let's set up the necessary calculations and use the Goal Seek function. Here's how you can do it:

Step1：Arrange your data in such a way that you have a formula cell and a changing cell that is reliant on the formula cell.

Step 2：Navigate to the Data tab > Forecast group, then click the What if Analysis option and choose Goal Seek...Define the cells/values to test in the Goal Seek dialog box and click OK

Step 3： Set cell - the reference to the formula-containing cell (B5).

To value - the formula result you're aiming for (1000).

By altering cell - the reference for the input cell that has to be changed (B3).

The Goal Seek Status dialog box will display, informing you whether or not a solution has been identified. If it was successful, the value in the "changing cell" was changed with a new one. Click OK to maintain the modified value or Cancel to go back to the original.

In this example, Goal Seek discovered that 223 things (rounded up to the nearest integer) must be sold in order to generate $1,000 in revenue.

Pros:

• Quick and efficient: Goal Seek automates the process of finding input values, saving time and effort.

• Simplified analysis: Goal Seek allows users to focus on desired results without the complexity of modifying formulas, preventing circular reference errors.

Cons:

• Limited solution scope: Goal Seek may not handle complex scenarios where multiple variables interact, as it focuses on finding a single input value.

• Manual intervention required: Goal Seek relies on user assumptions and requires manual initiation and repetition when parameters or targets change.

Best Alternative

If you think the above method is good, you can choose WPS online web page to try editing, this is a convenient and quick hands-on method.

WPS Office is a free, lightweight, feature-rich office suite with a high level of interoperability.  It is a very popular Office Suite in the office & productivity category, capable of managing texts in Writer, Presentation, Spreadsheet, and PDF files.

To make an absolute reference in WPS Office Excel, follow these steps:

Step 1: To begin the installation of an absolute reference, open your Excel document.

Step 2: Following that, a cell-to-cell reference should be established; in this example, the cell designated as C2 will be the repository for the data acquired between cells B2 and E2.

Step 3: After completing the previous step, you will simply need to choose cell C2 and drag the required amount of cells. In this example, all that is required is a drag to cell C7.

Step 4: The results will be presented on the screen after this step is done.

FAQs about troubleshoot errors with absolute reference in Excel

How to check for #REF! error in cell reference?

Method #1:

Step 1: Press the F5 key on your keyboard to open the "Go To" dialog box.

Step 2: In the dialog box, click on the "Special" button.

Step 3: In the "Go To Special" menu that appears, select "Formulas" and then check the box next to "Errors".

Step 4: Click "OK", and it will automatically take you to every cell that has a #REF! error in it.

Method #2:

Step 1: Press Ctrl + F on your keyboard to open the Find dialog box.

Step 2: In the Find dialog box, type "#REF!" (without quotes) in the search field.

Step 3: Click on "Find All". This will highlight every cell that contains the #REF! error.

How to use the evaluate formula tool?

Step 1: Choose the cell containing the formula you wish to test.

Step 2: Select Evaluate Formula from the Formula Auditing group on the Ribbon's Formulas menu.

Step 3: To display the value of the underlined reference, click the 'Evaluate' button.

Step 4: If the underlined reference is part of another formula, you may display it by clicking the 'Step In' button. Then 'Step Out' to return to the previous screen and continue assessing.

How to correct a #VALUE! error?

To correct a #VALUE! error in Excel, you can try the following steps:

• Verify the data types: The #VALUE! error often occurs when there is a mismatch in data types within a formula. Ensure that the data being used in the formula is of the correct type. For example, if you are performing a mathematical calculation, ensure that the values are numbers and not text.

• Check for leading/trailing spaces: Leading or trailing spaces in cells can cause the #VALUE! error. Use the TRIM function to remove any extra spaces from the cells involved in the formula.

• Use proper syntax: Make sure that the formula syntax is correct, including using the appropriate functions and operators. Check for any missing or misplaced parentheses, quotation marks, or other syntax errors.

• Resolve circular references: Circular references, where a formula refers to its own cell, can cause the #VALUE! error. Identify and correct any circular references by either adjusting the formula or reevaluating the logic.

• Check referenced cells/ranges: Ensure that all cells or ranges referenced in the formula actually contain valid data. If a referenced cell or range contains an error, such as #DIV/0!, #N/A, or #NAME?, it can result in the #VALUE! error in dependent formulas.

• Use error handling functions: Consider using error handling functions like IFERROR or ISERROR to catch and handle specific error values, including the #VALUE! error. These functions can help display custom messages or alternative calculations when errors occur.

• Re-enter formulas: If none of the above steps resolve the issue, try re-entering the formula manually. It's possible that a typo or error occurred during the initial entry, leading to the #VALUE! error.

Summary

In conclusion, this article emphasized the need of utilizing absolute references in spreadsheet software to enable correct computations and data analysis. It included examples and instructions on how to use absolute references efficiently. It also highlighted the capabilities of WPS Office, a cost-effective and flexible office suite that interfaces easily with other applications.

WPS Office distinguishes itself as a dependable and affordable solution for advanced PDF editing, with seamless integration across word processing, spreadsheet, and presentation applications for simple collaboration.  It's an excellent alternative for low-cost productivity software that's appropriate for both professionals and businesses.