MS OFFICE FUN

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.

Understanding This chapter teaches you how to deal with some common formula errors in Excel






#DIV/0!:  Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

#NAME?:  Names are wrongly/incorrectly spelled in a formula or cell.

#NUM!:  Occurs when Excel encounters an invalid number.

#NULL!:  Basically this occurs due to missing arguments in the formulas or named ranges.

#N/A:  Indicates that a value is not available to a formula.

#VALUE!:  Excel displays the #VALUE! error when a formula has the wrong type of argument.

######:  When your cell contains this error code, the column isn't wide enough to display the value.

#REF!:  Excel displays the #REF! error when a formula refers to a cell that is not valid.

Excel’s Flash Fill is a time-saving feature which fills in data for you when a pattern is evident


Start Flash Fill manually. If Flash Fill is turned on but doesn't start automatically when you type data that matches a pattern, you can try starting it manually by clicking Data > Flash Fill, or by pressing Ctrl+E. Check out this training course if you'd like to know more about using Flash Fill.

During this Excel 2016 tutorial, learn how to use text function and with other functions in Excel 2016.

PROPER, UPPER, & LOWER
Use Excel's PROPER, UPPER, or LOWER functions to correct or convert text data to capital and small letters as needed

CONCATENATE
CONCATENATE function allows you to combine text from different cells into one cell.

TRIM 
TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.

Len
The Microsoft Excel LEN function returns the length of the specified string.

Left
The text from which to extract characters. Use the LEFT function when you want to extract characters starting at the left side of text.

Right
The text from which to extract characters on the right. Use the RIGHT function when you want to extract characters starting at the right side of text.

FIND / SEARCH
FIND function is case sensitive while the SEARCH function is case-insensitive

The CHOOSE function in Excel allows you to 'Choose' a "value' depending on a specific input.

1.Randomly CHOOSE names (where value is "text" (in quotes))
2.The CHOOSE function can help show which fiscal quarter a month is in, or show the total sales for a selected store number

Try a formula construction like (with lookup value in cell B1):
=CHOOSE(B1,A1,A2,A3,A4,A5,A6,A7)

Watch this video to see how to use the Excel CHOOSE function, and select items in a list.