I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

--
Tim Slattery
timslattery <at> utexas <dot> edu

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

On 9/14/2026 9:11 AM, Tim Slattery wrote:

I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

total number of rows and columns on a 2007 worksheet:
1,048,576 rows by 16,384 columns

You're not close.
Possibly corrupted file. Have you tried selecting everything and
pasting it into a new worksheet?


--
Science Doesn?t Support Darwin. Scientists Do


--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

Tim Slattery wrote:

[snip]

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

There is a limit, which came to light during the early months of
Covid-19. Some civil servant used Excel to record the number of cases in
the UK, and this broke a limit within Excel, so the numbers were
publicly reported as being relatively low.

A classic exercise in using the wrong tool for the job!

Anybody with a GCSE pass in computer science should have been aware of
the potential for this sort of problem.

--
Graham J

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

On Mon, 9/14/2026 11:25 AM, Graham J wrote:

Tim Slattery wrote:

[snip]

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

There is a limit, which came to light during the early months of Covid-19. Some civil servant used Excel to record the number of cases in the UK, and this broke a limit within Excel, so the numbers were publicly reported as being relatively low.

A classic exercise in using the wrong tool for the job!

Anybody with a GCSE pass in computer science should have been aware of the potential for this sort of problem.

I find the LLM-AI are good for corner-condition detection, as a few observations
of a phenomenon can be extracted quickly. They are perhaps better at this
than even "writing summaries of poorly written material".

******************** CoPilot Question *******************
A user has this problem description.

"I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)"

Since this is not even remotely close to the
1,048,576 rows by 16,384 columns limit, what other factors
could be at work to cause this failure ? Would the date range
of the entries factor into it ? I'm baffled.

******************** CoPilot Answer *******************

The short version: **Excel is not refusing Automatic calculation because
of row count or date range.** What you?re seeing almost always comes from
**a workbook?level or session?level override** - typically caused by
*the first workbook opened*, *a macro*, *an add?in*, *external links*, or
*file corruption*. Your symptoms match those patterns exactly.

Below is a detailed, structured breakdown of the *actual* mechanisms that
force Excel back into Manual mode, with citations to the sources found.

---

## Core takeaway
The calculation mode is **application?wide**, not per?sheet. If *any* workbook you open first was saved in Manual mode, Excel switches the entire session to Manual and will *not* allow you to change it back for that session if a macro/add-in keeps re-asserting Manual.

( https://exceloffthegrid.com/why-does-the-calculation-mode-keep-changing/ )

Your 17,000?row sheet is not large enough to trigger this behavior.
Dates do not affect calculation mode.

---

## What actually forces Excel into Manual mode

### 1. **The first workbook opened in the session**
Excel inherits its calculation mode from the first file you open.
If that file was saved in Manual mode, Excel switches the entire session to Manual.
This is documented clearly: the first workbook opened controls
the global calculation mode.

( https://exceloffthegrid.com/why-does-the-calculation-mode-keep-changing/ )

**Why this matches your symptoms:**
You said *other sheets in the same workbook* also revert to Manual.
That means the workbook itself is saved in Manual mode.

---

### 2. **Macros (VBA) or Office Scripts**
Any of these lines in VBA will silently force Manual mode:

Application.Calculation = xlManual
Application.Calculation = xlCalculationManual

If a macro runs on open (Workbook_Open), Excel will immediately
revert to Manual after you change the setting.

Sources confirm macros can override user settings.

( https://exceloffthegrid.com/why-does-the-calculation-mode-keep-changing/ )

---

### 3. **Add?ins (Bloomberg, Solver, Analysis ToolPak, financial add?ins)** Many add?ins switch Excel to Manual mode for performance reasons.
Bloomberg is notorious for this.

WiseChecker confirms add?ins frequently override calculation mode.

( https://wisechecker.com/excel-automatic-calculation-turns-manual-fix/ )

**Test:**
Disable all COM add?ins --> restart Excel --> open your workbook first.

---

### 4. **External links that fail to update**
If your workbook contains links to other files that cannot be refreshed,
Excel may switch to Manual mode when opening the file.
This is documented in WiseChecker?s analysis of external link prompts.

( https://wisechecker.com/excel-automatic-calculation-turns-manual-fix/ )

---

### 5. **Volatile functions + performance heuristics**
Functions like INDIRECT , OFFSET , TODAY , NOW , RAND , RANDBETWEEN
can cause Excel or add?ins to push the workbook into Manual mode for performance.
This is listed as a major risk factor.

( https://everycalculators.com/what-makes-an-excel-worksheet-turn-automatic-calculation-off.html )

Your sheet has 17,000 rows - if many formulas use volatile functions, recalculation may be slow enough that Excel or an add-in decides to enforce Manual mode.

---

### 6. **Workbook corruption**
Corrupted calculation metadata can cause Excel to refuse to store the Automatic setting.
This is mentioned in diagnostic guides as a cause of persistent mode resets.

( https://everycalculators.com/what-makes-an-excel-worksheet-turn-automatic-calculation-off.html )

**Test:**
Copy all sheets into a *new* blank workbook --> save --> reopen --> try switching to Automatic.

---

### 7. **Shared or co?authored files**
Files saved from SharePoint/OneDrive can inherit Manual mode from another user. This is documented as a cause of inherited calculation settings.

( https://everycalculators.com/excel-workbook-calculation-keeps-changing-to-automatic.html )

---

## What does *not* cause this problem

### Row count
17,000 rows is trivial for Excel. The limit is 1,048,576 rows. You are nowhere near it.

### Date range
Dates do not affect calculation mode. Excel stores dates as serial numbers;
the range has no bearing on calculation settings.

### File size
Unless your workbook is hundreds of MB or contains tens of thousands of formulas,
file size is not the trigger.

---

## Practical diagnostic steps (in order)

1. **Close Excel completely.**
2. **Open Excel with no workbook loaded.**
3. Set **File --> Options --> Formulas --> Automatic**.
4. **Now open your checkbook workbook *first*.**
- If it flips to Manual immediately --> the workbook is saved in Manual mode or a macro is running.
5. **Disable all add?ins** (COM and Excel add?ins).
6. **Check for macros**:
- Press Alt+F11 --> look for Workbook_Open or any code setting Application.Calculation .
7. **Check for external links**:
- Data --> Edit Links.
8. **Copy all sheets into a new workbook** to rule out corruption.

---

## Non-obvious insight
Because calculation mode is **application?level**, the fact that
*other sheets in the same workbook* also revert to Manual is your
biggest clue: **the workbook itself is saved in Manual mode, or
something in that workbook is enforcing Manual mode on open.**

This is not a performance limit. It is a *state inheritance problem*.

---

## Follow?up question for you
Do you know whether your checkbook workbook contains **any macros, add?ins, or external links**?
That will determine the next step in narrowing down the cause.

******************** End: CoPilot Answer *******************

Paul

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

On Mon, 14 Sep 2026 09:47:52 -0500, sticks wrote:

On 9/14/2026 9:11 AM, Tim Slattery wrote:

[quoted text muted]
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

total number of rows and columns on a 2007 worksheet:
1,048,576 rows by 16,384 columns

You're not close.
Possibly corrupted file. Have you tried selecting everything and
pasting it into a new worksheet?

One trick is to do File?? Save As and select .xlsb type. That will
create a copy in binary format next to the original (I assume) .xlsx
format. Close the Excel program entirely, then reopen Excel and open
the .xlsb file.

That sometimes solves problems and sometimes does not, in my
experience. But you lose nothing by trying. If the binary version
does let you set calculation to automatic, do File?? Save As and save
as .xlsx type (with a new name), or simply continue using the .xlsb
one.

In any case, make sure that you have only one workbook open when
changing between manual and automatic calculation. When you have
multiple workbook open, Excel will apply the change to all open
workbooks.

P.S. Tim, you talked about setting "Automatically calculate" in a
sheet. That was probably just a thinko, and maybe you have only one
worksheet in your workbook, but as far as I'm aware all sheets in a
workbook always have the same calculation mode, and there's no way to
change just one sheet.

--
"The power of accurate observation is frequently called cynicism by
those who don't have it." --George Bernard Shaw

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

Graham J <nobody@nowhere.co.uk> wrote:

Tim Slattery wrote:

[snip]

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

There is a limit, which came to light during the early months of
Covid-19. Some civil servant used Excel to record the number of cases in
the UK, and this broke a limit within Excel, so the numbers were
publicly reported as being relatively low.

It wasn't excel per se, but the file format. They'd used .xls rather than
.xlsx which has s much lower row limit.

A classic exercise in using the wrong tool for the job!

Anybody with a GCSE pass in computer science should have been aware of
the potential for this sort of problem.

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

Tim Slattery <TimSlattery@utexas.edu> wrote:

I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

Which format do you save your file as? Some really old .xls formats only
allow 16k rows. Save it as an .xlsx to be sure you're not hitting any
limits.


--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

On Tue, 9/15/2026 2:26 AM, Chris wrote:

Tim Slattery <TimSlattery@utexas.edu> wrote:

I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

Which format do you save your file as? Some really old .xls formats only allow 16k rows. Save it as an .xlsx to be sure you're not hitting any
limits.

https://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx

"The limitations of xls is still 256 columns and 65536 rows,
if you use that file format in a newer Excel version.
"

And the LLM-AI answer did not pick up that corner case.

You would think, upon attempting to save something into
a file format with limits, a dialog would appear indicating
usage of the format (XLS) would truncate the content
of the worksheet.

Paul



--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

On 09/15/2026 2:26 AM, Chris wrote:

Tim Slattery <TimSlattery@utexas.edu> wrote:

I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

Which format do you save your file as? Some really old .xls formats only allow 16k rows. Save it as an .xlsx to be sure you're not hitting any
limits.

Excel 97-2003
65,536 (2??)

Excel 2007+
1,048,576 (2ý?)

--
...w­¤?ñ?¤

--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)

...winston <winstonmvp@gmail.com> wrote:

On 09/15/2026 2:26 AM, Chris wrote:

Tim Slattery <TimSlattery@utexas.edu> wrote:

I run Office 2007 on my Win11 laptop. I know it's ancient, but it does
what I need.

I keep my checkbook in an Excel spreadsheet. It goes back something
like 20 years and now has something over 17,000 rows. Recently, I have
been unable to set "Automatically calculate formulas" in that sheet.
In other sheets it's not a problem, but in that one, I can find and
set the option, but when I close the options dialog, it's instantly
set back to "Manual". (That applies to other sheets in that workbook
too, although they are only 50 to 100 rows.)

I'm wondering if the number of rows in the sheet has exceeded some
limit. Does any body have a clue?

Which format do you save your file as? Some really old .xls formats only
allow 16k rows. Save it as an .xlsx to be sure you're not hitting any
limits.

Excel 97-2003
65,536 (2??)

Excel 2007+
1,048,576 (2ý?)

Excel 95
16,384

https://www.askingbox.com/info/xls-and-xlsx-maximum-number-of-columns-and-rows


--- PyGate Linux v1.5.19
* Origin: Dragon's Lair, PyGate NNTP<>Fido Gate (3:633/10)