Every day, most analysts merge data in Excel and other spreadsheet programs to get better insights. Consolidating data in Excel is part of a bigger process called data preparation, but as the number of new data sources increases, if you want to merge data in excel spreadsheets, it is getting harder to do.
At Trifacta we are passionate about creating radical productivity for business analysts, so we became experts in data preparation. Even though we make software that replaces Excel in some cases, inside our company we still merge data in Excel just like you do, because sometimes it’s still the right tool for the right job. So, we know from firsthand experience when consolidating data in excel with a spreadsheet is a good idea, and how to do it well.
As it turns out, the terms “Merge” and “Consolidate” in Excel, refer to two separate functions. Because of that, we created a short guide to understanding them both, as well as what to do when consolidating data in Excel is no longer an option.
How to Consolidate Data in Excel
For this example, let’s say you are given two sets of data about the amount of loans a group of members have borrowed per year, each in an independent Excel workbook. You want to understand the total amount of loans borrowed by each member, so you may naturally wonder how to combine data in Excel.
If both sets of numeric data are already formatted in a similar way, such as prices always formatted as $1.00, you can use the Excel consolidate feature (under the ‘Data’ dropdown menu).
Open each sheet you plan to use and confirm that the data types you want to consolidate in Excel match.
- In a new empty worksheet, select ‘Consolidate.’
- In the ‘Function’ box, select the function you want to use. In this example, we’re using “Sum” to add together the total loans borrowed per member.
- Under ‘Reference,’ select ‘Browse’ to identify the Excel workbooks you want to consolidate the data from. Add the source(s).
- Important: Make sure the labels match. Then hit ‘OK,’ watch the data propagate, and begin reviewing or analyzing the new sheet.
How to Merge Data from Two Excel Worksheets
Traditionally, VLookup has been one of the most important tools for merging data in Excel, but the process requires multiple steps and can easily tire analysts who must merge multiple columns across many datasets. Instead, let’s take a look at how we can do this same process all within the Excel Power Query editor.
In this example, we’re using two individual datasets, the first containing basic member information, such as income, education, phone number, etc., and the second containing member loan information, such as the loan amount, interest rate, loan status, etc. Each dataset also contains a member ID, which will allow us to join the data on that common field in order to compare all of this data side by side. Here’s how to proceed:
- First, we’ll take a look at each dataset to roughly analyze their contents, and then open up a new worksheet for our merged dataset.
- Next, we’ll click on the “Data” tab of our new worksheet and select “From Text/CSV” because the files that we’re working with are csv files. You can also import Excel files by selecting “Get Data.”
- We’ll start by importing our file on basic member info, which will bring us into the Power Query editor.
- Next, we’ll bring in our other dataset by selecting “New Source” and “Text/CSV” and we’ll see the dataset on loan information added to the left-hand side.
- To merge these two datasets, we’re going to select “Merge Queries” and “Merge as New” so that we can have a separate space for our new dataset.
- We know that the common data field is “member ID” so we’re going to select that column for both datasets.
- Now, we’re going to delete some of the columns that we don’t need from our member information dataset. When we’re finished, we’re going to click on the icon next to “member_loans” to expand the dataset we just brought in.
- Here, we’re going to select the columns that we want from our member loans dataset. We don’t need to bring in member ID, as it’s already a column in our member info dataset.
- Once we’re finished selecting, we can rearrange these columns as it makes sense for our analysis.
- And finally, we’ll hit “Close and Load” to see the finished, merged dataset in our blank worksheet.
Merge Data In Excel with Designer Cloud
As the scale and complexity of your data sources grow, you might find merging data with Excel is harder to do. For large and varied data sets, Excel becomes too complicated, cumbersome and slow to use when you are trying to merge data in Excel. That’s where Designer Cloud comes in. Designer Cloud is specifically designed to make this preparation process easier and more intuitive.
For example, imagine being able to save the specific merge functions you use to merge data in Excel, customized to each unique data source, and then reuse and share them with your colleagues effortlessly.
With Designer Cloud, data preparation is accessible, intuitive, and scalable across the organization. By providing a connected application for users to explore, structure, and produce dashboard-ready datasets, Designer Cloud helps users deliver faster, more accurate analysis.
Designer Cloud was designed from the ground up to help reduce data cleansing and data preparation time. At Alteryx, we live and breathe data in order to provide easy-to-use, intelligent, visual data analysis that improves data understanding for any project or organization. Sign up for Free Designer Cloud today.