How to Import Excel into Power BI

Moving your data from a static Excel spreadsheet into a dynamic Power BI report is the gateway to unlocking deeper insights. It allows you to transform rows and columns into interactive visuals, dashboards, and automated reports that update with fresh data. This tutorial will walk you through the entire process, covering how to prepare your Excel file, the simplest import methods, and a few pro tips to make sure everything runs smoothly.

Graphed

Still Building Reports Manually?

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

Watch the Demo

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

Before you even open Power BI, spending five minutes preparing your Excel workbook can save you hours of headaches later. A clean, well-structured file is the foundation of a great report. Here’s a quick checklist to run through.

1. Format Your Data as a Table

This is the single most important tip for working with Excel data in Power BI. Instead of just having a range of cells, format your data as an official Excel Table.

• Click anywhere inside your data range.
• Go to the Insert tab in Excel and click Table, or just press the shortcut Ctrl + T.
• Make sure the "My table has headers" box is checked.

Why is this so important? When you format your data as a Table, Power BI recognizes it as a structured data object. This means if you add new rows or columns to your table later, Power BI will automatically include them when you refresh your data. If you import a simple worksheet range, you'll have to manually adjust the source range every time it changes.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Clean Up Your Data

Power BI works best with clean, tabular data. Think of a simple, database-style layout:

• One Header Row: Ensure you have a single row at the very top that contains clear, descriptive column headers. Avoid merged cells or multiple header rows.
• No Empty Columns or Rows: Remove any completely blank rows or columns inside your data. Blank cells within a row are fine, but entire empty rows can cause import issues.
• Consistent Data Types: Try to keep one type of data per column. For example, a "Sales" column should only contain numbers, and a "Date" column should only contain dates. Mixing text and numbers in the same column can cause Power BI to misinterpret the data type.
• Unpivot Your Data (If Necessary): If your data is in a "crosstab" or "matrix" format (e.g., months as column headers), it's best to unpivot it into a tabular format before importing. While you can do this in Power BI's Power Query Editor, doing it in Excel first is sometimes easier for beginners.

3. Name Your Worksheets and Tables Logically

Give your worksheets and tables descriptive names (e.g., "Sales_Data_Q3" instead of "Sheet1"). When you connect to the workbook in Power BI, you'll see these names in the Navigator, and clear labels make it much easier to select the correct data.

Once your file is prepped and saved, you're ready to make the jump to Power BI.

How to Import Your Local Excel File into Power BI Desktop

This is the most common method for getting started. You have a saved Excel file on your computer, and you want to use it to build a report in Power BI Desktop.

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

Launch Power BI Desktop. In the main window or on the Home tab of the ribbon, you'll see a prominent Get Data button. Click on it. This is your starting point for connecting to almost any data source.

Step 2: Choose "Excel Workbook"

A panel will appear with a list of common data sources. "Excel workbook" will be at the very top. Select it and click Connect.

Step 3: Find and Open Your Excel File

A standard file browser window will pop up. Navigate to the location where you saved your prepared Excel file, select it, and click Open.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Use the Navigator to Select Your Data

After a moment, the Navigator window will appear. This window shows you all the importable items from your Excel file. In the left pane, you'll see a list of any worksheets and any formatted Tables within the workbook.

This is where your preparation pays off! If you formatted your data as a Table, you'll see it listed with a small blue header icon. You will also see the raw worksheets, represented by a different icon.

You should almost always select the Table, not the worksheet.

Click on the name of your Table. A preview of the data will appear on the right side of the window, so you can confirm it looks correct. Check the box next to your Table name.

Step 5: Choose Between "Load" and "Transform Data"

At the bottom right of the Navigator, you have two options:

• Load: This option loads the data directly into your Power BI data model as-is. It's a good choice if you are completely confident your data is perfectly clean and requires no changes.
• Transform Data: This is the more powerful and recommended option. It opens the Power Query Editor, a tool within Power BI where you can clean, shape, and transform your data before it gets loaded into your model.

Even if you think your data is clean, it's a good habit to click Transform Data. In the Power Query Editor, you can perform tasks like:

• Remove unnecessary columns you don't need for your report.
• Split a "Full Name" column into separate "First Name" and "Last Name" columns.
• Rename columns to be more report-friendly (e.g., changing "sales_amt" to "Sales Amount").
• Change data types (e.g., making sure a zip code column is treated as text, not a number).

Once you've made your transformations in Power Query, click the Close & Apply button in the top-left corner. Your data will now be loaded into Power BI, ready for building visuals!

Graphed

Still Building Reports Manually?

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

Watch the Demo

Connecting to Excel Files in the Cloud (OneDrive or SharePoint)

Keeping your Excel file on your local machine is fine for one-off reports, but what if your data is updated regularly by your team? This is where cloud storage comes in.

By connecting Power BI to an Excel file stored in OneDrive or a SharePoint site, you can set up a scheduled refresh. This means your Power BI report can automatically update itself every day or every week without you needing to do a thing.

The process is slightly different:

1. In Power BI Desktop, select Get Data again.
2. This time, choose Web from the list of data sources.
3. You'll need the URL path to your file. The easiest way to get it is to open the Excel file in its web app (Excel Online).
4. Go to File > Info > Copy Path.
5. Paste this path into the URL box in Power BI. Important: You must remove the ?web=1 string from the end of the URL for the connection to work.
6. Click OK. You'll likely be prompted to sign in with your Microsoft credentials to authorize the connection.
7. From here, the process is the same. The Navigator window will appear, where you can select your table and choose to Load or Transform the data.

Troubleshooting Common Import Problems

Sometimes things don't go as planned. Here are solutions to a few common hurdles.

• Data Type Mismatches: If a column has both numbers and text, Power BI might default the whole column to "Text." In Power Query, you can click the icon in the column header to manually change the data type to Whole Number, Date, etc. Errors will be highlighted so you can find the problem cell.
• "File is Locked" Error: You can't import or refresh an Excel file in Power BI while you have it open in the Excel desktop app. Simply close the file in Excel and try again.
• Column Headers Aren't Recognized: If your first row isn't being used as headers, go to the Transform tab in Power Query and click Use First Row as Headers.
• You Moved the Excel File: If you move a local Excel file, your report will fail to refresh. To fix this, go to Transform Data > Data source settings. Select the broken source, click Change Source..., and browse to the new file location.

Final Thoughts

Getting your data out of Excel and into Power BI is the critical first step in automating your reporting and discovering more meaningful insights. By properly preparing your Excel file and understanding the import process, you set a solid foundation for building accurate and dynamic reports that can evolve with your data.

While Power BI is the perfect tool for doing deep Excel analysis, building reports that require data from multiple platforms - like Google Analytics, Shopify, Facebook Ads, and Salesforce - can still involve a lot of manual exporting and data wrangling in spreadsheets first. We built Graphed to remove that friction. It directly connects to your marketing and sales tools, centralizing your data and allowing you to build real-time dashboards just by describing what you want in plain English, moving you from raw data to insights in seconds instead of hours.