What does ! mean as an operator within a formula

[Tam]

I am copying formula which has an exclamation mark prefixing a cell reference
but I cannot determine what the operator calculates

[Leith Ross]

Hello Tam.

An exclamation mark follows a worksheet name in formula.

Example:
=Sales!$G$20

Sincerely,
Leith Ross

[bpeltzer]

The ! separates the worksheet name from the cell reference. The more general
form:
=[Book2]Sheet1!$A$1
The workbook name is inside the brackets, followed by the worksheet name,
the !, then the cell address.

"Tam" wrote:

> I am copying formula which has an exclamation mark prefixing a cell reference
> but I cannot determine what the operator calculates

[Tam]

"bpeltzer" wrote:

> The ! separates the worksheet name from the cell reference. The more general
> form:
> =[Book2]Sheet1!$A$1
> The workbook name is inside the brackets, followed by the worksheet name,
> the !, then the cell address.
>
> "Tam" wrote:
>
> > I am copying formula which has an exclamation mark prefixing a cell reference
> > but I cannot determine what the operator calculates

[Ricyteach]

The replies to this ancient question (this thread ranks high on google searches) leave out very important information, soooo:

It is true that most of the time, when entering a formula into a spreadsheet the exclamation mark (!) serves as a separator between an (optional) book and sheet name and the formula, as shown here:
=[Book2]Sheet1!$A$1

However, you can also enter an exclamation mark in front of the a formula with no book or sheet name at all when entering it as a Named Range in the Name Manager:
=!$A$1

OR:
=!A1

(Note that you cannot do this on a worksheet- only when entering a formula in the Name Manager, or in VBA.)

What does this do?

It means that the formula is calculated relative to the active sheet, rather than a named sheet. This can be useful.

One way it is useful is by preventing you from having to enter the same formula multiple times for multiples sheets. For example, let's say you have the named range CircleArea with the formula:
=PI()*Sheet1!A1^2

In this case, Sheet1!A1 is the radius of a circle. So let's give Sheet1!A1 the name Radius and change the CircleArea named formula to:
=A1 <--- Radius scoped to workbook
=PI()*Radius^2 <--- CircleArea scoped to workbook

Much more readable.

But if we made both of those names workbook scoped formulas, that means we can't have a different Radius and a different CircleArea on Sheet2... So we should delete the workbook scoped Radius and CircleArea and replace them with ones to be scoped to Sheet1 only, and then add a Radius and Circle Area both scoped to Sheet2 as well.

But now we have to have TWO ranged named Radius (one for each sheet), and TWO CircleArea named ranges as well! One for Sheet1, and one for Sheet2. And what is worse, the formulas are IDENTICAL:
=A1 <--- Radius scoped to Sheet1
=A1 <--- Radius scoped to Sheet2
=PI()*Radius^2 <--- CircleArea scoped to Sheet1
=PI()*Radius^2 <--- CircleArea scoped to Sheet2

And if we wanted to add a third or more sheets, these would get duplicated every single time. What a mess.

And even worse, if for some reason we later decided we wanted to change the name of the CircleArea named range to be CircleAreaInt and the formula to be rounded to an integer, we'd have to change EVERY SINGLE ONE:
=INT(PI()*Radius^2) <--- CircleAreaInt scoped to Sheet1
=INT(PI()*Radius^2) <--- CircleAreaInt scoped to Sheet2

Blech!

The exclamation point can save us ALL of this frustration. If we simply do this:
=!A1 <--- relative Radius scoped to workbook
=PI()*!Radius^2 <--- relative CircleArea scoped to workbook

...the named formulas become relative to the sheet. With this simple change, each worksheet will have its own value for Radius (depending upon what value appears in A1) and its own resulting value for CircleArea.

[sam4739]

The replies to this ancient question (this thread ranks high on google searches) leave out very important information, soooo:

It is true that most of the time, when entering a formula into a spreadsheet the exclamation mark (!) serves as a separator between an (optional) book and sheet name and the formula, as shown here:
=[Book2]Sheet1!$A$1

However, you can also enter an exclamation mark in front of the a formula with no book or sheet name at all when entering it as a Named Range in the Name Manager:
=!$A$1

OR:
=!A1

(Note that you cannot do this on a worksheet- only when entering a formula in the Name Manager, or in VBA.)

What does this do?

It means that the formula is calculated relative to the active sheet, rather than a named sheet. This can be useful.

One way it is useful is by preventing you from having to enter the same formula multiple times for multiples sheets. For example, let's say you have the named range CircleArea with the formula:
=PI()*Sheet1!A1^2

In this case, Sheet1!A1 is the radius of a circle. So let's give Sheet1!A1 the name Radius and change the CircleArea named formula to:
=A1 <--- Radius scoped to workbook
=PI()*Radius^2 <--- CircleArea scoped to workbook

Much more readable.

But if we made both of those names workbook scoped formulas, that means we can't have a different Radius and a different CircleArea on Sheet2... So we should delete the workbook scoped Radius and CircleArea and replace them with ones to be scoped to Sheet1 only, and then add a Radius and Circle Area both scoped to Sheet2 as well.

But now we have to have TWO ranged named Radius (one for each sheet), and TWO CircleArea named ranges as well! One for Sheet1, and one for Sheet2. And what is worse, the formulas are IDENTICAL:
=A1 <--- Radius scoped to Sheet1
=A1 <--- Radius scoped to Sheet2
=PI()*Radius^2 <--- CircleArea scoped to Sheet1
=PI()*Radius^2 <--- CircleArea scoped to Sheet2

And if we wanted to add a third or more sheets, these would get duplicated every single time. What a mess.

And even worse, if for some reason we later decided we wanted to change the name of the CircleArea named range to be CircleAreaInt and the formula to be rounded to an integer, we'd have to change EVERY SINGLE ONE:
=INT(PI()*Radius^2) <--- CircleAreaInt scoped to Sheet1
=INT(PI()*Radius^2) <--- CircleAreaInt scoped to Sheet2

Blech!

The exclamation point can save us ALL of this frustration. If we simply do this:
=!A1 <--- relative Radius scoped to workbook
=PI()*!Radius^2 <--- relative CircleArea scoped to workbook

...the named formulas become relative to the sheet. With this simple change, each worksheet will have its own value for Radius (depending upon what value appears in A1) and its own resulting value for CircleArea.

I created an account simply to just to comment on your response and say this is probably one of the best explanations of this I have ever read, and after several years of being confused by the purpose of the "!" I finally understand what it can be used for.

[Ricyteach]

Happy it helped someone! :D