Collect Totals from Multiple Worksheets and Create a Chart in WPS Office Spreadsheets
Question details
The user needs to aggregate totals stored on multiple worksheets into one helper table and use that table as the source for a chart.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains totals on separate worksheets, and the user wants to compare those totals visually.
- Observed behavior
- A summary table lists each worksheet name and its total, and a chart uses the worksheet names as categories and the totals as values.
Open the workbook that contains the worksheets you want to summarize. Save the workbook before using worksheet-name formulas such as CELL("filename",A1), because those formulas need a saved file path to return a valid sheet name.
Create a Summary Table with Worksheet Names and Linked Totals
Use this method to build a reusable summary that updates automatically when the source totals change.
Create a helper sheet with Worksheet and Total columns, then link each Total cell to the matching source worksheet. This keeps the chart tied to the source data and avoids manually retyping totals.
Open WPS Office Spreadsheets, click the plus icon next to the sheet tabs, and name the new sheet Summary.
In column A, type the names of the worksheets that contain the totals. You can enter the names manually or reference each source sheet cell directly if you need a more automated approach.
In column B, enter a reference to each total cell, such as ='Data2001'!B10. If the source data is an Excel Table with a totals row, use =Data2001[[#Totals],[Value]].
Select the Summary range, including the headers, then click Insert > Chart and choose Line or Column.
Use the Chart Tools in WPS Spreadsheets to change the chart title, series, and axis labels so the worksheet names appear as categories and the totals appear as values.
Manually Enter Totals on a Summary Sheet and Create a Chart
Use this quick method when you have only a few totals and do not need the chart to update automatically.
Create a Summary Chart from Multiple Worksheet Totals in WPS Spreadsheets
Use WPS Office Spreadsheets to link totals from several worksheets into a helper table and insert a line or column chart. The process is free, lightweight, and fully compatible with Microsoft Excel .xlsx files.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the separate worksheets.
- 2. Create a Summary sheet: Click the plus icon at the bottom sheet tab bar, then rename the new sheet to Summary.
- 3. Link worksheet totals into the summary: Type the worksheet names in column A and reference the total cells in column B, using formulas such as ='Data2001'!B10 or =Data2001[[#Totals],[Value]].
- 4. Insert a chart: Select the summary range, click Insert > Chart, and choose the Line or Column chart type that best compares the totals.
- 5. Format and save: Use Chart Tools to add a title, adjust axis labels, and then save the workbook to retain the chart and links.
Frequently Asked Questions
Can I collect worksheet totals without manually typing every sheet name?
If your spreadsheet version supports it, you can use =TEXTAFTER(CELL("filename",A1),"]") after saving the workbook to return the current worksheet name. For other worksheets, you still need to reference or list their names unless you build a more advanced formula or macro.
Why does my chart not update after I change a total on another worksheet?
Check that the Total column in your summary sheet uses direct cell references or table totals-row references, not typed numbers. If the totals are typed, the chart will not update automatically.
Can I plot totals row values from Excel Tables into a WPS chart?
Yes. If each worksheet contains an Excel Table with a totals row, reference the total with a formula like =Data2001[[#Totals],[Value]] in the helper table, then select the helper table and insert the chart.
Which chart type should I use for worksheet totals?
A clustered column chart works well for comparing totals across worksheet categories. A line chart is useful when the worksheets represent a sequence such as years or months.
Do I need to save the workbook before using a worksheet name formula?
Yes. CELL("filename",A1) depends on a saved file location, so save the workbook first. If the file is unsaved, the formula may return blank or an error.
