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, Layer 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 in 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. Create a separate sheet and rename it as ‘‘Summary’’, or a similar term. This is where Excel will create the summary table.
- 2. Click on an empty cell, preferably the upper-left, to indicate where your summary table starts.
How to Combine Multiple Excel Files Into One
Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master fileREAD MORE
- 3. Go to Data > Consolidate.
- 4. Click on the drop-down “Function” menu and then “Sum”, to add the values of all the worksheets.
- 5. In “Reference”, click on the upward arrow button to select the data source to consolidate.
- 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.
- 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.
- 8. Click on “Add” to include in the “All references” list.
- 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.
Discover what Excel version control is, the version control features Excel has to offer, and how to use them to share, merge, and review Excel changesREAD MORE
- 10. Select the “Top Row” and “Left Column” boxes to include labels in the highest rows and labels to the far left.
- 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”.
- 12. Click on the ‘+’ buttons to the left to view the original data from the new master worksheet.
- 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, Layer offers full transparency and control over your Excel workbooks. While you review changes and data input, Layer consolidates it automatically into your main file. This means that you save time creating new worksheets or new workbooks for summary tables. You can focus on accepting or rejecting the changes.
How to consolidate data in Excel with Layer?
Layer is a collaboration platform for spreadsheets that works on top of your existing Excel files and Google Sheets. Share parts of your spreadsheet, collect and consolidate data, and review changes to make collaboration seamless and more efficient while keeping full control over your data. Using Layer, you can:
- Manage Access: Give spreadsheet access on a tab or cell level to relevant stakeholders.
- Collaborate: Automate tasks, set deadlines, and communicate on top of your files.
- Review & Track: Consolidate input, track changes, and restore previous versions.
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, Layer is a practical solution to consolidate data from different files into one worksheet. The best thing about this is that Layer will do it for you.
If you’re interested in other methods to merge and combine data in your spreadsheets, you’ll find these articles useful: