1

Well I know partly why, my spreadsheet has about 180,000 rows. But that has not presented a problem for sorting or filtering.

But when I entered =B2 in cell C2 and stretched it down to the bottom, it took 10 minutes to calculate it. All the values in column B are simple integers. In fact, there are only about 5 different integers in that column. How could it possibly take that long to execute the simplest formula possible in Excel?

I'm running Excel 2013 on a brand new Alienware X51 computer, Intel i7-4790 @ 3.60GHz with 16 GB RAM, Windows 8.1 Examining my system performance, my CPU was at 20% or below during this "calculation," despite Excel claiming to be using all 8 cores. Why wouldn't it use more like 80% and get the job done 4x as fast? And why does it take so long to calculate the simplest formula anyway? It should take less than a second for ONE core!

Now I've done a vlookup against another table with 57000 rows, and that finished within 15 seconds. How could a simple = take so much longer?

And this ran instantly! =IF(C2>3,E2+25,IF(C2>0,E2+5,E2-5))

Improve this question
7
  • 1
    This is really a rhetorical question. There is no way anybody can answer this for an unknown spreadsheet on an unknown system other than by guessing. You don't have a theoretical spreadsheet, you have a real one.
    – fixer1234
    Commented Oct 13, 2015 at 3:16
  • 1
    FYI Excel 2013 runs calculations a bit differently than 2010. It will be trying to run all of those in parallel. Making changes to a single formula will cause ALL calculations to run. Start by going into advanced options and disabling animations (may need to add a reg key) and disabling hardware graphics acceleration. Actually does make a difference.
    – Rex Sheffield
    Commented Oct 13, 2015 at 4:29
  • Save the versions before and after the command =B2. Chose a human readable format. Do a comparison and stress the differences. Probably eXcel is doing something on all the cells of the B column, maybe it's only a check on formats, maybe it's changing it cell by cell, especially if it is not always the same. BTW, in my experience and IMHO, 57k rows and eXcel should never be together... :)
    – Hastur
    Commented Oct 13, 2015 at 7:49
  • @Hastur the first spreadsheet was 180k rows, the lookup table was 57k. These are MySQL exports but there are some things that are just (theoretically) easier to do in Excel than in MySQL. But if it's going to take 10 minutes just to change a simple value...
    – Buttle Butkus
    Commented Oct 13, 2015 at 20:25
  • @ButtleButkus "...>57k rows and eXcel should never be together." :-) The worst is that it starts to work in an acceptable way and unpredictably (at least for me) it comes out with something like this. There is always a reason that maybe require more time to be solved then to start from scratch. Here I have the strong impression that eXcel starts to do smart operations on all the column B or on all row 2 cells. Now you can try to see if any of the other cells characteristics changes when you write =B2. SaveAs in a human readable format that keeps formatting can be good for a comparison.
    – Hastur
    Commented Oct 14, 2015 at 8:37

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .