Most analysts merge data in Microsoft 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.
The terms “Merge” and “Consolidate” in Excel, can refer to two separate functions. Because of that, we created a short guide to understanding how to best combine data and what to do when consolidating data in Excel is too time-consuming. At Alteryx, we are passionate about creating radical productivity for business analysts, so we have included information on improving the data preparation process.
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 merge 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 in Excel from Multiple Worksheets
Let’s take a look at how we can merge data from multiple worksheets 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”, then “File” 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.
Additional Ways to Merge Cells and Columns in Excel
There are many ways to merge cells and columns in Excel, below are a few examples:
Combine text in different columns
- Text from cells can be combined into a new column using the Ampersand symbol. An example formula would be =A1&B1 to combine text without spaces and =A1&” “&B1 can be used to leave a space in between text. The CONCATENATE function can be used in place of the Ampersand but has a limit of 255 strings.
- The TEXTJOIN function can be used to combine multiple cells or columns. An example formula would be =TEXTJOIN(” “, TRUE, A1:B1). The formula begins with the delimiter to select how the text should be separated, the next step of the formula is ignore_empty, and when TRUE is used empty cells are ignored. The last part of the formula is to specify the cells that should be included.
Automatically fill in data with patterns
- Flash Fill can automatically fill in data that fits a pattern. You can manually turn this on by going to Data, then Flash Fill, or you can enter the desired information in a new column and press Enter to see the new information load.
Combine data with a common identifier
- The VLOOKUP function can be used to combine data with a common name or ID. An example formula would be =VLOOKUP(A2, ‘Different Table’!$A$2:$B$100, 2, TRUE) and starts first with the cell you want to search for, then the table_array to look for the value, next is the column number (col_index_num) where to include the value, and then FALSE for an exact match or TRUE for an approximate or closest match.
Merging cells
- To merge cells in Excel, first select the cells you would like to merge. Then right click, select Format Cells, click on the Alignment tab, and select Merge cells in the Text control section. Merging cells will only keep the values listed in the upper left cell and discard the values in other cells.
Prepare and Blend Data with Alteryx
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 Alteryx, 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, Alteryx helps users deliver faster, more accurate analysis.
Alteryx was designed from the ground up to help reduce data cleansing and data preparation time. At Alteryx, we provide easy-to-use, intelligent, visual data analysis that improves data understanding for any project or organization. View our Excel to Alteryx guide and sign up for a free trial today.