Create an Excel PivotTable for Monthly Utility Expenses
Question details
Create a PivotTable showing utility expenses by vendor and month, including the service period for each month.

- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Summarize a utility expense report by vendor and month while retaining service period details.
- Observed behavior
- The desired report includes vendor totals, monthly totals, and service periods, but no source data was supplied.
Obtain the source report and arrange it in columns with headers for Vendor, Expense, Month, and Service Period. Use numeric expense amounts and include the year in the month field if the report covers multiple years.
Build a PivotTable from the utility expense data
Use this method once the source data is available to summarize expenses and show service periods.
In Excel, enter or open the utility expense data with one transaction per row and headers such as Vendor, Expense, Month, and Service Period. Include a transaction date column if needed for the report.
Select a cell in the data range, choose Insert > PivotTable, confirm the source range, and select where to place the report.
In the PivotTable Fields pane, drag Vendor to Rows, Month to Columns, and Expense to Values. Open Value Field Settings for Expense and select Sum if it is not already selected.
Drag Service Period to the Rows area beneath Vendor to display the periods in the report. If the layout produces multiple lines per vendor, use the row labels and grand totals to read the vendor and monthly summaries.
Use PivotTable Design > Grand Totals to enable totals if needed, and use Value Field Settings > Number Format to display expense values as currency.

Create a utility expense PivotTable in WPS Spreadsheets
Use WPS Spreadsheets to arrange utility records in a PivotTable and summarize expenses by vendor, month, and service period.
- 1. Open the data: Open WPS Spreadsheets and use File > Open to select the utility expense workbook.
- 2. Insert a PivotTable: Select a cell in the source data, choose Insert > PivotTable, and confirm the data range and destination.
- 3. Set up the report: In the PivotTable field list, place Vendor and Service Period in Rows, Month in Columns, and Expense in Values.
- 4. Verify the calculation: Open the settings for the Expense value field and choose Sum, then check the monthly and grand totals in the PivotTable.

Frequently Asked Questions
Can I create the exact report without the source data?
No. You need the utility records, including vendors, expense amounts, months, and service periods, before you can calculate the actual totals.
Why does the PivotTable count expenses instead of summing them?
Check that the Expense column contains numeric amounts. Then open Value Field Settings for the Expense field and select Sum.
How do I keep the same month in different years separate?
Include the year in the Month field, such as January 2025, before inserting the PivotTable. Use that field in the Columns area.
How do I update the PivotTable after changing expenses?
After editing the source data, right-click the PivotTable and select Refresh. If new rows fall outside the original source range, update the PivotTable data source to include them.




