More Functions in Excel

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:

  1. Select the cell or range to be named
  2. Click into the Name Box
  3. 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
  4. Press Enter key on keyboard

To name a cell or range using the ribbon:

  1. Select the cell or range to be named
  2. On the Formula tab, in the Defined Names group click the Define Name button
  3. In the New Name dialog box type in the desired name into the Name field
  4. If required add a comment
  5. 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:

  1. Select the table containing the cells to be named
  2. On the Formula tab, in the Defined Names group click the Create from Selection button
  3. In the Create Names from Selection dialog box, choose the options which indicate where the names to be used are
  4. 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:

  1. Click on the arrow head in the Name Box
  2. 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:

  1. On the Formula tab, in the Defined Names group click the Define Name button
  2. In the New Name dialog box type in the desired name into the Name field
  3. If required add a comment
  4. In the Refers to box enter the value or formula for the named value
  5. 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:

  1. Select the cell containing the formula
  2. Click Trace Precedents in the Formula Auditing group on the Formula tab
  3. The trace arrows will appear

To remove trace arrows:

  1. Click Remove Arrows in the Formula Auditing group on the Formula tab
  2. 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:

  1. Select the cell containing the formula
  2. Click Evaluate Formula in the Formula Auditing group on the Formula tab
  3. The Evaluate Formula dialog appears
  4. The first part of the formula that Excel will process is underlined
  5. To follow through each step, click the Evaluate button
  6. The result is displayed. Each click of the Evaluate button will display the next step until the final result is displayed
  7. To see the whole process again, click Restart else click Close.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s