6 tips for batch processing in excel, saving 80% energy.

February 14, 2022

A free Office suite fully compatible with Microsoft Office

Free Download

Many people face the headache of inefficient and repetitive work while dealing with large volumes of data in a table. Today, we will learn how to process data in batches.

· 01 Automatic summation in batches

The SUMfunction is commonly used in summing cells in different rows or columns or specified areas.

Now, I'll teach you the tipfor automatic summation.

1)  Select the range that needsto be summed (including the range that concludesthe result column or row)

2) Press Alt+

One second to get the sum, that's how awesome it is!

· 02 Change the picture size in batches

If the pictures added to the table are of different sizes, most people will choose to compare each picture with the naked eye and then adjust the picture size manually. It is a costly undertaking, as you can't guarantee the same size of all pictures in this time-consuming process. Thats where WPS Spreadsheet can help.

1) Press Ctrl+G to activate the Go To window. Select Objects and click Go To.

2) Head to Picture Tools, where you can enter the Height and Width of these pictures in batches.

· 03 Delete blank rows in batches

When facing blank lines in a table, I guess you are still deleting one row at a time. It won't take much time to delete a few rows, but if there are hundreds of rows of data, your will definitely get dizzy. Why not choose a simpler way?

1) Select the range that contains the data.

2) Press Ctrl+G to activate the Go To window. Select Blanks and click Go To. Then right-click any blank row > Delete > Entire Row.

Here, we are easily getting rid of the extra blank rows, so struggling tolocate the blank lines is not necessary anymore.

· 04 Add email suffix (domain part) in batches

A complete mailbox consists of user name and domain name. Employees' mailbox suffix of WPS Office is @wps.cn. How can we turn this employee list into a list of mailbox?

1) Select the range that contains the data.

2) Right click the chosen area, click Format Cells > Custom.

3) Enter @@wps.cn at Type, click OK.

· 05 Delete duplicate rows in batches

People often spot the differences in a pile of data. Unfortunately, it happens that we remember the former one, but forget the latter one, even delete the wrong non-duplicate data. How can we prevent this?

In fact, WPS Spreadsheet can help you find differences and remove duplicates. You only need to:

1) Select the range that contains the duplicate items.

2) Click Data > Highlight Duplicates > Set > OK. By doing so, you can see the duplicate data highlighted in orange.

3) To delete them, select any cell in this table, then click Highlight Duplicates > Remove Duplicates > check Material name only > Remove Duplicates > OK.

· 06 Extract information in batches

To extract information fromthe mixed data, here is a smart way to separate them quickly, you don't need to copy and paste them one by one.

1) Hover over the contents of the first row, then press Ctrl+E.

2) To adjust the width of column C, double-click the border between column C and column D.

As you can , as long as you confirm that the information and format of the first row are correct, the remaining data can be formatted as you set.

Was this helpful?