Table Names in Excel

Excel Tables are given default names (Table1, Table2, etc.) when they are created. Table names are similar to defined names, which makes them easy use in formulas or to select data.

There are a couple of ways to select all the data in a Table by using the Table name. 1) select the Table name from the Name box,

Name Box Both

or 2) use the keyboard shortcut Ctrl+G then select the Table name and click OK.

Go To Dialog Box

Table names can be used in formulas =ROWS(Table1) and they show up in the formula autocomplete list.

Table Name in Formula

A Table name in Excel 2010 (Windows) is distinctly visible on the Table Tools tab. Here you can easily change the Table’s defined name.

Table Name Windows

Excel 2011 (Mac) doesn’t easily reveal the Table name even though there’s a standard Table tab on the Ribbon. You have to make sure the Table is active, then choose Table > Rename, which will select the entire table and highlight the table name in the Name box. (You don’t have to actually rename the table.)

Rename Table Mac

In Excel 2010 you can also see the Table name by choosing Formulas > Name Manager.

Name Manager Windows

In Excel 2011 you choose Insert > Name > Define to see the Table name.

Define Name Mac

Knowing a Table’s name is important in Excel. It’s the first step in understanding structured Table data.

Related Posts Plugin for WordPress, Blogger...

10 thoughts on “Table Names in Excel

  1. Tom

    Hi there,
    Mac 2011 does not allow me to rename most of my tables once they are created (and used somewhere). This is making my formulas unecessarily vague… ie I’d rather the formula used DataTableName rather than Table1. But now that it uses Table1, I cannot rename it.
    Any thought on why that would be?
    thanks
    Tom

    1. Gregory

      @Tom, I haven’t heard of Excel 2011 not letting you rename the tables. I just created on from some data, selected the Name box drop-down and clicked on Table3 to select it, then typed in MyTable and hit enter. The name was changed and Excel didn’t even squawk.

      I also went to Excel > Preferences and selected Tables to check the settings. Nothing amiss there. The first box will allow table names in formulas, but that’s the only thing remotely close to what your having problems with.

      Just wondering if you can select a Table by using the Name Box?

  2. Chris

    I am using Mac’s version of Excel 2011 and I can’t seem to find any information on how to clear/delete a table/name under the name box. I have a huge list of named tables which I want to clear/delete but don’t know how? Any suggestions?

    1. Gregory

      You can’t actually delete a Table Name in the Define Name box. You can re-name a Table by selecting a table, clicking the Tables tab on the Ribbon and clicking Rename. If you delete the Table, the name disappears from the Name box. If you’ve deleted a Table and the Table Name is still in the Name box, that’s something that I’ve not encountered.

  3. Clive

    In Excel 2010 (Windows) you can’t rename or delete a table using the Names Manager. The only way I’ve found so far is to pick any cell or select the existing table name (eg Table1, Table2, Table3, etc.) and convert the table back to a range…then the existing table name (Table3, etc) disappears. Then I can create a new table. Trying to rename the table name in the name box seems to create a new name but leaves the old one behind. Excel 2010 (Win) has a “Names Manager” that doesn’t seem to work for doing this.

    1. Gregory

      To change a Table name in Excel 2010 (Win), click inside the table then select the Table Tools tab on the Ribbon, and type a new name in the Table Name box (on the left side of the ribbon) and press Enter.

      Easy Peasy.

  4. phil

    I have a excel sheet where the table names are grayed out and I need to remove them.. when i add a formula next to the table I get something like : =Table5[@Notes] can someone explain to me how I can get rid of this ??

    1. Gregory

      If you need a formula next to a table and don’t want the “Tables” version in the formula, manually type in the cell reference instead of clicking the cell. For instance, assume a table has the range A1:B5 and you want to type a formula in cell C2 that adds the first data row of the table, you would type

      =A2+B2 and hit enter. The formula would not change. If, however, you clicked the cell A2 with the mouse, a table reference would appear and spoil your whole day. ūüôā

      As far as having the table names grayed out, I’m not sure what that is all about.

Comments are closed.