The success of any business is backed by a lot of data collection, research, and analysis. Today, every business operation produces a lot of data on the go, and in different forms. In such a scenario, picturing a unified view of the data accumulated from multiple sources can be difficult.
For example, let’s say you are doing online business through multiple sales points, i.e. online marketplaces and your own e-commerce store. In such cases, you need to accumulate sales data generated by all these sales points to evaluate your business performance. Manually putting data from one Excel sheet to another and using the formulas to combine these datasets can be the worst option here.
The solution is to consolidate data in Excel. In this article, we cover everything about data consolidation in Excel, along with practical examples.
What is data consolidation in Excel?
Data consolidation in Excel is the process of bringing similar data from multiple sources to one place, which can provide a unified view for better analytics.
The ‘Consolidate’ feature in Excel can be useful when you want to summarize the same data from multiple sources. Consolidating data in Excel allows combining data from multiple rows and ranges using functions such as sum, average, count, min, and max.
Data consolidation functionality also works well with data from other worksheets and workbooks.
Why do you need to consolidate data in Excel?
Different systems may produce different datasets in multiple formats. Bigger organizations may end up with hundreds (if not thousands) of CSV files and worksheets containing data. Combining these datasets into a single one, in a particular format, is essential for an overall analysis.
Data consolidation in Excel is a helpful functionality to combine similar datasets from multiple rows, columns, worksheets, or workbooks.
5 Examples of data consolidation in Excel
Theoretically, it may seem easy to consolidate data in Excel. But, it can turn complex when you are working with datasets from multiple worksheets or even multiple workbooks. Below are five common examples of data consolidation in Excel along with steps.
Example #1 – How to consolidate data in Excel from multiple rows
Rows consolidation in Excel can be helpful when you have multiple rows with the same entries.
Let’s say you have a record of various orders placed on an e-commerce store containing columns for order ID, product SKU, and quantity.
Here, you can use data consolidation to get the total quantity for each product by adding up quantities with similar SKUs.
You can follow these steps to consolidate data from rows:
- Open the Excel sheet and select the target cell where you want to place the consolidated rows.
- In the upper tab, select Data, and click on Consolidate in the ‘Data Tools’ section.
- The ‘Consolidate’ window will appear on the screen. Select the appropriate function, click on the little upside arrow in the reference field, and select the reference ranges.
- After selecting the range, click on Add button to add it as a reference. Check the appropriate option in the ‘Use labels in’ section (It is ‘Left column’ in this example to add up product quantities with the same SKU), and click on OK to proceed.
- Verify the results for a couple of rows manually to ensure that the rows have been accurately consolidated.
Example #2 – How to consolidate data in Excel from multiple ranges
You can also use consolidate in Excel to combine data from multiple ranges.
For example, let’s say you have order sales data for January & February months and want to combine both of them to find the total quantity of products sold in both months by SKU.
In this case, you need to select multiple ranges and use both the ‘top row’ and ‘left column’ as labels. Let’s see how to consolidate data in Excel from multiple ranges.
- Select the cell where you want to consolidate data from multiple ranges.
- Go to Data > Data Tools > Consolidate, and select the appropriate function (which is ‘sum’ here).
- Now, select and add all the reference ranges (remember to select ranges including the header rows), tick both ‘Top row’ & ‘Left column’ to use their labels, and click on OK to continue.
- The data will be consolidated into the target cell.
Example #3 – How to consolidate data in Excel from multiple worksheets
Similarly, you can also use the consolidate feature in Excel to combine data from multiple worksheets.
Suppose you have two data ranges in different worksheets and want to consolidate data in Excel.
Here, you can simply switch the worksheet and select the respective reference ranges from multiple worksheets for data consolidation.
The steps are provided below:
- Select the cell where you want to consolidate data from multiple worksheets.
- Go to Data > Data Tools > Consolidate, and select the appropriate function.
- Select all the ranges from multiple worksheets as references and add them to the Reference field.
- After adding ranges, tick the ‘Top row’ & ‘Left column’ options for the labels, and click on OK to proceed.
- The data will be successfully consolidated from multiple worksheets to the target cell.
Example #4 – How to consolidate data in Excel from multiple workbooks
You can also use Excel data consolidation to combine datasets from multiple workbooks and present it into a master sheet. Here, you need to define the file source in the reference along with the data range.
Complete steps to consolidate data in Excel from multiple workbook files are provided below.
- Open the worksheet and select the cell where you want to consolidate the data from multiple workbooks. (Keep the source workbooks open in multiple windows.)
- Go to Data > Data Tools > Consolidate, and select the appropriate function.
- Now, in the references field, click on the tiny up arrow, switch to the source workbooks, and select the reference ranges. This will add the file path along with the range to the reference.
- Add the selected range to the reference, and repeat the same procedure to add other references.
- Next, check the ‘Top row’ and ’Left column’ as the labels, and click on OK to proceed with the consolidation process.
- Voila! The data will be consolidated successfully in the target cell from multiple workbooks.
Automate data importing & consolidation in Excel
Excel supports data consolidation only from limited data sources such as other Excel and CSV files. Furthermore, you may need to perform the same procedure again to update the latest data. This can become tedious over time when you have a lot of data sources that keep updating.
Coupler.io automates the data-importing process from multiple sources at regular intervals. It lets you import data from 30+ Excel integrations and have a consolidated view in the destination sheet.
Example # 5 – How to consolidate data in Excel from multiple columns using Coupler.io
Let’s say we have the following columns that we want to combine from multiple worksheets.
To automate the data importing and consolidation in Excel, you need to create an account on Coupler.io.
Log into your account, click on Add New Importer, select Microsoft Excel in both Source & Destination Fields, and configure the following settings:
Source
- Connect your Microsoft OneDrive account to the Source account.
- Click on File to select the Excel workbook to import data from.
- After selecting the file, choose the worksheets from where you want to import and consolidate the data.
- Click on Jump to Destination Settings to proceed further, or if you want to specify the data ranges, click on Continue. By default, Coupler.io will export all data from the source sheets.
Destination
- Select the Account and click on Workbook to choose the destination file.
- Select the Workbook’s Sheet where you want to import and consolidate the data from the source sheets.
- Click on Continue and enter the Cell address where you want to import the data. (The default cell is A1)
- Under the Import mode, choose whether to replace the entire data with a fresh one or append the new data to the existing one. You can also add a column specifying the last updated date & time, and choose to skip import for the same data with no changes.
- Click on Continue to proceed with the next section.
Schedule
- Enable the Automatic data refresh option if you want to auto-import data at regular intervals.
- Select appropriate options for the auto-update timings and click on Save and Run.
Once the data importing process is completed, you can check the destination file. The result:
We can see, Coupler.io combined columns from both of the source sheets into one sheet. It also added a column for the sheet name for a better understanding of the data.
Now, if you have set up a scheduled import, Coupler.io will carry out the process import automatically at regular intervals without any manual inputs.
Furthermore, you can consolidate data from multiple Excel workbooks. For this, you need to click Add one more source and perform the setup as we described above.
Since Coupler.io supports many other data sources, you can automate data consolidation or data stitching from Google Analytics, Jira, Mailchimp, and more to Excel.
Data consolidation in Excel – Which method works best?
Unifying data from multiple sources is possible using consolidation in Excel. There are different types of consolidations you can try to combine datasets from multiple rows, ranges, worksheets, and even workbooks.
One can leverage this functionality in Excel to get a birds-eye view of their business through data unification. However, data consolidation in Excel has its own limitations. The manual method is prone to errors and may produce inaccurate results in cases of data inconsistencies.
Therefore, using specialized platforms such as Coupler.io is the best choice for businesses with complex data reporting and analysis.
