Best Practices for Naming Excel Tables

Bottom Line: Learn helpful tips for naming Excel Tables to make it easier to reference them in formulas and navigate to them.

Skill Level: Beginner

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

Below is an Excel file that has a couple of the same tables you see in the video. More importantly, it contains the macro I wrote that renames all of your tables to have the same prefix. Feel free to copy the macro to your own Personal Macro Workbook.

Benefits of Prefixing Table Names

Whenever you create a table in Excel, it is automatically named with a numerical value (Table1, Table2, etc).

Table name box table design tab

I have found that it's really helpful to rename the tables with a prefix and a short description of what the table holds. Personally, I use the prefix “tbl” but it could be any prefix that makes sense to you.

Excel Table Name Prefix tbl then Descriptive Name

Using the prefix that is common to all of the tables makes it much easier to find and reference them.

For instance, in writing this VLOOKUP formula, when I get to the Table Array argument, I just need to type the prefix “tbl” and it will list all of the tables that are available to me in the workbook.

table prefix to reference in formula

If you go to the Name Box, you can also see all of the tables. Because they all share a common prefix, they are all grouped together, which makes it handy to jump straight to the table you want.

Name Box with grouped table names

Another place you will see tables listed is in the Name Manager. You can access the Name Manager from the Formulas tab (or by using the keyboard shortcut Ctrl + F3).

Name Manager table list

Renaming Multiple Tables

Maybe you like this idea of adding a prefix to your tables, but you've already named them and don't know how to go about changing the table name. You can change the names of tables and other objects in the Name Manager. Just select the name of the table you want to rename and click the Edit button.

Name manager edit button to rename tables

VBA Macro to Rename Tables

If you're looking to rename lots of tables and don't want to take the time to rename each one, I've written two VBA macros that will add a new fix or replace an existing prefix to all Tables in the workbook.

Rename Tables with Prefix VBA Macro for Excel

The macros are included in the downloadable workbook at the top of this post.

Related Videos

If you're fairly new to Excel Tables or need a refresher, I've got a tutorial video here: Excel Tables Beginner's Guide.

Also, when working with tables, you may want to avoid having formulas automatically filling in a column. Here's a helpful post to deal with that: How to Prevent or disable Auto Fill in Table Formulas.

Conclusion

It might take a few extra seconds to add a prefix to each of your tables as you create them, but I think it saves time in the long run when you want to reference them in your formulas. If you have questions or suggestions, you can leave them in the comments below! I hope this was helpful for you.

13 comments

Your email address will not be published. Required fields are marked *

  • Thanks for that tip Jon. Does your macro also update the references to tables using the old name in the other macros in that workbook.

    Dennis

    • Hi Dennis,
      Great question! No, it does not update your macro code. If you have macros that references tables, you will need to update the code. You can do that with the Find and Replace window in the VB Editor though.

      I hope that helps. Thanks again and have a nice day!

  • Hi yesterday till today still something on.my mind and I don’t know how to go along with it. I have a payment summary. With payee name, amount. Different payment dates. I want to set a auto payment run and it will generate a payment voucher for each payee. How to do that?

  • Jon, I have been using your tbl prefix for years. Thanks!
    One trick I like to use is to select the range name and press the tab button in the Name Manager. Excel will select that named range on any worksheet in the active workbook?

  • Like the sound of your macro but can it be modified for the following.

    I want to run my copyrename macro and then immediately call a table renaming macro, so I need the solution to be for “Active Worksheet” and not the whole workbook.

    I’d like each table on the worksheet (6 tables in total) to be renamed as follows:

    Worksheet name e.g. ITP001_
    Header of First Table Column e.g. PC

    Result:

    Worksheet 1
    Table 1 = ITP001_PC
    Table 2 = ITP001_AT

    Worksheet 2
    Table 1 = ITP002_PC
    Table 2 = ITP002_AT

    Is this possible. I’ve hunted high and low for VBA code to rename tables (to overcome the fact excel giving the copied table an unpredictable name). Nothing has worked so far. Am getting very desperate now.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter