Unexplained performance hit on some worksheets but not others, related to named ranges.

    Disclaimer: For reasons outside the scope of this inquiry, I am trying to learn the reason my workbook is behaving the way it is, NOT looking for suggestions about how I could restructure my workbook to "work around" or "avoid" the issue. Any/all help would be most welcome!


    Details:


    I have a workbook which has been begun performing poorly for me, and which I have stripped down little by little in order to uncover the root cause(s). It is currently saved as .xlsx, with 10 sheets in it. All sheets use defined names, all of whose scope is the entire workbook.


    Four of the sheets take about 20 seconds to activate or to execute a change in a cell. The others behave quickly and normally.

    • When I delete the named ranges of the slow sheets, they behave normally.
    • When I recreate the named ranges from scratch, the sheets become slow again.
    • The named ranges of the sheets that take a long time to activate are not used in formulas elsewhere in the workbook.
    • The slow sheets have 8, 11, 18, and 22 named ranges, respectively.
    • The sheet with the most named ranges by far is not one of the slow sheets. It has 54 named ranges.


    All sheets are laid out as follows:

    • Defined names begin with "[abbrev]_", where [abbrev] is a one- or two-letter shorthand form of the sheet name
    • Defined names encompass entire rows or columns
    • A defined name called "[abbrev]_HEADERS" covers row 2
    • A defined name called "[abbrev]_CATEGORIES" covers row 1
    • Columns containing a header in row 2 have a defined name, "[abbrev]_[headername]"


    Some other steps taken:

    • Ensured the troublesome file is saved on the local HD as opposed to a remote server
    • Updated Office
    • Rebooted
    • Run the document inspector and followed its recommendations
    • Tried two different licenses:
      • MS Office 365
      • MS Office 365 ProPlus


    I have had no luck whatsoever resolving this issue. What could be causing this problem?


    Thanks in advance!


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Additional Info:

    • All data and static formatting below row 2 has been removed
    • All sheets contain conditional formatting.
      • The sheet with the most named ranges also has the most conditional formats: 30
      • The slow sheets contain 7-9 conditions, all of which are present in the sheet with the most named ranges and conditional formats


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    If you remove the conditional formatting, does it make a noticeable difference?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Yes, it does. If I delete the conditional formats OR if I delete the defined names it all works.


    But one of the other sheets is not responding slowly in the first place, even though it has all the same conditional formats plus more, and more than double the number of defined names.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Quote from royUK

    Have you tried saving as xlsb?

    It began as xlsb, but also had code behind it. I saved it as xlsx as a shortcut way to strip out the code.


    It was a good thought. I resaved as xlsb without the code. Unfortunately, there was no noticeable difference in performance.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Same size ranges on sheets that work vs sheets that don't?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Thank you for your continued engagement. Yes - defined names on slow sheets consist of two rows and anywhere from 8 to 22 columns.


    On the most encumbered sheet, which performs just fine, there are two defined name rows and 52 defined name columns.


    More info:

    • Starting from the file I just re-saved as xlsb (see comment #6 above), I just deleted all of the "OK" sheets and cleared out all the defined names that pertained to those sheets.
    • I confirmed that on the "bad" sheets, the used range is the last header cell in row 2 (verified by ctrl-end, as well as by ?Application.ActiveSheet.UsedRange.Address in the VBA console's Immediate window.

    The problem persists.


    I am leaning toward recreating the workbook from scratch. But I sure would like to know what's causing it.


    Before I turned the original xlsb file into an xlsx file, I encountered Excel's Ghost Break problem for the first time. This leads me to suspect that my workbook may just be cluttered with undetected artifacts from previous incarnations.


    That said, with everything I have stripped out of it, it is only 197 KB. So.... ya know ..... ¯\_(ツ)_/¯


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    To be honest, it's pretty much impossible to properly diagnose things like this without the actual workbook, since there are so many things that could be causing the problem. Can you post the work book itself, either with dummy data, or no data at all plus an indication of the used range prior to removing the data?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    OK, here at long last is the stripped-down workbook.


    I've reduced it to only 5 worksheets. The first is the one that's functioning well. The other 4 are very slow. (Sample size: 2 computers. So, um, yeah.) There's no data in the workbook.


    The unaltered workbook is a template, and has never held much data -- only that which was useful to demonstrate features during development.


    The "good" sheet is a core sheet that has been along for the ride through multiple development iterations. The four troublesome sheets are relatively recent additions.


    You may notice that some sheets have border formatting in every cell, while others have none. I was experimenting with getting rid of it but found no advantage to doing so. It is not contributing to the used range.


    Thanks again for having a look.

    All your named ranges are referring to either an entire sheet row or entire sheet column.


    My first thought was to change those to Dynamic Named Ranges, but that still returned an entire row or column not just cells that contained a value.


    The reason for that was your conditional formatting, that, too, is referring to entire sheet rows and columns.


    You are trying to use conditional formatting to replicate an Excel built-in Table. Though you have managed to get it to work, as you know it is an extremely slow way of doing what Excel can do anyway in less than a nanosecond!


    So the answer to your main question is that there is no bug that is slowing things down. It is just that excel has a boat-load of work to do when you enter, or make a change to, a worksheet, formatting has to be set for every single cell on the sheet (17,179,869,184 of them!).


    When I have your workbook open it drastically slows down every other workbook that I also have open!


    Quote from mdenino

    Disclaimer: For reasons outside the scope of this inquiry, I am trying to learn the reason my workbook is behaving the way it is, NOT looking for suggestions about how I could restructure my workbook to "work around" or "avoid" the issue. Any/all help would be most welcome!


    So there you have your reason, and Excel is behaving exactly as you have told it to, it is just that you have told it to so much that it takes time to do it all.


    Even though you do not want to know a solution, the best way would be to use Excel built-in Tables (you can create custom tables if the available ones are not suitable) then change all named ranges and and conditional formats (delete the conditional formatting for borders, that will be handled by excel automatically) to refer to just the table body.


    For example, your named range "c_01" would change from "=$A:$A" to "=Table1[c_01]", where Table1 is the name of the table you created, you can change that via "Table Design" on the ribbon, any change of Table Name made there will automatically change the "Table1" in the named range refers to formula.


    A quicker solution, by no means as good as the one above (but would still help enormously), would be to change all the "Refers to" for both all Named Ranges and all conditional formatting to a far more realistic number of rows and columns. For example "-$A:$A" could be "=$A$2:$A$1000" or "$2:$2" could be $A$2:$BA$2", that would be 53 columns and 1,000 rows (53,000 cells to be formatted as opposed to your current 17,179,869,184)


    These are not "workarounds", they are just means of doing what you are already doing, but doing it far more efficiently.


    Hope that helps.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 3 times, last by KjBox ().

    KjBox, I appreciate the time you spent examining the workbook and composing your response, and I understand what you are saying. However, I think you overlooked the core matter I am seeking to understand.


    The question is why the four considerably simpler worksheets perform slowly, while the more complicated one does not.


    The first worksheet, named "c", which contains far more named ranges and conditional formats, performs acceptably. The other four worksheets are considerably simpler, but perform slowly. Even if you delete the first four worksheets I believe you will find, as I have, that the fifth one still performs poorly.


    Moreover, I don't believe your explanation is correct. If it were, I should be able to avoid the slowdown effect by setting the calculation mode to manual insetad of automatic. But that is not what occurs. (Note that I have avoided the use of volatile functions in the conditional formats.)


    Again, I appreicate the interest & input, but I think something else is going on here.


    If your experience of this workbook's performance differs from what I have described, I would be interested in that information.


    Thanks.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    For a start, setting Calculation to Manual, affects only formulas in cells, it has no effect on the formulas used in conditional formatting. Also all sheets have changes made to them in order to speed up each sheet.


    In the attached file I have deleted all sheets except "cs", then, changed your manual table to an Excel built-in table, changed the Named Ranges to refer to the Table data only (except for "cs_HEADERS" which refers to the Table Header Row, and "cs_CATEGORIES", which refers to the Table Header Row offset bu one row up. Conditional formatting was also changed to refer to the named Table ("tblCS" in this case).


    Note that the ranges referred to in both Named Ranges and Conditional Formatting are, because they refer to the Table, fully dynamic and will change automatically as data row(s) are added or deleted.


    Do not have your original file open when you test this, as I said earlier your original file slows down every Excel file that is open.


    You will see that adding data or changing existing data in virtually instantaneous. Make these changes to each of your original sheets and your workbook will work perfectly normally.

    Again, I appreciate your engagement, but you are seeking to solve a problem I am not asking about. It's right in the title of my post:

    Unexplained performance hit on some worksheets but not others


    Any response that is not geared toward identifying the underlying reasons why the performance hit affects some worksheets but not others is not pertinent to the question I asked. I am seeking to identify a root cause, not employ a corrective action. I don't know how I can be any clearer about that.



    I feel I should also point out that this statement is not accurate:

    Quote

    For a start, setting Calculation to Manual, affects only formulas in cells, it has no effect on the formulas used in conditional formatting.

    To prove this to yourself:

    1. Conditionally format cell A1 of a blank sheet to fill with a color when the equation, =B1 > 0 is true.
    2. Change calculation to Manual
    3. Enter a value in cell B1 (and note that cell A1 does not change color)
    4. Hit F9 (and note that A1 changes color)

    See?


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    I do not have time to go into the details of your Named Ranges and Conditional Formatting formulas and how they interact with each other, but on a quick look at them I assume, that, since you say the named range names are not used in any formulas, they are used only in the conditional formatting formulas.


    If that is the case then you do not need the named ranges at all. The formula for you first condition could be just


    =($A$3:$A$4<>"")*(INDEX($D$3:$D$4,ROW())="PUR")


    There is no need to do the check if a particular cell needs to be coloured or not, because you are referring to the table data only. The $A$3:$A$4 and $D$3:$D$4 refer to the 1st and 4th columns columns of the Table and are fully dynamic (change automatically for changes in number of data rows in the Table), so there is no need to incorporate a Named Range in the formula.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    I may well be wrong about Manual Calculation not affecting, conditional formatting formulas. I avoid manual calculation as much as possible. It is second only to "Merged Cells" in the list of all things that must be avoided at all costs. The only place I ever use it is in VBA where it is set to manual at the start of the code, in order to speed up the code run time, then turned back to automatic at the end of the code.


    I have given you an explanation of the underlying reasons. You are causing a conflict between conditional formatting and excel's functionality, and you are conditionally formatting 17.5 billion cells instead of just those that matter.,


    Ignore the explanation if you wish, and ignore a perfectly working solution. Good luck to you with your workbook as it is.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    May I suggest that if you don't have time to attempt to help me answer my question, then you could've already saved yourself a lot of time spent communicating about other things?


    I do not see where you have identified a "conflict between conditional formatting and excel's functionality" that exists on the slow sheets, but not on the faster one. It's as though you're trying to say the problem is the cause of the problem. Except, of course, that conditional formatting has not been fingered as a root cause.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    You have already stated that if you delete all conditional formatting then the worksheet works as normal. Just what is it in that statement that makes you think that your conditional formatting is not the cause of the problem?


    I have given you an explanation of why the conditional formatting is causing the slowness

    1. You are conditionally formatting 17.5 billion cells
    2. Deleting the data in each sheet has no effect on speed because your conditional formatting formulas are still going to check every cell on each sheet to see if it is empty, rather than just checking those that could contain data. So the amount of data is irrelevant.
    3. The "conflict" is because you have preset all the cells to have a border then you are using conditional formatting to override the preset borders.

    I can help you no further than that.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited once, last by KjBox ().

    KjBox, nowhere have you addressed, or sought to address, why the behavior does not significantly affect the sheet with the most named ranges and conditional formats, which is what my question is about.


    Moreover, if I leave all conditional formats in place and instead delete the named ranges, then the problem is alleviated as well. That demonstrates a correlation, not a cause.


    Finally, I previously stated that some sheets contain borders throughout while others do not, and explained that the ones that do not contain them perform the same as those that do. That demonstrates the lack of a correlation.


    I truly do appreciate your interest in helping. But unfortunately it has not been successful.


    I don't want to argue. I appreciate your efforts. It just hasn't worked out.


    Thanks, anyway, for your efforts. I know you took some time with it.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    I must admit that I am totally mystified as to why you are so keen to know the exact "root cause" of the problem if you have no interest in fixing the problem.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login