Now you can identify the ranges that you want to consolidate. Now we have to do the same thing for the other three worksheets. Click the Team2 sheet tab, select cells B3 to F7, expand the dialog box, and click Add.To do that, click the collapse dialog button at the right side of the Range box. Finally repeat these steps for Team 3 and Team4 worksheet.Shown as follows: Figure 2 2013 Data worksheet Step 3 Click the Total Sales worksheet tab, and input row and column labels. The Consolidate dialogue box will open and show as below: Figure 4 The Consolidate dialogue box Then do the following: Figure 5 Add the 2012 Data cells area Step 6 Click Add.
We have to use a smart keyboard shortcut to display it.OK, go to the Team1 worksheet and now we can open the tool named the Pivot Table Wizard.Our goal to consolidate multiple worksheets into a pivot table.Further information: We need to make sure that there are no blank cells anywhere in our data table.This data summarizes yearly sales for 4 years (from 2013 to 2016); the years are along the top and the row labels are along the side. Team1, Team2, Team3 and Team4 tabs contain similar data.