More features in Split and Merge

Uploaded time: December 29, 2021 Difficulty: Beginner

More features in Split and Merge

More features in Split and Merge

More features in Split and Merge

In our daily work, we often need to split or merge worksheets.

Most people use an easy way of copying and pasting, but it is a waste of time.

 

In Microsoft Excel, we may need plug-ins, VBA or Power Query, etc., to split or merge workbooks.

For convenience, WPS Spreadsheets provides the function of splitting and merging workbooks.

 

First, let's learn about the splitting function. WPS Office provides three ways of splitting.

· Split Sheets By Content

Take this workbook as an example, which displays the sales of a company's employees. We want to split it into different worksheets by the month.

1. Click the Data tab, click the Split Sheet drop-down button, and select Split Sheets By Content.

2. Check My data has headers in the pop-up dialog and select Column D in the Split basis area.

_1.gif


3. We can choose to save the split worksheets as different new files or save them as different new sheets. Here we check Different new sheets and click Start.

_2.gif


· Split Books By Sheet

How can we split this sales workbook into separate worksheets?

1. Select Split Books By Sheet, and in the pop-up dialog, we check the workbook to be split, select the save path, and click Start.

2. Then the workbook is split into different files.

_3.gif


· Split Book

Similar to Split Books By Sheet, Split Book is to split a worksheet into different workbooks, but at the same time, it can also customize the range to split.

 

For example, there are 6 worksheets in this workbook. We want to split it quarterly, with worksheets 1-3 as one workbook and worksheets 4-6 as another.

1. Select Split Book, and select the file to be split in the pop-up dialog.

2. Click Range selection, enter 1-3,4-6 in the text box and click Split.

_4.gif

 

Then let's learn about the function of merging.

WPS Spreadsheets provides 6 ways of merging, namely, Merge multiple worksheets into a worksheet, Merge worksheets with the same name in workbooks, Merge Range Data, Merge multiple workbooks into a workbook, Recombine Same Name, and Merge Book.

_5.gif

 

Here we focus on three commonly used ways.

· Merge multiple worksheets into a worksheet

Taking this table as an example, we want to merge the sheets of January and June into one worksheet.

1. Click the Merge Sheet drop-down button, select Merge multiple worksheets into a worksheet, and check January and June.

2. To avoid headings duplication, we select 2 in Merge from the row and click Start merging.

3. A moment later, a newly merged workbook is generated with a report worksheet and a merged data worksheet.

_6.gif

 

· Merge worksheets with same name in workbooks

 We collected the grades of each class in each subject. To facilitate the analysis of grades, we need all the grades of each class in the same subject aggregated into one workbook.

1. Select Merge worksheets with same name in workbooks, and in the pop-up dialog, click Add file to add the needed files to the merging dialog.

In this way, the worksheets with the same name will be merged.

2. Check Mathematics, Physics, and Chemistry. 

3. Select 2 in Merge from row and click Start merging. 

A moment later, a new merged workbook is generated with a report worksheet.

We can click the data in the report to jump to the merged data worksheet. Here we can see the detailed information of each class in the Mathematics subject.

_7.gif


· Merge multiple workbooks into a workbook

If we want to merge two workbooks, select Merge multiple workbooks into a workbook, click Add file to add the needed files, and then click Start merging.

Later, the two workbooks are merged.

_8.gif

 

These are some features of Split and Merge in WPS Spreadsheets, which provides personalized settings to meet the diverse needs of users.