Edit in Excel

#new

Purpose

The tool is provided for editing attribute or standalone tables open in ArcGI 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.

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 and XML fields aren’t 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 ribbon or from the layer's context menu.

The tool runs Microsoft Excel application with the selected table open in it. You can edit attribute tables of feature classes or standalone tables in including ones with domains or joins. Tables without OIDs (e.g. txt, csv, xls or xlsx files) are not supported.

All read-only items like serviced fields (OID, Shape_Length, Shape_Area), table and field names, joined fields are marked grey and protected from editing. You can add and delete rows in Excel, but not new columns or sheets. Entered values are validated.

Only values, i.e. calculated results, can be imported back to ArcGIS Pro, not formulas.

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
Select Edit in Excel from the XTools Pro ribbon 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 ribbon or from the layer's context menu to bring edits back to ArcGIS Pro

- there is no need to save edits in Excel, these will be saved in ArcGIS Pro
- if successfully imported the table will close in Excel

Notes:

- If editing an attribute table of a feature layer, note that geometry will be empty for new records added in Excel.

- If you remove a layer or a table being edited in Excel from ArcMap 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.