Consolidate Data with 3D References

3D references provide a simplified way of aggregating data by referring to the same cell or range across multiple worksheets.

Say you had monthly sales figures stored in separate worksheets and wanted to summarise them. You might be tempted to construct a formula like the ones in Summary — Method 1. These are unnecessarily long-winded though, as there is no need to cycle through each worksheet to reference a cell if the format is consistent.

Summary — Method 2 shows a streamlined approach that involves referencing a single cell and a starting and ending worksheet.

Here are the full steps:

1. Type = (equals) followed by a function such as SUM (other ones like AVERAGE, COUNT, MAX and MIN also work) and then an opening parenthesis.
2. Click on the first tab you want to set as the starting point.
3. Select the cell or range to reference.
4. Hold Shift and select the tab you want to set as the endpoint.
5. Press Return to confirm the formula.

Other Information

• If data is stored in official Excel Tables, structured references will automatically convert to cell references.
• Inserting a worksheet tab between the start and endpoints will include it in the reference.
• Moving a worksheet so it’s outside the start and endpoints will exclude it from the reference.
• Deleting the start or endpoint will update the reference so the next tab in line becomes the beginning or finish.