This article presents a method to manage expenses efficiently using Google Sheets, ensuring that your monthly financial activities are well-organized and easily accessible.

Like many individuals, I also have financial obligations that need careful monitoring.

These expenses can include bills like utilities, rent, groceries, and even non-essential subscriptions such as streaming services. Living with my girlfriend means we share many of these costs.

To streamline the management and sharing of our expenses, I chose to create a spreadsheet. I thought others might find it beneficial as well!

How to Duplicate the Template (With Formatting)

While it may seem straightforward to simply select all cells, copy, and paste into a new sheet, doing so often results in loss of formatting, requiring additional adjustments.

A more reliable method to copy the sheet precisely is:

1. File
2. Make a Copy
3. Rename Sheet
4. Select File Destination
5. Make a Copy

Now that you understand how to duplicate it fully, feel free to do so! Let's delve into the details of this template.

The Template Overview

Below is an image showcasing the layout.

While the color scheme is simple, you can customize it with your preferred colors, ensuring that each section is clearly defined and organized.

Main Expense Table

The primary section (A1-E13) features a comprehensive table that captures all expenses listed in the sheet, providing a quick summary. This includes items like rent, utilities, and groceries, as well as subscription services. To add new entries, simply fill in the additional rows.

If you need to insert a new row in the table:

1. Highlight A13-E13
2. Right-click on the highlighted area
3. Select Insert Cells
4. Choose Insert Cells and Shift Down

The table also illustrates how expenses are divided among individuals.

You’ll find a list of expenses alongside their amounts, relevant details, and individual breakdowns. For instance, in cell A3, I opted to label the rent more explicitly rather than just writing "Rent." This helps me remember that when it's time to send the next payment, it pertains to the upcoming month’s rent (I prefer to send payments ahead of time).

Although I usually keep track, I cannot deny that I have mistakenly noted the current month on occasion.

It’s not catastrophic, but having a reminder can be helpful.

The month automatically updates based on the current date. I derive the month from today’s date, add one, convert it into text, and concatenate it with "Rent." Here’s the formula used:

=CONCATENATE(TEXT(EDATE(TODAY(), 1), "MMMM"), " Rent")

The TODAY() function fetches the current date. The EDATE() function allows for projecting a date forward or backward by a specified number of months; in this scenario, I use 1 to get the next month. The TEXT() function formats the month accordingly.

Column B is for user inputs, with the exception of B7, which derives from another table that will be explained later. Columns D and E display how the corresponding amount in Column B is divided according to the values in the subsequent splits table.

Splits Table

This section enables you to define your own split values for different expenses. This is particularly useful if expenses are shared, allowing you to establish the splits in one area and have them reflected across the entire sheet.

For example, if you wish to split rent 55% to one person and 45% to another instead of a 50/50 split, simply input these values in the respective cells of the splits table. The main table will update automatically based on these values.

To determine the amount owed by Person 1, multiply the rent amount for September (cell B3) by the corresponding split value (H3). For Person 2, apply the same method using I3.

Person 1 pays 55% of the rent ($275), while Person 2 covers 45% ($225). If you prefer to evenly split the costs, you can utilize what I refer to as the base splits.

To facilitate this automatically, modify the formula in cell D3 as follows:

=IF(ISBLANK(H3), B3*H12, IF(H3>-1, B3*H3, B3*H12))

This formula checks if H3 contains a value. If it does, it further checks if that value exceeds -1 (which will be clarified shortly regarding allowing a 0% split). If it does, then it applies that value.

Otherwise, it defaults to H12. You can apply a similar formula for Person 2 by swapping H3 with I3 and H12 with I12, respectively.

Why Consider 0%?

If you aim to account for only one individual, the easiest way is to eliminate all splits, designating both base splits and groceries as 100% for Person 1 and 0% for Person 2. I am looking to implement a more straightforward solution in future updates.

You might have also observed that I included values for various stores. These represent locations you might frequent for groceries or other purchases.

The splits are specified as I tend to visit certain stores more often than my girlfriend, such as Store One and Store Three. However, Store Two is where we combine our grocery shopping, so that split is even.

These values align with the calculations for the groceries section.

Groceries (Table and Calculations)

This part encompasses a table intended to document all transactions and their details, along with calculations that incorporate the splits.

To begin with the table, check your bank statements for transactions, noting the dates and amounts spent, and add them to the sheet.

The calculations are a bit intricate.

Each category for transactions includes columns for store names, total spending, and the splits per person.

Below the splits, you’ll find what I call the total split values, used if you wish to evenly divide all grocery expenses, irrespective of store or frequency.

If no specific grocery splits are provided, the default grocery splits will apply.

The totals for each store are calculated by summing only those amounts that match the corresponding store name.

I assess the amounts in the entire table (C21:C62), summing them only when the store name aligns with the associated calculation's store name (E28). The SUMIFS function is utilized for this purpose, as it sums values based on specified criteria.

If you find the table becomes crowded, you can apply the same method as before by inserting cells above the last line, which will adjust the range accordingly to extend beyond C62.

In the main table, if you wish to utilize total grocery splits, reference the grocery total cells under each person. For Person 1, set their grocery cell in the main table (B7) to the total grocery split value located further down the sheet (G35). For the specified grocery split, refer to G32. Do the same for Person 2, using either H35 or H32.

I plan to simplify this process in the future, but for now, this is the approach.

The Final Section

Lastly, we arrive at the grand total section, marked by a green double border. This summarizes the total amounts owed after consolidating information across all expense categories.

Upcoming Features

Here’s a list of features I am planning to introduce:

1. A button to reset the entire sheet (excluding rent, which usually remains constant).
2. A checkbox to reset the sheet at the end of each month.
3. A checkbox to toggle between general grocery splits and custom splits.
4. Highlighting split cells that total more or less than 100% to indicate incomplete or excessive values.
5. An option to specify the number of people included in the sheet, which will adjust the tables and splits accordingly. This will be the most challenging feature to implement.
6. Enabling users to sort grocery transactions by date, store name, credit card, etc.

I am currently developing a custom Google Apps Script to incorporate these features and am excited to integrate them into the spreadsheet! If you're interested in learning how to include a Google Apps Script in your Google Sheet, check out my previous post!

What additional features would you like to see? Are there any existing features you find unnecessary? Share your thoughts in the comments!

Upgrade your free Medium membership for unlimited access to a diverse array of ad-free stories from numerous writers across various publications. This is an affiliate link, and a portion of your membership helps support my content creation.

You can also subscribe via email to receive notifications whenever I post new content!