Excel Freezing on doing any Action

binny318

New Member
Joined
Aug 29, 2018
Messages
2
Hello All

I have a very large excel file with 7 million records and 45 MB in size and on doing any action like deleting column, my excel is freezing and it takes a long time to complete that action. Is there any way to make these actions on excel quicker.

Thanks in Advance.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,480
Office Version
365
Platform
Windows
Welcome to the Board!

I think you are pushing the limits of Excel with 7 million records (since Excel can only handle a little over 1 million per sheet, that must mean you have at least 7 huge sheets of data!). If you have any formulas, that is going to be even more problematic, as Excel has a limit to how many trace precedents it can track.

What you may try doing, to see if it helps at all, is to set your Calculation mode to Manual, and see if that makes any difference. If you do, you will need to press F9 any time you want to recalculate (and that will be slow), but at least it will not recalculate every time you make any change, and perhaps those actions will be faster.

Note that Excel is really not the best tool to store HUGE amounts of data. A database program might be a better option for storing all that data.
 
Last edited:

binny318

New Member
Joined
Aug 29, 2018
Messages
2
Welcome to the Board!

I think you are pushing the limits of Excel with 7 million records (since Excel can only handle a little over 1 million per sheet, that must mean you have at least 7 huge sheets of data!). If you have any formulas, that is going to be even more problematic, as Excel has a limit to how many trace precedents it can track.

What you may try doing, to see if it helps at all, is to set your Calculation mode to Manual, and see if that makes any difference. If you do, you will need to press F9 any time you want to recalculate (and that will be slow), but at least it will not recalculate every time you make any change, and perhaps those actions will be faster.

Note that Excel is really not the best tool to store HUGE amounts of data. A database program might be a better option for storing all that data.

Hi
Thank you fpr your response. I found another way to handle this through excel vba.
I created an excel instance and without making it visible, i was able to delete a column in that subsheet.

And yes we can store 7 million rows but the excel format should be xlsb, sorry i missed to add this initially. Anyways thanks again.
 

Forum statistics

Threads
1,081,996
Messages
5,362,612
Members
400,684
Latest member
Vie

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top