Practical Excel skills sharing: batch creation/splitting of worksheets, batch renaming of worksheets

At work, we often need to collect statistics on employee data. For example: you need to use Excel to count the attendance and rating status of each employee, and the data of each employee is stored in a worksheet. At this time, if you manually create a named worksheet, it may take a lot of time. Today I will share with you two tips: batch creation/splitting of worksheets and batch renaming of worksheets.

1. Create/split worksheets in batches

As shown below, first create the The worksheet name is entered in the cell of column A. The case is for 8 employees. A rating sheet and an attendance sheet are created for each employee.

Click on any cell in the data source and click the [Data] tab - [Pivot Table]

# #Enter the PivotTable creation page. The PivotTable area will select the data area in column A by default, then check [Existing worksheet], select a blank cell location, and click OK.

After the Pivot Table is successfully created, the Pivot Table fields will be displayed on the right. Select the "Worksheet Name" in the field with the mouse and drag it to the "Filter" range below. .

Then select the [PivotTable Tools-Analysis] tab, click [Options] and then select [Show Report Filter Page]

Enter the following page and directly select [Worksheet Name] and click OK

You can see that the worksheet has been created, and then you can add the Sheet1 table Deleted. (Looking at it from another angle, this is actually how to split worksheets in batches!)

But we will find cells A1-B1 of each worksheet created They all have pivot table headers, which we don’t need. Do we have to delete them one worksheet at a time? Of course not, there is a faster way, right-click on any worksheet name and click [Select All Worksheets], then all worksheets will be selected.

Select cells A1:B1, click the [Start] tab - [Clear] - [Clear All] to delete A1- in all worksheets in batches In the B1 cell area, we can then enter data at will.

We have created the worksheet, but sometimes we need to change the names of multiple worksheets (with unified rules) during work. Changing one by one is too time-consuming. Then you can use the following method.

2. Batch rename the worksheet

As shown below, you need to change the worksheet with the suffix name "Grade Sheet" to "Assessment" surface". (Right-click the lower left corner of the table to view all worksheet names)

Save the worksheet to the desktop and close it, right-click the excel file and click [Rename]. Change the extension xlsx to rar to become the file format of the compressed package.

#When changing, press Enter and a prompt as shown below will pop up. Just click OK.

Double-click the renamed file, open the compressed package, and click to enter the xl folder.

Select the workbook.xml file and drag it to the desktop with the mouse.

Right-click the workbook.xml file and select [Notepad] to open it

In Notepad, Click [Replace]

under [Edit]

In the pop-up dialog box, after searching for the content, enter "score table" and replace it with "evaluation table". Click the [Replace All] button on the right and close it. Press Ctrl s Save and close Notepad.

Drag the saved workbook.xml file back into the compressed package. When prompted, click OK as shown below.

#After clicking OK, you will be prompted that the following files already exist. Click "Replace" directly.

Close the open compressed package file, right-click the compressed package file, click "Rename", and change the extension back to xlsx to become an excel file Format.

Double-click to open the file. At this time, you can see that the name of the worksheet has been successfully changed from the original [Grade Sheet] to [Evaluation Sheet].

That’s it for today’s tutorial. Have you learned these two worksheet batch operation skills? Students, open excel and try it out!

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: batch creation/splitting of worksheets, batch renaming of worksheets. For more information, please follow other related articles on the PHP Chinese website!