Array formulas

Resolved · High Priority · Version 2010

Mon 8 May 2017, 16:56 Delegate Matthew said...

Matthew has attended:
Excel Advanced - Formulas & Functions course

Array formulas

If I have a complicated array formula that I would like to extend, because the number of rows with data has increased, what is the best way of extending the array to include all of the rows. I cannot just drag down as the output from the array formula depends on all previous data?

Tue 9 May 2017, 13:27 Trainer Marius said...

RE: Array formulas

Hi Matthew,

Thank you for your question. One way to extend your array to include new records is to select the existing array formula cells plus the empty cells next to the new data. Then, press F2 to adjust the cell references in the formula to include the new records. Finally, press Ctrl+Shift+Enter to refresh the array.

I hope this helps.

Kind regards
Marius Barnard
Excel Trainer

Tue 16 May 2017: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

No Zeros

Want all the zeros in your worksheet to appear blank? Choose Tools/Options, Click on the View Tab, Deselect the Zero Values Option, Click OK.
Easy wasn't it? But be aware that these cells are not actually blank, they still contain the value zero. This is important because certain functions (ie AVERAGE) make a distinction between blank cells and those with a zero value.

View all Excel hints and tips