Table Names in Excel

by Gregory on April 11, 2011

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...
Tom September 1, 2011 at 7:16 pm

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

Gregory September 1, 2011 at 8:08 pm

@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?

Tom September 1, 2011 at 8:37 pm

Yes I can. But can’t rename it. It’s frustrating. Maybe simplest if I email you a sample?…

Chris September 25, 2011 at 1:49 pm

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?

Gregory September 25, 2011 at 5:28 pm

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.

Clive October 13, 2011 at 6:05 am

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.

Gregory October 13, 2011 at 8:04 pm

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.

aphdstudent January 26, 2012 at 7:13 am

Had this problem too. Converting to a range from the right click menu solved it. Thanks!

phil May 28, 2013 at 12:19 pm

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 ??

Gregory May 28, 2013 at 5:13 pm

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 on this entry are closed.

Previous post:

Next post: