Fixing the We couldn't find any data in your Excel workbook error in Power BI

Picture the scene. A finance analyst has spent a week building a workbook of monthly sales by region. It looks good. The headers are bold, the numbers reconcile, the totals sit neatly at the bottom. They upload it to Power BI, click import, and get a polite dialog saying it can't find any data. The analyst is certain the data is there, because they can see it on screen. The manager is waiting for a dashboard by Friday.

This comes up a lot with Australian businesses moving reporting off spreadsheets and into Power BI. It is easy to fix once you know the rule, and the rule is not obvious from the wording of the message. Power BI does not care about the cells you can see. It looks for Excel tables. If your data is not in a table, as far as Power BI is concerned the workbook is empty.

The message itself reads:

We couldn't find any data formatted as a table. To import from Excel into the Power BI service, you need to format the data as a table. Select all the data you want in the table and press Ctrl+T.

Microsoft's own article on this error is short and accurate. It walks through the steps, and if you have never built a table in Excel, follow it. What it does not do is explain why a workbook that looks perfectly structured still fails, or what to do when the obvious fix does not work. That is the part we spend most of our time on.

What the error is really saying

Excel has two ways of thinking about a block of data. A range is just a set of cells. A table is a range that Excel has been told has headers. Once it is a table, Excel gives you filters, banded rows, structured references and a proper name you can use in formulas. Power BI uses that same object. When it imports a workbook, it looks for tables and loads each one as a table in the semantic model. If it finds none, you get the error.

That is why a sheet can look fine and still fail. Bold headers, borders, a light fill and a neat layout are all formatting. None of them creates a table. We see this most often in workbooks that were built by copying from an old report, a PDF or a web page. The formatting survives the copy and the table object does not, so the sheet looks like a table to a human and looks like a grid of loose cells to Power BI.

The fix, step by step

The steps are simple:

1. Open the workbook in Excel.
2. Select the cells that hold your data. The first row must contain your column headers.
3. Go to Insert > Table, or press Ctrl+T.
4. Make sure My table has headers is ticked, then click OK.
5. Save the workbook.
6. Go back to the Power BI service, choose Create, pick the Excel tile, and import the file again.

For most files, that is the whole job. Two things catch people out, though.

The first is headers. Every column needs a header, the header must be unique, and it cannot be blank. The usual culprit is a merged title row sitting above the real headers. Merged cells do not play well with tables, and Excel will sometimes refuse to convert the range at all. Move the title out of the data area, or put it on its own sheet, and keep the table to the clean grid of data underneath.

The second is where the file lives. If you are on Excel 2016 and the workbook is saved to OneDrive for work or school, the copy you are editing may not be the copy Power BI reads. Microsoft's guidance is to use File > Publish in Excel so the saved version is the one that gets imported. We have watched a team fix the table, save locally, re-import, and then spend an hour wondering why nothing changed. Before you blame the table, check which copy you are looking at.

Watch for the totals row

Once the table is working, a different problem tends to show up. Excel has a handy Total Row option for tables, and people love it. The trouble is that Power BI sees the total row as just another row of data. If you then sum the column in your report, the total gets counted again and your numbers double.

Our rule for any workbook that feeds a report is one table per sheet, no totals row, and a separate summary sheet that is never imported. Let the source hold the data and let the presentation live somewhere else. It feels like extra work for the first week and saves hours after that.

The same logic applies to column names. If someone renames a header from "Region" to "Sales Region" after the report is built, the refresh can fail or a visual can go blank, because the query is looking for a column that no longer exists. Agree on header names up front and treat them as an interface. Changing one is a change to the report, not a tidy-up.

Where Excel works well as a source

Excel is a perfectly good data source for the right job. A budget, a target list, a product-to-category mapping or a small set of reference data all fit well. These files change slowly, they are owned by one person or one team, and they are easy to audit.

It becomes painful when the workbook is also the place where people type notes, colour cells as a status system, insert ad hoc rows and paste in data from three different systems. At that point the import is not really about Power BI. The workbook is doing five jobs, and Power BI can only sensibly read one of them. If you find yourself adding exceptions to the import to handle each new habit, that is the signal to move the data somewhere it can be managed properly.

For a one-off analysis, importing the file directly is fine. For a report that will be refreshed and shared, we prefer to keep the workbook in SharePoint or OneDrive for business. That gives the refresh a stable location, and it means the person updating the numbers is not the only person who knows where the file is. It also makes it much easier to see who changed what when a number looks wrong.

What we have seen go wrong

A few patterns come up again and again on client engagements, so it is worth naming them.

Someone inserts a blank row inside the table to make the sheet look nicer. The blank row imports as a row of nulls, and any count of records is now off by one.

A pivot table is pasted onto the same sheet as the source table. Power BI does not import a pivot table as data. It imports the table, which is fine, but the pivot looks like it is part of the dataset and people assume the numbers match when they do not.

A table is converted, then the sheet is sorted by a column with mixed data types. The numbers stored as text sort to the bottom, the dates come through as text in one row out of a hundred, and the model gets a column of mixed types that breaks every measure built on it.

None of these are Power BI problems. They are workbook hygiene problems that Power BI happens to expose. The fix is the same each time: keep the table clean, keep the types consistent, and keep the presentation separate.

The bigger picture

If you are planning a move from spreadsheets to proper reporting, this error is a useful early warning. It tells you the workbook was built for a human reader, not for a data model. Fix the table and you have solved the first problem. The next one is usually a sheet with three date formats and a column that changes meaning halfway down. That is the point where a proper pipeline in Microsoft Fabric or Azure Data Factory starts to earn its keep, because it can enforce the types and the rules before the data reaches the report.

We do this kind of work regularly, from cleaning up a single workbook through to moving a whole reporting estate off spreadsheets. If you are looking at either end of that, our Power BI consultants and Microsoft Fabric consultants can help you choose how far to go. Our view is to start small. Fix the table, clean the headers, and then decide whether the workbook deserves a place in a proper data pipeline.

Source: Microsoft Learn, "Fix Excel Workbook Data Error in Power BI" (https://learn.microsoft.com/en-us/power-bi/connect-data/service-admin-troubleshoot-excel-workbook-data)