Teach you a good way to Excel without & quot; copy and paste & quot; split table

The original title: "never copy and paste" to split the form, teach you a good way! "

All the data is recorded in one table, and now it needs to be separated by category. Is there any good way to do this?

As shown in the following figure, all the achievements from sales one to sales seven are all in one table. now we split the data in the table into seven worksheets and name them automatically.

1, convert to PivotTable We first select the table, then click "insert"-"Table"-"PivotTable", and click the "OK" button in the pop-up "create PivotTable".

2. Set the field to drag the categories that need to be split to the "filter", and all the others to the "row".

For example, here I need to split by "department", I drag the "department" to the "filter", and others outside the department to the "line".

3. Set up the table layout 1, enter "Design"-"layout", and select "display in tabular form" and "repeat all project labels" in "report layout".

2. Select "do not show categorized totals" in "classified Summary".

3. Select disable rows and columns in totals.

4. Generate multiple worksheets after selecting the table, enter "Analysis"-"PivotTable"-"options"-"Show report filter page"-"OK". At this time, the tables have been classified and generated into their respective worksheets.

5, PivotTable to ordinary table We use the Shift + left button, select all the worksheets, to set it uniformly. Click on the triangle in the upper left corner, you can select the entire table, copy the contents first, then paste as "value", and finally delete the first two lines. At this point, the PivotTable becomes a normal table.

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Wang Wangqi