AI how-to notes · 2026-09-18 · last run 2026-09-18 · 한국어 원문

When an Excel formula will not calculate — reproducing text format, a leading apostrophe and manual calculation

One line summary

We built four separate cases of a formula not working and measured the symptom and the value after recovery. The two cases that showed the literal text =A1+A2 on screen were cells Excel never accepted as formulas (text format, and a leading apostrophe); the case that showed a number that refused to change had the calculation option set to manual.

Symptoms

• The cell shows the literal text =A1+A2 and no result.
• You change a number in a referenced cell and the total stays put.
• Some cells work and others do not.

Test environment

For each case we printed six things to see how Excel regarded the cell: the text on screen (.Text), the value (.Value2), the formula (.Formula), whether it counts as a formula (.HasFormula), the cell format (.NumberFormat), and any prefix character (.PrefixCharacter).

Reproducing the four cases

Below is the setup part lifted out of the script we ran. In practice, for case 3 we switched to manual and then changed A1 to 100 to confirm the value did not follow, and case 4 ran after calculation had been set back to automatic and A1 restored to 10.

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Range("A1").Value2 = 10
$ws.Range("A2").Value2 = 20

# 경우1 — 셀 서식을 '텍스트'로 만들어 두고 수식을 넣는다
$ws.Range("B1").NumberFormat = "@"
$ws.Range("B1").Formula = "=A1+A2"

# 경우2 — 앞에 작은따옴표를 붙여서 넣는다
$ws.Range("B2").Value2 = "'=A1+A2"

# 경우3 — 계산 옵션을 수동으로 바꾼다 (-4135 = xlCalculationManual)
$ws.Range("B3").Formula = "=A1+A2"
$xl.Calculation = -4135

# 경우4 — 자기 칸을 가리키는 수식
$ws.Range("B4").Formula = "=B4+1"

Results — symptom and recovery

Cases 1 and 2 look identical on screen. Both have .Text of =A1+A2 and .HasFormula of False. You tell them apart by the cell format and the prefix character.

A cell that went in as text sits left-aligned; a calculated number sits right-aligned. On a table where nobody has touched the alignment, that is the fastest thing to spot.

Case 1 — resetting the format is not enough

Print the cell again right after switching the format back from text to General and you get this.

[서식만 일반으로 바꾼 뒤] B1
    화면에 보이는 것(.Text)  = '=A1+A2'
    값(.Value2)              = '=A1+A2'
    수식인가(.HasFormula)    = False
    셀 서식(.NumberFormat)   = 'G/표준'

The format changed to General (G/표준 is the Korean name for it) and it is still text. A value that already went in as text does not turn back into a formula just because you change the format. The same formula has to be entered once more.

[수식을 다시 넣은 뒤] B1
    화면에 보이는 것(.Text)  = '30'
    수식인가(.HasFormula)    = True

The recovery verified in the original 2026-09-18 run was setting .Formula once more over COM. The “Steps in the Excel window” section below adds the format-and-re-entry procedure checked against official help. We have not executed that on-screen procedure ourselves.

Case 2 — the apostrophe is invisible on screen

The cell holding '=A1+A2 had a perfectly ordinary G/표준 format and still did not calculate. The leading apostrophe was not part of .Text; it survived only in .PrefixCharacter as '. Which is to say the text on screen was identical to case 1. Re-entering the formula in this cell also produced 30, and .PrefixCharacter came back empty.

Case 3 — the number is there but never changes

With =A1+A2 sitting at 30, we switched the calculation option to manual and then changed A1 from 10 to 100. It should have become 120; it stayed at 30.

Look at the status bar at the bottom left and there is one extra word next to 「준비」 (Ready) — 「계산」 (Calculate). It means calculations are pending. In the after-recovery capture the word is gone.

There are two ways to recover.

1. Calculate once — $xl.Calculate() over COM. Calling it flipped the value to 120. The matching key on screen is F9, but we did not confirm it by pressing the key.
2. Keep it automatic — $xl.Calculation = -4105 over COM. We changed A1 to 7 and then set this back to automatic, and it became 27.

One file someone sent you stops your other files

The calculation option is not held per file. We checked. We made one file saved with calculation set to manual and one saved with it automatic, then opened them in a fresh Excel in both orders and read Application.Calculation.

In both orders, the setting from the file opened first carried straight over to the file opened second. With the manual file open first, we opened the file saved automatic and changed its A1 to 100 — the total cell stuck at 3 and never became 102. If a file of yours that was fine suddenly stops calculating, suspect whatever you opened first that day. What we verified goes as far as two workbooks open inside the same Excel.

Case 4 — circular reference

We put =B4+1 in B4. The value became 0 and .HasFormula was True. It is a formula, it just did not calculate. Reading the sheet's .CircularReference returned B4. Changing it to =A1*2, which does not point at its own cell, gave 20.

We ran with dialogs turned off over COM, so we never saw the on-screen warning.

Where to start looking

1. Look at whether the cell hugs the left or the right. In our captures, cells that went in as text sat left and calculated numbers sat right. On a table where the alignment has been adjusted this signal does not hold, so treat it as a hint and read it together with the format.
2. Look at whether the cell's format is 「텍스트」 (Text). In our case 1 the cell had .NumberFormat of @ and .HasFormula of False.
3. We saw two ways a cell stays text despite a General format — the format was changed without re-entering the formula (case 1), and a leading apostrophe (case 2). The fix for both is the same, re-enter the formula.
4. If the number appears but never changes, look for 「계산」 in the status bar → case 3.
5. If you get 0, check whether the formula includes its own cell → case 4.

Steps in the Excel window

2026-09-28 supplement — checked against official help; these on-screen steps were not executed directly. These paths are for Excel on Windows desktop. The recovery values and screenshots above come from the original 2026-09-18 run.

1. If a number does not update, check automatic calculation. Select Formulas → Calculation Options → Automatic. The same setting is under File → Options → Formulas → Calculation options → Workbook Calculation → Automatic. For one calculation, select Formulas → Calculate Now or press F9. On desktop, the calculation setting affects other open workbooks too. Microsoft calculation help
2. For text format, switch to General and re-enter. Select the cell, press Ctrl+1, choose Number → General → OK, then press F2 → Enter. For a leading apostrophe, select the cell with General format and type the whole =A1+A2 formula again without the apostrophe, then press Enter. Microsoft formula troubleshooting
3. If the whole sheet shows formulas, check the display setting. If Formulas → Show Formulas is enabled, select it again to display results. This command switches the display between formulas and their results. Microsoft Show Formulas help
4. Locate a circular reference before editing it. Select Formulas → the arrow beside Error Checking → Circular References, select a listed cell, and edit the formula so its references no longer lead back to itself. For a chain involving several cells, use Trace Precedents and Trace Dependents to follow it. Microsoft circular-reference help

What failed

• On Korean Excel, setting NumberFormat = "General" over COM raised Range 클래스 중 NumberFormat 속성을 설정할 수 없습니다. — "unable to set the NumberFormat property of the Range class". Switching to NumberFormatLocal = "G/표준" went through. The recoveries in this note ran that way.
• Reading $xl.Calculation with no workbook open at all returns -2146826246. Opening one document gave -4105, and closing everything again went back to -2146826246. We confirmed it three times but could not work out what the value means. Every calculation-option value in this note was read with a document open.
• We could not reproduce anything by clicking menus or pressing keys on screen. The three images are captures of the Excel window alone while values were changed over COM.

What we did not test

• In the original 2026-09-18 run, we did not click the ribbon menu that changes the calculation option. What we verified was the Application.Calculation value and the word 「계산」 in the status bar. The menu paths above were added after checking official help on 2026-09-28; they were not tested by clicking them.
• We did not try fixing several text-formatted cells at once.
• We did not check whether the apostrophe shows in the formula bar when you click a cell that has one. What we saw is the .Text and .PrefixCharacter values.
• We have not turned on 「수식 표시」 (Show Formulas) ourselves. The display-switching path above is from official help, not an additional measured case alongside the original four.
• We did not look at the on-screen warning text or the status bar indicator for a circular reference.
• We did not test a circular reference with iterative calculation (Iteration) turned on. Iteration was False during our tests.
• The only formulas used were additions (=A1+A2, =B1+B2), =B4+1 for the circular reference, and =A1*2 for the recovery. We did not look at whether functions with many arguments, such as VLOOKUP, behave the same.

Related notes

• Excel IF — where the result splits on the threshold, blanks and zero
• The order to clean an Excel table — merged cells, blank rows, text dates
• Finding and removing duplicates in Excel