WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android


How to consolidate data in WPS Spreadsheet

Uploaded time: August 31, 2021 Difficulty Beginner

How to consolidate data in WPS Spreadsheet

How to consolidate data in WPS Spreadsheet

In daily work, we often need to summarize and calculate some related data. 

By using the Consolidate feature, we can quickly calculate the data in multiple worksheets. This feature can be also used in Microsoft Office Excel and Openoffice. Now let's learn it together!

Take the following table as an example. You can see that there are sales data from different staff. Now we want to count the total sales volume of all employees.  

1. Create a summary sheet in Spreadsheet.

2. Select cell A1 in the summary sheet, and click the Data tab-and then the Consolidate button in turn.  After the dialog box pops up, select a calculation method in the function drop-down list. Since we want to calculate the total sales volume, so we choose Sum.

3. Click Reference, and select the needed data areas in different sheets.

4. Click the Add button, then add the data areas that need to be merged in different sheets to the All References Locations list box.If you want to delete the added data area, just click the Delete button on the right.

5. Also, we need to remember to check the Top row and Left column of the Label location. If not doing so, WPS Spreadsheet would only count the values, while the salespersons' name could not be matched.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.