by Allen Wyatt
(last updated October 1, 2016)
David wonders what the difference is between tables and named ranges and why he would prefer one over the other. He currently uses ranges are named, of course, and they are dynamic when it suits his purposes (most of the time).
There is probably quite a bit about tables that could be written—probably much more than what I'll choose to write here. The bottom line is that a named range can be very powerful in formulas. However, a table encompasses named ranges (they are utilized in how Excel defines tables) and adds quite a bit more functionality.
To understand what I mean, let's take a look at how you create a table. It is not uncommon to have data in Excel that is quite "tabular." It consists of rows and columns, with the first row used to add column headings. (See Figure 1.)
Figure 1. Sample data in a worksheet.
You convert this data to a table by selecting any cell in the data and then clicking the Table tool on the Insert tab of the ribbon. Excel asks you to confirm that you want to convert the data to a table, and when you click on OK the deed is done. (See Figure 2.)
Figure 2. Sample data in a defined table.
You can tell it is now a table because Excel formats the data differently than it was before. It is now "banded" using alternating rows of color, designed to make it easier to keep your place as you work with the data.
Note, as well, that there are filter arrows at the right side of each column. This is something you cannot do with named ranges: filter the data as an inherent part of the range. If you click a down-arrow next to a column, you'll see different ways to sort and filter the data in the table based on the contents of that column.
You'll also notice that the ribbon has changed; there is now a Design tab, which should be selected. At the left side of the ribbon, in the Properties group, you see the name which Excel has assigned to your new table. It defaults to something like "Table1" or "Table2," but you should change the name to something more descriptive of the data in your table. This is, essentially, a named range that applies to the table as a whole.
As you start to work with the table, you'll notice other differences, as well. Here's a few of them:
Another neat feature is that you can use "shorthand" in your table formulas. So, instead of specifying cell references in a formula, you could do something like this:
The brackets indicate you are using the shorthand code, which consists of the @ sign followed by the name of the column defined in your header. This can make for much easier-to-read formulas in the table.
This should give you a quick idea of how defined tables differ in usage from named ranges. Like I said, there is much more that could be written about the differences, but the best way to find out is to start using tables for your data and see how they fit into your actual use of Excel.
There is one "gotcha" you need to be aware of, however. If you use tables in a workbook, you cannot share that workbook with others. Excel requires tables to be converted to named ranges if you decide to share the workbook with others.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13476) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel stores dates and times internally using what is called a serial number. This tip explains how that serial number is ...Discover More
Want to find out exactly what version of Excel you are using? Here's how to get to the info.Discover More
When you select a range of cells, Excel normally displays the sum of those selected cells on the status bar. If the sum ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.