Reference a range of cells on all sheets in Excel

Question

In Excel, =$J:$J references a column, and ='Sheet2'!$J:$J will reference that column on Sheet2 explicitly.

Is there a way to reference column J and all sheets without manually typing all the sheet names?

Answer 1

You can try using 3D cell references.

1. Select the cell in which you'd like to place the reference. Type =.
2. While holding Shift, click the first sheet and then click the last sheet.

3. Select the column that you want to reference. You'll end up with something like this:

   ='Sheet1:Sheet3'!J:J

For example, if you want to sum the contents of column J on all three sheets you can use the formula:

=SUM('Sheet1:Sheet3'!J:J)

Creating a Name for your 3D Reference

This will make it easier for you to reference the columns.

1. Go to Formulas > Defined Names
2. Type your preferred name. In the example below, I used "myrange".
3. Delete the contents of the Refers to box and type =.
4. While holding Shift, click the first sheet that you want to reference, and then click the last sheet.
5. Select the column that you want to reference. In your case, column J. You'll get something like this:

To sum the contents of column J in Sheet1 through Sheet3, you just have to use this formula:

=SUM(myrange)

Not all Excel formulas can accept 3D references. Here are the ones that do: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA