Create a Data Table from Excel Data Using Power Apps

Create a Data Table from Excel Data Using Power Apps

Microsoft Power Apps makes it easy to build business applications without extensive coding. One of the most common requirements is displaying Excel data inside a Power Apps application using a Data Table control.

Many organizations maintain data in Excel spreadsheets such as employee records, inventory lists, project details, customer information, and sales reports. By connecting Excel files to Power Apps, users can quickly create interactive applications that display and manage spreadsheet data.

In this article, we will learn how to create a Data Table from Excel Data Using Power Apps.

Why Use Excel as a Data Source?

Excel is one of the most widely used business tools and is often the first choice for storing operational data.

Common scenarios include:

Workflow:

Excel File → Power Apps Connection → Data Table Control → Display Records

Business Scenario

Suppose your organization maintains employee information in Excel.

Excel Columns:

Requirement:

Display Employee Data Inside Power Apps

Expected Output:

Employee List → Search Records → Filter Data → View Details

Prerequisites

Before starting, ensure the following requirements are met.

Excel File

Create an Excel spreadsheet.

Example:

Employees.xlsx

Format as Table

Power Apps requires Excel data to be formatted as a table.

Example:

Table Name: EmployeeData

Store File

Save the Excel file in:

Step 1: Prepare Excel Data

Create an Excel worksheet with employee information.

Example Data:

Employee ID Employee Name Department Email
EMP001 John Smith IT john@company.com
EMP002 Emma Brown HR emma@company.com
EMP003 David Wilson Finance david@company.com

Select all data and navigate to:

Insert → Table

Enable:

My Table Has Headers

Save the file after creating the table.

Step 2: Upload Excel File

Upload:

Employees.xlsx

Location:

This allows Power Apps to access the Excel table as a data source.

Step 3: Create Power Apps Canvas App

Navigate to:

make.powerapps.com → Apps → New App → Canvas App

Choose either:

Based on your business requirements.

Step 4: Connect Excel Data Source

Click:

Data → Add Data

Select:

Excel Online (Business)

Choose:

Employees.xlsx

Select the table:

EmployeeData

Result:

Excel Table Connected Successfully

Step 5: Insert Data Table Control

Navigate to:

Insert → Input → Data Table

Rename the control:

DataTableEmployees

This control will display records from the Excel table.

Step 6: Configure Data Table

Set the Items property:

EmployeeData

Result:

Employee Records Displayed Automatically

Select Columns:

Edit Fields

Add the following columns:

Output:

Employee Table

Step 7: Add Search Functionality

Insert:

Text Input

Control Name:

txtSearch

Set the Data Table Items property:

Filter(
    EmployeeData,
    txtSearch.Text in 'Employee Name'
)

Workflow:

User Types Name → Search Records → Filtered Results

This enables users to quickly locate employee records.

Step 8: Add Department Filter

Insert:

Dropdown

Dropdown Items:

Distinct(
    EmployeeData,
    Department
)

Data Table Formula:

Filter(
    EmployeeData,
    Department = Dropdown1.Selected.Value
)

Result:

Department Based Filtering

Users can now view employees by department.

Step 9: Sort Data

Sort employee names alphabetically.

Formula:

Sort(
    EmployeeData,
    'Employee Name'
)

Descending Order:

Sort(
    EmployeeData,
    'Employee Name',
    Descending
)

Result:

Sorted Employee List

Real-World Example

Employee Directory

Excel:

Power Apps:

Benefits:

Easy Employee Lookup

Inventory Management Example

Excel:

Power Apps:

Inventory Dashboard

Users can:

Customer Management Example

Excel:

Power Apps:

Customer Directory

Features:

Power Fx Formulas

Display Records

EmployeeData

Filter Records

Filter()

Search Records

Search()

Sort Records

Sort()

Unique Values

Distinct()

Best Practices

Format Excel as Table

This is required for Power Apps connectivity.

Use Meaningful Column Names

Example:

Avoid:

Store Files in OneDrive or SharePoint

This improves accessibility and collaboration.

Limit Large Datasets

Excel works best for small to medium-sized datasets.

Consider Dataverse for Enterprise Applications

Dataverse provides better scalability, security, and performance.

Use Search and Filters

Improve usability and help users find information quickly.

Common Challenges

Excel File Not Appearing

Ensure the data is formatted as a table and saved correctly.

Missing Columns

Refresh the data source connection.

Performance Issues

Reduce the number of records loaded into the app.

Sync Delays

Verify file permissions and accessibility.

Benefits

Fast Development

Build applications quickly using existing Excel data.

No Coding Required

Leverage Power Apps' low-code capabilities.

Existing Excel Usage

Reuse spreadsheets already maintained by business users.

Better User Experience

Provide interactive data viewing and filtering.

Cost Effective

No dedicated database is required for smaller solutions.

Workflow Summary

Excel File → Format as Table → Upload to OneDrive → Connect to Power Apps → Insert Data Table → Display Records → Search and Filter Data

Conclusion

Creating a Data Table from Excel Data Using Power Apps is one of the simplest ways to transform spreadsheet information into a functional business application. By connecting Excel files stored in OneDrive for Business or SharePoint, organizations can quickly display, search, sort, and filter records using Power Apps Data Table controls.

Whether you're building employee directories, inventory management systems, customer databases, or project tracking applications, Power Apps and Excel provide a practical low-code solution that helps users access and manage data more efficiently. By following the steps outlined in this guide, you can rapidly create interactive applications that improve productivity and make better use of existing Excel data.