# Named Cells, Ranges and Values

Formulas and functions are usually created using references to other cells on the spreadsheet. Where you need to refer to a fixed cell and still be able to duplicate the formula around the spreadsheet then you can use fixed cell references as explained in the Simple Formulas and Functions reference guide.

These absolute and mixed cell references can sometimes make the formulas difficult to read, especially when you get into creating the likes of VLOOKUP and SUMIFS formulas. Therefore we can name cells and ranges or set a value to a name for use in formulas to avoid this.

There are multiple approaches to naming cells. First you need to know where to see the names you create. All cells in Excel have a default name – their cell reference, which is displayed in the **Name**

**Box**. The Name Box is located next to the formula bar underneath the ribbon.

To name a cell or range using the Name Box:

- Select the cell or range to be named
- Click into the Name Box
- Type in the desired name

*This name can be made up of a combination of letters, numbers, full stops or underscores. It cannot be the same as cell references and can’t include spaces. It must be less than 255 characters in length and is case sensitive* - Press
**Enter**key on keyboard

To name a cell or range using the ribbon:

- Select the cell or range to be named
- On the
**Formula**tab, in the**Defined Names**group click the**Define Name**button - In the
**New Name**dialog box type in the desired name into the**Name**field - If required add a comment
- Click
**OK**

If you have set out a table in your spreadsheet with titles at the start of each column and row within the table then you can use these titles to create named ranges.

To name cells based on table headings:

- Select the table containing the cells to be named
- On the
**Formula**tab, in the**Defined Names**group click the**Create from Selection**button - In the
**Create Names from Selection**dialog box, choose the options which indicate where the names to be used are - Click
**OK**

Names need to be unique within a spreadsheet file (workbook) and as such can be used to navigate through the workbook.

To navigate to a cell or range using its name:

- Click on the arrow head in the
**Name Box**

- Choose the desired name

Names can also be used for fixed values which you do not want to display on the worksheet.

To create a named value:

- On the
**Formula**tab, in the**Defined Names**group click the**Define Name**button - In the
**New**Name dialog box type in the desired name into the**Name**field - If required add a comment
- In the
**Refers to**box enter the value or formula for the named value - Click
**OK**

You can use the name of named cells, ranges or values in any formula in place of a cell reference via the button, on the **Formula** tab, in the **Defined Names** group.

# Using Built-In Functions

## The SUM Function and the Formula Palette

The SUM function was discussed in the Creating Simple Formulas and Functions Reference Guide.

Here we will look at creating this function using the **Formula Palette**. Being able to work with the **Formula Palette** enables you to create a formula using any of the built-in functions within Excel.

The **Formula Palette** can be accessed in a number of ways. Both the button on the formula bar and the button in the **Function Library** group on the **Formulas** tab, both open the **Insert Function** dialog box which allows you to search for the desired function.

The **Formula Palette** for your chosen formula will open.

The Formula Palette dialog box is titled **Function Arguments**. Here you create all the parts or **arguments** of your formula.

Each box represents an **argument** or part of your formula. Where the title of the box is in bold that argument is essential whereas titles not in bold indicate arguments which are optional.

As you fill in the formula palette with arguments the formula will be building up in the formula bar to give the completed formula.

## The SUMIF Function

The SUMIF function adds up cells where a given condition is met. For example it can be used to add up values for a specific advisor.

As well as opening the Formula Palette via the buttons you can choose the desired function from the **Function Library** on the **Formulas** tab.

The SUMIF function can be found in the **Math & Trig** book of functions.

The example above shows a SUMIF being built to give the total for Pete.

In the formula palette, the **Range** has been set at cells A1 to A13, where the names of the advisors are listed. The **Criteria** is set as cell A16, which also has the name Pete in it to show that this is the total for Pete. Criteria can be entered directly into the formula palette, if preferred. The **Sum Range** is an optional argument. If no sum range is supplied the matching cells in the **Range **will be totalled.

## The SUMIFS Function

The SUMIFS function can be found in the **Math & Trig** book of functions from the **Function Library** on the **Formulas** tab.

The SUMIFS function was introduced in Excel 2007 and so was not available in Excel 2003 and earlier versions.

This function allows you to specify multiple criteria to identify the records to be totalled.

In this example the range to be totalled (**Sum_Range** argument) is C1 to C13, where A1 to A13 (**Criteria_range1** argument) contains the same as cell B15 (**Criteria1** argument) AND where B1 to B13 (**Criteria_range2** argument) contains the same as cell A16 (**Criteria2** argument).

Or in other words, the values are to be added up for investments in fund A as recommended by Pete.

## VLOOKUP and HLOOKUP Functions

VLOOKUP and HLOOKUP both do the same thing. They look up information in the leftmost column of a table and then return a value in the same row from a column you specify. By default the table should be sorted in an ascending order.

The difference between the two functions is in the layout of the table the information is looked up from. If the table is arranged with the headings on columns and each row being a set of data (vertically) you use VLOOKUP. If the table is arranged with the headings on the rows and each column being a set of data (horizontally) you use HLOOKUP. Therefore HLOOKUP is hardly ever used.

Argument |
What to include |

Lookup_value |
The cell reference of the information you want to find a match for in the reference table to identify the record you want to pull information out of. |

Table_Array |
The cell reference of the table you are looking the information up from. To make this formula easier to read it is a good idea to name the table and use its name rather than cell references here. The table can be on the same sheet, a different sheet or even in a different workbook. |

Col_index_num |
The number of the column which contains the information you want to see as the result of the formula. The first column of the table is always 1 regardless of the column letter. Then count on till you determine the column number for the column you require. |

Range_lookup |
If your table is sorted in ascending order and you want the closest match you can either leave this box blank or type in TRUE. In all other cases you must type FALSE in this box. |

### Sample VLOOKUPs

The VLOOKUP and HLOOKUP functions can be found in the **Lookup** book of functions from the **Function Library** on the **Formulas** tab.

## The TODAY Function

The **TODAY** function provides the current date (based on your computers clock). This is very useful when calculating with dates and producing forms to name just two.

The **TODAY** function is also one of the simplest of Excel’s many functions as it needs no additional information (arguments).

The **TODAY** function can be found in the **Date & Time** book of functions from the **Function Library** on the **Formulas** tab.

# Tracing And Correcting Worksheet errors

If there is an error in a formula it can be difficult to track down. Fortunately Excel has several tools that can help.

When you click into the formula bar to edit the formula, cell references are shown in different colours, with the cells they refer to surrounded by a line in the same colour.

This way you can see the cells used but only whilst editing the formula.

## Using Trace Arrows

Instead of using the coloured cell indicators when editing a formula, you can display trace arrows, which show which cells are referenced in a formula even when you are not editing the formula.

To display trace arrows:

- Select the cell containing the formula
- Click
**Trace Precedents**in the**Formula Auditing**group on the**Formula**tab - The trace arrows will appear

To remove trace arrows:

- Click
**Remove Arrows**in the**Formula Auditing**group on the**Formula**tab - Select
**Remove Arrows**

## Using the Evaluate Formula Feature

The **Evaluate Formula** feature demonstrates how a formula is being calculated and therefore can be particularly useful in tracking down errors that arise.

To use the **Evaluate Formula** feature:

- Select the cell containing the formula

- Click
**Evaluate Formula**in the**Formula Auditing**group on the**Formula**tab - The
**Evaluate Formula**dialog appears - The first part of the formula that Excel will process is underlined
- To follow through each step, click the
**Evaluate**button

- The result is displayed. Each click of the
**Evaluate**button will display the next step until the final result is displayed

- To see the whole process again, click
**Restart**else click**Close.**