• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste values - SLOW

I have large spreadsheets and will oftentimes add complex formulas (arrays, indexes, vlookups, etc.). So I will ordinarily copy and paste the results as values so that it won't recalculate that formula every time I do anything (create new column, add new formula, insert line, etc.). Sometimes, it pastes very quickly (pretty immediate), but there are instances when it takes a really long time to paste. For testing, I let it just run once when I headed off to an hour long meeting, and it was still trying to paste after the meeting was over. I ended up ending the process/task because it also tends to tank my whole system when it's pasting. Is this normal? And if so, are there any other options to make values paste faster?

YL
 

Chihiro

Excel Ninja
Try setting calculation mode to manual before you copy and paste values.

If there is another process that's taking up your computer's memory, that will impact performance as well. It may be beneficial to terminate any unnecessary process/tasks before you do your operation.
 
Setting it to manual calculation didn't actually help it. In testing just now, I've copied the column and pasted values in a different column altogether (not part of the table) - that paste was very quick. So I am trying to just delete the cells containing the formula, but it is just as slow as trying to paste values. The column contains an array formula and the table has 52k rows. Array formula is {=IF(SUM((O$2:O2=O2)*(G$2:G2=G2)*(M$2:M2=M2))>1,0,1)}. I've used a similar formula (with one less condition) and pasting values takes no time at all. I'm not sure what causes the slowdown on this.
 

Deepak

Excel Ninja
If you will share a sample of data as well as output then we may also suggest you the some other approach like as advance filter, pivot or VBA.. depending on the situation.
 

candybg

Member
Just a comment that ARRAY formulas do slow down spreadsheets. Can you try another approach as suggested by Deepak?
 
Deepak, I think the spreadsheet is too big to sample - if it were smaller, I don't think the problem would exist (i.e., I think it has to do with # of rows). I also think it's maybe just this specific array formula I use. I have another array formula using a combination of MIN and IF, and I use that on a 69k+ row spreadsheet, and copy and pasting values takes no time. I think it might be specific to this array using IF and SUM with 3 criteria. But I will continue to test different arrays to see which ones it's affecting. Thanks all.
 

JAMIR

Member
Check workbook name manager if there is unwanted name manager listed. Delete those name managers.
 

Antl19

New Member
Setting it to manual calculation didn't actually help it. In testing just now, I've copied the column and pasted values in a different column altogether (not part of the table) - that paste was very quick. So I am trying to just delete the cells containing the formula, but it is just as slow as trying to paste values. The column contains an array formula and the table has 52k rows. Array formula is {=IF(SUM((O$2:O2=O2)*(G$2:G2=G2)*(M$2:M2=M2))>1,0,1)}. I've used a similar formula (with one less condition) and pasting values takes no time at all. I'm not sure what causes the slowdown on this.
I am actually experiencing the same issue. Additionally, I have isolated this problem to only formulas who are only locked on one end of an array. (like your ($G2:G2) above.

The weird thing about this issue, is the formulas copy over easily. They also calculate relatively quickly. The slowdown happens when you try to paste value over the existing formulas. It doesn't matter if you have auto calcs turned on or off, its the actual overwritting that hangs up, not the calculations.

If anyone has any additional insight....??? This feels like some sort of bug
 

AVK

Active Member
if you upload sample file with reduce data upto 30 row, i want to see which formula's you are using.
Basically slow runing, cause as per below.
01) Large Data Record
02) Memory
03) Blank unuse row / column
04) Conditional Formatting
05) Number of formula (Depend on formula calculation

You must mark below points to give your slow Excel spreadsheet a little speed boost, and save you some time and frustration.
Avoid Volatile Functions (you must).
Use Helper Columns.
Avoid Array Formulas (if you can).
Use Conditional Formatting with Caution.
Use Excel Tables and Named Ranges.
Convert Unused Formulas to Values.
Keep All Referenced Data in One Sheet.
Avoid Using Entire Row/Column in References.
Use Manual Calculation Mode.
Use Faster Formula Techniques.
 

Antl19

New Member
upload_2017-5-22_21-19-12.png

Here is an example if the issue I have dealt with. This is a small sample, but expand that a few thousand rows even, and then valuing the formula in column a takes an incredible amount of time. Again, copying the formula down to more row takes a reasonable amount of time. Calculating is reasonably fast as well. The ONLY slow part is if I were to copy and paste the values over the existing formula. It is even incredibly slow trying to delete these formula. Only thing I can reason is that this is some sort of bug. This seems small, but this KILLS some financial models I have built. Easy way around this is to use an array formula to make an effective maxif(day <=current row). This options is easier to value, but much longer to calculate.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

Excel provides a lot of ways to do the same thing , and it is up to us to choose the one which is most efficient.

If you see , the formula you have posted basically looks at an array of values , and the dangerous part is that the size of the array steadily increases ; naturally , the time taken will be high.

Now consider the following suggestion :

In A2 , enter the formula =B2

In A3 , enter the formula =IF(B3 > A2, B3, A2)

Copy the formula in A3 downwards , as far as you want.

It is clear that this change can reduce the time consumed in computation , since the formula is never looking at more than 2 values.

Narayan
 

Antl19

New Member
Thank you for the advice Narayan. Though I am not sure this will work in my already complex model. The example I gave is a somewhat oversimplified example, but in my big model is being used nested within a long/complex formula. In order to attempt that sort of change in structure, I would need to add many more rows and/or columns, which make other functions in my model more difficult. I guess, we, as developers(if you can call us that) need to make certain compromises that best fit what we are trying to accomplish. Again, the only part that really bugs me about this issue, is it only occurs during an overwrite/deletion of existing complex formula.
 

Berserk

New Member
I just came across this - I see it is from a couple of years ago. I am having precisely the same problem. Windows 10 64-bit, 32 gig RAM, SSD drive, Excel 2016, 64-bit, , large workbook, trying to paste values (15,000 rows x 26 columns) of a large formula. It's not even an array formula though, it is using countifs but, I must admit, the formula is nesting 26 countifs functions (this is due to the data setup in which target can appear in any one of 26 columns.)

TAKES at least 6-8 hours!!!! I have to leave it overnight.

Now, regardless of whether or not the formula is optimized, why the heck does pasting values over the top of known results take so long?

Has anyone ascertained that this may be a bug in the way Excel handles this type of activity? Just seems silly. So annoying.

Berserk.
 

GraH - Guido

Well-Known Member
Basically, excel is slow for more then 1 reason, as @AVK already pointed out.
When it needs to handle many references (cell addresses) in memory, whether array formulas or not, badly designed formulas, or whatever ; duplicated/multiplicate pivot cache; volatile functions; excessive conditional formatting; and more.
If you are not careful, as in #13 explained by @NARAYANK991, you can meet your worst enemy, yourself.
Berserk, you mention 26 nested functions..., look no further. It could well be that 26 extra columns will increase performance drastically. But I agree, it is annoying that it plays a role in copy/paste. But is Excel at that time just copying or calculating? Huge difference.
As in #12 the formula in A2 refers 1 cell, but in cell A7 there are already 21 cells references active in memory.
A silent killer is the last cell. CTRL+END will reveal it. I've seen spreadsheet with hardly any data and only a few formulas be slow because of cell XFD1048576 being the last cell. Because of that Excel allocates memory to 17.179.869.184 cells (used or not). Delete all columns and rows after the last filled cell and save your workbook. Try performance again...
On the anecdotal level. The last workbook I improved from being dramatic as in +15 minutes of waiting after filtering, adding data or other simple actions by tweaking 2 things. First I used a cell to hold today's day (CTRL + ; ). All formulas using TODAY (), I replaced with this cell reference. Then I sorted all data used in vlookup to enable me using a VLOOKUP TRUE (inside an if to mimic the false type) instead of a VLOOKUP FALSE. Result of tweaks: waiting time was not even 3 seconds.
 
Last edited:

esjohnson14

New Member
This seems so obvious now that I've spotted it, but I believe that it is because the sheet you are copying from is filtered down. If there are hidden row it pastes each of the breaks separately. Trying sorting your source file in a way that groups your data together.

I've spent so much time waiting on excel and I was so glad to finally figure this out!!!
 

Peter Bartholomew

Well-Known Member
@esjohnson14
Since this old discussion has some relevance to you, I wondered whether the macro
Code:
Sub ConvertToValues()
Selection.Value = Selection.Value
End Sub
would have time issues on the problems you encounter?
 
Top