How to Create an Income and Expense Report in Excel

Tracking where your money comes from and where it goes is the first step toward financial control. An income and expense report is your best tool for the job, and you can build a powerful one using a tool you already have: Microsoft Excel. This article will guide you step-by-step through creating a personal or business income and expense report in Excel, from a blank sheet to an interactive dashboard.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, Why Keep an Income and Expense Report?

Dedicating a little time to tracking your finances brings huge rewards. A clear, organized report isn't just a list of numbers, it's a roadmap of your financial health. It helps you:

• Gain Financial Clarity: See exactly where your money is going. You might be surprised to see how much those daily coffees or recurring subscriptions add up.
• Budget More Effectively: When you know your actual spending habits, you can create a realistic budget that you can actually stick to.
• Simplify Tax Season: Come tax time, you'll have all your income and deductible expenses neatly organized in one place, saving you hours of stress and frantic searching through receipts.
• Spot Problems Early: A well-maintained report helps you identify negative cash flow trends before they become serious problems, allowing you to make adjustments proactively.

Step 1: Setting Up Your Excel Spreadsheet

Let's start with a clean slate. Open a new Excel workbook and create your transaction log. This is where you'll record every dollar that comes in or goes out.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Create Your Core Columns

On the first sheet (you can rename it "Transactions" by right-clicking the tab at the bottom), set up the following columns in the first row:

• A1: Date
• B1: Category
• C1: Description
• D1: Income
• E1: Expense
• F1: Balance

Make these headers stand out. Select Row 1, go to the "Home" tab, and click the Bold icon. You can also use the paint bucket icon to give the row a subtle background color for better readability.

Format for Success

Proper formatting makes your spreadsheet easier to use and harder to mess up.

• Dates: Select all of column A by clicking the "A" at the top. On the Home tab, find the Number format dropdown (it usually says "General") and select "Short Date." Now, whenever you type a date, Excel will format it correctly.
• Currency: Select columns D, E, and F. In the same Number format section, click the dollar sign ($) icon or choose "Currency." This will automatically format your numbers with a currency symbol and two decimal places.

Step 2: Entering Your Transactions

This is where you'll spend most of your time initially. Your goal is to log every single financial transaction. Be diligent! Even small purchases count.

Here’s how to fill out each row for a given transaction:

• Date: The date the transaction occurred.
• Category: Assign a category. Being consistent here is crucial for analysis later. Examples include "Client Payment," "Salary," "Rent," "Groceries," "Utilities," "Software," or "Marketing."
• Description: A brief note for context. Instead of just "Groceries," you might put "Weekly shopping at Safeway." Instead of "Client Payment," you'd name the client.
• Income: If money came in, enter the amount in this column.
• Expense: If money went out, enter the amount in this column.

Important: For any single transaction, you should only have a value in either the Income column or the Expense column, never both.

Example Entries:

Here's what a few transactions might look like:

(Example data not shown here as per instructions)

Step 3: Add Formulas to Automate Calculations

Now let's make Excel do the heavy lifting. We’ll use formulas to calculate totals and maintain a running balance so you always know where you stand.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Running Balance Formula

The "Balance" column gives you a real-time look at your cash flow. It updates automatically with each new entry.

1. Click on cell F2. This is the balance after your first transaction. The formula is simply your income minus your expense for that row. Type: =D2-E2
2. Now, click on cell F3. The formula here is different. It needs to take the previous balance (from F2) and then add the new income and subtract the new expense. Type: =F2+D3-E3
3. Click on cell F3 again, and you'll see a small green square at the bottom-right corner. Click and drag this square down the column. Excel will smartly adjust the formula for each row, always looking at the cell above it.

As you add new transactions, your balance will automatically update!

Calculate Your Totals

At the top of your sheet or on a separate summary sheet, it's useful to see quick totals for income, expenses, and net profit/loss. Let's add them above your data.

1. Insert a few new rows at the top. Right-click on row 1 and select "Insert" a few times.
2. Create some labels in column A, like "Total Income," "Total Expenses," and "Net Income."
3. In the cell next to "Total Income" (e.g., B2), use the SUM function to add up everything in your Income column. If your data starts on row 6, the formula would be: =SUM(D6:D1000) Using a large number like 1000 means you won't have to update the formula as you add more entries.
4. Do the same for "Total Expenses" in cell B3, using the expense column: =SUM(E6:E1000)
5. Finally, for "Net Income" in cell B4, simply subtract the expenses total from the income total: =B2-B3 This number is your bottom line - what’s left over after all expenses are paid.

Step 4: Visualize Your Data with a Dashboard

A list of transactions is useful, but charts and summaries make the story of your finances much easier to understand. A PivotTable is the easiest way to do this. We'll create it on a new sheet.

Create a PivotTable to Summarize Spending

1. Click anywhere inside your transaction data.
2. Go to the "Insert" tab and click "PivotTable."
3. Excel will automatically select your data range. Choose "New Worksheet" as the place to put the PivotTable and click "OK."
4. A new sheet will appear with a "PivotTable Fields" panel on the right. Now, just drag and drop:

Instantly, you'll have a clean summary table showing exactly how much you spent in each category. You could also drag Income into the "Values" box to see where your money is coming from.

Add Charts to See the Big Picture

With your PivotTable selected, creating a chart is just a click away.

1. Click inside your new PivotTable.
2. Go to the "Insert" tab and choose a chart type. A Pie chart is great for seeing your expense categories as a percentage of the total. A Bar chart is better for comparing the absolute value of spending between categories.
3. Select the chart you prefer, and Excel will generate it for you. This visual gives you an at-a-glance understanding of your financial breakdown. Now you can easily see if "Dining Out" is taking up more of your budget than you realized.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 5: Pro Tips for an Even Better Report

Once you've got the basics down, a few small tweaks can make your report more efficient and error-proof.

• Freeze Panes: On your transactions sheet, click on the cell just below your headers (e.g., A7 if you have 6 header rows). Go to the "View" tab, click "Freeze Panes," and select "Freeze Panes." Now, when you scroll down your long list of transactions, your column headers will always stay visible.
• Use Data Validation for Categories: To avoid typos like "Utiities" vs. "Utilities," create a dropdown list for your Category column. Create a list of your standard categories in a separate tab or an empty column. Then, select the Category column, go to "Data" > "Data Validation," choose "List" from the "Allow" dropdown, and select your list of categories as the source.
• Conditional Formatting: Make important numbers pop. Select your Net Income cell. Go to "Home" > "Conditional Formatting" > "Highlight Cells Rules" > "Less Than..." and set it to format cells with a value less than 0 with a red fill. This gives you a quick visual warning if you're in the red.

Final Thoughts

Building an income and expense report in Excel is a straightforward process that grants you complete control over your financial data. By consistently logging your transactions and using formulas and PivotTables, you can transform a simple spreadsheet into a valuable tool for budgeting, tax preparation, and strategic financial planning.

Once set up, the biggest challenge is the manual work of entering data from bank statements, sales platforms, and payment processors. We know that process can be a chore, which is why we built Graphed. It connects directly to your data sources - like Shopify, Google Analytics, QuickBooks, and Stripe - and builds interactive, real-time dashboards for you automatically. You can just ask questions in plain English like, "show me my profit margin by product this month," and get an answer in seconds, saving you from any manual data entry in a spreadsheet.