Excel 'What if' Data table cannot simulate using data from a different sheet in Windows 11. Behavior works in Windows 10, same version of Excel.

Question

We are operating a large excel sheet, running Monte Carlo simulations (10.000's) using data tables, drawing on data from another sheet where multiple values are dependent on the Trial ID. We have recently experienced an issue where behavior for users have changed when upgrading their Windows version from Windows 10 (business) to Windows 11.

Data Tables cannot perform simulation using data from a different sheet (in same workbook) when using "Calculate Sheet" ("Automatic, except data tables" enabled). Previously, this have not been a problem.

The issue is reproducible in a clean workbook!

Sheet 1 has data, which changes depending on a trial ID. Sheet 2 contains trial ID, and a column of numbers [1..200]. The goal is to calculate a sum across three rows in Sheet 1 - Subtotal(9;C2:C6), and display this sum for each of the 200 Trial IDs using a data table.

Set-up:

1. Create workbook with two sheets.
2. In Sheet 1, in row 1, fill in A1( =Sheet2!$B$2 )
3. In Sheet 1, in row 3, fill in A3("Column 1") B3("Column 2") C3("Column3").
4. In Sheet 1, in row 4, add these formulas: A4( =$A$1+3 ) B4( =A4*2) C4( =SUM(A4:B4)
5. Fill formulas from row 4 to rows 5 and 6.
6. In Sheet 2, in row 2, fill in A2("Trial ID:") B2( "1" )
7. In Sheet 2, in row 4, fill in C4( =SUBTOTAL(9,Sheet1!C4:C6)
8. In Sheet 2, in column B, Fill in numbers 1..200 starting in cell B5.

Intended behavior in Windows 10, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), Each trial is run, and produces different values, depending on the data in Sheet 1.
• If you change data in Sheet 1, return to Sheet 2, and click "Calculate Sheet", each trial is re-run with updated values.

Wrong behavior in Windows 11, 22H2 with excel v 2301 build 16026.20200:

• When you set up a data table with the 200 trial ID's and click "Calculate Sheet" (with feature "Automatic, except data tables" enabled), the first run may produce all 0's for each trial. Following attempts will provide the same value for all trials.
• If you now run "Calculate Now" (to calculate entire workbook), you get correct results as per Windows 10. However, if you now change a value in Sheet 1, go back to Sheet 2, and run "Calculate Sheet", it will again provide the same value for all trials.

We have reproduced good behavior on several Win 10 devices.

We have reproduced bad behavior on several Win 11 devices, leading us to believe the change is related to OS, and not excel version.

Can anyone else reproduce this behavior in Windows 11? Is there a setting we need to change?

This is a major issue for our workflows, as using "Calculate Now" in stead of "Calculate Sheet" means we have to run all simulations (24 data tables á 10.000 trials), rather than selected data tables, leading to each simulation run taking 15-30 minutes rather than seconds.

Theory: My theory is, that some behavior has changed from Windows 10 to Windows 11, where "Calculate Sheet" in excel is no longer allowed to access/calculate dependent fields in other sheets, as part of its calculation, leading to no values changing with trial ID. Why this change happens, I do not know.

Answer 1

Yes, this is our suspicion too. Do you have recommendations on how to take this issue further, to have it resolved?

As far as I know, I am on the most recent version of Win 11 and excel. As said previously, we have tested on different devices. See original post.

Answer 2

It's possible that the behavior of Excel has changed with the release of Windows 11, or there could be other factors at play that are causing the issue. It's also possible that the issue is specific to the version of Excel that you and your colleague are using.

One thing you could try is to update your version of Excel to the latest available version, to see if that resolves the issue. Microsoft regularly releases updates to Excel to address bugs and improve performance.

Another thing you could try is to test the behavior of your Excel file on a different computer running Windows 10 or a different operating system entirely. This could help you determine whether the issue is specific to your computer or more widespread.

If updating Excel or testing on a different computer doesn't resolve the issue, you may need to do some further troubleshooting to identify the cause of the problem. This could involve looking for conflicts with other software or add-ins that you're using with Excel, or looking for issues with your computer's hardware or operating system that could be affecting Excel's performance.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 3

Sneha,

Thank you for elaborating. I understand the concept of the three options for calculation. I thought from your first message, that it is possible to make customizations to the calculation chain.

I appreciate that the issue may be that it will not recalculate Sheet 1 when simulating the data table. This is also my theory as per the original post.

My issue is, that this is not historically the behavior excel has had, and as Hans points out above, he is not seeing the issue either.

Answer 4

Tak fordi du testede, Hans.

Interessant finding.

Jeg har denne oplevelse med Win 11 Pro, 22H2 22621.1265. Min kollega med samme problem har Win 11 Business 22H2, 22621.1105

Begge anvender Excel v 2301 Build 16026.20200.

Answer 5

Sure, let me explain the order in which Excel calculates formulas. When Excel calculates a workbook, it follows a specific order of operations to determine the value of each formula in the workbook. The order in which Excel calculates formulas is known as the calculation chain.

The calculation chain starts with cells that contain input values or formulas that do not depend on other cells. Excel calculates these cells first and then moves on to cells that depend on these cells, and so on, until all cells in the workbook have been calculated.

When you have a data table that depends on cells in another sheet, Excel needs to calculate the cells in the other sheet first, before it can calculate the data table. In Excel, there are different calculation modes that determine how Excel calculates the workbook. The "Automatic except data tables" calculation mode is designed to prevent Excel from recalculating data tables every time you change a value in the workbook. This means that Excel will not automatically recalculate the cells in the other sheet that the data table depends on, which can lead to the issue you are experiencing.

In summary, when you have a data table that depends on cells in another sheet, and you are using the "Automatic except data tables" or "Manual" calculation mode, Excel may not recalculate the cells in the other sheet that the data table depends on, which can lead to incorrect results.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha