Excel Functions and Formulas: Basic, Lookup, Budget, and Data Analysis

Sum values in a range.

Formula: =SUM(A1:A10)

Average values in a range.

Formula: =AVERAGE(A1:A10)

Minimum value in a range.

Formula: =MIN(A1:A10)

Maximum value in a range.

Formula: =MAX(A1:A10)

Round a number to 2 decimals.

Formula: =ROUND(A1,2)

Calculate total charge (DVDs × Price + Shipping).

Formula: =E6*F6+G6

Sum total DVDs for all customers.

Formula: =SUM(E6:E9)

Sum total charges for all customers.

Formula: =SUM(H6:H9)

Lookup description based on Major Code.

Formula: =VLOOKUP(D12,$I$21:$L$24,2,FALSE)

Lookup phone number based on Major Code.

Formula: =VLOOKUP(D12,$I$21:$L$24,3,FALSE)

Lookup advisor, but Freshmen = "Bus. Adv. Office."

Formula: =IF(C12="Freshman","Bus. Adv. Office",VLOOKUP(D12,$I$21:$L$24,4,FALSE))

Rank SAT scores so highest = 1.

Formula: =RANK.EQ(G5,$G$5:$G$25,0)

Calculate monthly mortgage payment (PMT).

Formula pattern: =-PMT(rate/payments_per_year, years*payments_per_year, loan_amount)

Example (cell refs) for PMT.

=-PMT(B29/B28, B27*B28, B26)

Round PMT to 2 decimals.

Formula: =ROUND(-PMT(B29/B28,B27*B28,B26),2)

Percent difference (new vs old).

Formula: =(New-Old)/Old

Net Income (Income − Expenses).

Formula: =C7-C15

Running cumulative total across a row.

Formula: =SUM($C$17:C17) (copy across)

Display Tuition if value in B20 ≠ 0, else 0.

Formula: =IF(B20<>0,B33,0)

Comparison operators.

"=", ">", ">=", "<", "<=", "<>"

Line break in a cell.

Win = Alt+Enter, Mac = Control+Option+Return

Lock references (absolute $).

Win = F4, Mac = Cmd+T or Fn+F4

Rename Sheet1 as Documentation.

Right-click the Sheet1 tab → Rename. Type Documentation → Enter.

In B4, enter first & last name on separate lines in the same cell.

Click B4, type your first name. Insert a line break: Windows: Alt+Enter • Mac: Control+Option+Return. Type your last name → Enter.

In B5, enter current date as mm/dd/yy and align left.

Click B5, type =TODAY() → Enter. Home → Number group → short date, then More Number Formats... → Custom: mm/dd/yy. Home → Align Left.

In B6, type the sentence given.

Click B6, paste/type: To reinforce my skill developed in Excel and prepare for the exam → Enter. Save (Ctrl+S / Cmd+S).

Set column widths: A=10, B=12, C=12; AutoFit D, E, I.

Click column A → Home → Format → Column Width... → 10. Repeat for B and C with 12. Select D, E, I (Ctrl/Cmd-click each) → Home → Format → AutoFit Column Width.

Autofit all rows to contents.

Click the Select All button (top-left corner of the grid). Double-click any row boundary, or Home → Format → AutoFit Row Height.

In H5, enter the label Total.

Click H5, type Total → Enter.

In H6, calculate total charge = DVDs × Price/DVD + Shipping.

Click H6. Type =E6*F6+G6 → Enter.

Copy H6 down through H9.

Re-select H6 → drag the fill handle to H9 (or copy/paste).

Set column H width to 11.

Click column H → Home → Format → Column Width... → 11.

In D10, enter the label Total.

Click D10, type Total → Enter.

In E10, sum total DVDs for all customers.

Click E10 → type =SUM(E6:E9) → Enter. (Adjust the range if your data extends further.)

In H10, sum total charges.

Formula: =SUM(H6:H9)

Total charge (orders)

=E6*F6+G6

Sum of DVDs

=SUM(E6:E9)

Sum of charges

=SUM(H6:H9)

VLOOKUP Description

=VLOOKUP(D12,$I$21:$L$24,2,FALSE)

VLOOKUP Phone

=VLOOKUP(D12,$I$21:$L$24,3,FALSE)

Advisor with IF

=IF(C12="Freshman","Bus. Adv. Office",VLOOKUP(D12,$I$21:$L$24,4,FALSE))

Rank SAT (highest=1)

=RANK.EQ(G5,$G$5:$G$25,0)

Percent change

=(New−Old)/Old

Net income

=Income−Expenses (e.g., =C7-C15)

Running total (row)

=SUM($FirstCell:ThisCell) across

Line break in a cell

Win Alt+Enter • Mac Control+Option+Return

Lock refs

Win F4 • Mac Cmd+T or Fn+F4

AutoSum

Click H10 → AutoSum (Σ) → confirm range (e.g., H6:H9) → Enter.

Fill comparison operators

B3: type "=" → Enter. B4-B7: type ">", ">=", "<", "<=", and "<>" (each with quotes), one per cell.

Display Major Description

Type =VLOOKUP(D12,$I$21:$L$24,2,FALSE) → Enter.

Display Phone Number

Type =VLOOKUP(D12,$I$21:$L$24,3,FALSE) → Enter.

Display Advisor

Type: =IF(C12="Freshman","Bus. Adv. Office",VLOOKUP(D12,$I$21:$L$24,4,FALSE)) → Enter.

Rank SAT

Type =RANK.EQ(G5,$G$5:$G$25,0) → Enter.

AutoFill month names

Type Jan in C4. Drag the fill handle across to N4 (stops at Dec).

Calculate monthly totals

Click C7 (Total Income). Type =SUM(C5:C6) → Enter.

Compute percent difference

Type =(D7-C7)/C7 → Enter.

Compute Net Income

Type =C7-C15 → Enter.

Compute Average, Min, Max

C20: =AVERAGE(C5:N5) • D20: =MIN(C5:N5) • E20: =MAX(C5:N5) → Enter.

Insert a new row

Right-click row 18 header → Insert.

Create cumulative Net Income

Type =SUM($C$17:C17) → Enter.

Save your file

Ctrl+S / Cmd+S.