Normally, an exclamation point is a character used to express emphasis or surprise, yet in Excel, it’s used for referencing datasets from other worksheets.
In this guide, we’ll explore what exactly the exclamation point means in Microsoft Excel and how you can use it to improve your skillset and make your spreadsheet tasks easier.
Exclamation Point (!) in Excel
In Excel, an exclamation point is used to differentiate a sheet from a particular cell reference that’s being used in a formula.
In most cases, you would see the exclamation mark is used before cell references and after sheet names in formulas.
This delimiter (!) is employed when the formula from one sheet uses a cell reference from a separate spreadsheet.
Technically speaking, when you use the exclamation point, you’re instructing Excel to collect the reference information from a separate sheet.
For instance, imagine you want to sum all the numbers in cells A1 and B1 on Sheet1.
You’d need to enter the following formula into our C1 cell on Sheet1.
=SUM(Sheet1!A1:B1)
But, if you want to add up the numbers from cells A1 and B1 from two separate sheets, then you’d need to enter the following formula into cell C1 from Sheet1 instead:
=Sheet2!A1+Sheet2!B1
The exclamation point located after Sheet2 in the formula instructs Excel to reference the data on Sheet2 to complete the formula.
Using The Exclamation Mark in Excel to Reference Ranges
You can also use the exclamation point to reference a range of cells on a different sheet.
For example, if you want to sum the entire range of numbers in cells A1 through B4 on Sheet2, you’d need to enter the following formula into your C1 cell on Sheet1:
=SUM(Sheet2!A1:B4)
The exclamation point instructs Excel to use the data in ranges A1:B4 on Sheet2 to complete the formula.
Using The Exclamation Mark in Excel to Reference Named Ranges
You can also use the exclamation point in Excel to reference named ranges on a separate sheet.
So, for instance, if you’ve created a range on Sheet2 named “Total”, then you can reference this range in a formula by employing the following in cell C1 on Sheet1:
Sheet2!Total
The exclamation point instructs Excel to reference the data from the named range “Total” in Sheet2.
Using The Exclamation Mark in Excel to Reference Workbooks
Lastly, you can also use the exclamation point in Excel to reference different workbooks.
So for this example, imagine you want to add up the numbers located in cells A1 and B1 on Sheet1 of your other workbook called “Test.”
You’d need to enter the following formula into your cell C1 on Sheet1 of your existing workbook:
=SUM('[Test.xlsx]Sheet1'!A1:B1)
In this case, by using the exclamation point, you’re instructing Excel to reference the data from Sheet1 in “Test.xlsx.”
The exclamation point in Excel is such a helpful tool, which will allow you to easily and quickly collate and reference data from different workbooks, sheets, and other applications.
By following the steps above and with a bit of practice, you can become an expert too.
Other Excel articles you may also like:
if you want to make sure in a vlookup that the values match the code on dont go down by the row number how do you do that