Power Apps with Excel: Create and Manage Records Easily

Power Apps makes it easy to build functional applications with minimal code, and one of the most requested scenarios is connecting to an Excel spreadsheet. In this tutorial, you’ll learn how to quickly create a Power Apps application that can add, update, delete, and search records in an Excel file hosted in OneDrive for Business.

For those managing data-heavy operations in Excel, this no-code solution transforms manual spreadsheets into interactive apps, accessible from mobile or web.

If you’re interested in more data-centric tools, check out Power BI Training by Data Bear.

Why Use Power Apps with Excel?

Many organizations store employee lists, inventory, or project tracking information in Excel. Power Apps gives you a low-code interface to interact with this data dynamically without the need to rebuild your processes from scratch.

Benefits include:

• Live integration with OneDrive-hosted Excel files
• Ability to add, update, or delete records directly
• Auto-increment logic for new entries
• Mobile and web accessibility
• Rapid app development with built-in templates

Step-by-Step Guide: Connecting Excel to Power Apps

1. Prepare Your Excel File

Upload your spreadsheet to OneDrive for Business and ensure it has:

• Clearly labeled columns
• A named table (e.g., Table1)

In this example, the spreadsheet includes:

• Employee ID (auto-incremented)
• Employee (name)
• Start Date

2. Launch Power Apps Studio

Go to make.powerapps.com, then:

• Click Create
• Choose Start from data
• Select OneDrive for Business
• Connect to your spreadsheet and table

Power Apps will automatically generate an app layout with:

• A gallery of records
• Edit and detail forms
• Insert functionality

Core App Functionality

View and Search Records

• A gallery control lists all Excel records.
• You can sort by columns like Employee ID or Employee Name.
• A search box lets you filter records dynamically.

To adjust the search logic:

Search(Table1, TextSearchBox1.Text, "Employee")

Edit Records

Click the pencil icon to enter edit mode:

• Set fields like Employee or Start Date.
• You can lock or hide fields (e.g., Employee ID) to prevent accidental edits.

Make a field view-only:

DisplayMode = DisplayMode.View

Add New Records with Auto-Increment

To auto-increment the Employee ID:

If(
    EditForm1.Mode = FormMode.New,
    Max(Table1, EmployeeID) + 1,
    ThisItem.EmployeeID
)

This ensures each new record receives the next available ID.

Set Default Date

To auto-fill today’s date in the Start Date field:

Default = Now()

Users can still override the value if needed.

Save, Publish, and Share

After building your app:

• Go to File > Save to name and store your app.
• Click Publish to make it live for others.
• Share it with colleagues by entering their emails under Share.

Power Apps also supports version control, letting you continue edits without affecting the live version.

Make It Tablet-Friendly

To change layout from phone to tablet:

• Go to File > Settings > Screen size + orientation
• Choose Landscape and adjust resolution

This step is optional but improves usability for desktop or tablet users.

Final Thoughts

Building a Power Apps application that connects directly to Excel in OneDrive is a fast and effective way to modernize spreadsheet-based workflows. With just a few clicks and some light customization, you can transform static data into an interactive, mobile-friendly app.

For more data transformation and reporting tools, don’t miss Power BI Training by Data Bear.