# Core comparison - Excel & data

#How to merge multiple tables into one pivot table

Key points of this article

When the table headers are consistent, you can use Power Query to append queries to merge multiple tables into one total table. When the source data is updated later, you only need to refresh.
Before merging, check whether each table has no merged cells, no subtotal rows, and the first row is the table header, otherwise it will be misaligned after the merge.
Add a "source" column when merging, and the pivot table can be classified and summarized according to the original table, without having to go back and check again.
After generating a pivot table, you can quickly find missing tables or extra header rows by comparing the sum of the row numbers of each source table with the total number of rows.
Tables in scanned documents or PDFs can be OCRed first and then exported to Excel, and then merged using the same method.

If you have a twelve-month sales table, or each department has submitted a report, and you want to use a pivot table for classification and summary, you are often stuck in the first step: a pivot table can usually only reference a continuous data area, and multiple independent workbooks or worksheets cannot be directly selected together. Most tutorials on the Internet only talk about how to do pivoting on a single table, and rarely talk about the step of "first merging multiple tables into one data source".

This article is written for people who need to summarize and analyze multiple Excel files or multiple worksheets. After reading, you will be able to judge which merging method is suitable for your own table, follow the steps to sort out a general table, and then generate a pivot table; you will also know how to check the results, and how to quickly find out how many pieces of data there are in several tables.

How to merge multiple tables into one pivot table? One sentence conclusion

It is a safe approach to unify the structure first and then merge them by appending them. The table below gives recommendations based on common situations.

your situation	Recommended practices	reason
The column names and order of several tables are exactly the same	Use Power Query to append queries and merge them into a total table	Set once, then refresh to update
The table structure is the same, but scattered in multiple files	Put the files into the same folder, import from the folder and append	Add new files and put them in
Column names are the same but in different order, or there are several columns missing	Adjust the columns in the query first, then append	Align by column name to avoid misplacing values into other fields
Only the total numbers from two or three tables are needed	Use the "Consolidate Calculation" function	Few operations, no need to establish data source
The form was originally a scan or PDF	First OCR and export to Excel, then merge according to the above method	Exported tables can be calculated and pivoted

Before you start: Confirm these points first

Taking a few minutes to check before you start can avoid rework after the merge.

Whether the headers are consistent. Additional merge is aligned by column name, "Sales" and "Sales Amount" will be treated as two columns. Unifying the names before the merger is easier than revising them afterwards.
Each table is not a canonical data area. Merged cells, subtotal rows inserted in the middle, and titles occupying several rows will cause the merged data to be misaligned. It is recommended to keep only one row of headers for each table, with all the details below.
Whether the source needs to be recorded. If you want to classify by month or department in the future, add a "source" column when merging, such as "March" and "Eastern China". This column is the basis for the pivot table's classification and summary.
Whether the data type is correct. The date column must really be a date, and the numeric column cannot be mixed with text. The type is wrong, the pivot table will treat the date as text, and the sum result will be zero.
The source file is not a PDF or a scan. If the table is just an image or a table in PDF, it needs to be OCR recognized first. The PDF editor of the OmniDoc desktop client can perform OCR on scanned PDFs and export them to Excel or Word. The recognition results must be checked item by item, and digital recognition errors will directly affect the summary.

Steps to append and merge multiple tables and generate a pivot table in Excel

The following uses Excel's built-in Power Query as an example, which is suitable for multiple workbooks or worksheets with consistent headers.

Put all source files into the same folder, or confirm that all worksheets are in the same workbook, and check whether the file names are regular.
Create a new blank workbook, find the entrance to obtain data in the data ribbon of Excel, and choose to import source data from a folder or workbook.
Enter the Power Query editor and check the header, column order and data type of each table one by one. If any inconsistencies are found, correct them here first.
Use the append function in the query editor to add the tables that need to be merged in order to generate a merged total table.
Add a "Source" column to each row to record which file or worksheet the data comes from.
Check the types of date, amount and other columns, confirm that they are correct, close the editor, and upload the results to the worksheet or data model.
Select the merged summary table, insert the pivot table, drag the category field to the row or column in the field list, and drag the numerical field to the value area.
If the source data changes in the future, just right-click to refresh without having to copy and paste again.

If there are few tables and the structure is simple, you can also directly copy each table to a continuous area of a worksheet, leaving a row of headers at the top and marking it with the "Source" column. This method is suitable for one-time summary, and subsequent updates will be more troublesome.

Check each item after completion

After the pivot table is generated, do not report it directly. Check the items one by one according to the table below.

Check item	How to see	What if there's a problem?
Total number of rows	The number of detail rows of each source table is added and compared with the total number of table rows	When inconsistent, check to see if a table is omitted to be appended, or if the header row is treated as data
Source distribution	Count rows by "source" in the PivotTable, and each source should appear	A source is missing, mostly because the file has not been imported or the worksheet name is misspelled
Numerical total	Sum the amount of each source table separately, and then compare it with the total table	When the difference is large, see if there are numbers in text format that are not counted.
Date range	Sort by date field to see if the early and late dates are reasonable	1900 or blank indicates that the date column is of the wrong type and needs to be converted in the query
Blankness and dislocation	Filter blank rows to check whether the column contents are strung to adjacent fields	Usually the column order is inconsistent, so go back to the query and realign the column names.

There is a simple way to check: write down the number of rows of each table in the source table, and the total number of rows after merging should be equal to their sum. This comparison is very fast, and most of the problems of missing meters can be found.

There are four common practices for merging multiple tables, each with its own emphasis.

Method	A suitable scene	Major limitations
Copy and paste into a table manually	Less watch, only do it once.	It is easy to make mistakes when the source data is updated.
Power Query append query	The structure of the table is consistent and needs to be updated repeatedly	It will take some time to set up for the first time
Combined calculation	You just need to sum by position or category.	Cannot flexibly classify and summarize by multiple dimensions
Perspective of data model plus multi-table	The relationship between multiple tables needs to be analyzed.	The operation is complicated and suitable for skilled users.

# frequently asked questions (FAQ)

1. Why refresh PivotTable after merging?

The merged summary table is imported from the source data, and the PivotTable reads the summary table. If the source data is changed, the total table and PivotTable will not change automatically. You need to refresh the query first, and then refresh the PivotTable.

2. How to quickly find out how many pieces of data there are in several tables?

Enter the formula in a blank cell and add up the data columns of each table. For example, each table A column is a detail that starts at the second row, and you can use = COUNTA (Table 1)-1 plus = COUNTA (Table 2)-1. The result is the number of detail rows in both tables. Note that column A does not have blank cells, otherwise the statistics will be too low.

3. What should I pay attention to when converting websites online and merging forms?

First look at the file size limit and whether there is a watermark, and then look at the privacy policy to confirm whether the uploaded data will be retained. Also check that the exported table retains the number format and date format. Tables involving sensitive data should be processed locally as far as possible.

4. If there is something wrong with the merged data, how to judge which step caused the problem?

It is checked item by item according to the order of "total number of rows, source distribution and numerical value". If the number of rows is wrong, most of the numbers are recognized as text; if the number of rows is incorrect, the table is usually missed or the header is treated as data. Important numbers should be manually sampled and checked for the contents identified or converted by the machine.

# Summary

PivotTable itself is not difficult, but it is difficult to merge multiple tables. First unify the header, use the additional query or merge calculation to sort out the data into a general table, and add the source mark when merging, and then generate the PivotTable for classification and summary. After the completion of the check with the number of rows and the total, you can rest assured that you can use this PivotTable.

Open OmniDoc and process your form file