Down to the Dollar: How to Track Spending with Pivot Tables!

Mmmk.  Can I get some love for pivot tables?  I mean, they take a buncha numbers and categories and organize them for you in SECONDS. 

Hang on.  Don't leave!  This is fun, I promise.  

How to se picot tables to track spending.JPG

So, I explained about how I had a money revelation here.   One of my first steps to understanding where all our money was going was to track every dollar I was spending.  There are many programs you can use: Mint, YNAB, Quickbooks, etc.  They aggregate your spending and offer lots of different views and things.  

For me, tracking my spending is much more effective if (1) I actually have to create an entry for everything I spend and (2)  I can fiddle with the numbers easily.  I use a normal Excel worksheet to track my spending (and keep my retirement projections) and use pivot tables to summarize my spending.  If you don't have Excel, try an open source spreadsheet program.

Entering the items myself (instead of a direct feed from my accounts) makes me think twice before spending and then twice again when I'm entering it.  Was that worth it?  What exactly did I spend $4 on last Thursday?  The pivot table summary gives me good line of sight to all of my expenditures.  It helps me to know if I stayed in my projected budget or if I need to change my spending habits in a certain area.


Excel Spending Entries.PNG

The first step is to access your first tab (I keep a tab for each month) and enter your expenses.  I use a very basic format of dollars spent, for what purpose/where, and then a very high level main category.  My main categories are the same throughout all my tabs.  It makes it easy to compare month over month.  You might choose to do things differently- maybe a notes column to indicate in more detail what the purchase consisted of.

At any point in the process, you can create your pivot table.  The pivot table will easily summarize your entries!  Once you've established your pivot table and you continue to add new entries for spending that month, all you have to do is right click on the table and choose "refresh" for it to pick up the new data and update the summary. 

To create your pivot table... 

Highlight columns.PNG

(1)  Highlight the columns of data you have.  Highlight the entire columns so that you can just add new entries and refresh the table.  

select pivot table.PNG

 (2) Go to your Insert tab and click on the PivotTable option.  If your Excel is earlier than 2010, it will be a little different.

pivot location.PNG

(3)  On the little window that pops up, you'll see that the columns you selected show as the "range" of the data the table will use.  You will need to determine where you want your pivot table to go.  I like mine on the same tab as all my data for that month, so I select "Existing Worksheet." click on the little picture at the end of the "Location" box and then select where in my tab I want the pivot table to land.  Click OK. 

pivot select rows.PNG

(4) An empty pivot table will suddenly appear along with a menu of options for the table.  I want my table to show dollars spent per main category.  So, I can drag and drop "Category" from the fields list to the row labels and then "$" from the fields list to the values.  You'll notice that it defaults to "count" of dollars, which is not what I want.  i want it to sum up the total dollars for that category. 

sum dollars.PNG

(5) If you click on the little arrow next to the "Count of $,"  it will give you a drop down.  Select "Value Field Settings" and you will receive this pop up.  Choose "Sum" from the list and then click OK.

final sum dollars.PNG

(6)  Yay!  Now your pivot table is all ready to view.   

These are fake numbers, by the way.  My butler's salary is obviously not on here, nor is the charge for my new gold plated ashtray. 

After this comes the hard part.  If you aren't already a thrifty person with a careful eye on expenses, it can be scary to be faced with a picture of your money outflows.  It is just the thing to spur you into action, though!  More on that another time.   

Anyone use any fancy programs or homemade spending trackers?  Tell me about it!