Business Functions | Excel

Non-contiguous ranges, ranges that consist of blocks of cells that are not joined together, are at the heart of how Excel stores ranges.

Any range can be non-contiguous. All you do is, as you are selecting cells with the mouse, hold the Control key down. You will be able to select as many disjoint blocks of cells on one sheet as you like. All Excel ranges can only be on one sheet, so your non-contiguous blocks cannot be on separate sheets, and cannot span sheets. In truth, Excel is not 3D, so you do have to stay within the confines of any one particular sheet, but within this constraint the non-contiguity is a powerful feature. You can use non-contiguous ranges as inputs to functions or as definitions for a range name.

Background

It's interesting to see how Excel stores ranges internally, and those of us that have in the past tinkered with the Excel API have come across Excel's indigenous range types.

• Excel's SRef type is a range of cells on the active sheet, defined by the first and last rows and columns of that range. In other words it defines a rectangular reference, but it is not specific about which sheet it is on. Whilst its hard to imagine why Excel would store a range and not know which sheet it is on, this does happen if you ever define a named local range ie a range local to the sheet, using a syntax like Sheet1!A1:A2 (it's paradoxical that in order to define a range that has no idea of its sheet name, you define it by giving it a sheet name). If you do define a local name, you are in effect defining a range of cells without saying what sheet they are on. The Print_Area of a sheet is an example of a local range name that you set up from the menu to determine how your page prints out.
  
• Excel's Ref type has a Sheet ID in its data structure, so it is a general range that can be referred to anywhere in the workbook, because Excel knows the sheet name (eg, in a formula, Sheet1!A1:A2)). It is also capable of being non-contiguous, because it is in itself a collection of rectangular references. This is why Excel's non-contiguous ranges have to all be contained within one particular sheet, because Excel can only store one Sheet ID against a range. When you are selecting cells in a non-contiguous range with the control key down, you are creating a Ref, or when you are selecting cells for a formula that aren't on the current sheet (so you get the Sheet suffix in the definition).
  
• Excel's Multi type is a list of values. You can specify a range of values in Excel using curly brackets, for example SUM({1,2,3}). That's a range, but its not a rectangular reference to anything, so its a range of values, or Multi. It doesn't need any information about where the data is. Note however that is a list of values, nothing to do with references at all, so SUM({A1,1,2,3}) won't work.

Conclusion

What does all this tell us? Well, it tells us that Excel is really built to deal with non-contiguous ranges from the ground up, its not a bolt-on feature, so we should be confident about using them. It also tells us to stop trying to set up multiple sheet ranges, because Excel just doesn't understand them (Excel accepts multisheet references for some functions like SUM(), but its non-standard behaviour that must use a unpublished technique). It helps to explain how local range names work,and again, be confident using them, they are at the core of how Excel works.