Don’t forget to share this post

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. 1. Create a separate sheet and rename it as ‘‘Summary’’, or a similar term. This is where Excel will create the summary table.
How to Consolidate Data in Excel Step by Step Separate Sheet
How to Consolidate Data in Excel (Step-by-Step) - Separate Sheet
  1. 2. Click on an empty cell, preferably the upper-left, to indicate where your summary table starts.
How to Consolidate Data in Excel Step by Step Click on cell
How to Consolidate Data in Excel (Step-by-Step) - Click on cell

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 file

READ MORE
How to Combine Multiple Excel Files Into One
  1. 3. Go to Data > Consolidate.
How to Consolidate Data in Excel Step by Step Consolidate
How to Consolidate Data in Excel (Step-by-Step) - Consolidate
  1. 4. Click on the drop-down “Function” menu and then “Sum”, to add the values of all the worksheets.
How to Consolidate Data in Excel Step by Step Function Sum
How to Consolidate Data in Excel (Step-by-Step) - Function > Sum
  1. 5. In “Reference”, click on the upward arrow button to select the data source to consolidate.
How to Consolidate Data in Excel Step by Step Upward arrow
How to Consolidate Data in Excel (Step-by-Step) - Upward arrow
  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.
How to Consolidate Data in Excel Step by Step Consolidate Reference
How to Consolidate Data in Excel (Step-by-Step) - Consolidate Reference
  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.
How to Consolidate Data in Excel Step by Step Consolidate range
How to Consolidate Data in Excel (Step-by-Step) - Consolidate range
  1. 8. Click on “Add” to include in the “All references” list.
How to Consolidate Data in Excel Step by Step All references
How to Consolidate Data in Excel (Step-by-Step) - All references
  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.
How to Consolidate Data in Excel Step by Step Full list
How to Consolidate Data in Excel (Step-by-Step) - Full list
The Beginners Guide to Excel Version Control
The Beginner's Guide to Excel Version Control

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 changes

READ MORE
  1. 10. Select the “Top Row” and “Left Column” boxes to include labels in the highest rows and labels to the far left.
How to Consolidate Data in Excel Step by Step Top Rows and Left Columns
How to Consolidate Data in Excel (Step-by-Step) - Top Rows and Left Columns
  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”.
How to Consolidate Data in Excel Step by Step Create Links
How to Consolidate Data in Excel (Step-by-Step) - Create Links
  1. 12. Click on the ‘+’ buttons to the left to view the original data from the new master worksheet.
How to Consolidate Data in Excel Step by Step Click on
How to Consolidate Data in Excel (Step-by-Step) - Click on +
  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.
How to Consolidate Data in Excel Step by Step Click on 2
How to Consolidate Data in Excel (Step-by-Step) - Click on 2

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.

Sign up to get started with Layer right now or schedule an onboarding call so we can help you set up and share your spreadsheet flows.

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, 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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jun 10 2022