In Excel, =$J:$J references an entire column on the current sheet while ='Sheet2'!$J:$J references that entire column on Sheet2 explicitly.

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

| improve this question | | | | |

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:


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


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:

enter image description here

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


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

| improve this answer | | | | |
  • This answers the question (so +1) but it might be wise to caution about summing many whole columns at once. At least put a sensible range in such as 'Sheet1:Sheet3'!$J$1:$J$9999 or a suitably high number without including 1 million rows unecessarily – AdamV Sep 5 '11 at 9:40
  • @AdamV I agree with you. Though the OP stated he wanted to call on the entire column, it's good practice to avoid doing so. – Ellesa Sep 5 '11 at 10:20
  • This answers my question. However, I am using the Conditional Formatting Rules Manager and it won't accept a 3D range and therefore does not solve my problem. But that's not your fault, so answer accepted. – Benjotron Sep 6 '11 at 14:30
  • Conditional formatting: Make the complex formula evaluate (e.g. =true) into a "hidden" (intermediate) cell, then do the formatting based on that cell. – Hannu Jul 2 '15 at 20:57
  • @Hannu You can even put the formula into a named range. Calling that from th CF will do the same without wasting a cell. – wbeard52 Jul 2 '15 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.