Files and lists · data source guide
Excel data in Power BI
Connector status
Connector in Microsoft's list
- Excel
The Excel connector is listed for Excel, Power BI (semantic models) and Power Query Online.
Checked against Microsoft Learn's Power Query connector list on 5 November 2026. Connectors change; check the list before you build.
How to get Excel data into Power BI
In order of preference for a report that refreshes on its own.
- 1
Format data as a table
In Excel, convert each data range to a table (Ctrl+T) and name it. Power BI then reads the table by name, so added rows and moved ranges don't break the query.
- 2
Store the file in SharePoint or OneDrive
A workbook in SharePoint or OneDrive for work or school refreshes in the Service without a gateway. A file on a local or network drive needs an on-premises data gateway.
- 3
Unpivot wide layouts
Budgets often have one column per month. In Power Query, select the month columns and Unpivot Columns so each row is one account and one month.
- 4
Several files: use Folder
For monthly files with the same layout, put them in one folder and use the Folder (or SharePoint folder) connector to combine them.
Typical Excel KPIs
Table and column names in the DAX are illustrative: rename them to match your model.
Actual vs budget
Actuals from the system of record against the Excel budget.
Var to Budget = [Actual] - SUM ( Budget[Amount] )Budget attainment
Actual as a share of budget.
Budget Attainment % = DIVIDE ( [Actual], SUM ( Budget[Amount] ) )Forecast accuracy
One minus the absolute error between forecast and actual, as a share of actual.
Forecast Accuracy = 1 - DIVIDE ( ABS ( [Actual] - SUM ( Forecast[Amount] ) ), [Actual] )Tracker completion
Share of tracker rows marked complete.
Completion % = DIVIDE ( CALCULATE ( COUNTROWS ( Tracker ), Tracker[Status] = "Complete" ), COUNTROWS ( Tracker ) )Target gap
Distance to target per row in a targets table.
Gap to Target = SUM ( Targets[Target] ) - [Actual]Modelling notes for Excel
- Merged cells and headers spread over several rows break imports; keep one header row.
- Typed numbers stored as text: set data types in Power Query, and watch for locale-specific decimal separators.
- People rename sheets; read tables by name, not sheets.
Templates for Excel data
Free page layouts with a background, theme JSON and PBIP project. Swap the sample tables for your own queries.
Executive template
Executive KPI ScorecardFree Power BI executive dashboard template: five headline KPIs, revenue vs target trend, unit breakdown and a scorecard table on one 16:9 page.
Finance template
CFO OverviewPower BI CFO dashboard template with revenue, EBITDA, cash and opex KPIs, a 13-week cash view, EBITDA bridge, P&L matrix and opex mix.
Finance template
Profit & Loss StatementPower BI P&L statement template: a full-height income statement matrix with actual, budget and prior year, margin KPIs and expense drivers.
Finance template
Budget vs ActualBudget vs actual Power BI template for cost-centre owners: actual, budget, variance and burn-rate KPIs, monthly combo chart and variance ranking.
Project & PMO template
Project PortfolioPower BI project portfolio (PMO) dashboard template with RAG status KPIs, roadmap Gantt slot, value vs effort matrix, status register and budget by programme.
Finance template
Accounts Receivable & CollectionsPower BI accounts receivable dashboard template: open and overdue AR, DSO trend, aging buckets by segment, a collector worklist and top overdue customers.
Power BI visuals for Project Management · More by department: Finance templates · Executive templates · Project & PMO templates
Visuals for Excel KPIs
Chart types that suit these KPIs, with data shapes and example prompts.
Visual
Bullet ChartExecutive KPI scorecards (5-10 KPIs in a grid); Sales attainment across reps or regions
Visual
Waterfall ChartRevenue walk: prior year → price → volume → mix → FX → current year; Budget reconciliation: budget → categorical variances → actual
Visual
KPI CardExecutive dashboard KPI row (revenue, customers, NPS, churn); SaaS metrics card (MRR, ARR, logo growth)
Visual
Gantt ChartProject schedules with phases, tasks, owners, and start/end dates; Resource capacity lanes (each person or team as a swimlane)
Excel and Power BI: questions
Do I need a gateway to refresh an Excel file?
Not when the workbook is in SharePoint or OneDrive for work or school. Files on a local or network drive need an on-premises data gateway.
Should I read a sheet or a table?
A table. Tables keep their name when rows are added or the range moves; sheet imports pick up stray cells and break when the layout changes.
Related data sources
Other sources teams often combine with this one.
Connector in Microsoft's list
SharePoint lists in Power BISharePoint lists in Power BI with the SharePoint Online list connector
No connector of its own in Microsoft's list
QuickBooks Online in Power BIQuickBooks Online data in Power BI
Connector in Microsoft's list
Azure SQL Database in Power BIAzure SQL Database in Power BI with the Azure SQL database connector
Excel is a trademark of its owner. Product names are used only to describe compatibility; VizForge is not affiliated with, endorsed by or sponsored by the owner or by Microsoft.
Design the Excel report page first
Lay out KPI cards and visual slots in the free Design Studio, then export the background, theme JSON and a PBIP project to bind to your data.
The design studio for Power BI. Free, in your browser, no sign-up.