WPS Spreadsheets: Create a Line Chart from Data on Multiple Sheets
Question details
Create a line chart in WPS Spreadsheets using data stored across multiple sheets, even when the source categories are text labels.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Weekly values are stored on separate sheets, and categories such as Bronze, Silver, and Gold need to appear as chart labels on the vertical axis.
- Observed behavior
- The chart source must use numeric scores for the vertical axis, while a custom number format displays 1, 2, and 3 as Bronze, Silver, and Gold.
Confirm that each worksheet uses consistent cell locations or lookup keys for the weekly data, and note the exact sheet names. Create a small mapping table that assigns Bronze, Silver, and Gold the numeric values 1, 2, and 3.
Build a numeric helper range and format the line chart labels
Use this method when category names are text and the chart data is distributed across multiple WPS Spreadsheets worksheets.
A line chart requires numeric values for its vertical axis. Keep the original category names in the source sheets, then create a chart-source range that converts those names to scores and uses a custom number format to show the original labels.
On a helper sheet, enter Bronze, Silver, and Gold in cells A2:A4 and enter 1, 2, and 3 in cells B2:B4. Select the range and use the mapping table as the lookup source.
Create columns for Week, Source Sheet, and Numeric Score. Enter each week and its corresponding worksheet name, then leave the score column for the lookup formula.
In the Numeric Score column, use a formula such as =VLOOKUP(INDIRECT("'"&$B2&"'!$A$2"),Mapping!$A$2:$B$4,2,FALSE), replacing the referenced cell and Mapping range with the locations in your workbook. Fill the formula down for all weeks.
Select the Week column and Numeric Score column in the helper range, open the Insert tab, choose Chart, select Line, and confirm the chart placement.
Right-click the chart's vertical axis and choose Format Axis. Open Number or Custom, enter [=1]"Bronze";[=2]"Silver";"Gold" as the number format, and apply it.
Create Multi-Sheet Line Charts with Numeric Category Labels in WPS Office
WPS Spreadsheets lets you combine worksheet data in a helper range, use VLOOKUP and INDIRECT to retrieve values, insert a line chart, and apply a custom axis format that displays numeric scores as Bronze, Silver, and Gold.
- 1. Prepare the worksheet data: Open WPS Spreadsheets and verify the sheet names, lookup keys, and weekly value locations.
- 2. Create the helper range: Add a mapping table for Bronze, Silver, and Gold, then use VLOOKUP with INDIRECT to convert the multi-sheet data into numeric chart values.
- 3. Insert the line chart: Select the helper range, choose Insert, select Chart, and pick the Line chart type.
- 4. Format the vertical axis: Open Format Axis, choose a custom number format, and enter [=1]"Bronze";[=2]"Silver";"Gold".
Frequently Asked Questions
Why must Bronze, Silver, and Gold be converted to numbers for a line chart?
A line chart plots numeric values on its vertical axis. Map Bronze, Silver, and Gold to 1, 2, and 3, then use a custom number format to display the original labels.
How can I retrieve values from different worksheets in WPS Spreadsheets?
Use INDIRECT to build a reference from a sheet-name cell, then use VLOOKUP to find the required week or category in that worksheet. For example, use a formula such as =VLOOKUP(INDIRECT("'"&$B2&"'!$A$2"),Mapping!$A$2:$B$4,2,FALSE) and adjust the ranges to match your workbook.
What custom number format displays the three chart categories?
In Format Axis under Number or Custom, enter [=1]"Bronze";[=2]"Silver";"Gold". Values 1 and 2 display as Bronze and Silver, while the remaining value displays as Gold.
Can I create the chart directly from the original text labels?
For a line chart, use a numeric helper range instead of the text labels. The helper range allows the chart to plot the values while the custom axis format preserves the Bronze, Silver, and Gold display.
