Know 3D reference system
3D referencing system is a little known feature of Excel and can immensely helpful if you have to perform simple mathematical operations on data from multiple worksheet. Assume you have tens of worksheets and want to add numbers from particular range of cells.
Following is an example with three worksheets and we had to sum sales of January month:
For this I used this formula:
=SUM(‘A:C’!B2)
A:C reference means consider all worksheets between worksheet named “A” and “C” i.e. A, B and C worksheets are considered.
“!” sign is used to refer worksheets and B2 is a cell reference. So Excel is asked to sum B2 cell of all three worksheets.
Bonus: Placement of tab matters!
As worksheet B falls between A and C worksheets, Excel considered B in A:C reference as well. Had the worksheet B not in the middle of A and C, it could have been left:
As you can see when I moved the worksheet B to the right and now A and C worksheets are together, the sum total has reduced from 1110 to 1100.
