How to Create an Income and Expense Report in Google Sheets

Tracking where your money comes from and where it goes is the foundation of financial health for any business. An income and expense report is your best tool for this, but you don't need complicated accounting software to get started. This guide will walk you through creating a powerful, flexible, and free income and expense report in Google Sheets, step-by-step.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Use Google Sheets for Your Income and Expense Report?

While dedicated accounting tools have their place, Google Sheets offers several unique advantages for small businesses, freelancers, and marketers who want a clear financial picture without a steep learning curve or monthly fee.

• It’s 100% free. All you need is a Google account. There are no subscription fees or hidden costs, making it the perfect choice for budding entrepreneurs or teams keeping a close eye on their budget.
• It's accessible anywhere. Your report lives in the cloud, so you can access and update it from your desktop, laptop, tablet, or phone. No more being tied to a single computer.
• It's great for collaboration. Need to share your financial report with a business partner, accountant, or team member? With Google Sheets, you can securely share access, leave comments, and see changes in real-time.
• It’s highly customizable. You have full control over the layout, categories, and formulas. You can tailor your report to perfectly match how your business operates.

Step 1: Create a New Google Sheet and Set Up Your Environment

First, let's create a blank canvas for your report. Get this initial setup right, and everything that follows will be much easier.

1. Navigate to sheets.google.com.
2. Click on the big "+" icon labeled "Blank" to start a new spreadsheet.
3. Immediately, give your spreadsheet a clear, descriptive name. Click on "Untitled spreadsheet" in the top-left corner and rename it something like "2024 Income & Expense Tracker" or "Company Financials." This helps you stay organized.
4. It’s a good practice to create two separate tabs (or "sheets") within your document. At the bottom-left, double-click on "Sheet1" and rename it to "Transactions." Click the "+" icon to the left of it to add another sheet, and rename that one "Dashboard" or "Summary."

The "Transactions" sheet is where you will do all the data entry. The "Dashboard" sheet will pull from this data to give you a clean, at-a-glance summary. This separation keeps your report tidy and professional.

Step 2: Define Your Columns in the "Transactions" Sheet

With your "Transactions" sheet open, it’s time to create the headers for your data columns. These columns will form the structure of your financial log. For a solid report, you need five key columns:

In row 1, enter the following headers:

• A1: Date - The date the transaction occurred. Keeping your records in chronological order makes them easy to review.
• B1: Description - A brief, clear description of the transaction. E.g., "Monthly coffee supply," "Invoice #123 payment," or "Facebook Ads spend."
• C1: Category - This is the most crucial column for effective reporting. Assigning a category to every transaction lets you see exactly where your money is going. We’ll talk more about this in a moment.
• D1: Income - For all money coming into the business.
• E1: Expense - For all money going out of the business.

Pro Tip: It's best to have separate "Income" and "Expense" columns rather than a single "Amount" column with positive and negative numbers. This makes writing formulas for totals much simpler later on.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Choosing Your Categories

Consistent categorization turns a simple log into a powerful analytical tool. Brainstorm the common types of income and expenses your business has. Your categories might include:

• Income Categories: Client Revenue, Product Sales, Affiliate Income.
• Expense Categories: Software/SaaS, Marketing & Ads, Rent, Utilities, Office Supplies, Payroll, Professional Fees.

Be specific enough to be useful, but not so specific that you have hundreds of categories. You can always add more later.

To ensure consistency and prevent typos, use Google Sheets' data validation feature to create a dropdown list for your categories. Here's how:

1. Click on the top of Column C to select the entire column.
2. Go to the menu and select Data > Data validation.
3. Next to "Criteria," choose "List of items."
4. In the box to the right, enter your expense and income categories, separated by commas (e.g., Software, Rent, Utilities, Client Revenue).
5. Ensure "Show dropdown list in cell" is checked and click "Save."

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 accidentally typing "Software" one time and "Saas" the next!

Step 3: Begin Entering Your Financial Data

Now you're ready to start populating your sheet. Go through your bank statements, receipts, and invoices and start logging each transaction on a new row. Remember:

• For money you receive, enter the amount in the "Income" column and leave the "Expense" column blank.
• For money you spend, enter the amount in the "Expense" column and leave the "Income" column blank.

Your "Transactions" sheet will quickly start to fill up and should look something like this:

Example table:

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Building Your Summary Report in the "Dashboard" Sheet

Managing long lists of transactions is no fun. This is where your second sheet, "Dashboard," comes into play. Here, we'll use simple formulas to automatically summarize the data from your "Transactions" log.

Click over to your "Dashboard" sheet. We'll start by building three key performance indicators (KPIs) at the top: Total Income, Total Expenses, and Net Savings/Profit.

Calculate Totals

In cell A1, type the label "Total Income." In cell B1, add this formula:

=SUM(Transactions!D2:D)

Similarly, in cell A2, type "Total Expenses." In cell B2, use:

=SUM(Transactions!E2:E)

These formulas look at the "Transactions" sheet and sum up everything in columns D and E, respectively. By leaving the row number off the end of the range (D2:D), the formula will automatically include any new rows you add later.

Finally, in cell A3, type "Net Profit." In cell B3, enter:

=B1-B2

Summarizing by Category

Now for the fun part: let's break down your expenses and income by category using the powerful SUMIF formula. This formula adds up numbers in a range that meet a specific criterion.

Below your KPIs, set up a simple summary table. In cell A6, list your expense categories (e.g., "Software," "Marketing"). In B6, we'll write the formula for each category.

If your list starts with "Software" in A6, the formula for its total in B6 would be:

=SUMIF(Transactions!C:C, A6, Transactions!E:E)

This formula tells Google Sheets:

• Look in column C of the 'Transactions' sheet (Transactions!C:C).
• Find cells that match the value in this sheet's cell A6 ("Software").
• Sum the corresponding values from column E (Transactions!E:E) (expenses).

You can drag this formula down to cover all your categories. To do the same for income categories, change the last range to column D instead of E.

Your dashboard will then look like a neat, automated summary with totals per category and overall financial health!

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 5: Visualizing Your Data with Charts

Numbers are great, but visuals are better for quick insights. Let's add a couple of charts to our dashboard to bring the data to life.

A pie chart is perfect for showing your expense breakdown.

1. Highlight your expense categories and their totals (the summary table you just built).
2. Go to the menu and select Insert > Chart.
3. Google Sheets often defaults to a pie chart, which suits us. If not, go to the Chart Editor on the right and select "Pie chart" under "Chart type."
4. Use the "Customize" tab to add a chart title like "Expense Breakdown by Category" and adjust colors to your preference.

You can move and resize the chart anywhere on your dashboard. When you add new transactions, the summary table and chart will update automatically!

For comparing income sources, a bar chart works well. Repeat the process for your income summary table.

Final Thoughts

By following these steps, you've created a clean, maintainable, and highly effective income and expense report in Google Sheets. You can now log your transactions systematically and see a real-time summary of your financial health, complete with visual aids that help you make better business decisions.

Manually updating a spreadsheet is a great way to understand your finances, but as your business grows, this can become time-consuming. You might have revenue data in Shopify, ad spend in Google Ads, and payments in Stripe. That’s why we designed Graphed to help. Instead of manual entry, it connects directly to your business tools, pulling all your financial data into one place automatically. Build powerful, real-time dashboards easily by describing what you want to see—saving you hours each week without touching a spreadsheet formula.