Excel displays formula rather than result

Sometimes a bug in Excel results in the application displaying the text of a formula rather than the result of the formula in the spreadsheet. I have not been able to find a pattern of when it does it, but I have some spreadsheets that do this consistently.

The Fix

To get Excel to properly display the result:

• Select the cell.
• Format the cell as "General". (Right-click the cell, select Format Cells, and choose "General.")
• Delete the "=" at the beginning of your formula, and hit Enter.
• Insert the "=" back in the formula at the beginning.

That is it. You should now see the result of the calculation in the cell rather than the text of the formula.

How To Extract Formulas From Excel At Once

Sometimes you may need to convert formula to text string in Excel, such as =A1+B1, follow the below mentioned steps for extracting the formulas from Excel at once.

Example

For Example, we need to show the Column C with original formulas but not their calculated results in Column D, see the following screen shot.

Steps

1. select Column C.
2. Under the Data menu tab, in the Data Tools group, click the Text to Columns button.
3. In Convert Text to Columns Wizard window, do the following:
   1. In Convert Text to Columns Wizard - step 1 of 3 window, click Next.
   2. In Convert Text to Columns Wizard - step 2 of 3 window, click Next.
   3. In Convert Text to Columns Wizard - step 3 of 3 window, under Column data format, choose Text, in Destination box, enter a cell reference: D1, or click Collapse Button  to temporarily hide the dialog box, select cell D1 on the worksheet, and then press Expand Button .
   4. click Finish.

Final Results

Video

Find Cells containing Formulas in Excel

Method 1: Using ‘Go To Special’ Option:

1. With your excel sheet opened navigate to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Alternatively, you can also press ‘F5’ and then ‘Alt + S’ to open the ‘Go to Special’ dialog.

2. Next, in the ‘Go to Special’ window select the ‘Formulas’ radio button. After checking this radio button you will notice that few checkboxes (like: Numbers, Errors, Logicals and Text) are enabled, these checkboxes signify the return type of the formulas.

So, if you select ‘Formulas’ radio button and only check the ‘Numbers’ checkbox then it will just search the Formulas whose return type is a number. Here in our example we will keep all of these return types checked.

3. After this click the ‘Ok’ button and all the cells that contain formulas get selected.

4. Next, without clicking anywhere on your spreadsheet change the background colour of all the selected cells.
5. Now your formula cells can be easily identified.

Method 2: Using a built-in Excel formula:

Excel has a formula that can find whether a cell contains a formula or not.

=ISFORMULA(reference)

Here ‘reference’ signifies the cell position which you wish to check for the presence of a formula.

For example: If you wish to check the cell ‘A2’ for the existence of a formula then you can use this function as =ISFORMULA(A2)

This function results into a Boolean output i.e. True or False. True signifies that the cell contains a formulas while False tells that cell doesn’t contain any formulas.

Method 3: Using a Macro for identifying the cells that contain formulas:

I have created a VBA Macro that can find and colour any cells that contain formula in the total used range of the Active sheet. To use this macro simply follow the below procedure:

1. Open your spreadsheet and hit the ‘Alt + F11’ keys to open the VBA editor.

2. Next, navigate to ‘Insert‘ > ‘Module‘ and then paste the below macro in the editor.

view plaincopy to clipboardprint?

1. Sub FindFormulaCells()  
2. For Each cl In ActiveSheet.UsedRange  
3. If cl.HasFormula() = True Then  
4. cl.Interior.ColorIndex = 24  
5. End If  
6. Next cl  
7. End Sub  

3. For running this formula press “F5” key.

4. This Macro will change the background colour of all the formula containing cells and thus makes it easier to identify them easily.