How to Create a Canvas App From Excel in Power Apps? [With Data from Scratch]

In this Power Apps tutorial, I will tell you how to create a canvas app in Power Apps from an Excel sheet step by step.

How to Create a Canvas App From Excel in Power Apps

In this section, I will show you how to create a canvas app from Excel in Power Apps.

Example:

I have an Excel sheet as [Employee_Salary] with various columns, as shown below:

1. Job id
2. Company id
3. Job type
4. Degree
5. Major
6. Industry
7. YearsOfExperience
8. mileFromMetropolis

To, create an interactive application using an Excel sheet, there are three different ways in Power Apps.

Let me show you all the three different ways to create an app in simple, easy steps:

1. Create a canvas app with data from an Excel file
2. Create a canvas app from scratch based on Excel data
3. Connect to Excel from Power Apps

Create a canvas app with data from an Excel file

To create a Canvas app in Power Apps from an Excel sheet, follow the steps below:

Step 1: Sign in to your Power Apps. On the Power Apps Home page, click on Start with data.

Step 2: Now, click on Create with Excel or .CSV file -> Then, click on Select from device -> Select an Excel file that you want to create an app from it.

Note,

Once you upload an excel file, it will be converted as a Dataverse table, By utilizing Dataverse’s standard and customizable tables, users can securely store their data in a cloud-based environment.

Step 3: Now, we can see that an Excel table will be added; if your Excel sheet doesn’t have table headers, you can turn on a toggle to “Use first row has column headers.”

Also, If the user needs to change the table name, you can click on the Edit icon [Edit table properties,] as shown below:

Step 4: We can also change the column name or the column’s data type. Near the particular column, Select the Expand icon -> Click on Edit Column

• Display Name = Here, you can change the column name
• Data type = In the dropdown menu, you change the data type of the particular column.
• Format = In the dropdown menu, you can change the format of the column
• Required = If you need to make sure that this column is required, you can turn it on the toggle.

Also, if it is the choice column, we can:

• Selecting Multiple Choice is allowed = Select a Check box if the user needs to select multiple values.
• Choices = This displays all the choice values here. You can delete a particular choice value, or you can sort based on your requirements, and also we can add a new choice value.
• Default Choice = We can also set a default value.

Then click on the Update button.

Step 6: Next, Select Row Ownership; Then expand the dropdown of Ownership type -> Choose how you want to manage row ownership.

Step 7: Then click on the Create app button.

Starting it will upload starting first 20 rows of sample data so you can start reviewing the data in your app. The remaining data will be uploaded in the background.

Step 7: Now, we can see that Excel sheet items will be converted to an interactive application.

This responsive application will now be created in the Power Apps canvas app for the Job information with the Power Apps gallery control, Power Apps forms, Power Apps Controls, and Power Apps icons.

Step 8: Now, let’s Save and Publish this app. From the top-right corner of your Power Apps app, click on a Save icon to save your app, or you can click on a Publish icon to publish your app to the cloud.

Using this Power Apps application, users can add, delete, or edit an item. Any customization made in the app will also be reflected in the Excel sheet.

• Search box = Here, there is a text-input control, the box that the end-users can use to filter the data in the gallery by searching based on the data.
• Add a record = The end-users can click on a +New icon -> The can add a new record -> Once they add it, click on the Check mark to save it. That particular data will be added to both the gallery control and the Excel sheet.
• Edit a record = The end-users can Select a particular record -> Click on an edit icon -> Update the particular record -> click on the Check mark to save it. That particular data will be reflected to both the gallery control and the Excel sheet.
• Delete a record = The end-users can Select a particular record -> Click on the Delete icon -> That particular data will be deleted in both the gallery control and the Excel sheet.

The image below represents the functionality of this Power Apps application.

Now, if you need to share this app with your organization or with external users, share this app with them in Can Edit permission.

They can also add, edit, or remove a particular item that will also be reflected in the Excel sheet.

In this section, I have explained how to create an interactive application using an Excel sheet.

Create a canvas app from scratch based on Excel data

To create a canvas app from Excel in Power Apps, follow the four simple and easy steps below:

1. Format your Excel file as a Table
2. Upload your Excel file to a cloud location like OneDrive for Business or to a SharePoint document library
3. Click on Create and Choose Excel
4. Save and Publish the Power Apps app

Step 1: Format your Excel file as a Table

This is one of the important things when creating a Power Apps canvas app from Excel. You need to format the Excel file as a Table. Otherwise, you will receive an error message: “There are no tables in this file. “

To do this:

• Open the Excel file, Select all the records you want, then click on Format as Table from the Ribbon on the Home tab. Then select a Table option like the screenshot below:

• Then, in the next dialog box, select the “My table has headers” option, as I want the first row to be my header columns.

• Finally, click on the Table Design tab from the ribbon and provide a Table name like the one below. If you do not provide a proper table name, it will come as Table 1 like this while creating the app in Power Apps.

Step 2: Upload your Excel file to a cloud location

Now, upload the Excel file to a cloud location like OneDrive for Business or a SharePoint Online document library. I saved the file to my OneDrive for Business in the My files folder.

Now, it is time to create the canvas app.

Step 3: Create Power Apps Canvas app from Excel file

• On the Power Apps home page, click the + Create button and choose Excel.

• Then, it will show you all the existing connections you have. Here, since I need to connect to my OneDrive for Business, I need to create a connection to OneDrive for Business. For this:
  • Click on + New connections and Choose OneDrive for Business like the below screenshot.

• Then the connection will be available, then click on the OneDrive for Business connection from the list of Connections. Then, it will show you all your Excel files from the My files folder. You can also choose any folder and choose your required file.

• Then, it will show you all the tables in the particular Excel file. In this case, I have just one table; select the table and click on Connect.

• Once you click the Connect button, it will create an app in Power Apps for you. This is a fully working app where you can do all the operations.

Now, this app consists of three screens:

1. Browse Screen = Here, we can see all the items from an Excel sheet, and users can scroll through them.
2. Detail Screen = The detail screen allows us to view information about a particular item from a list.
3. Edit Screen = In the Edit screen, the user can add a new item or they can customize a particular item.

Using this Power Apps application, users can add, delete, or edit an item. Any customization made in the app will also be reflected in the Excel sheet.

The image below represents the functionality of this Power Apps application.

This is how I easily created a Power Apps application using an Excel sheet.

Connect to Excel from Power Apps

In this section, I will show you how to connect the Excel sheet to our Power Apps application.

For example, On the Power Apps screen, I have added an Editform but I need to connect to an Excel sheet.

Let’s connect the Excel sheet to the Power Apps application.

• On the left navigation menu, Click on the Data icon -> Expand the +Add data -> Search for Excel Online (Business).

• The dialogue box will appear as Excel Online (Business), then click on the Connect button.

• Now, Select OneDrive for Business, as shown below:

• Select OneDrive -> Then, select an Excel sheet -> Select a table -> Now, click on the Connect button, as shown below:

• Now, select a Insert auto generated id into Excel table -> Then, click on a Connect button.

• Now, the Excel sheet will be connected to your Power Apps application.

• Then, select your Edit form and set its DataSource to:

Employee_Salary

Where,

1. Employee_Salary = Excel sheet table name

• Now, we can see that the Edit form will be connected to your Excel sheet, as shown below:

This way we can easily connect the Excel from Power Apps.

Additionally, you may also like:

• Power Apps List box Control
• Filter Power Apps Gallery By Dropdown
• How to Use Power Apps Date Picker Control
• How to Remove Items from a Power Apps Collection

Conclusion

I hope this Power Apps tutorial provided you with complete information about creating a canvas app from an Excel sheet.