# Guide to Combine and Consolidate Data in Excel

Author:

Guide to combining and consolidating data in Excel. This includes consolidating data from multiple cells, multiple worksheets, and multiple workbooks using formulas, functions, commands, and macros in Excel.

Here, I will explain what you can do in Excel and point you to tutorials that help you do those things.

### Sections:

Combine Cell Data

Combine Worksheet Data

Combine Workbook Data

Notes

## Combine Cell Data

To combine data from multiple cells you do what is called "concatenating" cells, which just means to combine them.

We have 3 great tutorials that cover all of those features:

1. Combine Cell Values Using the Concatenate and Ampersand Method This covers the basics of combining data from cells.
2. Combine Values from Multiple Cells in a List Quickly This is a trick that makes combining a list of data easy.
3. Custom Excel Function to Quickly Combine Multiple Cells into One Custom Excel function, that we show you how to create, that makes consolidating data between cells easy and quick. (This does take a few steps to setup at first, but everything is explained in the tutorial.)

## Combine Worksheet Data

The next thing you might want to do is to access, combine, and consolidate data between worksheets in Excel.

Depending on your data and exactly what you want to do, this can range from really simple, to quite complex.

1. Link to Cells on Other Worksheets This teaches you how to get data from one worksheet and put it on another worksheet. This allows you to get the data from a single cell on another worksheet or from the entire row, column, or dataset once you copy the formula down and across.
2. Consolidate Data from Multiple Worksheets in Excel Shows you a powerful, though more involved, way to combine and consolidate data from multiple worksheets in Excel.

## Combine Workbook Data

This is the most difficult way to combine or consolidate data in Excel but it can really save you a lot of time and effort if you get it right. The reason this is difficult is that, usually, the workbooks that you want to combine are not in exactly the same format and layout.

You can use both methods listed above for combining worksheet data to combine data between separate workbooks, you just need to have both of the workbooks open initially and then perform the same steps as you would if the two worksheets were in the same workbook.

One really nice thing that we have here is a macro that combines workbooks for you:

1. Combine Multiple Workbooks into One This macro combines the desired worksheet from all selected workbooks and puts them into a single worksheet in a new workbook.
2. Put Worksheets from Multiple Workbooks into One This places worksheets from multiple different workbooks into a single workbook; the worksheets are not "merged" into one sheet using this macro.

## Notes

Combining, consolidating, and linking data that is separate in Excel, be it in separate cells, worksheets, or workbooks, is one of the most important things that you will have to do in Excel. Doing it well requires knowledge of many different aspects of the program but it is well worth it to take the time now to learn how to do this instead of waiting until the last minute and pulling your hair out trying to combine two worksheets.

#### Question? Ask it in our Excel Forum

##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

Similar Content on TeachExcel
Combine Data from Multiple Worksheets in Excel
Tutorial: The easiest way to combine and consolidate data in Excel. Simple method to combine data ...
Quickly Consolidate and Compare Data in Excel Using a Pivot Chart & Pivot Table
Tutorial:   Visually combine and compare data in Excel in less than 1 minute! This technique takes ...
Convert Month Name to Number and Vice Versa in Excel
Tutorial: How to convert a month's name, such as January, into a number using a formula in Excel; al...
Data Visualization and Summary Trick for Excel - Outlining Data
Tutorial: How to group and outline data in Excel. Simple trick for collapsing large data sets so th...
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
Macro: This Excel macro filters data in Excel without the filter "arrow" appearing in the column ...
Convert Time to Minutes and Vice Versa in Excel
Tutorial: How to convert a time into minutes in Excel and also how to get minutes back into a time f...
Tutorial Details
##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)