Hello folks! How are you doing today? I’m doing quite well. I’m writing this on a Saturday just after I came upon quite a revolutionary new way to categorize and organize my expenses. What is it you ask? Pivot tables! I know, how exciting right!? But stay with me, if you are a detailed expense tracker like myself I think you too will find this tool super handy. While initially I plan on using this as a vacation expense tracker, it could be utilized for any expense category that you’d want a detailed look into (e.g. children related costs, home and houseware costs, etc).
What Are Pivot Tables?
Quite a scary and intimidating name, huh? But don’t let it fool you. Scratch the surface of pivot tables and you will see how they can by sooooooooo useful, amazing powerful, versatile and a huge timesaver! I kid you not…
Simply speaking, pivot tables allow you to take an excel file “data dump” and slice and dice it up quickly and easily into pretty little charts and tables. I’ll paint a more detailed picture with my newly created vacation expense tracker further below.
Some of you may be aware of what pivot tables are. Sadly, up until this week, I wasn’t and I even considered myself an excel junky. I’ve used simple excel spreadsheets to track my expenses and investments for over a decade and for work I’ve used excel on basically a daily basis including for some very complex modeling. But never pivot tables…
However, this week I got involved in a unique work project which historically had been accomplished with pivot tables. This forced me to learn by doing. Getting tossed right into the fire isn’t the most efficient way of learning things, but I’m glad to say I’ve gotten up to speed quickly.
Vacation Expense Tracker
The work project is now mostly complete and ready to go. A day later I picked back up some of the planning duties for Lucy and my upcoming 10-year anniversary Greece vacation which we’ll be taking this Fall. I’ve written before about how we’ve been quite successfully travel hacking this big expense, which will be practically entirely paid for with reward points.
We just booked some of the day tours we plan on taking while in Athens and Santorini. With most all of the costs now booked (airfare, hotels and tours), I wanted to make a single repository to track these expenses. Lucy and I have taken one other big vacation during our marriage and that was to Peru to see Machu Picchu for our five year anniversary. I tracked those expenses, but not in a single chart or table. Now with our second big vacation booked and the intent to begin doing these more regularly, I thought it was about time to create a detailed table.
Creating My Tracker
What better time to have been just been introduced to pivot tables! While I certainly don’t consider myself an expert, I do have it down fairly well. Let me show you how I did it and below I will link to a quick video tutorial that I found helpful.
First, with just eight columns (and the ability to add more in the future should I decide additional data points/ info is necessary), I detailed each expense. You could categorize your table as you see fit and of course customize it.
With each vacation going forward I can simply add it to the next row.
Then, highlight all this data (from cell A:1 to H:18), click Insert at the top and select PivotTable.
Create that pivot table in a new worksheet and you can drag and drop your fields from the PivotTable Fields in the right-hand column to the table as a Column, Row, Value, or Filter.
With a few quick drag and drops, I’ve built a custom table to provide a cost overview of each vacation.
But that wasn’t the only table I wanted to create. While keeping this table as is, I can right click on this newly created pivot table tab (that I titled Cost Overview Pivot Table), then…
…select “Move or Copy”, and check the “Create a Copy” box and have it “move to end”.
Before you know it, I’ve customized a handful of additional tables!
A detailed overview chart:
Airfare summary chart:
Note that the airfare summary chart was created by clicking the Category filter icon and selecting only Airfare. After that chart is created, right click on the tab again and make another copy. Next I sorted by category by Hotel.
Hotel summary chart:
And a tour summary chart:
Isn’t that fancy!!!!
Note that in the snippets I’ve made sure to show the PivotTable Fields on the right hand side in case you wanted to recreate any of these yourself.
Want to know how to create your very own pivot table, watch this six minute tutorial that I found helpful.
What a resource for future travel we book, for friends and family who ask me about the trip details, etc!
Try building your own and take some time to play around with it a little, dragging and dropping fields in different areas to see all the unique and great ways to categorize and present your data. All from a quickly created “data dump” of expenses.
If you happen to be a pivot table pro, please share any tips and tricks in the comments below.
And if you wanted to try and recreate any of the vacations Lucy and I have booked, I guess you now have all those details too. Let me know if you get a better price ;)! But can you travel hack them as well as we did and basically get the Greece trip free!?
Thanks for taking a look!
The Green Swan