How to Consolidate Data in Excel (Step-by-Step)

If your day-to-day revolves around dealing with multiple spreadsheets containing various types of data, then you'll be searching for ways to collect data from multiple sources in quick and efficient ways. For example, if you are dealing with financial data, drawing up an annual budget requires combining accounts from various departments according to a pre-established structure. Luckily, Excel includes the Data Consolidation feature that can help you achieve this without any manual work involved, meaning less time and effort on your part.

In this article, you’ll find a full explanation of what Data Consolidation in Excel is and a step-by-step guide on how to consolidate data in Excel from multiple worksheets into a single worksheet.  Alternatively, Sheetgo offers automation features to help you quickly consolidate data from various sources, including Excel and Google Sheets.

What is Data Consolidation in Excel?

The Data Consolidation feature in Excel allows you to collect data from different worksheets and merge them into one. This worksheet can then be used like a master worksheet, which is the same system used by most automation platforms. This master worksheet links to the source data worksheets, so any time a source worksheet is changed, the consolidated worksheet will be updated.

This type of consolidation tool is widely used by people in Finance, especially those in charge of planning and analysis. Since all data is structured into one table, it makes it much easier for anyone in your organization to carry out the analysis they need.

How to consolidate data in Excel?

The Consolidate function can be used to combine multiple Excel columns into one as well as consolidate rows in one same Excel worksheet. Moreover, you can consolidate data from different worksheets into a single worksheet.

Consolidate data from multiple worksheets in a single worksheet

Let’s say you or your manager need to analyze the annual expenses. This will mean compiling the costs associated with different departments, including HR, Marketing, Sales, etc. Each department saves the data on a different sheet. Compiling all this data for a general analysis will be too time-consuming, so a feature like Data Consolidation in Excel is necessary. This is how you can consolidate data in Excel from multiple worksheets in a single worksheet.

1. 1. Create a separate sheet and rename it as ‘‘Summary’’ or a similar term. This is where Excel will create the summary table.

1. 2. Click on an empty cell, preferably the upper-left, to indicate where your summary table starts.

1. 3. Go to Data > Consolidate.

1. 4. Click on the drop-down “Function” menu and then “Sum”, to add the values of all the worksheets.

1. 5. In “Reference”, click on the upward arrow button to select the data source to consolidate.

1. 6. A “Consolidate Reference” pop-up will show. Since the source data is in the same workbook, you can simply navigate through the sheets and select the data range by dragging your cursor.

1. 7. As shown below, Excel automatically enters the range of your source data. It shows the worksheet’s name followed by an exclamation mark, the selected rows, and the columns. Once selected, click on the arrow button to head back to the main “Consolidate” settings window.

1. 8. Click on “Add” to include in the “All references” list.

1. 9. Repeat steps 5, 6, 7, and 8 for the remaining sheets. Once you're done, you should see the full list of reference data, like the one below.

1. 10. Select the “Top Row” and “Left Column” boxes to include labels in the highest rows and labels to the far left.

1. 11. Select the “Create Links” option to allow the summary table to update whenever another user changes the source worksheet. If not, you will have to do this manually. Once you’re finished, click “OK”.

1. 12. Click on the ‘+’ buttons to the left to view the original data from the new master worksheet.

1. 13. If you want to display this original information for all rows, you can click on the number “2” button, right above the first “+” button.

The data shown in Column C refers to the name of the workbook that contains your data. If you prefer to hide this, you can do so by simply right-clicking on it and selecting “Hide”. This is simply telling you that these cells are part of a group that is currently collapsed.

Alternatively, Sheetgo offers full transparency and control over your Excel workbooks. While you focus on data input, Sheetgo consolidates it automatically into your main file. This means that you save time creating new worksheets or new workbooks for summary tables. Check out our post on how to merge Excel files using Sheetgo.

Conclusion

Consolidating data is usually one of the most common tasks done by data analysts. The reason is that a better interpretation of data is achieved when viewed in context. However, since it’s likely that each dataset will follow a unique structure or categorization, it’s not enough to copy all data into one single worksheet.

You should now know how the Excel Consolidate data function works for data contained in the same workbook and different workbooks. However, since it’s very likely that your data will be spread out in various workbooks, Sheetgo is a practical solution to consolidate data from different files into one worksheet. The best thing about this is that Sheetgo will do all the work for you.

If you’re interested in other methods to merge and combine data in your spreadsheets, you’ll find these articles useful:

• How to Combine Multiple Excel Files Into One
• How to Aggregate Data in Excel (Power Query)
• How to Combine CSV Files into One Excel Workbook