Excel 2010/2013 inserting rows very slowly

Question

I find inserting rows into table structures or into normal cells - manually or otherwise - is very slow. For example, it takes more than 10 minutes to insert 7 rows in a table (containing only literal rows) or in adjacent cells, on a sheet without conditional formatting.

The book has 45 tables and 20 tables, and large tables have XML files about 10 KB in size. There are XML tables with 33 MB tables, most of which are about 300 KB with 5 more than 1 MB, and one 15 MB. Its quite complicated, but not massive. All calculations are perfectly transferred from left to right, up, down, to the right of the sheet to the left sheet, and I basically managed to avoid array formulas. All tables have regular structures, and computed columns have one formula. Most table columns are computed, and only a few smaller ones contain literal data.

I have a lot of conditional formatting on several sheets, but I was very careful to keep it rational and stop it from fragmentation: I have about 45 rules for the whole sheet, and they are generalized to cover all columns. The main processing of the formation decisions is carried over to tables as auxiliary columns and, as I said, very correct in structure.

It seems that these types of patches are not thread safe, so only one processor is loading and disk activity is very light. I can't figure out what excel has been doing all this time. Of course, I set the calculation in manual mode ...

I have seen comments attributing this type of thing to the increased restrictions of rows and columns, but I don't understand why this should be a factor. If I look at XML spreadsheet files, there is only code for rows and columns that are occupied by values ​​or formulas. So why do unoccupied cells play?

This has a huge impact on my productivity - although I learn a lot by reading on sites like this in my new free time. I really need to find out what the problem is, so that I can avoid or get around this problem, if possible.

Can someone help me with this?

Just in case, people wonder about this, the answer is to use a power request and view power in excel. I find medium (500 thousand lines) data sets and complex structures and transformations, all work without a hitch. I never use formulas in tables again. Another thing is that this naturally leads you to the power of bi, which is great. That is my advice.

Answer 1

A long insertion time may be due to INDEX (or other functions) that refer to the entire column or the whole row.

I had a very similar problem: a not too complicated worksheet (about 2500 rows, with 15 columns of data (query results) and about 10 columns of formulas to extract data from the query results. When I inserted a column, the first columns can be inserted within 4 seconds or so, but the second insert will take a minute .. Yikes! I searched the Internet and found this site http://support.microsoft.com/kb/2755145 .

My experience:

I used a formula like = INDEX (11: 11.1, MATCH (AC $ 5, $ 10: $ 10.0)), about 25,000 times on my sheet. You can see that each formula refers to the whole line twice. Apparently, when I added the column, as each row was affected, and therefore each of my formulas was affected, Excel obediently went to work, trying to figure out what to do about it.

Based on what I learned on the Microsoft website, I changed the formula to = INDEX (QueryResults, ROW () - ROW (QueryHeaders), MATCH (AC $ 5, QueryHeaders, 0)), where QueryResults and QueryHeaders are simple named ranges.

After I made this change across the sheet, inserting the column became almost instantaneous - less than a second.

Answer 2

This sounds like the problem described here http://fastexcel.wordpress.com/2012/01/30/excel-2010-tableslistobject-slow-update-and-how-to-bypass/

If so, you need to break one of the conditions to get around it:
For this deceleration, each of the following conditions must be met:

A cell within the Table must be selected The sheet containing the Table must be the Active Sheet The cell being updated must be on the same sheet as the table, but does not have to be within the table There must be a reasonable number of formulas in the workbook. 

Perhaps you could do the update indirectly through VBA with another active worksheet. Or maybe moving all the formulas into a separate book goes around it.
Or convert your tables to regular ranges (and use dynamic range names if necessary)

Answer 3

Try removing conditional formatting and then reapplying it with vba after the main code has completed. Worked for me.