Dear Spreadsheet Whisperer,
I am a self-employed freelancer and I need to track and pay taxes on business expenses in multiple states. The way I have been capturing receipts and tabulating expenses is painstaking! Can you do anything to help?
From, Frustrated Finance Freelancer
Dear FFF,
I’m SO glad that you reached out about this! Working on this mini project made my brain go in so many different directions and I even learned/brushed up a few spreadsheet tricks that will definitely come in handy later. As we always say, the hardest part is asking for help. In the rest of this post, I’ll document what we built together… and then I’ll post some ideas for future enhancements. Remember, if you can dream it, you/we/SOMEONE can build it!
Idea 1: Pivot Tables
I started out with creating a test dataset where our expense categories were “Books, Crafting, Nails” (IYKYK) and our States were “DC, IA, IL, MA, and PA”. This gave us enough data to work with interesting categories and summaries.
Instead of using manual filters to find the total expense in each category in each state, I think using a “Pivot Table” is the perfect tool!
You can see here that the “Pivot Table” (column F to J) takes our States as rows and our Categories as columns… however you could arrange it any way you like! Pivot tables are free and come standardly available in Google Sheets and Microsoft Excel. All you have to do is “insert” a Pivot Table and drag and drop your options into the Pivot Table configuration menu. There are also special features for “grouping by date” in case you want to see expenses by Week/Month/Quarter/etc.
Idea 2: Divide data into category tabs with =filter()
FFF shared with me that they like to see tabs for each Expense Category, so that it is easier to give the data a look over and determine if anything is missing. I came up with two ideas to help keep data in ONCE centralized tab (for calculations) and MANY detail tabs (for data entry or for analysis).
If we are starting with One Tab To Rule Them All, we can quickly and automatically populate detail tabs using a quick “Filter” formula. Using this method would make the detail tabs READ ONLY as the “real” data would be on the main tab. Here’s a look at that formula and the result:
=FILTER('Source Data with Pivot Table'!A1:D41, 'Source Data with Pivot Table'!B1:B41="Books")
Remember, your columns, categories, and tabs may have different names than mine!
Also, you would need to repeat this manually for each tab. This could get annoying if you have a LOT of expense categories!
- Faster way to do this (paid product) is Power Tools for Google Sheets by Ablebits
- Medium faster (cooler) way to do this is with an addon like TemplateTab which will make your tabs for you (!!) and then some fancy footwork to pull in the right category name into your formula. I think the smart way to do this would be =Indirect() formula but I have not puzzled the whole process out, yet.
- Would need to maintain this to keep tabs relevant as expense types change over time
Idea 3: Compile data from category tabs with =filter() array formula
This option assumes that you are doing data entry on the detail tabs (which is something we talked about!) and you would like to combine them “up” to a full data tab for analysis purposes. I learned a new way of using formulas for this, borrowing from this great blog post. Using an array function (generally {} brackets in formula mean a table array) we are able to stack two filter functions on top of each other, and combine data from multiple sheets in one MegaSheet!
Here’s a look at that formula and the result (green line shows where one filter formula ends and the next starts)
={
FILTER('Books Only Via Filter'!A:D, LEN('Books Only Via Filter'!A1:A) > 0);
FILTER('Nails Only Via Filter'!A:D, LEN('Nails Only Via Filter'!A1:A) > 0)
}
Enhancements
I would be excited to further enhance this project by:
- Creating an associated Google Form to dump data into the main data tab, while also categorizing receipts for you in a searchable Google Drive folder
- Adding a link to this Form URL to your phone screen so that you can open it as a webpage easily
- Automating the maintenance of tabs and filter formulas as categories change over time
- Creating a dashboard of business budget to actuals graphs/charts if desired!
In conclusion…
FFF, you already have so many ideas in your head about ways to streamline your biz expenses and just needed a little bit of help to unlock the spreadsheet tools that are capable of it. With all of these functions working in your favor, I think you’ll soon be shedding the penname Frustrated Finance Freelancer and adopting “Fabulous Filtering Formula-maker!”
xoxo, Spreadsheet Whisperer