Finances can be intimidating. And when you think of spreadsheets, two words may come to mind: formulas and functions. But thanks to Google Sheets, tracking your spending doesn’t need to be a hassle. Nor do you need to invest in another software.
So, how can you build a custom spending tracker without the learning curve that comes with mastering spreadsheets? Read on for step-by-step instructions.
Which Columns to Include in Your Google Sheets Spending Tracker
Tracking your spending can seem like a lot if you’ve never done it before, but keeping things simple will help you get going. Start with three simple columns—four, if you want to add the date to your transactions—and build out a more detailed spreadsheet later if needed.
The three columns to start with are Place, Category, and Amount.
- Place—is the store you went to.
- Category—is the type of items you bought.
- Amount—is the total you paid in that transaction.
Using these three columns, you can see how much you’re spending within a category, but also look for patterns in the stores you visit.
It’s tempting to get more in-depth and track individual items, but doing so will add a lot of time to the process, and you may lose interest. If you often forget what you bought at a store, add a column for quick, general notes or simply keep your receipts.
You can create a dropdown menu using Data validation in Google Sheets. This is an excellent solution for your spending track because it allows you to quickly select a category with a couple of clicks, rather than typing it manually each time. You can do it by following these steps:
- Click a cell under Category.
- Go to the Data menu at the top of your screen.
- Select Data validation from the list.
- In Criteria, choose List of items.
- Enter the category names in the field, separating them with a comma.
- Hit Save.
You’ll see the dropdown arrow in the cell after saving it. Keep the cell selected—now it’s time to use Conditional formatting in Google Sheets to color code your categories. To do so:
- Go to Format in the top menu.
- Select Conditional formatting from the list—a sidebar will appear once you do.
- Under Format rules, click the dropdown below Format cells if…
- Choose Text contains.
- Place a category name in the field that appears underneath.
- Under Formatting style, change the background color or text color.
- Hit Done.
- Repeat this with the other categories.
To place the dropdown menu in every cell under the Category column, copy the cell with the formatting. Then, before pasting, click the title cell while holding CMD or CTRL.
How to Use Date and Currency Formatting in Google Sheets
If you want to keep a consistent date and currency formatting under each cell in your Google Sheets, here’s how to do it:
If you’re using a date column, and you want to keep things consistent, you can add specific formatting to your cells. To do so:
- Select the entire column using the letter at the top.
- In your toolbar, click More formats—its symbol is 123.
- Choose Date—beside it is an example of how the date will look.
Alternatively, you can do this by going into the Format menu at the top and under Number, choosing Date or Custom date and time.
To add currency formatting to your amount column, select the entire column > More formats and click the dollar sign ($) in the toolbar. You can also find the option in the Format menu under Number. Here, you can choose Currency, Rounded currency, and Custom currency.
Custom currency allows you to switch how the decimal and symbol appear, specify the type of dollars you’re working with, or choose another currency altogether.
How to Use the SUM Function for Your Spending Total in Google Sheets
Functions are a bit intimidating, but some are much easier to use than others, so hang tight if you’re not super confident. You can use the SUM function in Google Sheets to help you keep tabs on your spending total—and all it takes is a few steps.
- Select the cell you’d like to keep the total in.
- Go to Insert in the top menu.
- Select Function.
- Choose SUM at the top of the list.
- With your cursor between the brackets, click the letter above the column.
- Hit Enter on your keyboard.
Now your total will appear in that cell, changing as you add or subtract from the column. Alternatively, you can type =SUM() into your cell and fill in the brackets the same way.
How to Use the SUMIF Function for Category Totals in Google Sheets
Using the SUMIF function allows you to put conditions on what you’re adding, which you can use to get category totals. Here’s how:
- Select the cell you want the total to be in.
- Go to Insert in the top menu.
- Select Function, then Math, and SUMIF.
- Between the brackets, type the category column range—example B:B.
- Add a comma and type the category name in quotation marks.
- Enter another comma, and add the amount column range.
- Press Enter on your keyboard.
- Repeat this for each category.
Now your category total will appear and change as you work. Alternative to going through the Insert menu, you can type =SUMIF() into your cell and fill in the brackets, following the same steps.
How to Use Filters to View Spending Categories in Google Sheets
If you want to sort or hide transactions, you can add a filter to your Google Sheet. To do this, select the column you want to sort and click the Create a filter icon in the toolbar. Three lines will appear beside the column title.
Click the three lines, and you’ll see a list of options. In the category columns, you can use Filter by color to quickly show one category, or use the checklist at the bottom to select and deselect them. You can also use Sort A-Z in the amount column to view your transaction from least to most expensive, or Sort Z-A for the opposite.
Track Your Spending With Google Sheets
Finances and functions can be intimidating, but Google Sheets takes the worry out of them both. Plus, unlike a template you download off the internet, if something goes wrong with your spreadsheet, you’ll know exactly how to fix it.
Best of all, you can use the Google Sheets app on your phone to easily enter transactions on the go. That way, you’re not playing catch-up at the end of the month.
Read the full article here