Excel 2016 consolidating cells

Back in the Consolidate dialog, click Add to add this first set of data to the consolidation dialog.

Continue and add the second and each additional set of data to the References section of the dialog.

So the number of rows or the number of columns or the number of rows and columns might be different from sheet to sheet.

For example, if we had opened a new office in a September, we would need to expand the range selected in September to account for the additional rows of data for that new office and we would then continue to do that for every worksheet that contained those extra rows.

If you chose "Create links to source data" then the data is linked to the original cell containing it.

To see this, click in a cell with the data (not a sum function) and you will see a reference to the sheet and cell that contains that data.

As this data is linked you can use the Trace Precedents option to go to the cell containing that data.

Choose Data Consolidate to view the Consolidate dialog.

Here you will select the function to analyze your data and the references or ranges that you want to consolidate.

You can name your ranges before you start the Consolidation process.

To do this, select a range and type a name for it into the Name box at the far left of the Formula bar.

Leave a Reply