In this article, we'll demonstrate some of Excel's functions and features that a financial professional can use to make his or her job more efficient. This article does not discuss Visual Basic for Applications (VBA), but instead focuses on Excel features that non-programmers can deploy. Only basic knowledge of Excel is needed to make use and benefit from this program, so read on to learn about how it can make your job easier.

**Inserting Functions**

**Excel comes with a wide array of functions that can easily be inserted into a spreadsheet. In Microsoft Excel 2010, adding a function is as easy as clicking on the "Insert Function" button from Formulas > Insert Function**

Figure 1 |

For example, if you click on the "Insert Function" icon, the following window will appear:

Figure 2: Insert Function |

In addition to financial functions - such as present value, future value, payment and internal rate of return - that are of use to financial professionals, Excel has many functions that are useful in cleaning up and reconciling large data sets, a task frequently encountered by many in finance. Some of these functions are:

*EXACT*- Checks whether two strings of text are precisely the same and will return either True or False.*LEFT, RIGHT or MID*- Returns the characters from a text string given a starting position and length, such as the left side, middle of the text string or the right side.*TRIM*- Will remove, with the exception of single spaces in between different words, all spaces in a text string.*TRANSPOSE*- This function will convert a range of cells aligned vertically to a range of cells aligned horizontally, or vice versa.

**Look-Up tables**

"Look-up" tables are usually a part of any financial spreadsheet or model. In Excel, the "Look-Up" function searches for values in a table based on a certain condition. For example, if you use on-the-run Treasury yields as a benchmark pricing for other bonds, a "Look-Up" table could be used to pull in the appropriate Treasury yield. (To help you learn this function we suggest you open up your own Excel spreadsheet and follow along.)

Figure 3: Look-up table |

Creating a "Look-Up" table is a simple, two-step process.

1. A "Look-Up" table must be sorted in ascending order by the first column.

- Using the cursor, highlight the entire data table.
- Click on "Data."
- Click on "Sort."

- Using the cursor, highlight the entire data table.
- Click on "Insert."
- Click on "Name."
- Click on "Define."
- Key in a name

"Look-Up" tables have many uses beyond pulling in information for securities prices. However, we note that a simple securities pricing spreadsheet, as shown in the example above, can be made even more efficient by using the features offered by most pricing services, such as Bloomberg, which allow spreadsheets to link directly to live price feeds. For example, the Treasury yields in the benchmark "Look-Up" table above could be pulled directly into the table as a live link from a pricing service.

**Looking Up a Value Based on Two Conditions**

As demonstrated above, a "Look-Up" table can be used to pull in values based on a certain condition. The user must identify that condition and provide the column in which to find the value that is to be returned. (In the example above, the condition was the name of an on-the-run Treasury bond, and the column was the second column, which contained that particular bond's yield). There is a way to use a "Look-Up" table to return values when the column to be returned is not constant. In other words, based on certain variables, you might want to return the second, third, fourth, etc., columns of a "Look-Up" table. To do this, you must create a "Look-Up" table function within a "Look-Up" table function.

For example, the table below shows the value of mortgage servicing based on loan size and loan-to-value ratios (LTV). Let us call this table the "Servicing Table."

What if you wanted to pull the value of servicing into a spreadsheet for multiple loans with varying sizes and LTVs? In other words, the column number of the data you want to retrieve is not constant.

Figure 4: Spreadsheet example |

Figure 5: Table small |

Now you can write a formula to find serving values based on both the variable of loan size and the variable of LTV as shown below.

Figure 6: Vlookup |

**The "Exact" Statement**

The "Exact" statement (or function) is very useful in working with large sets of data, such as securities, where values within a spreadsheet vary by small amounts (for example, CUSIP numbers). You can use the "Exact" function to ensure that you are pulling in the actual value that you need, or to identify why you might not be able to find a value that you believe should be in the data set.

For example, when using the "Look-Up" function as described above, you must sort the "Look-Up" table by the first column in ascending order. The "Look-Up" function then searches for values in that first column. If a value in the "Look-Up" function cannot be found in the "Look-Up" table, the "Look-Up" function will find the next closest value - this is generally not good in financial spreadsheets, as exact figures are usually required.

For example, if the "Look-Up" function is searching for CUSIP number 912833WZ

**3,**which is not found in the "Look-Up" table, but 912833WZ

**4**is in the "Look-Up" table, the "Look-Up" function will return the value in the specified column number for the 912833WZ

**4**CUSIP number. This is simply not the correct CUSIP number.

Figure 7: Exact function |

Another common use of the "Exact" function is to figure out why you cannot find a value in a set of data when you "know" it is there. For example, you might be trying to reconcile two sets of data by CUSIP number. You know a certain CUSIP number is in both sets of data, but the formulas you have written aren't recognizing one of the CUSIP numbers as being the same as the other. Simply key the CUSIP number into both spreadsheets, and use the "Exact" function to let Excel tell you whether they are the same - there could be an unseen character in the data, such as a space before the CUSIP number that is causing the problems, which the "Exact" function will help you identify.

Figure 8: Exact function #2 |

**Using Arrays**

"Arrays" is a powerful feature in Excel that allows you to make calculations within large sets of data based on multiple conditions. For example, using "Arrays," you can sum the value of integrated oil stocks with a certain market cap within a large set of data that consists of stocks from many different industries and with many different market caps, and you can calculate the weighted average price of those stocks. Creating "Arrays" is a simple process, and the first step is to name the "Arrays."

1. Name the "Arrays."

- Using the cursor, highlight the entire set of data, including the column headers (the data must have column headers as they become the names of each "Array").
- Click "Insert" on the tool bar.
- Click "Name."
- Click "Create."
- Mark the "Top Row" check box only and click "OK."

Figure 9: Array |

Figure 10: Array chart |

*Important*: The "{ }" brackets shown in the formulas are not keyed into the "Array" function, but once the "Array" function has been created, you must simultaneously hit Shift, Control and Enter on your keyboard to activate the "Array" - this creates the "{ }" brackets.

"Array" Tips:

1. "Array" names that consist of more than one word are automatically named with a "_" to separate each word. For example, market cap becomes "Market_Cap," as shown above.

2. The vertical ranges of each "Array" used in an "Array" function must be identical. In other words, one column of data in a table of "Arrays" cannot be longer than the others.

3. You are not limited to conditions as shown in the examples above. You can create up to seven conditions using the "IF" function. Each condition is separated by a "*", as shown above.

**The Bottom Line**

Microsoft Excel has many features and functions that can add value and be used to create great efficiencies. One of the best ways to learn about and master these features and functions is to use the "Insert Function" feature. Creating "Look-Up" tables can create efficiencies, and mastering the use of "Arrays" can create vast efficiencies. Finally, don't forget about the simple functions like "Exact" or "Trim," as they can save you hours of frustration when reconciling data.

*Note: All images for this article were taken from Microsoft's Office Excel program. Microsoft holds all copyrights to these images.*