How to Consolidate Data by Position in Spreadsheets 2013

When you want to consolidate data by position, make sure that each range of data has the same layout. That means each range of data to be consolidated must be in the corresponding position on each separate worksheet. For example, in theData Example workbook, the three worksheets are 2011 Data,2012 Data and2013 data. We want to consolidate data in the these three worksheets and put the result in the 'Total Data' worksheet. Operation steps are as follows:

Step 1 Click the 2011 Data worksheet tab, and input the data. The other two worksheets have the similar layout of data.

Step 2 Add a new worksheet to collect the consolidated data. Click the Totalworksheet tab, and input row and column labels. Shown as follows:

Step 3 Select B3:E8 range in Total worksheet. Go Data tab > Consolidate. TheConsolidate dialog pops up.

Step 4 In Consolidate dialog, choose Function as Sum. Enter the range path in theReference box. Or click the source worksheet, and select the range of data to be consolidated. Then the range path of the original data will be displayed automatically in the Reference box. You need to add each one separately - select one cell range and press Add in Consolidate dialog box.

Step 5 Press OK to finish. You will find that the data is successfully combined to the Total worksheet.