You can hide this column, if desired, by right clicking it and choosing Hide. You'll find that the second column of data shows the name of the workbook that contains the data. You can format the data in the summary worksheet as you would in any regular Excel file. Click it and click Ok to go to that cell. Double click to open the Go To dialog - the cell reference will be automatically listed there. 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. To do this, click in the cell that contains the data you want to view. 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.Īs this data is linked you can use the Trace Precedents option to go to the cell containing that data. 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.
If you chose "Create links to source data" then the data is linked to the original cell containing it. You will see grouping tools down the left of the screen which you can use to display and hide the data. When you click Ok, Excel summarizes all the data into your new sheet. Select the Create links to source data check box and click Ok. Select the Use labels in: Top Row and Left Column checkboxes. Check that you have all the references there before you continue. You'll see that the worksheets appear in the All References panel in alphabetical order. 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. 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. To do this, select a range and type a name for it into the Name box at the far left of the Formula bar. You can name your ranges before you start the Consolidation process. 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. So the number of rows or the number of columns or the number of rows and columns might be different from sheet to sheet. The ranges you consolidate do not necessarily have to be of the same size in each worksheet. 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. Continue and add the second and each additional set of data to the References section of the dialog. 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.īack in the Consolidate dialog, click Add to add this first set of data to the consolidation dialog. However we could have chosen any of these 11 functions: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var and Varp.Ĭlick in the Reference area and click the Expand/Collapse button to its immediate right. For our data we want to add the values so we'll set the Function to Sum.
Here you will select the function to analyze your data and the references or ranges that you want to consolidate. Choose Data > Consolidate to view the Consolidate dialog. To do this select an empty sheet in the workbook - add a new one if necessary - and click in it.
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.
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.Ĭonsider the situation where you have data for twelve months of your business stored one month per sheet in an Excel workbook. 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. 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. There are many ways to consolidate data in Microsoft Excel. The updated version of this tutorial can be found here: How to consolidate data from multiple worksheets into a single sheet