Writing formulas in Excel is a fundamental task performed millions of times per day by people all over the world. Unfortunately, many of those formulas are longer and thus more complex than necessary.
When you are referencing cells from different sheets, Excel must recognize when a cell is on a different sheet as the formula being composed. Consider the following cell reference syntax:
- Referencing a cell on the same sheet as the formula (same workbook):
=A1
- Referencing a cell on a different sheet than the formula (same workbook):
=Sheet2!A1
Sheet names are separated from cell names with an exclamation point.
- Referencing a cell in a different workbook:
=[Workbook2.xlsx]Sheet2!A1
Workbook names are separated from sheet names by enclosing the workbook name in brackets.
Why so long?
During the formula writing process, when a user switches between sheets and clicks on a cell, Excel uses the Sheet!Cell reference model. Where the formulas get unnecessarily complex is when the user switches back to the sheet holding the formula and selects a cell from that same sheet. Excel will add the sheet name reference to the cell. This is overkill since the cell address is all that is necessary. When a cell is missing the sheet name in the reference, Excel assumes that the cell is on the same sheet as the formula.
The below sample has a tax rate on the Summary sheet and the sale amount on the Tennessee sheet. If a formula is created on the Summary sheet that multiplies the sale amount by the tax rate, the formula will appear as follows.
Trick to Eliminate Sheet Reference
Select the VIEW tab/ribbon and in the Window group select New Window
This will create a second window that displays a duplicate view of Excel. From the VIEW tab/ribbon select Arrange All.
![SNAGHTML15b8bfc[1]](https://www.bcti.com/wp-content/uploads/2018/01/SNAGHTML15b8bfc1.png "SNAGHTML15b8bfc[1]")
In the Arrange Windows dialog box select the desired arrangement scheme (vertical seems to work well on modern widescreen monitors.) Consider selecting the “Windows of active workbooks” option; this will prevent all of your open Excel files from being rearranged.
Once arranged, select the formula’s sheet in one window and the sheet being referenced in another window.
When composing the formula, instead of switching sheets to click on cells, switch windows. Switching windows will not produce the redundant sheet reference for cells selected on the same sheet as the formula.
