Microsoft Excel — Complete Study

Guide

1. INTRODUCTION TO MICROSOFT EXCEL

Microsoft Excel is a spreadsheet application developed by

Microsoft, part of the Microsoft Office suite. It is used for data
entry, calculation, analysis, charting, and database
management.

Key Terms:

Workbook – The Excel file (.xlsx)

Worksheet (Sheet) – A single tab/page inside a workbook

Cell – The intersection of a row and a column (e.g., A1, B5)

Row – Horizontal line of cells (numbered 1, 2, 3…)

Column – Vertical line of cells (labeled A, B, C…)

Range – A group of cells (e.g., A1:D10)

Active Cell – The currently selected cell

Name Box – Shows the address of the active cell

Formula Bar – Displays the content/formula of the active

cell

2. THE EXCEL INTERFACE

Ribbon Tabs:

Tab Purpose

File Save, Open, Print, Options

Home Formatting, Clipboard, Cells

Insert Charts, Tables, Pictures

Page Layout Margins, Orientation, Print Area

Formulas Functions, Name Manager

Data Sort, Filter, Data Tools

Review Spelling, Comments, Protect

View Zoom, Freeze Panes, Windows

Key Interface Components:

Quick Access Toolbar (QAT) – Customizable toolbar at the

top

Status Bar – Bottom bar showing Sum, Average, Count of

selected cells

Sheet Tabs – At the bottom, for switching between sheets

Scroll Bars – For navigating the sheet

3. BASIC OPERATIONS

Navigation Shortcuts:
 Key Action

Ctrl + Home Go to cell A1

Ctrl + End Go to last used cell

Ctrl + Arrow Jump to edge of data

Tab Move right

Shift + Tab Move left

Enter Move down

F5 / Ctrl+G Go To dialog

Selection:

Action Method

Select a range Click and drag, or Shift + Arrow

Select entire row Click row number

Select entire column Click column letter

Select entire sheet Ctrl + A

Select non-adjacent Ctrl + Click

Editing Cells:

F2 – Edit a cell

Delete – Clear cell content

 Ctrl + Z – Undo

Ctrl + Y – Redo

Ctrl + C / X / V – Copy / Cut / Paste

4. DATA ENTRY

Types of Data in Excel:

1. Text (Labels) – Left-aligned by default (e.g., Name, City)

2. Numbers (Values) – Right-aligned by default

3. Dates/Times – Stored as numbers, displayed as dates

4. Formulas – Begin with = sign

5. Logical Values – TRUE / FALSE

Auto-Fill:

Enter a value → drag the Fill Handle (small square at

bottom-right of cell)

Works for numbers, dates, days, months, and custom lists

Flash Fill (Ctrl + E):

Automatically fills a pattern Excel detects (e.g., splitting

First Name from Full Name)

5. FORMATTING

Cell Formatting (Ctrl + 1):

 Font – Type, Size, Bold (Ctrl+B), Italic (Ctrl+I), Underline
(Ctrl+U)

Alignment – Left, Center, Right; Wrap Text; Merge & Center

Number Format – General, Number, Currency, Accounting,

Date, Percentage, Text

Borders – All borders, Thick border, etc.

Fill – Background color

Number Formats:

Format Example

General 1234.5

Number 1,234.50

Currency ₦1,234.50 or $1,234.50

Accounting $ 1,234.50

Date 26-Jun-2026

Percentage 75%

Text 001234 (preserves leading zeros)

Conditional Formatting:

Automatically formats cells based on rules

Home → Conditional Formatting

Options: Highlight Cells Rules, Data Bars, Color Scales,

Icon Sets
6. FORMULAS AND FUNCTIONS

Formula Rules:

All formulas start with =

Use cell references instead of hardcoded values

Operators: + (add), - (subtract), * (multiply), / (divide), ^

(exponent)

Parentheses control order of operations

Cell References:

Type Example Behaviour

Relative A1 Changes when copied

Does not change when

Absolute $A$1
copied

Mixed (Row) A$1 Row locked

Mixed
$A1 Column locked
(Column)

Press F4 to toggle between reference types.

7. COMMON EXCEL FUNCTIONS

Mathematical Functions:
Function Syntax Description

SUM =SUM(A1:A10) Adds a range

Mean of a
AVERAGE =AVERAGE(A1:A10)
range

Smallest
MIN =MIN(A1:A10)
value

MAX =MAX(A1:A10) Largest value

Counts
COUNT =COUNT(A1:A10)
numeric cells

Counts non-
COUNTA =COUNTA(A1:A10)
empty cells

Counts
COUNTBLANK =COUNTBLANK(A1:A10)
empty cells

Rounds to 2
ROUND =ROUND(A1,2) decimal
places

Absolute
ABS =ABS(A1)
value

Remainder
MOD =MOD(10,3)
(returns 1)

POWER =POWER(2,3) 2³ = 8

SQRT =SQRT(A1) Square root

 Function Syntax Description

Returns 3
INT =INT(3.9)
(integer part)

Multiplies all
PRODUCT =PRODUCT(A1:A5)
values

Logical Functions:

Function Syntax Description

=IF(condition, value_if_true,
IF Conditional test
value_if_false)

True if ALL
AND =AND(A1>0, B1>0)
conditions true

True if ANY
OR =OR(A1>0, B1>0)
condition true

Reverses logical
NOT =NOT(A1=5)
value

Multiple
IFS =IFS(A1>90,"A", A1>80,"B")
conditions

=IFERROR(formula,
IFERROR Handles errors
value_if_error)

IF Example:

=IF(A1>=50, "Pass", "Fail")

Text Functions:

Function Syntax Description

Number of
LEN =LEN(A1)
characters

First 3
LEFT =LEFT(A1,3)
characters

Last 3
RIGHT =RIGHT(A1,3)
characters

4 characters
MID =MID(A1,2,4) from
position 2

Converts to
UPPER =UPPER(A1)
UPPERCASE

Converts to
LOWER =LOWER(A1)
lowercase

First Letter
PROPER =PROPER(A1)
Capitalised

Removes
TRIM =TRIM(A1) extra
spaces

CONCATENATE =CONCATENATE(A1," ",B1) Joins text

Joins text
CONCAT =CONCAT(A1,B1)
(newer)
 Function Syntax Description

Joins range
TEXTJOIN =TEXTJOIN(" ",TRUE,A1:A5) with
delimiter

Finds
FIND =FIND("a",A1) position of
text

Replaces
SUBSTITUTE =SUBSTITUTE(A1,"old","new")
text

Formats
TEXT =TEXT(A1,"dd-mmm-yyyy") value as
text

Date & Time Functions:

Function Description

=TODAY() Today's date

=NOW() Current date and time

=DATE(year,month,day) Creates a date

=DAY(A1) Day component

=MONTH(A1) Month component

=YEAR(A1) Year component

=WEEKDAY(A1) Day of week (1=Sun)

 Function Description

=DATEDIF(start,end,"D") Days between dates

=NETWORKDAYS(start,end) Working days between dates

=EDATE(date,months) Date n months away

Lookup & Reference Functions:

Function Syntax Description

=VLOOKUP(lookup, table,
VLOOKUP Vertical lookup
col, FALSE)

=HLOOKUP(lookup, table, Horizontal

HLOOKUP
row, FALSE) lookup

Returns value at
INDEX =INDEX(range, row, col)
position

Returns position
MATCH =MATCH(value, range, 0)
of value

Modern
=XLOOKUP(lookup, array,
XLOOKUP replacement for
return)
VLOOKUP

Returns offset
OFFSET =OFFSET(A1,rows,cols)
cell

CHOOSE =CHOOSE(2,"A","B","C") Returns "B"

ROW =ROW(A5) Returns 5

 Function Syntax Description

COLUMN =COLUMN(B1) Returns 2

VLOOKUP Notes:

4th argument: FALSE = exact match, TRUE = approximate

match

Can only look to the RIGHT

XLOOKUP is more flexible (looks in any direction)

Statistical Functions:

Function Description

MEDIAN Middle value

MODE Most frequent value

STDEV Standard deviation

VAR Variance

RANK Rank of a value in a list

LARGE(range,k) kth largest value

SMALL(range,k) kth smallest value

PERCENTILE Value at given percentile

FREQUENCY Frequency distribution

SUMIF / COUNTIF / AVERAGEIF:

 =SUMIF(range, criteria, sum_range)
=COUNTIF(range, criteria)
=AVERAGEIF(range, criteria, average_range)

Example:
=SUMIF(A1:A10, "Lagos", B1:B10) → Sum B where A
= "Lagos"
=COUNTIF(A1:A10, ">50") → Count values >
50

For multiple criteria, use SUMIFS, COUNTIFS, AVERAGEIFS.

8. CHARTS AND GRAPHS

Chart Types:

Chart Best Used For

Column / Bar Comparing categories

Line Trends over time

Pie / Doughnut Parts of a whole

Area Cumulative trends

Scatter (XY) Correlation between variables

Histogram Frequency distribution

Bubble 3-variable comparison

Radar / Spider Multi-variable comparison

Creating a Chart:

1. Select data range

2. Insert → Charts → Choose chart type

3. Use Chart Design / Format tabs to customise

Chart Elements:

Chart Title, Axis Titles

Legend, Data Labels

Gridlines, Plot Area

Data Series, Trendline

9. SORTING AND FILTERING

Sorting:

Data → Sort → choose column and order (A-Z, Z-A,

smallest to largest)

Multi-level sort: Sort by column A, then by column B

AutoFilter:

Data → Filter → dropdown arrows appear in headers

Filter by value, condition, colour, or search

Advanced Filter:

Filter to a different location

Use complex criteria (multiple conditions)

Data → Advanced
10. DATA TOOLS

Remove Duplicates:

Data → Remove Duplicates → select columns

Text to Columns:

Splits one column into multiple based on delimiter

(comma, space, etc.)

Data → Text to Columns

Data Validation:

Restricts what can be entered in a cell

Data → Data Validation

Options: Whole Number, Decimal, List (dropdown), Date,

Text Length

What-If Analysis:

Tool Use

Goal Seek Find input that gives a desired result

Scenario Manager Compare multiple scenarios

Data Table Show results for a range of input values

11. PIVOT TABLES

A PivotTable summarises large data sets dynamically.

Creating a PivotTable:

1. Click inside data → Insert → PivotTable

2. Choose placement (new or existing sheet)

3. Drag fields into: Rows, Columns, Values, Filters

PivotTable Areas:

Area Function

Rows Categories down the left

Columns Categories across the top

Values Data to calculate (Sum, Count, Average)

Filters Filter the entire table

PivotChart:

A chart linked to a PivotTable

Insert → PivotChart

Slicers:

Visual filter buttons for PivotTables

PivotTable Analyze → Insert Slicer

12. NAMED RANGES

Give a cell or range a name for easier formula writing

Formulas → Define Name or type in Name Box

 Example: Name A1:A10 as "Sales", then use =SUM(Sales)

13. PROTECTING WORKBOOKS AND

SHEETS

Protect Sheet:

Review → Protect Sheet → Set password

Prevents editing of locked cells

Protect Workbook:

Review → Protect Workbook → Prevents adding/deleting

sheets

Lock/Unlock Cells:

By default all cells are locked

Unlock cells first: Format Cells → Protection → uncheck

Locked

Then apply sheet protection

14. PRINTING

Key Print Settings:

Page Layout → Margins – Set page margins

Page Layout → Orientation – Portrait or Landscape

Page Layout → Print Area – Set what to print

 Page Layout → Print Titles – Repeat rows/columns on
each page

File → Print Preview – Preview before printing

Insert → Header & Footer – Add page numbers, date, file

name

Page Break:

View → Page Break Preview to see and adjust breaks

Insert → Page Breaks

15. COMMON EXCEL ERRORS

Error Meaning Common Cause

=A1/0 or dividing by
#DIV/0! Division by zero
empty cell

#VALUE! Wrong data type Text in a numeric formula

#REF! Invalid reference Deleted a referenced cell

Unrecognised
#NAME? Misspelled function
name

Value not VLOOKUP found no

#N/A
available match

Invalid Wrong use of range

#NULL!
intersection operator

#NUM! Invalid number SQRT of negative number

 Error Meaning Common Cause

Column too
##### Widen the column
narrow

16. KEYBOARD SHORTCUTS SUMMARY

Shortcut Action

Ctrl + S Save

Ctrl + P Print

Ctrl + Z Undo

Ctrl + Y Redo

Ctrl + C Copy

Ctrl + X Cut

Ctrl + V Paste

Ctrl + F Find

Ctrl + H Find & Replace

Ctrl + Home Go to A1

Ctrl + End Last used cell

Ctrl + Shift + L Toggle AutoFilter

 Shortcut Action

Alt + = AutoSum

F2 Edit cell

F4 Repeat / Toggle $ in references

F5 Go To

F11 Create chart in new sheet

F12 Save As

Ctrl + 1 Format Cells dialog

Ctrl + ; Insert today's date

Ctrl + Shift + : Insert current time

17. ADVANCED FEATURES

Array Formulas:

Perform calculations on multiple values at once

Entered with Ctrl + Shift + Enter (older Excel)

In Excel 365/2019+: Dynamic Arrays auto-spill

Dynamic Array Functions (Excel 365):

 Function Description

FILTER Filters a range based on conditions

SORT Sorts a range

UNIQUE Returns unique values

SEQUENCE Generates a sequence of numbers

RANDARRAY Generates random numbers in an array

Power Query (Get & Transform):

Data → Get Data

Import, clean, and transform data from external sources

Available from Excel 2016+

Macros and VBA:

Macro – Recorded sequence of actions

VBA (Visual Basic for Applications) – Programming

language for Excel

Developer → Record Macro → perform steps → Stop

Recording

Developer → Visual Basic → write/edit code

XLOOKUP (Modern VLOOKUP):

=XLOOKUP(lookup_value, lookup_array, return_array,

[if_not_found], [match_mode], [search_mode])
 Example:
=XLOOKUP(D2, A2:A100, C2:C100, "Not Found")

18. EXCEL FILE FORMATS

Format Extension Notes

Excel Workbook .xlsx Default, no macros

Excel Macro-
.xlsm Contains VBA macros
Enabled

Excel Binary .xlsb Faster, smaller file

Excel Template .xltx Template file

Plain text, no
CSV .csv
formatting

PDF .pdf For sharing, read-only

Excel 97-2003 .xls Old format

19. SAMPLE MCQ PRACTICE

1. Which function returns the number of cells containing

numbers?
A) COUNTA B) COUNT C) COUNTBLANK D) SUM → Answer: B

2. What symbol starts every formula in Excel?

A) # B) @ C) = D) $ → Answer: C
3. Which of the following is an absolute cell reference?
A) A1 B) $A1 C) A$1 D) $A$1 → Answer: D

4. VLOOKUP looks for a value in which direction?

A) Horizontal B) Vertical C) Diagonal D) Any direction →
Answer: B

5. Which key toggles AutoFilter on a selected range?

A) Ctrl+A B) Ctrl+Shift+L C) Ctrl+F D) F5 → Answer: B

6. What does the error #DIV/0! mean?

A) Wrong data type B) Division by zero C) Invalid reference D)
Value not found → Answer: B

7. Which chart type is best for showing trends over time?

A) Pie B) Bar C) Line D) Scatter → Answer: C

8. What does the TRIM function do?

A) Deletes a cell B) Removes extra spaces C) Rounds numbers
D) Sorts data → Answer: B

9. To lock a row reference in a formula, you press:

A) F2 B) F4 C) F5 D) F11 → Answer: B

10. Which tab contains the PivotTable option?

A) Home B) Data C) Insert D) View → Answer: C

End of Excel Complete Study Guide