From Spreadsheet to Dashboard: Connecting Power BI to Your Financial Model

You’ve built a beautiful financial model. Clean structure, transparent formulas, integrated financial statements, scenario switches. It does everything it needs to do.

And then the CFO asks: “Can you turn this into a dashboard?”

What they actually mean is: “I don’t want to open Excel to see how the business is performing. I want to open a browser, click a link, and see the answer. I want the board to see the answer. And I want it to update itself.”

That’s what Power BI does. And connecting it to your existing Excel model is far simpler than most people think.

Why Not Just Use Excel Charts?

Excel charts are fine for a static report. But they have fundamental limitations for ongoing reporting:

No interactivity. An Excel chart shows one view. If the board member wants to filter by region, drill down to a product line, or switch between months, they need to ask you to rebuild the chart — or learn to navigate your model. Neither is ideal.

No live access. Sending an Excel file means sending a snapshot. It’s out of date the moment you email it. And if three people have three different versions, nobody knows which numbers are current.

No mobile access. Board members read reports on iPads, phones, and laptops. Excel workbooks are painful on mobile devices. Power BI dashboards are designed for any screen size.

No governance. An Excel file can be modified by anyone who opens it. A Power BI dashboard shows the data without exposing the model. The board sees the outputs; the model remains under your control.

The Architecture

The setup is straightforward:

1. Your Excel model sits on SharePoint, OneDrive, or a shared drive. It contains all the logic, calculations, and outputs.
2. Power BI connects to the Excel file using Get Data → Excel Workbook. It reads the output tables from your model.
3. You build visuals in Power BI — charts, KPIs, tables, slicers — using the data from those output tables.
4. Power BI refreshes on schedule — daily, hourly, or on demand. When the model updates, the dashboard updates.
5. Users access the dashboard via the Power BI Service (browser) or Power BI mobile app. No Excel required.

The critical design decision is this: Power BI reads from your model; it doesn’t replace it. Your Excel model remains the single source of truth for all calculations. Power BI is the presentation layer — a window onto the model, not a replacement for it.

Designing Your Model for Power BI

To connect cleanly, your Excel model needs structured output tables. Power BI works best with tabular data — rows and columns, no merged cells, no blank rows, consistent headers.

If your model’s output sheets are already clean tables (actuals vs. forecast by month, KPIs by period, scenario comparison tables), Power BI can read them directly.

If your outputs are formatted for print — with subtotals, blank rows for spacing, merged header cells — you’ll need to add a “data” sheet that presents the same information in a flat table format. This is a one-time setup: a sheet with columns for Period, Category, Actual, Forecast, Variance, and so on.

Name your output tables as Excel Tables (Ctrl+T). Power BI recognises named tables and tracks them even if rows are added or removed.

Building the Dashboard

With the data connected, building the dashboard follows the same principles as any good data visualisation. Here’s where the approach matters more than the tool.

Start with the questions

Before you drag a single field onto the canvas, ask: what questions does this dashboard need to answer? Typically for a financial model dashboard:

• Are we ahead or behind forecast this month?
• What’s driving the variance?
• What does the full-year outlook look like?
• How do the key metrics trend over time?
• What changes under different scenarios?

Each question becomes a visual. No more, no less. If a chart doesn’t answer a question the board is asking, it doesn’t belong on the dashboard.

Use the right chart type

This is where most dashboards go wrong. Some guidelines:

• Time series → line chart. Not a bar chart. Lines show trends; bars show discrete comparisons.
• Actual vs. forecast → a combined bar (actual) and line (forecast) chart, or a waterfall showing the variance.
• Composition → stacked bar or a treemap. Not a pie chart. Pie charts are almost never the right choice.
• Single KPI → a card or gauge. Revenue this month: £4.2m. Variance to budget: +3.2%. EBITDA margin: 22.1%.
• Comparison across categories → horizontal bar chart, sorted by value.

Build for the audience

Board members scan dashboards in seconds. Put the most important information in the top-left corner (where the eye lands first). Use large, clear KPI cards for the headline numbers. Push supporting detail to lower sections or drill-through pages.

Use consistent colours: green for favourable, red for adverse, grey for neutral. Don’t use colour for decoration — use it for meaning.

DAX: The Calculation Layer

Power BI uses DAX (Data Analysis Expressions) for calculations. If your Excel model already computes everything, you may not need much DAX. But a few measures are almost always useful:

Variance to budget:

Variance = [Actual] - [Budget]
Variance % = DIVIDE([Actual] - [Budget], [Budget], 0)

Year-to-date:

YTD Revenue = TOTALYTD(SUM(Financials[Revenue]), Calendar[Date])

Conditional formatting logic:

Variance Color = IF([Variance] >= 0, "Green", "Red")

DAX is powerful but has a learning curve. For a basic financial dashboard connected to an Excel model, you’ll need perhaps 10-20 measures. The rest is drag-and-drop.

Refresh and Governance

Power BI can refresh from an Excel file on SharePoint or OneDrive automatically — typically up to 8 times daily on a Pro licence. This means:

• You update the model with actuals on the 5th of each month
• Power BI refreshes and the dashboard shows the latest numbers
• The board reviews the dashboard at any time, knowing the data is current
• Nobody emails a spreadsheet

For more frequent refreshes (real-time trading data, daily cash positions), you’ll need Power BI Premium or a direct database connection rather than an Excel file.

What You’re Really Building

A Power BI dashboard connected to your Excel model isn’t just a prettier way to show charts. It’s a shift in how the finance function communicates with the rest of the business.

The model becomes the engine — trusted, auditable, maintained by experts. The dashboard becomes the interface — accessible, interactive, self-service. Finance stops being the team that produces reports and starts being the team that provides insight.

That’s a more powerful position. And it starts with connecting your model to Power BI.

Will Wardle designs and builds Power BI dashboards connected to Excel financial models for clients across sectors. His approach to data visualisation is informed by the principles of Edward Tufte and Stephen Few.