Reduce Excel Formula Length

Remove redundant references

When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.

These references are redundant and do not need to be in the formula. Excel inserts the current sheet name whenever you return from navigating to another sheet whilst building a formula.

If you never leave the sheet Excel will not insert the current sheet name. This provides a clue that you don’t need the sheet name in the reference for the formula to work.

As an example, I have two sheets. One is called Data and the other State Report. I am creating a formula in the State Report sheet that refers to the Data sheet. The layout of the Data sheet is shown below.

As I create the SUMIFS formula you can see that when I return to the State Report sheet Excel inserts the name into the formula. See image below.

You can completely remove the reference (highlighted in yellow above)

'State Report'!

from the formula and the formula will still work.

I have included in the image below the before and after formulas, so you can see the difference in the formula length.

Note: Excel inserts apostrophes around any sheet names that have spaces in them. Which makes the formulas even longer. The longer the sheet name, the longer the formula.

To get around the problem you can just type in the current sheet formula reference, which saves you having to edit it later.

Using a short sheet name like Data also helps keep the formula shorter.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

2 thoughts on “Reduce Excel Formula Length