Show All

Refer to the same cell or range on multiple sheets

A reference that refers to the same cell or range on multiple sheets is called a 3-D reference.

1. Click the cell where you want to enter the function.
2. Type = (equal sign), enter the name of the function, and then type an opening parenthesis.

   Functions that can be used in a 3-D reference

   SUM - adds numbers

   AVERAGE - calculates average (arithmetic mean) of numbers

   AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and logicals

   COUNT - counts cells that contain numbers

   COUNTA - counts cells that are not empty

   MAX - finds largest value in a set of values

   MAXA - finds largest value in a set of values; includes text and logicals

   MIN - finds smallest value in a set of values

   MINA - finds smallest value in a set of values; includes text and logicals

   PRODUCT - multiplies numbers

   STDEV - calculates standard deviation based on a sample

   STDEVA - calculates standard deviation based on a sample; includes text and logicals

   STDEVP - calculates standard deviation of an entire population

   STDEVPA - calculates standard deviation of an entire population; includes text and logicals

   VAR - estimates variance based on a sample

   VARA - estimates variance based on a sample; includes text and logicals

   VARP - calculates variance for an entire population

   VARPA - calculates variance for an entire population; includes text and logicals

3. Click the tab for the first worksheet to be referenced.
4. Hold down SHIFT and click the tab for the last worksheet to be referenced.
5. Select the cell or range of cells to be referenced.
6. Complete the formula, and press ENTER.