Create an Excel app from scratch

  • 6 minutes to read
  • Contributors
    • Anne Frances Owen
    • Michael Blythe

Create your own app from scratch based on Excel data, formatted as a table, and then add data from other sources if you want. By following this tutorial, you'll create an app that contains two screens. On one screen, users can browse through a set of records. On the other screen, users can create a record, update one or more fields in a record, or delete an entire record. This approach takes more time than generating an app automatically does, but experienced app makers can use it to build the best app for their needs.

Prerequisites

To follow the steps in this tutorial exactly, first create an Excel file using this sample data.

  1. Copy this data, and then paste it into an Excel file.

    StartDay StartTime Volunteer Backup
    Saturday 10am-noon Vasquez Kumashiro
    Saturday noon-2pm Ice Singhal
    Saturday 2pm-4-pm Myk Mueller
    Sunday 10am-noon Li Adams
    Sunday 10am-noon Singh Morgan
    Sunday 10am-noon Batye Nguyen

  2. Format that data as a table, named Schedule, so that PowerApps can parse the information.

    For more information, see Format a table in Excel.

  3. Save the file under the name eventsignup.xls, close it, and then upload it to a cloud-storage account, such as OneDrive.

Important

You can use your own Excel file and review this tutorial for general concepts only. However, the data in the Excel file must be formatted as a table. For more information, see Format a table in Excel.

Open a blank app

  1. Sign in to PowerApps.

    PowerApps home page

    You can design an app from scratch for phones or for other devices (such as tablets). This topic focuses on designing an app for phones.

  2. Under Make apps like these, hover over the Start from blank tile, select the phone icon, and then select Make this app.

    Blank-app tile

    PowerApps Studio creates a blank app for phones.

  3. If the Welcome to PowerApps Studio dialog box opens, select Skip.

Connect to data

  1. In the middle of the screen, select connect to data.

  2. In the Data pane, select the connection for your cloud-storage account if it appears. Otherwise, follow these steps to add a connection:

    1. Select New connection, select the tile for your cloud-storage account, and then select Create.
    2. If prompted, provide your credentials for that account.

  3. Under Choose an Excel file, type or paste the first letters of eventsignup to filter the list, and then select the file that you uploaded.

  4. Under Choose a table, select the checkbox for Schedule, and then select Connect.

Create the view screen

  1. On the Home tab, select the down-arrow next to New screen to open a list of screen types, and then select List screen.

    Add a list screen

    A screen is added with several default controls, such as a search box and a Gallery control. The gallery covers the entire screen under the search box.

  2. Select the gallery by clicking or tapping near its center.

    A selection box with handles surrounds the gallery.

    Add a list screen

  3. In the right-hand pane, select CustomGallerySample to open the Data pane.

    Open Data pane

  4. Under Data source, select the down arrow to open the list of data sources for the app, and then select Schedule.

    Select a data source

  5. Under Layout, select the down arrow to open the list of layouts, and then select Title, subtitle, and body.

    Select layout

  6. Under Title2, change the column shown from Backup to Volunteer.

    Change column in label

  7. Close the Data pane by selecting the close icon in the upper-right corner.

    The gallery shows the name of each volunteer and the day and time of that volunteer's shift.

    The Schedule data in the gallery unsorted

  8. Select the gallery, and confirm that the property list shows Items.

    As the formula bar shows, the value of that property is Schedule.

    The Schedule data in the gallery unsorted

  9. Change the value of the Items property by copying this formula and pasting it into the formula bar:

    SortByColumns(Search(Schedule, TextSearchBox1.Text, "Volunteer"), "Volunteer", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

    The gallery shows data in alphabetical order by volunteer name.

    The Schedule data in the gallery sorted

    Users can sort and filter the gallery by volunteer name based on the SortByColumns and Search functions in that formula.

    • If a user types at least one letter in the search box, the gallery shows only those records for which the Volunteer field contains the text that the user typed.

    • If a user selects the sort button, the gallery shows the records in ascending or descending order (depending on how many times the user selects the button) based on the Volunteer field.

      For more information about these and other functions, see the formula reference.

  10. Type an i in the search box, select the sort button by clicking or tapping it, and then select it one more time (or an odd number of additional times).

    The gallery shows these results.

    Sort and filter the gallery

  11. Clear all text from the search box.

  12. At the top of the screen, select the Label control, and then replace [Title] with View records.

    Change title bar

Create the change screen

  1. On the Home tab, select the down arrow next to New screen, and then select Form screen.

    Add form screen

  2. In the screen that you just added, select Connect to data to open the Data pane, and then set the data source to Schedule.

  3. Under Fields, select all the checkboxes to show all the fields in the form.

    Show fields

  4. Drag the Volunteer field up so it appears at the top of the list of fields.

    Reorder fields

  5. Select the form, and set its Item property to this expression by typing or pasting it in the formula bar:
    BrowseGallery1.Selected

  6. At the top of the screen, select the Label control, and then replace [Title] with Change records.

    Change title bar

Delete and rename screens

  1. In the left navigation bar, select the ellipsis (...) for Screen1, and then select Delete.

    Delete screen

  2. Select the ellipsis (...) for Screen2, select Rename, and then type or paste ViewScreen.

  3. Select the ellipsis (...) for Screen3, select Rename, and then type or paste ChangeScreen.

Configure icons on the view screen

  1. Near the top of the ViewScreen, select the circular-arrow icon.

    Add record

  2. Set the OnSelect property for that icon to this formula:
    Refresh(Schedule)

    When the user selects this icon, the data from Schedule is refreshed from the Excel file.

    For more information about this and other functions, see the formula reference.

  3. In the upper-right corner of the ViewScreen, select the plus icon.

    Add record

  4. Set the OnSelect property for that icon to this formula:
    NewForm(EditForm1);Navigate(ChangeScreen,ScreenTransition.None)

    When the user selects this icon, ChangeScreen appears with each field empty, so that the user can create a record more easily.

  5. Select the right-pointing arrow for the first record in the gallery.

    Select arrow

  6. Set the OnSelect property for the arrow to this formula:
    EditForm(EditForm1); Navigate(ChangeScreen, ScreenTransition.None)

    When the user selects this icon, ChangeScreen appears with each field showing the data for the selected record, so that the user can edit or delete the record more easily.

Configure icons on the change screen

  1. On ChangeScreen, select the "x" icon in the upper-left corner.

    Cancel icon

  2. Set the OnSelect property for that icon to this formula:
    ResetForm(EditForm1);Navigate(ViewScreen, ScreenTransition.None)

    When the user selects this icon, any changes that the user made in this screen are discarded, and the view screen opens.

  3. In the upper-right corner, select the checkmark icon.

    Checkmark icon

  4. Set the OnSelect property for the checkmark to this formula:
    SubmitForm(EditForm1); Navigate(ViewScreen, ScreenTransition.None)

    When the user selects this icon, any changes that the user made is this screen are saved, and the view screen opens.

  5. On the Insert tab, select Icons, and then select the Trash icon.

  6. Set the new icon's Color property to White, and move the new icon so it appears next to the checkmark icon.

    Trash icon

  7. Set the OnSelect property for the trash icon to this formula:
    Remove(Schedule, BrowseGallery1.Selected); Navigate(ViewScreen, ScreenTransition.None)

    When the user selects this icon, the selected record is deleted from the data source, and the view screen opens.

Test the app

  1. Select the ViewScreen, and then open Preview by pressing F5 (or selecting the Preview icon near the upper-right corner).

    Open Preview mode

  2. Add a record.

  3. Update the record that you added, and then save the changes.

  4. Update the record that you added, and then cancel the changes.

  5. Delete the record that you added.

  6. Close Preview mode by pressing Esc (or by selecting the close icon in the upper-right corner).

Next steps

  • Press Ctrl-S to save your app in the cloud so that you can run it from other devices.
  • Share the app so that other people can run it.
  • Learn more about functions such as Patch, which you can use to manage data without creating a standard form.