How to Easily Categorize Spending with Excel Pivot Tables
This post may contain affiliate links. That means if you click and buy, we may receive a small commission (at zero cost to you). Please see our full disclosure policy for details.
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.
Thanks for sharing this post. I’ve been using a very similar approach for a couple of years now. Where I would love to streamline further is in the mapping between ‘description’ and ‘type’.
I import a few dozen new transactions at a time, and every time I have to fix the ‘type’ column. Have you found an efficient way to set up rules that indicate “if column B has ‘costco gas’ then column D should be ‘gas'” ?
I’ve considered applying a formula to the type column, but it feels like the formula would get really really long with each conditional statement…
Hi Alex, you could try a Vlookup formula using a separate table with Columns for ‘Costco gas’ to return ‘Gas’. Lookup these values using wildcard characters for extra characters that may show in the credit card data descriptions, and return a standard category. Hope that helps!
Would you say that we could do the same for calculating our tax deductions and income?
I Cannot thank you enough for this!!! Was searching for ages to find out to how to do this, or a template to download, Struggled until I found this.
Worked perfectly, great instructions to follow!
Hi Alana! So glad to hear this!
Hi! I know this would be really handy to read but there were so many ads on the page, including a pop up video that sits right over the top of content (comes from the side of the screen that doesn’t allow the user to close it, that I couldn’t get to your content. Just some feedback!
Absolutely, appreciate that!
This is the best thing that ever happened to me lol. I love Excel and have been using it for years, but never really played with Pivot Tables. This makes my monthly/yearly reporting so much easier!
Hi Cam! Love it! Pivot Tables are life-changing 🙂 So glad to hear you found this useful.
I use Excel for tracking our monthly expenses and tax deductions. One thing I wish I would do each year is take a refresher class on all the various functions that Excel can do.
Right? It’s always changing and there is always something to learn. We had a series of courses we could take at my last job. But now that I’m on my own, I try to find some good online courses – Udemy is typically my go-to. Thanks for the comment, Josh. 🙂
Can I take a guess that one of your credit cards is the Citi 2% cashback rewards card? 😉
This is a great step-by-step guide. I normally just took the company’s charts as is but creating a DIY chart is definitely a way to go. Especially because sometimes the credit card company doesn’t categorize things correctly.. Thanks for putting it together, Kelsey!
Haha, yes the Citi Costco card which gives 4% on gas (and tiers down in cash back from there) which is great for us. Especially my husband’s business because he drives so much.
Taking their charts is definitely quicker, but I like to review each charge and apply my own category. And like you said they don’t always categorize things the way I would like. Plus I am a major Excel geek. Haha. Thanks for checking it out!