How to Recreate a CDC-Style 100% Stacked Chart in WPS Office Spreadsheet
Question details
The user needs to reproduce a CDC-style wastewater surveillance graphic in WPS Office Spreadsheet using a 100% stacked area chart for percentage categories and a line series for a total or reference measurement.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has percentage category data by reporting period and a separate total or reference measurement to plot as an overlay line.
- Observed behavior
- The final chart should display each reporting period as a 100% stacked area while the total or reference measurement appears as a line, optionally on a secondary axis.
Arrange your data in WPS Office Spreadsheet with dates or reporting periods in the first column, one column for each percentage category, and a separate column for the reference or total series. Make sure every series has a clear header because the chart legend and axis labels use these names.
Create a 100% Stacked Area Chart and Add a Reference Line on a Secondary Axis
Use this method when the reference measurement uses a different scale or unit from the percentage categories, which is typical for CDC-style wastewater surveillance charts.
A 100% stacked area chart normalizes each reporting period to 100%, so the area segments always show category composition. Adding the total or reference measurement as a line on a secondary axis preserves that scaling while still displaying the reference trend.
Open WPS Office Spreadsheet and arrange dates or reporting periods in the first column, percentage categories in adjacent columns, and the total or reference measurement in a separate column.
Select the date and category columns only, including their headers, but leave the total or reference column out of the first selection.
Click the Insert menu, choose Chart, select Area, and then pick 100% Stacked Area to create the base chart.
Right-click the chart and choose Select Data. Add a new series, select the reference column as the series values, and use the date or reporting period column as the category labels.
Right-click the reference series in the chart, choose Change Series Chart Type, set that series to Line, and enable Secondary Axis if the reference values are not on the same percentage scale.
Build the Chart with All Series Selected and Then Convert the Reference Series
Use this alternative when all columns are already adjacent and you prefer to include the reference series from the beginning.
Create the CDC-Style Chart Directly in WPS Office Spreadsheet
WPS Office Spreadsheet includes Excel-compatible combination chart tools, so you can create a 100% stacked area chart, add a reference line on a secondary axis, and save it in Microsoft Excel format without switching software.
- 1. Open WPS Office Spreadsheet: Download and install WPS Office, then open the spreadsheet whose data you want to chart.
- 2. Prepare the data: Enter or review the date, percentage category, and reference columns so each series has a clear header.
- 3. Create the combination chart: Select the percentage area data, click Insert > Chart, choose 100% Stacked Area, add the reference series, and change it to Line on a secondary axis.
- 4. Save the chart file: Save the workbook as an .xlsx file or export the chart for your report, presentation, or web publication.
Frequently Asked Questions
Why should the reference line use a secondary axis in this chart?
A 100% stacked area chart always scales percentage categories to 100%. If the reference measurement uses counts, concentrations, or other units, placing it on a secondary axis prevents the line from being flattened or distorting the area scale. Right-click the series, choose Format Data Series, and enable Secondary Axis.
How do I add the reference line if I already created the stacked area chart?
Right-click the chart, choose Select Data, add a new series, select the reference column values and date labels, then right-click that series and change its chart type to Line.
Can WPS Office open and edit the Excel workbook after I create this chart?
Yes. WPS Office Spreadsheet supports .xlsx files, so you can open the workbook, create the combination chart, save it, and open it again in Microsoft Excel with the chart preserved.
What data layout works best for a CDC-style 100% stacked chart?
Use the first column for dates or reporting periods, one column per percentage category, and a final column for the total or reference series. Include clear headers so the chart labels and legend are generated correctly.
How do I make the stacked area segments show percentages instead of raw values?
Choose 100% Stacked Area rather than a regular Stacked Area chart. WPS Office normalizes each reporting period to 100%, so the vertical axis and data labels display the percentage composition.
