SPREADSHEET TIPS & TRICKS: The “Trim” Function

Ever had a data series that needed cleaning? This function removes spaces from the front and back of a set of characters in an Excel cell. For example, you may need to perform the Vlookup function on a list of client codes in an Excel spreadsheet.

SPREADSHEET TIPS & TRICKS: Text to Columns…

THE PROBLEM: You are given a list of email addresses and you wish to extract the First Name, Surname and Company Name from the list. The majority of the emails come in the format firstname.surname@company.com. Separating the information manually will be time consuming; you need a quick solution.
THE SOLUTION: Use the Text to Columns function as per the screenshots below…

SPREADHSEET TIPS & TRICKS: Left & Right...

The left and right functions allow you to extract a specified number of characters from a data field in Excel. For example, the below screenshot shows a time series data dump of currency rates, dates and times from Reuters. The date field has come in the format DD/MM/YY HH:MM:SS. You may wish to separate the date from the time in this field. One way to do so is to use the Left and Right Functions, as per the screenshots below…

SPREADHSEET TIPS & TRICKS: Upper, Lower, Proper...

Here are some useful functions to help tidy a spreadsheet that contains non-numeric data. If, for example, you are given a list of names and addresses, in all lower or all uppercase, but you really need them to be in the proper format, with the first character in uppercase only, then the “proper” function can be used. Screenshot A shows an example. It’s a quick and easy way to convert a list of incorrectly formatted non-numeric data that would otherwise be timely and manual process...

SPREADSHEET TIPS & TRICKS: Consolidating Data Lists...

Do you work in sales & trading? Have you ever had the head of your desk approach you and say, "Could you just run me a quick report to find out how many clients have dealt with us during 2009?"... Have you said "Yes" but thought to yourself, "Do I look like a spreadsheet jockey? Why is he getting me to do that? And more importantly; How on earth do I do that...?"

First, the excel tip, then the career advice...

SPREADSHEET TIPS & TRICKS: The F5 Key (Part 2 of 2)

Hit the F5 Key on your Keyboard within an Excel Spreadsheet and click the Special... button to show the tool in the below screenshots.

The Special... key is a powerful search and identification tool within Excel. To set the scene, when building a financial model, one must know which cells contain formulas and which cells contain hard coded data (numbers punched in manually). The creator of the model will not want new users to overtype the cells that contain formulas with hard coded numbers, otherwise formulas will be lost. To avoid this, the financial modeller will use a colour coding system, whereby all "entry" cells are filled in yellow and have blue coloured text, and all cells that contain formulas, and therefore are not to be overtyped, are left with a black font and clear cell colouring.

The screenshot shows an example of a completed DCF model. However, there lies one problem for the creator of this model, in that he or she cannot quickly remember which cells contain formulas and which are hard coded.

SPREADSHEET TIPS & TRICKS: The F5 Key (Part 1 of 2)

What does the F5 key do?

The F5 key brings up the Go To... dialogue box. This is useful for financial modellers who like to use named cells or cell ranges. It is a quick way to bring up a list of all named cells or cell ranges within Excel files. Equally if one is auditing another's spreadsheet and would like to check what named cells or cell ranges the original modeller has used, if any, then the F5 key offers a quick way to do so.

In the pictoral example, below, the modeller has many cell ranges and the F5 key is an easy way to view which names have been allocated to which cells or cell ranges. If the modeller has forgotten which cells each range refers to, they can simply double click an item on the list and Excel will highlight the relevant cell on the relevant tab within the Excel file.