Skip to main content
All CollectionsWorkflowsPersonal Finance
Visualize Your Financial Data With a Pivot Table
Visualize Your Financial Data With a Pivot Table

Learn how to create a pivot table from your transaction data to visualize your monthly spending across categories.

Heather Phillips avatar
Written by Heather Phillips
Updated over a year ago

Pivot tables are powerful tools to help you visualize your finances in a spreadsheet.

Google Sheets

Monthly per Category

Create a simple monthly pivot table to visualize your spending and earning across one or more categories.

  1. Select all the transaction data in your sheet by clicking the square in the upper left corner of the sheet, above row 1 and to the left of column A.

  2. Open the Insert menu and choose "Pivot table..." from the list.

    1. Choose New sheet

    2. Google Sheets will create a new sheet and add the pivot table, which will reference the selected data from the Transactions sheet.

  3. From the pivot table editor choose “Category” for the rows.

  4. Choose “Month” for the columns. 

  5. Choose “Amount” for the values and leave the summarize by SUM. 

If you need to edit the pivot table later, just click the "edit" icon in the lower left corner of the pivot table to re-open the Pivot table editor.

You can order the “Month” columns by “Descending” rather than “Ascending” to see the most recent month first. You can also add filters if you only want to pay attention to certain categories.

Organized by group

If you'd like to see the data organized by group instead of or in addition to category you can do that too.

  1. Add the Group column to your Transactions sheet if it's not already there

  2. Follow the basic Monthly Pivot table instructions above

  3. Use Group instead of category for the rows setting

You can also use multiple rows options in the pivot table's settings. You can nest Category under Group to see the breakdown of each category rolled up into the group.

Customize the time period

If you want to see the data organized by a different time period like week or year you can use one of those columns instead of "Month" for the columns in the pivot table's settings.

The Week column is a default column in the Transactions sheet and is populated by Tiller's feeds.

If you'd like to create a yearly pivot or filter the pivot table to only show data from specific years use these steps to add a Year column to your transactions sheet and use the Year column in your pivot table settings.

Get fancy with filters

Use the Filters option in the Pivot table editor to further customize by filtering out certain categories, groups, or time periods.

Keep in mind that if you filter out certain time periods, you may need to adjust the filter as time goes on to include new time periods, categories, or groups.

These are just a few examples of the ways you can use a pivot table to organize your financial data in a spreadsheet, but many more customization variations are possible!

Microsoft Excel

Yearly, Quarterly, and Monthly per Category

  1. Select all the transaction data in your Transactions sheet by clicking the square in the upper left corner of the sheet, above row 1 and to the left of column A.

  2. From the Insert ribbon and choose "Pivot table"

  3. Click OK in the pop up that appears to use the default settings to create a new sheet and use the selected data range.

  4. Drag the Category option from the Field Name list near the top of the pivot editor to the Rows section

  5. Drag “Month” into the Columns section

  6. Drag “Amount” to the Values section

By default Excel will nest the amounts by Year, Quarter, then Month. You can expand each year and quarter to see the monthly totals. If you remove this extra nesting by dragging those options back to the field name list, it will sum the amounts per month for all years where data exists.

Click anywhere inside the pivot table to edit its settings.

Did this answer your question?