Microsoft

Excel

—

Hard

Skills

0 1

Core

Workbook

Worksheet

Skills

Workbook

worksheet

operations

Insert

rename

move

copy

group

and

colour

code

sheets

;

work

confidently

across

multiple

open

workbooks

FOUN DAT ION A L

Navigation

selection

techniques

Ctrl

Arrow

jump

data

edges

Ctrl

Shift

Arrow

extend

selection

Name

Box

and

(

navigation

FOUN DAT ION A L

Cell

references

Relative

(

1),

absolute

(

$A$

and

mixed

(

references

;

cycle

reference

types

while

editing

FOUN DAT ION A L

Cross

sheet

& 3-

references

Sheet

external

links

such

Budget

xlsx

]

1'!

and

ranges

SUM

(

Jan

Dec

5).

FOUN DAT ION A L

Rows

columns

range

handling

Insert

delete

hide

and

autofit

;

fill

handle

;

Ctrl

and

Ctrl

;

transposing

and

resizing

ranges

FOUN DAT ION A L

Excel

Tables

(

Ctrl

)

Structured

references

totals

row

calculated

columns

auto

expansion

and

table

styles

FOUN DAT ION A L

Named

ranges

Define

edit

and

scope

names

workbook

sheet

level

;

apply

names

formulas

validation

and

chart

series

IN T ER MEDIAT E

Freeze

panes

split

view

custom

views

Lock

headers

place

compare

distant

regions

sheet

and

save

window

and

settings

per

view

IN T ER MEDIAT E

Page

setup

printing

areas

repeating

title

rows

scale

fit

margins

headers

and

footers

manual

page

breaks

FOUN DAT ION A L

Paste

Special

Paste

values

formats

formulas

and

column

widths

;

transpose

skip

blanks

and

add

multiply

operations

FOUN DAT ION A L

Find

Replace

Special

Wildcards

(? * ~),

format

based

selecting

blanks

constants

and

formulas

and

filling

blanks

with

Ctrl

Enter

IN T ER MEDIAT E

Sheet

workbook

protection

Lock

and

unlock

cells

set

allow

edit

ranges

protect

structure

and

windows

password

protect

workbooks

IN T ER MEDIAT E

1 / 7

0 2

Formulas

Functions

2.1 ·

MAT H

S TAT IS T ICAL

FUNCT IO NS

SUM

SUMIF

SUMIFS

Conditional

totals

across

single

and

multiple

criteria

ranges

including

wildcard

and

date

range

criteria

FOUN DAT ION A L

AVERAGE

family

AVERAGE

AVERAGEIF

AVERAGEIFS

MEDIAN

and

MODE

SNGL

for

central

tendency

analysis

FOUN DAT ION A L

Counting

functions

COUNT

COUNTA

COUNTBLANK

COUNTIF

and

COUNTIFS

for

record

counts

and

conditional

tallies

FOUN DAT ION A L

MIN

MAX

LARGE

SMALL

Extremes

and

nth

value

extraction

from

ranges

including

top

and

bottom

reporting

FOUN DAT ION A L

Rounding

multiples

ROUND

ROUNDUP

ROUNDDOWN

MROUND

CEILING

FLOOR

INT

and

TRUNC

for

controlled

precision

IN T ER MEDIAT E

SUMPRODUCT

Weighted

calculations

and

array

style

multi

condition

logic

without

requiring

array

entry

IN T ER MEDIAT E

SUBTOTAL

AGGREGATE

Totals

that

ignore

hidden

rows

nested

subtotals

error

values

—

essential

filtered

reports

IN T ER MEDIAT E

Statistical

spread

STDEV

VAR

PERCENTILE

INC

QUARTILE

INC

CORREL

and

SLOPE

for

distribution

analysis

A DVA N CED

Ranking

RANK

and

RANK

AVG

plus

rank

with

criteria

patterns

built

COUNTIFS

IN T ER MEDIAT E

2.2 ·

LO G ICAL

E RRO R

HANDLING

nested

IFS

Branching

logic

and

multi

condition

decision

trees

with

best

practice

for

readable

nesting

FOUN DAT ION A L

AND

NOT

XOR

Compound

Boolean

tests

inside

formulas

validation

rules

and

conditional

formatting

FOUN DAT ION A L

IFERROR

IFNA

Clean

handling

division

errors

failed

lookups

and

missing

data

without

breaking

reports

FOUN DAT ION A L

SWITCH

Value

matching

alternative

long

nested

chains

for

banding

and

category

mapping

IN T ER MEDIAT E

functions

ISBLANK

ISNUMBER

ISTEXT

ISERROR

ISNA

and

ISREF

for

robust

formula

guards

IN T ER MEDIAT E

2.3 ·

LO O KUP

RE FE RE NCE

VLOOKUP

HLOOKUP

Exact

and

approximate

matches

wildcard

lookups

and

locking

table

arrays

with

absolute

references

FOUN DAT ION A L

2 / 7

INDEX

MATCH

Leftward

two

way

and

column

insertion

proof

lookups

—

the

classic

robust

lookup

pattern

IN T ER MEDIAT E

XLOOKUP

XMATCH

Modern

lookups

with

modes

not

found

handling

and

array

returning

matches

IN T ER MEDIAT E

CHOOSE

CHOOSECOLS

CHOOSEROWS

Index

based

selection

from

lists

and

arrays

for

scenario

switching

and

column

picking

A DVA N CED

INDIRECT

OFFSET

Building

dynamic

ranges

and

references

with

awareness

volatility

and

recalculation

cost

A DVA N CED

Reference

utilities

ROW

COLUMN

ROWS

COLUMNS

ADDRESS

CELL

HYPERLINK

and

TRANSPOSE

IN T ER MEDIAT E

2.4 ·

T E XT

FUNCT IO NS

Text

extraction

LEFT

RIGHT

MID

and

LEN

for

parsing

codes

names

account

numbers

and

identifiers

FOUN DAT ION A L

substitution

FIND

SUBSTITUTE

REPLACE

TEXTBEFORE

and

TEXTAFTER

for

pattern

based

edits

IN T ER MEDIAT E

Cleanup

case

handling

TRIM

CLEAN

PROPER

UPPER

and

LOWER

for

standardising

inconsistent

source

text

FOUN DAT ION A L

Joining

formatting

text

CONCAT

TEXTJOIN

TEXT

with

custom

format

codes

VALUE

and

NUMBERVALUE

conversion

IN T ER MEDIAT E

2.5 ·

DAT E

T IME

FUNCT IO NS

Date

construction

TODAY

NOW

DATE

TIME

and

DATEVALUE

for

building

legitimate

date

values

rather

than

text

FOUN DAT ION A L

Date

components

DAY

MONTH

YEAR

WEEKDAY

WEEKNUM

and

ISOWEEKNUM

for

period

grouping

and

reporting

FOUN DAT ION A L

Date

arithmetic

EDATE

EOMONTH

DATEDIF

YEARFRAC

and

date

serial

numbers

for

ageing

and

tenure

calculations

IN T ER MEDIAT E

Business

day

logic

NETWORKDAYS

INTL

WORKDAY

and

WORKDAY

INTL

with

holiday

ranges

IN T ER MEDIAT E

2.6 ·

FINANCIAL

FUNCT IO NS

Investment

appraisal

NPV

IRR

XIRR

MIRR

and

XNPV

for

discounted

cash

flow

and

return

analysis

A DVA N CED

Loan

amortisation

mathematics

PMT

IPMT

PPMT

RATE

and

NPER

for

debt

schedules

and

payment

modelling

A DVA N CED

3 / 7

Depreciation

financial

modelling

SLN

and

DDB

plus

straight

line

and

accelerated

schedules

within

integrated

models

EXP ER T

2.7 ·

DYNAMIC

ARRAYS

MO DE RN

E XCE L

FILTER

Return

rows

columns

that

satisfy

one

conditions

with

fallback

for

empty

results

A DVA N CED

SORT

SORTBY

Dynamic

ordering

one

several

keys

ascending

descending

without

helper

columns

A DVA N CED

UNIQUE

duplicated

lists

and

distinct

counts

derived

directly

from

spilled

ranges

A DVA N CED

SEQUENCE

RANDARRAY

Generated

number

series

calendar

date

lists

and

randomised

sampling

arrays

A DVA N CED

Spill

ranges

the

operator

Spill

references

, #

SPILL

diagnostics

and

chaining

dynamic

ranges

into

downstream

formulas

A DVA N CED

LET

Named

intermediate

calculations

that

shorten

complex

formulas

and

reduce

recalculation

cost

EXP ER T

LAMBDA

named

functions

Build

reusable

custom

functions

with

parameters

—

VBA

required

EXP ER T

Array

shaping

VSTACK

HSTACK

TOROW

TOCOL

WRAPROWS

TAKE

DROP

and

EXPAND

for

restructuring

arrays

EXP ER T

GROUPBY

PIVOTBY

Formula

driven

grouping

and

aggregation

(

Microsoft

365, 2024+)

alternative

PivotTables

EXP ER T

Legacy

array

formulas

implicit

intersection

Ctrl

Shift

Enter

arrays

the

operator

and

migrating

legacy

arrays

dynamic

formulas

A DVA N CED

0 3

Data

Analysis

Modelling

PivotTables

Field

layout

grouping

dates

and

numeric

bands

value

field

settings

, %

parent

running

totals

and

refreshing

IN T ER MEDIAT E

Calculated

fields

calculated

items

Custom

formulas

inside

PivotTable

and

the

calculation

order

limitations

watch

for

A DVA N CED

PivotCharts

slicers

timelines

Interactive

filtering

connecting

one

slicer

multiple

PivotTables

timeline

driven

period

analysis

IN T ER MEDIAT E

GETPIVOTDATA

Stable

references

PivotTable

values

for

reports

and

dashboards

that

survive

layout

changes

A DVA N CED

4 / 7

Sorting

filtering

Multi

level

sorts

custom

lists

AutoFilter

filter

colour

icon

condition

FOUN DAT ION A L

Advanced

Filter

Criteria

ranges

with

AND

logic

unique

record

extraction

and

place

filtering

A DVA N CED

Power

Query

(

)

Connect

merge

append

unpivot

group

custom

columns

parameters

and

repeatable

refresh

A DVA N CED

Data

Model

Power

Pivot

Table

relationships

calculated

columns

and

DAX

measures

including

CALCULATE

SUMX

and

time

intelligence

EXP ER T

Data

Validation

Lists

whole

number

decimal

and

date

rules

custom

formulas

dependent

dropdowns

input

and

error

messages

IN T ER MEDIAT E

What

Analysis

Goal

Seek

one

and

two

variable

Data

Tables

and

Scenario

Manager

for

sensitivity

testing

A DVA N CED

Solver

Linear

and

non

linear

optimisation

with

decision

variables

constraints

and

integer

conditions

EXP ER T

Analysis

ToolPak

Forecast

Sheet

Regression

descriptive

statistics

histograms

correlation

matrices

and

seasonal

forecasting

A DVA N CED

0 4

Data

Visualization

Reporting

Conditional

formatting

Highlight

rules

data

bars

colour

scales

icon

sets

and

formula

driven

rules

;

rule

priority

and

management

IN T ER MEDIAT E

Chart

types

Column

bar

line

area

pie

and

doughnut

scatter

bubble

combo

waterfall

funnel

treemap

sunburst

histogram

Pareto

box

whisker

map

and

stock

charts

IN T ER MEDIAT E

Chart

formatting

Secondary

axes

axis

scaling

and

number

formats

data

labels

trendlines

error

bars

series

order

and

gap

width

IN T ER MEDIAT E

Dynamic

charts

Named

formulas

with

OFFSET

INDEX

chart

series

driven

Tables

and

formula

driven

chart

titles

A DVA N CED

Sparklines

cell

visuals

Line

column

and

win

loss

sparklines

;

REPT

based

bar

charts

;

icon

driven

status

columns

IN T ER MEDIAT E

Dashboard

design

KPI

tiles

form

controls

and

option

buttons

the

camera

tool

layered

layout

and

ready

reporting

A DVA N CED

Custom

number

formats

Accounting

currency

percentage

and

thousands

formats

;

conditional

formats

such

[

Red

]

and

;;;.

IN T ER MEDIAT E

5 / 7

0 5

Data

Cleaning

Transformation

Text

cleanup

chains

TRIM

combined

with

CLEAN

and

SUBSTITUTE

remove

non

printing

characters

stray

spaces

and

inconsistent

delimiters

IN T ER MEDIAT E

Bulk

value

fixes

Special

for

blanks

Ctrl

Enter

fill

down

and

converting

text

formatted

numbers

dates

and

leading

apostrophes

IN T ER MEDIAT E

Flash

Fill

Pattern

based

extraction

splitting

reformatting

and

concatenation

without

writing

formulas

FOUN DAT ION A L

Power

Query

ETL

workflows

duplication

splitting

pivot

and

unpivot

combining

files

from

folder

importing

CSV

JSON

and

PDF

sources

A DVA N CED

Data

quality

checks

Duplicate

detection

validation

rules

outlier

flags

completeness

checks

and

reconciliation

totals

IN T ER MEDIAT E

0 6

Automation

Scripting

Macro

recording

Recording

storing

running

and

assigning

macros

; .

xlsm

file

formats

and

macro

security

settings

A DVA N CED

VBA

object

model

Range

Cells

Worksheet

Workbook

ThisWorkbook

plus

ActiveX

and

form

control

wiring

A DVA N CED

VBA

control

flow

Then

Else

Select

Case

For

Each

While

With

blocks

arrays

and

collection

objects

A DVA N CED

User

defined

functions

add

ins

Custom

worksheet

functions

, .

xlam

add

ins

and

reusable

function

libraries

across

workbooks

EXP ER T

Event

procedures

Workbook

Open

Worksheet

Change

and

BeforeSave

events

for

event

driven

automation

and

validation

EXP ER T

Error

handling

dialogs

Error

GoTo

the

Err

object

and

MsgBox

and

InputBox

prompts

for

guided

user

interaction

A DVA N CED

Office

Scripts

Power

Automate

TypeScript

automation

for

Excel

the

web

plus

scheduled

and

triggered

cloud

flows

EXP ER T

0 7

Audit

Security

Collaboration

Formula

auditing

Trace

precedents

and

dependents

Evaluate

Formula

Show

Formulas

(

Ctrl

+ `)

and

the

Watch

Window

A DVA N CED

6 / 7

Error

checking

Circular

reference

resolution

error

tracing

and

diagnosing

, #

VALUE

!, #

REF

and

DIV

/0!

faults

IN T ER MEDIAT E

Workbook

inspection

Workbook

Statistics

Inspect

Document

and

review

linked

sources

and

external

references

A DVA N CED

Comments

notes

Threaded

comments

, @

mentions

resolving

conversations

and

legacy

notes

for

review

workflows

FOUN DAT ION A L

authoring

SharePoint

and

OneDrive

real

time

collaboration

AutoSave

version

history

and

restoring

prior

versions

IN T ER MEDIAT E

Protection

permissions

Sheet

and

workbook

protection

allow

edit

ranges

encrypted

files

and

sensitivity

labels

A DVA N CED

Model

governance

Naming

conventions

documentation

sheets

version

control

and

assumption

registers

for

shared

models

EXP ER T

0 8

Performance

Best

Practice

Calculation

control

Manual

versus

automatic

calculation

chain

awareness

recalculation

and

dependency

management

A DVA N CED

Performance

optimisation

Avoiding

whole

column

references

limiting

volatile

functions

(

OFFSET

INDIRECT

NOW

and

using

helpers

and

Tables

EXP ER T

Excel

limits

constraints

1,048,576

rows

× 16,384

columns

(

XFD

), 255-

character

sheet

names

and

levels

function

nesting

A DVA N CED

Model

architecture

Separating

inputs

calculations

and

outputs

;

preferring

INDEX

MATCH

over

OFFSET

;

one

formula

per

row

for

consistency

EXP ER T

Essential

keyboard

shortcuts

Ctrl

Shift

Alt

+ =,

Ctrl

+ `,

Alt

Ctrl

Shift

+ 1–5

number

formats

Ctrl

+ ;

and

Ctrl

Shift

+ ;.

FOUN DAT ION A L

7 / 7

Microsoft Excel Hard Skills

Prompt to Clone This Microsoft Excel Hard Skills

Apply this example's core structure, strategic pacing, and structural hierarchy directly to your own topic.

What are you generating today?