ChatGPT Reads Excel but Ignores Formulas (Returns Them as Strings)

Q: Why does ChatGPT return `=SUM(...)` as text instead of the number?

ChatGPT loads `.xlsx` with `openpyxl`, which returns the formula string by default. The calculated number is only stored in the file when Excel itself last opened and saved it. A script-generated workbook has the formula but no cached value, so you see the formula text or `0`.

Q: Does `data_only=True` always fix it?

No. `data_only=True` returns the value Excel cached on its last save. If Excel never opened the file, there is no cached value and you get `None`. In that case re-save in Excel (Step 2), evaluate the formulas in Python with `xlcalculator` (Step 3), or rebuild in pandas (Step 4).

Q: My totals show as 0, not as formula text. Same cause?

Usually yes. A `0` means `openpyxl` read an empty cached slot (`None`) and a downstream `sum()` treated it as zero. Run the bucket check at the top; if `cached` is `None`, you are in the "never calculated in Excel" case.

Upload an XLSX and ChatGPT reports plain cell values fine, but formula-driven cells come back as the literal text '=SUM(A1:A10)' or as 0. Here is why openpyxl does this and the fastest fix as of June 2026.

Published: May 24, 2026 Updated: Jun 15, 2026 Author: AI Productivity Guide Team 🌐 查看中文版本

You upload a workbook full of =VLOOKUP(...), =SUMIFS(...), and pivot-table-driven cells, ask ChatGPT to summarize the totals, and the answer is plainly wrong: totals come back as 0, or it reports the cell contents literally as =SUM(B2:B100). This is not a model hallucination. It is how the Excel reader inside ChatGPT’s Advanced Data Analysis (the sandbox formerly called Code Interpreter) works. ChatGPT loads .xlsx with openpyxl, and openpyxl returns the formula text by default, not the calculated number. When the file was generated by a script and never opened in real Excel, there is no calculated number stored at all.

Fastest fix (most reliable, ~30 seconds): open the workbook in Excel, select all, Copy, then Paste Special - Values, save as a new .xlsx, and upload that values-only copy. Every formula cell is now a static number that any reader can see. The rest of this page covers why it happens, how to confirm which bucket you are in, and four other fixes when you cannot re-save in Excel.

Which bucket are you in?

Run this once in ChatGPT to find out, then jump to the matching fix:

import openpyxl
wb_f = openpyxl.load_workbook("file.xlsx")                 # formulas
wb_v = openpyxl.load_workbook("file.xlsx", data_only=True) # cached values
c = "B2"  # a cell you know holds a formula
print("formula:", wb_f.active[c].value)
print("cached :", wb_v.active[c].value)

What you see for the formula cell	Bucket	Go to
`cached` is a number	Reader was in formula mode	Step 1 (force `data_only=True`)
`cached` is `None`	File never calculated in Excel	Step 2, or Step 3 (evaluate in Python)
`cached` is `#REF!` / stale	External reference or live connection	Step 4 (rebuild)
Formula cell is empty, value lives elsewhere	Pivot table	Step 5 (re-aggregate source)

Common causes

1. openpyxl defaults to formula text, not values

openpyxl.load_workbook(file) without data_only=True returns the formula. With data_only=True it returns the last cached value Excel wrote — but only if Excel actually opened and saved the file recently. Files generated by libraries (xlsxwriter, exceljs) often have no cached values at all.

How to spot it: Ask ChatGPT to print a known formula cell. If output is =SUMIFS(...) instead of a number, openpyxl is in formula mode.

2. Workbook created by code, never opened in Excel

Pandas to_excel, xlsxwriter, openpyxl write — these write the formula text. Excel only computes formulas and caches values when you open and save the file in actual Excel. A file that was created by a Python script and never touched by Excel has formula strings with no cached values.

How to spot it: openpyxl.load_workbook(..., data_only=True) returns None for the cell — meaning no cached value exists.

3. External references and live data connections

Formulas like =INDIRECT("[other.xlsx]Sheet1!A1"), =GETPIVOTDATA(...), or anything pulling from Power Query / data model do not have meaningful values when the source workbook isn’t also uploaded. Even with data_only=True, you get the cache from the last time Excel could reach the source — often stale or #REF!.

4. Pivot tables are computed by Excel, not stored

Pivot tables live as cached blobs that only Excel knows how to render. openpyxl sees the source data and a stub, not the pivot output. ChatGPT cannot read a pivot table — it can only read the underlying range and rebuild the aggregation in pandas.

5. Volatile functions never get cached reliably

NOW(), TODAY(), RAND(), OFFSET(), INDIRECT() — Excel recomputes these on every open. Their cached value, if present, is whatever was last persisted. Trust it only if you know when the workbook was last opened in Excel.

Shortest path to fix

Step 1: Force openpyxl to use cached values

If your bucket check showed real numbers under cached, the reader was just in formula mode. Tell ChatGPT explicitly:

import openpyxl
wb = openpyxl.load_workbook("file.xlsx", data_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
    print(row)

If the file was last saved by Excel, this returns numbers. If you see None where formulas should be, the file has no cached values, so go to Step 2 or Step 3.

Step 2: Save as values-only before upload (most reliable)

This is the fix that always works and takes about 30 seconds. In Excel: select all (Ctrl+A / Cmd+A) - Copy - Paste Special - Values, then File - Save As a new .xlsx. Every formula cell is now a static number, so any reader sees it. Upload the values-only file.

Alternatively, File - Save As - choose .csv. CSV stores only computed values by definition, which is why a CSV export never has this problem.

Step 3: Evaluate the formulas in Python (no Excel available)

If you are on a machine without Excel (and the bucket check returned None), have ChatGPT evaluate the workbook’s formulas in the sandbox itself. The xlcalculator and pycel libraries compile Excel formulas to Python and compute them without Excel installed. Both are pip-installable in the Advanced Data Analysis sandbox:

# pip install xlcalculator
from xlcalculator import ModelCompiler, Evaluator
model = ModelCompiler().read_and_parse_archive("file.xlsx")
evaluator = Evaluator(model)
print(evaluator.evaluate("Summary!B2"))  # computes =SUM(...) etc.

These libraries cover common functions (SUM, SUMIFS, VLOOKUP, IF, most math/text/lookup) but not every Excel function, and they do not run pivot tables or external links. If xlcalculator raises on an unsupported function, fall back to rebuilding in pandas (Step 4).

Step 4: Rebuild the calculation in pandas

If you cannot re-save the file and the formulas are too custom for Step 3, ask ChatGPT to read the source ranges and recompute:

import pandas as pd
df = pd.read_excel("file.xlsx", sheet_name="Data")
# Re-derive the VLOOKUP / SUMIFS in pandas
totals = df.groupby("region")["amount"].sum()
print(totals)

You lose the original Excel formula audit trail but you gain a correct number, and the result is reproducible.

Step 5: For pivot tables, read the source and re-aggregate

A pivot table is a cached blob only Excel can render. openpyxl sees the source range and a stub, not the pivot output. Read the underlying sheet and rebuild the aggregation:

import pandas as pd
df = pd.read_excel("file.xlsx", sheet_name="RawData")
pivot = df.pivot_table(
    index="category",
    columns="quarter",
    values="revenue",
    aggfunc="sum",
)
print(pivot)

Faster and more flexible than wrestling with the cached pivot blob.

Step 6: Use the ChatGPT for Excel add-in (lets Excel do the math)

The ChatGPT for Excel and Google Sheets add-in (in beta from March 5, 2026, then generally available across all plans in May 2026) lets you skip the upload problem entirely. The add-in reads your actual cells, formulas, and tab structure and runs calculations directly in Excel, so it always sees the real computed values, not formula text. As of June 2026 it is available globally to ChatGPT Free, Go, Plus, Pro, Business, Enterprise, Edu, and K-12 users (Free and Go get limited usage). Install it from Excel via Insert - Get Add-ins (Office Add-ins store), search for ChatGPT, then sign in. Because the math runs inside Excel, you can also audit each formula it touches and undo any edit it proposes.

Step 7: Paste critical computed values separately

When nothing else is workable, open the workbook in Excel, copy the final 5-20 numbers you actually need, and paste them into chat as a markdown table. ChatGPT then has authoritative values without parsing any Excel internals.

How to confirm the fix

After re-saving or rebuilding, verify the read:

import openpyxl
wb = openpyxl.load_workbook("file.xlsx", data_only=True)
ws = wb["Summary"]
for row in ws.iter_rows(min_row=1, max_row=10, values_only=True):
    print(row)

Every formula cell should now show a number, not None or a = string. If still None, the workbook never went through Excel — fall back to Step 3 (rebuild in pandas).

Prevention

Always save Excel files in Excel (not via a script) before uploading — this caches every formula value.
For data destined for ChatGPT analysis, prefer “Paste Special - Values” copies. Keep the formula version as a separate audit file.
Avoid external references and live data connections in any workbook you plan to share with an LLM.
For repeated workflows, build the aggregation in pandas / SQL from the start — Excel is then just a viewer, and ChatGPT reads the raw data directly.
If the file must keep formulas, attach a small text file alongside listing the expected totals so ChatGPT can sanity-check its own reads.

FAQ

Why does ChatGPT return `=SUM(...)` as text instead of the number?

ChatGPT loads .xlsx with openpyxl, which returns the formula string by default. The calculated number is only stored in the file when Excel itself last opened and saved it. A script-generated workbook has the formula but no cached value, so you see the formula text or 0.

Does `data_only=True` always fix it?

No. data_only=True returns the value Excel cached on its last save. If Excel never opened the file, there is no cached value and you get None. In that case re-save in Excel (Step 2), evaluate the formulas in Python with xlcalculator (Step 3), or rebuild in pandas (Step 4).

Will saving as CSV lose my formulas?

CSV keeps the computed values and drops the formulas. That is exactly what you want for analysis, since ChatGPT reads the numbers cleanly. Keep your formula-bearing .xlsx separately as the audit copy.

Can ChatGPT read my Excel pivot tables?

Not directly. A pivot table is a cached object only Excel renders; openpyxl sees only the source range and a stub. Point ChatGPT at the underlying data sheet and have it rebuild the aggregation with pandas.pivot_table (Step 5).

Is there a way to avoid uploading files at all?

Yes, if you use the ChatGPT for Excel add-in (Step 6). It runs calculations inside Excel, so it reads the real computed values and never hits the formula-string problem. As of June 2026 it is generally available across all plans (Free, Go, Plus, Pro, Business, Enterprise, Edu, and K-12), with limited usage on Free and Go.

My totals show as 0, not as formula text. Same cause?

Usually yes. A 0 means openpyxl read an empty cached slot (None) and a downstream sum() treated it as zero. Run the bucket check at the top; if cached is None, you are in the “never calculated in Excel” case.

Tags: #ChatGPT #ChatGPT files #Troubleshooting #Debug #Excel