How to split data in Excel table?

php editor Baicao teaches you how to split data in Excel! Sometimes we need to split a large Excel table according to a certain column or a certain rule, which makes data analysis and management easier. In Excel, you can easily split data and improve work efficiency through functions such as filtering, column sorting, and pivot tables. Next, we will introduce the specific operation steps in detail, allowing you to easily cope with various data splitting needs.

The editor takes the following data as an example to see how to split the data.

First, select the cell where the number is located, and then click the "Data" > "Column" option in the menu bar;

.

After the [Text Column Wizard-Step 1] dialog box pops up, select "Delimiter" and click "Next";

.

After the [Step 2] dialog box pops up, check the "Other" option and fill in the delimiter in the right box. For example, if the delimiter for data is a comma, fill in a comma, and if it is a period, fill in a comma. , and then click "Next";

.

After the [Step 3] dialog box pops up, you can select "Column Data Format" as needed, and then click "Finish" after checking that "Data Preview" is OK.

.

After completing the above operations, you can see that the data has been split into separate cells. If you want the data to be arranged vertically, we can also set it by "inversion".

First, copy the horizontal data;

.

Then, select the first cell you want to arrange vertically, right-click the mouse and select "Paste Special";

.

After the [Paste Special] dialog box pops up, check the "Invert" option, and then click "OK".

.

You can see that the original data has been arranged vertically.

.

If you find that the "Split Data" option is gray and cannot be clicked when setting "Split Data", it is not that the setting cannot be made, but that the Excel table has been set to "Restrict Editing" and no changes can be made.

We only need to cancel the protection of the table and then we can set it.

.

To cancel the "Restrict Editing" of the Excel table, you only need to click the "Review">"Cancel Worksheet Protection" option in the menu bar, then enter the originally set password in the pop-up dialog box, and click [Confirm] , the protection of the Excel table will be revoked, and the content can be changed or edited.

.

Of course, if you forget your password, you cannot revoke the "restricted editing" of the Excel table, because there is no option to forget the password in the Excel table.

In this case, we might as well use other tools, such as the tool used by the editor, which does not require a password and can directly remove the "restricted editing" of the Excel table.

Just select the [Unrestriction] module in the tool, and then import the restricted Excel table;

.

The Excel table after the restrictions are lifted will be saved as a new table. Click [Go to View] to open the folder to see it.

The above is the detailed content of How to split data in Excel table?. For more information, please follow other related articles on the PHP Chinese website!