How to Show Monthly and Yearly Sales Variance in a WPS Spreadsheets PivotTable
Question details
The user needs to create a sales variance report in WPS Spreadsheets that compares current sales with the previous month and previous year in both dollar and percentage terms, and then visualize those variances in a PivotChart.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is preparing a sales report and needs a PivotTable that compares current sales with the previous month and previous year.
- Observed behavior
- The PivotTable should display current sales, dollar variance, and percentage variance side by side and support a connected PivotChart.
Prepare a source range with continuous columns for order date and sales amount, and make sure dates are recognized as actual date values rather than text. This allows Month and Year grouping in the PivotTable and reliable Difference From calculations.
Calculate Monthly and Yearly Sales Variances by Reusing the Sales Field
Use this approach when you need one PivotTable that shows current sales plus the dollar and percentage change compared with the previous month or previous year.
WPS Spreadsheets supports Excel-style Show Values As options, so you can add the Sales field to the Values area several times and set each copy to a different comparison. The layout with Month in Rows and Year in Columns keeps monthly and yearly comparisons readable.
Open the workbook in WPS Office, click any cell in the sales table, go to the Insert tab, and choose PivotTable. Confirm the range and place the PivotTable on a new worksheet.
In the PivotTable Fields pane, drag the order date field to Rows and group it by Month if WPS does not group it automatically. Drag the Year field to Columns and drag the Sales amount field to Values.
Drag the Sales amount field to the Values area again twice so the PivotTable contains three Sales value fields: base sales, dollar variance, and percentage variance.
Right-click any cell in the second Sales value column, choose Show Values As > Difference From, set the base field to the date or Month field, and select Previous Month or Previous Year as the base item.
Right-click any cell in the third Sales value column, choose Show Values As > % Difference From, set the same base field, and select Previous Month or Previous Year as the comparison period.
Visualize the Variance PivotTable with a PivotChart
Use this after the variance values are configured to turn the monthly and yearly comparisons into a chart for dashboards or presentations.
Build a Sales Variance PivotTable in WPS Spreadsheets Without Manual Formulas
Use the familiar PivotTable Show Values As options in WPS Spreadsheets to compare each sales period with the previous month or previous year. You get dollar variance, percentage variance, and Excel-compatible workbooks in one free office suite.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS site, install it, and open the workbook that contains your sales data.
- 2. Start a PivotTable: In WPS Spreadsheets, click inside the sales data, go to the Insert tab, and select PivotTable. Choose a new worksheet for the report.
- 3. Configure the value fields: Drag Month to Rows, Year to Columns, and add the Sales field to Values three times. Right-click the extra Sales values and set Difference From and % Difference From to the previous period.
- 4. Insert a chart and review: Select the PivotTable, go to Insert > PivotChart, choose a line or column chart, and use the chart filters to display the variance series.
Frequently Asked Questions
What does % Difference From show compared with Difference From?
Difference From shows the change in the same unit as the value, such as dollars. % Difference From shows the percentage change between the current period and the previous period. You can set both by right-clicking a Sales value cell and choosing Show Values As.
Why can't I select Month or Year as the base item in the variance settings?
Make sure the date field in Rows or Columns is grouped by Month or Year. Right-click the date field, choose Group, and select Months and Years. If the source column contains text instead of real dates, grouping and previous-period comparisons will not work.
Can I show previous month and previous year variance at the same time?
Yes. Add the Sales field to the Values area multiple times and set each copy to Difference From or % Difference From with the previous period base item. You can also arrange Month in Rows and Year in Columns to see both comparisons clearly.
How do I stop the PivotTable from summing date values instead of grouping them?
Check that the date column contains real date values and not text-formatted entries. Then right-click the date field in the PivotTable, choose Group, and select Months and Years so it works as a time period instead of a sum.
Will this variance PivotTable still work if the file is opened in Microsoft Excel?
Yes. WPS Spreadsheets saves in the .xlsx format, so PivotTables, Show Values As settings, and PivotCharts remain available when the file is opened in Microsoft Excel.
