Do you find that referring to cell ranges in Excel formulas is a difficult task? Confused by the use of the colons, commas and spaces?

These characters are definitely a part of formula writing and if they aren’t used correctly, you’ll probably run into some problems.

So, here’s the information that can make formula writing with ranges a much more manageable task.

• A colon ( : ) names a single range. A1:C2 names the range of cells from A1 to C2. Like this:

  

• A comma will result in the union of two ranges. (A union will be all cells in both ranges named). A1:C2, B1:B4 will result in this:

  

  I should note here that when I did a sum formula using the union it used any overlapping cell values twice – once for each range they’re in. So as a friendly warning, be careful and double check that it’s doing what you intend it to do.

• Finally, a space will result in the intersection of the ranges. (An intersection will include only the cells where the named ranges overlap). A1:C2 B1:B4 will result in this:

  

I’ve shown you the results of the identified ranges as highlighted cells, but obviously, when you actually go to use them in formulas, it’s the values within those cells that are put to work.

Now, go forward and confidently use ranges in your formulas to your heart’s content!

~ April