In the last month of 2022, no matter what your job may be, you can't escape from the year-end data reporting. The ability to collate summary data without making mistakes is one of the essential skills for every employee!
When I was a newbie in Spreadsheets, my leader asked me to summarize the year-round sales data into a single table.To impress him, I spent the whole afternoon copying and pasting the sheets, which meant a total of 300+ sheets for a year.Actually, it only takes 30 seconds to complete data merging with the right skills. Want to know how to do it?
Scenario 1: Except for the data inside, the format of all the data tables are the same
Steps:
- Click Data > Merge Sheet. WPS provides 6 different merging schemes and we choose Merge multiple Worksheets into a worksheet.
- Click Add File and select the data tables needed in the pop-up dialog.
- Note: You need to make adjustments at Merge from row. The value to be filled here depends on the number of rows of headings in your data table. (Mostly 1 row, some have 2-3 rows.)
After the settings are complete, click Start merging, then all worksheets are merged into one workbook!
Next, you can easily make data comparison and analysis comprehensively. If you also know the rules of pivot tables, I'm sure it will make your workflow easier!
Scenario 2: Each data table has subtle difference in header row fields and the number of rows and columns.
Steps:
- In this case, we recommend you use the Consolidate feature!
- Click Data > Consolidate and select the calculation method such as SUM, Count, or Average in the pop-up dialog.You can also select the data area to be merged at the Reference.
- Don't forget to check the Top Row and Left Column, then click OK. Now multiple data tables are calculated in summary. However, this method also has some limitations. For example, it is more suitable for lightweight data aggregation, but not convenient for data aggregation of more than ten data books.
Of course, there are many more ways to do data merging, so if you know any easier ways, please share with us!
