Have you ever tried to calculate a lot of cells, and found yourself entering the same formula over and over again? Or your formula gets really long? You can save time by using an array formula. You enter the formula once, and it fills as many rows or columns as you want.
Here's how:

Select the cells where you want to see your results.

Enter your formula.

Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.
You use standard formula syntax here. Start with an equal sign (=) and use any of Excel's builtin functions. For example, this formula calculates the total value of an array of stock prices and shares, and places the result in the cell next to "Total Value."
The formula first multiplies the shares (cells B2 – I2) by their prices (cells B3 – I3), and then adds those results to create a grand total of 53,184. This is an example of a singlecell array formula because the formula lives in just one cell.
You can also create multicell array formulas. You typically do this when rows or columns of data need a subtotal or other calculation. The same formula lives in a set of cells in a column or row.

Select the range of cells in which you want to enter the array formula. Always do this first.

Enter your formula in the formula bar.

Press Ctrl+Shift+Enter.
Here's an example.
The formula multiples shares by price in each column, and the formula lives in the selected cells in row 5.
Also, Excel surrounds the formula with braces ({ }). It does that automatically, and you can't enter them yourself. If you do, the formula won't work because Excel treats the braces as text, and it can't calculate text. So make sure you press Ctrl+Shift+Enter.
Notes: If you need to include new data in your array formula, see Expand an array formula. You can also try:

Rules for changing array formulas (they can be finicky)

Delete an array formula (you press Ctrl+Shift+Enter there, too)

Use array constants in array formulas (they can be handy)

Name an array constant (they can make constants easier to use)
Give it a try
If you want to play around with array constants before you try them out with your own data, you can use the sample data here.
The workbook below shows examples of array formulas. To best work with the examples, you should download the workbook to your computer by clicking the Excel icon in the lowerright corner, and then open it in the Excel desktop program.
Copy the table below and paste it into Excel in cell A1. Be sure to select cells E2:E11, enter the formula =C2:C11*D2:D11, and then press Ctrl+Shift+Enter to make it an array formula.
Salesperson 
Car Type 
Number Sold 
Unit Price 
Total Sales 
Barnhill 
Sedan 
5 
2200 
=C2:C11*D2:D11 
Coupe 
4 
1800 

Ingle 
Sedan 
6 
2300 

Coupe 
8 
1700 

Jordan 
Sedan 
3 
2000 

Coupe 
1 
1600 

Pica 
Sedan 
9 
2150 

Coupe 
5 
1950 

Sanchez 
Sedan 
6 
2250 

Coupe 
8 
2000 
Create a multicell array formula

In the sample workbook, select cells E2 through E11. These cells will contain your results.
You always select the cell or cells that will contain your results before you enter the formula.
And by always, we mean 100percent of the time.

Enter this formula. To enter it in a cell, just start typing (press the equal sign) and the formula appears in the last cell you selected. You can also enter the formula in the formula bar:
=C2:C11*D2:D11

Press Ctrl+Shift+Enter.
Create a singlecell array formula

In the sample workbook, click cell B13.

Enter this formula using either method from step 2 above:
=SUM(C2:C11*D2:D11)

Press Ctrl+Shift+Enter.
The formula multiplies the values in the cell ranges C2:C11 and D2:D11, then adds the results to calculate a grand total.