How to Update Data in Power BI from Excel

Manually updating your Power BI reports by re-uploading Excel files is a tedious and error-prone chore that eats up valuable time you could be spending on actual analysis. If your workflow involves regularly exporting data into Excel and then pulling it into Power BI, there's a much better way to work. This guide will walk you through setting up an automatic refresh, ensuring your dashboards always display the latest data from your spreadsheets without you having to lift a finger.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Automating Your Excel Refresh Matters

You already know the frustration. It's Monday morning, and you have to get the weekly performance report ready. You download a new CSV from your CRM, clean it up in Excel, save it with a new name like "Sales_Data_This_Week_v2_FINAL.xlsx," open Power BI, change the data source, cross your fingers that nothing breaks, and publish the update. Just rinse and repeat every week.

Switching to an automated process does more than just save time. It creates a "single source of truth" that improves the reliability and trustworthiness of your reporting. When you and your team know the dashboard is always up-to-date, you can make decisions with confidence. This frees up your mental energy from data wrangling and puts it where it belongs: uncovering insights and driving business growth.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Easiest Route: Use OneDrive or SharePoint

The simplest and most reliable method to automatically update data in Power BI from Excel is to store your Excel file in a cloud location like OneDrive for Business or a SharePoint Online folder. The Power BI service is designed to seamlessly connect to these Microsoft cloud sources, which eliminates a lot of the technical hurdles you'd face with a local file.

Why does this work so well? Power BI's cloud service can directly access the file in OneDrive or SharePoint without needing a bridge or gateway. It handles the authentication using your Microsoft account, making the refresh process smooth and secure.

Step-by-Step: Connecting to an Excel File in OneDrive

Here’s how to set it up from scratch for a hassle-free, automated update process.

1. Use "Format as Table" in Excel

Before you even think about Power BI, open your Excel workbook. Select all your data (headers included) and format it as a table by pressing Ctrl + T (or going to the Home tab and clicking "Format as Table").

This is arguably the most important best practice. When you format data as a formal Excel Table, Power BI learns to expect a structured object. When you add new rows to your table, they are automatically included in the future refresh - no need to manually adjust ranges. Give your table a descriptive name (e.g., "SalesData") in the "Table Design" tab.

2. Save the File to OneDrive or SharePoint

Instead of saving to your local "My Documents" folder, save the Excel file to a location within your OneDrive for Business or a SharePoint document library. If you have the OneDrive desktop client installed, this is as simple as saving it to the local OneDrive folder that syncs with the cloud.

3. Connect from Power BI Desktop

Now, open a new Power BI Desktop file.

• Go to the Home tab and click on Get data.
• Select Excel workbook.
• Navigate to your local OneDrive sync folder and open the Excel file you just saved. You're simply telling Power BI where the file lives, because it's in a OneDrive folder, publishing it will work perfectly.
• In the Navigator window, you'll see your sheet and the named table (it has a blue header icon). Always select the table, not the raw sheet. This ensures your connection stays robust when rows are added.
• Click Transform Data to open the Power Query Editor for any cleaning or shaping, or click Load to import it directly.

4. Build and Publish Your Report

Create your visuals and build your report as you normally would. Once you're happy with it, click Publish from the Home tab and select the workspace in the Power BI service where you want the report to live.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Schedule the Automatic Refresh

Once your report is published, the real magic happens in the Power BI service (app.powerbi.com). This is where you tell Power BI how often you want it to check your Excel file for new data.

1. Navigate to your workspace in the Power BI Service.
2. Find the dataset associated with your new report (look for the orange icon). Hover over it, click the three-dot menu (...), and select Settings.
3. Expand the Data source credentials section. You should see a message about your OneDrive credentials. Click Edit credentials, sign in with your Microsoft account (using OAuth2), and link your account. This gives Power BI permission to access the file.
4. Now, scroll down and expand the Scheduled refresh section.
5. Toggle the switch to On.
6. Select your preferred Refresh frequency (either Daily or Weekly).
7. Select your Time zone.
8. Click Add another time to specify when you want the refresh to run. You can add multiple time slots per day (e.g., 9:00 AM and 5:00 PM).
9. Click Apply.

That's it! Now, Power BI will automatically check your Excel file at the times you scheduled. As long as you update the master Excel file in OneDrive with new rows (making sure they are part of the table), your report visuals will reflect the latest information after the next scheduled refresh. No more manual uploads.

The Alternative: Refreshing Local Excel Files with a Gateway

What if company policy dictates that the Excel file must stay on a local computer or a shared network drive? In this scenario, you can't use the direct OneDrive method. Instead, you need to set up an On-premises data gateway.

Think of the gateway as a secure bridge or pipeline. It’s a small piece of software you install on a computer that is always-on (like a server or your primary workstation). This bridge allows the Power BI service in the cloud to securely "reach through" into your local network, find your Excel file, and pull the latest data for a refresh.

Graphed

Still Building Reports Manually?

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

Watch the Demo

When to Use a Gateway

• Your Excel file is on your computer's local hard drive (e.g., "C:\Reports\Sales.xlsx").
• The file is on a shared network drive within your organization (e.g., "\SERVER\Shared\Data\Revenue.xlsx").
• Security protocols prevent you from storing the data in the cloud.

Steps for Setting Up a Gateway Refresh

The process is more involved than the OneDrive method, but works reliably once configured.

1. Download and Install the Gateway: In the Power BI service, click the download icon in the top right and select Data Gateway. Download and run the installer for a standard gateway on a machine that will remain on and connected to the network.
2. Configure the Gateway: Follow the on-screen prompts. You'll sign in with your Power BI account to register the gateway with your tenant. Give it a descriptive name.
3. Add Your File as a Data Source: After configuring, go to Settings > Manage connections and gateways in the Power BI service. Click into your gateway settings and add a new connection. Select "File" and input the full path to your Excel file (e.g., "C:\Data\finance_report.xlsx"). You'll also need to provide your Windows username and password for the computer where the file is stored.
4. Map the Dataset: Open the dataset settings for the report you published. Under the "Gateway and cloud connections" section, you should see options to map your dataset's local file source to the gateway connection you just created.
5. Schedule the Refresh: Once mapped, the "Scheduled refresh" section will become available, and you can set up a schedule an update frequency and times as described in the previous section.

Now, when a scheduled refresh is triggered, the Power BI service sends a query to your gateway, the gateway securely fetches the data from the local Excel file, and then sends it back up to the Power BI service to update your dataset.

Best Practices and Common Mistakes to Avoid

• Never Change the File Name or Path: Once the connection is set up, don’t rename or move the Excel file. Doing so will break the path, and your next refresh will fail.
• Keep a Consistent Column Structure: Try not to change column header names or delete columns in your Excel file. If you need to make structural changes, you'll have to open the report in Power BI Desktop, refresh the preview in Power Query, and republish it.
• Use a Master File: Don't create new versions of the file every week ("Data_Week1.xlsx", "Data_Week2.xlsx", etc.). Have one master file - e.g., "Live_Sales_Data.xlsx" - and simply add new rows to the table within it.
• Think About Data Volume: While convenient, refreshing from Excel can become slow if your file size grows to hundreds of thousands of rows. For very large datasets, consider a more robust data source like a database or data warehouse.

Final Thoughts

By shifting your Excel files to a cloud location like OneDrive or properly configuring a gateway for local files, you can turn your manual update process into an automated, reliable system. It saves hours of tedious work and ensures that your stakeholders are always making decisions based on the most current data available.

While mastering Power BI refresh schedules is a great step forward, it often just moves the bottleneck. Now the setup and data prep are the slow parts, especially when your analysis requires combining Excel files with data from other platforms like Google Analytics, Shopify, or your CRM. At Graphed, we built a tool to solve this exact problem. Simply connect all your data sources in seconds, and then just ask for what you need in plain English - like "Show me a comparison of Facebook ad spend and Shopify revenue over the last quarter." It builds an auto-updating dashboard for you in real time, so you can skip the complex setup and get straight to the insights.