An expanding reference is a reference type in Excel and Google worksheets that extends the range of a cell when a formula is copied to the cell below or to the right. In this article, we will explain how to use extended references in Microsoft Excel or Google worksheets with specific examples.
Table of Contents
Expanding reference
Extended reference is mainly achieved through a mixture of reference methods, including absolute and relative references. When you want to position a cell region, you can set the first cell as an absolute reference, set the last cell as a relative reference, so that you can constitute an extending reference, for example: $A$1:A1.
EXAMPLE 1
When you want to add up data from the same column, you can use Expanding reference to do it in Excel or google sheets. In this example, the data that needs to be added up is the cell range A1:A6, and the following steps are used to achieve the summation by expanding the reference.
Step1: Enter the following summation formula in cell B1 and press Enter to apply the formula.
=SUM($A$1:A1)
Where, $A$1:A1 is the Expanding Reference cell range.
Step2: In cell B1, drag the AutoFill Handle until cell B6
Step3: You can click on any cell in the B1:B6 area to see its summation formula.
EXAMPLE 2
Suppose you have a list of names and you want to mark the number of occurrences of each name. For example, the first occurrence of the name TOM is marked as 1, the second occurrence is marked as 2, and so on. This can be easily achieved by using the COUNTIF function in combination with the Expanding Reference method.
The formula is as follows:
=COUNTIF($A$1:$A1,A1)
Video: Excel/Google Sheets: Expanding Reference
SAMPLE Files
Below are sample files in Microsoft Excel and Google sheets that you can download for reference if you wish.
Google Sheets Sample file: click here.