Consolidate Data in Excel

Do you have to spend hours to manually consolidate Excel worksheets?  If you do, then you may be glad to know that Excel provides a simple tool to give you the freedom from repetitive copy and paste processes.

Consolidate Data

Let’s say we receive 3 worksheets (it can be 30 or more) everyday that contain sales data.

We create a new worksheet for total sales.

In the total sales worksheet, we click the Consolidate button in the Data tab.

The Consolidate dialogue will be showed.

We enter (or select) the Asia Sales range in the Reference:

The we click the Add button.

We repeat these 2 steps for America and Europe Sales worksheets.

Click OK button.

Now, we have the total sales.  Very simple processes!  However, we still have to repeat these processes everyday because it won’t automatically pick up new changes.  Don’t worry, it can be automatically updated with a single mouse click.

Automatically Update

We open the Consolidate dialogue again by clicking the Consolidate button, then we enable Create Links to source data.

Let’s update the Asia Sales worksheet.

When we switch back to Total Sales worksheet, we see the number is already updated.

From now on, you don’t have to manually copy and paste the data from different worksheets.  All you have to do is to make sure you receive the worksheets and save them into the designated folder, then Excel would automatically do the consolidation for you.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!