Inserting Rows in Excel Tables with Structured References May Take Several Seconds

Applies to: Excel 2013Excel 2010Microsoft Office Excel 2007

Symptoms


Consider this scenario: You create a large Excel table, and create calculated columns that utilize Structured References. Then you select a row header and click Insert on the Home Tab. Alternatively, select a row and click Delete. It takes several seconds to insert or delete a row.

Cause


The process of updating the cell dependencies takes longer when Structured References are used in calculated columns.

Resolution


To work around this issue, remove the Structured References, and use cell addresses in your formulas. 

If you already have a large table utilizing Structured References, you can easily convert them to cell addresses as follows:

  1. Select any cell in the table, and click on Table Tools Ribbon, Design Tab
  2. Click Convert to Range, and confirm by clicking Yes on the message box.
  3. On the Home Ribbon, click Format as Table, and choose your preferred formatting.

More Information


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

For a description of using Structured References in Excel Tables, see http://office.microsoft.com/en-us/excel-help/using-formulas-in-excel-tables-HA102749547.aspx?CTT=1