How to Solve the “Excel Array Formula Not Showing Results” Problem (4 Solutions)

Dataset Overview

To illustrate our solutions, we’ll work with a dataset containing four columns: Car, Unit Sold, Unit Price, and Revenue. Our goal is to apply an array formula to determine the revenues for each type of car sold by a specific company.

Excel Array Formula not Showing Result 1

Solution 1 – Turning Off Show Formulas Command in Excel

  • If the Show Formulas command is enabled, the sheet displays formulas instead of results.
  • To address this:
    • In cell E5, enter the following formula:


Excel Array Formula not Showing Result 2

Formula Breakdown

  • This array formula multiplies corresponding cells from columns C and D, yielding the value in column E.
  • For instance, E5 = C5 * D5, E6 = C6 * D6, and so on.
  • Press ENTER.
  • Instead of showing the Excel array formula result, it will show us the array formula.

Excel Array Formula not Showing Result 3

  • If the array formula still shows as a formula, deselect Show Formulas from the Formulas tab.

Excel Array Formula not Showing Result 4

  • The Excel array formula will show the result.

Excel Array Formula not Showing Result 5

Solution 2 – Solving #SPILL! Error in Excel Array Formula to Show Result

  • In Excel 365, array formulas dynamically populate cells when you press ENTER.
  • If an existing value interferes, you’ll encounter the #SPILL error.
  • To fix this:
    • Enter the formula in cell E5:


Excel Array Formula not Showing Result 6

  • Notice any existing value (e.g., “s”) in cell E6.

Excel Array Formula not Showing Result 7

  • Delete that value and press ENTER.
  • So, our array formula will show the result.

Excel Array Formula not Showing Result 8

Solution 3 – Changing Cell Format to Fix Array Formula Display

  • If a cell is formatted as Text, it shows the formula rather than the result.
  • Rectify the Number Format:
    • Enter the formula in cell E5:


  • Press ENTER.

Excel Array Formula not Showing Result 9

  • If the formula remains visible, change the cell format (e.g., select Currency format).
  • Active the formula by double-clicking on cell E5.
  • Press ENTER.

Excel Array Formula not Showing Result 10

  • The Excel array formula will return the desired output.

Sample Dataset 1

Read More: [Fixed!] Formula Not Working in Excel and Showing as Text

Solution 4 – Showing Result by Pressing Only ENTER in Excel Array Formula

  • In Excel 365, use ENTER (not CTRL+SHIFT+ENTER) to calculate array formulas.
  • To fix this:
    • Enter the formula in cell E5:


Sample Dataset 2

  • Instead of CTRL+SHIFT+ENTER, press ENTER.

Sample Dataset 3

  • Activate cell E5 by double-clicking on it.
  • Press ENTER.
  • The result will display as expected.

Sample Dataset 4

Read More: [Solved:] Excel Formula Not Working unless Double Click Cell

Things to Remember

  • These solutions apply to Excel 365. For older versions (pre-Excel 2019), use CTRL+SHIFT+ENTER for array formulas (except for functions like AGGREGATE and SUMPRODUCT).

Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Practice Dataset

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

  1. Hi. I was using the “mode.mult” function but for set of numbers with more than 1 mode, the function shows only 1 mode instead of 2 or more. I tried using the “ctrl+shift+enter” but it still only shows 1 mode. How do I fix this? Thank you.

    • Hello Cielo. Thank you for your question. I have tested the function on Excel 365, 2021, and 2013 versions. On first two version, you simply press ENTER and the formula will be spilled. However, in Excel 2013 if you press that, it will only show the first value.

      Here, we have typed the formula in cell C5.

      Then, pressed CTRL+SHIFT+ENTER. But, only the first value is seen.

      To solve this, you need to follow these steps:
      1. Select the cell ranges C5:C8 first.

      2. Type the formula =MODE.MULT(B5:B11)

      3. Press CTRL+SHIFT+ENTER. So, it will show all the mode values. Additionally, we have selected four cells, but there are only three outputs. Therefore, there is a “#N/A” for that reason.

      Solution to the Issue: MODE.MULT Function Not Working

Leave a reply

Advanced Excel Exercises with Solutions PDF