EXCEL Formulas

Excel shortcuts
Adjust columns
Quickly move to left, right, top & bottom
Move/copy & insert data
Fill Series Options
Formulas
Relative & Absolute referencing
Shortcut to copy
Text functions
Conditional formulas
Lookup function
1
EXCEL SHORTCUTS:
Automatically adjust all columns to fit the data:
 Click on the cell above row 1 and to the left of column A (selects all data on sheet
 Go to any label and double click on the vertical line between the column labels (all columns will be adjust to fit the data)
To quickly select a large group of cells:
 Click in the Name Box (box above column label A)
 Type your cell range (a1:a5000)
 Press enter
o Note: All your cells are highlighted. You can then apply desired formatting or move the block to a new location. Faster than clicking and dragging to select.
 Option: With the cells highlighted, click in the Name box and type a descriptive name and enter. Click on any cell to deselect range. Click on the name box dropdown arrow and select the named cell range. This name can also be used in formulas. =sum(courses)
Move quickly to top/bottom/left and right:
 Click on a cell
 Double click on the right border line to go to the last cell on the right or
 Double click on the left border line to go to the left most cell or
 Double click on the top border line to go to the top most cell or
 Double click on the bottom border line to go to the last cell
o Note: Will stop before the first blank row or column
Move cells to a new location:
 Highlight a cell or group of cells
 Click on the outside border of the selected cells (will see 4-headed arrow) and drag to new cell location
Move & insert data within existing rows of data:
 Highlight a cell or group of cells
 Press shift & click on the outside border of the selected cells (will see 4-headed arrow) and drag to new cell location to insert the data
o Note: If you don’t press the shift key, the data you move will overwrite existing data.
Move/Copy & insert data within existing rows of data:
 Highlight a cell or group of cells
 Right click on the outside border of the selected cells (will see 4-headed arrow) and drag to new cell location
2
 Options include: Move Here, Copy Here, Copy Here as Values Only, Copy Here as Formats Only, Link Here, Create Hyperlink Here, Shift Down and Copy, Shift Right and Copy, Shift Down and Move, Shift Right and Move
o Note: Selecting one of the “Shift” options will always insert data, not overwrite existing data.
Use the Fill Series to enter data (Months, Numbers, Weekdays):
 Enter data in your first cell i.e.: January
 Click and drag the bottom right corner of the cell containing January to fill in all the months or double click on right cell corner to fill in until the last row.
Use the Fill Series to generate weekday dates:
 Enter a start date
 Right click and drag the bottom right corner of that cell to desired location
 Select Fill Weekdays (notice options: Fill Months, Years, Series)
 Options include: Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, Fill Days, Fill Weekdays, Fill Months, Fill Years, Linear Trend, Growth Trend, Series
FORMULAS:
Formulas can be entered by:
 Using the AutoSum button ( Σ located on the Home and Formula tab) or
 Typing the formula directly into a cell / example: = sum(b5:b100) or
 Using the Insert Function (fx). The Insert Function (fx) helps you create formulas step by step through the use of a dialog box. You are prompted to enter the Arguments (the values to calculate). Arguments are parts of the formula. Example: =average(d3,d7,c11) The arguments are in parentheses after the function name.
Formula Examples Description
=sum(d2:h2)
Sum the cells d2 through h2
=(c2*.20)/3
Multiply c2 x .20 then divide by 3
=COUNTIF(H2:H120,"harkins")
Count the number of cells in the range H2:H120 that read harkins
=IF(SUM(D8:F8)>3,SUM(D8:F8)*30,SUM(D8:F8)*35)
If the sum of cells D8:F8 are greater than 3 then sum D8:F8 x 30 (true) else sum D8:F8 x 35 (false)
3
Use relative and absolute references:
Relative Referencing (most common)
A relative address automatically changes if you copy a formula to a new location on the worksheet.
Exercise: Enter the AutoSum button to calculate the total expenses. (Use the Forecast sheet tab):
1. Click in the blank cell: b15
2. Click Home / click on AutoSum Σ (editing group)
3. Press enter (formula is automatically entered: sum(b8:b14))
4. Copy the formula to the remaining cells: click on the bottom right corner of cell b15 and drag to e15 or use the copy/paste function
Exercise: Type the formula to calculate Net Income:
1. Click in the blank cell: b17
2. Type =b6-b15
3. Press enter
4. Copy the formula to the remaining cells: click on the bottom right of cell b17 and drag to e17 or use the copy/paste function
Exercise: Enter a formula to calculate the expenses for one year. Click in c8 (blank cell) and enter the formula to calculate the expense in b8 for one year. Formula: =b8*12
1. Copy the formula to the remaining cells. Check the formula in several cells to view relative referencing:
i. i.e.: = b8*12, =b9*12, =b10*12
Absolute Referencing
An absolute reference will always point to the location of a specific cell, even if you copy it. Great option when projecting increases.
To define an absolute reference: Press f4 before the cell address or type $ before row and column. $G$5
Exercise: Use absolute referencing to create a formula that references a cell containing a percent value. Find out what the expenses would be if they were increased by 4% increase:
i.e.: =(C8*$G$5)+C8 =(C9*$G$5)+C9 =(C10*$G$5)+C10
1. Go to cell g5 and enter a percent value = .04
4
2. Use absolute referencing in the formula to reference cell g5 instead of entering .04 in your formula
3. Enter the formula in cell d8 to calculate what the expense would be if you increased the expense of year one by 4%. Formula: =(C8*$G$5)+C8
4. Copy formula to remaining cells
5. After you get your answer, change .04 to .08 (formulas automatically update)
Exercise: Copy formulas to remaining cells:
1. Enter your first formula
2. Double click on the bottom right of the formula cell (will automatically fill in all formulas to the end of the column) or click and drag bottom right corner
TEXT Formulas
Exercise: Display only the first 3 characters of the building name using the function LEFT:
1. Insert a blank column after the building column
2. Click on fx (Insert Function)
3. Type left in the search box and press enter to find
4. Click ok or enter
5. Enter j2 in the text box
6. Enter 3 in num_chars box
7. Copy formula =LEFT(J2,4)
 Note: You can use function “RIGHT” to display characters from the right or use function “MID” to display characters from the middle of the text string. With MID, need to specify starting character and number of characters; i.e.:=MID(f1,3,4)
Exercise: Combine 3 cells / fields (Dept, Number & Section) into one cell (Dept-Number-Section):
1. Insert a blank column after the section column
2. Click on fx (Insert Function)
3. Select the category “TEXT”
4. Click on Concatenate and click ok
5. Enter c2 in Text1 box
6. Enter – in Text2 box
7. Enter d2 in Text3 box
8. Enter – in Text4 box
9. Enter e2 in Text5 box
10. Copy your formula =CONCATENATE(C2,"-",D2,"-",E2)
5
Conditional Formula
The IF function performs a logical test on an argument (the values to calculate). Depending on the value of a cell, a certain function will be performed. If the argument is true a certain action will be performed and if the argument is false, a different action will be carried out. The Insert Function (fx) is helpful when using formulas containing logic.
Examples:
If cell is not blank, copy the value to current cell, otherwise copy a different cell value.
If someone ordered a quantity > 5, charge them $30 per item, otherwise, charge them $35 per item.
Exercise: Use the “Names” sheet tab and separate the names to read first, middle and last using the text to columns function. Then use the IF statement to list all last names in one column:
1. Select columns B:D and click on Home / Insert
2. Select column A and click on Data / Text to Columns
3. Use delimited then select space as the delimited character
4. Click finish
 Note: You will see that all the last names do not appear in one column because some have middle initial or a suffix.
5. Click in cell d2 to enter your formula.
6. Click on the Insert Function (fx)
7. Type IF then press Enter / ok
8. On the Logical Test field enter c2 = “” (check for blank in c2)
9. On the If True field enter b2 (If c2 is blank, then enter name from b2)
10. On the If False field enter c2 (If c2 is not blank, then enter name from c2)
11. Click ok
12. Double click on bottom right formula cell or click and drag to copy formula =IF(C2="",B2,C2)
 Option: Hide column B&C or Copy D and use Paste Special and only paste values
Additional Formulas
Exercise: Use the function AVERAGE to determine the average for a group of grades. Each grade is worth a different percentage value. The first grade is worth 10%, the second is worth 20%, the third is worth 30% and final is worth 40%.
1. Click on the sheet tab labeled “Lookup”
2. Delete the current Average formulas (c3:c12)
3. Enter the new formula in cell c3: =(D3*0.1)+(E3*0.2)+(F3*0.3)+(G3*0.4)
4. Copy the formula to the remaining cells
6
Exercise: Use the above example with the Lookup Function to have the system enter a letter grade based on a lookup tables:
Lookup functions are used to retrieve a value from a table. It’s great for getting the letter grades from the final number grades or entering a value based on a code.
HLOOKUP – searches across the top row of the range until the value is met
VLOOKUP – searches down the first column of the range until the value is found
1. Click on the tab labeled: lookup and click cell b3
2. Look at the table of grades (Note: table values need to be in ascending order)
3. Use the fx function and select category Lookup and Reference/function name vlookup
4. Lookup_value field: cell contain the number grade - c3
5. Table_array field: Highlight the table range - $a$18:$b$28 (absolute reference)
6. Row_index_num field: Specify the line the letter grade on 2 (2nd column of array) and click ok
7. Copy and paste the formula =VLOOKUP(C3,A18:B28,2)
Exercise: Determine the average of a series of grades without including the lowest grade. Sum the group of numbers then subtract the lowest number and then divide by 3. Use the MIN function to remove the lowest number:
1. Click in a blank cell where you want the total to display
2. Click in the formula bar and enter =(SUM(D3:G3)-MIN(D3:G3))/3
3. Copy formula
More Examples:
=IF(A3=35, "Call"," ")
If cell A3 is equal to 35, place the word “Call” in the cell.
If it doesn’t equal 35 then leave the cell blank.
Note: Text needs to be in quotes.
=IF(N2<=10,000,"Within budget","Over budget")
If the value in cell N2 is less than or equal to 10,000, then the formula displays "Within budget". Otherwise, the function displays "Over budget".
=IF(sum(d8:f8)>50, "bonus",sum(d8:f8))
If the sum of d8:f8 is greater than 50, enter the word “bonus”. If not greater than 50, just sum d8:f8.
=IF((AND(c6="DD",f6>20)),2,1)
Logical test AND(c6="DD",f6>20) no spaces
Value if true 2
Value if false 1
=networkdays(a2,a3)
Find the # of workdays between the two dates referenced.