You could change the other formulas in column H without affecting the formula in H20. This is another advantage of using array formulas - flexibility.

Also, notice that the single-cell formula in cell H20 is completely independent of the multi-cell formula (the formula in cells H10 through H19). You can sum part or all of that data by creating an array formula in a single cell instead of dragging the formula down through the 1,000 rows. For example, suppose you have 1,000 rows of data. This example shows how powerful this type of formula can be. The result is a grand total of $1,590,000 in sales. In this case, Excel multiplies the values in the array (the cell range F10 through G19), and then uses the SUM function to add the totals together. In cell H20 of the example workbook, type or copy and paste =SUM(F10:F19*G10:G19), and then press Enter. They’re just there for reference, so if you want to adjust the formula, you’ll need to select cell H10, where the master formula lives. You might also notice that the formulas in cells H10:H19 are grayed out. Notice that the spill range is highlighted with a border when you select any cell within the spill range. When you press Enter, you'll see the results spill down to cells H10:H19. Here we're calculating Total Sales of coupes and sedans for each salesperson by entering =F10:F19*G10:G19 in cell H10. The second set uses a single-cell formula to calculate a grand total. The first set of steps uses a multi-cell formula to calculate a set of subtotals. This exercise shows you how to use multi-cell and single-cell array formulas to calculate a set of sales figures. Download our examplesĭownload an example workbook with all the array formula examples in this article. Where possible, we’ve included examples with some of the dynamic array functions, as well as existing array formulas entered as both dynamic and legacy arrays. The following examples show you how to create multi-cell and single-cell array formulas. Sum every Nth value in a range of values. Sum only numbers that meet certain conditions, such as the lowest values in a range, or numbers that fall between an upper and lower boundary. You can use array formulas to perform complex tasks, such as:Ĭount the number of characters contained in a range of cells. They’re commonly referred to as CSE formulas.

Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. Dynamic array formulas, whether they’re using existing functions or the dynamic array functions, only need to be input into a single cell, then confirmed by pressing Enter. This change in behavior is also accompanied by several new dynamic array functions. Array formulas can return either multiple results, or a single result.īeginning with the September 2018 update for Microsoft 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. You can think of an array as a row or column of values, or a combination of rows and columns of values. LessĪn array formula is a formula that can perform multiple calculations on one or more items in an array. Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel for iPad Excel for iPhone Excel 2010 Excel 2007 Excel for Mac 2011 More.

Microsoft excel functions explained