+ Reply to Thread
Results 1 to 4 of 4

Excel sheet is taking a while to calculate, is there a way to find the bottleneck?

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Excel sheet is taking a while to calculate, is there a way to find the bottleneck?

    Hi,

    My excel sheet is quite complex. I'm trying to boost performance so have been removing array formulas etc which has helped. However I'm interested in knowing if there is a way to automatically detect which cells/calculations are causing the bottlenecks?

    Would be incredibly useful

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Excel sheet is taking a while to calculate, is there a way to find the bottleneck?

    I find that in my prior life; I had been asking Excel to examine the entire contents of multiple columns; looking for a condition, or finding a value, etc.. If that's the case, you might limit the number of rows your formulas are evaluating to just the ranges that contain actual data.

    Pete

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Excel sheet is taking a while to calculate, is there a way to find the bottleneck?

    Automatically detecting I wouldn't know. I suspect that would take elaborate case specific VBA.

    The best I can offer is this link. There is a formula timer macro there that I use.

    https://docs.microsoft.com/en-us/pre...1(v=office.12)
    Dave

  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: Excel sheet is taking a while to calculate, is there a way to find the bottleneck?

    I know this doesnt answer your actual question, but 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
    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. Replies: 5
    Last Post: 09-03-2018, 03:20 AM
  2. Workbook performance and bottleneck analysis
    By tatoon in forum Excel General
    Replies: 2
    Last Post: 12-05-2017, 10:51 AM
  3. Taking data from different excel sheet in one Master sheet
    By Dost.Rehman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2017, 02:45 AM
  4. Cleaning up excel sheet taking too long
    By slamdunka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2015, 02:32 AM
  5. Simple Stock Taking Excel sheet (need help on forumlas etc)
    By Mr.West in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-09-2014, 05:14 PM
  6. Taking path name from another excel sheet
    By missf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2011, 04:46 AM
  7. Taking data from excel sheet into userform
    By darklide in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2010, 03:59 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