Sometimes Power BI ignores data when refreshing inputs from Excel sheets that have formatted cells

Hi Griff - This issue with Power BI skipping rows in large Excel files during data refreshes is fairly common, especially when dealing with files that have complex or inconsistent formatting, hidden rows/columns, or specific Excel features like merged cells

Try saving the Excel file in a different format, such as .xlsx (if it’s currently in .xls) or even as a CSV file. This can strip out any hidden formatting that might be causing issues and can often resolve data reading problems in Power BI.

Open the file in Excel and use Power Query (Get & Transform Data) to load the sheet. In Power Query, you can:
Remove unnecessary formatting, empty rows, or columns.
Standardize the data structure.
Save the cleaned data as a new worksheet or table within the same Excel file.
Then, in Power BI, connect to the newly transformed data instead of the original sheet.

In Excel, ensure that your data is explicitly defined as a Table (using Insert > Table in Excel). This can sometimes make data import more reliable as Power BI recognizes the entire table range explicitly.
Avoid having multiple tables on the same sheet, as this can cause confusion with range detection.

In Excel, go to each sheet, select all rows/columns, and Unhide any hidden rows/columns.
Delete any blank rows or columns at the end of the data range (especially if you notice blank rows being imported into Power BI).

These are the steps we follow when we format, hope this helps.