Pivot Table Issues

Create an Excel Year-to-Date PivotTable Summary by Department, Expense, and Month

Tauseeq Magsi Nov 12, 2026 869 views

Question details

Create a year-to-date summary of monthly budget amounts by department and expense category for a selected period, such as January through March.

Create an Excel Year-to-Date PivotTable Summary by Department, Expense, and Month

Product: Microsoft Excel PivotTable
Device & OS: not provided
Scenario: The user needs to analyze structured budget data by department, expense category, and month while limiting results to a year-to-date date range.
Observed behavior: The desired result is a PivotTable with departments in rows, expense categories in columns, budget amounts in values, and months or dates available for filtering or grouping.

Before you start

Make sure the source list has one header row and includes fields such as Department, Expense Category, Budget Amount, and Date or Month. If months are stored as text, use a real date field when possible so the PivotTable can group and filter the period correctly.

Solution 1Recommended

Build a Year-to-Date PivotTable from Budget Data

Use this method when your budget records are organized in rows and include department, expense category, amount, and date or month fields.

A PivotTable can summarize budget records without formulas. Place the organizational fields in the row and column areas, then filter or group the date field to display only the months included in the year-to-date period.

Select the source data

Click any cell in the budget data range, or select the full range including headers such as Department, Expense Category, Budget Amount, and Date.

Insert a PivotTable

In Excel, select Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and click OK.

Arrange the summary fields

In the PivotTable Fields pane, drag Department to Rows, Expense Category to Columns, and Budget Amount to Values. If the value is not totaled automatically, select the value field, choose Value Field Settings, and select Sum.

Add and group the date field

Drag Date or Month to Filters. If using a Date field, you can also drag it to Rows, right-click a date in the PivotTable, select Group, and choose Months and Years.

Select the year-to-date period

Open the Date or Month filter in the PivotTable and select the required year and months, such as January, February, and March. The table updates to show the selected year-to-date budget totals.

Build a Year-to-Date PivotTable from Budget Data

Year-to-date summary created: Your PivotTable now compares budget totals across departments and expense categories for the selected months.

Solution 2

Use a PivotTable Timeline for Faster Date Filtering

Use a Timeline when the source data contains real Excel dates and you want to switch between months or quarters visually.

Create budget summaries with WPS Spreadsheet

Create a Year-to-Date Budget PivotTable in WPS Spreadsheet

WPS Spreadsheet lets you create a PivotTable to summarize budget amounts by department, expense category, and month. Use a date filter or grouped dates to focus the report on the required year-to-date period.

1. Open the budget worksheet: Open the workbook in WPS Spreadsheet and click a cell in the data range that contains column headers.
2. Create the PivotTable: Select Insert > PivotTable, verify the source range, choose the destination, and confirm the creation.
3. Set up departments and expenses: In the PivotTable field list, place Department in Rows, Expense Category in Columns, and Budget Amount in Values.
4. Filter the year-to-date dates: Drag Date or Month to Filters, then open the filter and choose the reporting year and the months included in the year-to-date period.

Summarize budget totals by department and expense category with PivotTables.Filter or group date fields by month, quarter, and year for year-to-date reporting.Open and edit Microsoft Excel .xlsx and .xls budget workbooks.Use a familiar spreadsheet interface for a smooth transition from Excel.

Frequently Asked Questions

What columns are needed for a year-to-date budget PivotTable?

Use at least Department, Expense Category, Budget Amount, and Date columns. Each row should represent one budget record, and the first row should contain unique headers.

How do I show months across the top of the PivotTable instead of expense categories?

In the PivotTable Fields pane, drag Month or Date to Columns and move Expense Category to Rows or Filters. If Date is used, right-click a date item and select Group > Months.

Why is my Budget Amount showing Count instead of Sum?

The amount values may be stored as text or contain blanks. First convert the source values to numbers, then right-click the value field in the PivotTable, select Value Field Settings, and choose Sum.

Can I filter the PivotTable to January through March only?

Yes. Add the Date or Month field to Filters, open its filter control, and select January, February, and March. If your data spans multiple years, filter the year as well.

Do I need to refresh the PivotTable after changing budget data?

Yes. Click inside the PivotTable and select PivotTable Analyze > Refresh in Excel, or use the Refresh command in WPS Spreadsheet, after adding or editing source records.

Excel & Data Problems