Latest posts by techwriter (see all)
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016
- How to View a List of All Open MS Word Documents through VBA Macro - August 31, 2016
By Chris Le Roy
Microsoft Excel is one of the most important applications that you can ever learn in your working career, the reason I say that is that over 90% of businesses use Microsoft Excel on a day-to-day basis so not knowing Microsoft Excel can really put you on the outer. One of the greatest challenges we have in working with Microsoft Excel is the cell addresses. The use of the column naming standard from A to Z then AA to IV is really ingenious but it really does cause a lot of problems for people using large spreadsheet. Just imagine trying to remember what every single cell does in a spreadsheet that has over 20,000 cells.
Well if you think that’s hard, just try imaging one … there must be an easier way.
Absolutely there is and it involves naming your cells in a worksheet. So what does naming involve, well essentially naming a cell involves giving a cell address such as AA543 a normal name like Interest Rate or Total Amount. The cool part about giving cells a name is that you can actually refer to the name in formulas rather than the cell address. Naming a cell makes life so much easier in Microsoft Excel simply because as humans we are able to relate to more descriptive names like Interest Rate or Total Amount than we can with a standard cell however there is some very strict rules we must follow.
Rule #1 – Names Must Not Exceed 255 Characters
The first rule that we must follow is that when you are naming a cell, the cell name must not exceed 255 characters. There is another aspect of this that you should also be aware of and that is if the name exceeds 253 characters then the name will not show up in the Name box on the formula toolbar. In reality though you want to follow this rule of thumb, in that your names should not exceed 30 characters, generally we try to limit them between 10 and 20 characters because anymore than that and your formulas and functions can be too difficult to fault find.
Rule #2 – Cell Names Are Not Allowed To Contain Spaces
Cell Names in Microsoft Excel must not have spaces. There are two ways that are considered acceptable in overcoming this problem. One way is to distinguish unique words in a name by placing the letter at the start of each word in Capitals such as –
An alternative way of naming a cell is to use the underscore such as –
Either technique is quite acceptable. You may find though that using underscores in Names where you have used borders that it may be difficult to distinguish the underscore from a border. In this particular case it would be better to use the first naming standard.
Rule #3 – Microsoft Excel Does Not Distinguish Between a Capital Letter and a Lower Case One
Microsoft Excel Cell Names are not case sensitive which means that a capital C is exactly the same as a lower case c and this is one of the reasons why we utilise the first naming standard in Rule #2.
Rule #4 – Cell Names Must Be Unique and Must Not Resemble a Number, Cell Address Or Reserve Word
Microsoft Excel is very strict in that you cannot have the following in cell names –
– No Spaces in Cell Names
– No Mathematical Symbols such as + – * / < > &
– Names cannot resemble a number, cell address or reserved word
A reserved word is a text used for functions and formulas. For example you cannot call a cell by the name SUM as it is already used in the function SUM.
Rule #5 – Cell Names May Only Contain The Following Characters
When naming cells you may only use the following characters –
– Letter Characters From A to Z
– Number Characters from 0 to 9
– Non-Uniform Characters such as . _ ?
Rule #6 – A Cell Name must start with a Letter, Backslash ( ) or an Underscore ( _ )
Whilst you can use numbers and non-uniform characters such as a decimal and question mark all cell names must start with a Letter, Backslash ( ) or an Underscore ( _ )
So, How do we give a cell a name?
The simplest way to name a cell is to first select the cell, then once you have selected the cell simply click in the Name Box on the formula bar, type the name you wish to use and then press the Enter Key. What you will notice is that each time you click on the cell with the cell name, Microsoft Excel will substitute the cell address for the name you gave the cell.
Naming cells is a really useful skill to learn especially when you are starting to create complex spreadsheets as it is very easy to forget which cell address contains each piece of data. Naming cells means that it will be easier for you to write your formulas that refer to the cells with the right information.
Chris Le Roy has available Microsoft Excel Shortcuts to help you with Microsoft Excel. To learn more about Naming Cells or to get a Certificate in using Microsoft Excel in just 16 hours, simply check out Chris’s correspondence course where you can earn yourself Microsoft Excel Certificates issued by his company without even leaving home – Microsoft Excel Spreadsheet Training