Sometimes a small tip can make a world of difference. The tip in this post is one of those.
If you use Excel regularly, you will probably create tables from time to time. And, if you often enter functions, you probably make your formulas more readable by using cell names. If, several months later, you need to make any changes to the workbook, you might not remember the exact name of the cell or table, or maybe you don’t even know whether you actually used cell names or tables. You can always click on the arrow in the name box to see a list of all cell names and tables.
When entering a formula, a drop-down list is displayed with the cell and table names. You need to have an idea of the name in order to know which characters to enter. Also, you will see the function, cell and table names mixed up.
Hence this tip: “Use prefixes for all your names”. For example, always use cnas a prefix for cell names. In tables, use tblas a prefix. The list of cell names and table names will then look like this:
Although this may seem a little strange, the added value will be clear immediately. When entering functions, you now only have to enter cnand you will have a self-explanatory list of all your cell names (no table names and no function names).
This also applies to the tables, of course:
And finally, you will clearly see a cell name or a table in the function you have referred to.
A few minor comments:
- Names can still be changed later by going to the Formulastab in the ribbon, under the Name Manager option.
- When entering formulas and functions, you can also click on F3to see a list of all the cell names. (No tables)