EXCEL 2023: An Illustrated Step-by-Step Guide for Beginners

CONTENTS
LESSON 1

INTRODUCTION

LAUNCHING THE EXCEL APPLICATION

THE EXCEL START SCREEN

THE EXCEL INTERFACE

Quick Access Toolbar

Mouse or Touchscreen Mode

Excel Ribbon

Formula Bar

Status bar

Zoom Views

LESSON 2

EXCEL WORKBOOK STRUCTURE

The Workbook

The Worksheet

Grind lines

Cell

CUSTOMIZING THE EXCEL QUICK ACCESS TOOLBAR

SAVING AN EXCEL DOCUMENT

OPENING AN EXISTING DOCUMENT

EXERCISE

LESSON 3

ENTERING AND EDITING DATA

Dealing with Lengthy Sentences

Adjusting Column or Rows Manually

BASICS OF TEXT AND NUMERIC DATA

Cut, Copy, and Paste Data

AutoFit Column and Row

Keyboard Navigation within a Worksheet

AutoComplete Data Entry

AutoFill Sequence Data Entry

How to Create a Custom AutoFill Sequence

EXERCISE

LESSON 4

CELL FORMATTING

Changing Font Type

Change Font Size

Boldfacing and Underling

Changing Font Color

Adding Border

Cell Content Alignment and Orientation

Wrap Text

Shrink to Fit

Merge Cells

AutoFiting

Adding Fill and Text Color

Cell Styles

Number Formatting

Format Painter

LESSON 5

MODIFYING AN EXCEL WORKSHEET

Moving and Copying an Excel Worksheet

Inserting and Deleting Rows and Columns

Deleting Row(s) and Column(s) in a Worksheet

Changing the Width and Height of a Cell

Hiding and Unhiding Excel Rows and Columns

Renaming an Excel Worksheet

Deleting an Excel Worksheet

LESSON 6

INTRODUCTION TO FORMULAS IN EXCEL

What are formulas in Excel, and what are their purposes?

Basic Structure and Syntax of Excel Formulas

Excel Order of Operations

How to Create a Simple Excel Formula

How to Edit an Excel Formula

Copy/Paste an Excel Formula

LESSON 7

EXCEL CELL REFERENCING

Simple Cell Reference

Referring to a Cell Range

Absolute, Relative, and Mixed Cell References in Excel

REFERENCING ANOTHER EXCEL SHEET OR WORKBOOK

Referencing a Cell within a Worksheet

Referencing a Cell in Another Worksheet

Automated Reference to Another Worksheet in the same Workbook

Referencing Another Workbook in Excel

Referencing Another Opened Workbook

Automatically Referencing Another Opened Workbook

Referencing a Closed Workbook

LESSON  8

INTRODUCTION TO EXCEL FUNCTIONS

Definition of Excel Functions

How to use Excel Functions in a Worksheet

Inserting Functions from the Formulas Tab

Choosing a Formula in the Formula Tab

The AutoSum Option

Quick Access to Recently Used Functions

COMMONLY USED MATHEMATICAL AND STATISTICAL FUNCTIONS

SUM Function

AVERAGE Function

AVERAGEIF Function

COUNT Function

MAX or MIN Function

MEDIAN function

MODE function

STANDARD DEVIATION Function

VARIANCE function

CORRELATION function

LOGICAL FUNCTIONS

IF Function

AND Function

OR Function

NOT Function

TRUE Function

FALSE Function

IFERROR Function

TEXT FUNCTIONS

CONCATENATE Function

CONCAT Function

LEFT Function

RIGHT Function

UPPER Function

LOWER Function

MID Function

LEN Function

LOOKUP FUNCTIONS

Forms of Excel Lookup Functions

Vertical Lookup (VLookup) Function

Horizontal Lookup (HLookup) Function

INDEX MATCH Function

INDEX-MATCH-MATCH Function

XLOOKUP  Function  (Excel 365)

DATE AND TIME FUNCTIONS

DATE Function

TIME Function

NOW Function

DAY Function

WEEKDAY Function

EDATE Function

FINANCIAL FUNCTIONS

PMT Function

FV Function

NPV Function

IRR Function

RATE Function

LESSON 9

EXCEL KEYBOARD SHORTCUTS

Basic Shortcuts

Navigation Shortcuts

Workbook Shortcuts

Ribbon Shortcuts

Worksheet Shortcuts

Selection Shortcuts

Cell Shortcuts

Formatting Shortcuts

Rows and Columns Shortcuts

LESSON 10

IMAGES AND SHAPES IN EXCEL

Inserting Images

Inserting Shapes

CUSTOMIZING YOUR SHAPES AND IMAGES

Resizing and Positioning Shapes and Images

Objects Alignment and Distribution

Send to Back Features

Adding Text to Shapes

Adding Border to Shapes

Working with Excel SmartArt

LESSON 11

CHARTS IN EXCEL

Why Data Visualization in Excel?

Fundamental Ideas Regarding Charts in Excel.

CREATING CHARTS IN EXCEL

Selecting Data

Choose Chart Type

Create a Simple Line Chart

CHART ELEMENTS

Chart

Titles

Labels

Legends

Gridlines

CHART STYLES AND FILTER

CUSTOMIZE CHART

How to Change the Chart Type

How to Change the Chart Data

How to Change the Chart Style

How to Change the Chart Style Color

LESSON 12

INTRODUCTION TO PRINTING IN EXCEL

PRINT AREA

How to Set the Print Area

PRINT PREVIEW

Show Margin and Zoom to Page

PAGE SETUP

THE PAGE TAB

Page Orientation

Scaling

Page Size

Print Quality

THE

MARGINS TAB

Page Edges Spacing

Center to Page

HEADERS AND FOOTER TAB

SHEET OPTIONS TAB

Print Area Box

Print Titles

Print

Page order

LESSON 13

DATA VALIDATION

ADDING A DATA VALIDATION

DATA VALIDATION CRITERIA ALLOWED IN EXCEL

Whole number and Decimal Criteria

Date and Time Criteria

Text Length Criteria

Custom Formula Criteria

List Criteria

REMOVING DATA VALIDATION

LESSON 14

EXCEL PIVOT TABLES

UNDERSTANDING EXCEL PIVOT TABLES

CREATING AN EXCEL PIVOTABLE

REFRESHING A PIVOT TABLE

SORTING AN EXCEL PIVOT TABLE

MODIFYING PIVOT TABLE CALCULATIONS

VIEW FIELDS AND AREAS SIDE-BY-SIDE

DISPLAY REPORT FILTER REPORT ON A SEPARATE WORKSHEET

ADD DATA BARS TO A PIVOT TABLE

CREATING PIVOT CHARTS

LESSON 15

PROTECTING EXCEL WORKSHEETS AND WORKBOOKS

PROTECTING THE ENTIRE WORKBOOK

Encrypting The Entire Workbook

How to Remove a Password

Set the Workbook as Read-Only

Protecting the Structure of a Workbook

PROTECTING A WORKSHEET

PROTECTING SPECIFIC CELLS WITHIN A WORKSHEET

LESSON 16

INTRODUCTION TO MACROS IN EXCEL

CREATING A  MACRO IN EXCEL

RECORDING YOUR FIRST MACRO IN EXCEL

RUNNING YOUR FIRST MACRO IN EXCEL

INDEX