In Microsoft Excel, whenever you create a new Table, by default Excel will name it Table1, Table2, and so on.
This would work fine when you have one or two Tables, but imagine a situation when you have lots of Tables in a workbook.
For instance, let’s say you have a Table containing records of employee salary, then it would be better if you name it something like EmployeeSalary. This also makes it easier when you’re using the table as a source for Power Query or in formulas.
So how do you rename a Table in Excel?
In this tutorial, I will show you various methods and some quick tips for renaming a Table in Excel.
Method 1: Rename Excel Table Using the Table Name Option
One straightforward method to rename a Table is by using the Table Name option available in the design tab.
Below I have an Excel dataset that contains the employee details (such as id, name, designation, salary, etc), and I want to convert this data into an Excel Table and change the name to something more meaningful
To convert this data into an Excel table, select any cell inside the data range and press CTRL + T from the keyboard (or click the Insert tab and then click on the ‘Table’ icon).
Excel would give this table a generic name (such as Table1) that we need to change.
Below are the steps to rename this Excel Table:
- Select any cell inside the Excel Table
- Click on the ‘Design’ tab in the ribbon. This is a contextual tab which means that it will only become visible when you select a cell in the Excel Table
- In the Design tab, look for the option ‘Table Name’ where you can see the default Table name generated by Excel (Table3 in this case)
- Delete the default Table name and enter a suitable Table name. In this example, I am going to name it Employee.
This will rename the Excel Table to ‘Employee’.
Pro Tip: You can also use the keyboard shortcut ALT+J+T+A to go to the ‘Table Name’ option. So once you the above shortcut, the cursor would be placed in the Table Name field and you can just type the name you want to give to the table. This would only work when you have already selected a cell in the Excel Table
Method 2: Rename Excel Table Using the Name Manager option
You can also rename a Table using the Name Manager option available in the Formulas tab.
Below I have an Excel table that contains the employee details (such as id, name, designation, salary, etc), and I want to convert this data into an Excel Table and then change the name to something more meaningful
First of all, convert that range into an Excel Table. For that, select any cell inside the data range and press CTRL + T from the keyboard. This will open the Table dialog box, where you can confirm the range and click OK
Below are the steps to rename an Excel Table using the Name Manager:
- Click on the Formulas tab in the Ribbon
- In the Defined Names section, click on the Name Manager option
- This will open the Name Manager dialog box showing the names of all the existing name ranges and Excel Tables.
- Click on the Excel Table for which you want to change the name. In this case, I have only one Table so I am going to click on Table1.
- Click on the Edit option
- This will open the Edit Name dialog box as shown in the screenshot below
- In the Name option, delete the default Table name and enter a suitable name. For instance, I am going to name it ‘Employee’.
- Hit OK and this will rename the Excel Table.
Apart from renaming a Table using Name Manager, you can rename any Named Ranges in Excel. When the Name Manager is opened, it will show you a list of all the name ranges and Excel Tables in that workbook.
Pro Tip: You can open the Name Manager dialog box using the keyboard shortcut CTRL + F3 (hold the control key and then press the F3 key)
Method 3: Rename Excel Table Using VBA Script
In this method, I will show you how you can rename a Table name using a VBA macro code script.
Below is the VBA code that will rename a table with the name “Table1” to “Employee”
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub RenameTable()
ActiveSheet.ListObjects("Table1").Name = "Employee"
End SubI have hardcoded the name of the table that I want to assign to Table1. You will have to change the above code based on your table names.
Here is the explanation of the above code.
- Activesheet: Specify the sheet name where your Table is located
- Table1: Default name generated by Excel. Check the name of your Table from Name Manager.
- Employee: Specify the new name of the Table.
Now run the script by using the Run button or F5 from the keyboard and it will rename the Excel Table.
Instructions to Run the VBA Macro Code
- Open the VBA editor by pressing Alt + F11 from the keyboard in the case of Windows and Opt + F11 if you are using Mac.
- To insert a new Module click on the Insert tab.
- Now click on Module
- This will insert a new Module where you can write your VBA script.
- Copy and Paste the above VBA code into the module code window
- To run the code, place the cursor anywhere in the code and then press the F5 key (or click on the green triangle play icon in the toolbar)
Some Things to Keep in Mind When Renaming Excel Tables
- You can not have spaces in between the words in the name of an Excel Table. For example, you can not have the name “Employee Sales” but you can have the name EmployeeSales
- You can not use a name that might refer to an existing cell reference. For example, you can not name your Excel table ‘AA1’ as that is also a cell reference in the worksheet
- The first character of your Excel Table name should be an alphabet or underscore or backspace. So your Table name can not start with a number or any other special character.
- You can not give a name to your Table that is already assigned to any other table or named range in your workbook.
In this tutorial, I showed you three different methods of renaming a Table in Excel.
Methods 1 and 2 will use the option available in the ribbon while method 3 is based on the VBA script.
Moreover, I shared some quick tips and keyboard shortcuts in order to make things easy for you.
Other articles you may also like:
