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

February 14, 2022
1,275 Views 0

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 SUM function is commonly used in summing cells in different rows or columns or specified areas.

Now, I'll teach you the tip for automatic summation.

1)  Select the range that needs to be summed (including the range that concludes the result column or row)

2) Press Alt+

Automatic summation.gif 

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.

delete blank.gif

Here, we are easily getting rid of the extra blank rows, so struggling to locate 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.

Add email suffix.gif 

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

Add email suffix (2).gif 

· 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.

Delete duplicate rows in batches.gif

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

Delete duplicate rows in batches (2).gif

· 06 Extract information in batches

To extract information from the 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?