The exclamation point is a very versatile punctuation mark. In Excel, the exclamation is used as a delimiter between the sheet name and reference to a cell in that sheet. For example, if you want to reference cell A1, use the formula “A1!”.
Here we learn what an exclamation symbol means in excel formula with examples-
Excel’s ability to reference cells on any worksheet can be extremely helpful, especially when you need to reference specific values from one worksheet to another. To do this, begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you need to reference a Cell A1 on Sheet1, you would enter Sheet 1!A1.
But the most important thing about this formula is that if your worksheet name comes with a single space, you must use single quotation marks (‘ ‘). For example, your reference cell A1 and your worksheet name Annual Report, its cell reference would be ‘Annual Report’!A1.
Let’s do it practically-
Example to Reference Cell with a Worksheet Name
In our example, we share two sheets Menu Order and Catering Invoice. We will refer to an E14 cell with a calculated value between those two sheets. Here select the reference cell E14 on the Menu Order sheet.
And select our other worksheet, Catering Invoice.
The next step is to select a cell where we want the value to appear. Here we select cell B2 on the Catering Invoice sheet.
So, our formula format is Equal sign, the sheet name with the exclamation point, and the cell address. We have to type =’ Menu Order’!E14.
Now press enter to appear the value of the reference cell. And if the value of E14 will change on the Menu Order sheet, then every time, it will update automatically on the Catering Invoice sheet of the B2 cell.
You can use this formula if you want to use the same value on two different worksheets. It will save your time and effort by eliminating the need to rewrite the formula or copy data between worksheets.
