Excel formula not working

This article explains why your formula is not working properly, there are usually four different things that can go wrong.

1. Formula not working - check the function arguments

This example shows a function in cell B2 that returns a #VALUE! error, this may happen when there is something wrong with the arguments.

The SUBTOTAL function allows you to use a number between 1 and 11, the first argument is in this example 12 which is an invalid argument.

How to quickly show function arguments?

Type the function in a cell, and then the beginning parentheses, Excel instantly shows the arguments and in some cases, allowed values.

Here is a list of Excel functions and their arguments: Excel functions

Here is a list of common errors: Excel formula errors

Back to top

2. How to solve "numbers stored as text"?

The first one is numbers stored as text, demonstrated in the picture above.

Why is the SUM function in cell C7 returning 0 (zero) in the picture above?
The numbers in cell range C3:C6 are stored as text. You can see that by the green arrow in each cell's top left corner.

Why is this happening?
The cell range C3:C6 was formatted as text cells before you entered the numbers in these cells.

How to solve it?
Select cell range C3:C6, and press with left mouse button on the exclamation mark symbol (or exclamation point if you are American) to open a menu.
Press with mouse on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.

Back to top

3. How to change the "manual calculation" setting

The SUM formula in cell C7 returns 0 (zero), why is this happening?

Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button.

In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.

Back to top

4. The cell shows the formula and not the result - here is how to fix it

Cell C7 displays the formula, not the result. Why is this happening?

Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).

Press with mouse on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation. If this is not working, read section 5 below.

Back to top

5. Formula cell formatted as text

The cell in C7 shows the formula? I don't want that, I want to see the calculation result.

Select cell C7, press CTRL + 1 on your keyboard.

This reveals that the cell is formatted as "Text" making it show the formula and not the result. Press with mouse on "General" and then on the OK button.

Now it shows the output from the formula.

Back to top

6. Formula not working - #NAME? error

The #NAME? error hints that you misspelled the function name.

Tip! Check the suggestions while typing the formula. Use the arrow up and down keys to change the selected suggestion. Use the TAB key to auto-complete the selected suggestion.

Back to top

7. How to troubleshoot a formula not working as intended?

The image above shows a formula in cell C6 that returns no error, however, the calculation is wrong. How do we troubleshoot this formula to find what is wrong in the calculation?

The "Evaluate formula" tool lets us see the calculation steps in greater detail. Here is how to start the tool:

1. Select the cell you want to troubleshoot, in this example cell C6.
2. Go to the tab "Formulas" on the ribbon.
3. Press with left mouse button on the "Evaluate Formula" button located on the ribbon.
   
4. A dialog box appears.

The dialog box shows the formula in the white box and has four buttons below. The underlined expression shows what part of the formula will be calculated in the next step.

The italicized text is the most recent result, press with left mouse button on the "Evaluate" button to start the formula evaluation.

Keep press with left mouse button oning on the "Evaluate" button until greyed out to see all steps in the calculation, this helps to identify where the problem is.

The tool shows that the formula multiples values in cells C2 and C3 before adding the number in cell C4. We need to add C2 with C4 and then multiply with C3.

Press with left mouse button on the "Close" button to dismiss the dialog box.

The parentheses allow us to control the order of operation. Here is the final formula:

Back to top

8. Count text string in all formulas in a worksheet - VBA

I will in this article demonstrate a macro that counts how many times a specific text string is found in cells containing formulas.

Press with left mouse button on button named "Start" to run the macro named "CountTextInFormulas". The macro will use the value in cell C2 to count how many times that particular string is found in a worksheet. The result is shown in cell c3.

In the example above, the macro counts how many times the text string "COUNTIF(" is found on the current worksheet. 18 is the result. There are 18 formulas in cell range F7:F24 containing text string "COUNTIF(".

Note that this macro counts all matching text string in a cell, in other words, if the text string is found three times in one cell the macro returns 3 for that cell and then returns the total to cell C3.

I will also in this article demonstrate how to

• create a button and place it on a worksheet.
• assign a macro to a button.
• where to put the VBA code.
• how to save your workbook as a macro-enabled workbook.
• explain how the macro works.

VBA Code

I wanted to quickly select all cells containing formulas on a sheet. I found Rick Rothstein (MVP - Excel) macro here and modified it.

'Name macro
Sub CountTextInFormulas()

'Dimension variables and declare data types
Dim Rng As Range, C As Range, Cnt As Double, Scnt As Double

'Enable error handling
On Error Resume Next

'Save a reference to cells containing formulas to range object named Rng
Set Rng = Cells.SpecialCells(xlFormulas)

'Disable error handling
On Error GoTo 0

'Check if Rng is not empty
If Not (Rng Is Nothing) Then

   'Iterate through each cell in range object Rng
   For Each C In Rng

      'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
      With Worksheets("Sheet1")

          'Calculate how many times the text strign is found in a given cell
          Cnt = (Len(C.Formula) - Len(Replace(C.Formula, .Range("C2"), ""))) / Len(.Range("C2"))

       End With

       'Add number to count variable
       Scnt = Scnt + Cnt

   'Continue with next cell
   Next
    
End If

'Return number to cell C3 on worksheet Sheet1
Worksheets("Sheet1").Range("C3").Value = Scnt
End Sub

Where to put the code?

1. Press shortcut keys Alt + F11 to open the Visual Basic Editor.
2. Press with left mouse button on "Insert" on the menu.
3. Press with left mouse button on "Module".
4. Paste code to module window.
5. Exit VB Editor and return to Excel.

Create button

1. Go to tab "Developer" on the ribbon. You have to enable the "Developer" tab if it is missing.
2. Press with left mouse button on "Insert Controls" button.
3. Insert a button (Form Controls) on the sheet near cell C2.
4. Select a macro to assign a macro to your button.
5. Press with left mouse button on OK to close the dialog box.

Edit text in button

1. Press with right mouse button on on button.
2. Press with mouse on "Edit text"
3. A prompt appears inside the button. Clear the text and then type what you want it to say.
4. Press with left mouse button on somewhere outside the button to save the text you wrote.

Change button size

 

It is hard to select the button if a macro is assigned to it. Use the right mouse button to select without triggering the macro. The image above shows sizing handles around the button.

1. Press with right mouse button on on the button to select it. You know it is selected when the sizing handles appear.
2. Press and hold with the left mouse button on any of the size handles.
3. Drag with mouse to change the size.
4. Release the mouse button when you are happy with the button size.

You can use the SHIFT key to resize the button keeping the relationship between width and height. This works only if you press with left mouse button on and drag the corner handles.

The Alt key lets you snap to the cell grid while resizing the button, the animated image above shows this.

Change button location

1. Select the button.
2. Press and hold with left mouse button on the button.
3. Drag with mouse to the desired location.

You can press and hold the SHIFT key to move the button vertically or horizontally using the mouse.

The Alt key lets you snap the button to the cell grid beneath.

Sum category

More than 1300 Excel formulas

Excel categories

Leave a Comment