## 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.

### 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:

- In cell

`=C5:C10*D5:D10`

**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**.

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

- The
**Excel array formula**will**show**the**result**.

### 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**:

- Enter the formula in cell

`=C5:C10*D5:D10`

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

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

### 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**:

- Enter the formula in cell

`=C5:C10*D5:D10`

- Press
**ENTER**.

- 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**.

- The
**Excel array formula**will return the desired output.

**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**:

- Enter the formula in cell

`=C5:C10*D5:D10`

- Instead of
**CTRL+SHIFT+ENTER**, press**ENTER**.

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

**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.

**Download Practice Workbook**

You can download the practice workbook from here:

## Related Articles

- How to Refresh Formulas in Excel
- [Fixed!] SUM Formula Not Working in Excel
- [Fixed!]: Excel Formula Not Showing Correct Result
- Excel Formulas Not Calculating Automatically
- [Fixed!] Excel Formulas Not Working on Another Computer
- [Solved]: Excel Formulas Not Updating Until Save

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

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, and2013versions. On first two version, you simply pressENTERand the formula will be spilled. However, in Excel2013if you press that, it will only show the first value.Here, we have typed the formula in cell

.C5Then, 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:C8first.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.