Too slow formula execution in MS Excel 2010

Asked By 0 points N/A Posted on -
qa-featured

I am working with a large file in MS Excel 2010 and I have been working with this file for days already.

Whenever I work with formulas, my excel worksheet would work very slowly that it would take about half an hour for it to completely execute the compilation.

Then it would just stop responding. It also gave me the error message below.

Is my worksheet too heavy? What should I do?

Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use Copy and Paste commands to insert it into the sheets of another workbook.

SHARE
Best Answer by Thompson Locker
Best Answer
Best Answer
Answered By 0 points N/A #121245

Too slow formula execution in MS Excel 2010

qa-featured

It is pretty clear that you have put a lot of information the current workbook that you are using until it has been overloaded, and therefore it is having problems loading. For the work book to be fast, you need to use a considerable amount of data in it that will not end up overloading it.

I will suggest the following workaround for you:

  • Move some of the data from the workbook to a new workbook so that the current workbook is not overloaded.
  • In case you have all the information placed in only one sheet, then I will suggest that you divide the information between the spreadsheets so that one sheet is not overloaded with data.

Regards
Thompson Locker

 

Answered By 0 points N/A #121246

Too slow formula execution in MS Excel 2010

qa-featured

Before understanding ‘Big Grid’ feature, we take example of Microsoft Excel 2007 in connection with the 65,000 rows expansion limit and similarly over 1 million row limits we have in MS Excel 2010, calculation also improved in MS Excel 2010.

In MS Excel 2010 multithreaded calculation has been added as a new and advance feature in which user defined functions can be embedded in the Excel Sheets and reusable.

Now, I tell you how to use these multithreaded calculation options.

  • Go to File menu and open it
  • Open options
  • Open Excel Options dialogue box window
  • Go into the Advanced
  • And then Formulae
  • Just under the Formulae area you can examine and set the multithreaded calculation options.

One thing another which is much important, setting up ‘WorkbookForceFullCalculation’ when you choose TRUE for this above mentioned option then dependencies doesn’t load at the time of opening or starting. I recommend you to use this option when you are working [using] in a large Excel file. By this way Excel skips to load unnecessary calculations during starting and your file become open quickly.

As you know that Excel is two modes for working

  • Automatic mode of working
  • Manual mode of working

How to Change Mode: Go to Formulae’s Tab and use Calculation Group [note: FileOptionsFormulae] you can change mode. But it is my recommendations that don’t use automatically because this feature converts the file as very heavy. You have an option to recalculate only the selected area by pressing SHIFT+F9.

Note: Some volatile functions [i.e. RAND(), NOW(), TODAY() etc…] always recalculated at every calculation.

Hope you will understand the article.

CHEERS.

Login/Register to Answer

Related Questions