How to Connect Excel to Power BI — The Right Way

Excel is the most common data source for Power BI in small businesses. Most SMEs have years of data in spreadsheets before they start thinking about dashboards. Getting that data into Power BI correctly — and keeping it updated automatically — is more nuanced than it looks.

The three ways to connect Excel to Power BI

Option 1: Connect directly to a file on SharePoint or OneDrive (recommended)

If your Excel file lives on SharePoint or OneDrive, Power BI can connect to it directly via a URL. When the file is updated and Power BI refreshes, it reads the latest version automatically. This is the best option for most SMEs.

To do this: in Power BI Desktop, choose Get Data → SharePoint Folder or Web, paste the SharePoint URL of your file, and navigate to the table or range you want.

Option 2: Connect to a local file

You can connect Power BI to a file sitting on your local hard drive or a mapped network drive. The connection works fine in Power BI Desktop. The problem comes when you publish the report to Power BI Service — automatic refresh requires the Power BI Gateway to be installed and running on the same machine that holds the file.

For small teams, this is often more hassle than it is worth. Move the file to SharePoint and use Option 1 instead.

Option 3: Import the data (no live connection)

You can import the data from Excel into Power BI at a point in time, with no ongoing connection. This creates a static snapshot. Every time you want updated data, you have to re-import. For a live management report, this is not the right approach.

What your Excel file needs to look like

Power BI expects structured table data. Before connecting, check your Excel file:

• Data must be formatted as an Excel Table (Insert → Table) — not just a range of cells
• First row must be column headers — no merged cells, no multi-row headers
• No blank rows within the data
• No summary rows or totals rows — these will be imported as data rows
• Consistent data types in each column — do not mix numbers and text in the same column

Handling multiple sheets or multiple files

If your data is spread across multiple sheets in one workbook, Power BI will let you choose which sheets or tables to import. Each sheet becomes a separate table in the data model — you can then relate them to each other.

If your data is in multiple separate files — one per month, for example — use the SharePoint Folder connector. Point Power BI at the folder, and it will combine all files automatically. The columns must be named identically across all files for this to work.

Setting up automatic refresh

Once the report is published to Power BI Service, you can schedule automatic refresh. For SharePoint-connected files, no gateway is needed — Power BI can access SharePoint directly.

Go to Power BI Service → your dataset → Settings → Scheduled refresh. Set the frequency (daily, twice daily, or up to 8 times per day on Pro). The report will update automatically without anyone needing to open Power BI Desktop.

Common problems and fixes

• Refresh fails: the Excel file has been moved or renamed — update the data source path in Power BI Desktop and republish
• Column disappears after update: someone renamed the column in Excel — column names must stay consistent
• Wrong data type: a column that should be a number is being read as text — fix the column format in Excel, or transform it in Power Query
• Blank rows appearing: there are blank rows in the Excel file — remove them from the source