A table in Excel is a list of data with multiple rows and columns. What the Excel tables offer more than a conventional data list is Excel tables facilitate more features such as sorting, filtering, etc. In this article, we are going to discuss all the facts that you need to know about Excel tables such as table creation, renaming, and so on.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
Create an Excel Table
Creating an Excel table is quite easy and it’s just a matter of a few clicks. Excel enables us to transform a list of data into an Excel table. Now you are going to learn the process of creating an Excel table from a list of data.
To Create an Excel table from a list of data all you need to do is:
❶ Select the whole data list.
❷ Then go to the Insert ribbon.
❸ After that click on Table.
Or you can follow another process which is
❶ Select the whole data list.
❷ Then press CTRL + T.
After following any of the processes you will see a Create Table dialog box appear.
Where the table range is already inserted. All you need to do is just,
❸ Hit the OK command.
After hitting the OK command, you will see your data list has been converted into an Excel data table as in the picture below:
All You Need to Know About Excel Table Name
In this article, we will be using a sample product price list as a dataset to demonstrate all the tips and facts about Excel table names. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the tips one by one.
1. Rename a Table Using Table Name Box
If you want to rename your table immediately after creating them, only then you can use this option. Because the Table Design ribbon becomes visible just after creating a new table. Other times this ribbon gets invisible. So when you are done with creating a new table, you are required to follow the steps below:
❶ Go to the Table Design ribbon first.
Then within the Properties group, you will find the Table Name option.
❷ Edit your table name within the Table Name box.
By default table, names are formed as Table1, Table2, etc. So you can edit your table name as you like.
Read more: Pivot Table Field Name Is Not Valid
2. Rename a Table Using the Name Manager
There’s an alternative way that you can use to change your table name at any moment. All you need to do is,
❶ Go to Formulas ▶ Defined Names ▶ Name Manager.
After hitting the Name Manager command, the Name Manager window will pop up. From the pop-up window,
❶ Select your table name.
❷ Then hit the Edit option.
After that, another dialog box named Edit Name will appear. From the dialog box,
❶ Insert the table name within the Name box.
❷ Then hit the OK command.
That’s all you need to do to edit your Excel table name.
- [Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)
- Pivot Table is Not Picking up Data in Excel (5 Reasons)
- Use Excel Table Reference (10 Examples)
- How to Edit a Pivot Table in Excel (5 Methods)
Table Naming Rules
There are the following restrictions that you need to consider while naming your Excel tables. Let’s get the rules one by one:
- You can’t use the same table name over and over again. That is, all of the table names have to be unique.
- Spaces between two consecutive words are not allowed while naming your Excel tables. You can use underscore to link the words if necessary.
- You cannot use more than 255 characters in your table name. This means using too lengthy table names is strictly prohibited.
- At the start of each table name, you can use either a letter, or an underscore, or a backslash(\).
- You cannot use a cell reference as your table name.
Rename a Table Column in Excel
To change your table column name, you don’t need to go through a load of hassles. All you need to do is,
❶ Select a table column header where you want to bring changes.
❷ Double click on the existing name.
❸ Wipe out the already existing name on it.
❹ Type your new column name.
That’s simply it can be done. Bingo!
Get a List of All Table Names in Excel
There are multiple ways that you can use to get a list of all the table names in Excel. So, let’s discuss them all one by one.
1. Using Name Box
This is the quickest way to display all the table names throughout your Excel workbook. You can easily find the Name Box at the left side of the formula bar as indicated in the picture below:
You will see a dropdown arrow within the Name Box. You all need to do is,
❶ Just click on the drop-down arrow.
That’s all you need to get a list of all the table names throughout your Excel workbook.
2. Using VBA Code
You can use the VBA code to get a list of all the table names. All you need to do is,
❶ Press ALT + F11 to open up the VBA editor.
❷ Go to Insert ▶ Module.
❸ Copy the following VBA code.
Sub GetTableNameList() Dim x As ListObject Dim y As Worksheet Dim z As Long z = -1 Sheets.Add.Name = "Name List" For Each y In Worksheets For Each x In y.ListObjects z = z + 1 Sheets("Name List").Range("C2").Offset(z).Value = x.Name Next x Next End Sub
❹ Paste and Save the above code to the VBA editor.
After that go back to your Excel worksheet and
❺ Press ALT + F8 to open up the Macro window.
❻ Select the function GetTableNameList() from the function name list.
❼ Hit the RUN command.
When you are done with all the steps above, you will get a list of all the table names throughout your Excel workbook as in the picture below:
Read more: How to Use an Excel Table with VBA
Things to Remember
📌 You can press CTRL + T to turn your data list into an Excel table.
📌 Press ALT + F11 to open up the VBA editor.
📌 You can press ALT + F8 to bring up the Macro window.
To sum up, we have discussed all the facts that you need to know about table names in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.