How to Open Excel File in Power BI

Bringing your trusted Excel spreadsheets into Power BI is a common first step to creating powerful, interactive dashboards. The good news is that it’s surprisingly straightforward. This guide will walk you through the best methods for connecting your Excel files to Power BI, from a simple local file import to setting up automatically refreshing reports from a cloud source.

Why Connect Excel to Power BI Anyway?

You might be wondering why you should even bother. After all, Excel has charts and PivotTables. While Excel is fantastic for detailed calculations, data entry, and ad-hoc analysis, Power BI is built for a different purpose: creating intuitive, shareable, and automatically updating dashboards.

Here’s what you gain by connecting the two:

• Interactive Visualizations: Move beyond static charts. Power BI lets you and your team click on a chart element to filter the entire report, drilling down into insights in real-time.
• Automated Refreshes: Set up a connection once, and your published dashboards can update automatically on a schedule. Say goodbye to the weekly routine of downloading CSVs and rebuilding your reports.
• Data Consolidation: Your Excel file is just the beginning. In Power BI, you can easily combine that spreadsheet data with dozens of other sources, like Google Analytics, Salesforce, or your company’s database, to get a unified view of performance.

First, Get Your Excel File Ready for Power BI

A little bit of prep work in Excel can save you a lot of headaches later. Power BI works best with clean, structured data, often called "tabular" data. Before connecting your file, make sure it follows these best practices.

Format Your Data as a Table (This is a Must!)

This is the most important step. Instead of just having data in a range of cells, you need to format it as an official Excel Table. Just click anywhere within your data range and press Ctrl + T (or go to the Home tab and click "Format as Table").

Why is this so critical?

• It’s Structured: Power BI immediately recognizes it as a container of data with headers and rows. This makes importing clean and error-free.
• It’s Dynamic: When you add new rows to your Excel Table, Power BI will automatically include them the next time you refresh your data. If you use a simple range, you'd have to edit your connection every time.
• It’s Named: Tables are given names (like "Table1", though you can rename it in the "Table Design" tab), making them easy to identify in the Power BI Navigator window.

More Quick Formatting Tips

• Clear Column Headers: Make sure every column has a unique, descriptive header in the first row. Avoid merged cells in your headers.
• No Blank Rows or Columns: Remove any empty rows or columns within your data set. These can confuse Power BI during the import.
• Consistent Data Types: Ensure each column contains only one type of data. A "Date" column should only have dates, a "Sales" column should only have numbers, and so on.

Once your file is prepped, you're ready to connect.

Method 1: Import a Local Excel File from Your Computer

This is the most direct and common method for first-time users. It's perfect for when you have a static Excel file sitting on your desktop or in a local folder that you want to visualize quickly.

Here’s the step-by-step process:

1. Open Power BI Desktop. From the main Home ribbon, you'll see a button that says Get Data. Click on that.
2. In the menu that appears, select Excel Workbook.
3. A standard file browser window will pop up. Navigate to where your Excel file is saved, select it, and click Open.
4. After a moment, the Navigator window will appear. This is Power BI showing you all the "data objects" it can see inside your workbook. You'll see any worksheets (with a $ beside them) and any formatted Excel Tables.
5. Select your Table! You should see the name you gave your table (e.g., "SalesData"). Check the box next to it. A preview of your data will appear on the right. Avoid selecting the entire worksheet, as it might pull in extra junk like titles or notes that are outside your main data area.
6. Now you have two choices: Load or Transform Data.

Once your data is loaded, you’ll see its fields appear in the "Data" pane on the right-hand side of Power BI Desktop, ready for you to start dragging and dropping them into visuals.

Method 2: Connect to Excel Files in OneDrive or SharePoint

While importing a local file is easy, it has one major limitation: it doesn't automatically update online. If you want to publish a report to the Power BI Service (app.powerbi.com) and have it automatically refresh, you need to connect to a file that's stored in the cloud. OneDrive for Business and SharePoint are the perfect places for this.

Connecting to an Excel File in OneDrive for Business

Connecting this way ensures your Power BI report always reflects the latest changes you make in your Excel spreadsheet, without you having to manually re-upload anything.

1. In Power BI Desktop, go to Get Data and select Web.
2. Now, in a separate browser tab, navigate to your OneDrive for Business and open the Excel file you want to connect to.
3. Once the file is open in the browser, go to File > Info and click the Copy path button.
4. Go back to Power BI and paste this long URL into the URL field.
5. Important Step: Look at the very end of the URL you pasted. Delete the ?web=1 part. This small change tells Power BI to access the file itself, not its web-view rendering.
6. Click OK. You'll be asked to sign in with your organizational account credentials to give Power BI permission.
7. From here, the process is the same as before. The Navigator window will open. Select your data table and click Transform Data or Load.

Connecting to an Excel File in a SharePoint Folder

This is an incredibly flexible method if you store multiple project files or monthly reports in a single SharePoint folder.

1. In Power BI Desktop, go to Get Data, click More..., and then search for and select SharePoint folder. Click Connect.
2. You'll be asked for the Site URL. Paste in the main URL for your SharePoint site, not the full path to the document library or file itself. For example: https://yourcompany.sharepoint.com/sites/YourSiteName.
3. Power BI will show you a list of every single file within that SharePoint site. Click Transform Data to open the Power Query Editor.
4. In Power Query, you will see columns with filename, folder path, date created, etc. Use the filter button on the Folder Path or Name column to find your specific Excel file.
5. Once you have filtered to the single row containing your file, look for the Content column. It will say Binary. Click on the word Binary. This tells Power BI to "open" that file.
6. The Navigator interface will appear again, allowing you to select the specific Table from within that file you want to use.

This method feels a bit more advanced, but its power is that you can adapt the query to, for example, combine all Excel files in a folder into a single master table – great for consolidating monthly sales reports!

Refreshing Your Data: Keeping Your Reports Current

Once your dashboard is built and published, you need the data to stay fresh. How Power BI refreshes depends entirely on the connection method you used.

• Locally Saved Files: If you imported an Excel file from your computer, you need something called a Power BI Gateway to schedule a refresh once it's published online. The Gateway is a piece of software that creates a secure bridge between your local computer and the Power BI cloud service. This can be complex to set up.
• OneDrive or SharePoint Files: This is where the magic happens. Because the file is already in the Microsoft cloud ecosystem, no Gateway is needed. After you publish your report, you can go into the dataset settings in the Power BI Service and set up a daily or even hourly refresh with just a few clicks.

For this reason alone, storing your key Excel report files in OneDrive or SharePoint is highly recommended if you plan on automating any part of your reporting process.

Final Thoughts

Bringing your Excel data into Power BI opens up a new world of analytical possibilities. You can start simply by importing local files to get familiar with the toolkit, then move to cloud-based connections using OneDrive or SharePoint to build truly dynamic, self-updating reports that move beyond the static limitations of a spreadsheet.

While Power BI helps streamline reporting from Excel and other sources, we created Graphed to take automation a step further. We designed it for users who want to connect to marketing and sales platforms like Google Analytics, Facebook Ads, Shopify, or HubSpot and create entire dashboards just by describing what they want to see in plain English. Instead of manually navigating data connectors and the Power Query Editor, you can just ask for the report you need, and we build it for you in real-time.