Dec 16, 2014

I had a student recently say that she “didn’t need to know formulas in Excel” as she “didn’t work with financial spreadsheets.” Formulas, however, do a lot more than work out a company’s balance sheet. There are formulas for many different purposes, so no matter what you use Excel for – there’s a formula for you. There are around 350 worksheet functions in Excel. Functions are basically built-in formulas. The most commonly known function is SUM which is designed to simply add up numbers. The other 349 do a multitude of different types of task. These can involve numbers, text, dates and/or times, and sometimes a combination of all three. Text Functions Below are some of the many text-related functions. These can be useful for cleaning up imported or incorrectly keyed-in data. Case Functions – UPPER, LOWER, PROPER These three functions convert text to a different case, so if names are in lower case, they can be converted to proper (title) case. Cleaning Functions – CLEAN, TRIM CLEAN removes non-printing characters and TRIM removes excess spaces. Find and Replace Functions – FIND, LEFT, RIGHT, MID, REPLACE, SEARCH, SUBSTITUTE These functions either locate the position of a character or replace part of a text string. Number to Text Conversion Functions – CODE, DOLLAR, NUMBERVALUE, VALUE These functions convert text into numbers and similar actions. To find out more about these types of functions as well as many others, take a look at New Horizons’ Excel 2010 Level 4 training program.