Build a Searchable CRM in Excel with Power Query Custom Data Types

Want to turn your messy customer spreadsheets into a powerful, searchable CRM, right inside Excel? In this step-by-step tutorial, you'll learn how to use Power Query custom data types to combine customer details, interactions, and opportunities into a sleek, self-updating Excel CRM dashboard.

No VBA. No add-ins. Just Excel for Microsoft 365.

Watch the Excel CRM Step-by-step Video

Get the Excel CRM Template and Practice Files

What You'll Learn

• How to import multiple CRM tables into Power Query
• How to create custom data types for customers, interactions, and opportunities
• How to use formulas to extract customer details and filter active leads
• How to build a CRM that updates automatically as your data grows

Why Build a CRM in Excel?

Excel remains one of the most flexible and accessible tools for managing business data and with Power Query custom data types, you can compress entire records into single cells and then pull out just the fields you need, with ease.

That means less clutter, more control, and better performance.

Step 1: Prepare Your CRM Data

My example has three tables (all in Excel format) but you can have more or less:

1. Customers: Name, Industry, Email, Country, Status
2. Interactions: Date, Channel, Topic, Outcome
3. Opportunities: Stage, Value, Expected Close Date

You can store these tables in one file, multiple files, or even connect to a database. Power Query handles them all.

Step 2: Import the CRM Tables via Power Query

1. Go to Data > Get Data > From File > From Workbook (or chose the appropriate source for your data)

This opens Power Query Editor.

Step 3: Create Custom Data Types in Power Query

For each table:

3. Rename the resulting column for each table:

• dtCustomers
• dtInteractions
• dtOpportunities

4. Rename each query for clarity:

• qCustomers
• qInteractions
• qOpportunities

Then go to Home > Close & Load To > Only Create Connection

Right-click each query in the Queries & Connections pane > Load To > Table (on the same worksheet if desired)

Step 4: Build the Customer Details Report

Create a new worksheet with this layout:

Create the Drop-Down List

3. In cell C4, use Data Validation > List
Source: =dtCustomers

Extract Customer Details

Use dot notation to pull fields from the custom data type in cell C4:

Name: =IFERROR(C4.FirstName & " " & C4.LastName, "Select Customer ID")

Industry: =IFERROR(C4.Industry, "Select Customer ID")

Country: =IFERROR(C4.Country, "Select Customer ID")

Status: =IFERROR(C4.Status, "Select Customer ID")

Email: =IFERROR(C4.Email, "Select Customer ID")

Now, changing the customer ID in the dropdown instantly updates the report.

Step 5: Display Customer Interactions

Add this section below the customer details:

Use this formula to filter interactions for the selected customer:

=FILTER(qInteractions[dtInteractions], qInteractions[dtInteractions].CustomerID = C4.CustomerID, "No records")

Extract fields from the spilled array:

Date: =IFERROR(B12#.Date, "")

Channel: =IFERROR(B12#.Channel, "")

Topic: =IFERROR(B12#.Topic, "")

Outcome: =IFERROR(B12#.Outcome, "")

Step 6: Build the Active Leads Report

Create a new sheet with these columns:

• Customer ID
• Status
• Industry
• Opportunity ID
• Opportunity Name
• Stage
• Value

Get Active Customers

=FILTER(qCustomers[dtCustomers], qCustomers[dtCustomers].CustomerStatus="Active", "No active leads")

Extract Details

Status: =IFERROR(B5#.CustomerStatus, "")

Industry: =IFERROR(B5#.Industry, "")

Match Opportunities

Opportunity ID: =XLOOKUP(B5#.CustomerID, qOpportunities[dtOpportunities].CustomerID, qOpportunities[dtOpportunities], "")

Then use dot notation to display opportunity fields:

Opportunity Name: =IFERROR(E5#.OpportunityName, "")

Stage: =IFERROR(E5#.Stage, "")

Value: =IFERROR(E5#.Value, "")

Step 7: Filter and Refresh

Apply Excel filters to slice by:

• Industry (e.g. Technology, Finance)
• Stage (e.g. Qualified, Proposal, Negotiation)

To update everything, just go to Data tab > Refresh All.

Your CRM now grows with your data — no manual editing required.

Real-World Use Cases

Besides a CRM, Power Query custom data types can be used for:

• Product catalogues
• Bills of materials
• Employee databases
• Support ticket dashboards
• Inventory systems and more

Want to Learn More?

If you found this helpful and want to master Power Query and Excel automation, check out my full Power Query course. Thousands of professionals have already used these techniques to build tools they actually use.

See all Excel course options here.