+ Reply to Thread
Results 1 to 26 of 26

Poor Excel performance

  1. #1
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Poor Excel performance

    Hi All,

    Newcomer here, so apologies if I miss anything out!

    I am working with Excel 2013 64-bit at work.

    I do the vast majority of the businesses forecasting using Excel and at the moment i am really struggling to get along with it.


    Document:

    Binary Worksheet - 25mb
    About 50 sheets
    Linked to 3 documents
    Heavy use of pivots and vlookups
    Main sheet approx 2 million cells (mix of raw data and lookups)
    All lookups refer to data within this document and not another document

    Problem
    Constantly crashing
    Takes 1-2mins to refresh large pivots
    Format painter tool can take 1min to do just 10-20 cells
    Undo takes at least 10seconds to undo just 1 cells raw data, can take 10mins to undo formatting.
    Forever crashing and 'not responding'

    I know its not a hardware problem. I am on a gig network, Xeon processor and SSD, we've even tried running it on out new server before it was implemented.

    The problem is even worse when I have an off-site meeting and have to work with my laptop (nothing special), it's basically impossible!

    How can I improve/fix this?

    Appreciate any help you can give.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Poor Excel performance

    It's really hard to give generic advice - much better to see the specific file, to identify how it may be improved / optimized.

    A couple of observations, though:
    - Try using Index / Match instead of Vlookup. It nearly always reduces memory used.
    - Try to make sure you separate your data / business logic / visualisations.
    - Normalise data wherever possible. Create normalised lookup tables.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Poor Excel performance

    Impossible to say for sure without seeing the workbook itself, but here are a few things that could be causing slowdown:

    1) Lots and lots of conditional formatting.
    2) Formulas that refer to entire columns/rows rather than just a necessary portion of. This can be combatted using dynamic named ranges to cover all the data but negate the need to maintain the formulas when data is added/removed.
    3) Volatile formulas such as INDIRECT / OFFSET / NOW etc.
    4) Clunky and inefficient formulas like SUMPRODUCT.
    5) Spurious rows/columns. These are the ones that go far beyond your data requirements but have been included because someone has put a random character somewhere in an area of the workbook that is never used.

    There are other things that could slow things down, but those are my usual suspects.

    BSB

  4. #4
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Thanks for the quick replies!

    Unfortunately, the document holds a lot of sensitive information, so sharing any of it is out of the question.
    - Not sure memory is an issue, 16gb in pc and doesn't max out.
    - I always try to have normalized lookup tables.
    - Not many volatile formulas.
    - Document only has what is required, nothing more, nothing less. I built if from scratch so there are no odd rows or columns (i've checked).

    I do us a lot of IF with lookups in-bedded or lookups with concatenates. Does this matter?

    Also, even though i have a Xeon powered rig, it will always max out CPU when refreshing.

    My main priority at the minute is to stop the document from crashing.

  5. #5
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    Agree with all the suggestions above. When you say the main sheet has 2 million cells with a mix of VLOOKUPS and raw data, how many rows and columns do you have and how many columns of VLOOKUPS?

  6. #6
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    One other thing to check is the Cell Styles drop down to see if there are corrupt cell styles.

  7. #7
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    I've also attached a file that you can use to time the calculation speed on your sheets to see where the problem lies
    Attached Files Attached Files

  8. #8
    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: Poor Excel performance

    Hi and welcome to the forum,

    Not many volatile formulas
    Sometimes it's not the number of volatiles but also where they are in the dependency tree(s). In other words all dependents > dependents > dependents .... etc also recalculate.

    Also might check for volatiles in any named formulas in Name Manager.
    Dave

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Poor Excel performance

    Without a sample to provide specific recommendations, there is this article from MS: https://msdn.microsoft.com/en-us/lib...ffice.14).aspx which has a lot of suggestions.

    One common one is in working with lookups. If you have a lot of data, and you can sort the lookup values, the binary search (approximate match) options in Excel's lookup functions are a lot faster than the linear (exact match) options. I have also seen a lot of performance gains by using the MATCH() function in a helper cell to perform a lookup once, then using the INDEX() function separately to extract multiple results for that one lookup. The article has an entire section dedicated to these kinds of ideas.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    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: Poor Excel performance

    Of late I have noticed performance hits with formula constructions that include dynamically increasing ranges that are popular.

    For example COUNTIF($A$1:A1,A1) works fine on short runs. That formula works well up to about 40,000 rows single column on my machine, but somewhere between 40K and 50K rows (cells) there is a noticeable slow down.

    Same is true for LOOKUP(criteria,$A$1:A1) .... etc, etc.

    I have found the same true for ROWS($1:1).

    I don't find mention of this elsewhere so maybe it's my machine. I have had to come up with alternative constructions.
    Last edited by FlameRetired; 09-08-2016 at 11:52 PM.

  11. #11
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Rows - 32,185
    Columns - 84

    I was wrong, not mainly Vlookups.
    4 vlookups, one with multiple IF
    6 IF
    3 Concatenate
    40 various /*-+ formulas
    rest raw data

  12. #12
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Ran the Macro timer thing. Overall it took about 3.3 seconds to calculate.

    Not too sure about styles, will look into this when i get a chance (fridays are busy for me!)

  13. #13
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Quote Originally Posted by MrShorty View Post
    Without a sample to provide specific recommendations, there is this article from MS: https://msdn.microsoft.com/en-us/lib...ffice.14).aspx which has a lot of suggestions.

    One common one is in working with lookups. If you have a lot of data, and you can sort the lookup values, the binary search (approximate match) options in Excel's lookup functions are a lot faster than the linear (exact match) options. I have also seen a lot of performance gains by using the MATCH() function in a helper cell to perform a lookup once, then using the INDEX() function separately to extract multiple results for that one lookup. The article has an entire section dedicated to these kinds of ideas.
    I haven't tried approx search, will give it a go.

    To be honest, i tried MATCH and INDEX but couldn't get along with it.... I have to try again

  14. #14
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Quote Originally Posted by wynhopkins View Post
    I've also attached a file that you can use to time the calculation speed on your sheets to see where the problem lies
    I have ran all the different tests now and can't see any clear obstructions.

    Although, I am finding it take longer and longer to format or insert rows. Even to insert a row on a blank sheet in the document takes a good 10 seconds at the minute

  15. #15
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    I think it may just be a case of you've pushed Excel to its limit.


    32,000 rows with 4 columns vlookups containing multiple ifs and 50 other formula columns on just one sheet with 50 other sheets including large pivot is fairly extreme.

    Are you using multiple slicers on your pivot tables.
    Are you using SUMIFS to reference your big formula sheet
    What size range are your VLOOKUPS refrerencing - are you referencing entire columns?

  16. #16
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Quote Originally Posted by wynhopkins View Post
    I think it may just be a case of you've pushed Excel to its limit.


    32,000 rows with 4 columns vlookups containing multiple ifs and 50 other formula columns on just one sheet with 50 other sheets including large pivot is fairly extreme.

    Are you using multiple slicers on your pivot tables.
    Are you using SUMIFS to reference your big formula sheet
    What size range are your VLOOKUPS refrerencing - are you referencing entire columns?
    Yes, for some of the pivots I am using slicers. Most of the pivots are smaller in size (displayed data) but reference the whole master sheet. I have also deleted some pivots that had a lot of conditional formatting but hasn't seemed to make a difference.

    No, don't use many SUMIFS at all.

    VLOOKUPS on the master sheet are only referencing about 70 rows, which I now have as named tables.

    I've included some of the formulas I have below, maybe the are built inefficiently?

    =IF(AND(BH32168="No",AR32168="None"),"No",IF(BH32168="Yes",BI32168,AR32168))

    =IF(OR(AR32169="Activity 1",AR32169="Activity 2",AR32169="Activity 3",AR32169="Activity 4",AR32169="Activity 5",AR32169="Activity 6",AR32169="Activity 7",AR32169="Activity 8",AR32169="Activity 9"),VLOOKUP(O32169,NamedTable1,2,0),1)

    =GETPIVOTDATA("GSV Total",$A$3,"Customer","1","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","2","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","3","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","4","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","5","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","6","Quarter","Q4")+GETPIVOTDATA("GSV Total",$A$3,"Customer","7","Quarter","Q4")


    One from an update document i have that gets sent out to colleagues. Basically summaries master sheet for them. Really heavy document, takes for ever to load. Has around 155,00 of these:

    =IF(VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0)="None","",VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0))

    5 types of these, all the same but each pulls from individual pivot that is 2 columns and 32,000 rows

  17. #17
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    Your LOOKUPS referencing the entire columns (Million+ rows) on the Main Document Sheet are probably killing things


    =IF(VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0)="None","",VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0))

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Poor Excel performance

    Quote Originally Posted by DWatson91 View Post
    Although, I am finding it take longer and longer to format or insert rows. Even to insert a row on a blank sheet in the document takes a good 10 seconds at the minute
    Hi,

    To me, the quote above suggests that you have some volatile formulas in place, possibly early in the calculation chain or you have code running- perhaps in an installed add-in if not in the workbook itself.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  19. #19
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Quote Originally Posted by wynhopkins View Post
    Your LOOKUPS referencing the entire columns (Million+ rows) on the Main Document Sheet are probably killing things


    =IF(VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0)="None","",VLOOKUP(CONCATENATE($C29,L$1,L$3),'Main Document'!$I:$J,2,0))
    I use them like this because the document is forever expanding and i don't want to have to keep changing the formulas. Would it help if i made the main sheet and named table itself?

  20. #20
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    Quote Originally Posted by xlnitwit View Post
    Hi,

    To me, the quote above suggests that you have some volatile formulas in place, possibly early in the calculation chain or you have code running- perhaps in an installed add-in if not in the workbook itself.
    Is there and easy way to find them?

    I've gone through my formulas and they look good to me but then what do i know!

  21. #21
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    wynhopkins -

    Forgot to say, the formula that you picked out there. It is in a separate document that references my main one.

    If it is closed, then surely it doesn't effect the performance on the main document?

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Poor Excel performance

    Does your workbook contain formulas using functions such as TODAY(), NOW(), OFFSET(), INDIRECT(), RAND() or CELL()?
    Are the sum ranges in all your SUMIF formulas the same size as the criteria ranges? If not, the formula will be volatile.

    I think some of these have been asked previously, but is there a lot of conditional formatting in your workbook, or a lot of custom styles or number formats? Are there a lot of shapes used on worksheets- form controls for example?

  23. #23
    Registered User
    Join Date
    09-08-2016
    Location
    London
    MS-Off Ver
    2013 64-bit
    Posts
    11

    Re: Poor Excel performance

    No, the workbook does't contain any of those, and I don't use any SUMIF formulas.

    Yes, it has conditional formatting. Not sure what you would consider a lot but i suppose it is. All cells are formatted to either Currency or numbers varying from whole to 2 decimals depending on the application. No form controls used.

    All the document really has besides the raw data and formulas is pivots and pivot charts. The majority of the conditional formatting is on these pivot tables.

  24. #24
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Poor Excel performance

    Conditional formatting is more volatile than volatile functions because it must be recalculated as your screen shows different parts of the worksheet. If I may suggest a simple test
    • Save a copy of your workbook
    • Remove all conditional formatting from all sheets
    • See if the performance improves

  25. #25
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    Ah yes, you're right it won't impact the main document.

    You should turn your source data into a Table where possible and then limit the vlookup to that table.

    2nd suggestion, specific to that formula (but may be relevant to other formula), is that rather than doing 2 vlookups, just do one, and then add another column that says =IF(cell with vlookup result = "None","", cell with vlookup result)

  26. #26
    Registered User
    Join Date
    09-03-2016
    Location
    Perth, Australia
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    22

    Re: Poor Excel performance

    Ah yes, you're right it won't impact the main document.

    You should turn your source data into a Table where possible and then limit the vlookup to that table.

    2nd suggestion, specific to that formula (but may be relevant to other formula), is that rather than doing 2 vlookups, just do one, and then add another column that says =IF(cell with vlookup result = "None","", cell with vlookup result)

+ 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. Poor performance when triggered by event but fast when hitting F5
    By 6StringJazzer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2012, 12:50 PM
  2. Poor Macro Performance
    By ckm08 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-07-2011, 04:36 AM
  3. Poor Array Performance, COUNTIF
    By nine7 in forum Excel - New Users/Basics
    Replies: 14
    Last Post: 03-04-2009, 01:03 PM
  4. poor macro performance
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2006, 09:05 PM
  5. Performance in excel 97 is poor
    By cctd in forum Excel General
    Replies: 1
    Last Post: 01-05-2006, 11:20 AM
  6. C# worksheet function poor performance
    By Simon Murphy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2005, 09:05 PM
  7. Poor Workbook Performance due to Named Ranges
    By jrusso in forum Excel General
    Replies: 2
    Last Post: 01-10-2005, 08: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