How to Create an Income and Expense Report in Tableau

Tracking income and expenses is the financial foundation of any business, but a static spreadsheet can only tell you so much. If you're ready to create a dynamic, interactive view of your financial health, this guide will walk you through building a powerful income and expense report using Tableau, from structuring your data to building an interactive dashboard.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Why Use Tableau for This Report?

You’re probably already using Excel or Google Sheets to log your transactions. That's a great start, but it's often a manual, time-consuming process to get real insights. Every month, you refresh pivot tables, copy and paste data, and cautiously update charts, hoping a formula doesn't break.

Tableau elevates this process by turning your flat file into an analytical goldmine. Here’s why it’s a better choice for financial reporting:

• Interactivity: Instead of static charts, Tableau allows you to create dashboards where you can click on a category to filter your entire view, drill down from a yearly summary to daily transactions, and explore your data in real time.
• Automation: Once connected to your data source (like a Google Sheet), you can set up scheduled refreshes. This means no more manually downloading CSVs and updating your report every week.
• Visual Power: Tableau is built for data visualization. You can move beyond basic tables and create compelling bar charts showing profit and loss, line charts tracking revenue trends, and more - all in one place.

Step 1: Get Your Data Ready for Tableau

Before you can build anything, your data needs to be in a format that Tableau can understand easily. This is the most important step, great data structure leads to a smooth and flexible reporting experience.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Structure Your Data

Your goal is to have a simple, "long" table of transactions. Avoid layouts with separate columns for income and expenses or different months spread across an Excel sheet. Instead, structure your data with these columns:

• Date: The date of the transaction. (e.g., 2023-11-15)
• Description: A brief note about the transaction. (e.g., "Monthly Software Subscription")
• Category: The financial bucket it belongs to. (e.g., "Software," "Client Payments," "Office Supplies")
• Type: A simple tag to identify it as "Income" or "Expense".
• Amount: The value of the transaction. Important: keep all amounts as positive numbers. We’ll teach Tableau how to handle the math later.

Here’s a small example of what your data should look like in Excel or Google Sheets:

Step 2: Connecting Your Data and Building the Basic Report

With your data prepped, it's time to bring it into Tableau and build your first table.

1. Connect to your data: Open Tableau Desktop or Tableau Public. On the left-hand side under "Connect," choose the file type where your data is saved (e.g., "Microsoft Excel" or "Google Sheets"). Navigate to your file and open it.
2. Verify your data: Tableau will show you a preview of your data on the "Data Source" screen. Ensure the columns look correct. If everything is fine, click on "Sheet 1" at the bottom left to enter the worksheet view.
3. Build a text table: Let's start with a classic income vs. expense breakdown by category.
4. Add Grand Totals: To see the total income, expenses, and overall profit/loss, go to the Analysis menu at the top, select "Totals," and then click "Show Column Grand Totals" and "Show Row Grand Totals."

You now have a clean, simple text table summarizing your finances. It’s a good start, but now let’s make it more visual.

Step 3: Creating Visualizations with Calculated Fields

To create powerful charts like a profit and loss statement, you'll need to teach Tableau one simple trick: how to make expenses negative. This is done with a calculated field.

Create a "Signed Amount" Calculated Field

A calculated field lets you create new data from the data you already have. We'll use it to assign a negative value to all expenses, which is critical for accurate financial summaries and visualizations.

1. In the Data pane (the left sidebar), click the small dropdown arrow at the top and select "Create Calculated Field."
2. Name the field something clear, like "Signed Amount".
3. In the formula box, enter the following logic:

IIF([Type] = "Expense", [Amount] * -1, [Amount])

This formula tells Tableau: "If the Type column for a row says 'Expense', then take the Amount and multiply it by -1. For everything else (i.e., 'Income'), just use the positive Amount." Click OK to save it.

You now have a new measure called Signed Amount in your Data pane which can be used to easily calculate net profit.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Building a Profit & Loss Bar Chart

A bar chart makes it incredibly easy to see your most profitable and costly categories at a glance.

1. Open a new worksheet by clicking the icon at the bottom.
2. Drag Category to the Columns shelf.
3. Drag your new Signed Amount field to the Rows shelf. Tableau will automatically create a vertical bar chart.
4. Add Color: To make the chart even clearer, drag the Signed Amount field again, but this time drop it onto the Color icon in the Marks card. By default, Tableau will apply a color gradient (e.g., orange for negative/expenses and blue for positive/income).

Instantly, you can visually distinguish between money-making categories and cost centers.

Building a Trend Over Time Line Chart

How have your revenue and expenses trended over the past year? A line chart is perfect for answering this.

1. Open a new worksheet.
2. Drag Date to the Columns shelf. By default, Tableau will likely show "YEAR(Date)". Click the dropdown on the blue Date pill and select "Month" (the continuous option with a green calendar icon) to see a monthly trend.
3. Drag Signed Amount to the Rows shelf.

This creates a single line showing your net income over time. To see income and expenses separately:

• Drag the Type field onto the Color icon in the Marks card.
• Drag Amount (the original, positive values) to the Rows shelf, replacing Signed Amount.

You’ll now have two separate lines — one for income and one for expenses — making it easy to spot trends, seasonality, or months where spending spiked.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Putting it All Together in an Interactive Dashboard

A dashboard is where you combine your individual worksheets into a single, dynamic view. This is where Tableau truly shines.

1. Create a new dashboard: At the bottom of the window, next to your worksheets, click the icon for "New Dashboard".
2. Set your dashboard size: In the left-hand pane under "Size," you can set a fixed size or choose "Automatic" to have the dashboard adapt to the screen size.
3. Drag in your worksheets: From the left pane, you will see a list of the sheets you created (the table, the bar chart, the line chart). Simply drag and drop them onto the canvas area. You can resize and rearrange them as you see fit.
4. Enable interactivity: This is the most powerful part. Click on your bar chart showing categories. In the top-right corner of that chart's container, you'll see a few small icons. Click the one that looks like a funnel, which is the "Use as Filter" option.

Now, click on any bar in your category chart (e.g., the "Advertising" bar). Watch as your line chart and financial summary table automatically update to show data for only that category! This lets you effortlessly explore your finances without building dozens of different report versions.

Final Thoughts

This guide walks you through building a foundational income and expense report in Tableau, turning a simple spreadsheet into a clear, interactive financial dashboard. You've learned how to properly structure your data, build core visuals like profit & loss charts and trend lines, and combine them into a tool that helps you explore your business's performance.

While Tableau is an incredibly powerful tool for custom analysis, the time it takes to set up data, create calculated fields, and design dashboards can be a significant hurdle for busy teams who just need immediate insights. At Graphed , we’ve automated this entire process. We allow you to connect your financial data sources (like QuickBooks or Google Sheets) and build real-time reports and dashboards just by describing what you want in simple, natural language. It’s like having a data analyst on your team, giving you the answers you need in seconds instead of hours of manual work.