How to use Microsoft Excel Pivot Tables to categorize spending easily. Align with forecast and budget categories. Review for accuracy and find recurring expenses that need to be canceled.
Welcome! Why I am posting personal finance on a food blog? I wrote this post before I decided to focus mainly on food blogging. I used to be a financial analyst and I’m also an excel geek at heart. I hope you find it useful.
Today I’m sharing a really easy way to categorize spending with the use of Microsoft Excel Pivot Tables. And before you doze off or close the page 😉 — this will save you some precious time + give you a way to review your spending in detail on a regular basis. You may have charges you’ve been meaning to cancel or inaccurate charges from companies. I use this method for both personal and small business finances.
This goes hand-in-hand with one of my very first posts on this blog: How to Develop a Personal Finance Forecast. This is a basic version of how I used to forecast as a finance analyst. It’s a tool you can use to regularly review spending habits + plan and budget several months out. This post will help with documenting your actual spending.
Some banks and credit card companies provide spending reports. And some people use an online tool like Personal Capital to track and review spending. However, my favorite is still Excel. I’ve found instances where charges have been placed in the wrong bucket on several occasions. Plus, this gives you a great way to review each charge and know exactly what is going through your accounts.
So without further ado, let’s look at how you can easily categorize and review actual spending.
Export data from bank or credit card company
Log in to your online account and export your spending data. I choose to do this monthly, so it isn’t so overwhelming. But you could do as many months as you’d like. You can typically select the date range.
Here is what a bank download button may look like:
Here is what a credit card download may look like. Make sure you choose the spreadsheet option or a comma-delimited format.
Your data should look something like this: (Please note, this is an example only and does not represent my financial information.)
Save the file in Excel
Save the file in an Excel format. If you miss this step, you’ll likely have issues creating the Pivot Table.
Create category & month columns
Add two columns. 1) for spending categories, I used Type as the column heading. 2) for the month, which is handy when you have several months of data.
Enter the =month() formula in the Month column. Selecting the corresponding date cell. In this case =month(B2).
Copy the formula down to all rows of data.
Sort by description
Sort the description column for easy categorizing.
Once the descriptions are sorted, it will be much quicker to categorize each purchase in the “Type” column.
Create a pivot table
Create the pivot table by selecting Insert –> Pivot Table –> Pivot Table.
Select the data table and select “New Worksheet.” Click OK.
Drag the field “Type” in the PivotTable Field List to the Row Labels section. Drag the “Month” field to the Column Labels section. Drag “Debit” and “Credit” to the Values section.
Unless your default is set up differently, you’ll notice the table is counting the items instead of displaying the dollar amount. So select “Count of Debit” and click on “Value Field Settings.”
Choose “Sum.” Repeat for the Credits.
This will display the dollar amount. Format numbers to dollars. And voila! This is your Pivot Table. You can easily add your actual spending by category to your forecast or budget. And research any weird charges you find. If the “Total” columns bother you. Right-click on “Total Sum of Debit” and select “Remove Grand Total.”
I’m sure various exports from different financial institutions look different. However, the process should basically be the same:
- Export financial data.
- Save in Excel format.
- Add a column for purchase type and month.
- Create a month formula and copy down to all rows.
- Sort descriptions for easy categorizing.
- Assign each purchase a “type” or “category.”
- Be sure to align categories to budget or forecast.
- Create your Pivot Table.
- Research and/or cancel any purchases you don’t recognize.
- Plug the information into budget or forecast.