Where is Power BI in Excel?

Searching for a big "Power BI" button in your Excel ribbon can feel like a fool's errand. The truth is, Power BI isn't a single feature you turn on in Excel, it's a powerful suite of data tools and integrations deeply embedded within it. This article will show you exactly where to find these capabilities and how to use them to transform your Excel experience from simple spreadsheets into a robust analysis tool.

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

Excel’s "Power" Suite: The Heart of Power BI

Before Power BI became a standalone application, many of its core technologies began life as powerful add-ins for Excel. These are often what people are looking for when they ask about Power BI in Excel. They are collectively known as the "Power" tools, and they give Excel the ability to handle data on a scale that was previously impossible without dedicated databases and business intelligence software.

Understanding these three components is the key to unlocking Excel's full potential:

• Power Query: Your tool for getting and cleaning data. It lets you connect to hundreds of data sources, then transform messy data into clean, usable tables automatically.
• Power Pivot: Your tool for modeling and analyzing data. It allows you to work with millions of rows, create relationships between different tables, and perform complex calculations.
• 3D Map (formerly Power Map): Your tool for geographic data visualization. It plots geographic data on an interactive 3D globe.

Let's explore how to find and use the two most important tools: Power Query and Power Pivot.

Finding and Using Power Query in Excel

Power Query is the single most transformative data tool built into Excel in the last decade. It automates the tedious, time-consuming process of cleaning and preparing data for analysis. Think of every time you've had to manually remove columns, filter rows, fix date formats, or copy-paste data from multiple files. Power Query automates all of that.

Where to find Power Query

Fortunately, Power Query is built directly into modern versions of Excel (Excel 2016 and later, and Microsoft 365) and is very easy to find. You don't need to install anything.

Navigate to the Data tab on the Excel ribbon. The entire group of tools on the far left, labeled "Get & Transform Data," is Power Query.

This is your starting point for connecting to external data sources. Clicking "Get Data" reveals a vast list of connectors, from simple files like text/CSV and other Excel workbooks to databases like SQL Server and cloud services.

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

A practical example: Combining multiple CSV files

Let's walk through a common business scenario: you receive a separate sales report as a CSV file for each month. Your task is to combine all twelve files into one master list for annual reporting. Doing this manually is an agonizing copy-paste session prone to errors.

With Power Query, you can do it in a couple of minutes, and the process will be repeatable for next year.

1. Place all your individual CSV files into a single folder on your computer.
2. In Excel, go to the Data tab.
3. Click Get Data > From File > From Folder.
4. Browse to and select the folder containing your CSV files and click Open.
5. A navigator window will appear showing you the files in the folder. Don't just click "Load"! Instead, click the "Combine & Transform Data" button. This is where the magic happens.
6. Excel will show you a preview based on the first file and then open the Power Query Editor.

The Power Query Editor is a separate window where you shape your data. Here, you can remove unneeded columns, change data types (like ensuring a 'Date' column is actually a date, not text), and filter out junk data. Every change you make is recorded as a step in the "Applied Steps" pane on the right. When you're happy with your data's structure, you click "Close & Load" on the top-left.

Power Query will then load your perfectly combined and cleaned data into a new Excel sheet as a formatted table. The best part? Next month, when you add a new CSV file to the folder, you just need to right-click your table in Excel and hit "Refresh" to have the new data automatically added and cleaned.

Activating and Using Power Pivot for Advanced Analysis

If Power Query is for getting and cleaning data, Power Pivot is for analyzing it. It breaks through Excel’s standard limitation of just over one million rows per sheet and lets you work with datasets containing many millions of rows. Its main purpose is to build a "Data Model," which is essentially a collection of data tables connected by relationships — eliminating rafts of complex VLOOKUP or INDEX/MATCH formulas.

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

How to activate Power Pivot

Unlike Power Query, Power Pivot is an add-in that you may need to enable first. It comes with most versions of Excel but isn't always turned on by default.

1. Go to File > Options.
2. In the Excel Options dialog, select Add-ins from the side menu.
3. At the bottom of the window, in the "Manage" dropdown, select COM Add-ins and click Go...
4. Check the box for "Microsoft Power Pivot for Excel" and click OK.

A new Power Pivot tab will now appear on your ribbon. This is your command center for creating and managing your data model.

A practical example: Replacing VLOOKUPs with a Data Model

Imagine you have three separate tables in Excel:

• A massive Sales table with Transaction ID, Product ID, Customer ID, and Sale Amount.
• A Products table with Product ID, Product Name, and Category.
• A Customers table with Customer ID, Customer Name, and Region.

To analyze sales by Product Category or Customer Region, you'd traditionally write tons of VLOOKUPs to pull the category and region data into your giant sales table. This creates huge, slow, and clunky files.

Power Pivot offers a much more elegant solution.

1. First, load each of your tables into the Data Model. Select a table, go to the Power Pivot tab, and click "Add to Data Model."
2. Do this for all three tables.
3. Now, on the Power Pivot tab, click Manage to open the Power Pivot window.
4. In the top-right corner of the Home tab in this new window, click on Diagram View.
5. You'll see visual representations of your tables. Now, create relationships by simply clicking and dragging the "Product ID" from the Sales table to the "Product ID" on the Products table. Do the same for "Customer ID" between the Sales and Customers tables.

That's it! Your tables are now linked. You can go back to Excel and insert a PivotTable. When creating it, you'll be prompted to use the workbook’s Data Model. Now, in your PivotTable Fields list, you will see all three tables. You can drag and drop fields from any table — for example, putting "Region" from the Customers table into Rows and "Sale Amount" from the Sales table into Values — and it will all work perfectly, without a single VLOOKUP in sight.

You can also create much more powerful calculations using the DAX (Data Analysis Expressions) language in Power Pivot, giving you abilities far beyond standard Excel formulas.

Connecting Excel Directly to Power BI Datasets

Beyond the "Power" add-ins, modern Excel has direct integration with the Power BI Service (the cloud-based Power BI platform). This is incredibly useful when your organization has established official, trusted datasets in Power BI that you want to analyze in Excel's familiar environment.

This allows you to connect a live, authoritative data source that your whole company uses, rather than working from a stale CSV export someone emailed you last Tuesday.

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 connect to a Power BI Dataset

1. On the Data tab, click Get Data.
2. Choose From Power Platform > From Power BI.
3. A pane will open on the right-hand side of your screen, listing all the Power BI datasets you have access to.
4. Select the dataset you need and click Insert PivotTable.

Excel will create a new PivotTable that is directly connected to the Power BI dataset in the cloud. Any time the data is updated in Power BI, the changes are reflected in your Excel file with a simple refresh. This ensures everyone is working from the same source of truth.

Publishing Your Excel Workbook to the Power BI Service

Finally, the integration goes both ways. You can publish your Excel workbook directly to the Power BI Service to share it with your colleagues in a secure, online environment.

When you have a workbook containing data, tables, charts, or even a full Data Model, follow these steps:

1. Go to File > Publish and click the Publish to Power BI button.
2. You'll be asked to sign in to your Power BI account.
3. Choose a Workspace within Power BI to save your file.
4. You have two main options: Upload or Export.

For fully integrating your analysis into the Power BI ecosystem, "Export" is the more powerful choice. It transforms your raw Excel data into a reusable dataset for your entire team.

Final Thoughts

Power BI in Excel isn’t a single button, but a collection of deeply integrated features that bring enterprise-level data capabilities into a familiar spreadsheet environment. By mastering Power Query for data wrangling, Power Pivot for sophisticated modeling, and the direct integrations with the Power BI service, you can dramatically elevate your data analysis skills.

While these tools are a huge step up from traditional spreadsheet workflows, they still come with a learning curve — especially when it comes to data modeling and writing DAX formulas. We built Graphed for those who need valuable insights from their sales and marketing platforms without becoming data engineers. We automate the entire process by connecting directly to sources like Google Analytics, Shopify, and Salesforce, allowing you to build real-time dashboards and reports simply by describing what you want to see in plain English, saving hours of manual data wrangling.