AUTOFILL - A method of copying repetitive data (whether text, number or formula) into several rows and columns.1. Select the cell you want to replicate.2. Place the cursor on the bottom right corner of the active cell. You will see a black square. It will turn into a cross once you let the cursor hover over it.3. You can either drag from the black square until you are able to copy the data into the cells you want to fill OR you can just double click the black square so that the blank cells will automatically be filled. NOTES: • The “double-clicking method” works only with vertical filling. For horizontal filling, you need to use the “drag method”. • The “double-clicking method” will only fill cells where the left column is filled with data. It will stop “filling” as soon as the cell on the left column is blank.Drag from the bottom right corner24CUT / COPY / PASTE – These commands/actions are used for moving and replicating text or objects. To move a text or object:1. Highlight the text or select the object.2. Press Ctrl + X or click the cut icon (Home tab > Clipboard group).3. Click the cell or object where you want to move the text / object.4. Press Ctrl + V or click the paste icon at the Clipboard group. To replicate text or objects once or multiple times:1. Highlight the text or select the object.2. Press Ctrl + C or click the copy icon (Home tab > Clipboard group).3. Click the cell or object where you want to move the text / object.4. Press Ctrl + V or click the paste icon at the Clipboard group. ACTION SHORTCUT KEYCut Ctrl + XCopy Ctrl + CPaste Ctrl + V25PASTE OPTIONSERROR DESCRIPTION#NAME?Typing errorTyping a range name that does not exist Leaving off the quotation marks around a text string in a formula#DIV/0!Tried to divide by zero. Either the cell's value was zero, it was blank, or it contained text instead of a number. Numbers that are formatted as text can cause this, too.#VALUE!Tried to do something to data of the wrong type, like trying to add values when at least one is text instead of a number.#REF!Formula refers to a cell that you deleted or pasted over.#NUM!Problem with a number in the formula. An argument is invalid. A calculated value is too large or too small to display.#NULL!The formula includes multiple ranges with a space in between instead of a comma, but the ranges do not overlap. When the ranges do overlap, the formula uses only the values from the intersection of the ranges.FormulaOmits Adjacent CellsThis one may not actually be an error! It depends on what your formula is supposed to do. The warning helps when your dragging missed a cell or two.COMMON ERRORS – SW2FORMULAS & FUNCTIONS“Formulas are equations that can perform calculations, return information, manipulate the contents of other cells, test conditions, and more. A formula always starts with an equal sign (=).” (MS Excel def.) You can enter formulas either through the formula bar or directly into the cell. Parenthesis () organize the levels of “nesting” within a formula. “Nesting” occurs when there are several levels of formulas in one compound formula. You can have up to 64 IF functions nested within one formula.A formula may contain a combination of these elements:1. CONSTANTS – This refers to numbers or characters included in the formula. Example: 1, 50, 1000, “apple”, “Year”NOTES:• When encoding a formula, numbers from 1,000 and above should not include the comma ( , ) to prevent formula error.• Unless typing a defined name, text should be enclosed in quotation marks (“ “) for it to be recognized as text. If not, the formula will result in #NAME? (error).29302. OPERATORS – This refers to the mathematical symbols involved in the formula. In the absence of any parenthesis, Excel will generally recognize the MDAS rule in calculations. Example: + - / * ^3. FUNCTION – “a preset formula in Excel. It begins with the equal sign (=) followed by the function's name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.” (About.com def.). Example: =SUM(), =TEXT()4. CELL REFERENCES – As the name implies, this involves referring to another cell to “call” for the contents of that cell. To indicate a cell reference, you need to use the cell address (letter + number).Example: A1, Z$32, $B$2There are two kinds of cell references:a. Relative - This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill. b. Absolute – This is used when you don’t want to change the cell reference. To keep the column or row reference unchanged, you need to use “$”.CELLREFERENCEEFFECT$A1Keeps the column reference unchanged but not the row reference.A$1Keeps the row reference unchanged but not the column reference.$A$1Keeps both the column and row references unchanged.NOTE: Shortcut for inserting the dollar ($) sign: press F4.CREATING FORMULAS 1. Using operators and constants.To do this, simply type “=“ (equal sign without the quotation marks) in the cell where you want to put the formula. Then type a combination of constants and operators as you see fit. Press Enter.Example: =(114+56)*32. Using cell referencesThis is similar to creating formulas using operators and constants except this uses cell references/addresses most of the time instead of constants. This allows greater flexibility in terms of updating data.To add a cell reference, you can either type the cell address after “=“ or you can use your mouse after typing “=“. Using your mouse will allow a dotted border to appear around the cell you are linking to. The border color will be the same as the color of the cell address in the formula.Example: =IF(B10>10,$C7,”missing”)323. Using functions. There are two ways to do this: • Type directly into the cell.• Use the “Insert Function”.The “Insert Function” dialog box can be accessed by:• Clicking the icon before the formula bar • Going to FORMULAS Tab > Function Library > Insert Function.33Once the “Insert Function” button is clicked, a dialog box will appear.To use the Insert Function dialog box:1. If you’re unsure about the function, use the search boxor select a category.2. Once the category is determined, all the formulas belonging to that category will appear as a list of choices. Select one. Click