Excel/Google Sheets: Expanding Reference

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.

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.

 Excel/Google Sheets: Expanding Reference


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.


Where, $A$1:A1 is the Expanding Reference cell range.

 Excel/Google Sheets: Expanding Reference

Step2: In cell B1, drag the AutoFill Handle until cell B6

 Excel/Google Sheets: Expanding Reference

Step3: You can click on any cell in the B1:B6 area to see its summation formula.

 Excel/Google Sheets: Expanding Reference


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:

 Excel/Google Sheets: Expanding Reference

Video: Excel/Google Sheets: Expanding Reference


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.

Related Posts

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

Excel/Google Sheets: Mixed Reference

In Microsoft Excel Spreadsheet or Google sheets, there is another cell reference, mixed references, where part of the reference is absolute, part of the relative. This article will describe how to use mixed references through specific examples. Mixed Reference When ...

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute ...

Conditional formatting based on another column in Google Sheets or Excel

In Microsoft Excel Spreadsheet or google sheets, conditional formatting is used to highlight any cell based on a predefined condition and the value of those cells. In the previous article, we described how to format a specific column or cell ...