• Table of ContentsTable of Contents
  • IndexIndex
  • GlossaryGlossary
 
|    XTools Pro Website

Edit in Excel

#updated

Purpose

Missing your usual Excel table editing capabilities working with tables in ArcGIS or looking to bring your Excel habits to the attributes editing in ArcGIS? The Edit in Excel tool in XTools Pro accomplishes the Microsoft Excel interoperability capabilities provided in XTools Pro and allows direct editing attribute tables or standalone tables in Microsoft Excel application.

Description

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

  • Microsoft Office 2003 and newer versions supported

  • .Net Programmability Support for Microsoft Office should be installed

  • Tables should be editable, importing edits requires open edit session. 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 add or remove records but you cannot add new or remove existing columns

  • New columns, sheets, graphics etc. added to Excel book are ignored when importing edits back to ArcMap

  • Geometry, Raster, BLOB, GUID and Global ID fields are not exported and cannot be edited in 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 ArcMap may take some time, especially for larger tables.

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

Editing tables in Excel

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

  • Data validation

  • Formulas

  • Filtering 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 ArcMap Table of contents for editing

Following layer properties are supported:

- visible selected features

- definition queries

- field order

- fields visibility

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

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

- field domains

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

  • Select Edit in Excel from the Table Operations menu or from the layer's context menu

  • Make edits in Excel

Following editing operations supported:

- edit cell values (manually or with formulas)

- add or remove records

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

  • When finished with editing switch back to ArcMap and select Import Edits from Excel from the Table Operations menu or from the layer's context menu to bring edits back to ArcMap

- there is no need to save edits in Excel, these will be saved with the edit session in ArcMap

- if successfully imported the table will close in Excel

- only calculation results not formulas will be imported

Notes:

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

  • If all attributes (including OID) for a record are cleared (or whole record is removed) in Excel, this feature\record will be deleted when importing edits.

  • If not all attributes but OID cell are cleared for a record in Excel, this feature\record will be deleted and a new feature\record with a new OID and empty geometry keeping remained attributes will be added in the attribute table when importing edits.

  • If all attributes (except OID) for a record are cleared in Excel, this feature\record with its geometry without any attributes will be kept in attribute table when importing edits.

  • 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 of editing tables in Excel

Example 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.

Example 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. All new values are saved in their cells.

 
 
 
 
 

Table of Contents

Index

Glossary

Search

Back