Excel 2016 consolidating
Doing so creates an Excel table that contains a list of all of your data.
So, we have Product A for the year 2013, the value; Product A for the year 2014 with a value, and so on.
In this tutorial, we will use the consolidating sample file. The workbook contains four worksheets, and all those worksheets are data collections that you can use to combine into a Pivot Table.
On the Team1 worksheet, you can see that there is a rectangular region of data; it starts here in cell B3, and runs down the cell F7.
You can also Auto Fit the width for several columns at the same time.
With numerical data, the cell will display pound signs (#######) if the column is too narrow.It is important to make sure that the insertion point is flashing inside the Range box. Datasets are the same size, and the exact same shape. The next task is to specify how many page fields we want. Click Next, and we can select where to create the new pivot table. Doing so creates a pivot table with the values that are all consolidated into a pivot table from our four worksheets. To do that, click a value in the Row Labels area, and then on the Analyze contextual tab of the ribbon, which is already selected.Choose the collapse dialog button, and select the first set of cells. If you look over in the Pivot Table Fields pane at the right corner of the main window, you’ll see that we have three fields. Value is ok, further explanation is not necessarry. Now we will modify the value in the Active Field box.It’s very important that your data be in a perfect rectangle. If we were to have Product in cell B2, and Year in cell C2, the data values would not be perfectly rectangular.In this case the Pivot table consolidation technique will not work.