The Secret to User Friendly Excel Worksheets

The Secret to User Friendly Excel Worksheets

By Marie McCooey

Let’s admit it.

Excel formulas can be difficult to read and understand.

Have you ever received a complex Excel worksheet you needed to update?

You pulled out your hair trying to decipher the long formulas. You tried to keep track of which cell was referring to which cell but lost your way. And while clicking through the sheets, you felt exhausted and fed up.

It doesn’t have to be so difficult.

Let me introduce you to Names.

Names can make your worksheets much simpler, easier to understand and manage.

What is a name?

A name is an easy to remember, short text description you create to identify:

  • a cell
  • a range of cells
  • a constant
  • a formula

Instead of using confusing cell adddresses, simplify your formulas with user friendly names.

For instance, instead of entering the cell range C6:C14, to indicate January expenses, name the range C6:C14, January and enter the name January into the formula.

Example of Range Name

 

How to make enormous workbooks manageable

Let me explain.

1. Large workbooks with many worksheets can be overwhelming to manage and work with. Names applied to cells are easier to remember than actual cell addresses.

2. Names use absolute cell references e.g. Formulas!$C$8:$C:13.
An absolute cell reference means the name stays fixed on a specific cell or cell range. Copying and pasting the formula containing the name to other cells, does not change the named cells or cell range.

3. Names apply to the entire workbook.
Working on another worksheet in the same workbook? Do you need to reference a cell or cells on another worksheet? Instead of clicking on the worksheet tab and endlessly scrolling around to find the cell range, type the cell range name directly into your formula.

4. Use names to help you move quickly around your workbooks:
From any cell in the workbook, click the Name box drop-down arrow to display a list of all the names in the workbook.
List of Names
Click any name to instantly move to the related cell or range.

Not only that.

The keyboard shortcut, F5, otherwise known as the GoTo key, quickly displays a list of workbook names to help you navigate your worksheets.

Names in Workbook

3 common mistakes to avoid when creating names

Mistake #1: Including spaces in a name

Names:

  • Can be a mix of upper and lower case
  • Can be up to 255 characters long
  • Can contain letters, numbers, periods and underscores
  • CANNOT include spaces, e.g. Tax Rate

Mistake #2: Begin with a number

Names must start with:

  • A letter
  • An underscore “_”
  • A backslash “\”
  • CANNOT start with a number, e.g. 1Qtr_Expenses.

Mistake #3: Creating a name that is a cell address

  • Single letter names are acceptable, except for the letters R and C
  • Names CANNOT be actual cell addresses, e.g. A$10 or R2C6.

 

Potential pitfalls to keep in mind when creating names:

1. Names apply to the cell, not the cell contents.If you change the cell contents, e.g. change the Commission Rate value to 10% the cell contents are updated and any other cells which rely on the Comm Rate are updated automatically.

2. It is possible for a cell (or range) to have more than one name.
If you select a cell or range of cells and type a new name into the Name Box, Excel creates a new name instead of changing the named range.

 

2 Easy Ways to create names

1. Use the Name box on the Formula bar.

a. Select a cell or range of cells.

b. Click in the Name box on the Formula bar.
The address of the active cell displays in the Name box.

Active cell address in Name boxWarnings:

– If the currently selected cell or cell range has an assigned name, the name box displays its name. Otherwise, you’ll see the address of the active cell in this box.

– If the name you type already exists, Excel will not apply the name to the selection. Instead, Excel selects the range attached to the name you typed.

c. Type the name in the name box, then press ENTER.
Use the name in any worksheet in the workbook.

2. Create a name using row and column labels

a. Select a range of cells including the row and/or column headings.

b. Click the Formulas tab.

c. Select Create from Selection in the Defined Names section.
Create names from selection option
The Create Names from Selection dialog box displays.
Create Names from Selection options

d. Select the appropriate Row/Column options and click OK.
In this case, select the Top row and Left column options and click OK.

e. Click the Name Manager button on the Formulas tab or click the Name box drop-down arrow to view the names.
The names are available for use in any worksheet in the workbook.

 

How to Manage Names?

You’ve created names for your constants and cell ranges and used the names in formulas.

But, now your worksheet has changed.

For instance, you hired another employee and the June Sales figures need to include the new employee’s results.

How can you update the names with the new information and delete names no longer needed?

Updating a Name’s Range

  1. On the Formulas tab, click the Name Manager button.
    All the workbook names display.
  2. Select the name to adjust and click the Edit button.
    The Edit name window displays the range name details.
  3. Click the Refers to button.
    The related range is highlighted.
  4. Select the new range and click the Refers to button to return to the Edit Name window.
    Click OK.
    The Name Manager displays the updated name.

Deleting a Range Name

  1. On the Formulas tab, click the Name Manager button.
    All the workbook names display.
  2. Select the name to delete and click the Delete button.
  3. Click OK when the confirmation message displays to delete the range name.

How to Use Names to Make your Formulas Easier to Understand and Maintain

You can easily enter a name in a formula by:

  • Typing
    Type the name in the formula, e.g. =SUM(JanuarySales).
  • Using Formula AutoComplete
  1. As you type a formula, the Formula AutoComplete drop-down list displays valid names.
    Available Names to use
  2. Press the down arrow key or continue typing to highlight the name.
  3. Press TAB to select the highlighted name.
    The name displays in the formula.picture
  4. Complete the formula and press ENTER when complete.
  • Selecting from Paste Name
  1. Start typing a formula.
  2. When you are ready to enter the named cell or cell range, press F3.
    The Paste Name dialog box displays.

Paste name options

  1. Select a defined name from the list and press ENTER.
    The name displays in the formula.
  2. Complete the formula and press ENTER.
    The Paste Name dialog box closes and the formula result displays.

Tip: You can use Names in all areas of Excel that accept a range of cells as an argument.

Applying Names

You know that using names is a good practice.

So you created names for every value in your complex workbook.

But, how can you update the formulas which still refer to cells by their addresses?

Use the Apply Names feature to make your formulas readable by replacing cell addresses with the names all at once.

  1. Click Define Name on the Formulas tab.
  2. Select Apply Names.
    Use Apply names to update formulas with names
  3. Click OK.
    Instantly, the names created for all row and column headings are applied to the ranges and formulas.

 

Start Using Range Names Today

What are you waiting for?

Creating user-friendly worksheets is easier than you think.

Leave a Reply

Your email address will not be published. Required fields are marked *