Excel Absolute ReferencingMicrosoft Excel, also known as Excel, is an extremely powerful spreadsheet program developed by the Microsoft Company. Using Excel, users access multiple worksheets to record large amounts of data within one or more cells. Additionally, Excel has a different range of builtin functions to help us apply various operations or calculations on recorded data to achieve financial analysis or desired output. Although most formulas can work with numerical values directly assigned to them, we often use cell references to access desired values that we want to supply in specific formulas. Using cell references is useful because the formula automatically updates the corresponding output in the resulting cells accordingly if we change the values recorded. There are mainly three types of cell references, namely relative, absolute and mixed. In this tutorial, we discuss a brief introduction to Excel Absolute Referencing. We also discuss the stepbystep procedures to create or use an absolute reference in our Excel worksheet with the help of relevant examples. Before we look at Absolute Reference in Excel, let us first introduce Excel Cell Reference. What is a Cell Reference?An Excel cell reference refers to the address or name of the corresponding cell or a range of multiple cells. It usually consists of the corresponding column name followed by the specific row number. A cell reference tells Excel formulas where to look for the data/value used in the formulas. In Excel, we can refer to cells in the same sheet, another sheet, another workbook, and other supported programs. What is an Absolute Reference in Excel?By default, Excel uses relative references, which means that the cell reference changes based on the relative position of the row and column when it is copied to another cell or range. However, there may be cases when we may need to fix the addresses of some cells to get specific outputs. After fixing the cell references, we can ensure that they do not change even after copying them to another location. This is where Excel Absolute Referencing comes into play. By definition, "Excel Absolute Reference refers to a' locked' reference so that the address of its corresponding row and column does not change when copied". An absolute reference in Excel represents an actual fixed location or address on the worksheet. So, when we want to fix the position of the desired cell in an Excel formula so that its position remains constant while copying it to another location, we make the corresponding cell in the formula absolute. When do we need to use Absolute Cell References in Excel?When working in Excel, we come across a wide range of usecases where we have to use absolute references to get the desired results, regardless of the field or industry we are working on. Some of the common cases when we commonly use absolute references for efficient calculation and reporting are as follows:
How to create or make an Absolute Cell Reference?We should include the dollar sign ($) before the column name and row number for desired cell reference to make it absolute. Depending upon the usecases, we may need either to fix an individual cell or an entire range. Let us discuss both the cases: Creating/ Making a cell AbsoluteWhen we need to make a single cell an absolute cell, we must insert a dollar sign before the column name and row number. This will change the relative reference into an absolute reference. For example, an absolute reference to cell A1 will be as below: =$A$1 If we copy the cell to the other cell below, the absolute reference remains the same and gives the same output. Creating/ Making a range AbsoluteWhen we need to make an entire range absolute, we must add a dollar sign to each cell reference in the range. For example, if we have a range A1:B7 to be used in the form of an absolute reference. We need to include the dollar sign in the following way: =$A$1:$B$7 That way, we fix the column letters and row numbers for the entire range. Note: Instead of manually adding the dollar sign ($), we can also press the F4 function key once after placing the cursor in the specific cell reference in the formula bar. This will immediately add a dollar sign to a particular cell reference, making it absolute. Pressing the F4 function key multiple times will toggle between other types of cell references.How to use Absolute Referencing in Excel?We need to perform the below steps to use the absolute referencing in Excel properly:
Let us now understand the concept of absolute reference better with the help of the following examples: Absolute Referencing in Excel: Example 1Consider the following sheet as an example data set where we have prices of different items in USD ($). We need to convert these prices from USD to INR in a column (C) next to it. Suppose 1 USD equals approximately 75 INR, recorded in cell B2 in the sheet. In that case, we need to multiply the recorded prices of different items with cell B2. So, we need to fix cell B2 in the multiplication formula while keeping all other cells relative or default. The steps to convert or change the recorded prices from USD to INR by using the multiplication formula are listed below:
Absolute Referencing in Excel: Example 2Consider the following sheet as an example data set where we list some grocery items with their prices and purchased quantities. Our example sheet shows that we have the fixed GST (Goods and Service Tax Rate) rate of 5% for each item. For calculating the Net Prices (Total Price including the GST), we first need to calculate the GST price for each item and then include this in the respective total prices. The steps to calculate the total prices, including the GST for each item, are listed below:
An alternate to Excel Absolute Referencing: Named RangeIn Excel, a named range is an alternative to an absolute reference. We can name any desired cell or a range accordingly and use it in our formulas. This way, we can make our formulas easier to understand. Depending on our requirements, we can create multiple named ranges in a single sheet. For this, we can use the Name Manager and create, edit or delete the desired named range accordingly. For example, suppose we have salary data of some employees, and we want to include the additional bonus of 1500 INR with each respective salary. The data is structured in an Excel sheet in the following way: We need to fix cell C1 in the formulas in the above sheet. So, we name this cell as a BONUS using the Name Manager (Formulas > Name Manager). After that, we select the resultant cell C4 and insert the formula (Salary + Bonus) to calculate the total salary for the first employee. After pressing the Enter key, we get the first employee's desired result (total salary). Now, we can drag or copypaste the formula in other cells below C4 to get the total salary of other employees. Since we have named the cell C1 as BONUS, it does not change automatically in other resultant cells (C5, C6, C7, and C8). Similarly, we can also assign a name for the desired range and use it in the corresponding formulas. This concept works similarly to the Excel Absolute Referencing. Important Points to Remember
Next TopicInstruction while typing data in Excel
