Edit in Excel

#updated

Purpose

The tool is provided for editing attribute or standalone tables open in ArcGIS Pro with Microsoft Excel application which allows for applying the habitual and powerful editing capabilities of Excel to your table editing work in ArcGIS Pro.

Description

The Edit in Excel tool allows direct editing tables with Microsoft Excel. At that, following requirements and limitations apply:

.NET Programmability Support for Microsoft Office should be installed.
Microsoft Office 2003 and newer versions supported.
Tables should be editable and the editing is enabled. The tool is disabled for read-only tables or layers.

It is not allowed to change/modify tables and column names in Excel.
You may edit, add or remove records but you cannot add new or remove existing columns. New columns, sheets, graphics and etc. added to Excel book are ignored when edits imported back to ArcGIS Pro.
Geometry, Raster, BLOB, GUID and Global ID fields are not exported and cannot be edited in Excel.
If you remove a layer or a table being edited in Excel from ArcGIS Pro the link will be lost and edits won't be imported from Excel.
A number of read-only restrictions and data validations are applied to the table in Excel. If you change or remove them then backward importing of the edits is not guaranteed.

WARNING: Editing in Excel might be a great and efficient way to work with your table data but please take into account that processing and especially importing edits back to ArcGIS Pro may take some time, especially for larger tables.

Editing in Excel functionality is available either from the XTools Pro Table Tools or from the layer's context menu.

Editing tables in Excel

The tool brings the power of Excel to tables editing capabilities in ArcGIS Pro. Here are some of the advantages that you may benefit from editing tables in Excel:

Data validation
Formulas
Macros
Filtering and sorting data
Data analysis
Spell check
Styles and color schemes
Data formatting

and a lot more...

In order to start editing a table with Microsoft Excel:

Select either a feature layer or a standalone table in ArcGIS Pro for editing

Following layer properties are supported:

- visible selected features

- definition queries

- fields order

- fields visibility

- joined fields (they will be read-only fields in Excel)

- read-only fields (including OID, Shape_Length, Shape_Area)

- field domains

Please note that tables based on .txt, .csv, .xls, .xlsx files are not supported.

Select Edit in Excel from the XTools Pro Table Tools or from the layer's context menu
Make required edits in Excel

Following editing operations supported:

- edit cell values (manually or using formulas)

- add or remove records

- adding OIDs is optional, OIDs will be re-calculated when imported back to ArcGIS Pro

When finished with editing switch back to ArcGIS Pro and select Import Edits from Excel from the XTools Pro Table Tools or from the layer's context menu to bring edits back to ArcGIS Pro

- there is no need to save edits in Excel. But the edits should be saved in ArcGIS Pro after importing.

- if successfully imported the table will close in Excel

- only calculation results not formulas will be imported

- importing new records, their empty cells will be filled with the default values specified for the fields

Selecting fields to be edited

You can select input fields to be edited running the tool with the user interface (see XTools Pro Options). Fields will be added to Microsoft Excel sheet in order they've been selected (OID field will be added by default). But input layer fields won't be re-ordered after importing edits from Excel.

Working with coded domains

By default descriptions will be displayed for fields with assigned coded domains in Excel sheet. You can edit a field value with assigned coded domain selecting a required description from drop-down list in Excel. Codes for the edited field cells will be updated in the attribute table when importing edits from Excel.

But if you have same descriptions for different codes, you can specify to display only codes or codes and descriptions in Excel sheet (see XTools Pro Options). At that, displaying only codes can be used to do calculations with code values if required.

Notes

If editing an attribute table of a feature layer, note that geometry will be empty for new records added in Excel.
If all feature attributes (including OID) are cleared (or the whole record is removed) in Excel, this feature will be deleted when importing edits.
Using the tool interface, please note that the attributes of the deleted feature in the fields unselected to be edited won't be kept in this case.
If feature attributes are partially cleared in Excel but OID cell is cleared, this feature will be deleted and a new feature with a new OID and empty geometry keeping attributes remained in Excel will be added to the attribute table when importing edits.
If all feature attributes (except OID) for a record are cleared in Excel, this feature with its geometry without any attributes will be kept in attribute table when importing edits. Using the tool interface, please note that the attributes of the feature in the fields unselected to be edited will be also kept in this case.
If you remove a layer or a table being edited in Excel from ArcGIS Pro map the link will be lost and edits won't be imported from Excel.

Examples

1. Forecasting of large cities population in 2020 (data taken from Wikipedia):

Table above: Standalone table is edited in Excel. Statistical analysis formula Forecast is used to calculate cities (Moscow, New York, Tokyo) population in 2020. Colors show the city growth direction (increasing – green, decreasing – red).
Table below: Edits are imported back to ArcGIS Pro. All new values are saved in their cells.

2. Solving the cost minimization problem for product delivery (in conventional units) from goods storages (St) to clients (C), considering client needs and goods quantity at storages (in pieces) (synthetic data used):

Table above: Standalone table is edited in Excel. Considering the constraints imposed the simplex method for the optimization problem solution is applied. Values of product quantity (green cells) showing how many product units will be delivered from every storage to every client are calculated. The minimum of delivery cost to all clients is estimated (red cell).
Table below: Changes are imported back to ArcGIS Pro. All new values are saved in their cells.