How to Update Excel File in Power BI

Keeping your Power BI reports fresh with the latest Excel data shouldn't be a frustrating, manual chore. If you're tired of constantly re-uploading spreadsheets to see the latest numbers, there's a much better way. This guide will walk you through exactly how to set up automatic refreshes for your Excel files, whether they're stored in the cloud or saved on your local computer.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Automating Your Excel Refresh is a Game-Changer

Manually updating reports is more than just an inconvenience, it's a bottleneck that slows down decision-making. Every time you have to find the latest Excel file, open Power BI Desktop, hit refresh, and republish the report, you lose valuable time. Worse, it creates a risk that team members are looking at outdated dashboards, leading to strategies based on stale information.

By connecting Power BI directly to your Excel file and scheduling a refresh, you create a "set it and forget it" system. Your reports update themselves on a schedule you define, ensuring that every time you or your stakeholders look at a dashboard, the data is as current as possible. Let's cover the two primary methods for making this happen.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: The Easiest Way - Using OneDrive for Business or SharePoint

The simplest and most reliable way to keep your Excel data updated is by storing the file in a cloud location that Power BI loves: OneDrive for Business or SharePoint. Since these are all part of the Microsoft ecosystem, they are designed to work together seamlessly. This method is highly recommended because it does not require you to install any special software like a data gateway.

The Power BI service can connect directly to your OneDrive or SharePoint file in the cloud, pull the latest data, and refresh your report automatically.

Step-by-Step Guide for OneDrive/SharePoint Refresh

1. Save Your Excel File in OneDrive or SharePoint

First, make sure your Excel workbook is saved in either your OneDrive for Business account or a SharePoint document library. This acts as the single source of truth that Power BI will connect to.

2. Get the Correct Connection Path

This is the step where most people trip up. You cannot just use the URL from your web browser's address bar. You need to get the direct path to the file.

• Navigate to your Excel file in OneDrive or SharePoint online.
• Click the three dots (...) next to the file name and select "Open" > "Open in app." This will launch the file in your desktop Excel application.
• Once the file is open, go to File > Info.
• Click the Copy path button. This copies the direct URL you need.

Your copied path will look something like this but without the extra parameters at the end. You'll want to delete everything after ?web=1.

https://yourcompany-my.sharepoint.com/personal/your_name/Documents/Reports/Financials.xlsx?web=1

All you need is this part:

https://yourcompany-my.sharepoint.com/personal/your_name/Documents/Reports/Financials.xlsx

3. Connect Power BI Desktop to the Excel File

Now, open a new or existing Power BI Desktop file.

• Go to the Home tab and click Get Data > Web.
• In the dialog box that appears, paste the cleaned-up URL you copied from Excel.
• Click OK. Power BI will ask you to sign in.
• Choose Organizational account and sign in with your Microsoft 365 credentials (the same ones you use for OneDrive/SharePoint).
• Once authenticated, a Navigator window will appear, showing you the sheets and tables inside your Excel file. For best results, always load data from an Excel Table rather than a sheet (e.g., 'Sheet1'). Using tables is more robust and less likely to break if the sheet structure changes.
• Select your table and click Load or Transform Data to start building your report.

4. Publish Your Report and Set the Schedule

Once your report is designed, publish it to the Power BI Service.

• Click Publish from the Home tab in Power BI Desktop and choose a workspace.
• Now, navigate to the Power BI Service in your browser (app.powerbi.com).
• Find the dataset associated with your report (it will have the same name), click the three dots (...) and select Settings.
• Under Data source credentials, your credentials should already be good to go. Power BI should have recognized it's an OAuth2 connection to your own account. If there's an error, click "Edit credentials" and sign in again.
• Scroll down to Scheduled refresh. Toggle it on.
• Choose your desired refresh frequency (e.g., Daily) and add specific times for the refresh to happen. Power BI sends refresh failures to the dataset owner, so you'll be notified if something goes wrong.

That's it! Your report will now automatically fetch the latest data from your Excel file in OneDrive without any manual intervention.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Working with Local Excel Files (Using a Data Gateway)

What if your Excel file has to stay on a local computer or a company file server due to security policies or other constraints? You can still set up an automatic refresh, but it requires one extra component: the On-premises data gateway.

Think of the gateway as a secure bridge. It's a small application you install on a computer within your local network that allows the Power BI cloud service to securely connect to your on-premises data, refresh it, and send it back to the cloud.

Step-by-Step Guide for Local File Refresh

1. Install and Configure the Data Gateway

First, you need the gateway itself. It should be installed on a computer that is always on and connected to the internet - a server is ideal, but a reliable desktop PC will work too. A laptop that regularly goes to sleep is not a good choice.

• Download the On-premises data gateway (standard mode) from the official Microsoft Power BI website.
• Run the installer. During the setup, you'll be asked to sign in with your Power BI account.
• Choose "Register a new gateway on this computer."
• Give your gateway a descriptive name (e.g., "Company_Marketing_Gateway") and create a recovery key. Save this key somewhere safe! You will need it if you ever have to restore the gateway.

2. Connect Power BI to Your Local File

In Power BI Desktop, the process is straightforward:

• Go to Get Data > Excel Workbook.
• Browse your local computer or network drive and select the Excel file.
• Load your data and build your report or dashboard just as you normally would.
• When ready, Publish the report to the Power BI Service.

3. Link the Dataset to the Gateway

This critical step connects your cloud report to your local file via the gateway.

• In the Power BI Service, find your dataset and go to its Settings page.
• You'll see a section called Gateway and cloud connections. Power BI should automatically detect that the dataset needs a gateway and show you the one you just installed.
• You will need to map your local data source to the gateway. You'll see the file path of your Excel workbook listed with a prompt to add it to the gateway.
• Click into it and you'll be asked to provide credentials for the computer where the file is stored. This is typically your Windows username and password. This gives the Power BI gateway service permission to access that specific file on that machine.

4. Schedule the Refresh

With the gateway connection established, the final step is identical to the OneDrive method:

• On the same Settings page, scroll down to the Scheduled refresh section.
• Toggle it on, set your desired frequency and times, and apply the changes.

Now, at the scheduled time, the Power BI Service will send a request through the gateway, which will access your local Excel file, retrieve the latest data, and update your dataset in the cloud.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Common Problems and Quick Fixes

Setting up refreshes can sometimes hit a snag. Here are a few common issues and how to resolve them:

• Refresh Failed Notification: If you get an email about a failed refresh, the first things to check are: Is the data gateway online? Has your Windows password changed (for local files)? Has the source file been moved or renamed?
• Column Name Changes: If you change a column header, sheet name, or table name in your source Excel file, your Power BI query will break. Ensure the structure of your Excel file remains consistent.
• File is Locked: If someone has the Excel file open on their desktop when a refresh is scheduled to run, the gateway might not be able to access it. It's best practice to have the source Excel file in a location where it isn't constantly being edited, especially during planned refresh times.
• The Wrong OneDrive URL: A very common error is using the browser URL instead of the direct path. If you have trouble connecting, double-check that you used the "Copy path" button from within the desktop Excel application.

Final Thoughts

Automating your reports by setting up a scheduled refresh in Power BI is a fundamental step toward creating a reliable and efficient analytics workflow. Whether you use the simple cloud-based method with OneDrive or configure a gateway for local files, the investment of time and setup saves you from the tedious task of manual updates and ensures your insights are always based on the latest data available.

While Power BI's scheduled refresh solves one part of the data puzzle, we know that getting insights often requires pulling data from many different places like Google Analytics, Shopify, your CRM, and ad platforms, not just Excel. This is where we designed Graphed to simplify the entire process. Rather than spending time building reports and managing connections, you can connect all your marketing and sales sources in seconds and let our AI handle the rest. Just describe the dashboard you want in plain English, and Graphed builds it for you with real-time, automatically updating data, helping you get from data to decision faster than ever.