I have some data in an Excel sheet.

Is there a way to write a formula that's only in cell B1 (cells B2:B7 are blank), that will duplicate the values in A1:A7 to the range B1:B7, using Excel's "spilling" feature for functions that return arrays?

My first idea was to use "=A1:A7", but that just copies the value from the formula's row in column A to the cell with the formula, without affecting the other cells.

CodePudding user response：

Use

=A1:A7

You will need to format the resulting cells as you want. Formula will not copy formatting and will not replace the existing formatting in the cells.

If your version does not have the dynamic array spill capabilities (Excel 2019 and older). Then one will need to array enter the formula or just drag a formula down.

To array enter we select a range the same size and shape as the target and put the above formula in the formula bar:

Then we confirm the formula with Ctrl-Shift-Enter and Excel will fill the formula down the range:

But this can also be done by putting =A1 in B1 and dragging it down. Older version require a formula in each cell that one expects an output. There is no "Spill" feature in older versions.