Excel combine lists from multiple sheets
|
Sep 1 Consolidate data in Excel and merge multiple sheets into one worksheetby | updated on July 30, 2021 Show
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.) and merging sheets (i.e. copying data from multiple worksheets into one).
Consolidate data from multiple worksheets in a single worksheetThe 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: To consolidate the data in a single worksheet, perform the following steps:
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. To combine just a couple of sheets, you may not need anything else but the good old copy/paste. But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, you may want to employ one of the following techniques to automate the merge. How to merge Excel sheets into oneOverall, there are four ways to merge Excel worksheets into one without copying and pasting:
How to combine Excel spreadsheets with Ultimate SuiteThe built-in Excel Consolidate feature can summarize data from different sheets, but it cannot combine sheets by copying their data. For this, you can use one of the merge & combine tools included with our Ultimate Suite for Excel. Combine multiple worksheets into one with Copy SheetsSupposing 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.
Other ways to combine sheets in ExcelApart from the Copy Sheets wizard, the Ultimate Suite for Excel provides a few more merging tools to handle more specific scenarios. Example 1. Merge Excel sheets with a different order of columnsWhen you are dealing with the sheets created by different users, the order of columns is often different. How do you handle this? Will you be copying the sheets manually or moving columns in each sheet? Neither! Commit the job to our Combine Sheets wizard: And the data will be combined perfectly by column headers: Example 2. Merge specific columns from multiple sheetsIf you have really large sheets with tons of different columns, you may want to merge only the most important ones to a summary table. Run the Combine Worksheets wizard and select the relevant columns. Yep, it's that easy! As the result, only the data from the columns that you selected get into the summary sheet: These examples have demonstrated only a couple of our merge tools, but there is much more to it! After experimenting a bit, you will see how useful all the features are. The fully functional evaluation version of the Ultimate Suite is available for download at the end of this post. Merge sheets in Excel using VBA codeIf you are a power Excel user and feel comfortable with macros and VBA, you can combine multiple Excel sheets into one by using some VBA script, for example this one. Please keep in mind that for the VBA code to work correctly, all of the source worksheets must have the same structure, the same column headings and same column order. Combine data from multiple worksheets with Power QueryPower Query is a very powerful technology to combine and refine data in Excel. At that, it's rather complex and requires a long learning curve. The following tutorial explains the common uses in detail: Combine data from multiple data sources (Power Query). How to merge two Excel sheets into one by the key column(s)If you are looking for a quick way to match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard. The latter is a visual user-friendly tool that lets you compare two Excel spreadsheets by a common column(s) and pull matching data from the lookup table. The following screenshot demonstrates one of possible results. The Merge Tables wizard is also included with the Ultimate Suite for Excel. This is how you consolidate data and merge sheets in Excel. I hope you will find the information in this short tutorial helpful. Anyway, I thank you for reading and look forward to seeing you on this blog next week! Available downloadsUltimate Suite 14-day fully-functional version (.zip file) You may also be interested in
Excel: featured articles
Table of contents97 comments to "Consolidate data in Excel and merge multiple sheets into one worksheet"
Post a commentClick here to cancel reply. |
