Microsoft Excel是一个非常强大的工具，可以轻松处理复杂的计算，但当您的Excel公式无法正常工作或计算时则无法处理。发生这种情况时，查明问题原因可能会令人沮丧，而找到适当的纠正措施则更令人沮丧。
值得庆幸的是，这些问题通常是由于简单的错误或设置错误而发生的，这些错误很容易解决。如果Excel公式无法计算或无法正常工作，请按照以下步骤解决问题。
切换到自动计算
Excel公式无法工作或无法计算的一个常见原因是Excel中的计算选项可能设置为手动而不是自动。这意味着当数据或公式发生更改时，工作簿不会自动更新。
要纠正此问题，请按照下列步骤操作：
- 打开Excel工作簿并选择“公式”(Formulas)选项卡。
- 按(Press)计算选项(Calculation Options)按钮。
- 从下拉菜单中，选择“自动”(Automatic)。
现在，您的公式应该会在进行更改时自动更新。如果您需要手动重新计算工作簿，可以随时按键盘上的 F9 来执行此操作。
Shift + F9重新计算当前活动工作表。
(Remove Text)从公式中
删除文本、空格或撇号(Spaces)
有时，Excel公式不起作用，因为它们包含文本、空格或撇号。这些元素可能会导致公式出现错误。要解决此问题，请仔细检查您的公式并删除任何不需要的字符或空格。
如果公式中有引号或撇号，请考虑使用双引号来表示单引号。例如，不要使用=“It's a beautiful day”，而是使用=“It”“sa beautiful day”。
解决此问题没有简单的方法 - 您需要仔细检查并检查是否在数据集中正确输入了公式。
切换到数字单元格格式
Excel公式无法正常工作的另一个常见原因是单元格格式问题。如果包含公式的单元格格式为文本而不是数字，则公式将显示为纯文本并且不会计算。
要解决此问题，请按照下列步骤操作：
- 打开Excel工作簿并选择包含公式的单元格。
- 在“主页”选项卡的(Home)“数字”(Number)部分中，选择“数字(Number)格式”的下拉列表。
- 为单元格选择适当的格式，例如“常规”(General)或“数字”(Number)。
更改单元格格式后，假设公式或数据集没有其他潜在问题，公式应开始按预期计算。
识别(Identify)并删除循环(Remove Circular)引用
当公式直接或间接引用自身时，就会出现循环引用，从而导致无法解析的无限循环。识别(Identifying)和修复循环引用对于公式正常工作至关重要。
要识别循环引用，可以使用Excel 状态栏中的
错误检查功能。(Error Checking)
- 打开Excel工作簿并选择“公式”(Formulas)选项卡。
- 在“公式审核”(Formula Auditing)部分中，按Error Checking > Circular References。
- 在菜单中，选择列出的循环引用之一 - Excel 将自动选择包含循环引用公式的单元格。
(Fix)通过相应地更改公式或单元格引用来修复这些引用。例如，您可以复制并粘贴在其他地方使用的数据，并更新公式以使用该数据，从而允许您在不进入循环引用循环的情况下引用它。
一旦这些问题得到解决，你的公式就应该起作用了。
修复不正确的公式语法(Fix Incorrect Formula Syntax)或括号
公式在Excel(Excel)中不起作用的常见原因可能是函数参数语法错误或括号使用不正确。这可能会导致类似#N/A formula error错误，其中公式所需的数据未正确引用。
确保您了解函数所需的参数并相应地使用它们通常是一个好主意。虽然没有可遵循的黄金法则，但最好确保您：
- 公式始终(Always)以等号 = 开头。
- 确保函数参数以逗号分隔。
- 使用(Use)括号 () 对计算进行分组并阐明复杂公式中的优先顺序。
此外，您应该检查当公式中的文本未用引号括起来或数字格式设置为文本时（如上所述）是否会出现损坏的公式。删除任何引号或更改数字格式以解决这些问题。
使用“立即计算”按钮
Excel是内存密集型的 — 工作簿越大，重新计算数据就越困难。如果Excel公式未按预期工作（即使打开自动更新），您可能需要强制手动重新计算，尤其是当您一次更改大量数据时。
要强制重新计算公式，您可以使用“立即计算”(Calculate)按钮。去做这个：
- 打开Excel工作簿并选择功能区栏上的
“公式”选项卡。(Formulas)
- 找到计算(Calculation)组并按立即计算(Calculate)按钮。
这将强制Excel重新计算工作簿中的所有公式。如果您在使用大型Excel工作簿时仍然遇到问题，您可能需要考虑将部分数据切换到多个工作簿(multiple workbooks)。
然后，您可以根据需要
将数据导入到主Excel文件中。(Excel)
查看和编辑从属单元格
了解单元格之间的关系对于解决公式问题至关重要。如果公式在Excel中计算不正确，则查看和编辑从属单元格（公式引用的单元格）会很有用。
- 为此，请打开Excel工作表并选择包含无法正确计算的公式的单元格。
- 在“公式”(Formulas)选项卡上，找到“公式审核”(Formula Auditing)部分。
- 按(Press)“跟踪相关项”(Trace Dependents)按钮。这将显示指向依赖于所选单元格的单元格的箭头。
- 要编辑从属单元格，请双击箭头指向的单元格。请记住仔细检查相关单元格本身不包含错误或格式不正确的内容。
- 要删除箭头（进行更改后），请按“公式审核”组中的(Formula Auditing)“删除箭头”(Remove Arrows)按钮。
解决Microsoft Excel中的错误(Microsoft Excel)
当您的Excel公式无法正常工作或计算时，请不要惊慌 - 在大多数情况下，按照上述步骤即可解决问题。如果它不起作用，请尝试检查您的数据，重写所有不起作用的公式，或者完全切换到新的工作簿以查看是否可以解决问题。
如果需要处理的数据过多并且Excel 停止响应(Excel stops responding)，请先尝试重新启动应用程序，然后再尝试更彻底的修复。
正在为外部数据源而烦恼吗？如果您需要断开 Excel 中的链接(break links in Excel)，请不要忘记更新您的公式，否则它们也会停止工作。如果您想完全放弃Excel，您可以随时将Excel文件转换为Google Sheets，然后开始在线处理电子表格。
Microsoft Excel Formulas Not Working or Calculating? Try These 7 Fixes
Mіcrosoft Excel is an incredibly powerful toоl that can handle complex calculations with ease, but not when your Excel formulas aren’t working or calculating properly. When this happens, it can be frustrating to pinpoint the cause of the problem and even more frustrating to find the appropriate correction.
Thankfully, these issues often occur due to simple mistakes or settings errors that can be easily resolved. If Excel formulas aren’t calculating or working for you, follow the steps below to troubleshoot the problem.
Switch to Automatic Calculation
One common reason for Excel formulas not working or calculating is that the calculation options in Excel may be set to manual instead of automatic. This means that the workbook won’t automatically update when changes are made to the data or formulas.
To correct this issue, follow these steps:
- Open your Excel workbook and select the Formulas tab.
- Press the Calculation Options button.
- From the drop-down menu, select Automatic.
Your formulas should now update automatically when changes are made. If you need to recalculate your workbook manually, you can do so by pressing F9 on your keyboard at any point.
You can also recalculate the current active sheet by pressing Shift + F9.
Remove Text, Spaces, or Apostrophes from a Formula
Sometimes, Excel formulas don’t work because they contain text, spaces, or apostrophes. These elements can cause errors in your formulas. To troubleshoot this problem, carefully review your formulas and remove any unwanted characters or spaces.
If you have a quotation mark or apostrophe in your formula, consider using double quotation marks to represent a single quotation mark. For example, instead of =”It’s a great day”, use =”It””s a great day”.
There’s no easy way to resolve this problem—you’ll need to go through and check that you’ve inputted your formulas correctly across your data set.
Switch to Number Cell Formatting
Another common cause of Excel formulas not working properly is cell formatting issues. If the cell containing your formula is formatted as text, rather than a number, the formula will be displayed as plain text and won’t calculate.
To fix this issue, follow these steps:
- Open your Excel workbook and select the cell containing the formula.
- In the Number section on the Home tab, select the drop-down for the Number format.
- Choose the appropriate format for the cell, such as General or Number.
After changing the cell format, the formula should start calculating as expected, assuming there are no other underlying problems with the formula or data set.
Identify and Remove Circular References
Circular references occur when a formula directly or indirectly refers to itself, causing an infinite loop that can’t be resolved. Identifying and fixing circular references is crucial for your formulas to work correctly.
To identify circular references, you can use the Error Checking feature in Excel’s status bar.
- Open your Excel workbook and select the Formulas tab.
- In the Formula Auditing section, press Error Checking > Circular References.
- In the menu, select one of the circular references listed—Excel will automatically select the cell containing the circular reference formula.
Fix these references by changing the formula or cell reference accordingly. For instance, you can copy and paste the data you’re using elsewhere and update your formula to use that data instead, allowing you to reference it without entering a circular reference loop.
Once these problems are resolved, your formula should work.
Fix Incorrect Formula Syntax or Parentheses
A common cause for formulas not working in Excel can be errors with function argument syntax or incorrect use of parentheses. This can cause errors like the #N/A formula error, where the data a formula needs isn’t being referenced correctly.
It’s usually a good idea to make sure that you understand the function’s required arguments and use them accordingly. While there’s no golden rule to follow, it’s a good idea to make sure that you:
- Always start a formula with an equal sign =.
- Ensure function arguments are separated by commas.
- Use parentheses () to group calculations and to clarify the order of precedence in complex formulas.
Additionally, you should check for broken formulas that occur when you have text within a formula that isn’t enclosed in quotes or when a number is formatted as text (as we explained above). Remove any quotes or change the number format to fix these issues.
Use the Calculate Now Button
Excel is memory intensive—the larger the workbook, the harder it is for your data to recalculate. If an Excel formula isn’t working as expected (even with automatic updating switched on), you may need to force a manual re-calculation, especially when you’ve made changes to a large amount of data at once.
To force the formulas to recalculate, you can use the Calculate Now button. To do this:
- Open your Excel workbook and select the Formulas tab on the ribbon bar.
- Locate the Calculation group and press the Calculate Now button.
This will force Excel to recalculate all formulas in the workbook. If you still have problems working with a large Excel workbook, you may need to consider switching out some of your data to multiple workbooks.
You can then import the data into your main Excel file as and when you need to.
View and Edit Dependent Cells
Understanding the relationships between cells is crucial for troubleshooting formula issues. If a formula isn’t calculated correctly in Excel, it’s useful to view and edit the dependent cells—cells that the formula references.
- To do this, open your Excel sheet and select the cell containing the formula that isn’t calculating properly.
- On the Formulas tab, locate the Formula Auditing section.
- Press the Trace Dependents button. This will display arrows pointing to the cells that depend on the selected cell.
- To edit the dependent cells, double-click on the cell the arrow is pointing to. Remember to double-check that the dependent cells themselves do not contain errors or incorrectly formatted content.
- To remove the arrows (after making changes), press the Remove Arrows button in the Formula Auditing group.
Resolving Errors in Microsoft Excel
When your Excel formulas aren’t working or calculating properly, don’t panic—follow the steps above to resolve the issue in the majority of cases. If it doesn’t work, try to go through your data, rewrite any formulas that aren’t working, or switch to a new workbook entirely to see if it resolves the problem.
If there’s too much data to handle and Excel stops responding, try restarting the application first before you try more drastic fixes.
Struggling with external data sources? If you need to break links in Excel, don’t forget to update your formulas, or they’ll stop working, too. If you want to ditch Excel completely, you can always convert your Excel file to Google Sheets and start working on your spreadsheet online instead.
