OmniDocHow to merge multiple Excel worksheets into one table
There are multiple worksheets in the same workbook and I want to merge them into one table. This article explains how to first determine the table structure and then use copy append or query append to merge, and gives a checklist after the merge.
Key points of this article
- Before merging, make sure that the header names, column order and data types of each worksheet are consistent. This is the prerequisite for error-free merging.
- Headers, total rows and blank rows will be merged as data and should be deleted or processed separately before merging.
- When the amount of data is small and the table structure is simple, manual copy and append is straightforward; when there is a lot of data or repeated merging is required, query append is more stable.
- Add a "source worksheet" column after merging, and then you can trace which table each row of data comes from.
- When merging results, check the number of rows, totals and key fields to make sure there are no missing rows, duplications or misplacements.
Many people split the same type of data into different worksheets by month, department or region in Excel, such as one table each for "January", "February" and "March". When it comes time to summarize, they need to be put into the same table for filtering, pivoting or statistics. This article is written for people who are doing this type of organization. It focuses on how to merge multiple worksheets in the same workbook into one table, and what to check before and after merging.
After reading this article, you should be able to determine which merging method is suitable for your table, complete the merging step by step, and use several checks to confirm that the result is correct. What is discussed here is multiple worksheets in the same workbook; if there are multiple independent files, or two tables need to be matched according to a certain key field, the operation logic will be different.
How to merge multiple worksheets into one table? One sentence conclusion
When multiple worksheets have the same structure, the essence of merging is to stack the data areas of each table into a new table in order; when the structures are different or need to be mapped according to key fields, the structure must be unified first or other methods must be used. The table below can help you choose quickly.
| your situation | Recommended practices | reason |
|---|---|---|
| There are few worksheets (a few), the amount of data in each sheet is not large, and the structure is completely consistent. | Manually copy the data area of each table and paste it into the summary table one by one | Few steps, intuitive results, suitable for one-time sorting |
| There are many worksheets, or new worksheets may be added in the future | Use the query function to append all worksheets into one table | Can be refreshed and redone to reduce missing lines caused by repeated copying |
| I just want to summarize the numbers, no details need to be kept | Use formulas to reference totals across tables, or use pivot tables to summarize | The result is a summary value, it is not necessary to merge all details |
| The column order or column names in each table are different | First adjust each table to a unified structure, and then merge | Column misalignment puts numbers into the wrong fields, making it difficult to spot later. |
| Need to correspond to customer number, order number and other fields | Use find or join methods that match by key fields | This is an association between two tables, not a simple stack. The corresponding relationship between rows is determined by the fields. |
Before you start: Confirm these points first
- first, check that the headers of each worksheet are consistent. * the header is the basis for merging. If "amount" is written as "amount (yuan)" in one table and "amount" in another table, it will be stacked into two columns. Before merging, it is recommended to take a look at the first row of each table, with field names and field order aligned.
- second, confirm the boundary of the data area. * the worksheet may have a title and description at the top, a total line at the bottom, and a blank line in the middle. If these contents are merged together, redundant words or duplicate totals will appear in the summary table, and the statistical results will be too large. The true data range of each table should be clearly defined before merging.
- third, pay attention to merging cells. * if there are merged cells in the table, the copy or query will often take only the value of the first cell, and the rest of the location is empty. It is recommended to cancel the merger and fill in the blanks before merging.
- fourth, uniform date and number format. * some of the dates in the table are real dates, some are just texts that look like dates, and some numbers are identified as text with units or commas. Numbers in text format do not participate in summation, and sorting will be messy after merging. It is recommended to unify the format in each table first.
- Fifth, decide whether or not to keep the source information. * after the merge, many people will forget which table a row originally came from. If you want to check later, try to add a column of "source worksheet" to the data of each table before merging, and fill in the name of the table. This column does not affect the merger, but saves the trouble of checking later.
# steps to merge multiple worksheets into one table in Excel
The following example is to copy and append manually, which is suitable for situations where the number of worksheets is small and the structure is consistent. When the quantity is large, you can use the query append method instead, the train of thought is the same, but the tables are read automatically by Excel.
- Create a new worksheet, named "Summary", and put it in front of all the data sheets for easy viewing.
two. Write a uniform header in the first row of the Summary table, such as date, Department, Product, amount. Write the header only once, do not repeat it.
- Open the first data table, select the data area below the header (no header, no total rows), and copy.
- Go back to the summary table and paste in the first blank line below the header.
- Repeat steps 3 and 4 for each remaining datasheet, pasting to the next row of the previous data each time to avoid overwriting.
- If you need source information, insert a column on the left or right side of the summary table and fill in the corresponding worksheet name by data block.
- Check with filtering or sorting to make sure there are no blank lines, no duplicate headers, and no mistakenly entered totals.
The way to add a query is to import each worksheet into a query in the data function of Excel, and then use "append" to merge them into a single table. The advantage of this approach is that the original table can be refreshed after modification and does not need to be copied again. Where "append" requires that the column names of each query be the same, so the previous header check is still necessary.
Another common practice is to use macros to automatically traverse all worksheets and copy data one by one. It is suitable for people who often do the same arrangement, but it requires a certain programming foundation, and if the scope setting in the macro is miswritten, it will also miss lines or take more. In the case of occasional merge, manual or query append is sufficient.
# check item by item after completion
After the merger is completed, do not take the statistics directly. It is recommended to check one by one according to the following items.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Is the number of rows correct? | Add the number of rows in each original table and compare it with the number of rows in the summary table | If the number of rows is too small, it means that a table or area has been left out; if there are too many rows, it means that the header or blank line has been mixed, and then go back to the corresponding table to check the range. |
| Is there only one row in the header? | Use filtering to see the values of each column and whether the header text accidentally appears in the middle of the data | Delete the middle duplicate header row, and then check the replication range of the table |
| Whether the total figures are duplicated | Check to see if there are rows of "totals" and "totals" at the end or middle of the data | Delete the total row and ensure that the total is calculated only once where needed |
| Whether the number can participate in the calculation | Sum the amount column to see if the result is consistent with the original table; see if the numbers are aligned to the left | The number on the left is usually in text format and needs to be converted to a numeric value before summing |
| Is the date uniform? | Sort the date column to see if there are any text dates that cannot be sorted | Uniformly convert the text date to the date format, and then reorder |
| Whether the source information is complete | Select a few lines at random to see if the source worksheet corresponds to the original table. | Fill in the blanks to make sure that the source name of each data block is correct |
If you need to view, check, or continue to organize this form after the merge, you can use the UniCell table in the OmniDoc Desktop client (download.omnidoc.top) to compare the original data table by table. After opening the results, the main items are still checked above, and the tools only help you to view and compare them more easily.
# Core comparison
There are three common ideas for merging multiple worksheets, each of which is suitable for different scenarios. The following table compares them together.
| Method | An appropriate situation | Major limitations |
|---|---|---|
| Manually copy append | Few worksheets, consistent structure, merge only once | It has to be redone every time there is a change, and it is easy to miss the line when there is a large amount of data. |
| Query append | Worksheets are numerous, need to be updated regularly, and are structurally fixed | It takes time to set up the previous stage, and the names of each table are required to be strictly consistent. |
| Formula or PivotTable Summary | Only aggregate or classified statistics are needed, no details are needed. | You can't do arbitrary screening and line-by-line checking like merging details. |
Specifically, manual replication is quick to add, and no additional settings are needed, but the result is static and will not be updated automatically after the original table is changed. The query appends tables suitable for long-term maintenance, and the new results can be obtained by refreshing the original table after modification. The formula or PivotTable calculates the total directly, which is suitable for scenarios as long as the conclusion is reached.
When making a choice, you can first ask yourself: do you need to keep each line of details? Will the data be updated? Is the structure completely unified? The answers to the three questions can basically determine which way to use.
# which method is suitable for you?
- make an one-time monthly summary with a small number of tables and a fixed structure : you can copy and add it manually, and it will be enough to check it after the merger. - merge new tables every month, and the number of tables continues to increase : it is recommended to use query append to fix the template with a uniform structure, and then just refresh it. - only care about the total amount of each department : it is easier to summarize with PivotTable or formula, and it is not necessary to keep all the details. - the table structure varies greatly, and the column names do not match *: do not rush to merge, but should first unify the structure; if you need to match by number, you should match by key fields instead of stacking them directly.
# frequently asked questions (FAQ)
1. What if the header repeats after the merge?
This is usually because the first row of each table is selected when copying. Filter the row where the header text is located and delete it, and then select it from the next row in the header when copying.
2. Can the worksheet name be automatically written into each line?
When copying manually, you need to fill in the "Source worksheet" column by yourself, and it is easy to make mistakes when the table is too many. When the query is appended, you can keep the fields corresponding to the worksheet name during the merge process, and then sort them out later.
3. What is the reason for the incorrect result of the sum of numbers after the merger?
There are two common reasons: one is that some of the numbers are in text format, and the other is that they are mixed with total lines. First check whether the numbers are aligned to the left, and then see if there is a total at the end of the data, and then sum it after processing.
4. Can only some columns be retained when merging?
Sure. Delete unwanted columns before merging, or select only the desired fields in the query before stacking. Before deleting a column, make sure that other tables have the same column name to avoid missing fields.
5. Can the merged data still be linked with the original table?
The data copied manually is static data, and the original table will not be updated automatically after modification. Query appends can be refreshed to get new results, but you still need to make sure that the location of the data source has not changed.
# Summary
To merge multiple worksheets into one table in Excel, the key is to unify the header and structure first, then choose the way to copy and append or query the append, and finally check the number of rows, totals, numbers and dates item by item. Doing in this order can not only avoid missing lines and mislocations, but also facilitate the maintenance of the summary table in the future.
