Why is my sheet running so slow??

Hi, I found membet Jiptastic tread with this same or similar problem.
http://www.excelforum.com/excel-gene...g-so-slow.html
The workbook is a register journal (sheet "Zurn"). First I fill sheet "Priedas1" or "Priedas2" and with macro "Perkelti į zurnalą" data goes to "Zurn". It seams, that problem groving with every new row in sheet "Zurn". Also, if I try, as in Jiptastic problem, a basic action - tu cut ant paste, or insert a row, it takes VERY long. Could anyone help me to solve this problem? I tried to remove conditional formating, but it doesn't get any better.

Priedai Nr1, Nr2 2014,03,26.xlsm

I think your problem is the formulas at the bottom of your Zurn sheet like this:
=SUMIFS(H:H,D:D,E1574,A:A,">"&"2012.12.31",A:A,"<"&"2014.01.01")
Get rid of the whole column references and change to H4:H1565 or something.

Originally Posted by ragulduy

I think your problem is the formulas at the bottom of your Zurn sheet like this:
=SUMIFS(H:H,D:D,E1574,A:A,">"&"2012.12.31",A:A,"<"&"2014.01.01")
Get rid of the whole column references and change to H4:H1565 or something.

But don't you think, what if would solve the problem, it wouldn't depend of amount of filled row number? - when it was few rows - was no any problems!

Ok, I don't know then, maybe somebody else can give you a better solution.

no any more suggestions to solve the problem?

Now I'm changing formulas in all sheets (because all excel is extemely slow) to limited ranges and hopeful it will help. But could You say me, if it really will help, why this problem grows with amount of rows, because formulas all the time was with whole column?

I do not know how the excel functions are coded but I would guess as they have more non-zero terms, the calculations become more complex.

I changed all the formulas in which were all columns counted. In attachment Priedai Nr1, Nr2 2014,03,27.xlsm all with changed formulas, but no any better - inserting, pasting extremely slow. Could anybody now, what else could be?

Originally Posted by newdoverman

Give this a try and see if it works any better: Using formula, worksheet and workbook timers, the indication is that it should be faster. The key word is should.

I'm sorry, I do not understand what you offer? do you offer to use a timers? I never used it and, actually, do not know anything about it i excel, can you explain?

Originally Posted by newdoverman

There were several formulae under row 1566 that had a strange date format (maybe due to Excel versions) that didn't calculate properly for me even though they didn't produce errors. The time of execution was slower than when I changed the date formats to match what you have in your data. I also ran a format cleaner to delete excess formatting and reset the last row of ranges on all worksheets.

I have macros installed on my Excel that measures the time of execution of single formula or range of formulae, measures the time of execution of all formulae in a worksheet (re-calculation time) and one that measures the time to re-calculate the entire workbook.

The excess format cleaner resets the last row of data ranges so that excess empty cells are not calculated. Excess formatting is also removed at the same time.

Can you send me back my file with your done changes? If I understood, you tryed changes just in a few cells? If yes, can you mark these cells(for example, yellow colour), that I could do this same (about time format) for the orhers?

Originally Posted by newdoverman

I have already uploaded the file...message #10

The changes are in the summary area under your data approximately row 1570 to approximately row 1605 in column H and column K where there were dates in the formulae. In addition to the date format used, I changed the ranges in the formulae from whole columns to limited ranges. This was a case of try something, test the time, try something else and time again until the optimal time was achieved.

I tried that file. Maybe your time calculater shows better result after changes, but in practise,when I try insert or paste enything, I really do not feel any faster work. If you made change with the dates in formulae, maybe it is necessary to do tris same in all column A ?

Originally Posted by newdoverman

Column A of your workbook seems to be ok as it caused no problems.

Can you describe your computer? Processor, Ram, Free space on the Hard Drive etc.

Mine Dell Inspiron:
Pentium(R) Dual-Core CPU 2,20 GHz
Ram 4 GB
Ower 160 GB free space (55 in disk C, others- in disk, where file is saved)

But I tried this file on few orher computers - this same slow. Do you want to say, that in your computer this file works perfect???