Skip to main content
Super User

Return to Answer

added 913 characters in body
Source Link
phuclv
phuclv
  • 28.4k
  • 15
  • 128
  • 252

However you don't even need to fill the formula to all cells. Using a multi-result array formulaarray formula would be better. Just enter the formula like normal but replaces the cells by the range (e.g. B1 with B1:B10000. That's a bit oversimplifiedsomewhat an oversimplification but that's the idea and it works for most simple cases, read below for more details) and then press Ctrl+Shift+Enter. The formula will then be applied immediately to all the cells in the table, very few keystrokes needed

#Update:

Newer Excel versions will automatically use array formulas to fill down when there's a new data row so you don't need to do that manually

Beginning with the September 2018 update for Office 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. 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. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas.

Guidelines and examples of array formulas

Array formulas have many advantages:

#Why use array formulas?

 

However you don't even need to fill the formula to all cells. Using a multi-result array formula would be better. Just enter the formula like normal but replaces the cells by the range (e.g. B1 with B1:B10000. That's a bit oversimplified but that's the idea, read below for more details) and then press Ctrl+Shift+Enter. The formula will then be applied immediately to all the cells in the table, very few keystrokes needed

#Why use array formulas?

However you don't even need to fill the formula to all cells. Using a multi-result array formula would be better. Just enter the formula like normal but replaces the cells by the range (e.g. B1 with B1:B10000. That's somewhat an oversimplification but that's the idea and it works for most simple cases, read below for more details) and then press Ctrl+Shift+Enter. The formula will then be applied immediately to all the cells in the table, very few keystrokes needed

#Update:

Newer Excel versions will automatically use array formulas to fill down when there's a new data row so you don't need to do that manually

Beginning with the September 2018 update for Office 365, any formula that can return multiple results will automatically spill them either down, or across into neighboring cells. This change in behavior is also accompanied by several new dynamic array functions. 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. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas.

Guidelines and examples of array formulas

Array formulas have many advantages:

#Why use array formulas?

 
added 430 characters in body
Source Link
phuclv
phuclv
  • 28.4k
  • 15
  • 128
  • 252
Improved Formatting
Source Link
phuclv
phuclv
  • 28.4k
  • 15
  • 128
  • 252

YouDuplicates:

However you don't even need to fill the formula to all cells. Using a multi-result array formula would be better and produces a smaller file. It's also faster since the access pattern is already known. Now instead of doing 11 different calculations separately, they can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU. Just enter the formula like normal but replaces the cells by the range (e.g. B1 with B1:B10000. That's a bit oversimplified but that's the idea, read below for more details) and pressthen Ctrl+Shift+Enterpress Ctrl+Shift+Enter. The formula will then be applied immediately to all the cells in the table, very few keystrokes needed

Array formula is also faster since the access pattern is already known. Now instead of doing 11 different calculations separately, they can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU

For example if you want cells in column D to contain the product of cells in B and C, and column E contains sum of B and C, instead of using D1 = B1*C1 and E1 = B1 + C1 and drag down you just put the below formulas in D1 and E1 respectively and press Ctrl+Shift+Enter

For more information read Create an array formula

That said, it's also possible to fill the cells manually with the keyboard

You don't need to fill the formula to all cells. Using a multi-result array formula would be better and produces a smaller file. It's also faster since the access pattern is already known. Now instead of doing 11 different calculations separately, they can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU. Just enter the formula and press Ctrl+Shift+Enter

For example if you want cells in column D to contain the product of cells in B and C, and column E contains sum of B and C, instead of using D1 = B1*C1 and E1 = B1 + C1 and drag down you just put the below formulas in D1 and E1 respectively and press Ctrl+Shift+Enter

For more information read Create an array formula

That said, it's also possible to fill the cells manually with the keyboard

Duplicates:

However you don't even need to fill the formula to all cells. Using a multi-result array formula would be better. Just enter the formula like normal but replaces the cells by the range (e.g. B1 with B1:B10000. That's a bit oversimplified but that's the idea, read below for more details) and then press Ctrl+Shift+Enter. The formula will then be applied immediately to all the cells in the table, very few keystrokes needed

Array formula is also faster since the access pattern is already known. Now instead of doing 11 different calculations separately, they can be vectorized and done in parallel, utilizing multiple cores and SIMD unit in the CPU

For example if you want cells in column D to contain the product of cells in B and C, and column E contains sum of B and C, instead of using D1 = B1*C1 and E1 = B1 + C1 and drag down you just put the below formulas in D1 and E1 respectively and press Ctrl+Shift+Enter

For more information read Create an array formula

Source Link
phuclv
phuclv
  • 28.4k
  • 15
  • 128
  • 252