Exceljet

Quick, clean, and to the point

Broadcasting

Excel formula broadcasting example

Broadcasting is an array calculation behavior in Excel formulas. It refers to a step during formula calculation where a smaller array is "filled" with duplicate values to create an array with the same dimensions as a larger array in the formula. In essence, Excel resizes arrays to be the same size.

In the example show, cells F5:F7 contain this multi-cell array formula:

=B5:B7*D5

The first range results in an array with 3 items. The second range results in an array with only 1 item. Broadcasting occurs when the second array is resized to contain 3 items, {2;2;2}, to match the dimensions of the first array {2;4;6}:

=B5:B7*D5 // starting formula
={2;4;6}*2 // initial evaluation
={2;4;6}*{2;2;2} // broadcasting
={4;8;12} // final result

Notice Excel simply repeats the value 2 during the operation. Broadcasting only occurs when the value to copy is unambiguous. When it's not clear what value to copy, Excel will use #N/A. For example, if we multiply {2;4;6;8}*{2;2}, the second array needs to be re-sized, but because it's not possible to know what values to use for third and forth elements, Excel fills with #N/A:

={2;4;6;8}*{2;2}
={2;4;6;8}*{2;2;#N/A;#N/A}
={4;8;#N/A;#N/A}

For more information on Excel's "array calculation behaviors", see this presentation by Joe McDaid on array formulas.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables