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.

image

image

image

We create a new worksheet for total sales.

image

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

image

The Consolidate dialogue will be showed.

image

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

image

The we click the Add button.

image

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

image

Click OK button.

image

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.

image

Let’s update the Asia Sales worksheet.

image

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

image

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!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

One Response to Consolidate Data in Excel

  1. Pingback: Is Excel our only option? « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: