Forum Discussion
Excel crashing when copying large number of cells to another file
Hi everyone
I have an Excel file that has a sheet with about 43k rows, and each have a VLOOKUP formula in Column B and INDEX formula in column C:
When I try to copy column A, B and C up to last row 43,792 and paste it in a new Excel file as Values, Excel starts crashing and becomes unusable:
I tried a few things to stop all other apps, restarted PC, it's the same.
I am on a Win10 22H2, have a powerful CPU and 64 GB RAM, so I don't think it's the PC requirements.
Any idea what I can do so that Excel can copy large amounts of data (that have formulas in them) and paste them in a New Excel file as Values, and not crash?
PS: my current solution is to run a Python script on the Excel file, but I'd like to make Excel more usable instead of tweaking my Python script if the Excel file has different other columns too :/
Thank you!
5 Replies
I'm not sure why Excel is crashing on that but here are a couple alternative ideas you could try to circumvent that:
a) copy then convert to values. Not sure if this would help at all but what if you copied the data first and then did a copy and paste as values in the same file. To copy you can try a) copy-paste, b) copy sheet to new workbook (right click on tab and choose copy or move sheet), c) Save As the file
b) since you just want the value, do a Save As and save it as a CSV file and then open the CSV file in Excel (don't forget to save as an xlsx if you do any further manipulations)
I was thinking of this for b) i'd have that sheet as the first one to save as CSV, so then the CSV when it's saved it's using just that sheet. Everytime i save as CSV, it always saves the first sheet for me.
Regarding a) i tried but it also crashed, I'll try again.Thank you!
by chance you don't have any volatile functions in those formulas do you? Like RAND() or INDIRECT(). And assuming you have Excel 365 you might consider using some of the newer functions that have been optimized better like XLOOKUP. Maybe you could share a small version of that workbook (with nothing personal/private) and we could see if there might be a better option.
Perhaps there is an issue with the file in question. Can you open a blank workbook and use a formula pointing to the range in question and then copy/paste special values that formula range?
i think u have a point. Both formulas VLOOKUP and INDEX i am using Columns as specified range , i.e. E:E from a different sheet.
I am gonna try to use specific range like $E$1:$E:$10 for instance.thx!
