Insert row takes much time

[atuljadhavnetafim]

Hi

i have one sheet1 which has 25,000 row data with 38 Column, another sheet2 has 56000 data with 40 column
Sheet1 three column has sumif formula from sheet2,
now the issue is, whenever i insert any row in sheet1 in mid of the data anywhere it takes so much time, many time excel hang up and need to close forcefully.
i have remove entire MS office package and reinstalled but still issue is there,
then i create new excel file with only tow sheet but still problem same. not able to insert row immediate
in sheet2 also has same issue,

the excel don't have any external link, no other formula (Except SUMIF).

can anyone tell me the reason, using office 365.

 

[Anfinsen]

Hi

i have one sheet1 which has 25,000 row data with 38 Column, another sheet2 has 56000 data with 40 column
Sheet1 three column has sumif formula from sheet2,
now the issue is, whenever i insert any row in sheet1 in mid of the data anywhere it takes so much time, many time excel hang up and need to close forcefully.
i have remove entire MS office package and reinstalled but still issue is there,
then i create new excel file with only tow sheet but still problem same. not able to insert row immediate
in sheet2 also has same issue,

the excel don't have any external link, no other formula (Except SUMIF).

can anyone tell me the reason, using office 365.

My best guess is turn off automatic calculations before inserting a row into such a large sheet, then turn it back on when done.

 

[atuljadhavnetafim]

Thanks, i have also tried same way but still takes same time,

 

[Alex Blakenburg]

Turning off calculations worked for me. Mind you it helped when you inserted the row but you still had the delay when you turn the calculation back on.
Each one of your 25k rows has to run through 56k rows for each SUMIF. That is a lot of processing.

Does your spreadsheet have any conditional formatting applied ?
Is your workbook an xlsm or xlsb workbook and if so do you have any event macros in the workbook ?

You might want to consider using Power Query or VBA to replace what you are doing with SUMIF.

 

[kvsrinivasamurthy]

Since there are formulas in many cells it takes time for calculation. Some times You can use helper columns or VBA macro to avoid delay.