WPS Spreadsheet: Create a Run Chart with Trend Lines and Bars
Question details
The user needs to create a combination run chart that compares monthly issue counts with percentages, averages, thresholds, rolling averages, and calculated scores.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The source data contains Critical, Major, and Minor issue records that must be visualized with bars and multiple line series on appropriate scales.
- Observed behavior
- The source data includes a #SPILL! error and a VLOOKUP error, preventing reliable chart creation and scoring calculations.
Review the source table for #SPILL! and VLOOKUP errors before selecting chart data. Confirm that each month has a complete value for issue counts, percentages, averages, thresholds, rolling averages, and scores.
Fix the source data and insert a combination run chart
Use this approach when errors in the source table are preventing the chart from displaying accurate bars and trend lines.
Resolve the calculation errors first so the chart does not contain missing or incorrect points. Use columns for issue counts or scores and line series for percentages, averages, thresholds, and rolling averages.
A secondary axis is useful when counts and percentages have significantly different value ranges.
In WPS Spreadsheet, select the cell showing #SPILL!, review the highlighted spill range, and clear any occupied cells or merged cells blocking the formula result.
Select the cell showing the VLOOKUP error and use the Formula Bar to check the lookup value, table range, column index, and exact-match argument; correct the source reference or lookup key, then press Enter.
Check the Critical, Major, and Minor issue counts, percentages, thresholds, rolling averages, and calculated scores for each month. Confirm that the scoring criteria produce the expected values before charting.
Select the Month column and the required series columns, including issue counts or scores, percentages, averages, thresholds, and rolling averages.
Open the Insert tab, choose Chart, select Combo or Combination, assign counts or scores to Column and the percentage, average, threshold, and rolling-average series to Line, then place percentage-based series on a Secondary Axis.
Format the run chart for clearer trend analysis
Use this option after the chart is working to distinguish issue bars, trend lines, thresholds, and rolling averages.
Build Accurate Combination Run Charts in WPS Spreadsheet
WPS Spreadsheet can combine columns and lines in one chart, assign series to a secondary axis, and format percentages, averages, thresholds, rolling averages, and issue scores for clear monthly analysis.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the monthly issue data.
- 2. Repair the source formulas: Select the #SPILL! and VLOOKUP error cells, use the Formula Bar and source ranges to correct the formulas, and verify the resulting values.
- 3. Select the monthly series: Highlight the Month column together with the Critical, Major, Minor, percentage, average, threshold, rolling-average, and score columns needed for the chart.
- 4. Insert a combination chart: Choose Insert > Chart, select Combo or Combination, and assign count or score series to columns and percentage-based series to lines.
- 5. Set the secondary axis: Open the chart series settings and place percentage or differently scaled series on the Secondary Axis, then format the chart and verify its values.
Frequently Asked Questions
Why should I fix #SPILL! before creating the run chart?
A #SPILL! error prevents a dynamic formula from returning all of its results. Select the error cell, clear the highlighted spill range, and confirm that the complete monthly series appears before selecting chart data.
How do I show issue counts as bars and percentages as lines?
Select the month and relevant data columns, choose Insert > Chart, select Combo or Combination, assign issue counts to Column, and assign percentage series to Line. Put percentage lines on the Secondary Axis if their scale differs from the counts.
How can I add a rolling average to the chart?
Create or verify a rolling-average column beside the monthly data, include that column when selecting the chart range, and set its series type to Line in the chart's combination settings.
What should I do if VLOOKUP returns an error in the source table?
Check the lookup value and table range in the Formula Bar, make sure the lookup key exists in the first column of the selected range, verify the column index, and use FALSE for an exact match when appropriate.
Which chart axis should display thresholds and percentages?
Use the primary axis for counts or scores and the secondary axis for percentages, averages, thresholds, or rolling averages when their numeric ranges differ. Keep related line series on the same scale for accurate comparison.
