3 Steps for Tracking Spending With Spreadsheet Subcategories

We sometimes get asked whether we support the use of subcategories in Tiller Sheets.

The ability to be more detailed in categorizing your spending can be really useful. Perhaps you want to apply a subcategory to all your food purchases so you can see how much you spend on restaurants vs fast food or groceries. Maybe you have a similar use case with your business expenses.

There are a few simple steps you can take to set up a system that works off that basic idea using the existing category logic. From there you can create a pivot table that shows how much you’re spending in each subcategory by week or by month.

These steps are focused on our Standard Template or the Build Your Own template but may work with some of our other templates that use category logic as well.

1. Set up your categories and subcategories in your money worksheet

First, you’ll want to establish your category scheme on the Category Setup tab. You’ll use this single list for all your categories and subcategories together. You could simply type them all out in each cell. Food, fast food, restaurants and groceries.

Food is your main category and the three others are your subcategories.

If you’d like to be a bit more meticulous you could create a naming scheme such as F.0 Food, F.1 fast food, F.2 restaurants, F.3 groceries so that they’re all together from the alphabetized list in the category selector menu on your Transactions tab.

2. Add a column to your transactions tab

On your Transactions tab you’ll want add a column that also has the category logic applied to it. You can do this by right clicking on the “Category” column header and choosing “Insert 1 left.”

A new column will be inserted to the left of the Category column that should automatically have the category logic applied to it, which is indicated by the drop down menu available in each cell in this new column.

Give the new column a title such as “Subcategory” or whatever makes sense for your money management sheet.

3. Setting up your spending pivot table

The next step is to set up a pivot table that breaks down spending by sub category on a week by week basis.

Select the entire transactions tab by clicking the square in the upper left corner of the sheet. From the Data menu choose “Pivot table” and a new tab will be created where you can configure your pivot table.

Choose to group by “Week” for the row selection. Choose to group by “Category” for the columns selection.

Add a second field to the columns selection and choose “subcategory.” Choose “Amount” for the “Values” field. Finally, add a filter to the pivot table for “Category” and choose only the “Food” category.

Cleaning and customizing

From here I like to tidy it up a bit to make sure all the values are aligned in the center of the cells by selecting the entire sheet and then formatting the text alignment to center.

Be aware that if you haven’t applied any of the categories or subcategories to your spending data on the Transactions tab you won’t see much in this pivot table.

If you’d rather see this subcategory spending broken down by month, you’d simply choose the month column for the row selection when you’re configuring the pivot table instead of the week selection.

You can continue customizing your data to include charts or graphs. You can update the filters from the pivot table configuration menu to review your spending for different categories and subcategories by simply changing the selected category from the filter menu.

The options for slicing and dicing your data are limitless with Tiller and Google Sheets.