How to Create a Monthly Spending Tracker on Google Sheets

Finances can be a daunting task, but thanks to Google Sheets , keeping track of your spending doesn't have to be a hassle. You don't have to invest in another software either.

With a simple expense tracker built with Google Sheets, you can track your expenses and make sure you don't overspend.

Table of Contents

What should your spending tracker include?

Tracking your spending can seem like a daunting task if you've never done it before, but keeping things simple will help you get started. Start by creating two sheets in your spreadsheet—one to record your expenses and another to summarize and visualize them.

The first worksheet must have at least the following 5 columns:

  1. Location : The store you visited
  2. Category : The type of item you purchased
  3. Amount : The total amount you paid in that transaction
  4. Date : the date you made the purchase
  5. Month : The month the purchase date is in - this column will be used to generate the summary.

You may want to go deeper and track each individual item, but doing so will add time to the process and you may lose interest. If you tend to forget what you bought at a store, add a column for quick, general notes or just to keep track of your receipts.

Create color-coded drop-down menus for spending categories in Google Sheets

How to Create a Monthly Spending Tracker on Google Sheets

You can use the drop-down list in Google Sheets to add categories to your transactions. Doing this will allow you to quickly select an option with just a few clicks, instead of having to type it in manually each time. It also helps keep your input consistent, so you can easily find and sort it later.

You can follow these steps:

  1. Highlight the Category column . Hold CMD or CTRL , uncheck the header cell.
  2. Right-click with your mouse or trackpad. Or, go to Insert in the top menu. Click Dropdown . This will open the sidebar.
  3. Enter the categories you want to include in the text field. Click Add another item to create additional options, or you can press Enter or Return on your keyboard.
  4. Color code your categories using the drop-down menu to the left of the text field and press Done .
How to Create a Monthly Spending Tracker on Google Sheets

Then, if you want to go back and edit or add to the drop-down list, you can find the menu again by right-clicking on one of the cells and selecting Dropdown again . You can also access it through the Data menu at the top of the screen, where you select Data validation . Now, you will find a list of rules, and you can select the rule you want to use.

Tracking tool format

If you want to keep the date and currency formatting consistent in every cell in your Google Sheets, here's how to do it:

Date format

If you're using a date column and want to keep things consistent, you can add specific formatting to your cells. To do this:

  1. Select the entire column by the letter at the top.
  2. In your toolbar, click More formats - its symbol is 123 .
  3. Select Date - next to it is an example of how the date will display.
How to Create a Monthly Spending Tracker on Google Sheets
Date Format in Google Sheets

Alternatively, you can do this by going to the Format menu at the top and under Number , select Date or Custom date and time .

Month format

Instead of entering the month every time you enter any transaction, it is better to use the TEXT function to automatically extract and display the month name from the purchase date. To do this, go to the first row in the Month column and enter the following:

=TEXT(tham chiếu của ô chứa ngày tương ứng, "mmm")
How to Create a Monthly Spending Tracker on Google Sheets
Using the TEXT function in Google Sheets

So if the date is in cell D2, the formula would look like this:

=TEXT(D2, "mmm")

This will return the first three letters of the month in the Month column. Once applied to the first cell, you can highlight the first cell and drag down to apply to other cells in the column.

Currency format

To add currency formatting to an amount column, select the entire column > More formats and click the dollar sign ($) on the toolbar. You can also find the option in the Format menu under Number . Here, you can choose Currency , Currency rounded , and Custom currency .

How to Create a Monthly Spending Tracker on Google Sheets
Custom Currency Format for Google Sheets

Custom currencies allow you to toggle how decimals and symbols are displayed, specify the type of dollars you're using, or choose a different currency entirely.

Create cost summary using functions

Once you've created a worksheet to record your expenses, switch to a new worksheet to summarize your expenses.

From cells B1 to M1, enter the first three letters of each month's name. Column N will be used for the total categories. Similarly, from the second cell of column A, start entering the expense categories.

Make sure to include the same categories as in the drop-down list of the first sheet. Once all the categories are entered, the next cell in the column will be used for the Monthly total.

Now that you have the basic structure, it's time to use the SUMIFS function to fill in the summary. This function will sum the selected range if multiple conditions are met.

In cell B2, enter the formula

=SUMIFS(Phạm vi cột Amount của trang tính đầu tiên, Phạm vi cột Category của trang tính đầu tiên, A2, Phạm vi cột Month của trang tính đầu tiên, B1).

Next, make all cell references absolute by selecting the cell reference and pressing F4 . Next, remove the $ sign that appears before 2 (in A2) and B (in B1).

How to Create a Monthly Spending Tracker on Google Sheets
SUMIFS function in Google Sheets

In the example, the function would look like this:

=SUMIFS(Sheet2!$C$2:$C$100,Sheet2!$B$2:$B$100,$A2,Sheet2!$E$2:$E$100,B$1)

After entering the formula, select cell B2 and drag down to the last cell before the Monthly total row. Then, select the entire range from cell B2 to the last cell before the Monthly total and drag right until column M .

Next, in column N2 , enter the formula =SUM(B2:M2) and drag down. Similarly, use the SUM function in Monthly total to add up the expenses of all categories incurred in a particular month.

Cost Summary in Google Sheets

Now, whenever you add expenses to the first sheet, Google Sheets will continue to add them to the relevant month and category in the summary. Plus, you can see how much you spend each month and how much you spent on a particular category for the year.

Visualize costs with pie charts and sparklines

To better understand your spending habits, you can visualize your expenses using Google Sheets.

First, add a sparkline to see how your spending on a specific category changes each month. In cell O2, enter the formula below:

=SPARKLINE(B2:M2)

Then scroll down to the last category. You can also customize the appearance of the sparkline.

How to Create a Monthly Spending Tracker on Google Sheets
Create sparklines in Google Sheets

Next, add a pie chart to see a detailed breakdown by category of total costs. To do this, select Insert >  Chart . This will add a new chart and open the chart editor in the sidebar.

From the chart type drop-down list, select Pie chart or 3D Pie chart . In Data range , add the range N1:N8 (or whatever your last cell is). Then, click Add label and the 4 squares icon to select the data range for the label.

Here, enter the range A1:A8 . Sheets will now display a pie chart showing your cost breakdown.

How to Create a Monthly Spending Tracker on Google Sheets
Chart Editor in Google Sheets

Use filters to view spending categories

If you want to sort or hide transactions, you can add filters to your Google Sheets. To do this, select the column you want to sort and click the Create a filter icon in the toolbar. Three lines will appear next to the column header.

Click on the three lines and you will see a list of options. In the category columns, you can use Filter by value to quickly display a category, use the checkbox below the title to select and deselect them by name.

You can also use Sort A to Z in the amount column to view your transactions from least expensive to most expensive, or Sort Z to A to sort in reverse.

How to Create a Monthly Spending Tracker on Google Sheets
Use filters to sort expenses in Google Sheets

With this simple expense tracker, you can keep a good track of your spending. Plus, unlike templates you download from the internet, if something goes wrong with your spreadsheet, you'll know exactly how to fix it.

You can use the Google Sheets app on your phone to easily enter transactions on the go. That way, you don't have to deal with everything at the end of the month. Better yet, you can create a spreadsheet to plan your spending in advance.

Sign up and earn $1000 a day ⋙

Leave a Comment

Comment utiliser la fonction IMPORTRANGE dans Google Sheets

Comment utiliser la fonction IMPORTRANGE dans Google Sheets

La fonction IMPORTRANGE dans Google Sheets nous aidera à lier les données de différentes feuilles de calcul, à rechercher rapidement des données et à récupérer des données en fonction des exigences d'affichage de la fonction.

Comment se débarrasser de #Div/0 dans Google Sheets

Comment se débarrasser de #Div/0 dans Google Sheets

Découvrez comment résoudre l’erreur #Div/0 dans Google Sheets avec nos conseils pratiques et nos formules. Améliorez votre efficacité dans la gestion des données.

Comment effacer tous les filtres dans Google Sheets

Comment effacer tous les filtres dans Google Sheets

Découvrez comment effacer tous les filtres dans Google Sheets rapidement et efficacement. Les filtres permettent de trier et d

Comment utiliser la fonction Image dans Google Sheet

Comment utiliser la fonction Image dans Google Sheet

Découvrez comment utiliser la fonction Image dans Google Sheets pour insérer des images en ligne via des URL. Suivez nos étapes simples!

Comment fusionner des onglets dans Google Sheets

Comment fusionner des onglets dans Google Sheets

Apprenez à fusionner des onglets dans Google Sheets pour améliorer l

Comment supprimer facilement le cache dans Google Sheets

Comment supprimer facilement le cache dans Google Sheets

Le chargement de Google Sheets prend-il trop de temps ? La solution peut être de supprimer le cache. Découvrez les avantages de cette action essentielle pour améliorer votre expérience utilisateur.

Comment envelopper du texte dans Google Sheets [Tous les appareils]

Comment envelopper du texte dans Google Sheets [Tous les appareils]

Lors de l

Comment compter les valeurs uniques dans Google Sheets

Comment compter les valeurs uniques dans Google Sheets

Apprenez à utiliser la fonction COUNTUNIQUE pour compter les valeurs uniques dans Google Sheets, ainsi que d

Comment sélectionner des lignes alternées dans Google Sheets

Comment sélectionner des lignes alternées dans Google Sheets

Découvrez comment appliquer des couleurs alternées dans Google Sheets et améliorer la lisibilité de vos feuilles de calcul avec des astuces de formatage.

Erreur danalyse de formule Google Sheets - Comment réparer

Erreur danalyse de formule Google Sheets - Comment réparer

Découvrez comment résoudre l

Instructions pour colorier les cellules et le texte dans Google Sheets

Instructions pour colorier les cellules et le texte dans Google Sheets

Google Sheets prend en charge de nombreuses façons de mettre en évidence des cellules dans Google Sheets ou des cellules contenant du texte dans Sheets. L'article ci-dessous vous guidera sur la façon de mettre en évidence des cellules et du texte dans Google Sheets.

Why doesnt the freezer have a light but the refrigerator does?

Why doesnt the freezer have a light but the refrigerator does?

Refrigerators are familiar appliances in families. Refrigerators usually have 2 compartments, the cool compartment is spacious and has a light that automatically turns on every time the user opens it, while the freezer compartment is narrow and has no light.

2 Ways to Fix Network Congestion That Slows Down Wi-Fi

2 Ways to Fix Network Congestion That Slows Down Wi-Fi

Wi-Fi networks are affected by many factors beyond routers, bandwidth, and interference, but there are some smart ways to boost your network.

Comment rétrograder diOS 17 à iOS 16 sans perte de données avec Tenorshare Reiboot

Comment rétrograder diOS 17 à iOS 16 sans perte de données avec Tenorshare Reiboot

Si vous souhaitez revenir à la version stable d'iOS 16 sur votre téléphone, voici le guide de base pour désinstaller iOS 17 et rétrograder d'iOS 17 à 16.

Que se passe-t-il dans le corps lorsque vous mangez du yaourt tous les jours ?

Que se passe-t-il dans le corps lorsque vous mangez du yaourt tous les jours ?

Le yaourt est un aliment merveilleux. Est-il bon de manger du yaourt tous les jours ? Si vous mangez du yaourt tous les jours, comment votre corps va-t-il changer ? Découvrons-le ensemble !

Quel type de riz est le meilleur pour la santé ?

Quel type de riz est le meilleur pour la santé ?

Cet article présente les types de riz les plus nutritifs et comment maximiser les bienfaits pour la santé du riz que vous choisissez.

Comment se réveiller à lheure le matin

Comment se réveiller à lheure le matin

Établir un horaire de sommeil et une routine de coucher, changer votre réveil et ajuster votre alimentation sont quelques-unes des mesures qui peuvent vous aider à mieux dormir et à vous réveiller à l’heure le matin.

Latest Bathroom Tower Defense Codes and How to Enter Codes

Latest Bathroom Tower Defense Codes and How to Enter Codes

Get Bathroom Tower Defense Roblox game codes and redeem them for exciting rewards. They will help you upgrade or unlock towers with higher damage.

4 façons dont lIA améliore les téléviseurs intelligents

4 façons dont lIA améliore les téléviseurs intelligents

De la meilleure qualité d’image et de son au contrôle vocal et bien plus encore, ces fonctionnalités alimentées par l’IA rendent les téléviseurs intelligents bien meilleurs !

Pourquoi ChatGPT est meilleur que DeepSeek

Pourquoi ChatGPT est meilleur que DeepSeek

Au début, les gens avaient de grands espoirs pour DeepSeek. En tant que chatbot IA commercialisé comme un concurrent sérieux de ChatGPT, il promet des capacités et des expériences de chat intelligentes.

Découvrez Fireflies.ai : le secrétaire IA gratuit qui vous fait gagner des heures de travail

Découvrez Fireflies.ai : le secrétaire IA gratuit qui vous fait gagner des heures de travail

Il est facile de manquer des détails importants lorsque l’on note d’autres éléments essentiels, et essayer de prendre des notes tout en discutant peut être distrayant. Fireflies.ai est la solution.

Comment élever un axolotl dans Minecraft et apprivoiser une salamandre dans Minecraft

Comment élever un axolotl dans Minecraft et apprivoiser une salamandre dans Minecraft

Axolot Minecraft sera un excellent assistant pour les joueurs lorsqu'ils opèrent sous l'eau s'ils savent comment les utiliser.

DeceptionAds Warning: New Malicious Ad Type Exploits Legitimate Services to Display Fake CAPTCHAs

DeceptionAds Warning: New Malicious Ad Type Exploits Legitimate Services to Display Fake CAPTCHAs

DeceptionAds is a new form of malvertising, where scammers use legitimate advertising platforms and make sure their malicious sites avoid censorship as much as possible.

What to do when you see the “AirTag Found Moving With You” warning?

What to do when you see the “AirTag Found Moving With You” warning?

Wondering what the Airtag Found Moving With You warning is? Its a safety feature on AirTags designed to prevent them from being used to track you.

Le pouvoir mystérieux du nombre 33 en numérologie

Le pouvoir mystérieux du nombre 33 en numérologie

En numérologie, le nombre 33 est souvent considéré comme un nombre puissant et mystique. Voici ce que vous devez savoir sur le nombre 33 en numérologie.

What is dark energy?

What is dark energy?

Dark energy is driving the accelerating expansion of the universe, but its nature remains a complete mystery. Heres what you need to know about dark energy.