+ Reply to Thread
Results 1 to 4 of 4

Huge spreadsheet- Slow calculation speed for formulas

  1. #1
    Registered User
    Join Date
    05-04-2018
    Location
    US
    MS-Off Ver
    2010
    Posts
    3

    Question Huge spreadsheet- Slow calculation speed for formulas

    I have an excel spreadsheet with about 300,000 records. I am applying a Vlookup formula on the first row and then trying to copy and paste special as formulas to the remaining rows. But, the calculation tends to consume a lot of time and shows progress in multi-threads which is really slow.

    Could someone suggest a way to do this to save some time.

    Thank you!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Huge spreadsheet- Slow calculation speed for formulas

    Change the calculation method to manual and then manually calculate the file (F9) before saving or printing.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-04-2018
    Location
    US
    MS-Off Ver
    2010
    Posts
    3

    Re: Huge spreadsheet- Slow calculation speed for formulas

    Hello, many thanks for your response.

    Could you please explain exactly how to do it.

    Appreciate it. Thanks!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Huge spreadsheet- Slow calculation speed for formulas

    With any "slow" file, there are a few things to check...
    1. click end/home and see where you end up. If it is nowhere near where you know where your data ends, start CAREFULLY deleting rows and columns till you get to where you now your data ends
    2. Check if you have any Conditional Formatting and see if the rules have doubled up (I have seen them repeated 3 and 4 times just in 1 cell)
    3. With both CF or Data Validation, check to make sure you are not using full rows/columns, try to restrict the ranges to just what you need - or 2-3 times what you think you will need.
    4. Check the same thing for any ARRAY formulas
    5. To use Manual Calcl...
    - click Formula tab
    - Calc Options (far right)
    - select Manual

    Another option to consider is that if formulas for older data will not change their answer, maybe copy/paste values over some/lots of them?
    Last edited by FDibbins; 05-04-2018 at 10:02 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need help to simplify my formulas since the calculation get slow.
    By domz3669 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-07-2018, 05:36 AM
  2. How to fix slow Spreadsheet with 50000 rows but only with one formulas
    By PaulaGon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-17-2018, 01:05 AM
  3. Spreadsheet Really Slow - Can anyone help speed it up?
    By AlexChatz in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-18-2014, 01:12 AM
  4. Replies: 1
    Last Post: 04-09-2012, 11:22 AM
  5. Excel filesize and calculation speed (slow)
    By pixifaery in forum Excel General
    Replies: 2
    Last Post: 08-31-2010, 10:46 AM
  6. Slow Calculation speeds in big spreadsheet
    By MichaelC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-16-2006, 04:50 PM
  7. [SOLVED] application.calculation is slow: mouse clicks speed it up...
    By Noel McWilliam in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1