Excel formulas & functions — hands-on

guide (Kevin Cookie Company)

Tip: wherever you see B2:D2, that means select the range starting at B2 and ending at D2
(inclusive).
PC shortcuts shown; on Mac, use ⌘ instead of Ctrl and Fn+F4/F3/F9 for the function keys if
needed.

1) Sheet: Formula Fundamentals

Goal

Learn to enter/edit formulas, use cell references (relative vs absolute), copy formulas with the
fill handle, and calculate with + − × ÷.

Tasks

1. Status bar quick math (no formula)

o Select B2:D2 (the 3 cookie revenues).
o Look at the status bar (bottom of Excel): you should see Sum = 23 (10+8+5).
o Right-click the Sum on the status bar → Copy → click an empty cell (e.g.,
B4) → Ctrl+V to paste.
o (This is just a quick check; delete the pasted value to continue.)
2. Your first formula (hard-coded numbers)
o In B4, type =10+8+5 → Enter → result 23.
o Edit: double-click B4 (or use the formula bar), change a number, press Enter.
3. Use cell references (relative references)
o Replace numbers with cells: in B4 type =B2+C2+D2 → Enter.
o Change B2 from 10 to 11 → B4 updates to 24 automatically.
Why: linked cells recalc; you’re not “hard-coding” numbers anymore.
4. Build formulas using keyboard arrows
o In B4, type = → use arrow keys to select B2 → type + → arrow to C2 → + →
arrow to D2 → Enter.
5. Build formulas using the mouse
o Click B4, type = → click B2 → type + → click C2 → + → click D2 → Enter.
6. Copy a formula with the fill handle
o With B4 selected, drag the fill handle (small square at bottom-right) down
one row to copy the pattern if there’s a matching row beneath, or practice
dragging right across adjacent columns in similar tasks later.
Why: Excel adjusts references relatively (row/column shifts).
7. Subtraction (profit = revenue − cost)
o In B6, type =B2-B3 → Enter → drag right to D6.
o Check: if B2=10 and B3=4, B6 should be 6.
8. Multiplication (tax)
 o In B5, type =B4*10% → Enter → result 0.6 if B4=6 or 2.3 if you used total
(match your sheet’s placement).
o Note: use the asterisk * for multiply (not “x”).
9. Absolute references (lock the tax rate)
o Put the tax rate in a single cell (e.g., G2 = 10%).
o In B5, type =B4*$G$2 → Enter.
o Press F4 while the cursor is on G2 in the formula to cycle absolute/relative:
 $G$2 locks column and row
 G$2 locks row only
 $G2 locks column only
 G2 is fully relative
o Drag B5 right to D5 → the tax uses the same fixed rate.
10. Reference a value on another sheet + Name it
o Instead of G2 on this sheet, click the Tax Rate sheet (if present), select the
10% cell, press F4 to make it absolute, then Enter. You’ll see the sheet name
appear in the formula (e.g., =B4*Tax Rate!$A$2).
o Give that cell a friendly Name:
 Go to the Tax Rate sheet, select the 10% cell.
 Click the Name Box (left of the formula bar), type TaxRate, Enter.
 Back in Formula Fundamentals, use =B4*TaxRate.
Why: named ranges make formulas self-explanatory.
o Press F3 during formula entry to paste a name from the name list.
11. Division (profit split)
o In a cell labeled something like Profit Split, type =TotalProfit/2 or directly
=B6/2 (adjust the reference to your total profit cell) → you and Patty each get
half.

2) Sheet: Order of Operations

Goal

Understand Excel’s calculation order and how to control it with parentheses.

Tasks

1. Enter =B2-B3*10% → You’ll likely get a surprising result (too big).

Why: Excel does multiplication/division before addition/subtraction.
2. Fix with parentheses: =(B2-B3)*10% → Now tax is 10% of profit, not 10% of cost
subtracted from revenue.
3. Pro tip (evaluate part of a formula): select a sub-expression in the formula bar and
press F9 to see its computed value. Press Ctrl+Z immediately to undo the
substitution.

3) Sheet: Basic Functions

Goal

Use the core aggregate functions and shortcuts.

Tasks

1. SUM of sales list: =SUM(B2:B8)

o Shortcut: select the cell below the column and press Alt+= (AutoSum).
2. MAX: =MAX(B2:B8) → highest sales (e.g., 97 for Chocolate Chip).
3. MIN: =MIN(B2:B8) → lowest sales (e.g., 36 for Fortune).
4. LARGE (2nd largest): =LARGE(B2:B8,2)
5. SMALL (2nd smallest): =SMALL(B2:B8,2)
6. COUNT numbers: =COUNT(B2:B8) → counts numeric cells only.
7. COUNTA non-blanks: =COUNTA(A2:A8) → counts cookie names (text).
8. COUNTBLANK: =COUNTBLANK(A2:A9) → how many empty cells.
9. AVERAGE: =AVERAGE(B2:B8)
10. MEDIAN: =MEDIAN(B2:B8) (middle value; often 52 in Kevin’s data).
11. MODE: =MODE(B2:B8) (most frequent; often 52 reappears)

Also try FORMULATEXT to display a cell’s formula:

=FORMULATEXT(CELL_WITH_FUNCTION)

4) Sheet: Conditional Functions

Goal

Sum/average/count only those rows that meet criteria, including multiple conditions and
wildcards.

Tasks

1. SUMIF — cookies sold in United States:

=SUMIF(A2:A10,"United States",B2:B10)
2. AVERAGEIF and COUNTIF follow the same pattern:
o =AVERAGEIF(A2:A10,"United States",B2:B10)
o =COUNTIF(A2:A10,"United States")
3. Wildcards — countries that end with “s”:
=SUMIF(A2:A10,"*s",B2:B10)
(Matches United States and Philippines, etc.)
4. SUMIFS — multiple criteria (United States AND Chocolate Chip):
=SUMIFS(B2:B10, A2:A10,"United States", C2:C10,"Chocolate Chip")
5. Multiple “either/or” criteria (United States AND (Chocolate Chip or Oatmeal
Raisin)):
o Two SUMIFS and add them:
=SUMIFS(B2:B10,A2:A10,"United States",C2:C10,"Chocolate Chip")
+ SUMIFS(B2:B10,A2:A10,"United States",C2:C10,"Oatmeal Raisin")
o Or use one SUM with a SUMIFS that returns an array if your Excel supports
dynamic arrays:
 =SUM(SUMIFS(B2:B10, A2:A10,"United States", C2:C10, {"Chocolate
Chip","Oatmeal Raisin"}))

5) Sheet: Text Functions

Goal

Clean and reshape text; pick out parts like names, zip codes, etc.

Tasks

1. PROPER — title case: =PROPER(A2) → “kevin cookie company” → “Kevin Cookie

Company”.
2. TRIM — remove extra spaces: =TRIM(A2)
(Removes leading/trailing spaces and extra internal spaces.)
3. Join text:
o CONCAT: =CONCAT(A2:C2) (joins without separators)
o &: =A2 & " " & B2
o TEXTJOIN with delimiter and ignore blanks:
=TEXTJOIN(" ", TRUE, A2:C2) → easiest, most flexible.
4. Extract parts:
o RIGHT — last 5 characters (e.g., zip): =RIGHT(A2,5)
o LEFT — first 5: =LEFT(A2,5)
o MID — N chars from the middle: =MID(A2, start, length)
5. Count characters: =LEN(A2)
6. Find positions:
o FIND (case-sensitive) or SEARCH (case-insensitive)
=FIND("@", A2)
7. Combine LEFT with FIND to get email username:
=LEFT(A2, FIND("@",A2)-1)

6) Sheet: Logical Functions

Goal

Evaluate conditions and branch your results with IF, AND, OR, and IFS.

Tasks

1. Basic comparisons (return TRUE/FALSE):

=10=5, =10>5, =10>=5, =10<5
2. AND / OR:
o =AND(10>5,10>11) → FALSE (both must be TRUE)
o =OR(10>5,10>11) → TRUE (at least one TRUE)
 3. IF — which sold more, Chocolate Chip or Oatmeal Raisin?
=IF(B2>C2,"More Chocolate Chip","More Oatmeal Raisin") (Adjust cell refs
to your counts.)
4. IF with AND/OR — “Does Chocolate Chip beat both others?”
=IF(AND(ChocChip>Oatmeal, ChocChip>Sugar), "Yes","No")
5. Nested IF (traditional multi-branch):
6. =IF(A2="Chocolate Chip",10,
7. IF(A2="Oatmeal Raisin",8,
8. IF(A2="Sugar",5,"Unknown")))
9. IFS (cleaner multi-branch; no nesting):
10. =IFS(
11. A2="Chocolate Chip",10,
12. A2="Oatmeal Raisin",8,
13. A2="Sugar",5,
14. TRUE,"Unknown"
15. )

7) Sheet: Lookup Functions

Goal

Look up values from a table by a key (cookie type → price/cost).

Tasks

1. VLOOKUP — price of “Oatmeal Raisin”:

=VLOOKUP("Oatmeal Raisin", A2:C10, 2, FALSE)
o Looks in first column (A) for the name, returns column 2 (price).
o FALSE = exact match.
2. XLOOKUP (newer, more flexible) — price of “Sugar”:
=XLOOKUP("Sugar", A2:A10, B2:B10)
o Lookup array is the names; return
array is the prices.
3. XLOOKUP returning multiple columns (e.g., price and cost):
=XLOOKUP("Sugar", A2:A10, B2:C10) → spills two cells (dynamic array Excel).
4. Why choose XLOOKUP:
o Lookup column doesn’t need to be the first column.
o Can return columns to the left or multiple columns.
o Useful not found message:
=XLOOKUP("Snickerdoodle", A2:A10, B2:B10, "Not found")

8) Sheet: Date & Time

Goal

Return current date/time without arguments.

Tasks
 1. TODAY (date only): =TODAY()
2. NOW (date + time): =NOW()
3. Optional formatting:
o Right-click → Format Cells → Date/Time or Custom (e.g., yyyy-mm-dd
hh:mm).

9) Sheet: Bonus / Auditing

Goal

Quickly see formulas, find where values come from or go to, and highlight formulas.

Tasks

1. Show all formulas: press Ctrl+` (the key under Esc). Press again to toggle back.
2. Highlight formulas: Home → Find & Select → Formulas → now you can apply a
fill color to make them stand out.
3. Trace precedents (what feeds this cell):
o Select a formula cell → Formulas tab → Trace Precedents.
4. Trace dependents (what this cell feeds):
o Select the source cell → Formulas tab → Trace Dependents.
5. Remove arrows: Formulas → Remove Arrows.

10) Sheet: Tax Rate (supporting sheet)

Goal

Centralize a tax rate and reference it cleanly from other sheets.

Tasks

1. Enter 10% in the tax rate cell (e.g., A2).

2. Name that cell:
o Click the cell → in the Name Box type TaxRate → Enter.
3. Use the named cell elsewhere:
o Example in a totals sheet: =Profit*TaxRate
o Or absolute cell reference: =$A$2 (press F4 to toggle).
4. Insert Name while editing a formula: press F3, choose TaxRate.

Micro-skills & shortcuts index (quick reference)

 Enter a formula/function: start with =
  AutoSum: Alt+=
 Copy formula: drag fill handle (bottom-right of the cell)
 Absolute/relative toggle: F4 (Mac: Fn+F4)
 Insert Function dialog: Shift+F3
 Paste Named Range into formula: F3
 Evaluate part of a formula: select it in the formula bar → F9 (then Ctrl+Z)
 Show all formulas: Ctrl+`
 Status bar quick stats: select a range → look bottom-right (right-click to choose which
stats show; you can Copy the result)

Common gotchas (so you don’t stumble)

 Multiplication uses *, not “x”.
 If a formula shows as text (e.g., =SUM(...) is visible rather than calculating), the cell
is probably formatted as Text. Change to General, press F2, then Enter.
 #N/A in VLOOKUP/XLOOKUP usually means the lookup value isn’t found (extra
spaces, spelling, or wrong exact/approximate match).
 Relative vs absolute references: if copied formulas “shift” away from the correct tax
cell, make the tax reference absolute ($A$2) or use a Named Range.
 COUNTA counts any non-empty cells (text, numbers, booleans), while COUNT
counts numbers only.