Announcements

Got USB?

If you do, then here's something that's an absolute must have. You see, it's been my experience that you can never have enough places to plug in USB accessories. It's not a matter of if you'll need more space, it's a matter of when. Well, we have a solution to that little problem. It's the Targus Mini 4 Port USB Hub.

Unfortunately, most USB hubs are big and bulky - this one isn't (gotta save that desktop space). This high-end hub is small, compact, and is easy to keep out of the way. Plus, there's really no setup to it - just plug it in (with its built in 12" cord) and go. You've just turned one USB port into four.

It's also made to travel, so those of you with laptops can easily take it along for the ride. The included USB cord wraps neatly into the bottom of the unit.

The price on this little bugger is fantastic - only $18.97 and US shipping is free! You'd pay more than that for a regular, bulky, low-end hub!

If you use USB, then this is a must have. Check it out at the link below - but hurry. This was a one-time buy and once it's gone, it's gone:

http://www.worldstart.com/store/targus-usb-hub.htm

PS - This is one of those things that you'll wish you had after you buy a new toy. You go home, try to plug your newest find into your computer only to discover you don't have any open USB slots. When that happens to you, be ready with this handy little hub!

MS Office 101

Did you know that...

... when you have a title at the top of a column of data that you can use that title in formulas—instead of the cell reference.

For example:

Let's suppose that you have two columns labeled "Original Price" and "Discount". Now let's say you want to create a third column that calculates the sale price of the items (Original Price - Discount).

Your formula for the third column might look something like this:

=A2 - B2

Not bad—but certainly not very meaningful, unless you take the time to figure out what's in columns A and B.

Wouldn't this be a bit better?

=Original Price - Discount

Yeah—I thought it was easier to understand too.

The good news is that you don't have to do anything special. Once you've put the column titles in, Excel can figure out that the data in the column goes with the title.

Cool!

However, this only seems to work on the same worksheet. In other words, you can't use the formula on a different worksheet from where the Original Price and Discount data is located.

But...

What if there was a quick and easy way to do the same thing on other worksheets in the workbook?

Would it be worth a couple of seconds to "Name" the data so you could use meaningful formulas anywhere in the workbook?

Think about it—less tracking of cell references to figure out what each cell is representing. It could make life easier...

Want to know more about naming Excel ranges?

Yes?

I was hoping you'd say that.

Keep on reading—it's time to play the Game of Names in MS Excel.

Office Tip of the Day

Playing The Name Game

I'm glad to see that you've decided to come along for this little adventure.

I think the best place to start will be with a better understanding of what it means to name ranges of cells in Excel.

Basically, naming a range (or a single cell for that matter) is to give Excel a meaningful word (or words) that you will refer to the selected set of cells by.

Just like we did above with the column titles—only better.

Better because we can then use the name on any worksheet in the workbook—not just the one where the data appears.

OK. So now that we have a basic understanding of what naming is in Excel, I think it's time we learn how to actually get the naming done.

Once you've decided that you want to name a range of cells you really have a couple of different choices.

One method is to use the Name box in the formula bar. Here's what to do.

• First, highlight the data to be named.

• Now click into the Name box.

  

• Once you've got the cursor in the Name box you're ready to type in the name of the data. (Make sure that the cell reference that was there is deleted during this process.)

• And finally, either hit the Enter key or simply click out of the Name box.

Not too bad—easy as can be—with no extra windows to open or close. (That's always a bonus in my book.)

Another method would involve using the titles already given to the column or row.

• The first step here is to highlight the data and the column/row title.

• Now go to the Insert menu, Names submenu, Create choice. (Alt, I then N then C will also work.)

• A little window for creating names will open.

  

• From the list you need to pick where the name is located - i.e. where the column/row title is located. Excel will try to select this for you based on the highlighting you did so you may find it's ready to go right away.

• Once you've made your selection click OK.

And, once again, that's it. You're done. The range has been named and you're on your way.

I believe that this would be a good time to discuss some restrictions on how you name your ranges. The Office Assistant lists several items, so it's probably a good idea to list those as he does.

• The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.

• Names cannot be the same as a cell reference, such as Z$100 or R1C1.

• Spaces are not allowed. Underscore characters and periods may be used as word separators—for example, First.Quarter or Sales_Tax.

• A name can contain up to 255 characters.

• Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

Also, you might be interested to know that the Name box is also a pull down list of all named ranges in the workbook.

So... if you need to get somewhere in a hurry simply pull down the list and select the name of the range you need to see. Instantly you'll be taken to the location you're looking for.

Now that you know the facts, don't sit on the bench—get into the game. It might just take some of the intimidation out of those confusing Excel formulas.

~ April