What is the fastest fix when formulas display as text?

Change the cell format to General, turn off Show Formulas mode with Ctrl+`, and then use Text to Columns → Finish to reevaluate the cells. This resolves most cases immediately.

What should I do when values do not update?

Change Formulas > Calculation Options to Automatic and press F9 to recalculate the entire workbook. If you use Manual calculation because the file is large, be sure to press F9 before saving.

Why do some files require semicolons (;)?

Depending on the system locale, the function argument separator may be set to ; instead of ,. Check the settings under File > Options > Advanced or modify the formula for your environment.

How do I remove leading apostrophes and invisible spaces?

Use SUBSTITUTE and TRIM to remove NBSPs and leading apostrophes, then run Text to Columns → Finish to have Excel recognize the cells as formulas again.

CONTENTS

Excel Formulas Not Calculating — Fix Text Displays in 90 Seconds

When Excel formulas are not calculating, the most common causes are Text formatting, Show Formulas mode, and Manual calculation mode. Apply the following checklist and fixes in order to restore normal operation at once.

90-Second Quick Fix

Select the range → set Format Cells to General → press F2, then Enter, or choose Data > Text to Columns → Finish.
Press Ctrl+` to turn off Show Formulas mode.
Set Formulas > Calculation Options to Automatic, then press F9 to recalculate.

Summary of Causes (7)

Text formatting, leading apostrophes, or hidden spaces (NBSP)
Show Formulas mode (Ctrl+`)
Calculation Options set to Manual
Separators (, / 😉 or European decimal formats
R1C1 reference style
Sheet or cell protection

Fixes by Situation

Text Formatting, Leading Apostrophes, and Hidden Spaces

=SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), "'","")

After cleaning, use Text to Columns → Finish to have Excel recognize the formula again.

Show Formulas Mode

Press Ctrl+` to switch back to displaying results.

Automatic/Manual Calculation

Choose Formulas > Calculation Options > Automatic, then press F9 to recalculate the workbook or Shift+F9 to recalculate the worksheet.

Separators and European Decimal Formats

=NUMBERVALUE(A2, ",", ".")

R1C1 and Sheet Protection

Clear the R1C1 checkbox under File > Options > Formulas, and select Review > Unprotect Sheet.

Practical Example

// Remove NBSPs/leading apostrophes from a formula string
=SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), "'","")

Troubleshooting

Symptom	Cause	Fix
=SUM displays as entered	Text formatting/Show Formulas mode	Use General format, turn off Ctrl+`, and use Text to Columns
Values do not change	Manual calculation	Set calculation to Automatic and press F9
#VALUE!	Numbers stored as text	Use VALUE/TRIM/SUBSTITUTE or NUMBERVALUE
Function separator error	, / ; locale differences	Adjust the separator for your environment
References use R1C1	R1C1 style	Turn it off in Options

Prevention Checklist

For external pastes, use Values only → enter formulas only where needed
Set Calculation Options to Automatic in templates
Check separators and locale settings in international files
Use Power Query to standardize data types when cleaning large datasets

엑셀 피벗테이블에서 값이 합계(Sum)가 아니라 개수(Count)로 표시될 때 원인·해결(숫자형 정리, 값 필드 설정, 공백/텍스트 처리, 파워쿼리 타입 고정)

Excel Formulas Not Calculating: Fix Formulas That Display as Text or Don’t Update