What's the difference between a Table and a Named Range in Excel 2007?

Question

Can someone explain the difference between Excel Tables and Named Ranges in Excel 2007? It seems that in addition to having the features of Named Ranges, they're somehow marked as Tables which gives them special formatting & filtering options in the ribbon.

Other questions:

1. Can I treat a table as a named range?
2. Does a named range provide me any functionality not offered by a table, and vice versa?
3. Should I use one over the other (assuming that consumers of the spreadsheet are using Excel 2007 or higher)?

Google has not been helpful (excel difference between named range and table and excel 2007 difference between named range and table), and I've found one resource describing table functionality, but no reference to named ranges.

EDIT: It seems that the primary difference is the ability to use structured references. There's also the ability to easily re-size the dimensions of the table, and to insert new columns without moving an entire column, though those last two are only from my observations.

Answer 1

I'd try googling again. Searching for "Excel 2007 named ranges" yields lots of results, as does "Excel 2007 tables."

They are pretty separate topics, although with some overlap, so that may be why your difference search isn't working. It's like if I researched "difference between bicycles and wheels" - bikes do have wheels, but wheels have many other uses.

A table is a defined grid of cells for data and formulas that automatically expands as you add to it and also automatically has the capacity to sort and filter. Creating a table also automatically adds a named range to your worksheet.

A named range is just one or more cells to which you, or Excel, have assigned a name. A common one that Excel adds is "Print Area", if you have used the Set Print Area command. Named ranges can be individual cells that you've named to make formulas easier to read, e.g., instead of "=A1 + A2" if you've renamed both cells your formula could be "=Expenses + Income", just for example. Named ranges can also be created for groups of cells.

Both are very useful tools. Try googling them separately or consulting a basic Excel book and you should be able to get all the info you need.

Answer 2

Tables must exist as contiguous areas of cells on a sheet, and every column must have a heading (even if they are hidden by turning the heading row off). They have lots of features like including total rows, filter / sort arrows by default (without having to turn them on), the column headings replace the usual A, B, C if you scroll down, all sorts of stuff.

Named ranges do not necessarily refer to actual cells on a worksheet, althoug they often are used to do that for simple implementaitons:

Named ranges can be defined to refer to: - a single cell - a range of cells - another range (especially one in another workbook) - a constant - a formula which evaluates to a value, or text, or to point to a range (using OFFSET functions to produce dynamically expanding ranges is quite common).

You can use implicit intersections to refer to parts of a named range, but not to a Table (or at least, not in the same way). Structured references are useful, producing a syntax a bit like a query string, but can be clunky to use. 2010 has a better shorthand for referring to elements of the same table.