Looking for solutions to Excel array formula not showing results? 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”.
Excel Array Formula Not Showing Result: 4 Solutions
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.
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
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.
- Now, we will fix this issue.
- Firstly, from the Formulas tab, deselect Show Formulas.
- Therefore, we can see that the Excel array formula will show the result.
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.
- After that, press ENTER.
- Doing so, we will the #SPILL error and our Excel array formula is not showing the result.
- 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.
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.
- 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.
- Thus, the Excel array formula will return the desired output.
Read More: [Fixed!] Formula Not Working in Excel and Showing as Text
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
- 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.
- To fix this, activate cell E5 by double-clicking on it.
- Then, press ENTER.
- Thus, it will show the result as expected.
Read More: [Solved:] Excel Formula Not Working unless Double Click Cell
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.
Download Practice Workbook
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. Thanks for reading, keep excelling!
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.