Chapter 3.2: Microsoft Excel

Computer Fundamentals and Introduction to Programming
WorksheetsData entryFormulasData analysisCharts
Sercan KÜLCÜ · Giresun University

Introduction

Learning Outcomes

Workbook

Use the ideas of sheet, cell, row, column and range.

Formulas

Calculate with cell references and basic functions.

Organizing data

Apply tables, sorting, filtering and validation.

Visualization

Pick a chart that fits the data and present it clearly.

Introduction

What Is Excel For?

Calculation

Repeatable calculations with formulas and functions.

List management

Organize records in rows and columns.

Analysis

Sorting, filtering and summarizing.

Visualization

Trends and comparisons with charts.

Planning

Budgets, schedules, stock and task tracking.

Sharing

Co-editing and commenting on a workbook.

Worksheets

Basic Concepts

B4fx125ABCDEF1234561258040603590752055Sheet1Sheet2+Name BoxColumn letterRow no.Active cellFormula barRange B4:D6Sheet tabs
Workbook

The whole Excel file (.xlsx).

Worksheet

The grid of cells inside a workbook.

Cell / range

B4 is a single cell; B4:D6 is a rectangular range.

Worksheets

The Grid and Cell References

C3fx30ABCD123ItemQtyPriceTotalPen128Notebook53096150

C3 = where column C meets row 3. The Name Box shows the address of the selected cell; the formula bar shows its contents.

D2fx=B2*C2ABCD123ItemQtyPriceTotalPen128Notebook53096150

D2 holds a formula: =B2*C2. The cell shows the result (96); the formula bar shows the formula itself.

D3fx=B3*C3ABCD123ItemQtyPriceTotalPen128Notebook53096150

D3: =B3*C3 → 5·30 = 150. A formula uses addresses, not values.

B2fx20ABCD123ItemQtyPriceTotalPen208Notebook530160150

B2 goes from 12 to 20, and D2 recalculates by itself (160). That is the power of references.

Data Entry

Data Types

PenTextleft1250Numberright1-Mar-2024DaterightTRUELogicalcenter24Formula =B2*2result right#DIV/0!Error valuecenter
Text

Names, descriptions and categories.

Number

A quantity that can take part in calculations.

Date and time

Time information that Excel stores as serial numbers.

Logical value

TRUE or FALSE.

Formula

A calculation that starts with an equals sign.

Error value

A special result that reports a problem with a formula or the data.

Data Entry

Entering and Editing Data

Edit cellF2
Confirm entryEnter
New line in a cellAlt+Enter
Clear contentsDelete
Select rowShift+Space
Select columnCtrl+Space
  • Numbers stored as text can break sorting and calculations.
  • One observation per cell and one attribute per column make analysis easier.
A2fxAtatürk Cad. No:5ABC123AddressAmountCodeAtatürk Cad. No:5Giresun125000125İstasyon Sok. 398000087EditF2 / double-clickEdit mode; shown in the status barAlt+Enternew line in the same cell (Giresun)Green trianglenumber stored as text (00125);left-aligned, SUM ignores it

Worksheets

Rows, Columns and Sheets

Before scrollingABC12345NameMidtermFinalAli7080Ece9085Can6072Deniz5564Scrolled down: row 1 stays putABC141424344NameMidtermFinalSelin8891Burak7670Mert6581Zehra9390View → Freeze Panes → Freeze Top Row
Insert / delete

Changes the structure by shifting cells at the selected spot.

Size

Row height and column width are set by hand or automatically.

Hiding

Removes data from view without deleting it.

Freezing

Keeps header rows or columns visible while you scroll.

Sheet name

Use a short name that describes the contents.

Sheet layout

Keep data, calculation and report sheets apart.

Formatting

Cell Formatting

BeforeABC1234ItemPriceQtyPen125012Notebook35.540Eraser8100AfterABC1234ItemPriceQtyPen₺1,250.0012Notebook₺35.5040Eraser₺8.00100
Font and fill

Set off headers and key areas.

Alignment

Positions text within a cell.

Borders

Can mark table edges; overuse hurts readability.

Wrap Text

Splits long content over several lines within a cell.

Merging

OK for titles; in data ranges it gets in the way of sorting and filtering.

Cell styles

Apply repeated formats consistently.

Formatting

Number Formats

ValueFormatDisplay
0.25Percentage25%
1250Currency₺1,250.00
45292Date1-Jan-2024
0.5Time12:00
123456789Scientific1.23E+08
B2fx0.2525%displayed (Percentage)stored valueB3fx452921-Jan-2024displayed (Date)stored value
A format changes only the display; calculations use the underlying value shown in the formula bar.

Formulas

Formula Basics

Excel formulas start with an equals sign and can contain constants, references, operators and functions.

formula startfunctionrange referenceoperatorcell referenceoperatorconstant=SUM(B2:B4)*C1+5
  • 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

PrecedenceOperationExample
1Parentheses=(A1+B1)*2
2Negation (sign)=-A1
3Percentage=A1*10%
4Exponent=A1^2
5Multiplication and division=A1*B1/C1
6Addition and subtraction=A1+B1-C1
7Joining=A1&" TL"
8Comparison=A1>B1

Operations of equal precedence go left to right: =12/3*2 → 8.

Formulas

A Formula Evaluated Step by Step

B14B1 = 4reference → 4=2+3*(B1-1)^2

Excel first replaces references with their values: B1 → 4.

B14B1 = 4inside parentheses → 3=2+3*(4-1)^2

Parentheses come first: (4-1) → 3.

B14B1 = 4exponent → 9=2+3*3^2

Then the exponent: 3^2 → 9.

B14B1 = 4multiplication → 27=2+3*9

Then multiplication: 3*9 → 27.

B14B1 = 4addition → 29=2+27

Addition last: 2+27 → 29.

B14B1 = 4=29result

The result is 29. Without the parentheses, =2+3*4-1^2 → 2+12-1 = 13.

negation (sign) → -2=-2^2

In Excel the sign (negation) is applied before the exponent: -2 becomes (-2) first.

exponent → 4=(-2)^2

(-2)^2 = 4. To get the mathematical -2² = -4, write =-(2^2) or =0-2^2.

=4result

The result is 4.

Formulas

Cell References

Relative

Shifts with the row and column when copied.

A1
Absolute

Stays fixed when copied.

$A$1
Mixed

Only the row or the column is fixed.

$A1 · A$1
Another sheet

Prefixed with the sheet name.

Sales!B2
Range

Covers everything from the first to the last cell.

A1:A10
Named range

A meaningful name makes the formula readable.

VAT_Rate
ABCDE12345B2D4Range B2:D4 = 3 columns × 3 rows = 9 cellsReference to another sheetC1fx=Sales!B2*VAT_RateB2 on the Sales sheet = 1000cell named VAT_Rate = 0.2 → C1 = 200SummarySalesSettingsIf the name has a space, quote it: 'Sales 2024'!B2

Formulas

References When Filling Down

ABCD12345101520Relative A1=A1= 5Absolute $A$1=$A$1= 5Mixed A$1=A$1= 5

Three different formulas go in B1, C1 and D1; all three point to A1 (5).

ABCD12345101520Relative A1=A1= 5=A2= 10Absolute $A$1=$A$1= 5=$A$1= 5Mixed A$1=A$1= 5=A$1= 5

One row down with the fill handle: A1 is relative, so it became A2; $A$1 and A$1 kept the row fixed.

ABCD12345101520Relative A1=A1= 5=A2= 10=A3= 15Absolute $A$1=$A$1= 5=$A$1= 5=$A$1= 5Mixed A$1=A$1= 5=A$1= 5=A$1= 5

One more row: the relative reference moved to A3 (15). The absolute and mixed references still point to A1.

ABCD12345101520Relative A1=A1= 5=A2= 10=A3= 15=A4= 20Absolute $A$1=$A$1= 5=$A$1= 5=$A$1= 5=$A$1= 5Mixed A$1=A$1= 5=A$1= 5=A$1= 5=A$1= 5

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

ABCD12345657911RelativeA1=A1= 5Absolute$A$1=$A$1= 5MixedA$1=A$1= 5Mixed$A1=$A1= 5

Row 1 holds data (5, 7, 9, 11). Four kinds of reference go in A3:A6; all point to A1.

ABCD12345657911RelativeA1=A1= 5=B1= 7Absolute$A$1=$A$1= 5=$A$1= 5MixedA$1=A$1= 5=B$1= 7Mixed$A1=$A1= 5=$A1= 5

One column right: A1 → B1 and A$1 → B$1 (the column moved); $A$1 and $A1 kept the column locked.

ABCD12345657911RelativeA1=A1= 5=B1= 7=C1= 9Absolute$A$1=$A$1= 5=$A$1= 5=$A$1= 5MixedA$1=A$1= 5=B$1= 7=C$1= 9Mixed$A1=$A1= 5=$A1= 5=$A1= 5

In column C, the relative and A$1-style references point to C1 (9); a column letter with a $ never changes.

ABCD12345657911RelativeA1=A1= 5=B1= 7=C1= 9=D1= 11Absolute$A$1=$A$1= 5=$A$1= 5=$A$1= 5=$A$1= 5MixedA$1=A$1= 5=B$1= 7=C$1= 9=D$1= 11Mixed$A1=$A1= 5=$A1= 5=$A1= 5=$A1= 5

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

B2fx=$A2*B$1ABCD12341234564$A: columnlockedB$1: rowlocked

One formula goes in B2: =$A2*B$1 → 4·1 = 4. Column A and row 1 are locked.

C2fx=$A2*C$1ABCD123412345648$A: columnlockedB$1: rowlocked

C2: =$A2*C$1 → 8. Copied right, only the unlocked column letter changed (B→C).

D2fx=$A2*D$1ABCD12341234564812$A: columnlockedB$1: rowlocked

D2: =$A2*D$1 → 12. Copied right, only the unlocked column letter changed (B→D).

B3fx=$A3*B$1ABCD123412345648125$A: columnlockedB$1: rowlocked

B3: =$A3*B$1 → 5. Copied down, only the unlocked row number changed.

C3fx=$A3*C$1ABCD12341234564812510$A: columnlockedB$1: rowlocked

C3: =$A3*C$1 → 10. Copied down, only the unlocked row number changed.

D3fx=$A3*D$1ABCD1234123456481251015$A: columnlockedB$1: rowlocked

D3: =$A3*D$1 → 15. Copied down, only the unlocked row number changed.

D4fx=$A4*D$1ABCD123412345648125101561218$A: columnlockedB$1: rowlocked

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

F2fx=B2*E1F4not pressed yetColumn (E):shiftsRow (1):shiftswhen filling down / right

=B2*E1 is typed. With the cursor on the E1 reference, press F4. E1 is relative: filled down it becomes E2, E3.

F2fx=B2*$E$1F41st pressColumn (E):constantRow (1):constantwhen filling down / right

1st F4: $E$1, absolute. Column and row are fixed; the VAT rate is always read from E1.

F2fx=B2*E$1F42nd pressColumn (E):shiftsRow (1):constantwhen filling down / right

2nd F4: E$1, mixed. Only the row is fixed; the column letter changes when filled right.

F2fx=B2*$E1F43rd pressColumn (E):constantRow (1):shiftswhen filling down / right

3rd F4: $E1, mixed. Only the column is fixed; the row number changes when filled down.

F2fx=B2*E1F44th press: back to startColumn (E):shiftsRow (1):shiftswhen filling down / right

4th F4: the cycle is complete, back to E1 (relative). When you reach the form you want, press Enter.

F4 works while editing a formula with the cursor on a reference. Laptops may need Fn+F4; on a Mac the shortcut may differ. You can also type the $ yourself.

Formulas · Step by step

A Relative Reference Error in a Copied Formula

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2003Pen1504Eraser80

Three prices are in column B and the discount rate in a single cell: E1 = 20%. The discounted-price formula goes in C2.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1504Eraser80=B2*(1-E1)

C2: =B2*(1-E1) → 200 × 0.8 = 160. Correct.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1501504Eraser80=B3*(1-E2)

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!

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1501504Eraser8080=B4*(1-E3)

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.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1504Eraser80=B2*(1-$E$1)

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.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1501204Eraser80=B3*(1-$E$1)

Copied down: =B3*(1-$E$1). It moved to B3 while $E$1 stayed put → 150 × 0.8 = 120.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1501204Eraser8064=B4*(1-$E$1)

C4: =B4*(1-$E$1) → 80 × 0.8 = 64. Change the rate to 30% and all three results update at once.

ABCDE1ItemPriceDiscountedDiscount20%2Notebook2001603Pen1501204Eraser8064=B2*(1-$E$1)=B3*(1-$E$1)=B4*(1-$E$1)

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

FunctionExampleResult
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

B1234567Score127none205=SUM(B2:B7)44adds the numbers=AVERAGE(B2:B7)=MAX(B2:B7)=MIN(B2:B7)=COUNT(B2:B7)B4: text, B6: empty cell

SUM adds only the numbers: 12+7+20+5 = 44. The text “none” and the empty cell are ignored.

B1234567Score127none205=SUM(B2:B7)44adds the numbers=AVERAGE(B2:B7)11sum / count=MAX(B2:B7)=MIN(B2:B7)=COUNT(B2:B7)B4: text, B6: empty cell

AVERAGE = 44 / 4 = 11. The divisor is 4, not 6: only numbers count (an empty cell is not taken as 0).

B1234567Score127none205=SUM(B2:B7)44adds the numbers=AVERAGE(B2:B7)11sum / count=MAX(B2:B7)20largest=MIN(B2:B7)=COUNT(B2:B7)B4: text, B6: empty cell

MAX finds the largest number: 20.

B1234567Score127none205=SUM(B2:B7)44adds the numbers=AVERAGE(B2:B7)11sum / count=MAX(B2:B7)20largest=MIN(B2:B7)5smallest=COUNT(B2:B7)B4: text, B6: empty cell

MIN finds the smallest number: 5.

B1234567Score127none205=SUM(B2:B7)44adds the numbers=AVERAGE(B2:B7)11sum / count=MAX(B2:B7)20largest=MIN(B2:B7)5smallest=COUNT(B2:B7)4how many numbersB4: text, B6: empty cell

COUNT counts the cells that hold numbers: 4. (For all non-empty cells, use COUNTA.)

Formulas

The IF Function

=IF(B2>=60, "Pass", "Fail")
Logical test

Produces TRUE or FALSE: B2>=60

Value if true

Returned when the test is TRUE: “Pass”

Value if false

Returned when the test is FALSE: “Fail”

Read the cellB2>=60 ?TRUE“Pass”FALSE“Fail”
Function names and argument separators depend on Excel's language and regional settings: some localized versions translate the names and separate arguments with ; (Turkish Excel writes =EĞER(B2>=60;"Pass";"Fail")).

Formulas

IF Row by Row

C2fx=IF(B2>=60,"Pass","Fail")ABC1234NameScoreResultAli75Can45Ece60Read the cell75>=60 → TRUETRUE“Pass”FALSE“Fail”

C2: B2 is 75; the test is 75>=60 → TRUE.

C2fx=IF(B2>=60,"Pass","Fail")ABC1234NameScoreResultAli75PassCan45Ece60Read the cell75>=60 → TRUETRUE“Pass”FALSE“Fail”

The test is TRUE, so the second argument is returned: C2 = “Pass”.

C3fx=IF(B3>=60,"Pass","Fail")ABC1234NameScoreResultAli75PassCan45Ece60Read the cell45>=60 → FALSETRUE“Pass”FALSE“Fail”

C3: B3 is 45; the test is 45>=60 → FALSE.

C3fx=IF(B3>=60,"Pass","Fail")ABC1234NameScoreResultAli75PassCan45FailEce60Read the cell45>=60 → FALSETRUE“Pass”FALSE“Fail”

The test is FALSE, so the third argument is returned: C3 = “Fail”.

C4fx=IF(B4>=60,"Pass","Fail")ABC1234NameScoreResultAli75PassCan45FailEce60Read the cell60>=60 → TRUETRUE“Pass”FALSE“Fail”

C4: B4 = 60; 60>=60 → TRUE (>= includes equality; with > it would be FALSE).

C4fx=IF(B4>=60,"Pass","Fail")ABC1234NameScoreResultAli75PassCan45FailEce60PassRead the cell60>=60 → TRUETRUE“Pass”FALSE“Fail”

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

FormulaResultDescription
=AND(B2>=60,C2>=70)FALSEBoth must be TRUE; C2 fails
=OR(B2>=60,C2>=70)TRUEAt least one TRUE is enough
=NOT(B2>=60)FALSETurns TRUE into FALSE
=IFERROR(B2/D2,"Can't compute")Can't computeB2/0 is an error; text is shown instead
=IF(B2>=85,"AA",IF(B2>=70,"BB","CC"))BBNested IF: the value of the first true condition
=IF(AND(B2>=60,C2>=70),"Pass","Fail")FailAND serves as the test of IF
In a nested IF the conditions are tried in order; start from the highest threshold, and every IF closes its own ).

Formulas

COUNTIF and SUMIF

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58=COUNTIF(B2:B7,"Electrical")= 3counts rows where Dept = “Electrical”=SUMIF(B2:B7,"Electrical",C2:C7)=COUNTIF(C2:C7,">=80")

COUNTIF(range, criteria): “Electrical” appears 3 times in the Dept column.

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58=COUNTIF(B2:B7,"Electrical")= 3counts rows where Dept = “Electrical”=SUMIF(B2:B7,"Electrical",C2:C7)= 215adds the scores of rows that meet the condition=COUNTIF(C2:C7,">=80")

SUMIF(range, criteria, sum_range): the Electrical rows' scores 72+85+58 = 215.

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58=COUNTIF(B2:B7,"Electrical")= 3counts rows where Dept = “Electrical”=SUMIF(B2:B7,"Electrical",C2:C7)= 215adds the scores of rows that meet the condition=COUNTIF(C2:C7,">=80")= 2counts scores of 80 and above

Criteria can be a comparison too: ">=80" goes in quotes → 2 people (91, 85).

Formulas

Lookup with VLOOKUP

D2fx=VLOOKUP("Ece",A2:B5,2,0)AB12345NameScoreAli72Ece91Can85Deniz64lookup value: “Ece”table: A2:B5 (searched in the first column)column: 2 (Score)0: exact match

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.

D2fx=VLOOKUP("Ece",A2:B5,2,0)AB12345NameScoreAli72Ece91Can85Deniz64lookup value: “Ece”table: A2:B5 (searched in the first column)column: 2 (Score)0: exact match

A2 = “Ali” ≠ “Ece” → move down a row.

D2fx=VLOOKUP("Ece",A2:B5,2,0)AB12345NameScoreAli72Ece91Can85Deniz64lookup value: “Ece”table: A2:B5 (searched in the first column)column: 2 (Score)0: exact match

A3 = “Ece” = “Ece” → found.

D2fx=VLOOKUP("Ece",A2:B5,2,0)AB12345NameScoreAli72Ece91Can85Deniz64lookup value: “Ece”table: A2:B5 (searched in the first column)column: 2 (Score)0: exact matchD2 = 91

Column 2 of the found row, B3, is returned: 91.

D2fx=VLOOKUP("Mert",A2:B5,2,0)AB12345NameScoreAli72Ece91Can85Deniz64lookup value: “Mert”table: A2:B5 (searched in the first column)column: 2 (Score)0: exact matchD2 = #N/A

“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

ABCDE1NameScoreLetterThresholdLetter2Ece720F3Can8550D4Mert4960C5Ada-570B685A

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.

ABCDE1NameScoreLetterThresholdLetter2Ece72B0F3Can8550D4Mert4960C5Ada-570B685A 0 ≤ 72 ? yes 50 ≤ 72 ? yes 60 ≤ 72 ? yes 70 ≤ 72 ? yes 85 ≤ 72 ? no

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.

ABCDE1NameScoreLetterThresholdLetter2Ece72B0F3Can85A50D4Mert4960C5Ada-570B685A 0 ≤ 85 ? yes 50 ≤ 85 ? yes 60 ≤ 85 ? yes 70 ≤ 85 ? yes 85 ≤ 85 ? yes

C3: looking up 85. Equality counts too: 85 ≤ 85 → the largest qualifying threshold is 85 → A.

ABCDE1NameScoreLetterThresholdLetter2Ece72B0F3Can85A50D4Mert49F60C5Ada-570B685A 0 ≤ 49 ? yes 50 ≤ 49 ? no 60 ≤ 49 ? no 70 ≤ 49 ? no 85 ≤ 49 ? no

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.

ABCDE1NameScoreLetterThresholdLetter2Ece72B0F3Can85A50D4Mert49F60C5Ada-5#N/A70B685A 0 ≤ -5 ? no 50 ≤ -5 ? no 60 ≤ -5 ? no 70 ≤ -5 ? no 85 ≤ -5 ? no

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.

ABCDE1NameScoreLetterThresholdLetter2Ece72B0F3Can85A50D4Mert49F60C5Ada-5#N/A70B685A

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 85
XLOOKUP(lookup_value, lookup_array, return_array)

The modern successor to VLOOKUP; no column number needed, and it can look left.

#SPILL!

Appears when a non-empty cell blocks the spill range; clear it and the result appears.

ABCDE12345NameScoreAli72Ece91Can85Deniz64Ece91Can85D2fx=FILTER(A2:B5,B2:B5>=80)the result spills into D2:E3
These functions exist in Microsoft 365 and Excel 2021 and later; they don't work in Excel 2019 or earlier. The Excel version of whoever opens the file matters too.

Formulas

Dates and Times

11-Jan-1900≈453521-Mar-20244536615-Mar-2024=B2-A2 → 14 daystime = fraction of a day: 0.5 → 12:00
TODAY

Returns the current date.

=TODAY()
NOW

Returns the current date and time.

=NOW()
Date difference

Dates are numbers, so you can subtract them.

=B2-A2
Format

The same value can be shown as a date, a time or a number.

Invalid dates

A date stored as text may not take part in calculations.

Durations

A duration over 24 hours may need a suitable custom format.

Formulas

Formula Errors

=B2/C2 (C2 = 0)#DIV/0!Division by zero:check the divisor=SUMM(A1:A3)#NAME?Unknown name:function spelling (SUM)="apple"+5#VALUE!Wrong data type:arithmetic on text=B2+#REF!#REF!Invalid reference:column C was deleted1250000 (narrow column)#####Value doesn't fit:widen the column=VLOOKUP("Mert",A2:B5,2,0)#N/AValue not found:not in the lookup list

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

D2fx=B2/C21ABCD1234ItemAmountQtyUnit pricePen5000#DIV/0!Notebook300560Eraser9033023CauseC2 is 0: division by zero is undefined.An empty cell counts as zero too.4FixCheck the divisor or make it conditional:=IF(C2=0,"",B2/C2)

#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).

B5fx=SUMM(B2:B4)1AB12345ItemPricePen50Notebook120Eraser30Total#NAME?23CauseExcel doesn't recognize “SUMM”: thefunction name is misspelled.4FixFix the function name (picking it fromthe Formulas tab avoids typos):=SUM(B2:B4)

#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).

C2fx=B2*21ABC1234ItemPriceDoublePen12 TL#VALUE!Notebook2550Eraser81623Cause“12 TL” in B2 is text, not a number.You can't do arithmetic on text.4FixType just 12 in the cell; show TL with anumber format (Currency):=B2*2 → 24

#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.

C2fx=B2+#REF!1ABC1234ItemPriceTotalPen12#REF!Notebook25#REF!Eraser8#REF!23CauseThe column the formula used was deleted;a reference to a deleted cell is invalid.4FixUndo the deletion right away with Ctrl+Z, orretype the reference. Clearing contentsinstead of deleting the column keeps it.

#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.

C2fx=VLOOKUP(D2,A2:B4,2,0)1ABCD1234NameGradeResultLookupAli70#N/ASelinAyşe85Can6023CauseThe lookup value D2 (“Selin”) isn't in A2:A4.The last argument 0 means exact match.4FixCheck spelling and spaces, or add therecord; show “-” instead of the error:=IFNA(VLOOKUP(D2,A2:B4,2,0),"-")

#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

12345two values: step 1JanFebMarAprMaymonth namesItem 1Item 2Item 3Item 4Item 5text + number55555single number: copies
Copying

Carries a formula or constant into adjacent cells.

Series

Continues a pattern such as 1, 2, 3 or dates.

Relative reference

Shifts with position as the formula is copied.

Fill handle

Drag it from the bottom-right corner of the selection.

Options

Choose results such as Copy Cells, Fill Series or Fill Formatting Only.

Checking

Inspect the first and last results for a wrong pattern.

Organizing Data

Shortcuts and Flash Fill

UndoCtrl+Z
Format CellsCtrl+1
Create tableCtrl+T
Toggle filterCtrl+Shift+L
Flash FillCtrl+E
Find and ReplaceCtrl+H
AB1Full NameName2Ayşe YılmazAyşe3Mehmet Demir4Zeynep KayaAB1Full NameName2Ayşe YılmazAyşe3Mehmet DemirMehmet4Zeynep KayaZeynepCtrl+E1) Type an example in B2, Ctrl+E in B32) Pattern is guessed: values, not formulas
  • 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

ItemPenNotebookEraserRulerTotalCategoryStationeryStationeryStationeryStationerySales120854030275Compassfilter / sort button in the headerbanded rowstotal row (choice of function)a new row typed below joins the table
Structured data

Turns a range with headers into a table.

Auto-expansion

Can take in new rows and columns.

Filter and sort

Adds controls to the header cells.

Structured references

Formulas can use column names instead of cell addresses.

Total row

Summarizes with the functions you pick.

Style

Gives banded rows and a consistent look.

Data Analysis

Sorting

Sorting rearranges records by certain columns while keeping each row intact.

Text

A to Z or Z to A.

Number

Smallest to largest or the reverse.

Date

Oldest to newest or the reverse.

Multilevel

More than one key, e.g. department first, then score.

Header row

Must not be sorted along with the data.

Consistent type

Don't mix numbers and numbers stored as text in one column.

Sort×Add LevelDelete LevelCopy LevelMy data has headersColumnSort OnOrderSort byDept▾Cell Values▾A to Z▾AfterScore▾Cell Values▾Largest to Smallest▾OKCancelResultDeptScorePhysics88Physics71Chemistry92Chemistry64Math80Dept A→Z; within a dept, score largest to smallest

Data Analysis

Sorting Before / After

ABC123456NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78▾▾▾Unsorted data5 records, 3 columns

Start: records in the order they were entered. Each row holds one student's data.

ABC123456NameDeptScoreEceComputer91CanElectrical85BurakMechanical78AliElectrical72DenizComputer64▾▾↓(was row 3)(was row 4)(was row 6)(was row 2)(was row 5)Sort: ScoreLargest to Smallestrows move as a whole

Data → Sort, Score: largest to smallest. Rows moved as whole units: every name kept its own score.

ABC123456NameDeptScoreEceComputer91DenizComputer64CanElectrical85AliElectrical72BurakMechanical78▾↑↓(was row 3)(was row 5)(was row 4)(was row 2)(was row 6)Multilevel sort1. Dept: A to Z2. Score: largest to smallest

Multilevel: Dept A→Z first, and within the same dept by Score (Electrical: 85, 72).

ABC123456NameDeptScoreAliElectrical91EceComputer85CanElectrical78DenizComputer72BurakMechanical64▾▾↓ERROR: rows brokenOnly the Score column was selected,“Continue with the current selection”was chosen

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

Purpose

Shows only the rows that meet the criteria; it doesn't delete the others.

Value filter

Shows the chosen categories.

Number / date filter

Applies greater-than, less-than, between or period conditions.

Text filter

Uses contains, begins with or equals.

Several columns

Conditions narrow the results together.

Clearing

Remove the filter and all records show again.

City▾Score▾Sort A to ZSort Z to AText Filters ▸(Select All)AnkaraGiresunİzmirOKCancelResultCityScore12Ankara784Giresun855Ankara667Giresun90rows 3 and 6 (İzmir) hidden, not deletedBlue row numbers and a funnel icon: filter on

Data Analysis

Filtering Before / After

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58▾▾▾No filterall 6 records visible

Data → Filter: drop-down buttons (▾) appear on the headers; all records are still visible.

ABC1247NameDeptScoreAliElectrical72CanElectrical85SelinElectrical58▾⧩▾Dept = Electrical3 of 6 records visiblerow numbers skip

Only Electrical is selected in the Dept column. The row numbers are blue and skip: the other rows weren't deleted, just hidden.

ABC14NameDeptScoreCanElectrical85▾⧩⧩Dept = ElectricalAND Score > 801 of 6 records

A second condition: Number Filters → Greater Than 80. The conditions narrow together (AND): only Can is left.

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58▾▾▾Filter clearedall 6 records are back

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

ABC1NameDeptScore2SelinComputer783CanElectrical854ÇağlaComputer915AliElectrical646İremMechanical707BurakComputer55C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)443C11: =SUBTOTAL(1,C2:C7)73.8

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.

ABC1NameDeptScore2ÇağlaComputer913SelinComputer784BurakComputer555CanElectrical856AliElectrical647İremMechanical70C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)443C11: =SUBTOTAL(1,C2:C7)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.

ABC1Name ▾Dept ▾Score ▾2ÇağlaComputer913SelinComputer784BurakComputer55C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)224C11: =SUBTOTAL(1,C2:C7)74.7Dept = Computer

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.

ABC1Name ▾Dept ▾Score ▾2ÇağlaComputer913SelinComputer78C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)169C11: =SUBTOTAL(1,C2:C7)84.5and Score > 75

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.

ABC1Name ▾Dept ▾Score ▾2ÇağlaComputer913SelinComputer784BurakComputer555CanElectrical856AliElectrical647İremMechanical70C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)443C11: =SUBTOTAL(1,C2:C7)73.8

Clear: the filters go, all rows return, and SUBTOTAL is 443 again. Filtering deleted no data.

ABC1Name ▾Dept ▾Score ▾2AliElectrical643BurakComputer554CanElectrical855ÇağlaComputer916İremMechanical707SelinComputer78C9: =SUM(C2:C7)443C10: =SUBTOTAL(9,C2:C7)443C11: =SUBTOTAL(1,C2:C7)73.8

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

Cell rules

Apply a format at certain thresholds.

Color scales

Show low and high values with a color gradient.

Data bars

Visualize the size of a value inside the cell.

Icon sets

Mark status or trend with icons.

Formula rule

Uses a custom logical condition.

Restraint

Too many colors and rules make the data hard to read.

Highlight Cells Rules4580629530Greater Than 60 → filledData Bars4580629530bar length ∝ valueColor Scales4580629530low red → high greenIcon Sets▼45▲80▶62▲95▼30≥67% ▲ ≥33% ▶ other ▼

Data Analysis

A Conditional Formatting Example

AB12345678NameScoreAliEceCanDenizBurakSelinMert72918564785845Raw scoresNo rule

The same data shown five ways: first the raw scores.

AB12345678NameScoreAliEceCanDenizBurakSelinMert72918564785845Cell ruleLess Than 60 → red fill

Home → Conditional Formatting → Highlight Cells Rules → Less Than 60: 58 and 45 turn red. If a value changes, so does its format.

AB12345678NameScoreAliEceCanDenizBurakSelinMert72918564785845Data barsBar length proportional to value

Data bars: the largest value (91) gets the longest bar; compare without reading the numbers.

AB12345678NameScoreAliEceCanDenizBurakSelinMert72918564785845Color scaleLow light → high dark

Color scale: the lowest value (45) gets the lightest shade, the highest (91) the darkest.

AB12345678NameScoreAliEceCanDenizBurakSelinMert72918564785845Icon set▲ ≥ 67% · ● ≥ 33% · ▼ otherthresholds: 75.8 and 60.2

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

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58FieldsRows:—Values:Dept(empty PivotTable)

Source: a list with headers and no blank rows. Insert → PivotTable opens an empty PivotTable on a new sheet.

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58FieldsRows:DeptValues:DeptComputerElectricalMechanicalGrand Total

The Dept field is dragged to Rows: each dept is listed once (3 rows + Grand Total).

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58FieldsRows:DeptValues:Sum of ScoreDeptComputerElectricalMechanicalGrand TotalSum of Score15521578448

The Score field goes to Values: each dept's total score is calculated (Electrical: 72+85+58 = 215).

ABC1234567NameDeptScoreAliElectrical72EceComputer91CanElectrical85DenizComputer64BurakMechanical78SelinElectrical58FieldsRows:DeptValues:Average of ScoreCount of NameDeptComputerElectricalMechanicalGrand TotalAvg Score77.571.778.074.7People2316

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

ListElectricalElectricalComputerMechanicalInput messageEnter a gradeWhole number from 0 to 100Error alert (150 typed)Microsoft Excel✕This value doesn't matchthe data validation restrictionsdefined for this cell.RetryCancel
List

Offers the user drop-down choices.

Number range

Accepts only values within set limits.

Date limit

Limits entries to a date range.

Input message

Explains the expected data when the cell is selected.

Error alert

Tells the user about invalid entries.

Limitation

Copy and paste can get around some validation checks, so audit the data.

Data Analysis · Step by step

Data Validation: List and Error Alert

FileHomeInsertFormulasDataReviewSort & FilterData ToolsData ValidationAB1234ItemOutlinePenNotebookEraserSelected range: B2:B4The rule applies to these cells.

1. Select the range: select the cells to validate (B2:B4) and open Data → Data Tools → Data Validation.

Data ValidationSettingsInput MessageError AlertAllow:ListData:betweenSource:Electrical,Computer,MechanicalIn-cell dropdownIgnore blankOKCancelThe source can be given two ways:Typed in directly:Electrical,Computer,MechanicalOr linked to a cell range:=$E$2:$E$4The separator follows regional settings(a comma in English settings).

2. List rule: on the Settings tab choose Allow: List; type the choices in Source, or point it at a cell range.

AB1234ItemOutlinePenElectricalNotebookEraserElectricalComputerMechanicalSelect the cell and an arrow appears.The only choices are the valuesin the source; the chosen value goesinto the cell. Typing anything elsetriggers the error alert.

3. Result: a drop-down list appears in the cell; the user can only pick one of the source values.

Data ValidationSettingsInput MessageError AlertShow error alert after invalid data is enteredStyle:StopTitle:Invalid groupError message:Pick a group from the list.OKCancelStyle options:Stop: rejects the wrong entry.Warning: the user can accept itanyway.Information: informs; OKaccepts the value.

4. Error alert: on the Error Alert tab set the Style, Title and Error message.

AB1234ItemOutlinePenElectricalNotebookBananaEraserInvalid group✕Pick a group from the list.RetryCancelHelp

5. Invalid entry: type “Banana”, which isn't in the list, and the Stop style rejects it; Retry puts the cell back into editing.

Stop✕Pick a group from the list.RetryCancelStop: rejects the entry.Warning!Pick a group from the list.YesNoCancelWarning: Yes accepts it anyway.InformationiPick a group from the list.OKCancelInformation: OK accepts it.

6. Styles: Stop blocks the entry; Warning and Information let the user accept the value anyway.

Charts

Choosing a Chart

PurposeSuitable chartExample
Comparing categoriesColumn or bar
Showing change over timeLine chart
Part-to-wholeA pie chart with few categories, or a 100% bar
Relationship between two numeric variablesScatter chart (XY)
Showing the shape of a distributionHistogram

Charts

Clear Charts

Misleading: axis starts at 95959799JanFebMarClear: axis from 0, with title and unitsMonthly sales (₺ thousands)050100Jan96Feb98Mar97
Title

Says what the chart shows.

Axes and units

Say what the values mean.

Source data

Pick the right range and series.

Scale

Set so that differences aren't exaggerated.

Color

Separates series; doesn't carry meaning on its own.

Simplicity

Drop needless 3-D effects and decoration.

Reporting

Printing and Page Layout

Page Break PreviewABCDE12345Page 1Page 2Print Titles: row 1 on every page12
Print area

Sets which cells go into the printout.

Orientation

Consider landscape for wide tables.

Scaling

Can fit a table to the page width.

Print Titles

Repeats the header row on multi-page printouts.

Page breaks

Control where the printout splits.

Print preview

Check for blank pages, cut-off columns and readability.

Workbook

Managing Workbooks

◀ ▶DataCalcReport+Insert…DeleteRenameMove or Copy…Tab Color ▸HideRight-click a sheet tab to openthe sheet operations menu.Tab colors group sheets;hiding gives no security.
Insert / delete sheets

Shape the workbook to your needs.

Rename

Give a name that describes the sheet's contents.

Move / copy

Moves a sheet within the same or another workbook.

Hide / unhide

Takes helper sheets out of view; no security.

Tab color

Groups sheets visually.

Links

Manage references to other sheets or workbooks carefully.

Workbook

Protection and Collaboration

Sheet protection

Protects certain cells and operations from changes.

Workbook structure

Can restrict inserting, deleting or moving sheets.

File encryption

Can require a password to open the file.

Comments

Enable discussion and feedback on cells.

Version history

Gives access to earlier versions in a supported cloud location.

Limitation

Sheet protection is not the same as strong encryption.

1) Unlock the input cellsFormat Cells → ProtectionLocked(cleared for B2:B4)HiddenDefault: every cell is Locked2) Protect SheetReview → Protect SheetPassword (optional):••••••Locking works only once protection is on3) ResultItemQtyAmountPen3120Notebook5200Eraser280Qty cells can be edited;Amount (=B2*40) is locked

Quality

Good Data Layout

✗ Not fit for analysisABC12345ItemQuantityApple12 kg(blank row)Subtotal12 kg2024 Sales (merged)✓ TidyABC1234DateItemQuantity (kg)5-Jan-2024Apple125-Jan-2024Pear86-Jan-2024Apple15totals in a separate cell / report sheet
  • 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

  1. What is the difference between a workbook and a worksheet?
  2. Does a number format change a cell's underlying value?
  3. How do the references A1 and $A$1 behave differently?
  4. What are the three parts of the IF function?
  5. Why must rows stay intact when sorting?
  6. Which chart suits showing change over time?

Summary

Exercises

  1. 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 $).
  2. Fill a 1–10 multiplication table with one formula typed in B2 (=$A2*B$1).
  3. In a grade list, show Pass/Fail with IF; find the number of passing students with COUNTIF and the class average with AVERAGE.
  4. Compute the total score per department with SUMIF; check the same result with a PivotTable.
  5. 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.
  6. 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.

The End

Microsoft Excel
Previous: Chapter 3.1: Microsoft Word · Next: Chapter 3.3: Microsoft PowerPoint
1 / 1Sercan KÜLCÜ. All rights reserved.