There was a time when work was known to be working in the fields or factories and in large warehouses. A time when the strength of a person’s back determined how much money they made.
But these days the reverse is the case. Now, most of the jobs available out there are usually in front of a computer screen. This means that if you want to survive in this era, you must be willing to adapt to the types of jobs available.
Speaking of the types of jobs available, one crucial tool which every working person or student for that matter should have in their arsenal is the “Microsoft Excel tool.” If you are a student, office worker or an aspiring job applicant, and you don’t correctly know how to use and navigate your way around this incredible tool, then it is high time you go for some excel courses or even an excel boot camp.
This Microsoft tool can be used to perform a lot of tasks ranging from recording expenditures, incomes, plan a budget, chart data to more complex tasks like pulling data from externals sources such as the stock market feeds, and running data through formulas for financial models in real-time.
Microsoft excel is the current status quo in many businesses and institutions today; that is why we are going to be going through some easy excel tricks and tips when working with names.
What a name consists:
A name is more or less an identifier for something in a workbook. This “something” can include a range, a chart, a shape, a cell, etc. If you decide to provide a name for your range, you can then go ahead and use that name in your formulas. e.g., if your worksheet contains daily sales information stored in the range B2:B200. Further, assume that C1 contains a sales commission rate. The following returns the sum of the sales, multiplied by the commission rate:
=SUM (B2:B200) * C1
Now the formula above works fine, but its use is not clear. To help clarify the formula, you can define a descriptive name for the daily sales range and another descriptive name for cell C1. Let’s assume, for this example, that the range B2:B200 is named DailySales, and cell C1 is named CommissionRate. You can then rewrite the formula to use the names instead of the actual range address:
=SUM (DailySales) * CommissionRate
From the above example, it clear that using names instead of cell references makes the formula self-documenting and much easier to understand. moreover using named cells and ranges offers a number of advantages like
- Names enhance your formulas by making them more understandable and easier to use, especially for people who didn’t make the worksheet. Obviously, a formula like revenue-expenses is more readily understood than D10-D20
- When you select a named cell or range, it appears in the name box. This is an excellent way to ascertain that your names refer to the correct cells.
- You can quickly, move to areas of your worksheet either by using the name box, located at the left side of the formula bar (click the arrow for a drop-down list of defined names) or by choosing Home-Editing-Find-and Select-go and specifying the name range.
- Macros are more comfortable to create and maintain when you use range names rather than cell addresses.
The scope of a name:
It is important to note that when creating and working with names, that you understand that all names have a scope. A name’s scope defines or specifies where you can make use of that name, and they are scoped at two levels.
- Workbook-level names: Can be used in the worksheet. This is the default type range name.
- Worksheet-level names: Can be used only in the worksheet in which they are defined, except they are preceded with the work sheet’s name. A work can contain multiple worksheet-level names that are identical.
You can refer to a work-book-level name just by using its name from any sheet in the workbook. For worksheet-level names, you must precede the name with the name of the worksheet unless you are using its own worksheet. It may sound confusing, but the example below will put it in a better perspective.
For example, let’s assume you have a workbook with two sheets, Sheet1 and Sheet2. In this workbook, you have Tedy_Speech (a workbook-level name), Nerdy_Speech (a worksheet-level name on Sheet1), and Serdy_Speech (a worksheet-level name on Sheet2). On sheet1 or Sheet2, you can refer to Terdy_Speech by simply using the name:
If you are on Sheet1 and you want to refer to Nerdy_Speech, you can use a similar formula because Nerdy_Speech is defined on Sheet1:
However, if you want to refer to Serdy_Speech on Sheet1, you will need to do a little more work. Sheet1 can’t “see” the name Serdy_Speech because it’s defined on another sheet. Sheet1 can only see workbook-level names and worksheet-level names specified on Sheet1. To refer to Serdy_Speech on Sheet1, prefix the name and an exclamation point:
Here is another good tip for you—if your worksheet name contains a space, enclose the worksheet name in single quotes when referring to a name defined on that sheet:
In general, it’s a good practice to scope your names as narrowly as possible. If you want to use a name on only one worksheet, set the name’s scope at the worksheet level. For names that you want to use throughout your workbook, a workbook-level scope is more appropriate.
Using worksheet-level names can be sometimes confusing because Excel allows you to specify worksheet-level names even if the workbook contains the same name as a workbook-level name. In such a case, the worksheet-level name takes primacy over the workbook-level name but only in the worksheet in which you defined the sheet-level name.
If for instance, you can define a workbook-level name of SUM for a cell on Sheet1. You can also define a worksheet-level name of Sheet2!SUM. When Sheet2 is active, SUM refers to the worksheet-level name. When any other sheet is active, SUM refers to the workbook-level name. at this stage, I’m guessing you are already probably getting confused but just stay with me. To make it easier and less confusing, it is better to play it safe by simply avoiding the use of the same name for both workbook and worksheet levels.
In conclusion, I hope these few naming tips will go a long way in reducing the problems associated with naming in Excel for beginners and average users as well.
© Respect Azubike