In Excel, an array formula performs more intricate and powerful calculations than Excel’s basic formulas. Creating arrays in Excel will allow you to calculate numbers based on your set of conditions, along with large calculations for a wide range of cells.
Download the creating arrays example to follow along.
In our example, you will learn to use array formulas with a generic car sales report.
Using the Multi-Cell Array Formula in Excel
To begin, you must multiply the values of the array in the cell ranges of C2 through D13. This is the number of cars sold multiplied by the unit price according to each row. When using multi-cell arrays, you are able to write the formula in one place and have it fill in the cells you choose.
Choose cells E2 through E13 and then type this in the formula bar:
=C2:C13*D2:D13
CTRL + SHIFT + ENTER will activate this formula.
Using this combination of keys lets Excel know that you are specifically inputting an array formula. The formula will automatically have curly braces generated around it in the formula bar as a result to indicate the array status.
Once this has been done, all cells will display the correct answer according to the array formula.
Using a Single Cell Array Formula in Excel
This process will calculate the total amount of sales for this example. Choose cell B14 to enter the formula. This will add the amount of products in the “number sold” and the “unit price” columns.
=SUM(C2:C13*D2:D13)
Again, press CTRL + SHIFT + ENTER to input the formula. The answer will be generated and your single cell array formula will be finished.
Related Templates:
- Excel Cell References
- Math Formulas in Excel
- CTRL + Shift Selections
- How to Combine Two Names in Excel
- Remove Spaces Between Words in Excel
View this offer while you wait!