7 Ways to Name a Table in Microsoft Excel

Do you want to name or rename your table? This post is going to show you how to name a table in Excel.

When you create a table, Excel will give it a generic name such as Table1, Table2, etc. But this isn’t usually a very good descriptive name for your table.

You will need to reference your table name in formulas and other locations, so giving it a descriptive name is the best practice.

Get your copy of the example workbook to follow along with the post and find out how to give your table a new name!

Rules for Naming Tables in Excel

Not all names are allowed for tables. There are a few rules you will need to follow when naming a table.

  • Allowed characters: You can only use letters, numbers, underscore, or backslash characters in a table name. No spaces or other special characters are allowed.
  • First Character: A table name must begin with either a letter or an underscore, it can not begin with a number.
  • Maximum Length: A table name can have a maximum of 255 characters.
  • Reserved Names: Certain reserved names are not allowed such as R, and C.
  • Cell References: Names such as ABC123 are not allowed because the name is already used as a cell reference.
  • Case Insensitive: Names are not case-sensitive so MyTable is the same name as MYTABLE.
  • Unique Names: Each table must have a unique name within a workbook.

These rules do allow a wide variety of names, so most of the time you won’t notice they exist.

If you do try to create a name that violates one of these rules, a pop-up warning will tell you the syntax of this name isn’t correct.

💡 Tip: Name all tables in your workbook with a common prefix such as tbl. This way all the tables are listed together in places like the name box, formula IntelliSense, or name manager!

Name a Table from the Table Design Tab

The process to name a table is simple and can be done from the Table Design tab.

This is a contextual tab that will only appear in the ribbon when your table is selected.

Follow these steps to name your table from the Table Design tab.

  1. Select a cell inside your table. This will cause the Table Design tab to show up in the ribbon.
  2. Go to the Table Design tab.
  3. Click into the Table Name field and type a new name.
  4. Press the Enter key to confirm the name.

Now your table has a new name!

Name a Table from the Navigation Pane

The navigation pane is an easy way to see all the objects in your workbook and navigate to them with a click.

The navigation pane includes a list of all the tables in your workbook and you can even name them directly in the pane.

Follow these steps to name a table with the Navigation pane.

  1. Go to the View tab.
  2. Click on the Navigation command.

This will open the Navigation pane on the right side of the workbook. You’ll see all the sheets in your file listed and you can expand each sheet to see all the objects that it contains.

  1. Expand the sheet that contains the table to name.
  2. Right-click on the table.
  3. Select Rename from the menu.

This opens the Rename menu inside the Navigation pane.

  1. Give the table a name in the Rename to field.
  2. Press the OK button.

This will give your table a new name! The Navigation pane is a great option for naming multiple tables as you can see them all listed in one window.

Name a Table from the Name Manager

Another place that will list all your tables is the Name Manager. As the name suggests, it allows you to manage named objects such as named ranges and tables.

You’ll be able to name your tables in the Name Manager too!

Follow these steps to use the name manager to name your tables.

  1. Go to the Formulas tab.
  2. Click on the Name Manager command.

This will open up the Name Manager which shows you all your named objects.

  1. Select the table to name.
  2. Click on the Edit button.

💡 Tip: You can show only the tables in the Name Manager if you have a long list of named objects. Click on the Filter button and select Table Names from the options.

When you click on the Edit button, Excel will open the Edit Name menu.

  1. Change the name in the Name input.
  2. Press the OK button.

This will name the table and you can press the Close button to close the Name Manager.

Name a Table from the Accessibility Tab

Giving your table a meaningful name can help people with vision problems understand what the table contains. A screen reader will read the table name to the user along with any alt text.

This is why you’ll also find the table naming command in the Accessibility tab.

Follow these steps to name a table from the Accessibility tab.

  1. Select a cell inside the table to name.
  2. Go to the Review tab.
  3. Click on the top part of the Check Accessibility command.

This will open the Accessibility pane on the right of the workbook as well as reveal an Accessibility tab in the ribbon.

  1. Go to the Accessibility tab.
  2. Type the table name into the Table Name input box in the Name section.
  3. Press the Enter key to accept the name change.

Your table name has now been updated!

Name a Table from the Quick Access Toolbar

Naming a table is such a common task in Excel that you should consider adding the Table Name input to the Quick Access Toolbar.

This way you’ll be able to quickly name your tables without the need to navigate to any ribbon.

When in the Table Design tab, right-click on the Table Name heading and choose the Add to Quick Access Toolbar option from the menu.

Now wherever you are in the Excel ribbons, you can quickly name your tables!

Name a Table with VBA

Suppose you have a workbook with loads of tables all suffixed with the year.

When you reuse the file for next year, you’ll need to update all the table names to the new year. This could be a tedious task!

This is something that could be easily done with a VBA script.

Go to the Developer tab and click on the Visual Basic command, or press the Alt + F11 keyboard shortcut to open the visual basic editor.

Go to the Insert tab of the visual basic editor and select the Module option.

Sub RenameTables()

Dim wsObj As Worksheet
Dim tableObj As ListObject

For Each wsObj In ActiveWorkbook.Worksheets
    For Each tableObj In wsObj.ListObjects
        tableObj.Name = Replace(tableObj.Name, "2022", "2023")
        MsgBox tableObj.Name
    Next tableObj
Next wsObj

End Sub

You can then paste in the above VBA code.

This code will loop through each worksheet and then loop through each table in the worksheet. It uses the Replace vba function to find and replace instances of a given text in the table name.

tableObj.Name = Replace(tableObj.Name, "2022", "2023")

The above code will find 2022 in the table name and replace it with 2023, but you can change this to suit your needs. Just change the above line of code accordingly.

Name a Table with Office Scripts

If you are using Excel online, then you won’t be able to use VBA! But you can still automate the table naming process with Office Scripts.

Go to the Automate tab and click on the New Script option.

This will open the Office Scripts code editor on the right side of the workbook.

function main(workbook: ExcelScript.Workbook) {
	//get all tables in the workbook
	let tbls = workbook.getTables();
	//loop through each table in tbls and replace text
	for (let i = 0; i < tbls.length; i++) {
		tbls[i].setName(tbls[i].getName().replace('2022', '2023'));
	};
};

Copy and paste the above code into the Code Editor and press the Save script button. Press the Run button to execute the code.

The code will loop through all the tables and replace the text from 2022 to 2023 in the table names. You can edit this part of the code replace('2022', '2023') to change this to suit your own situation.

Conclusions

Tables are the best tool for keeping your tabular data organized and they come with many beneficial features!

Naming your table is just as important to help organize all your tables. This is an easy process and can be done from either the Table Design or Accessibility tabs.

The Name Manager or the Navigation pane can also be used to see a list of all your tables and then give them names.

If you need to automate the table naming process, this can also be done with either VBA or Office Scripts.

Do you take the time to name your tables? Do you know any other table naming tips? Let me know in the comments section below!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃