Microsoft Excel has gained prominence in the field of data analysis because it constantly updates and evolves to meet the needs of the time. One important feature that is often overlooked is merging Excel spreadsheets. This feature is crucial not only because it makes reporting and presentation easier by providing all the data in one sheet, but it also simplifies data analysis. However, many people find it challenging to combine Excel spreadsheets. Therefore, in this article, we will learn how to merge two Excel spreadsheets using different methods.
How to Merge Sheets in WPS Office
WPS Office facilitates sheet merging seamlessly. Let's explore how to use the same example of merging January and February sales into a consolidated "Total Sales" sheet. WPS Spreadsheet not only merges data but also incorporates functions for added ease.
Step 1: Open the destination sheet in WPS Spreadsheet.
Step 2: In WPS Spreadsheet, navigate to the "Data" tab and click on the "Consolidate" option in the ribbon.
Step 3: In the consolidate window, click on the "Reference" field. Go to the source sheet (January Sales) and select the entire table, and then click on "Add".
Step 4: In the “Reference” field again, select the February sales table and click "Add".
Step 5: Since we want to add the sales of both months, choose "Sum" in the "Function" field and click "OK".
Step 6: The data from both sales tables will be summed and appear on the destination sheet.
WPS Spreadsheet offers a user-friendly interface with straightforward options, making it more accessible than the consolidate function in Excel. Not only that, but accessing your source sheets is also simplified, providing a cohesive experience. Download WPS Office now to witness the effortless process of merging sheets in WPS Office.
How to Merge Two Excel Spreadsheets in MS Excel
Let's explore the process of merging Excel spreadsheets into a single sheet using an example. Suppose we have a store, and to keep track of the sales each month, we use Excel sheets. However, each month's sales are in separate sheets, and we want to merge these Excel sheets together. But how do we combine these Excel spreadsheets to get one sheet that follows the same pattern? Simply follow these steps for the answer:
Step 1: Ensure that both sheets have an identical structure, such as products listed in rows and units sold and revenue in columns.
Step 2: After filtering and sorting the data, create a new sheet where we will consolidate the data.
Note: The consolidation process will only bring numerical values to the sheet.
Step 3: Create a similar sheet to combine data for both January and February, ensuring it has the same columns.
Step 4: Once the sheet is prepared, go to the "Data" tab and click on the "Consolidate" icon in the Data Tools section.
Step 5: In the reference section, select the entire table for January and click "Add".
Step 6: Next, select the February table and add it as well.
Step 7: To display the total sales for 2023 (the sum of January and February), select "Sum" in the Function Field and press OK.
And there you have it—the sales for the entire year consolidated into a single sheet.
If you want to add a new table, click on Consolidate again and add new data. If you wish to calculate a product, simply select "Product" in the Function field. Excel simplifies the entire process.
Consolidate Data in Excel and Merge Multiple Sheets into one Worksheet
Data consolidation can be achieved through various methods like VBA, Power Query, and utilizing a key column.
Using One Key Column:
Let's focus on the VLOOKUP method. Ensure the sheets to be merged have a key column serving as an identifier; for instance, the "Product A" column.
Suppose we want to fetch data from different sheets (January and February) into a destination sheet with columns for January and February sales.
Step 1: Navigate to the January column and initiate the VLOOKUP function with an "=" sign.
Step 2: For the lookup_value argument, select the product cell (e.g., A4 for "Product A").
Step 3: Set the table_array by going to the January sales sheet, selecting the entire sheet, and using absolute references.
Step 4: In the col_index argument (3rd), specify the position of the revenue column in the table (e.g., "3" for the 3rd column where revenue is).
Step 5: In the last argument, choose "False" to ensure an exact match and press Enter.
Step 6: Repeat the process for February revenues. Use the Autofill handle to copy the function to other cells.
Now, you have consolidated revenues for both months. To calculate the total, use a simple SUM function in the destination sheet.
A straightforward VBA code facilitates the consolidation of spreadsheets into a single sheet. However, it merely gathers the sheets in one place without combining them. Let's explore how to enhance data management:
Using VBA:
A straightforward VBA code facilitates the consolidation of spreadsheets into a single sheet. However, it merely gathers the sheets in one place without combining them. Let's explore how to enhance data management:
Step 1: Open the Excel file in Microsoft Excel and navigate to the Developer tab.
Note: If the Developer tab is not visible, enable it by accessing the Options menu in the File menu.
Step 2: Click on "Visual Basic" in the Developer tab to open the VBA window.
Step 3: Select "Module" from the Insert menu in the Developer tab to initiate coding.
Step 4: In the Module window, insert a code to merge spreadsheets. The command should also include the file path. Here's how the code would look:
Sub ConslidateWorkbooks()
Path = "/"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Step 5: After pasting the code, ensure to insert your Excel spreadsheet files' path in the Path code line.
Note: Make sure all your spreadsheets are in the same folder; this code will fetch all spreadsheets in that folder for merging.
Step 6: One crucial step before running the code is that, in the path, the folder name comes at the end, and there's a "hyphen" at its end, which is present in the code. If you remove it, an error might occur.
Step 7: Click on the "Play" icon, and your desired spreadsheets will be merged into a single workbook.
Using Power Query Editor:
The last method is considered the go-to approach when dealing with larger datasets or multiple worksheets. Let's see how to do it:
Step 1: Open a new blank worksheet in Microsoft Excel and head over to the Data tab.
Step 2: In the Data tab, click on "Get Data" and then select "Launch Power Query Editor".
Step 3: In the Power Query Editor, go to the sidebar and right-click to open the context menu.
Step 4: In the context menu, select "New Query" > "Other Sources" > "Blank Query".
Step 5: Go to the function field, and there enter the function "=Folder.files()" to fetch all the worksheets in a particular folder. In between the parentheses, copy the path of the folder, which would look something like this: "C:\Users\OneDrive\Documents".
Step 6: Once the worksheets have been extracted, numerous irrelevant columns will appear. We only need the "Content" and "Name" columns, so select them while holding the "CTRL" key.
Step 7: Right-click on the selected columns and choose "Remove Other Columns" in the context menu to eliminate remaining cells.
Step 8: Go to the "Add Column" tab and then select "Custom Column".
Step 9: In the Custom Column window, enter "=Excel.Workbook()" in the Custom column formula field. In between the parentheses, double click on the “Content” column. Click on “OK” to add a new column containing data from our worksheets.
Step 10: Now, go to your custom column header, and you will see a "Double Arrow" icon. Click on it to expand and then click "OK" to expand all the columns.
Step 11: Again, numerous columns will appear in your data. This time we need only 3 columns: Name, Custom Name & Custom Data. Right-click and remove other columns.
Step 12: Click on the "Double Head Arrow" icon in the Custom Data column to expand all columns. This will display all the data from your worksheets.
Step 13: Go to the Home tab and click on "Use First Rows as Headers" to have custom column names as your worksheets.
Step 14: Click on any column and remove "Null" rows to clean data and any other rows you wish to remove.
Step 15: Notice that we don't need the Custom Name and Custom Column anymore. Let's remove them as well.
Step 16: All the steps can be viewed in the Query settings tab under Applied Steps.
Step 17: Finally, in the Home tab, click on "Close & Load", and then click on "Close & Load" from the drop-down menu to load the worksheets into our blank sheet.
There you have it, all done. Using the Query Editor, you have merged all your worksheets into one new destination worksheet. Now, perform your analysis, create charts, on the entire dataset.
How to Merge Multiple Worksheets into a Single Worksheet in WPS Office
Microsoft Office holds a prestigious position among office suites, but WPS Office has been gaining recognition for offering similar advanced features while addressing the limitations of MS Office. Firstly, WPS Office is completely free, providing a familiar interface to ease the transition, eliminating the learning curve. The user interface is not only neat but also thoughtfully designed for easy navigation. If you're concerned about compatibility, WPS Office seamlessly works with LibreOffice, MS Office, and other suites, ensuring a hassle-free experience when working with MS Office files.
Moreover, WPS Office simplifies file merging using the same consolidation option as MS Excel. This process is even more convenient as all apps and tabs open in a single window, eliminating the need to juggle between multiple spreadsheets. Download WPS Office now to make file merging a seamless and efficient part of your workflow.
Tips for merging two spreadsheets in Excel
Merging two spreadsheets in Excel can be a useful task, and here are some tips to help you do it efficiently:
Check After Merging: Always reopen the saved sheet to ensure accurate data transfer. Save the original sheets as a backup in case of discrepancies.
Formatting Matters: Verify that both source and destination sheets follow proper formatting. Eliminate blank rows or columns for seamless merging.
Utilize VLOOKUP Function: Leverage VLOOKUP with a common identifier for merging specific columns. Ideal for linking data based on unique keys.
Consider External Tools: For large or complex datasets, explore tools like Power BI or databases for efficient and effective data management.
FAQs
Q1. How to Merge two Excel Spreadsheets and remove duplicates?
Here’s the step-by-step guide on how to merge two Excel spreadsheets and remove duplicates:
Step 1: Open the two spreadsheets you want to merge.
Step 2: Copy the first from one spreadsheet using Ctrl+C and paste the copied data into the other spreadsheet using Ctrl+V.
Step 3: Repeat the copy-and-paste for all sheets.
Step 4: Select all the data in the merged spreadsheet.
Step 5: Click on the Data tab in the Excel ribbon and choose Remove Duplicates.
Step 6: When you see the Remove Duplicates box, decide if your data has headers by checking or unchecking the box. Also, make sure all columns stay selected.
Step 7: Select OK. A notification will appear indicating the quantity of removed and the remaining items.
Step 8: Confirm by selecting OK again. The sheets are now merged into one, eliminating all duplicate entries.
Q2. Can I merge Excel sheets with different structures?
You can merge Excel sheets with distinct structures using advanced features such as Power Query. This tool intelligently aligns and consolidates data from sheets with varying formats, ensuring a smooth integration. Power Query adapts to different structures, recognizing and coordinating the data intelligently. Hence, this flexibility allows users to combine information from diverse sources or sheets, facilitating comprehensive data analysis and reporting.
Q3. Can I undo a merge operation in Excel?
Yes, the "Undo" function (Ctrl + Z) provides the capability to revert the most recent operation effortlessly, including the merging process, in Excel.
Advanced Data Analysis: Merge Excel Sheets with WPS Office
Learning how to merge two Excel spreadsheets elevates your data analysis capabilities. Excel provides various simple and abundant methods, allowing users to choose based on their data. For a simplified experience, consider trying WPS Office, a free alternative to Microsoft Excel. WPS Office not only offers advanced features for in-depth data analysis but also provides unparalleled PDF functionalities. With mobile apps for both Android and iOS, it enables you to work on the go. Download WPS Office now to enhance your daily productivity, regardless of the system you're using.
