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

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.

 Table Naming Best Practices.xlsm (23.5 KB)

Benefits of Prefixing Table Names

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

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.

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.

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.

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).

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.

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.

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

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1