Excel sports a large number of worksheet functions that can be used to slice and dice data. One of the qualities that makes Excel so fascinating is the endless variety that these functions can be combined into formulas producing powerful, surprising, even elegant solutions. There is much to learn here and the user is easily bewildered, wondering which function or combination to work with to produce the desired results.

Just knowing where to start in a given situation requires experience. There's a science to it, but truly inspired solutions are as much art as science. Think of this series of articles as a treasure map, as I will attempt to lead you to the most productive paths.

Before we delve in, I need to backup. We must understand what a range is and how it is specified. A range is a group of one or more cells. The range is NOT the values in those cells. This distinction is important. Some functions (and hence formulas) return values. Some return ranges. Still others can return either in varying circumstances. The value is just one property of a cell. Other properties include color, type of border, font, etc. Typically just the value property is directly accessed from a formula, but many of these other properties can be manipulated via formulas when using Conditional Formatting. All range properties can be accessed when using VBA.

Ranges can be specified by either R1C1 notation or A1 notation. R1C1 is no longer common and requires an option setting. This article will discuss A1 notation exclusively.

Excel recognizes three reference operators. These three operators combine input ranges and produce an output range.

The first is the Range Operator, which produces a minimum rectangle around the inputs. The Range Operator is the COLON:

A1:B4

You've seen this in action a million times, I'm sure, but you may have never realized what it means. The colon specifies the range defined by the minimum rectangle circumscribing the input ranges. The input ranges can be specified by any method that describes a range, including defined names, functions, and formulas:

A1:K2:N16:W15A1:lstLastInvoiceINDIRECT("B1"):OFFSET(Z1,,,100,100)

A1:W16

but by substituting formulas for the input ranges, novel ways of controlling other formulas begin to present themselves.

There are two other reference operators and you may not even know about them. These are the Union and Intersect operators. The Union Operator is the COMMA. The Intersect Operator is the SPACE character (space bar).

While the Range Operator always returns a rectangular range, these two other operators can return ranges of any shape, contiguous or not, as long as all of the input ranges are on the same worksheet:

(A1,B2,C3,D4,E5,F6)(A3:E3,C1:C5)(A3:E3 C1:C5)(A1,A10,J10,J1)(CurRow CurCol)

The first of these five examples returns a range in the shape of a diagonal. The second returns a range in the shape of a plus sign. The third returns a range composed solely of C3. The fourth returns a range of four disconnected cells in the shape of the vertices of a square. The last returns the range where the named formulas intersect. This last example is probably the most overlooked lookup method in Excel and can be used to conveniently return information from a list or a table of data. Again, the caveat is that all of the input ranges must be on one worksheet.

Another note is in order for the Union and Intersect Range Operators. In math, a union of two or more sets will only include an element once, but the Excel Union Range Operator does not work this way (pity):

=SUM( (A2:B2,B2:C2) )

The above formula uses the Union Range Operator. It should be the equivalent of:

=SUM(A2:C2)

But it is not. Whatever is in B2, gets summed twice in this example. Also, notice the parentheses around the compound range. This is good practice since functions that require multiple input parameters use commas to separate those inputs. The parentheses ensure that Excel is not confused by ambiguous input.

So the Union Range Operator joins input ranges but is not a true Set Union Operator. On the other hand, the Intersect Range Operator is a true Set Intersect Operator, returning only elements (cells) in common.

There's at least one more twist to range referencing that deserves mention. Ranges can also be 3-D:

Sheet1:Sheet12!A14:Z14

Notice that the Range Operator is used twice in this 3-D range example. This returns a range composed of A14:Z14 for twelve worksheets. This could be useful for adding monthly sheets, for example. The other two reference operators do not work on 3-D ranges. Here is an article from Microsoft explaining 3-D ranges in greater detail and specifying which functions work with them. There's actually more functions than listed there, but that is a topic for another day.

Now that we know what a range is, we need to realize that the roster of functions that we can use to slice and dice our data, use these ranges as inputs. This is most commonly done with rectangular ranges, but the others can produce interesting results.

There is a lot to go over here and so I am going to break it up into a series of articles. To begin with, I thought I would share a video presentation I made a couple of years ago on the VLOOKUP function, long before I began this blog. I had the idea for the blog at the time, but really did not know the direction I would take with it. Here is the VLOOKUP video:

It's a long video; 27 minutes on a simple function! Probably too long. But I'd like to hear your feedback. I've always wanted to host a masters class in Excel, and after seeing the popularity of Chandoo's foundational Excel School, I started thinking of this once again. The production is not as polished as I would like, but hey it was a couple of years ago when I was just learning how to make videos.

The idea behind the masters series is to explore each function in detail and then see how they can be combined into elegant formulas. Of course it would have to have advanced charting, excelhero style. So what do you think? Is this something I should invest my time in?

If you liked this article, please share it!

I would love this kind of in-depth exploration of Excel, especially when it comes to combining functions! I know I have barely scratched the surface of so much that is available in Excel.

I cringe when looking at training opportunities at how basic even the so-called "advanced" Excel courses.

How can I watch the video....?

Definitely Yes, You should invest some time as you have a great way of explaining things in excel. I am interested in joining such class and learn excel

Thank you very much

Hi Daniel,

Great video tutorial very well explained and presented can't wait until the next one.

Thank you for sharing your expertise with us

I come from China,27 years old.

Could you introduce yourself in detail?such as age,educationg,experience?

I want to know How could you do such beautiful work!

Daniel...The video is cut at approx 19 min....It does not download/play completely....

Neat article Daniel.

Although I know of and use the Intersect Operator (Space), I generally don't like it for general use as the intersect operator.

It is hard to spot and read in general use and people who aren't aware of it miss it.

I enjoyed watching the video and would love to see more. It would be great to see you in action in Excel as well.

About the problem you mentioned at the end with using a compound-index column; if you use an Excel 2007/2010 table the formulas you add will automatically fill down as the list grows.

Wow, Awesome post - Which Function To Use - Part 1 - Excel Hero Blog was a wonderful read. I'm such a newbie when it comes to all this, Thanks for this!

Daniel.. Great post looking forward to the next part.

Of interest the VLOOKUP function can return more than one result if used with as an Array formula.

'={VLOOKUP(A2,'Mar 10 Data'!A:G,{3,5,7},0)}

But I guess iti si important to know whom the Video is targeted at.

Enjoy you blogs very much, thanks for your inspiring work

The master series sounds great to me.

Union operator wow.

The match function can be used for multi criteria lookups

{INDEX(D9:F15,MATCH("f"&10,D9:D15&E9:E15,0),3)}

Hi Daniel,

I really like the way you presented ranges topic here. The video could have a quicker pace though and considering your audience I think you could move over the basics quickly and focus on those little tricks that we all love and that make the difference:)

As for your last question - personally, I'd love to see more of your lessons. You're the creative guy that we all want to steal a little bit from:)

@ Alan,

The & operator is slow

Use this Array entered

Index(RngToPick,Match(1,(Condition1)*(Condition2),0)

or non array entered

=Index(RngToPick,Sumproduct(Match(1,Cnd1*Cnd2,0)))

Condition1 = Typically A1:A10 = "Something"

Daniel,

Great work, once again. From my perspective, and apparently from most others who comment on your site, I would love to see more videos. Keep up the stellar work!

Tom

Daniel...The video is cut at approx 9 min....It does not download/play completely....

@Lubo -

Sorry that you had problems with the video. Not sure what it could be because it is working for me. I uploaded it again, so maybe you will have better luck now.

Regards,

Daniel Ferry

excelhero.com

3D references aren't ranges, strictly speaking. For example,

=CELL("Address",Sheet2:Sheet3!A1)

returns #VALUE!. OTOH, try entering the following as array formulas in a 4-row by 1-column range.

=CELL("Address",OFFSET(D1,{0;2;4;6},0))

=CELL("Address",INDIRECT("Sheet"&{1;2;3;4}&"!G3"))

However, the following returns a #REF! error.

=CELL("Address",INDEX(OFFSET(D1,{0;2;4;6},0),3))