February 26, 2014

Consolidate Excel Worksheet Data Using Copy Autofill

How to consolidate multiple Excel worksheet data using the copy and autofill features in Excel

There are many interesting ways to consolidate data from multiple worksheets in Excel including the ‘consolidate’ feature but this copy and autofill feature is so simple that it can be used by anyone quickly and easily.
In two separate Excel worksheets we have the sales data of the years 2008 and 2009. Now we would like to consolidate the data of both the years in another sheet called 2_years_sales.
Click on the View tab.
Select Arrange All
From the pop-up “Arrange Windows” we select Vertical and click OK
Now you can view the workbook in two different vertical panes.
On the left-hand side we open the 2_years_sales worksheet. In this worksheet we click in cell B4 below Qtr1 and on the right of TV. Next we start writing our formula ‘=’ and then click inside cell B4 of worksheet sales_2008 and our formula in 2_years_sales looks like this ‘=sales_2008!B4’. We place a ‘+’ sign after the formula, go to worksheet sales_2009, click on cell B4 and our formula in sheet 2_years_sales becomes ‘=sales_2008!B4 + sales_2009!B4’. On pressing enter we can see a value of 480 for the total sales of TVs in years 2008 and 2009. Then we do an autofill downwards and to the right and we can confirm that sales data for all the items in various quarters for the years 2008 and 2009 has been consolidated in the worksheet 2_years_sales.

Further reading:
HOW TO: Consolidate Multiple Worksheets to a Single Sheet in Excel