Excel: The Name Box and Named Ranges

The Name Box

Most of us don’t give a lot of thought to the humble Name Box in Excel. We glance up at it occasionally to see which cell we have selected, but few people realize that there is a lot more than meets the eye with this feature.

Did you know you that this little box allows you to name a cell or range of cells? I could even rename a cell after myself.

You will be shocked to hear how easy this is… all I did was type over the cell name in the name box and suddenly “A1” became “Ali”.

Exercise Download

Let’s see how this works with a range of cells. For this, follow along with this document: NamedRangesExercise .

This data will look familiar to those of you who have taken Excel Essentials. It is a list of fictional employees, departments and salaries.

…By the way, you might find it useful to resize the name box for today’s exercise. This can easily be done by hovering your mouse to the right of the name box until you see a double headed arrow. Then simply click and drag.

Named Ranges

I would like to name the Salary data so I can easily find and select it.

Select Cells E1 through E22
- Remember: to select a cell in Excel you hover your mouse over the first cell, and you are looking for your cursor to turn into a white cross for Excel to select it. Then click, hold down the mouse and drag down to the bottom of the list.

In the Name Box click over the existing cell name (in this case, E1),

Then replace it with the word Salary and press enter.

Click onto any other cell to deselect the range.

Notice there is now a dropdown that appears in the name box now with your newly created Salary moniker.
- Test it out! Select it from the dropdown… it should select your entire range.

Named ranges allow users to quickly find and select a range of cells. Guess what else named ranges help you do? Formulas!

Formulas with Named Ranges

I would like to quickly figure out a 10% bonus for my employees based on the Salary range.

In F2, type = Salary*.1

Before you even finish typing salary, you will probably see something like this.

Press Tab to accept the range or select it with your mouse.

The end formula looks something like this:

Then use AutoFill to take that formula down to the bottom of the list.
- Remember the trick we talked about in Advanced Formulas for a quick AutoFill? Hover your cursor in the lower right until you see a black plus sign, then double click to carry the formula down.

Managing Named Ranges

Inevitably, someday you will want to manage your named ranges. They live in the Formulas tab, in a button titled Name Manager.

Select Name Manager to see your full list of named ranges, alter, or delete them.

Have you thought of a way you will use Named Ranges? Tell me about it!

Word: Citations and Bibliography

In Word Essentials training we talk about one time saving option in the References tab, the automatic Table of Contents. If you thought that was cool, you are going to want to go back in time and redo all of your high school and college papers using the Citations and Bibliography features in the References tab.

Exercise File

To follow along, download the exercise file here: CitationsExercise

This document contains a lovely writeup about Microsoft Word that needs a couple citations (indicated by placeholders that read Source 1 and Source 2), and it also needs a Works Cited/Bibliography section. The two concepts go hand in hand, as we will see in the exercise. The yellow text box contains the source information for these citations. But enough back story, let’s jump in.

The Ribbon

For this exercise, we will work from the References Tab, Citations and Bibliography Group

Step 1: Choose a Citation Style

In the Style dropdown, select the desired citation style; e.g. APA, MLA, Chicago.

The style you select will guide the fields that will appear in your Works Cited section

Step 2: Insert Citations

Remove the bold placeholder text that reads Source 1, and place your cursor at the end of the sentence where the placeholder text had been. Wherever your cursor is flashing is where the citation will appear.
From the References tab, Citations & Bibliography group, select Insert Citation, Add New Source
For Type of Source select Article in a Periodical. Complete the fields that appear in the prompts by using the information in the gold text box. Press OK.
- Notice the Citation inserts where your cursor was:
Now replace Source 2 placeholder text the same way, only select Journal as the Type of Source.
- There are a couple additional fields you might want to add: Volume and Number.
- Even though that doesn’t appear in the Fields list, you can check the box next to Show All Bibliography Fields and scroll down to see more fields and enter this additional information.

Step 3: Insert a Bibliography or Works Cited

Now the easy part!

Either remove the text box or click underneath it. The Works Cited will insert wherever your cursor is flashing.
Go to the Citations & Bibliography group and select from the list of options.
A Works Cited list will appear where your cursor was.

Making Adjustments: Managing Sources

I see a typo from my data entry, so I want to change my source and update my Works Cited.

To edit or manage sources:

Go to References tab, Citations & Bibliography group, select Manage Sources.
Select the source to be edited and select Edit to make changes (optional).
If any sources are edited or added, the Works Cited will need to be updated, just like the automatic Table of Contents we created in Word Essentials. The process is the same: right click over the Works Cited and select Update Field.

By the way, this is just the beginning of what you can do working with citations. There are also ways to transfer sources from old documents to new documents, to change citation styles mid paper, and so much more… but I promised a bite sized piece of information, so I must stop myself here!

I think you should go back to school and take a class or two just so you can play with this amazing feature in Word. I know a place where you can take some classes…

Outlook Formatting Woes: Replying to Email Sent from a Phone

You have probably experienced this scenario:

On your desktop Outlook you open an email that a coworker sends you from her phone.

You notice that the formatting looks sparse, but you don’t think much about this until you start to compose your reply. Your signature looks different than usual, you can’t format text, or change fonts… even doing something simple, like making a word bold or italic, is impossible as a good portion of the ribbon is greyed out and unselectable. What is going on here?

When your coworker emailed you from her phone (or tablet), most likely the formatting of the entire email changed over to Plain Text. This means minimal to no formatting and no images, even for your responses to her email.

How to Fix

Want to hear the good news? You can fix this in two clicks.

Important: first, be sure that you are replying in a popped out message pane. We talk about this Pop Out view a lot in Outlook Essentials training, and how it opens up a whole new world of opportunities in the Outlook ribbon. This button lives right above the Send button in the Reply preview pane.

Okay, are you ready for the two steps?

Go to the Format Text Tab
In the Format group, change the selection from Plain Text to HTML by pressing the HTML button.

Go back to your Home and Insert tabs, and you will see that all of your options have returned. Happy day! Heads up though, if your signature is set to automatic, you may need to reinsert your signature to bring back the formatting in the signature.

I hope this tip prevents some frustration for you in the future. No exercise files today, unless you would like me to send you an unformatted email from my phone to test out this setting… if so, you know where to find me!

Excel Text Formulas

We usually think about formulas in Excel in terms of numbers, but there are also a handful of formulas and functions for text. The good news is, these functions are not only incredibly useful, they also happen to be user friendly! (we are looking at you, complicated nested IF formulas)

Perhaps you have inherited a list of names in all lower case or all caps that you want to return to proper case… or vice versa, the list is in proper case and you long for a list in all caps (no judgement). Here is a quick way to transform that list in to the text case you want.

Follow along with the attached document NameList.

Let’s do Proper Case first as our template: