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:
- Can I treat a table as a named range?
- Does a named range provide me any functionality not offered by a table, and vice versa?
- 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.
Best Answer
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.