This is part of a series on enhancing your Excel skills. You’ll find links to other stories below.

Most people think of a formula in Excel as producing a single result for a specific spreadsheet cell.

But with Excel’s dynamic arrays feature, one formula can populate multiple cells—each with a unique calculation—making it a snap to build large workbooks while minimizing errors. You can use these magical formulas anywhere you need to repeat a calculation across an array of data.

Check out these two examples of how you can save time and effort using dynamic arrays.

Your goal: Create an addition table and answer key like the one shown below to help your child learn math (and to help you check their answers).

The old way: After entering the values to be added together in the first row and first column, you’d create a formula like =C4+B5 in the first table cell, then copy and paste that across the first row, then copy and paste that row into the ones below. Lots of copying and pasting!

The dynamic way: Create a single formula that references the entire range of cells and calculates every value in one fell swoop.

How to do it: Using the table below as an example, enter in the first answer cell =C4:J4+B5:B18, where C4:J4 represents the range of cells in the first row and B5:B18 represents those in the first column. Press Enter and the entire table populates!

Your goal: Take the winners of a weekly sales contest and figure out the total sales for each person—and update those totals as new winners are added.

The old way: You could use the =SUMIF() function to summarize sales by person, but since new winners are added weekly, expanding the cell range, you’ll need to manually adjust the formula each time.

The dynamic way: Use a dynamic array that automatically updates the range whenever a new weekly winner is added.

How to do it: The table below shows the weekly winners so far. To get a list of salespeople and omit duplicate entries, use the new UNIQUE() function: In an empty area of the workbook, enter the formula =UNIQUE(B3:B12), where B3:B12 is the current list of winners:

To get the total sales for each person, you’ll use a special version of the =SUMIF() function: In the column next to Murat’s name, enter the formula =SUMIF(B3:B12,E3#,C3:C12), where B3:B12 is the current list of winners and C3:C12 is the current list of weekly sales.

The number sign (#) is the magical bit here: It tells Excel to reference the entire range of the dynamic array—in other words, to account for any new rows. Press enter and Excel summarizes the sales for each person:

Now when you add Alyssa as the winner of week 11, that data is added to the summary list automatically:

Excel offers many ways to use dynamic arrays, including with the FILTER(), SORT(), SORTBY(), SEQUENCE(), and RANDARRAY() functions. By requiring you to use each of these only once to fill an endless number of cells, dynamic arrays can be a huge time-saver.