MS Excel Formula 101

This function joins separate pieces of text into one item.

• ABS

• CONCATENATE

• CLEAN

• TRIM

This function removes unwanted spaces from a piece of text. The spaces before and after the text will be removed completely. Multiple spaces within the text will be trimmed to a single space

• ABS

• CONCATENATE

• CLEAN

• TRIM

This function removes any non-printable characters from text. These non-printing characters are often found in data which has been imported from other systems such as database imports from mainframes.

• ABS

• CONCATENATE

• CLEAN

• TRIM

This function calculates the value of a number, irrespective of whether it is positive or negative.

• ABS

• CONCATENATE

• CLEAN

• TRIM

This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.

• LOOKUP (Array)

• LOOKUP (Vector)

• VLOOKUP

• HLOOKUP

This function counts the number of numeric entries in a list. It will ignore blanks, text and errors.

• COUNT

• COUNTA

• COUNTBLANK

• COUNTIF

This function counts the number of numeric or text entries in a list. It will ignore blanks.

• COUNT

• COUNTA

• COUNTBLANK

• COUNTIF

This function converts the first letter of each word to uppercase, and all subsequent letters are converted to lower case.

• Lower

• Proper

• Upper

• None of the above

This function picks the highest value from a list of data.

• Min

• Mid

• Median

• Max

This function counts the number of characters, including spaces and numbers, in a piece of text.

• COUNT

• LEN

• CHAR

• COUNTIF