How to Use Power BI with Excel

If you live and breathe Excel, the idea of learning another data tool can feel like a chore. You’ve perfected your VLOOKUPs, mastered pivot tables, and your spreadsheet is the central source of truth for your team. This post will show you how to connect your existing Excel workbooks to Microsoft Power BI to create interactive, easily shareable dashboards - without abandoning the tool you already know and love.

Wait, Isn't Excel Enough? Why Add Power BI?

For decades, Excel has been the go-to for data analysis, and for good reason. It’s flexible, familiar, and powerful. But as data gets bigger and the need for real-time reporting grows, some of Excel's limitations start to show. This is where using it with Power BI makes a huge difference.

Think of it less as a competition and more as a partnership:

• Excel is your reliable data-entry tool. It's perfect for manual data input, quick calculations, and organizing raw data in a structured way that you control.
• Power BI is your dynamic visualization & reporting engine. It takes your well-organized Excel data and transforms it into interactive dashboards that can be refreshed automatically and shared securely with anyone.

Here’s a quick breakdown of what Power BI adds to your Excel workflow:

• Interactive Visualizations: Power BI dashboards are dynamic. Clicking on one chart instantly filters all other charts on the page, allowing you to slice and dice data on the fly. In Excel, creating this level of interactivity often requires complex macros or slicers that can slow down your workbook.
• Handling Large Datasets: Does your Excel file crawl to a halt once it hits 100,000 rows? Power BI is built to handle millions of rows of data with ease, performing calculations and generating visuals without freezing up.
• Automated Data Refreshes: Stop manually copying and pasting new data every week. You can set Power BI to automatically refresh its data from your Excel file (especially if it’s saved on OneDrive or SharePoint) on a schedule - daily, hourly, whatever you need.
• Secure, Centralized Sharing: Instead of emailing different versions of an Excel file (and wondering who has the latest one), you can publish a single Power BI report and share a secure link. You control who sees what, and everyone is looking at the same up-to-date information.

By connecting them, you get the familiarity of Excel for managing your data and the enterprise-level reporting power of Power BI for visualizing and sharing it.

Step 1: Get Your Excel Data Organized

Before you even open Power BI, the most critical step happens inside your spreadsheet. A clean, organized data source makes the entire process smoother. Power BI works best with structured, tabular data. Follow these simple rules to prepare your file.

Format Your Data as a Table

This is the most important rule. Instead of just having data in a range of cells, you must format it as an official Excel Table.

Here’s how (and why):

1. Click any cell inside your data range.
2. Go to the Insert tab in the ribbon and click Table.
3. Excel will automatically detect your data range. Make sure the "My table has headers" box is checked if your data has column titles (which it should!). Click OK.

Why is this so important? Formatting your data as a Table gives it a defined structure. Power BI recognizes this structure, making the import seamless. It also gives the table a name (like "Table1"), which is how you’ll select it in Power BI. Plus, as you add new rows to your table, Power BI will automatically include them during the next refresh.

Follow Good Data Hygiene

• One Header Row: Ensure you have a single row at the top with clear, unique column names (e.g., "Date," "Region," "Sales," "Units Sold").
• No Merged Cells: Merged cells are great for formatting but a nightmare for data tools. Remove all merged cells from your data table.
• Consistent Data Types: Keep the data within each column consistent. The "Sales" column should only contain numbers, the "Date" column should only contain dates, and so on.
• No Blank Rows or Columns: Delete any completely empty rows or columns within your data table, as they can cause import issues.

Once your Excel file is clean and your data is formatted as a Table, save it. For the easiest refreshing, save the file in a cloud location like OneDrive or a SharePoint folder.

Step 2: Connecting Excel to Power BI Desktop

Now for the fun part. Let's pull that clean Excel data into Power BI. We’ll be using Power BI Desktop, the free-to-download application where you’ll build your reports.

Importing Your Local Excel File

This is the most straightforward method to get started.

1. Open Power BI Desktop.
2. Get Data: On the Home tab of the ribbon, click Get Data. A dropdown will appear. The most common sources are listed, and Excel Workbook should be at the top. Select it.
3. Locate and Select Your File: A file explorer window will open. Navigate to where you saved your Excel file and click Open.
4. Use the Navigator: Power BI will analyze the file and open a "Navigator" window. This is where formatting your data as a table pays off. You'll see a list of available items to import from your workbook. Always look for your named table (e.g., "Table1," or whatever you renamed it to). Click the checkbox next to your table name. A preview of the data will appear on the right.
5. Load in Your Data: At the bottom of the Navigator window, you'll see two options: Load and Transform Data.

• Load: Use this if your data is already perfectly clean and ready to go. Power BI will load the table directly into your report.
• Transform Data: This opens the Power Query Editor, a powerful tool for cleaning and reshaping your data (e.g., splitting columns, changing data types, removing errors). Since we prepped our file, we can just click Load.

That’s it! Your Excel data is now in Power BI, ready to be visualized. You’ll see the table and its columns listed in the Data pane on the right side of the screen.

Step 3: Building Your First Interactive Visuals

With your data loaded, you can now build a simple dashboard. The Power BI interface is designed for drag-and-drop actions.

Let's create a couple of related charts to see how interactivity works.

Anatomy of the Report Builder:

• The Canvas: The large blank area in the middle is where you build your report.
• The Data Pane: On the far right, this lists all your tables and their columns (the fields).
• The Visualizations Pane: Just to the left of the Data pane, this shows all the chart types you can use (bar chart, pie chart, map, etc.).

Creating a "Sales by Region" Bar Chart

1. Select a Chart Type: In the Visualizations pane, click on the icon for a stacked column chart. A blank visual will appear on your canvas.
2. Add Your Data: With the visual selected, go to your Data pane. Drag the "Region" field into the "X-axis" box in the Visualizations pane. Then, drag the "Sales" field into the "Y-axis" box.

Instantly, you have a bar chart showing total sales for each region. No formulas needed.

Creating a "Sales by Product Category" Donut Chart

1. Click on an empty area of the canvas to de-select your first chart.
2. In the Visualizations pane, click the donut chart icon. A new blank visual will appear.
3. From the Data pane, drag "Product Category" into the "Legend" box and "Sales" into the "Values" box.

You now have two charts on your canvas. Click on a specific region in the bar chart - like "West" - and watch the donut chart instantly update to show only the sales by category for that region. This cross-filtering is built-in and is a cornerstone of what makes Power BI so powerful for exploration.

Step 4: Publishing and Sharing Your Report

A report is no good if you can't share it. The final step is to move your creation from your desktop to the cloud-based Power BI Service, where your team can view it.

1. Publish Your Report: On the Home tab in Power BI Desktop, click the Publish button. You will be asked to save your work and then select a "workspace" to publish to (you will at least have "My workspace" by default).
2. View in Power BI Service: Once published, you can open the report in a web browser. From here, you can share it with others in your organization.
3. Set Up Scheduled Refresh: If you saved your file in OneDrive or SharePoint, you can configure Power BI to automatically refresh the data. In the Power BI Service, find your dataset, go to settings, and set up a refresh schedule. Now, whenever you update the Excel file, your Power BI dashboard will automatically reflect the changes without you needing to do a thing.

Final Thoughts

Combining Excel and Power BI transforms your static spreadsheets into dynamic, interactive dashboards. You're simply adding a powerful reporting layer on top of the data management tool you're already an expert at using, unlocking automated updates and easier sharing in the process.

While the Power BI and Excel combo is a massive leap forward from standalone spreadsheets, we know there's still a learning curve and setup process involved. That’s why we created Graphed. We wanted to skip the step-by-step report-building entirely. Instead of configuring data sources and dragging fields onto a canvas, we let you connect all your data (from Google Analytics and Shopify to your HubSpot CRM and go-to spreadsheets) and use plain English to build real-time dashboards and reports in seconds.