[Solved]: Excel Array Formula Not Showing Result (4 Suitable Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Looking for solutions to Excel array formula not showing result? Then this guide is the right place for you. We are going to show you the top 4 reasons with solutions to the problem “Excel array formula not showing result”.


Download Practice Workbook


4 Suitable Solutions to Excel Array Formula Not Showing Result

To demonstrate our solutions, we have taken a dataset with 4 columns: “Car”, “Unit Sold”, “Unit Price”, and “Revenue”. We are going to apply an array formula to find the revenues for each type of car sold by a particular company.

Excel Array Formula not Showing Result 1


1. Turning Off Show Formulas Command in Excel to Solve Array Formula Not Showing Result

If the Show Formulas command is turned on, then we will see the formula instead of the result in our Sheet. Let us see what happens when we turn accidentally turn on the “Show Formulas” command in Excel.

Steps:

  • To begin with, type the following formula in cell E5.

=C5:C10*D5:D10

Excel Array Formula not Showing Result 2

Formula Breakdown

  • Firstly, this array formula multiplies a cell from the C and D columns and returns the value in column E.
  • In cell E5, we will get the multiplication value for cell C5 and cell D5. That means, E5 = C5*D5.
  • Similarly, E6 = C6*D6, E7 = C7*D7, and so on.
  • Thus, this array formula works.
  • Then, press ENTER.
  • After that, instead of showing the Excel array formula result, it will show us the array formula.

Excel Array Formula not Showing Result 3

  • Now, we will fix this issue.
  • Firstly, from the Formulas tab, deselect Show Formulas.

Excel Array Formula not Showing Result 4

  • Therefore, we can see that the Excel array formula will show the result.

Excel Array Formula not Showing Result 5


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

In Excel 365, the array formulas are dynamic. If we type the formula and hit ENTER, the formula will auto-populate the cells. Now, if there is an existing value in the way, then we will get the #SPILL error. To fix this, we need to remove that existing value from the cell.

Steps:

  • Firstly, type this formula in cell E5.

=C5:C10*D5:D10

  • Next, notice there is an existing value “s” in cell E6.

Excel Array Formula not Showing Result 6

  • After that, press ENTER.
  • Doing so, we will the #SPILL error and our Excel array formula is not showing the result.

Excel Array Formula not Showing Result 7

  • Now, to fix this, we just need to delete the value from cell E6.
  • Lastly, press ENTER.
  • So, our array formula will show the result.

Excel Array Formula not Showing Result 8


Similar Readings


3. Changing Cell Format in Excel to Fix Array Formula Not Showing Result

For the third reason, if our cell is formatted as Text then we will see the formula rather than the result. We need to rectify that Number Format to show the result of the array formula.

Steps:

  • First, type the formula in cell E5.

=C5:C10*D5:D10

  • Next, press ENTER.
  • Then, we will notice that the formula is not showing any result. Moreover, we are seeing the formula only.
  • Additionally, the Number Format is set to Text.

Excel Array Formula not Showing Result 9

  • Now, we need to change that Text format to any other format.
  • So, we have selected the Currency format from the dropdown list.
  • Then, active the formula by double-clicking on cell E5.
  • Lastly, press ENTER.

Excel Array Formula not Showing Result 10

  • Thus, the Excel array formula will return the desired output.

Sample Dataset 1

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


4. Showing Result by Pressing Only ENTER in Excel Array Formula

Microsoft has changed how array formulas work in the version of Excel 365. On older versions, we needed to press CTRL+SHIFT+ENTER to calculate an array formula. However, now we need to press just ENTER. Therefore, if anyone presses the previous Keyboard shortcut, the array formula may not work.

Steps:

  • To begin with, type the following formula in cell E5.

=C5:C10*D5:D10

Sample Dataset 2

  • Now, instead of pressing just ENTER, we have pressed CTRL+SHIFT+ENTER.
  • So, we will only see the first value, which means the C5*D5 part only from our array formula.

Sample Dataset 3

  • To fix this, activate cell E5 by double-clicking on it.
  • Then, press ENTER.
  • Thus, it will show the result as expected.

Sample Dataset 4


Things to Remember

  • This article is based on the Excel 365 version. Therefore, if you are using an older than Excel 2019 version, then you must press CTRL+SHIFT+ENTER to use the array formula. If you just press ENTER, then your array formula will not work except for some functions such as the 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


Conclusion

We have shown you the top 4 reasons with solutions to the issue of the Excel array formula not showing a result. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

2 Comments
  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

 

 

ExcelDemy
Logo