Consolidating databases excel

To do this select an empty sheet in the workbook - add a new one if necessary - and click in it.

Choose Data Consolidate to view the Consolidate dialog.

consolidating databases excel-85

One of the benefits of using this Consolidation tool is that the data does not need to be in a single workbook.

Instead, if you have a series of workbooks each of which contains a similar arrangement of data for different periods of time, you can consolidate all these using this tool.

The ranges you consolidate do not necessarily have to be of the same size in each worksheet.

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

Double click to open the Go To dialog - the cell reference will be automatically listed there. You can format the data in the summary worksheet as you would in any regular Excel file.

You'll find that the second column of data shows the name of the workbook that contains the data.

There are many ways to consolidate data in Microsoft Excel.

You can use the Table feature and its associated filters to filter a list so it shows only those items you are interested in seeing and you can create Pivot Tables to look at your data in different ways.

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

To do this, click in the cell that contains the data you want to view. Because the precedent cell is on a different worksheet hold your mouse over the black arrow head till the mouse cursor turns into a hollow white arrow.

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

Tags: , ,