Need to speed up Spreadsheet

Copper Contributor

Hi All,

 

I have a very large dataset in Sheet2. This dataset has a bunch of formula applied based on inputs on Sheet1.

On Sheet1, I have some standalone calcs and also some inputs and graphical outputs from Sheet2.

 

Due to the file size (majority of this is from Sheet2 datasets) the spreadsheet is rather laggy, and takes tens of seconds to calculate when just changing a single input.

 

I was thinking I could speed things up by applying a macro that tells Sheet2 to calculate only when a command button is pressed. I want Sheet1 to remain as auto calculate all the time.

 

I was thinking something like the below would solve this, but this doesn't seem to speed things up. 


Sub Calc()
Sheets("Sheet2").EnableCalculation = True
Sheets("Sheet1").Calculate ' Force calculation on Sheet1

Application.EnableEvents = False
Sheets("Sheet2").EnableCalculation = False
Application.EnableEvents = True
End Sub

 

Any ideas?

 

Many thanks, 

Jacob

6 Replies

@JWorthers 

Your approach is on the right track, but you might want to consider a few additional optimizations. The issue could be complex formulas, large datasets, or dependencies that are still causing delays. Here's an updated version of your code with some additional tweaks:

Vba code (is untested):

Sub CalcSheet2()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Calculate only Sheet2
    Sheets("Sheet2").Calculate
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

In this version, I've included Application.ScreenUpdating to prevent the screen from updating during the calculation, which can improve performance.

You can assign this macro to a command button in your Excel sheet, and then run it whenever you want to manually trigger the calculation for Sheet2. This way, the calculations for Sheet2 won't be happening in the background every time there's any change on Sheet1.

Remember, the effectiveness of this depends on the nature of your calculations and formulas. If your formulas are still complex or dependent on large datasets, the calculation time might still be significant. Consider optimizing your formulas or breaking down your data into more manageable chunks if possible.

Additionally, if you are working with a very large dataset and the performance issue persists, you might want to explore more advanced techniques, such as using PivotTables, summarizing data differently, or using a database solution. The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE 

 

Thanks for the response. 

 

Sheet2 has some complex formula, some rather hefty stuff and there are probably some efficiencies to made just by tidying it up. 

 

I think you've addressed my general concern, which is in fact that I'm trying to shortcut the issue.

In my simple mind I figured that by turning off large computations (until necessary) this would speed up the rest of the workbook, but perhaps I need to go back and find a way to be more efficient with my formulas.

 

Link below to the spreadsheet that I'm using to test the VBA code. This includes some random formulas to intentionally weigh down Sheet2 to represent my issue. Feel free to see if you can come up with a solution using macros. 

https://we.tl/t-Q6ZeliMzyC 

 

 

 

@JWorthers 

I appreciate the additional context. I reviewed the file you provided. It's a good example, and indeed, large datasets and complex formulas can lead to performance issues.

Your instinct about optimizing the formulas is on point. Here are a few suggestions:

  1. Check for Volatile Functions: Certain functions, such as INDIRECT, OFFSET, and CELL, are volatile and can significantly slow down your workbook. If possible, try to minimize the use of these functions.
  2. Use Named Ranges: Instead of referencing entire columns like Sheet2!A:A, try to use named ranges that encompass only the necessary data. This can help Excel perform calculations more efficiently.
  3. Array Formulas: Some formulas in your Sheet2 use entire column references. Consider using array formulas with specific ranges to reduce the number of cells being calculated.
  4. Avoid Full Column References: Refrain from using full column references if you don't need them. For example, instead of =IF(A:A="Yes", B:B, ""), use =IF(A1:A100="Yes", B1:B100, "") if your data only goes up to row 100.

While macros can help control when calculations occur, optimizing the formulas themselves will have a more significant impact on overall performance.

@JWorthers 

Adding a macro to this large workbook is putting a bandage on a big problem and introducing a security element where it's not needed.

 

The way to best optimize this is to reduce the number of inputs.  The workbook posted is slow because it has a ton of scalar-based based formulas (1 cell = 1 formula).  This adds up quickly with 900,000 rows.

 

These are the formulas I've added.

 

The intervals column repeated:

=TOCOL(times*SEQUENCE(,37866,1,0),,1)

The KW column in sheet2.  The '2' in the XLOOKUP is for binary search (ascending).  Since your times are in ascending order, we can speed up the search a bit with a binary search.

=XLOOKUP(B5#,times,Sheet1!F9#,,,2)

The 'Higher/Lower' column:

=LET(
    a, DROP(C5#, -1),
    b, DROP(C5#, 1),
    IF(a > b, "Higher", "Lower")
)

The final column - I call it 'status':

=LET(
    counter, SEQUENCE(ROWS(D5#)),
    MAP(
        counter,
        D5#,
        LAMBDA(c, d,
            LET(
                status, IFERROR(TEXTJOIN("-", , CHOOSEROWS(D5#, c, c + 1)), ""),
                SWITCH(
                    status,
                    "Higher-Higher", "Really High",
                    "Higher-Lower", "Higher",
                    "Lower-Lower", "Really Low",
                    "Lower-Higher", "Lower",
                    d
                )
            )
        )
    )
)

 

The workbook is now an XLSX - 20 MB.

 

Hi @Patrick2788

 

Thanks for taking a look and giving examples! I just set up the 'Test Calc' as a simple workbook for testing macros, but actually your examples for modifying the formulas are really interesting. I've not really come across using formulas in this way before.

 

My main calc is mostly a mess of nested 'IF' functions. Is there an easy way of making these more efficient? or am I going to have to go through each formula one-by-one trying to rewrite for efficiency? I know AI can sometimes help in this area, but it's usually after a long battle getting the ruddy thing to understand the requirements. 

 

Many thanks.

The way I approach writing logic in Excel: if there's more than 3 IFs - Am I better using a lookup function or SWITCH?

I'm away of IFS but prefer not to use.