Relative Reference, Absolute Reference, Mixed Reference

Values: Paste only the results of formulas, not the formulas themselves.

Linking and Special Uses

Transpose Feature

Validation: Paste only data validation rules.

Comments and Notes: Paste comments or notes only, not the cell content.

Formats: Paste only the formatting (font, color, borders).

Formulas: Paste only the formulas.

Mathematical Operations with Paste Special

Skipping Blanks

Number Formatting

Date and Time Formatting

Text Formatting

Basic Conditional Formatting Tools

Custom Conditional Formatting

Managing Conditional Formatting

Formatting Options

Creating a Table

Table Features and Tools

Table Design and Customization

Data Management in Tables

Advanced Table Features

Specialized Filtering Tools

Advanced Sorting Techniques

Advanced Filtering Options

Dynamic Filtering with Formulas

Filtering in Tables

Key Uses of Go To Special

Types of Selections Possible

Advanced Options

Data Validation: Select cells with data validation rules.

Conditional Formats: Find cells with active conditional formatting.

Errors: Select only cells that display error values (useful for auditing).

Formulas: Select cells containing formulas, with or without resulting in errors.

Blanks: Select all blank cells in a range for quick data entry or deletion.

Constants: Find all non-formula values (numbers, text, dates, etc.).

Introduction to Pivot Tables

Dynamic Dashboards with Pivot Tables

Working with Multiple Data Sources

Data Analysis Techniques

Advanced Formatting and Design

Advanced Field Settings

Creating and Customizing Pivot Tables

Introduction to Advanced Charts

Funnel Charts (depicting stages in a process)

Pareto Charts (highlighting key contributors)

Histogram & Box and Whisker Charts (statistical analysis)

Advanced Chart Types

Waterfall Charts (showing cumulative effects)

Dynamic & Interactive Charts

Customizing and Formatting Charts

Gauge/Speedometer Chart (KPI visualization – built manually)

Sparklines (mini-charts within cells)

Scatter Plot with Trendlines (correlation analysis)

Treemap & Sunburst Charts (hierarchical data display)

Combo Charts (e.g., line & column on the same chart)

IF

Combining Logical Functions

AND, OR, NOT

IFS

IFERROR, IFNA

VLOOKUP

HLOOKUP

INDEX & MATCH

XLOOKUP

Multi-condition lookups with INDEX/MATCH or XLOOKUP.

Approximate and exact matches.

Reverse lookups (find cell address).

2-Dimensional lookups (e.g., searching based on row AND column).

Lookup Functions with Error Handling

Spill ranges for multiple results (dynamic arrays).

Advanced Use Cases

AVERAGE, MEDIAN, MODE: Calculate mean, median, and most frequent value.

MIN, MAX, LARGE, SMALL: Find extremes or ranked values.

COUNT, COUNTA, COUNTBLANK: Count numbers, non-blanks, or blanks.

COUNTIF, COUNTIFS: Count values that match one or more criteria.

SUMIF, SUMIFS: Sum values based on multiple criteria.

AVERAGEIF, AVERAGEIFS: Calculate conditional averages.

FREQUENCY: Create distributions using arrays.

RANK, RANK.EQ, RANK.AVG: Rank data within a dataset.

UNIQUE, SORT, FILTER (Excel 365/2021+): Analyze and process data arrays dynamically.

LEFT, RIGHT

VALUE, TEXT

SPLIT

TRIM

UPPER, LOWER, PROPER

REPLACE, SUBSTITUTE

FIND, SEARCH

CONCAT, CONCATENATE, TEXTJOIN

LEN

MID

TODAY()

ISOWEEKNUM

DATEDIF

NETWORKDAYS, WORKDAY

HOUR

TIME

WEEKDAY, WEEKNUM

EDATE, EOMONTH

DATE

NOW()

YEAR, MONTH, DAY

PMT

IRR

NPV

NPER

RATE

PV

FV

Basic Data Validation Types

Input Messages & Error Alerts

Advanced Validation Techniques

Preparing Data for Consolidation

Goal Seek

Scenario Manager

Data Table

Subtotal Functions

Using the Subtotal Feature

Advanced Protection Features

Setting Passwords

Protecting Cells, Ranges, and Objects

Types of Protection

Customizing Output

Managing Page Breaks

Print Titles and Headings

Page Setup Features

Understanding VBA (Visual Basic for Applications)

Managing and Running Macros

Recording Macros

Objective & Subjective Exam