Best Practices for Naming Excel Tables

Sign up for our Excel webinar, times added weekly: www.excelcampu...
In this video, I share best practices for naming Excel Tables. You will learn how to name your tables with a common prefix. This makes the tables easier to reference when writing formulas and easier to navigate to in your sheets and workbooks.
I also have written a macro that will rename all of your existing tables to have the same prefix. You can download the Excel file that contains the VBA code in the accompanying blog post on my website: www.excelcampu...
Related Videos:
Excel Tables Tutorial: Beginners Guide for Windows & Mac
• Excel Tables Tutorial:...
How to Disable Auto Fill in Table Formulas + Keyboard Shortcut
• How To Disable Auto Fi...
VLOOKUP Tutorial: Everything You Need to Know
• Excel Vlookup Tutorial...
#MsExcel #ExcelCampus

[@dennisd5776]

Thank you for bringing up best practices. I was taught 15 years ago to name tables - tbl; forms - frm; buttons - btn; etc as the method for letting others knows what design elements you were using in making any application.

[@ExcelCampus]

Awesome! Thanks for the additional naming conventions Dennis! 👍

[@database_tips_tricks]

best excel channel on the web

[@jimfitch]

Jon, great advice & video on a very important topic. *** FWIW, here are my table naming conventions: "t" for data tables entered directly by user; "pq" for tables created by Power Queries; "pt" for pivot tables. AYK, the PQ query name becomes the name of the output table. Using different prefixes ("t" v. "pq") is very handy when PQ extracts from a direct-entry data table & loads results to a different output table. When I used "tbl" for all tables, I had a hard time tracking whether a table was input or output (particularly if I came back to the workbook weeks or months after initial development to enhance or (gasp!) repair it). Now, the convention makes it clear. ("pq" is useful for connection-only PQs, too, especially in VBA.) *** While we're at it, here are my range naming conventions: "r" for named ranges; "rg" for range objects in VBA. This helps greatly in writing & debugging VBA: reference "r" identifies a named range defined in Excel whereas "rg" means that the reference exists only in VBA. *** I haven't done much yet with Power Pivot pivot tables, but I probably will use a different prefix ("pp"?) for those tables when I do to distinguish between pivot tables created from tables/ranges stored in Excel ("pt") & those created from the data model ("pp").

[@ExcelCampus]

Hey Jim, I love this! Great idea with adding more categories for your prefixes. Especially the Power Query input vs. output. If it's ok with you, I'm going to add a section to the blog post with ideas from your comment. And give you credit, of course. Thanks again and have a nice day! 🙂

[@jimfitch]

@@ExcelCampus It's fine with me to put these ideas on your blog (or anywhere else). Don't worry about providing credit. Not needed. I'll recognize them when I see them. :-)

[@sidkings]

Life saver, thank you very much indeed Sir.

[@lisabrecht3483]

This is the BEST tutorial on KZbin!

[@ExcelCampus]

Thank you, Lisa! I appreciate your support! 🙌

[@joedi]

I use the tbl naming method for filtering in PowerQuery.

Very helpful! You have no idea

[@darrylmorgan]

Wicked Tutorial With Really Helpful Tips..I've Got Into The Habit Of Naming All My Tables As A Best Practice,Makes My Life So Much Easier!Thank You Jon :-)

[@ExcelCampus]

Thanks so much, Darryl! 🙏

[@abdulrahmanmuslih141]

Thanks

[@honeykevat7078]

Thank you so much. Help with my assignment.

[@ExcelCampus]

Happy to help @honeykevat7078 😀

[@ThatOfficeGuy]

Great advice and great video. I find a lot of people overlook tables and table names in Excel, it is becoming more and more important to use excel tables in the 365 world and more so if you want to start using Power Automate etc.

[@ExcelCampus]

Thank you! Yes, I completely agree about the importance of using Tables in modern Excel. 🙌

[@levelfinancefield8668]

Super helpful!

[@mohammadpourheydarian5877]

Great Video

[@alexnewman1364]

Would love to see a video addressing table names and power query. i.e. when you change a table name that a query is based on.

[@afsarbmahmud]

Thank you for a very helpful tip. Subscribed :)

[@TEKNISHA]

Great video again as usual Jon. I love the way you are always on point... Get to learn a lot from your videos Btw I am also passionate about excel and do have my own KZbin channel where I do try to make some videos that hopefully will help people.. Cheers

[@charlesotstot63]

I start all of my tables and named ranges with "_" since no functions start with it. I further name tables with a leading "f" or "d' for Fact and Dimension. Makes it super easy to pull up whatever I need

[@levelfinancefield8668]

great tactic

[@ExcelCampus]

Great tips! Thanks for sharing Charles! 🙌

[@ricos1497]

Interesting Charles. I wonder if you'll change that when LET becomes more widespread. the reason I ask is because I use underscore for variables in LET formulas. I prefer that because it keeps the variable names shorter in long formulas. I totally agree with the f and d for Fact and Dimension. I use tbl for tables like Jon, in case anyone cares!

[@explainedbyquotes1735]

Subscribed. Great content...

[@wayneedmondson1065]

Hi Jon.. thanks for the great video and super tips. I too use this and other naming conventions to be able to quickly find common grouped and sorted tables, named ranges, etc. Another good place to do this is if you write a lot of user defined functions in VBA. If so, I start each with a "u" or "u_" or "udf" and then when I need to use them on a worksheet, I can quickly sort to the group with "=u". So, great advice. A little forward planning can help to save time later. Thanks for the great tricks. Always something new and fun to learn at Excel Campus. Thumbs up and stay safe!!

[@mikelennon1078]

Excel 2019 how to delete all existing tables within in a workbook with all of their tables names , all together without loosing data?

[@sunracemangesh]

Can we add table names with numbers and space like for eg i need table name as below - 1. Not Started

[@andreausa123]

The part of [ Formula - Name manager - Refers to] has been grayed out , how can l make it editable ?

[@robepineda25]

when i click on the table, the table design option doesnt appear how can i do to solve this problem

[@zahirshah4117]

sir jon i will wait for your reply

[@robertgrlic6505]

Great video! In what circumstance would you use the prefix "d" dimensional table and "f" for fact as oppose the one you shown in your video?

[@ExcelCampus]

Great question Robert! You might want to see Jim Fitch's comment on this video for his strategy on naming different types of tables. I would use "d" and "f" if your file contains a data model (Power Pivot) or even a lot of lookup formulas like VLOOKUP that create "relationships" between the tables. Anything that helps keep you organized will be an improvement. You might still want to prefix all tables with a unique identifier, then follow that with the type (d or f), then the name. t_f_Sales t_d_Calendar This will allow you to group all tables, then create subgrouping by types. I hope that helps. Thanks! 🙂

[@miamited69]

When renaming a table with the name manager, does it update references to that table also?

[@ExcelCampus]

Great question! Yes, the Name Manager will update references in the formulas and throughout the workbook. It's the same as updating the name from the Table Design tab.

[@alexnewman1364]

@@ExcelCampus but will it break my power query source for queries built on a renamed table?

[@jakariahabib1062]

Can you pls explain the formula you have wrote on 1:20?

[@ofraberger62]

Hi Jon, is it possible to rename a table suffix based on a cell value in the sheet? I have a sheet that has 3 tables, which names are: AB_5, CD_5, EF_5. When I copy the sheet I get names like AB_546, CD_546, EF_546. I have a cell in each sheet with the number that I want to appear in the table name - for example: in cell A10 I have the number 6. I would like to have some macro that copies cell A10 and replace it with the numbers that appear after the underscore in the table names (eg. AB_6, CD_6, EF_6).

[@martinargimon730]

Hi Jon So you think the technique listed below to load a Dependent Combo Box with data is good? ( Using Indirect instead of VBA) Define a name called Second_List whose RefersTo property is =INDIRECT("List_"&Sheet1!$L$2) and then use Second_List as the Listfillrange for the second combobox.

[@vivanverma1234]

I WAS UNABLE TO UNDERSTAND HOW THE THE TABLE WAS NAMED AND HOW TO USED THAT, IF SOMEONE COULD EXPLAIN

[@ExcelCampus]

Hi Vivian! 😊 Tables are named automatically with the word Table and a number. Click into the table name to rename the table as shown beginning at the 00:14 mark in the video. The name of the table can be used in place of a cell range in functions such as VLOOKUP, as shown beginning at the 01:02 mark in the video. I hope this helps! 🙂

[@Teabadger]

Dude this is so annoying. Everytime I go to name it in the cell, and I click enter it just resets to the old name....does not work