Skip to main content

Use “Edit in Excel” in Dataverse for Teams

We are excited to announce the general availability of the Edit in Excel feature in Dataverse for Teams.   This Dataverse feature is now available in Dataverse for Teams as an option from the ellipse menu in the Table designer.   This feature enables customers to move data both into Excel and edit it but also to add or change data and publish it back into Dataverse for Teams.  Adding data into Dataverse for Teams is one of the more important and frequent feature requests we’ve gotten.  You can use the Edit in Excel feature to do this.

Download your Excel file for your Dataverse for Teams database.

First you will need to get the Excel spreadsheet that will allow you to add your data.

  • From the Build tab select Tables, and then select your table.
  • Expand the ellipses menu to access the “Edit in Excel” feature.
  • Select “Edit in Excel.”  This will download an Excel file into your Download folder.

 

Importing data into Dataverse with Edit in Excel.

  • Open the downloaded Excel file.
  • Choose to Enable Editing when you open the Excel file.  This will allow the Edit in Excel add-in to retrieve and publish data to and from Dataverse for Teams.

  • View your data.  Opened, you can now see the data in your Dataverse for Teams database.  In this example, I already have 4 records.

  • Add new data.  There are two ways to add new data.
    1. Single records.  Choose the “New” menu in the Edit in Excel pane on the right and a new row will be added to the table in Excel.
    2. Multiple records.  The database table is represented in an Excel Table.  You can add multiple records into Excel the way you normally add new records to an Excel table with a Paste operation.

Paste immediately below the table and it will add new rows to the Excel Table.   In the example below, I have added five new records by pasting names into the Name field.

Choose “Publish” from the Edit in Excel pane on the right.   Once published, notice that fields like “Status” and “Created On” are automatically updated.

Choose “Refresh” from the Edit and Excel pane on the right.  Once refreshed notice that additional fields are updated like “Owner (Looup)”, and “Created By (Lookup)”.    Also note that the data is sorted differently.  The “George” record now sorts to the bottom of the list.

Editing your data.

  • You can edit the existing records as well.  The Edit in Excel add-in pane on the far right will guide you by letting you know which fields are editable and what legal values are possible for a given column.   Select any column and you will get information about that column.  In the image below, the “Favorite City” column is selected and there are four possible lookup values you can choose “London”, “New York”, “Paris”, and “Los Angeles.”

Configuring Edit in Excel.

The Edit in Excel pane can be configured for specific situations

  • Select your Name in the pane and you can sign in or out.
  • Selecting the Gear icon (next to name) will allow you to set various parameters for how the Edit in Excel Add in works including control of which columns you can see in the Excel table.

Limits

  • By default, Edit in Excel can support refresh and edit of up to a maximum of 1, 000, 000 fields (cells).

Documentation

Edit in Excel is an established Dataverse feature.  For more details on see our Edit in Excel documentation.