How to Connect Excel to Power BI

Excel is where most data starts, and Power BI is where it should end up once you need something interactive and shareable. Connecting the two is straightforward, but a few habits separate a report that refreshes cleanly for months from one that breaks the first time someone adds a row. This guide covers importing workbooks the right way, handling multiple sheets and named ranges, keeping the connection refreshable, and dodging the classic traps around merged cells and formatting.

Import the file, don't paste the data

The single most important rule: connect to the Excel file, not the contents of your clipboard.

In Power BI Desktop (the free Windows app), go to Home > Get Data > Excel workbook, browse to your .xlsx, and Power BI opens the Navigator. The Navigator lists everything it can find in the workbook — each worksheet, every defined Table, and any named ranges — with a preview pane on the right.

Pasting cells with "Enter Data" feels faster, but it bakes a static snapshot into the model. The day your numbers change, you're re-pasting by hand. A file connection remembers the path and re-reads the source every time you refresh. Always start from Get Data.

When you tick items in the Navigator, you get two buttons:

• Load — pulls the data straight in, as-is.
• Transform Data — opens Power Query first so you can clean before loading.

Use Transform Data almost every time. Even tidy-looking spreadsheets usually need a header promoted or a blank column dropped, and it's far easier to fix that once in Power Query than to patch every visual later.

Sheets, Tables, and named ranges are not the same thing

The Navigator distinguishes three kinds of objects, and picking the right one saves a lot of grief.

Source type	What you'll see	Behavior on import	Recommendation
Worksheet (whole sheet)	Sheet/grid icon	Grabs the entire used range, including blank rows around your data	Avoid unless the sheet is nothing but a clean grid
Excel Table (Ctrl+T)	Table icon	Imports exactly the table's rows and columns, with real headers	Best choice — stable and self-documenting
Named range	Range/tag icon	Imports the cells in the defined range	Fine if the range is well-bounded

The lesson: before you import, go into Excel and turn each data block into a proper Table with Ctrl+T. Give it a clear name (Table Design > Table Name) like Sales, Budget, or Customers. Named Tables auto-expand when you add rows, so new data flows into Power BI on the next refresh with zero extra work. Importing a raw worksheet, by contrast, often drags in Column1, Column2, and a band of empty rows that you then have to scrub out every time.

Bringing in multiple sheets and tables

A workbook with several tabs is no problem — tick every Table you need in the Navigator and each comes in as its own query (and its own table in the model).

A few patterns worth knowing:

• Many tabs, identical layout (e.g., one tab per month). Don't import twelve tables and try to stack them with visuals. Instead, point at the whole folder (Get Data > Folder) if each month is a separate file, or use Power Query's Append Queries to stack same-shaped tables into one tall table. One clean table is far easier to model and chart than twelve near-duplicates.
• Lookup tables (a Products tab, a Regions tab). Import these as their own tables and connect them to your main fact table with relationships rather than merging everything into one giant sheet.
• Don't over-merge. It's tempting to flatten everything into a single wide table, but Power BI is happiest with a star schema — a central fact table (transactions, sales lines) surrounded by dimension tables (dates, products, customers) joined on key columns. Keep your sheets as separate tables and build relationships in the Model view.

If you want a deeper walkthrough of that structure, see our guide on building a star schema in Power BI.

Cleaning the data in Power Query

Once you click Transform Data, you're in the Power Query Editor — a step-by-step recorder of every cleanup action. Each step shows in the Applied Steps list on the right and reruns automatically on every refresh, so you fix things once.

The moves you'll use constantly:

• Use First Row as Headers — promotes your real column names when Power BI guesses wrong.
• Remove Top Rows — strips out a title or logo row sitting above the data.
• Remove Blank Rows and remove empty columns — kills the Column7-type junk that comes from importing a whole sheet.
• Change Type — set each column to Text, Whole Number, Decimal, or Date explicitly. Don't trust the auto-detected types; a date that's secretly text will quietly break your time intelligence.
• Unpivot Columns — if your spreadsheet has a column per month (Jan, Feb, Mar…), select those columns and Unpivot to turn them into two tidy columns (Attribute and Value). This is the most common reshape, and it makes everything downstream easier.

Set the types and shape here, not in DAX. Power Query is for getting clean data in; DAX is for calculating on data that's already clean. Once the data is tidy, a measure can be as simple as:

Total Sales = SUM ( Sales[Amount] )

That clean separation — shape in Power Query, calculate in DAX — keeps both layers easy to maintain.

Keeping it refreshable

A report is only useful if it stays current, and refresh behavior depends entirely on where the file lives.

• Local drive (C:, Desktop, Downloads). You can refresh manually in Power BI Desktop. After you publish to the Power BI Service, the cloud can't reach your local drive directly, so a basic scheduled refresh won't work — you'd need the on-premises data gateway (below) for the Service to read a local or file-server source.
• OneDrive or SharePoint. Store the workbook here and the Service can refresh on a schedule with no gateway, because the source already lives in the cloud. This is the easiest path for most teams.
• On-premises gateway. If the file must stay on a company file server (or a local drive), install the on-premises data gateway on a machine that's always on, and the Service refreshes through it.

Two habits protect refresh reliability:

1. Don't rename or move the file after connecting. The query stores the path; move the file and refresh breaks with a "file not found" error. (If you must, update the path in Transform Data > Data source settings.)
2. Keep the column names and structure stable. If a colleague renames "Revenue" to "Total Revenue" in the spreadsheet, the query that referenced "Revenue" errors out. Lock down the source layout, or add a rename step early in Power Query so downstream steps don't care.

Note that scheduled refresh in the Service generally requires a paid license — Power BI Pro lists at roughly $14 per user per month (check Microsoft's site for current pricing). Power BI Desktop and manual refresh are free.

The pitfalls: merged cells, formatting, and "pretty" spreadsheets

Spreadsheets built for humans to read are often hostile to Power BI. The fixes:

• Merged cells. Power BI keeps the value only in the top-left cell of a merge and leaves the rest blank. Unmerge in Excel and repeat the label down each row. If you can't touch the source, select the column in Power Query and use Transform > Fill > Down to copy the value into the blanks.
• Multi-row headers. A header that spans two stacked rows confuses Power BI. Flatten it to a single header row in Excel, or in Power Query remove the extra row and promote the right one.
• Formatting isn't data. Cell color, bold text, and font size don't import — Power BI reads values, not appearance. If "red means overdue," add an actual Status column with the word "Overdue" so Power BI has something to work with.
• Totals and subtotals inside the data. A "Total" row mixed in with detail rows will double-count in your visuals. Filter these out in Power Query (filter the column to exclude "Total") and let Power BI compute totals itself.
• Numbers stored as text. Values with stray spaces, currency symbols, or leading apostrophes import as text and won't sum. Trim them and set the column type to a number explicitly.
• Blank spacer columns and rows used purely for visual padding — delete them in Power Query.

A good test: if your spreadsheet looks like a clean database table (one header row, one row per record, no merges, no blank spacers, one value per cell), it'll import beautifully. The more it looks like a formatted human report, the more cleanup you'll do.

Once your data is in and modeled, you can polish the look with consistent brand colors — our free Power BI theme generator builds a theme JSON you can drop straight into Desktop.

Send us the spreadsheet, get back a finished report

If you'd rather skip the Power Query cleanup, the relationships, and the merged-cell archaeology, that's exactly what Instant PowerBI is built for. Send us your Excel workbook — messy tabs and all — and we send back a finished, branded, fully modeled Power BI report that opens in the free Power BI Desktop: clean tables, a proper star schema, DAX measures, and a refreshable connection ready to go. Get started here and turn that workbook into something you can actually share.