Chapter 3.2: Microsoft Excel
Introduction
Learning Outcomes
Use the ideas of sheet, cell, row, column and range.
Calculate with cell references and basic functions.
Apply tables, sorting, filtering and validation.
Pick a chart that fits the data and present it clearly.
Introduction
What Is Excel For?
Repeatable calculations with formulas and functions.
Organize records in rows and columns.
Sorting, filtering and summarizing.
Trends and comparisons with charts.
Budgets, schedules, stock and task tracking.
Co-editing and commenting on a workbook.
Worksheets
Basic Concepts
The whole Excel file (.xlsx).
The grid of cells inside a workbook.
B4 is a single cell; B4:D6 is a rectangular range.
Worksheets
The Grid and Cell References
C3 = where column C meets row 3. The Name Box shows the address of the selected cell; the formula bar shows its contents.
D2 holds a formula: =B2*C2. The cell shows the result (96); the formula bar shows the formula itself.
D3: =B3*C3 → 5·30 = 150. A formula uses addresses, not values.
B2 goes from 12 to 20, and D2 recalculates by itself (160). That is the power of references.
Data Entry
Data Types
Names, descriptions and categories.
A quantity that can take part in calculations.
Time information that Excel stores as serial numbers.
TRUE or FALSE.
A calculation that starts with an equals sign.
A special result that reports a problem with a formula or the data.
Data Entry
Entering and Editing Data
- Numbers stored as text can break sorting and calculations.
- One observation per cell and one attribute per column make analysis easier.
Worksheets
Rows, Columns and Sheets
Changes the structure by shifting cells at the selected spot.
Row height and column width are set by hand or automatically.
Removes data from view without deleting it.
Keeps header rows or columns visible while you scroll.
Use a short name that describes the contents.
Keep data, calculation and report sheets apart.
Formatting
Cell Formatting
Set off headers and key areas.
Positions text within a cell.
Can mark table edges; overuse hurts readability.
Splits long content over several lines within a cell.
OK for titles; in data ranges it gets in the way of sorting and filtering.
Apply repeated formats consistently.
Formatting
Number Formats
| Value | Format | Display |
|---|---|---|
| 0.25 | Percentage | 25% |
| 1250 | Currency | ₺1,250.00 |
| 45292 | Date | 1-Jan-2024 |
| 0.5 | Time | 12:00 |
| 123456789 | Scientific | 1.23E+08 |
Formulas
Formula Basics
Excel formulas start with an equals sign and can contain constants, references, operators and functions.
- Parentheses make the order explicit:
=(B2+C2)/2. - A formula recalculates when the cells it refers to change.
- Keeping constants in their own cells, not buried in formulas, makes maintenance easier.
Formulas
Operator Precedence
| Precedence | Operation | Example |
|---|---|---|
| 1 | Parentheses | =(A1+B1)*2 |
| 2 | Negation (sign) | =-A1 |
| 3 | Percentage | =A1*10% |
| 4 | Exponent | =A1^2 |
| 5 | Multiplication and division | =A1*B1/C1 |
| 6 | Addition and subtraction | =A1+B1-C1 |
| 7 | Joining | =A1&" TL" |
| 8 | Comparison | =A1>B1 |
Operations of equal precedence go left to right: =12/3*2 → 8.
Formulas
A Formula Evaluated Step by Step
Excel first replaces references with their values: B1 → 4.
Parentheses come first: (4-1) → 3.
Then the exponent: 3^2 → 9.
Then multiplication: 3*9 → 27.
Addition last: 2+27 → 29.
The result is 29. Without the parentheses, =2+3*4-1^2 → 2+12-1 = 13.
In Excel the sign (negation) is applied before the exponent: -2 becomes (-2) first.
(-2)^2 = 4. To get the mathematical -2² = -4, write =-(2^2) or =0-2^2.
The result is 4.
Formulas
Cell References
Shifts with the row and column when copied.
A1Stays fixed when copied.
$A$1Only the row or the column is fixed.
$A1 · A$1Prefixed with the sheet name.
Sales!B2Covers everything from the first to the last cell.
A1:A10A meaningful name makes the formula readable.
VAT_RateFormulas
References When Filling Down
Three different formulas go in B1, C1 and D1; all three point to A1 (5).
One row down with the fill handle: A1 is relative, so it became A2; $A$1 and A$1 kept the row fixed.
One more row: the relative reference moved to A3 (15). The absolute and mixed references still point to A1.
Result: when filling down, A$1 behaves like an absolute reference (the row is locked). To see the difference, fill right.
Formulas
References When Filling Right
Row 1 holds data (5, 7, 9, 11). Four kinds of reference go in A3:A6; all point to A1.
One column right: A1 → B1 and A$1 → B$1 (the column moved); $A$1 and $A1 kept the column locked.
In column C, the relative and A$1-style references point to C1 (9); a column letter with a $ never changes.
The rule: $ locks the part right after it. $A$1 locks both, A$1 only the row, $A1 only the column.
Formulas
Mixed References: A Multiplication Table
One formula goes in B2: =$A2*B$1 → 4·1 = 4. Column A and row 1 are locked.
C2: =$A2*C$1 → 8. Copied right, only the unlocked column letter changed (B→C).
D2: =$A2*D$1 → 12. Copied right, only the unlocked column letter changed (B→D).
B3: =$A3*B$1 → 5. Copied down, only the unlocked row number changed.
C3: =$A3*C$1 → 10. Copied down, only the unlocked row number changed.
D3: =$A3*D$1 → 15. Copied down, only the unlocked row number changed.
The last row is filled too: one formula built the whole table; every cell multiplies its row's A value by row 1 of its column.
Formulas · Step by step
F4 and the Absolute Reference Shortcut
=B2*E1 is typed. With the cursor on the E1 reference, press F4. E1 is relative: filled down it becomes E2, E3.
1st F4: $E$1, absolute. Column and row are fixed; the VAT rate is always read from E1.
2nd F4: E$1, mixed. Only the row is fixed; the column letter changes when filled right.
3rd F4: $E1, mixed. Only the column is fixed; the row number changes when filled down.
4th F4: the cycle is complete, back to E1 (relative). When you reach the form you want, press Enter.
$ yourself.Formulas · Step by step
A Relative Reference Error in a Copied Formula
Three prices are in column B and the discount rate in a single cell: E1 = 20%. The discounted-price formula goes in C2.
C2: =B2*(1-E1) → 200 × 0.8 = 160. Correct.
Copied down with the fill handle, both references move one row: =B3*(1-E2). E2 is empty, and an empty cell counts as 0 → 150 × 1 = 150. No discount was applied!
C4: =B4*(1-E3) → 80. Excel shows no error message: the results are numbers, so at first glance they don't look wrong. This is the sneakiest relative-reference bug.
The fix: in C2, with the cursor on E1, press F4 → =B2*(1-$E$1). B2 stays relative (each row needs its own price); E1 becomes absolute.
Copied down: =B3*(1-$E$1). It moved to B3 while $E$1 stayed put → 150 × 0.8 = 120.
C4: =B4*(1-$E$1) → 80 × 0.8 = 64. Change the rate to 30% and all three results update at once.
Check: Formulas → Show Formulas (Ctrl + `) shows formulas instead of results. If a reference to a single cell must stay the same on every row, look for the $.
C2: =B2*(1-E1)
C3: =B3*(1-E2) ← when copied
C4: =B4*(1-E3)
C2: =B2*(1-$E$1)
C3: =B3*(1-$E$1)
C4: =B4*(1-$E$1)Formulas
Basic Functions
| Function | Example | Result |
|---|---|---|
| SUM | =SUM(B2:B10) | Adds the values |
| AVERAGE | =AVERAGE(B2:B10) | Arithmetic mean |
| MAX | =MAX(B2:B10) | Largest value |
| MIN | =MIN(B2:B10) | Smallest value |
| COUNT | =COUNT(B2:B10) | Number of numeric cells |
| COUNTIF / SUMIF | =COUNTIF(B2:B10,">=50") | Counts / sums the cells that meet a condition |
| TODAY | =TODAY() | Current date |
Formulas
Functions on the Same Data
SUM adds only the numbers: 12+7+20+5 = 44. The text “none” and the empty cell are ignored.
AVERAGE = 44 / 4 = 11. The divisor is 4, not 6: only numbers count (an empty cell is not taken as 0).
MAX finds the largest number: 20.
MIN finds the smallest number: 5.
COUNT counts the cells that hold numbers: 4. (For all non-empty cells, use COUNTA.)
Formulas
The IF Function
=IF(B2>=60, "Pass", "Fail")
Produces TRUE or FALSE: B2>=60
Returned when the test is TRUE: “Pass”
Returned when the test is FALSE: “Fail”
=EĞER(B2>=60;"Pass";"Fail")).Formulas
IF Row by Row
C2: B2 is 75; the test is 75>=60 → TRUE.
The test is TRUE, so the second argument is returned: C2 = “Pass”.
C3: B3 is 45; the test is 45>=60 → FALSE.
The test is FALSE, so the third argument is returned: C3 = “Fail”.
C4: B4 = 60; 60>=60 → TRUE (>= includes equality; with > it would be FALSE).
The test is TRUE, so the second argument is returned: C4 = “Pass”.
Formulas
Logical Functions: AND, OR, NOT, IFERROR
Sample data: B2 = 72 (grade), C2 = 65 (attendance %), D2 = 0
| Formula | Result | Description |
|---|---|---|
=AND(B2>=60,C2>=70) | FALSE | Both must be TRUE; C2 fails |
=OR(B2>=60,C2>=70) | TRUE | At least one TRUE is enough |
=NOT(B2>=60) | FALSE | Turns TRUE into FALSE |
=IFERROR(B2/D2,"Can't compute") | Can't compute | B2/0 is an error; text is shown instead |
=IF(B2>=85,"AA",IF(B2>=70,"BB","CC")) | BB | Nested IF: the value of the first true condition |
=IF(AND(B2>=60,C2>=70),"Pass","Fail") | Fail | AND serves as the test of IF |
).Formulas
COUNTIF and SUMIF
COUNTIF(range, criteria): “Electrical” appears 3 times in the Dept column.
SUMIF(range, criteria, sum_range): the Electrical rows' scores 72+85+58 = 215.
Criteria can be a comparison too: ">=80" goes in quotes → 2 people (91, 85).
Formulas
Lookup with VLOOKUP
VLOOKUP(lookup_value, table, col_index, 0): finds the value in the first column of the table and returns the requested column from the same row. A last argument of 0 (FALSE) means exact match; approximate match (1/TRUE) on sorted data is not for this job.
A2 = “Ali” ≠ “Ece” → move down a row.
A3 = “Ece” = “Ece” → found.
Column 2 of the found row, B3, is returned: 91.
“Mert” isn't in any row of the first column: every row was tried and the result is #N/A (value not available).
Formulas · Step by step
Approximate Match with VLOOKUP: a Grading Scale
Columns D–E hold the grading scale: the first column is the threshold, sorted ascending. The last argument is TRUE: not exact but approximate match. The threshold table is locked with $, so it doesn't shift when the formula is copied down.
C2: looking up 72. The result is the largest threshold less than or equal to 72 in the first column: 0, 50, 60, 70 qualify, 85 doesn't → the row of 70, column 2 → B.
C3: looking up 85. Equality counts too: 85 ≤ 85 → the largest qualifying threshold is 85 → A.
C4: looking up 49. Only 0 qualifies (50 > 49) → F. With exact match (FALSE), scores missing from the table, like 72 or 49, would give #N/A.
C5: looking up −5. No threshold is less than or equal to −5 → #N/A. The smallest threshold should be the smallest possible score (0); invalid scores can be blocked with data validation.
In approximate match Excel doesn't scan row by row; it does a binary search, so the first column must be sorted ascending. On an unsorted scale it can return the wrong letter with no error message.
C2: =VLOOKUP(B2,$D$2:$E$6,2,TRUE) C3: =VLOOKUP(B3,$D$2:$E$6,2,TRUE) C4: =VLOOKUP(B4,$D$2:$E$6,2,TRUE) C5: =VLOOKUP(B5,$D$2:$E$6,2,TRUE)
Formulas
Modern Functions: XLOOKUP and FILTER
=XLOOKUP("Ece",A2:A5,B2:B5) → 91
=FILTER(A2:B5,B2:B5>=80) → Ece 91 / Can 85The modern successor to VLOOKUP; no column number needed, and it can look left.
Appears when a non-empty cell blocks the spill range; clear it and the result appears.
Formulas
Dates and Times
Returns the current date.
=TODAY()Returns the current date and time.
=NOW()Dates are numbers, so you can subtract them.
=B2-A2The same value can be shown as a date, a time or a number.
A date stored as text may not take part in calculations.
A duration over 24 hours may need a suitable custom format.
Formulas
Formula Errors
Selecting an error cell shows a warning icon, and the Formulas → Formula Auditing tools help trace the source.
Formulas · Step by step
Formula Errors Step by Step
#DIV/0!: the formula =B2/C2 gives #DIV/0!. Cause: the divisor C2 = 0 (an empty cell counts as 0). Fix: check the divisor or write =IF(C2=0,"",B2/C2).
#NAME?: the formula =SUMM(B2:B4) gives #NAME?. Cause: Excel doesn't recognize “SUMM” (a misspelled function name; text without quotes gives the same error). Fix: =SUM(B2:B4).
#VALUE!: the formula =B2*2 gives #VALUE!. Cause: B2 holds the text “12 TL” (left-aligned), not a number. Fix: type just 12 in B2 and show “TL” with a number format.
#REF!: the formula =B2+#REF! gives #REF!. Cause: a column the formula used was deleted (invalid reference). Fix: undo with Ctrl+Z or retype the reference.
#N/A: the formula =VLOOKUP(D2,A2:B4,2,0) gives #N/A. Cause: “Selin” isn't in the list (exact match). Fix: check the spelling, add the record, or show a message with IFNA (Excel 2013+).
Organizing Data
AutoFill
Carries a formula or constant into adjacent cells.
Continues a pattern such as 1, 2, 3 or dates.
Shifts with position as the formula is copied.
Drag it from the bottom-right corner of the selection.
Choose results such as Copy Cells, Fill Series or Fill Formatting Only.
Inspect the first and last results for a wrong pattern.
Organizing Data
Shortcuts and Flash Fill
- Flash Fill: next to the “Ayşe Yılmaz” column, type Ayşe in the first row and press Ctrl+E in the next cell; Excel guesses the pattern and fills the column.
- The results are values, not formulas, and don't update when the source changes; review the filled rows one by one.
- Before a bulk change with Ctrl+H, check the search scope under Options.
Data Analysis
Excel Tables
Turns a range with headers into a table.
Can take in new rows and columns.
Adds controls to the header cells.
Formulas can use column names instead of cell addresses.
Summarizes with the functions you pick.
Gives banded rows and a consistent look.
Data Analysis
Sorting
Sorting rearranges records by certain columns while keeping each row intact.
A to Z or Z to A.
Smallest to largest or the reverse.
Oldest to newest or the reverse.
More than one key, e.g. department first, then score.
Must not be sorted along with the data.
Don't mix numbers and numbers stored as text in one column.
Data Analysis
Sorting Before / After
Start: records in the order they were entered. Each row holds one student's data.
Data → Sort, Score: largest to smallest. Rows moved as whole units: every name kept its own score.
Multilevel: Dept A→Z first, and within the same dept by Score (Electrical: 85, 72).
Wrong use: sort only the Score column and names and scores no longer match (Ali now has 91!). In the warning, choose “Expand the selection”.
Data Analysis
Filtering
Shows only the rows that meet the criteria; it doesn't delete the others.
Shows the chosen categories.
Applies greater-than, less-than, between or period conditions.
Uses contains, begins with or equals.
Conditions narrow the results together.
Remove the filter and all records show again.
Data Analysis
Filtering Before / After
Data → Filter: drop-down buttons (▾) appear on the headers; all records are still visible.
Only Electrical is selected in the Dept column. The row numbers are blue and skip: the other rows weren't deleted, just hidden.
A second condition: Number Filters → Greater Than 80. The conditions narrow together (AND): only Can is left.
Clear Filter: every record shows again. Filtering doesn't change the data; it only narrows the view.
Data Analysis · Step by step
Multilevel Sort, Filter and SUBTOTAL
Six records in entry order. Below them, three formulas: SUM, SUBTOTAL(9,…) (sum) and SUBTOTAL(1,…) (average). For now all three cover every row: 443 and 73.8.
Two levels in the Sort dialog: Dept A to Z first, and within each dept Score largest to smallest. Computer: Çağla 91, Selin 78, Burak 55. Rows moved as whole units.
Filter: Computer only. Row numbers skip; 5–7 are hidden. SUM is still 443: it adds hidden rows too. SUBTOTAL counts only the visible ones: 91 + 78 + 55 = 224, average 74.7.
A second condition, Score > 75: the two filters narrow together, and Burak (55) is hidden too. SUBTOTAL(9) = 169, SUBTOTAL(1) = 84.5. SUM didn't change.
Clear: the filters go, all rows return, and SUBTOTAL is 443 again. Filtering deleted no data.
Name A to Z: with a Turkish sort locale Ç comes after C and İ after I: Ali, Burak, Can, Çağla, İrem, Selin. The formulas point to C2:C7, so sorting doesn't change their results.
Sort: Dept A to Z, Score Largest to Smallest Data → Filter; Dept ▾: Computer only Score ▾: Number Filters → Greater Than 75 Data → Clear Name ▾: Sort A to Z
Data Analysis
Conditional Formatting
Apply a format at certain thresholds.
Show low and high values with a color gradient.
Visualize the size of a value inside the cell.
Mark status or trend with icons.
Uses a custom logical condition.
Too many colors and rules make the data hard to read.
Data Analysis
A Conditional Formatting Example
The same data shown five ways: first the raw scores.
Home → Conditional Formatting → Highlight Cells Rules → Less Than 60: 58 and 45 turn red. If a value changes, so does its format.
Data bars: the largest value (91) gets the longest bar; compare without reading the numbers.
Color scale: the lowest value (45) gets the lightest shade, the highest (91) the darkest.
Icon set: the top third of the range (45–91) gets ▲, the middle ●, the bottom ▼. The default thresholds are 67% and 33%, so the cut-offs are 75.8 and 60.2.
Data Analysis
PivotTables
Source: a list with headers and no blank rows. Insert → PivotTable opens an empty PivotTable on a new sheet.
The Dept field is dragged to Rows: each dept is listed once (3 rows + Grand Total).
The Score field goes to Values: each dept's total score is calculated (Electrical: 72+85+58 = 215).
Score is summarized by Average, and the Name field is counted: Electrical averages 71.7 with 3 people. A summary with no formulas; when the source changes, Refresh.
Data Analysis
Data Validation
Offers the user drop-down choices.
Accepts only values within set limits.
Limits entries to a date range.
Explains the expected data when the cell is selected.
Tells the user about invalid entries.
Copy and paste can get around some validation checks, so audit the data.
Data Analysis · Step by step
Data Validation: List and Error Alert
1. Select the range: select the cells to validate (B2:B4) and open Data → Data Tools → Data Validation.
2. List rule: on the Settings tab choose Allow: List; type the choices in Source, or point it at a cell range.
3. Result: a drop-down list appears in the cell; the user can only pick one of the source values.
4. Error alert: on the Error Alert tab set the Style, Title and Error message.
5. Invalid entry: type “Banana”, which isn't in the list, and the Stop style rejects it; Retry puts the cell back into editing.
6. Styles: Stop blocks the entry; Warning and Information let the user accept the value anyway.
Charts
Choosing a Chart
| Purpose | Suitable chart | Example |
|---|---|---|
| Comparing categories | Column or bar | |
| Showing change over time | Line chart | |
| Part-to-whole | A pie chart with few categories, or a 100% bar | |
| Relationship between two numeric variables | Scatter chart (XY) | |
| Showing the shape of a distribution | Histogram |
Charts
Clear Charts
Says what the chart shows.
Say what the values mean.
Pick the right range and series.
Set so that differences aren't exaggerated.
Separates series; doesn't carry meaning on its own.
Drop needless 3-D effects and decoration.
Reporting
Printing and Page Layout
Sets which cells go into the printout.
Consider landscape for wide tables.
Can fit a table to the page width.
Repeats the header row on multi-page printouts.
Control where the printout splits.
Check for blank pages, cut-off columns and readability.
Workbook
Managing Workbooks
Shape the workbook to your needs.
Give a name that describes the sheet's contents.
Moves a sheet within the same or another workbook.
Takes helper sheets out of view; no security.
Groups sheets visually.
Manage references to other sheets or workbooks carefully.
Workbook
Protection and Collaboration
Protects certain cells and operations from changes.
Can restrict inserting, deleting or moving sheets.
Can require a password to open the file.
Enable discussion and feedback on cells.
Gives access to earlier versions in a supported cloud location.
Sheet protection is not the same as strong encryption.
Quality
Good Data Layout
- Each row should hold one record, and each column one variable.
- Headers should be short, unique and meaningful; put the unit in the header.
- No blank rows, merged cells or subtotals in raw data.
- Keep raw data, calculations and report output on separate sheets.
Summary
Chapter Summary
- A workbook consists of one or more worksheets.
- Formulas start with an equals sign and use cell references.
- Relative and absolute references behave differently when a formula is copied.
- Functions standardize common calculations; errors point to a problem in the data or a reference.
- Tables, sorting, filtering and validation keep data organized.
- The right chart depends on the purpose of the data and the kind of relationship.
- COUNTIF/SUMIF count and sum by condition; VLOOKUP pulls values from a table; a PivotTable summarizes without formulas.
Summary
Review Questions
- What is the difference between a workbook and a worksheet?
- Does a number format change a cell's underlying value?
- How do the references A1 and $A$1 behave differently?
- What are the three parts of the IF function?
- Why must rows stay intact when sorting?
- Which chart suits showing change over time?
Summary
Exercises
- Build a 5-row table with Item, Qty and Price columns; compute the Total and the total with VAT (VAT rate in a single cell, with
$). - Fill a 1–10 multiplication table with one formula typed in B2 (
=$A2*B$1). - In a grade list, show Pass/Fail with IF; find the number of passing students with COUNTIF and the class average with AVERAGE.
- Compute the total score per department with SUMIF; check the same result with a PivotTable.
- From a name–score list, fetch a student's score with VLOOKUP; try a name that isn't in the list and observe #N/A.
- Apply a “red if below 60” rule to the scores and draw a column chart whose axis starts at 0.
Summary
References
- Microsoft Support, Basic tasks in Excel.
- Microsoft Support, Overview of formulas in Excel.
- Microsoft Support, Sort data in a range or table.