By now, you must have understood that you can directly publish the workbooks using Microsoft Excel 2016 and later versions into Power BI. It can then be used for creating intuitive and creative dashboards as well as reports. All of them can be shared with other co-workers for collaboration.

When you publish an Excel workbook into the Power BI, you need to take care of a few things like the below ones:

1. You must be using the same account for Office, OneDrive (Business), and Power BI to publish the workbook.
2. You are not allowed to publish a workbook that is not having any data in it and is completely empty. Also, it is not allowed to publish a workbook having unsupportive content with respect to Power BI.
3. You are not allowed to publish the workbook, which is either password protected or encrypted with Protection Management.
4. You must enable the modern authentication before publishing the workbook. If it is not enabled, the Publish button will be either greyed out or not present in the menu.

The first step is to open Microsoft Excel. Go to Select “File” -> Publish. Now you can perform two options: Export or Upload.

If you use the Upload option for the workbook, you will be able to communicate with your workbook in the same way as communicating in Microsoft Excel Online. Pinning the selection to dashboards is allowed from the workbook. The complete workbook or its selected sheets can be shared with others using the Power BI interface.

If you use the Export option for the workbook, all the table data, as well as the data model, will get exported to the Power BI dataset. Then you will be able to create dashboards and reports in Power BI.

You can easily publish the local files using Microsoft Excel. It is not required that it is present in SharePoint Online or OneDrive (Business). One thing to note here is that only Microsoft Excel 2016 and later versions are compatible to perform this operation if they have the Office 365 license. Standalone installations of Excel 2016 will also be able to Publish, but in that case, the workbook has to be present in SharePoint Online or OneDrive (Business).

After selecting the option “Publish,” you will be able to choose the workspace where you would like to publish it. It can be either the personal workspace you are using or the group workspace in which you have access.

When it gets published to Power BI, all the content present in the workbook will get imported, and the local file will be kept separate as it is. In order to make any changes in the file present in Power BI, you need to publish the latest version once again. Otherwise, you need to perform a data refresh by scheduling an automatic refresh for the workbook in the Power BI interface.

If you are publishing it using the standalone installation, save the workbook in OneDrive (Business). You need to click the “Save to Cloud” option, then provide the location where you want to save in OneDrive. Once you use the Publish button, two options will be there, as mentioned earlier also.

In this option, the workbook will be similar to how it looks in Microsoft Excel Online. But only limited features will be present for pinning the elements on the dashboards from the worksheet.

Editing the workbook is not allowed in Power BI. For making any changes in the data, click the Edit button. Now open in in Excel on your computer or online. Now those changes will be saved in OneDrive (Business). Also, datasets will not be created automatically. The uploaded workbooks will be given a unique icon to identify them like the ones that are uploaded and not imported.

You should wisely use the Upload option if there are Charts, Pivot Tables, or data in worksheets that are required in Power BI.

In this option, all the table data and the data model will be exported in the newly created dataset. If there are any Power View sheets, those will be created again in the form of reports.

You are allowed to edit the workbook directly, and the changes will automatically get in sync with the dataset present in the Power BI interface in every 60 minutes. If it is an urgent update, you can use the Publish option present in Excel to get the changes updated instantly. All the visualizations will be updated as well.

So you can use any option from the above ones. Once it is done, Microsoft Excel will automatically sign in to Power BI using the current account. Then it will publish it and you will able to check the progress. Once the process is complete, you will get a successful message, as shown in the below image.

All files having a size less than 1 GB can be uploaded easily into Power BI. You must already be aware that a workbook has two parts in it. They are:

1. The Data Model
2. The remaining report is covering the core content structure.

The following limits are placed in the system for saving the workbook in OneDrive (Business).

1. The complete workbook can’t exceed 1 GB size.
2. The core content worksheet can have a maximum size of 30 MB.

Thus, if your content worksheet has more than 30 MB size, you will not be able to upload the workbook in Power BI. The elements which make your core content size greater than 30 MB are Images, Colored worksheets, Clip arts, Shaded cells, and Text boxes.

Therefore, you need to remove these elements from your workbook (if possible).

If there are Data Models, then you can remove the data from the worksheet and store the data in the Model only. You can also consider creating a data model that is memory efficient in order to decrease the size.

If the data has been imported to Excel using the Excel Data Tab or Power Query tab, then the workbook will have similar data as present in the tables. Big tables eat up the content size and make it greater than 30 MB. Therefore, if you will remove the tables and keep the data in the model itself, the size will be decreased considerably.

Use the below points while importing data into Excel.

1. Do not use the option “Load to worksheet” from Power Query. This will ensure that the data is not imported in the worksheet at all and goes directly to the model.
2. Use the Excel Data tab to navigate to Existing Connections. Go to “Only create connection.” Now remove all the native tables that are created while the initial import was done.
3. Do not use the option “Table” present in the Import Data screen.
4. You can use the workbook size optimizer in order to decrease the overall workbook size. This will work if you have a data model present in it.

So this was how you could publish using Microsoft Excel into Power BI easily.