Create a reference to the same cell range on multiple worksheets (2024)

FAQs

How to create a reference to the same cell range on multiple worksheets? ›

Click the tab for the first worksheet that you want to reference. Hold down the Shift key then click the tab for the last worksheet that you want to reference. Select the cell or range of cells that you want to reference. Complete the formula, and then press Enter.

Go to Data > Consolidate. In the Function box, select the function that you want Excel to use to consolidate the data. To indicate where the labels are located in the source ranges, select the check boxes under Use labels in: either the Top row, the Left column, or both. In each source sheet, select your data.

First, click on Formulas and then on Define Name. In the pop−up, enter the name "Range," then set scope to workbook," select all the sheets in the reference, and click OK to complete the task. Formula > Define name > Name > Scope > Refers to > Ok.

You can refer to cells that are on other worksheets in the same workbook by prepending the name of the worksheet followed by an exclamation point (!) to the start of the cell reference.

There may be times when you do not want a cell reference to change when copying or filling cells. You can use an absolute reference to keep a row and/or column constant in the formula. An absolute reference is designated in the formula by the addition of a dollar sign ($).

Select the cell or cells where you want to create the external reference. Type = (equal sign). Switch to the source workbook, and then click the worksheet that contains the cells that you want to link. Press F3, select the name that you want to link to and press Enter.

If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.

Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.

How do you reference a named range in another worksheet? ›

WorkSHEET Specific Named Range

Refer to this named range by just the name in the same worksheet, but from another worksheet you must use the worksheet name including "!" the name of the range (example: the range "Name" "=Sheet1! Name").

Select the cell or cells where you want to create the external reference. Type = (equal sign). Switch to the source workbook, and then click the worksheet that contains the cells that you want to link. Press F3, select the name that you want to link to and press Enter.

If you need to get the sum across multiple worksheets, you can use one of the less-known Excel feature called 3D referencing. With 3D referencing, you can refer to the same cell in multiple worksheets, and can also use this in formulas such as the SUM or AVERAGE, or COUNT.

Cross-Referencing Two Excel Sheets:

Select the cell from the first sheet you want to cross-reference. Type the “='” sign followed by the second sheet name and the cell you want to reference. For example, if you want to reference the cell B2 from Sheet2, type “='Sheet2′! B2”.