Excel Tips: Consolidating Data
Sarah Verge - June 04, 2014
Pivot tables are brilliant for slicing and dicing data, but sometimes a simpler approach works just fine.
Suppose you have an Excel workbook with four worksheets, each holding the sales figure for a quarter in cell B1. The worksheets are named Quarter 1, Quarter 2, Quarter 3 and Quarter 4.
You then create a worksheet called ‘Year’ that adds the four quarters together to give you the sales for the year.
On your ‘Year’ worksheet, you could enter – manually or by selecting the cells – the formula:
+’Quarter 1′!B1+’Quarter 2′!B1+’Quarter 3′!B1+’Quarter 4′!B1
A better approach would be to enter this formula on your ‘Year’ Worksheet:
=SUM(‘Quarter 1:Quarter 4’!B1)
or, better still, enter:
This isn’t much quicker when you’re working with four quarters, but with 12 months or 52 weeks, this would be a real timesaver.
Note that, if you add an extra worksheet before Quarter 1 or after Quarter 4, this won’t be picked up in the formula, but if you slot one in between Quarter 1 and Quarter 4 it will be.