Reduce the size of an Excel workbook to view it in Power BI
You can upload any Excel workbook smaller than 1 GB to Power BI. An Excel workbook can have two parts: a Data Model, and the rest of the report—the core worksheet contents. If the report meets the following size limits, you can save it to OneDrive for work or school, connect to it from Power BI, and view it in Excel Online:
- The workbook can be up to 1 GB.
- The core worksheet contents can be up to 30 MB.
What makes core worksheet contents larger than 30 MB
Here are some elements that can make the core worksheet contents larger than 30 MB:
Consider removing these elements, if possible.
If the report has a Data Model, you have some other options:
- Remove data from Excel worksheets and store it in the Data Model. For more information, see the Remove data from worksheets section.
- Create a memory-efficient Data Model to reduce the overall size of the report.
To make any of these changes, you need to edit the workbook in Excel.
For more information, see File size limits for Excel workbooks in SharePoint Online.
Remove data from worksheets
If you import data into Excel from the Power Query Editor or the Excel Data tab, the workbook might have the same data in an Excel table and in the Data Model. Large tables in Excel worksheets might make the core worksheet contents more than 30 MB. Removing the table in Excel and keeping the data in the Data Model can greatly reduce the core worksheet contents of the report.
When you import data into Excel, follow these tips:
In Power Query Editor: Clear the Load to worksheet box under File > Options and settings > Query Options.
This imports data only into the Data Model, with no data in Excel worksheets.
From the Excel Data tab, if you previously selected Table in the import wizard:
Go to Existing Connections.
Select the connection, and then select Open. Select Only Create Connection.
Delete the original table or tables created during the initial import.
Workbook size optimizer
If your workbook contains a data model, you can run the workbook size optimizer to reduce the size of your workbook. For more information, see Download Workbook Size Optimizer.