Excel tips (this may change and expand through the course) Selecting a large block of cells. Place the cursor in the upper left corner of the block of cells you wish to select. Scroll down to the bottom right corner, press the Shift key and then mouse-click. The block of cells will now be selected and ready for copying (Alt-C), cutting (Alt-X) or pasting (Alt-V). Keyboard shortcuts: Combine the Ctrl key with letter keys to perform tasks. Ctrl-Z (undo), Ctrl-X (cut), Ctrl-C (copy) and Ctrl-V (paste) are just a few of the many keyboard shortcuts that can save you much time when doing repetitive entries. Others include: Select all (Ctrl-A), Save (Ctrl-S), Quit (Ctrl-Q), Close (Ctrl-W), Open (Ctrl-O), Fill down (Ctrl-D), Fill right (Ctrl-R). Redo (Ctrl-Y), Underline (Ctrl-U), Italicize (Ctrl-I), Bold (Ctrl-B), Print (Ctrl-P). Break up complex equations: If you already know enough Excel to do your calculations in it, great.
The one added trick you may not think of is to "break up" complex equations. For example, if an equation has a complex numerator and a complex denominator, put those "bits" in two different columns. Then make a third column that is just the numerator over the denominator. For complex equations it is hard to keep all the brackets straight... Learn some of the major functions:
=Average(B1:B23) gives the arithmetic mean for the numbers in Column B from Row 1 to Row 23. =Average($B$1:$B$23) makes the reference absolute (can be pasted or moved elsewhere) rather than relative.
=If(condition, result-if-true, result-if-false) can be very useful. You can use it to look for outliers, to find lack of match between sets of numbers, and in many other ways. Use the right kind of plot: If you use Excel for plotting, be sure that you use the correct graph type to get the X-axis correct. For example, if you want to plot population size as a function of date (years, such as 1989), don't use a graph type that makes the X-axis dates into categories. That will make the "distance" from 1650 to 1850 the same as the distance from 1930 to 1975. Using the plot option "XY scatter" will avoid that problem, at least. To turn rows into columns or vice versa: Use the Transpose() function or Edit<Paste special.
Transpose function method.
Say you have the values A1=12 (A1 is the cell label), B1=9, C1=14, D1=11.
1) Select cells A3 to A6 (by clicking and dragging).
2) Type “= Transpose(”. The typing will appear in Cell A3. 3)With the cursor still in Cell A3, click in Cell A1. That will put “A1” into the formula.
4) Now shift-click on Cell D1. That will select the arrayA1:D1 (cells A1 to D1. Excel uses colons to separate the first and last cell labels of an array) and you will now have “=Tanspose(A1:D1” in Cell A3.
5) Type “)” to close out the formula.
6) Press Ctrl-Shift-Enter all together (Ctrl is the Control key, usually at the bottom left of the keyboard). You will now have a vertical array of the four values in Cells A3:A6.
Paste special method.
1) Select cells A3 to A6 (by clicking and dragging).
2) Copy the cells (press Ctrl-C ).
3) Put your cursor in Cell A3.
4) Go to the Edit menu and scroll down to Paste Special.
5) In the resulting dialog box click the cell in the Transpose box near the bottom.
6) You will now have the numbers in an array from A3:A6. To get cells as values not formulae: Now that you have the values transposed, you may want to make them "regular numbers" rather than references to cells elsewhere in the spreadsheet.
1) Select the new column cells,
2) Copy them (Ctrl-C or Alt-C)
3) use the “Values” radio button, a "Paste Special" subselection, to turn the cells from cell references into numerical values that do not refer to another cell. Fill down and Fill right. To copy a formula down a column, you can use the Fill down command (shortcut is Alt-D) or the Fill right command (Alt-R). Automatically create a series of numbers in a row or column:
1) In a new worksheet, put “1” in A1 and “2” in A2.
2) Select the cells by clicking and dragging over them.
3) Get your cursor to the bottom right corner. Notice that it changes shape to be a cross or plus sign shape.
4) Click-drag down to cell A10. The cells will fill up with the values 3, 4, 5,….10. If you put 2, 4 in the first two cells the numbers will increase by 2s.
Very useful for filling in long sets of sequential numbers. Basic graph skills. Download the “ExcelCharts.xls” worksheet from the Main Index page for the course. Don’t do intermediate rounding. (More likely a problem if you do hand or calculator calculations). Repeated rounding can have a disastrous effect on accuracy. It does not make sense to report thousandths of a kilometer in a report of ferret dispersal distances. If, however, the dispersal calculations require repeated applications of formulae, and you round to integers at every round, you will likely wind up far off the mark. Round only at the point where you are presenting the numbers, not when you are doing calculations. Concatenation: Sometimes you may want to combine items (e.g. numbers, ID labels) that are in two different columns. That is called concatenation (combining a series of items). At other times, you may want to split a column into two separate columns. You can do this in Excel, if you know the “=Left(cell ref, digit count)”, “=Right(cell ref, digit count)” and “cell ref & cell ref” commands. The Left and Right commands take part of a cell and allow you to place it in a new column. The & command combines items from two different cells (or whole columns, if you paste it down).
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.
