I'm using a template called "sales invoice tracker" in Excel 2016. The following is the problem going on here.

Screenshot from Excel

As you can see the text is either not showing at all or showing the formula.

The formula it uses is as follows:

=IFERROR(INDEX(InvoiceDetails,SMALL(IF(InvoiceDetails[Invoice '#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(1:1)), MATCH($C$8, InvoiceDetails[#Headers], 0)),"")

Here is a view of the "details" sheet:

View of the "details" sheet

Video of what happens in Excel:

Click here for the video

  • Have you turned on Show Formulas by accident? Check the Formula tab in the Formula Auditing group.
    – CharlieRB
    Feb 17, 2017 at 18:41
  • @CharlieRB Nope, that's disabled. Also, when I try to change a formula of an existing text and press enter, it disappears while the formula didn't change and the text was showing before
    – Chris
    Feb 17, 2017 at 18:45
  • Updated the post with a video showing what happens in Excel
    – Chris
    Feb 17, 2017 at 18:53

2 Answers 2


"Show formulas in cells instead of calculated results" is probably checked.

To uncheck it...

Go to File > Options > Advanced
Scroll about half way down until you get to the heading "Display options for this workbook"
Uncheck "Show formulas in cells instead of calculated results"


Other things can that cause this problem:

If the cell that the formula was entered into was formatted as "Text" when the formula was written, it will generally appear as text, even if you reformat the cell to "General" or "Number".
To solve this, reformat the cell to "Number", click on it, and press "Return".

  • This option is actually unchecked. But thanks for helping!
    – Chris
    Feb 17, 2017 at 18:51
  • Unfortunately no change there. I also just noticed that this problem also occurs when I open a new template.
    – Chris
    Feb 17, 2017 at 19:06

The problem you are facing is because the formulas used in this template are "Array formulas". If you select one of the cells that are working properly, you should see that the formula is in {} brackets. To restore these formulas to their proper function, edit the cell again.
Instead of pressing enter, you press CTRL+SHIFT+ENTER

In the cells that are merged however, this will most likely throw an error: "Array formulas are not valid in merged cells" In this case, you can un-merge the cells first, apply the formula and re-merge the cells again.
However, the Beschrijving column may still result in only showing the formula, despite you pressing CTRL+SHIFT+ENTER, at least for me it did. I solved this by changing the formatting options of the cell to "General".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.