How to Create a Monthly Expense Report in Google Sheets

Tracking your monthly spending doesn't have to involve complicated software or costly subscriptions. With just a few simple steps, you can build a powerful and custom monthly expense report right in Google Sheets. This article will guide you through creating an expense tracker from scratch, complete with formulas for quick analysis, charts for visual insights, and pivot tables for ultimate control.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Start with a Google Sheets Expense Template (The Quick Way)

If you need an expense report right now and don't want to build one from the ground up, Google Sheets has you covered. It offers built-in templates that are functional and easy to use.

Here’s how to find and use one:

1. Go to the Google Sheets homepage.
2. At the top of the page, click on "Template gallery".
3. Scroll down to the "Personal" section. You'll find a "Monthly budget" template. While named "budget," it functions perfectly as an expense tracker. There is also an "Expense report" template under the "Work" section, which is great for business use.
4. Click on the template to create a new sheet based on it.

These templates are pre-filled with formulas and charts. All you need to do is clear out the sample data and start adding your own expenses. It’s a fantastic starting point, but if you want more control and a report tailored exactly to your needs, building one yourself is the best way to learn.

How to Build an Expense Report from Scratch

Let's create a custom report. This approach allows you to decide exactly what information you want to track and how you want to see it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Set Up Your Spreadsheet and Column Headers

First, open a new, blank Google Sheet. The most important step is deciding what data you need to collect for each expense. A good starting point includes the following columns. Title them in the first row (A1, B1, C1, and so on):

• Date: The date the expense occurred.
• Category: The type of expense (e.g., Software, Marketing, Office Supplies, Travel).
• Item/Description: A specific note about the purchase (e.g., "Monthly Adobe Subscription," "Facebook Ad Campaign," "New Desk Chairs").
• Amount: The total cost of the expense.
• Payment Method: How you paid (e.g., Business Credit Card, PayPal, Bank Transfer).

To make your sheet look clean, select the first row, make the text bold, and consider adding a background color to the cells to make the headers stand out.

Step 2: Use Data Validation for Clean Categories

To keep your expense categories consistent (and avoid typos like "Sofware" vs. "Software" which can ruin your formulas), we can create a dropdown list using Data Validation. This is a game-changer for data accuracy.

1. Create a new tab in your sheet and name it "Lists".
2. In the first column of the "Lists" sheet, list all your potential expense categories, one per cell. For example:
3. Go back to your main expense tracker sheet. Select the entire 'Category' column by clicking on the column letter 'B'.
4. Go to the menu and click Data > Data validation.
5. In the pop-up window:

Now, when you click on any cell in the Category column, a dropdown arrow will appear, letting you select from your pre-defined list. No more guesswork or misspellings!

Step 3: Format Your Data Correctly

To make sure your numbers are treated as currency and your dates are recognized as dates, apply some simple formatting:

• For the 'Amount' column: Click the column letter 'D' to select it all, then go to Format > Number > Currency. Now, any number you enter will automatically be formatted with a dollar sign and two decimal places.
• For the 'Date' column: Select column 'A', then go to Format > Number > Date to ensure all entries are in a consistent date format.

Analyzing Your Expenses with Key Formulas

Once you start entering your expenses, you need a way to summarize them. Let’s create a small "Summary" or "Dashboard" area at the top right of your sheet to see key metrics at a glance.

Total Monthly Expenses

This is the most fundamental calculation. In an empty cell (say, G2), type the label "Total Expenses". In the cell next to it (H2), enter the following formula:

=SUM(D2:D)

This formula sums every value in the 'Amount' column (from cell D2 down to the very last row), giving you a live total of all your expenses.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Expenses by Category

Understanding where your money is going is crucial. The SUMIF formula is perfect for this. It lets you sum amounts based on a specific category.

In your summary area, list out your main categories (e.g., in cells G3, G4, G5). Then, in the cell next to your first category (H3), use this formula:

=SUMIF(B2:B, G3, D2:D)

Here’s what that formula does:

• B2:B is the range it will check (the 'Category' column).
• G3 is the criterion it looks for (in this case, the category name you wrote in cell G3, like "Software").
• D2:D is the range it will sum if the criterion is met (the 'Amount' column).

You can drag this formula down to apply it to your other categories. Now you have an automatic breakdown of spending for each category.

Other Useful Formulas

• Average Expense Amount: To see the average cost of your transactions, use the AVERAGE formula.

=AVERAGE(D2:D)

• Number of Transactions: To count how many expenses you've logged, use COUNTA.

=COUNTA(D2:D)

Making Your Report Visual with Charts

Numbers are great, but a visual chart can make your spending patterns instantly clear. A pie chart is a classic and effective way to see your expense breakdown by category.

1. Select the data from your "Summary by Category" table you just created with the SUMIF formulas. Highlight both the category names and their total amounts.
2. Go to the menu and click Insert > Chart.
3. Google Sheets will often intelligently choose a pie chart by default. If not, you can select "Pie chart" from the Chart type dropdown in the Chart editor on the right.

You can now see a simple, easy-to-understand visualization of which categories are taking up the biggest portion of your spending. You can drag and drop this chart anywhere on your sheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Advanced Analysis with Pivot Tables

For even more powerful and flexible reporting without writing a single formula, use a pivot table. This tool is spectacular for quickly summarizing data. Let's create the same "spending-by-category" summary using a pivot table instead.

1. Click anywhere inside your expense data.
2. Go to the menu and click Insert > Pivot Table.
3. You'll be asked where to put it. Choose "New sheet" to keep things clean and click Create.
4. A new tab will open with the Pivot table editor on the right. Now, just tell it what you want to see:

Instantly, you have a perfectly formatted table showing the sum of expenses for each category. But the power of pivot tables is in their flexibility. Want to see how much you spent with each payment method? Just drag the "Payment method" field into the "Columns" section. Now your report is broken down by category and payment method in seconds.

Final Thoughts

Creating a monthly expense report in Google Sheets is a free and flexible way to gain control over your finances. By setting up clean columns, using clever formulas, and visualizing your data with charts and pivot tables, you can transform a simple spreadsheet into a powerful analytical tool.

As you get comfortable, you’ll realize tracking expenses this way is straightforward. But for businesses, this manual process can quickly become tedious when your financial data lives across multiple platforms - QuickBooks for accounting, Shopify for sales, and other tools for marketing spend. We built Graphed to solve this problem by connecting to all your data sources automatically. Instead of exporting CSVs and tinkering with spreadsheets, you can create real-time, self-updating dashboards just by describing what you want to see, giving you hours back to focus on growing your business.