Excel is a versatile tool for handling data, but users may face issues like formulas displaying as text rather than results. This can be frustrating and affect productivity. This guide provides step-by-step solutions to troubleshoot and resolve this common problem, ensuring accurate calculations and proper data presentation.
What Causes Excel to Show Formulas Instead of Results?
MS Excel uses an array of cells organized in a spreadsheet to manipulate data and perform mathematical functions.
Users use the formula bar to enter a desired formula in a cell and perform a calculation. The following example showcases data.
The bottom right cell shows the formula instead of doing calculations, while other cells perform certain functions like average and count.
Sometimes, an Excel glitch leads to the software displaying the formula's text instead of the calculated result within the spreadsheet.
Here are the 5 most common reasons for Excel showing formula instead of result.
Formula visibility function in Excel lets the user observe actual mathematical expressions or formulas within Excel cells.
If the show formula setting is enabled, Excel will show the formulae/functions used instead of the calculation results.
Excel displays formulas rather than results if a cell is formatted to show formulas or the "Show Formulas" option is enabled.
Cell Reference Errors occur when formulas contain incorrect or unavailable cell references, disrupting accurate calculations in Excel.
Incorrect cell references, circular references, or missing data in referenced cells can prevent Excel from calculating and displaying results.
Calculation Settings determine how Excel processes and updates formulas for accurate results.
When Excel is in manual calculation mode, formulas won't update automatically, requiring manual intervention to display their results.
Array Formulas involve performing calculations on arrays of data, while volatile functions update whenever changes occur in a worksheet.
Complex array formulas or functions that update frequently (volatile functions) can cause temporary formula display during calculation.
Data and External Links can also sometimes interfere with the documents’ progress and result in glitches due to network or software errors.
Issues with linked workbooks, external references, or outdated data sources can hinder proper formula calculation and result display.
Two Main Causes Resulting in Excel Formula Error
If you're not seeing any results after entering a formula in Excel, there are two main potential causes for this situation:
Accidental Activation of "Show Formulas" Feature
Sometimes, the "Show Formulas" feature might have been activated unintentionally. This makes Excel display the actual formula text instead of the calculated results. To fix this:
Step 1: Check the "Formulas" tab on the Excel ribbon. If the "Show Formulas" button is highlighted, click it to turn it off. Alternatively, press Ctrl + `` to toggle this feature.
Step 2:Once turned off, Excel should display the calculated results for your formulas.
Formula Treated as Text
Another common reason for not getting results is when Excel interprets your formula as plain text rather than a mathematical operation. To address this:
Step 1: Double-check your formula for any syntax errors or missing operators.
Step 2: Ensure that your formula starts with an equal sign (=), which signals Excel that you're entering a formula and not text.
Step 3: Verify that cell references in your formula are correctly formatted (e.g., A1 instead of 'A1').
How to Fix Excel to Show Formulas Instead of Results?
Here are some of the quick fixes to help you get through the hassle of not getting proper results.
1. Using the Cell Format Option
Open your Excel spreadsheet.
Select the cell or cells containing the formulas you want to display.
Right-click on the selected cell(s) and choose "Format Cells."
In the "Format Cells" dialog box, go to the "Number" tab.
In the "Category" list, select "Text."
Click "OK."
The selected cells will now display the formulas instead of the calculated results. Note that this method will also prevent any further calculations in these cells until you change the formatting back to a numeric format.
2. Formula Errors
Look for formula errors by checking for green triangles in the top-left corner of cells. Clicking on the triangle provides information about the error. Common errors include division by zero (#DIV/0!), invalid cell references (#REF!), or incorrect function usage (#NAME?, #VALUE!, etc.).
#DIV/0! - Division by Zero Error: This error occurs when you attempt to divide a number by zero.
#VALUE! - Value Error: There are multiple reasons why this error can pop up, such as using the wrong data type or incorrect arguments in a function.
#REF! - Reference Error: When a cell reference is invalid, such as when you delete a referenced cell or range.
#NAME? - Name Error: It appears when Excel doesn't recognise a function name or label in a formula.
#N/A - Not Available/Error: This occurs when a lookup function (like VLOOKUP or HLOOKUP) can't find the specified value.
#NUM! - Number Error: It appears when a numeric value is invalid for the specific function.
#NULL! - Intersection of Two Ranges Error: This error occurs when referencing an intersection of two ranges that don't intersect.
#SPILL! - Spill Error: When the result of a formula spills over multiple cells unintentionally due to a conflict with adjacent data.
3. Use Function Wizard
If you're uncertain about constructing a formula, use the "Insert Function" (fx) button next to the formula bar. It opens the Function Wizard, guiding you through the process of entering a function correctly.
4. Evaluate Formula
Excel provides the "Evaluate Formula" feature that lets you process a specific formula's calculation process. Go to the "Formulas" tab > "Formula Auditing" group > "Evaluate Formula" to identify where issues may arise.
5. Adding Space before the equality sign
Sometimes, when starting a formula, people accidentally put a space before the equal sign. However, formulas must begin with an equal sign without any spaces before it. Remember to avoid that extra space and start with the equal sign directly to make sure your formulas work correctly. It will help you get the results you want in your worksheet.
6. Quoting Formulas
Often, people tend to enclose formulas or significant content in quotes. However, when working with Excel, it's important to avoid using quotes around formulas, as this can potentially disrupt their proper functioning. Quotes should only be used within a formula when it's absolutely necessary.
How to Edit Microsoft Office Word for Free?
WPS Office is a free office suite that allows you to open, create, edit, and save Microsoft Office files, including Word documents, Excel spreadsheets, and PowerPoint presentations. It offers full compatibility with both Windows and Mac operating systems. You can seamlessly edit, craft, manage and edit your documents, sheets, and presentations from one platform.
With WPS Office, you can seamlessly work with Word documents without needing a paid Microsoft Office subscription. It provides a range of features similar to those found in Microsoft Word, allowing you to format text, insert images, create tables, and more. The suite also offers compatibility with popular file formats, ensuring smooth collaboration with others using Microsoft Office.
Steps to Edit Word/Excel/Powerpoint in WPS
Editing Word, Excel, and PowerPoint documents in WPS Office is straightforward.
Editing Word Documents
Step 1: Open WPS Writer (the word processing component of WPS Office).
Step 2: Click "File" in the top-left corner.
Step 3: Select "Open" and choose the Word document you want to edit from your computer.
Step 4: Make your edits within the document using the available tools.
Step 5: When you're done editing, click "File" and then "Save" to save your changes.
Editing Excel Spreadsheets
Step 1: Open WPS Spreadsheets.
Step 2: Click "File" in the top-left corner.
Step 3: Choose "Open" and select the Excel spreadsheet you wish to edit.
Step 4: Edit the spreadsheet by updating values, formulas, formatting, etc.
Step 5: Once you've finished editing, click "File" and then "Save" to save your modifications.
Editing PowerPoint Presentations
Step 1: Launch WPS Presentation.
Step 2: Click "File" in the upper-left corner.
Step 3: Select "Open" and select the PowerPoint presentation or template you want to edit.
Step 4: Make your desired changes to the presentation's slides, content, and design.
Step 5: After completing the edits, click "File" and then "Save" to save your updated presentation.
How to Use the Collaboration Feature in WPS to Edit and Create Documents.
WPS Office provides collaboration features allowing multiple users to edit and create documents in real-time. Here's how to use the collaboration feature in WPS Office:
For Editing and Creating Documents
Step 1: Open the Document
Open the document you want to edit or create in the respective WPS Office component (Writer, Spreadsheets, or Presentation).
Step 2: Initiate Collaboration
Click on the "Collaboration" or "Share" button. This button is typically located in the upper-right corner of the interface.
Step 3: Share the Document
Choose how you want to share the document:
Generate Share Link: You can create a shareable link that others can access. It is useful for sharing the document with users both inside and outside your organisation.
Invite Collaborators: You can invite specific collaborators by entering their email addresses. This is particularly useful for collaborating within your organisation.
Step 4: Set Permissions
Depending on the collaboration method, you can set permissions for your collaborators. You can define whether they can view, comment, or edit the document.
Step 5: Collaborate in Real-Time
Once you've shared the document, your collaborators can access it via the link or invitation. They can edit the document in real time, and their changes will be visible to all collaborators.
Step 6: Review and Accept Changes
As collaborators make changes, you can review and accept or reject those changes. Most collaboration features offer tools to track changes and manage version history.
Step 7: Save and Exit
After completing your collaborative editing session, make sure to save the document. Your changes will be synchronised with the document in real time.
FAQs
How do I remove a formula and keep only values in Excel?
To remove a formula and keep only the values in Excel, you can follow these steps:
Select the cells containing the formulas that you want to replace with values.
Right-click on the selected cells and choose "Copy" from the context menu, or press Ctrl + C to copy the cells.
Right-click on the same range of cells again, and choose "Paste Special" from the context menu. Alternatively, you can go to the "Home" tab in the Excel ribbon, click on the "Paste" dropdown arrow, and select "Paste Special."
In the "Paste Special" dialogue box that appears, you have a few options:
Choose "Values" under the "Paste" section to paste only the values.
You can also choose other options like "Values and Number Formats" or "Values and Source Formatting", depending on your needs.
Click the "OK" button to replace the formulas with their corresponding values.
How To Convert Formula to Text String In Excel?
You can use the TEXT function to convert a formula to a text string in Excel. The TEXT function allows you to format a value as text using a specified format code. Here's how you can use it:
Suppose you have a formula in cell A1 that you want to convert to a text string.
In another cell (let's say B1), enter the following formula:
=TEXT(A1, "General")
Replace "General" with the appropriate format code to specify a specific format.
Press Enter. Cell B1 will now display the formula from cell A1 as a text string.
Keep in mind that the TEXT function may not work for all formulas, especially complex ones. *Testing this method with your specific formulas is important to ensure that it accurately converts them to text strings.
Suppose you want to remove the formula from a cell and replace it with its current value as text. In that case, you can use the "Copy" and "Paste Special" method mentioned earlier, but choose "Values" in the "Paste Special" dialogue box to paste the values as text. This approach is suitable when you don't need to retain the formula.
Summary
The article addresses Excel users' common issue of displaying formulas instead of results, citing reasons like the "Show Formulas" feature activation and formula formatting errors. It offers step-by-step solutions to overcome the problem. The article also spotlights WPS Office, an all-encompassing suite featuring Writer, Spreadsheets, and Presentation tools for seamless collaborative document editing. The suite's compatibility, lightweight nature, and integrated supplementary tools, such as resume assistants and screen recording, are emphasized, making it a comprehensive and efficient office solution.
