How to Create a Report in Power BI Using Excel

You're likely wrangled with Excel for hours, meticulously crafting charts and pivot tables, only for the report to become obsolete a day later when new data comes in. While Excel is a fantastic tool for data organization, Power BI can turn your static spreadsheets into dynamic, interactive dashboards. This guide will walk you through exactly how to connect your Excel file to Power BI and build a professional report from scratch.

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

Why Bother Using Power BI With Excel?

You might be wondering, "Why add another tool when Excel works just fine?" Moving your reporting from Excel to Power BI unlocks a new level of data analysis and presentation. While Excel relies on manual updates and static charts, Power BI is built for modern reporting.

Here are a few key advantages:

• Interactive Dashboards: Instead of static charts, Power BI creates a connected ecosystem. When you click on a data point in one chart, every other chart on the report instantly filters to reflect that selection. This allows you to explore your data in a way that just isn't possible with a standard Excel file.
• Automatic Data Refresh: If your Excel file is saved in OneDrive or SharePoint, you can schedule Power BI to automatically refresh the data. No more manually downloading a new CSV, copying and pasting data, and remaking your charts every week. Your report stays current without you lifting a finger.
• Better Visualizations: Power BI offers a much wider and more modern range of charts and graphs than Excel. From maps and funnel charts to KPI cards and gauges, you have more creative freedom to tell a compelling story with your data.
• Combining Data Sources: Power BI excels at merging data from multiple sources. You can start with your main sales data from an Excel file, then pull in marketing data from another, and even connect to a live Google Analytics account - all within the same report.

Essentially, you get to keep the simplicity and familiarity of using Excel for data entry while gaining the powerful reporting capabilities of a dedicated business intelligence tool.

First, Prepare Your Excel Data For Success

Before you even open Power BI, the most important step happens inside your spreadsheet. Power BI works best with clean, structured data. A few minutes of prep work here will save you hours of headaches later. Your goal is to make your data look like a simple database table, not a human-readable report with merged cells and fancy formatting.

Follow these simple rules to set a solid foundation:

1. Format Your Data as a Table

This is the single most important step. Select your entire data range (including headers) and press Ctrl + T (or go to Insert > Table). This officially formats your data as a structured table.

Why is this so important?

• It auto-expands: When you add new rows of data to your Excel sheet, the table automatically expands to include them. This means Power BI will pick up the new data on its next refresh without you having to redefine the data source.
• It makes it easy to name: Once it's a table, go to the "Table Design" tab that appears in Excel and give your table a descriptive name (like "SalesData" instead of "Table1"). You'll thank yourself for this during the connection process.

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

2. Keep Your Headers Clean and Clear

Your table should have a single row of headers at the top. Each column header must be unique and descriptive. Avoid:

• Merged cells: Never merge cells in your header row. Each column needs its own individual header.
• Double-decker headers: Don’t stack header information across two rows. Keep it to one simple row.
• Blank headers: Every column needs a name.

3. Remove Extraneous Gunk

Your data table should be just that - a table of data. Delete any of the following that exist above, below, or within your data:

• Logos, report titles, or text descriptions.
• Subtotals, grand totals, or aggregated summary rows. (Power BI will calculate these for you.)
• Completely blank rows or columns.

In short, if it isn't a column header or a row of your core data, it shouldn't be in the table you connect to Power BI.

An Example of Bad vs. Good Data Structure

Bad Data Structure (hard for Power BI to read):

Good Data Structure (perfect for Power BI):

Connecting Excel to Power BI: A Step-by-Step Guide

Once your Excel file is saved and ready, it's time to fire up Power BI Desktop (which is a free download from Microsoft). The process is surprisingly straightforward.

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

From the main home screen, you'll see a prominent button in the Home ribbon labeled "Get Data." This is your gateway to connecting hundreds of different data sources. Click it.

Step 2: Choose "Excel Workbook"

A menu will appear with common data sources. Select Excel Workbook from the list. If you don't see it right away, click "More..." to open the full list of connectors and you'll find it under the "File" category. Then, click "Connect."

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

Step 3: Navigate to and Select Your File

A standard file browser window will pop up. Locate your prepared Excel file on your computer and click "Open."

Pro Tip: To enable automatic refreshing, it's best to save your Excel file in a cloud location like OneDrive or SharePoint before connecting it. For this first tutorial, a local file is perfectly fine.

Step 4: Use the Navigator to Select Your Data Table

After a moment, a "Navigator" window will appear. This window shows you all the digestible elements inside your Excel file, including a sheet for each worksheet and an icon for each named Table.

This is where naming your table earlier pays off! Instead of clicking a generic Sheet name, check the box next to your descriptively named table (e.g., "SalesData"). Power BI will show you a preview on the right so you can confirm it looks correct.

Step 5: Choose "Load" or "Transform Data"

At the bottom of the Navigator window, you'll see two options:

• Load: This option takes the data as-is and loads it directly into your Power BI report model. If you diligently cleaned your Excel file according to the steps above, this is the button for you.
• Transform Data: This option opens the Power Query Editor, a powerful tool for cleaning and reshaping data. If you noticed a column needs to be renamed, a data type is wrong (e.g., dates being read as text), or you need to perform more advanced cleaning, you'd choose this option.

For now, since we already prepped our file, go ahead and click Load.

Building Your First Interactive Report

After you click "Load," Power BI will import your data. You'll shortly see a blank report canvas. On the right-hand side of the screen are three important panes. This is your command center.

• Fields: This pane lists all the columns from your Excel table. This is your raw material.
• Visualizations: This is where you pick the type of chart you want to build (bar chart, line chart, map, etc.).
• Filters: This pane allows you to add report-level, page-level, or visual-level filters to your data.

Creating a "Sales by Product" Bar Chart

Let's start with a classic. We want to see how much revenue each product generated.

1. In the Visualizations pane, click the icon for a stacked column chart. A blank chart placeholder will appear on your canvas.
2. Make sure the blank chart is selected. Now go to the Fields pane.
3. Find your "Product" column and drag and drop it into the "X-axis" well in the Visualizations pane.
4. Next, find your "Revenue" column and drag it into the "Y-axis" well.

Just like that, Power BI generates a bar chart visualizing your sales revenue for each product. No formulas needed!

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

Adding a KPI Card for Total Revenue

Dashboards often need big, obvious numbers. Let's add a Key Performance Indicator (KPI) card to show our total revenue.

1. Click on an empty space on the report canvas to de-select your bar chart.
2. In the Visualizations pane, click on the "Card" icon (it looks like "123").
3. A blank card will appear. With it selected, simply drag the "Revenue" field from the Fields pane and drop it onto the "Fields" well for the card.

Power BI automatically sums up the entire Revenue column and displays the grand total. You can drag and resize this card just like any other object.

The Magic of Interactivity

Here's where Power BI's power really shines. Click on one of the bars in your "Sales by Product" chart - for example, the "Widget A" bar. As soon as you click it, watch the Total Revenue KPI card. It will instantly update to show you the revenue for only Widget A.

This cross-filtering is on by default and it's what transforms a static report into an analytical tool. You can empower your team to ask and answer their own questions by simply clicking around the dashboard. Click the bar again (or click in the chart's white space) to un-select it and see the total again.

Final Thoughts

By following these steps, you've successfully turned a simple Excel spreadsheet into a dynamic and reusable Power BI report. You've cleaned your data for compatibility, connected it as a source, and built interactive visuals that can uncover insights much faster than static charts or pivot tables ever could.

The journey from data to dashboard doesn't have to be complex. While tools like Power BI are incredibly powerful, they still require a manual setup and some familiarity with the interface. We built Graphed to simplify this process even further. By connecting directly to your core business platforms (like Google Analytics, Shopify, HubSpot and more), we allow you to build real-time dashboards and get answers from your mountain of data by simply asking questions in plain English, bypassing the manual build process entirely.