Can i combine spreadsheets in excel

Make sure to include either the top row or left column information that you previously selected. Note: Any labels that don't match labels in the other source areas cause separate rows or columns in the consolidation. On the Data tab, under Tools , click Consolidate. Make sure to include either the top row or left column information that you previously selected, and then click Add.

Combine by position For consolidation by position to work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list. Open each source sheet and make sure that your data is in the same position on each sheet. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after - consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.

Today we will tackle a problem that many Excel users are struggling with daily - how to merge multiple Excel sheets into one without copying and pasting. The tutorial covers two most common scenarios: consolidating numeric data sum, count, average, etc. The quickest way to consolidate data in Excel located in one workbook or multiple workbooks is by using the built-in Excel Consolidate feature.

Let's consider the following example. Supposing you have a number of reports from your company regional offices and you want to consolidate those figures into a master worksheet so that you have one summary report with sales totals of all the products. As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns:.

If one or some of the sheets reside in another workbook, click the Browse bottom to locate the workbook. Configure the update settings.

Select the Create links to source data box if you want the consolidated data to update automatically whenever the source data changes.

In this case, Excel will create links to your source worksheets as well as an outline like in the following screenshot. If you expand some group by clicking the plus outline symbol , and then click on the cell with a certain value, a link to the source data will display in the formula bar. As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. However, it does have a few limitations. In particular, it works for numeric values only and it always summarizes those numbers in one way or another sum, count, average, etc.

If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go.

In this case, you may want to employ one of the following techniques to automate the merge. The built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data.

Supposing you have a few spreadsheets that contain information about different products, and now you need to merge these sheets into one summary worksheet, like this:. With the Copy Sheets added to your ribbon, the 3 simple steps is all it takes to merge the selected sheets into one.

On the Excel ribbon, go to the Ablebits tab, Merge group, click Copy Sheets , and choose one of the following options:. Each color represents changes from a different user, so you can tell at a glance who made the change. Excel Merging Copies of a Shared Workbook. Selecting the Quick Access toolbar. Adding the Compare and Merge Workbooks command.

Compare and Merge Workbooks command.