Pivot Table Issues

Create an Excel PivotTable for Monthly Utility Expenses

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Create a PivotTable showing utility expenses by vendor and month, including the service period for each month.

Create an Excel PivotTable for Monthly Utility Expenses
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.
Before you start

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.

Solution 1Recommended

Build a PivotTable from the utility expense data

Use this method once the source data is available to summarize expenses and show service periods.

1
Prepare the source table

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.

2
Insert a PivotTable

Select a cell in the data range, choose Insert > PivotTable, confirm the source range, and select where to place the report.

3
Arrange the fields

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.

4
Add service periods

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.

5
Check totals and formatting

Use PivotTable Design > Grand Totals to enable totals if needed, and use Value Field Settings > Number Format to display expense values as currency.

Build a PivotTable from the utility expense data
Source data is required: The exact vendors, periods, and totals cannot be calculated until the utility expense report is provided.
Summarize expenses in WPS Spreadsheets

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. 1. Open the data: Open WPS Spreadsheets and use File > Open to select the utility expense workbook.
  2. 2. Insert a PivotTable: Select a cell in the source data, choose Insert > PivotTable, and confirm the data range and destination.
  3. 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. 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.
Summarize utility expenses by vendor and monthShow service periods alongside expense totalsOpen and work with Microsoft Excel XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.