Excel Video 354 has a practical example of an array formula. We’ll write a formula that loops through a range of cells to sum the collections if the year equals 2009. The array formula is made up of two parts that might be best understood from the inside out. The...

Excel Video 353 has our first trick with arrays. I’ll show you a quick way to get the name of range of cells you’ve named using Name Manager, and then we’re off to work. Sometimes this helps and sometimes this doesn’t help, but one of the features of an array is that...

If you have a horizontal array and need to use it with a vertical range, Excel Video 352 can make your life much easier. If you’ve defined an array across columns but need to use it down rows, you’ll see in Excel Video 352 that you won’t get the right answer. Rather...

If you need to make the named array constants we created in Excel Video 350 a little more flexible, watch Excel Video 351. Today we’ll define our array of constants as a range instead of entering the numbers 1 through 5 in the array. Now if we decide to change our...

Today we’ll simplify the formula we created in Excel Video 349 by naming an array in Excel Video 350. If you’re going to reuse an array with constants, an easy way to do it is to name the array. Watch how I have to put braces around the array in the formula and how I...

Excel Video 349 has a practical example of using constants in an array formula. The example is a weighted average of the past 5 years of revenues. Notice how much space on the Excel spreadsheet it takes to do the weighted average compared to what can be done in one...

Watch Excel Video 348 to see how to enter constants in array formulas. Constants are numbers, like 6 or 10, as opposed to cell references, like A4, that we’ve used so far. I’ll say this in the video, but there are much, much easier ways to fill a range of cells with...

Excel Video 347 offers several tips to manage your array formulas. It’s easy to delete or move an entire array formula, but you can’t delete or move part of an array formula. It’s all or nothing. You can expand an array formula to include more cells, but you can’t...

We’ve created an array formula in one cell. Excel Video 346 shows to create an array formula in multiple cells at the same time. It’s easy to create an array formula in multiple cells. Select the cells that you want to populate with the formula, enter the formula that...

Arrays can make you a real Excel power user. Watch Excel Video 345 to get started. An array is simply a collection of things, and an array formula can make multiple calculations at once on the contents of an array. The important thing to take away from this Excel...