Blog

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.

Excel Tips: Consolidating Data

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:

=SUM(‘*’!B1)

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.

Sarah Verge's avatar

Author

Sarah Verge

Sarah is an accountant with a background in IT. In her spare time, she advises Sistema England, a music charity and is an ISPA committee member. She is a regular theatre and concert-goer: jukebox musicals are her guilty pleasure.

Comment

Keep in touch with the latest news