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!

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: Logo

You are commenting using your 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: