How to Load Data in Power BI from Excel

Moving your data from an Excel spreadsheet into Power BI is often the first step in creating powerful, interactive dashboards. This guide will walk you through the process, covering the best practices for preparing your Excel file and the different methods for connecting to it, whether it's saved on your computer or in the cloud.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

We'll cover the right way to set up your spreadsheet to avoid common errors, how to import a local file, and how to connect to a file hosted on OneDrive or SharePoint for easier collaboration and automatic refreshing.

First Things First: Prepare Your Excel File for a Flawless Import

Before you even open Power BI, spending a couple of minutes preparing your Excel file will save you from major headaches down the road. Power BI works best with structured, machine-readable data, not nicely formatted reports meant for human eyes. Here’s what to do.

1. Format Your Data as a Table

This is the single most important step. When your data is in a random range of cells, Power BI has a hard time understanding where your data begins and ends. Formatting it as an official Excel Table fixes this instantly. It gives your data a defined structure that Power BI can easily recognize and work with.

• Click anywhere inside your data range in Excel.
• Go to the 'Insert' tab and click 'Table', or just press the shortcut Ctrl + T (or Cmd + T on a Mac).
• Make sure the 'My table has headers' box is checked if your data has column titles (which it should!).
• Give your table a meaningful name. Click the 'Table Design' tab that appears, and type your new name (e.g., "SalesData2024") into the 'Table Name' box on the far left. This makes it much easier to find in Power BI later.

2. Clean Up Your Data Structure

A clean table is a happy table. Take a moment to check for common formatting issues that will cause errors during the import process:

• Remove Blank Rows and Columns: Power BI sees a blank row as the end of a dataset. Make sure your data is in one continuous block with no empty rows or columns in the middle. Extra blank rows at the very top of the sheet should also be removed.
• Get Rid of Merged Cells: Merged cells are great for report titles in Excel but are a showstopper for Power BI. Unmerge all cells within your data table.
• Ensure Consistent Data Types: Make sure each column contains only one type of data. A column for 'Sales' should only have numbers, not text like "N/A" or "Pending." Similarly, a 'Date' column should only contain valid dates. You can fix most of these issues inside Power BI, but it's often faster to clean them up in Excel first.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

How to Load Data from a Local Excel File

If your Excel file is saved on your computer, this is the most straightforward way to get started. Just remember that if you move or rename the file, you'll have to update the path in Power BI to refresh your data.

Step 1: Open Power BI and Select "Get Data"

Launch Power BI Desktop. On the main 'Home' tab, you’ll see a large icon group called 'Data.' Click on the 'Get Data' dropdown menu.

Step 2: Choose 'Excel Workbook'

From the dropdown, you’ll see common data sources listed. Select 'Excel Workbook'. This will open a standard file browser window.

Step 3: Navigate to Your File

Locate the Excel file on your computer, select it, and click 'Open'.

Step 4: The Navigator Window

Once you select your file, the 'Navigator' window will pop up. This window shows you all the sheets and any Tables you’ve formatted inside that Excel workbook. Here’s what that looks like:

• On the left side, you'll see a folder icon for your file. Clicking it reveals the individual components.
• You'll see icons for both worksheets (look like a grid with a blue bar) and tables (grid with a blue bar on top).
• Always choose your named Table, not the worksheet. Remember that table we named "SalesData2024"? Select that. When you do, Power BI shows you a preview of your data on the right.

Step 5: Load vs. Transform Data

At the bottom of the Navigator window, you'll see two crucial buttons: 'Load' and 'Transform Data'.

• Load: This option closes the Navigator and loads your data directly into your Power BI data model as-is. Choose this only if you are 100% certain your data is perfectly clean and requires no changes.
• Transform Data: This is the recommended choice in almost every situation. This option opens the Power Query Editor, a powerful tool within Power BI where you can clean, shape, rename columns, filter rows, change data types, and much more. It's the best practice to always quickly review your data here before loading it.

After you’ve made any necessary adjustments in the Power Query Editor, click the 'Close & Apply' button in the top left corner. Your data is now loaded and ready to be used in visuals!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Connecting to an Excel File in OneDrive or SharePoint

Loading a local file is fine for individual projects, but it falls short for collaborative work or when you need automated refreshes. Connecting to an Excel file stored in a cloud location like OneDrive or SharePoint is far more powerful because you can schedule data refreshes in the Power BI service without needing your computer to be on.

Step 1: Get the Correct File Path (Not the Browser URL!)

This is where most people get tripped up. You cannot just copy the URL from your browser's address bar. You need a specific path that Power BI can understand.

1. Navigate to your Excel file in OneDrive or SharePoint.
2. Important: Open the file in the Desktop App. Click the 'Editing' dropdown and select 'Open in Desktop App'.
3. Once the file is open in your desktop version of Excel, go to 'File' > 'Info'.
4. Click the 'Copy path' button. Paste this path into a text editor (like Notepad), we'll need it in a moment.

Step 2: Connect via the 'Web' Connector in Power BI

Back in Power BI Desktop, the steps are slightly different.

• Go to the 'Home' tab, click the 'Get Data' dropdown, and this time select 'Web'. A small dialog box will appear asking for a URL.

Step 3: Paste and Clean Up Your URL

Paste the path you copied from Excel. Look at the very end of the URL. You may see some characters like <code>?web=1</code>. You must delete everything from the question mark onward. Your URL should end with <code>.xlsx</code> or your file extension.

Click 'OK.' Power BI will then ask you to authenticate. Since you are connecting to an organizational account, choose the 'Organizational account' option and sign in with your work credentials.

Step 4: Select Your Table and Load

After you successfully sign in, you'll be greeted with the same 'Navigator' window from the local file method. The process from here is identical: select your named table, click 'Transform Data' to open Power Query for cleaning, and then click 'Close & Apply'.

Now your Power BI report is connected directly to the cloud version of your Excel file, making updates and refreshes a breeze once you publish your report.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

What Happens After Loading the Data?

Once your data is loaded, you'll be taken to the main Power BI Desktop screen. On the left side, you'll see three icons:

• Report View: The canvas where you build your charts and visuals.
• Data View: A spreadsheet-like view where you can see the actual data that has been imported.
• Model View: Where you can see how different tables connect and create relationships - essential when you start bringing in data from multiple sources.

Your imported Excel data will appear in the 'Data' pane on the right-hand side, ready for you to drag and drop onto the report canvas to start building your analysis.

Final Thoughts

Connecting Excel to Power BI unlocks a new level of data visualization and breathes new life into your spreadsheets. By properly preparing your files and choosing the right connection method - local or cloud - you set yourself up for an efficient and error-free reporting process that can scale with your needs.

As powerful as this is, the process of structuring spreadsheets, manually refreshing reports, and learning a complex BI tool sometimes feels like a job in itself. At Graphed, we decided to automate that entire process. Instead of working through menus, we let you connect data directly from sources like Google Analytics, Shopify, and Salesforce and then ask for the insights in plain English. This lets you get an instant, real-time dashboard without the data prep or steep learning curve - freeing you up to focus on strategy instead of struggling with report building.