How to Connect Power BI to Excel (On-Site/On-Premises and Cloud Services)

Microsoft Power BI is a tool for data analytics. The most evident advantage of using Power BI is its ability to access data from multiple sources.

Microsoft Excel is the most widely used data source in Power BI. Users may retain their data on-site/on-premises or in the cloud services (OneDrive, SharePoint, or Google Drive).

This blog article provides a thorough walk-through on connecting Power BI to Excel files saved on various mediums.

In this blog we will also give a step-by-step guide to enable automatic refresh settings and real-time updates for successful reporting.

1. How to connect Power BI to an Excel File kept locally/shared drives (On-site/On-Premises)

Power BI allows you to import or link directly to an Excel file maintained locally on your PC or on a shared network drive. However, automatic refresh for real-time updates calls for Power BI Gateway.

Step 1: Importing an On-Premises Excel File into Power BI

1. Open Power BI Desktop.
2. Click Home > Get Data > Excel.
3. Browse to the Excel file (.xlsx or .xls) stored on your local drive.
4. Click Open.
5. In the Navigator window, select the table or sheet you want to import.
6. Click Load to bring the data into Power BI.

Tip: If your Excel data isn’t in table format, convert it into a table in Excel by selecting the data and pressing Ctrl+T.

Step 2: Setting Up Automatic Refresh for On-Premises Excel

Since Power BI does not automatically refresh local files, you have two options:

Option A: Manual Refresh (Simple but Requires User Action)

• Open Power BI Desktop and click Refresh whenever the Excel file changes.
• Re-publish the updated report to Power BI Service.

Option B: Automatic Refresh Using Power BI Gateway

If your Excel file is stored on-premises (local PC or shared drive), set up Power BI Gateway for automatic updates:

1. Download & Install Power BI Gateway (Download Here).

• For organizations & team collaboration: Choose “Download Standard Mode”.
• For personal use (one-user access): Choose “Download Personal Mode”.

• We will be using the personal mode.
• Open the gateway application, select path and click on install.

2. Click on Sign in and sign in with your Power BI account.

3. Open Power BI Service (app.powerbi.com).

4. Sign in to Power BI Service (Power BI Service).

5. You can check the status of your gateway in Power BI Service by Clicking on the Settings (⚙️) icon in the top-right corner.

6. Select Manage connections and Gateways -> select On-premises data gateways -> select On-premises data gateways (Personal mode) from top drop-down.

The status for gateway is online.

7. Publish your report to Power BI Service.

8. Open the workspace in which you published your report and open the lineage view.

9. If you refresh the report now it will give an error as we haven’t configured the dataset’s gateway connection yet. Open the semantic model’s settings.

10. Go to Gateways and cloud connections and add select your gateway. And click Apply.

11. Set the Data source credentials by clicking on Edit credentials. Select Authentication Method (Windows) and Sign in.

12. Enable Scheduled Refresh, and Set the Refresh Frequency (Hourly, Daily, etc.).

13. Click Apply.

Now, Power BI fetches updated data from your local Excel file automatically.

Tip: The machine running the gateway must remain powered on. Also, ensure that the file path doesn’t change.

2. How to connect Power BI to a Cloud-Based Excel File (OneDrive, SharePoint)

Cloud-based Excel files in OneDrive or SharePoint Online allow Power BI to sync automatically and eliminate the need for manual updates. Cloud-based Excel files in OneDrive for Business and SharePoint Online are stored within SharePoint document libraries. This means the same method applies to both when connecting them to Power BI.

Step 1: Uploading an Excel File to OneDrive/SharePoint

1. Open OneDrive for Business or SharePoint Online.
2. Navigate to the desired folder and upload your Excel file.

Step 2: Getting the Correct File Link

1. Right-click on the uploaded file and go to details. In the details pane Copy the link.

Step 3: Connecting Power BI to OneDrive/SharePoint Excel

1. Open Power BI Desktop.

2. Click Home > Get Data > Web.

3. Paste the OneDrive/SharePoint link.

4. Authenticate your Microsoft 365 account.

5. Select the Excel table or sheet, then click Load.

Now, Power BI automatically syncs with your Excel file, allowing scheduled refresh for real-time updates!

3. How to connect Power BI to Google Drive (Google Sheets)

Google Drive is another popular cloud storage solution, and Power BI can connect to Google Sheets for live updates.

Step 1: Upload & Share Google Sheets (Excel Alternative)

1. Upload your Excel file to Google Sheets.

2. Click File > Share > Copy Link.

Step 2: Connecting Power BI to Google Sheets Using the Web Connector

1. Open Power BI Desktop > Get Data > Google Sheets.

Paste the Google Sheets link and authenticate using your google account. After authentication click on your relevant tables or sheets and click Load.

Power BI will now sync live data from your Google Sheet into reports.

4. How to set up an Automatic Refresh for Cloud-Based Excel Files

Step 1: Enabling Scheduled Refresh in Power BI Service

• Publish your Power BI report to the Power BI Service.
• Under Scheduled Refresh, toggle it On.
• Set a refresh frequency (hourly, daily, or weekly).
• Click Apply to save the settings.

Now, whenever the Excel file updates in OneDrive, SharePoint or Google Sheets, Power BI refreshes automatically.

Step 2: Troubleshooting Common Refresh Issues (best practices)

• Authentication issues → Re-enter credentials.
• Data source moved → Update the source path.
• Query timeout errors → Optimize Excel data before importing.

Comparison: On-Premises vs. Cloud-Based Excel in Power BI

Final Thoughts

• Use Power BI Gateway for automatic refreshing on-site Excel.
• Power BI supports live synchronization for Cloud Excel (One Drive/SharePoint).
• Choose the right method based on data refresh needs & collaboration requirements.

By following this guide, you can ensure Power BI always reflects the latest Excel data, boosting efficiency and real-time decision-making.

Featured Snippets For better SEO

Quick Answer:

• To connect Power BI to an on-premises Excel file, use ‘Get Data > Excel.’
• Use ‘Get Data > Web‘ for OneDrive/SharePoint and paste the file.
• URL. Turn on auto-refresh in Power BI Service for instantaneous changes.

If you are looking to develop your skills in Data Analytics, refer to Dynamic Web Training Excel courses and Power BI courses.