Overview:

This document is meant to provide a high level look at several commands available in Microsoft Excel.

Abs

This function returns the absolute value of a number. Negative numbers are converted to positive numbers and positive numbers are unaffected. =ABS(-3) returns a value of 3.

Autofill Shortcut

When the formula is in the first cell and you want to copy it the full length of a column, press Ctrl + Shift + the down arrow or Ctrl + Shift + End.

Choose

This function returns a value from a list using a given position or index. =CHOOSE(2,”red”,”blue”,”green”) returns “blue”, since blue is the second value listed after the index number.

Clean

This function takes a text string and returns text that has been “cleaned” of line breaks and other non-printable characters. =CLEAN(text)

Concatenate/Concat

When you concatenate cells in Excel, you combine only the contents of those cells. This combines values without a delimiter. =Concatenate(text1,text2)

If you want to join values in a way that includes commas, spaces, various punctuation marks or other characters enclose that character in quotation marks. =Concatenate(A1, “ “, B1) This will put a space between A1 and B1 text.

Countif

This function counts cells that meet a single criteria. It can be used to count cells with dates, numbers, and text that match specific criteria. =COUNTIF(range,criteria). =COUNTIF(A1:A10,”>32”) will count cells greater than 32.

Countblank

This function returns a count of empty cells in a range. Cells that contain text, numbers, errors, etc. are not counted. =COUNTBLANK(A1:A10) will count the number of blank cells in that range.

Find

This function returns the position of one text string inside another. =FIND (find_text, within_text, [start_num])

find_text - the text to find.

within_text – the text to search within

[start_num] – [optional] the starting position in the text to search. Optional, defaults to 1.

How to Calculate List Price using Gross Margin

Take the cell with the cost and divide that by (1-0.55) for example. This calculation would be for a gross margin of 55%. If the cost in a cell A1 was 2.86 for example, the formula in the cell to calculate the cost would be =A1/(1-0.55) which is 6.36.

IsError

This function returns TRUE for any error type excel generates, including #N/A, #VALUE, #REF!, #DIV/0, #NUM!, #NAME?, or #NULL!. You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when found. =ISERROR(A1) will return TRUE if A1 is displaying any one of the errors mentioned above, and FALSE if not.

Left/Right

For example, truncate six digits from the left in a cell.

Select a blank cell next to the string you want to truncate and enter =LEFT(cell,6)

To truncate 6 digits from the right in a cell, enter =RIGHT(cell,6)

Len

Use LEN function, it counts letters, numbers, characters and all spaces.

To use this function, enter =LEN(cell) in the formula bar, then press Enter.

Mid

The Excel MID function extracts a given number of characters from the middle of a supplied text string. =MID(”apple”,2,3) returns “ppl”.

Proper Case

If you need to clean up names that aren’t in proper case, you can use a simple formula based on PROPER functions. =PROPER(text)

Round

This function returns a number rounded to a given number of digits. This function can round to the right or the left of the decimal point. =ROUND(number,num_digits)

Number – The number to round

Num_digits – The number of digits to which number should be rounded

This is similar to ROUNDUP and ROUNDDOWN.

Sum

This function returns the sum of values supplied. It can handle up to 255 individual arguments.

Text

This function returns a number in a specified number format, as text. You can use this function to embed formatted numbers inside text.

Trim

This function strips extra spaces from text, leaving only a single space between words and no space characters at the start or end of the text. =TRIM(text)

Upper Case

This function is used to convert text to uppercase. =UPPER(cell reference)

VLookup

This allows you to lookup and retrieve data from a specific column. Lookup values must appear in the first column of the table, with lookup columns to the right.

The syntax is =VLOOKUP(value, table, col_index, [range_lookup])

Value – the value to look for in the first column of a table.

Table – The table from which to retrieve a value.

Col_Index – The column in the table from which to retrieve a value.

Range_Lookup – [optional] TRUE = approximate match (default). FALSE = exact match.