Some Excel Helpful Hints

Some Excel Helpful Hints:

Entering Formulas

To enter a formula in a worksheet cell, you will first type an equal sign (=) followed by elements that may include values, operators, cell references, names or functions. Then press Enter or click the Check button on the Formula Bar . Excel displays the result of the calculation on the cell and the entered formula on the Formula Bar. To switch between displaying the results or the formulas on the cells of a worksheet, press Ctrl´. This is useful for checking your formulas, because Excel will produce a result as long as your formula is free of syntax errors. It's up to you to make sure that you don't have the right answer to the wrong formula.

Operators

An operator is a symbol that performs an operation, such as a plus sign (+). The arithmetic operators for performing standard mathematical operations are: + for addition, - for subtraction, * for multiplication, / for division, ^ for exponentiation, and ( ) for grouping terms.

Operations	Operator	Example	Description
Addition	+	=10+13 =B1+B2+B3	Adds 10 to 13 Adds the values of cells B1, B2, B3
Subtraction	-	=C9-B2 =1-10	This will subtract the values or numbers you have entered

Multiplication	*	=C9*B9 =.06*7	This multiples the values of the cells, or the numbers you have entered
Division	/	=C9/B9 =15/3	This divides the value in the cells or the numbers you have entered
Exponentiation	^	=B5^3 =3^B5	This raises the values in cell B5 to 3, or raised 3 to the value in B5

Precedence

Order of Operations (Do you remember 6th grade Math?)

When a formula contains several operators, there is a predetermined order in which they will be performed:



( ) Parentheses

^ Exponents

* or / Multiplication or Division

+ or – Addition or Subtraction



(Forgetful? This phrase will help you remember: Please Excuse My Dear Aunt Sally).

Operations on Dates & Times

Since Excel treats dates and times as numbers, they can be math operands. For example, subtract two dates to find the number of days in between.

Formula	Result
="6/8/96"-"5/27/96	12 "
="12:30 pm"-"9:00"	3:30

Text & Logical Operations

You can also perform logical and text operations. The & operator concatenates two values to produce one text value. Comparison operators, which include =, <, >, >=, <=, and <>, compare two values and return the logical value TRUE or FALSE.

Formula	Result
=123&456	123456
="Route"&66	Route66
="Net"&" "&"Sales"	Net Sales
=6=5	FALSE
=6>=5	TRUE

Cell References

You can use cell references instead of values as operands in formulas. That way, if you change the value in a cell referenced by a formula, the calculated result updates automatically.



To enter a cell reference in a formula, type it in directly or point and click on the cell and Excel will fill in the cell reference. In this example, the formula in cell D2 multiplies the value in cell B2 by the value in cell C2. If you change the unit price for fine lamps to $50.00, then the total will automatically change to $1,000.00.



In formulas, you can use references to cells on other worksheets in the workbook (or even cells in other workbooks). At the point in the formula where you want to enter the cell reference, switch to that worksheet by clicking the worksheet tab, then click the cell and continue with formula. Suppose that for the above purchase order, you obtain the fine lamps total discount by multiplying the fine lamps total with the Blue Sky Airlines courtesy discount in cell B3 on the worksheet named Customers. The reference for the discount factor in the formula is Customers!B3.

Relative References

To compute the totals for leather chairs and hardwood desks, you can copy the formula in cell D2 to cells D3 and D4. Use the Copy and Paste buttons on the Standard toolbar or drag on the AutoFill handle of cell D2. You will see that the cell references will automatically adjust for the new location. For example, the formula in cell D3 will be =B3*C3, correctly reflecting the total for leather chairs. This automatic adjustment applies when you copy (or move) formulas with relative cell references.

Absolute References

If you need to copy a cell reference exactly as it appears in the original formula, then you need to use absolute cell references. An absolute cell reference has a $ sign preceding the column letter and row number. For example, if you want to ensure that the same discount is applied to the leather chairs total and the hardwood desks total, edit the formula in cell E3 so that the reference to cell B3 on the Customers worksheet is absolute before copying the formula to cells E3 and E4. This way the formula in say, cell E3 is = D3*Customers!$B$3.

Mixed References

You can also use mixed references, such as $A1 or A$1. When you copy a formula with a mixed cell reference, the column or row (whichever one does not have a $ sign) automatically adjusts for the new location while leaving the other part of the reference unchanged. As shown in the example, before copying the formula for January totals to calculate the February totals, first make the reference to column B absolute so that the correct unit price multiplies the February order quantity: your formula for D3 would be $B3*E3. Copy this formula to F3, fill down, and you are good to go.

 

Names

If you named a cell or a range of cells, you can use the name in formulas to refer to values in the range. To enter a name in a formula, type the name or select the name from the Name Box in the Formula Bar.



In this example, cell E2 is named Hourly_Rate and the formula that computes the daily wage for Monday is =C6*Hourly_Rate. When you copy this formula to compute the total for Tuesday, Wednesday, etc., the copies retain the reference to cell E2.



In this next example, the range B2:E2 is named Sales and the range B3:E3 is named Expenses. The formula for profit for each quarter is =Sales-Expenses. Excel actually uses the values for the particular column. You can refer to the intersection of two ranges using each range's name separated by a space. In the above example, the range B2:B4 is named Qtr_1. The formula =Qtr_1 Sales results in 67,810.68.

Functions

Excel has lots of built-in functions that perform many different types of calculations. Excel functions are used in formulas and have the format: name(arguments). A function can be the only element in a formula or part of a larger formula. Arguments can be numbers, text, cell references, range names or even formulas containing functions.

AutoSum

The AutoSum button on the Standard toolbar is a shortcut for creating a formula that totals values in a range of cells using the Excel SUM function.



For example, to compute total January sales in cell C9, click the AutoSum button (note that Excel gave the correct sum range, C5:C8) and then press Enter or click the Check button on the formula bar. To total sales for each month all at once, select the range C9:E9 before clicking the AutoSum button. To compute monthly totals in row 9, regional totals in column F, and the grand total in cell F9, select the range C5:F9 before clicking the AutoSum button.

In proposing a sum range, Excel assumes that you are adding values down a column or across a row. If the proposed range is incorrect, just select the right range and then press Enter. The formula =SUM(C5,D8,E7) adds 10111, 21500, and 16900 while the formula =SUM(C5:E5,C7:E7) totals the north and east region totals.

Auto Format

Auto Formats are designed for worksheets with labels in the left column and top row, and totals in the bottom row or right column. To use Auto formatting, select the data to be formatted instantly, or place your mouse pointer anywhere within the range to be selected, then click Format on the menu bar, click Auto Format then, select from the sample boxes.

Function Wizard

To create a formula that begins with an Excel function, select Function... from the Insert menu or click the Function Wizard button on the Standard toolbar. Excel inserts an = sign to begin the formula and then brings up the Function Wizard dialog box. To insert a function elsewhere in the formula, select Function... from the Insert menu or click the Function Wizard button on the Standard toolbar or the Formula Bar.

In Step 1 of the Function Wizard, select the function by clicking on its name. If you do not know the name of the function that you need, select the Function Category that it falls under to filter the Function Name list. Everytime you select a function name, a description of the function and its arguments appears. For more information on the function you selected, click the Help button on the Function Wizard dialog box. This brings up the on-line help topic window for that function.



Once you've selected the function, click the Next button to move on to Step 2. In this final step, you fill in the required arguments in the edit box(es) provided and click Finish when you're done. For the arguments, you can type values, select cell ranges or select a name you previously defined from the Name box. You can also enter a formula (without starting with an = sign) as an argument. If you want to enter a function in the argument (this is called a nested function), click the Function Wizard button beside the edit box. You will need to complete the steps of the Function Wizard for the nested function before completing the original function.

Examples

The formula that computes average January sales for Blue Sky Airlines is =AVERAGE(C5:C8) . If the range C5:C8 were named January, then the formula =AVERAGE(January) produces the same result. Some other examples include:

Function	Result	Description
=COUNT(C5:E8)	12	counts the numbers
=MAX(C5:E8)	24050	finds the maximum number
=MIN(C5:E8)	10111	finds the minimum number
=MAX(C5:E8)*.10	2405	finds 10% of 24050
=MAX(C5:E8)-MIN(C5:E8)	13939	subtracts 10111 from 24050
=IF(C5>20000,1,0)	0	returns 1 if C5>20000, returns 0 otherwise

Correcting Errors

If you tried to enter a formula that contains an error, Excel will give you an error message. Click OK on the error message's dialog box. Then either edit the formula on the Formula Bar to correct the formula or click the Cancel button to clear it. Common sources of error are parentheses that don't match or missing arguments for functions. A formula that is free of syntax errors may result in an error value. Here are some error values you might get:

Error Code	Explanation
######	Not really an error. The result is too long to fit in the cell, just make the column wider.
#DIV/0!	You're trying to divide by zero.   ~Correct the divisor.   ~If the divisor is a cell reference, check that it is not empty.
#NAME?	There's a name in the formula Excel doesn't recognize. If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box instead of typing it in. If you typed in a function, check its spelling or verify that such a function exists. If you're performing operations on text, enclose it in double quotation marks.
#REF!	A cell reference is not valid. This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula. You will need to reenter the formula.
#VALUE!	The formula uses a wrong type of operand or argument. Check to see that you're not performing math operations on labels or that arguments of functions that need to be numeric are not referring to cells containing labels.