How to Consolidate Data in Excel 2013 - 2003

Note. The updated version of this tutorial can be found here: How to consolidate data from multiple worksheets into a single sheet

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 PivotTables to look at your data in different ways. You can also consolidate large amounts of data into a summary form which can show you as much or as little of your data as you want to see. In this post, I'll show you how to use the Consolidate data feature in Excel to take data from a series of worksheets - or workbooks - and summarize them into a single worksheet.

Work with multiple data sets

Consider the situation where you have data for twelve months of your business stored one month per sheet in an Excel workbook. If each sheet contains that month's data and if it is laid out in the same arrangement of columns and rows then you can summarize this full year of data into a single sheet using the Consolidate tool.

With a series of data like one sheet for every month, you can consolidate it into a summary worksheet  using the Consolidate tool.

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. Here you will select the function to analyze your data and the references or ranges that you want to consolidate. For our data we want to add the values so we'll set the Function to Sum. However we could have chosen any of these 11 functions: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var and Varp.

For the summary to total all the data, select Sum as the function.

Click in the Reference area and click the Expand/Collapse button to its immediate right. Select over the first data range to consolidate - to do this you will need to click the Sheet tab and then drag over the data including the row and column headings.
Select the first sheet with data to consolidate and drag over the data area including row and column headings.

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. Provided you have the same range of data on each of your worksheets and they are all in the same position all you will need to do is to click the Worksheet tab and the range will already be selected for you so it's not as big a job as it might seem.

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. 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 have extra rows or columns in subsequent worksheets you can select a different range to consolidate on these sheets.

Top Tip

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. If you name each range then, when you create the consolidation, place your cursor in the Reference field, press F3 and then choose the range from the list in the Paste Name dialog. This way you can give each range a descriptive name and you won't need to remember later what you had entered in Sheet1, cells A3:F40.

You'll see that the worksheets appear in the All References panel in alphabetical order. Check that you have all the references there before you continue. Select the Use labels in: Top Row and Left Column checkboxes. Select the Create links to source data check box and click Ok.

Add references to each of the ranges with data to consolidate

The consolidated data

When you click Ok, Excel summarizes all the data into your new sheet. You will see grouping tools down the left of the screen which you can use to display and hide the data.

The consolidation shows a summary of the data you had selected with Grouping buttons down the left of the screen.

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.

Choose to link to the source data and each cell will contain a formula linking back to the original data.

If you did not choose "Create links to source data" the consolidation is simply a summary of the data without any details so there is no grouping applied and the results are summarized only.

Without linking to the source data you get a summary with no indication of where the data came from.

Top Tip

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. Click Formulas tab > Trace Precedents. 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. Double click to open the Go To dialog - the cell reference will be automatically listed there. Click it and click Ok to go to that cell.

To view a linked cell on another sheet, use Trace Precedents

Format the data

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. You can hide this column, if desired, by right clicking it and choosing Hide. This simply hides the column so the data is there should you need to refer to it later on.

You can format the data to display it more effectively

Different workbooks

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.

To do this you would use the same process as before and select an empty worksheet or click Insert > Insert Sheet to include a new worksheet in a workbook. Choose the Consolidate tool and this time instead of selecting a sheet in the current workbook, click the Browse button to open a different workbook.

Now select the references to use in that particular workbook. Repeat for each workbook containing the data you are interested in viewing. I find it easier to switch between open workbooks when I add the Switch Windows button to the QAT (Quick Access Toolbar).

Add the Switch Windows button to your QAT if your data are in different workbooks

If you select the Create Links to Source Data checkbox then, when you create your consolidation, the connection will be live so that changes in the underlying worksheets and workbooks will be reflected in the consolidated data. In addition, the second column of the summary will display the workbook name. The tip about using Trace Precedents also works to quickly take you to the workbook if it is open already, but will fail if it is not.

>Update the consolidation

If the data in your sheets changes, for example, you add another office after you have created your consolidation, first add the data to the appropriate sheets and save the workbook. Then go to the sheet containing the consolidation and select and remove all the rows containing the consolidated data.

Click Data > Consolidate and you'll find the references are all still in place. Make changes to the data by adding or removing ranges or changing the range size and click Ok to recreate the consolidation.

Warning tip!

If you have chosen to link back to the source data, you cannot update the consolidation by choosing Data > Consolidate without having first removed the earlier results. The reason is that this will embed one consolidation inside another rather than updating it making a nonsense of the worksheet and the results. You can update the ranges manually by changing the formulas but it's probably best to start over.

If, however, you did not select to link back to the source data, you will find the consolidation is simply a summary of the data without any details. In this case you can update the consolidation by clicking in the top cell containing the results and choosing Data > Consolidate, make the changes and click Ok.

The benefit of this Consolidate option is that it allows you to keep your data where it was originally located even across multiple worksheets and multiple workbooks and still consolidate it for analysis. While you cannot reorder the data as you could in a PivotTable if you don't need that functionality and if all you need is a simple summary worksheet laid out in a similar way to the original data then this is a quick and easy solution.

See also