Excel Lecture Notes

Course Title: DATA PROFICIENCY WITH MICROSOFT EXCEL

Module 1: Introduction to Excel

Topics: - Navigating the Excel interface - Creating, saving, and editing

workbooks - Basic data entry and formatting - Understanding cells, rows,
columns, and ranges

Module 2: Formatting and Basic Formulas

Topics: - Cell formatting, borders, and styles - Copy-paste, fill handle, and
shortcuts - Basic formulas (SUM, AVERAGE, MIN, MAX) - Relative vs
absolute referencing

Module 3: Logical and Text Functions

Topics: - IF, AND, OR functions - Nested formulas - Text functions:

CONCATENATE, LEFT, RIGHT, LEN, TRIM - Data validation and conditional
formatting

Module 4: Data Analysis Tools

Topics: - Sorting and filtering - Tables and structured referencing - Pivot

Tables and Pivot Charts - VLOOKUP and HLOOKUP

Module 5: Dashboards and Reports

Topics: - Creating visual dashboards - Linking data across sheets - Chart

creation and formatting - Page layout and print settings
MODULE 1: INTRODUCTION TO EXCEL

Topics: - Navigating the Excel interface - Creating, saving, and editing

workbooks - Basic data entry and formatting - Understanding cells, rows,
columns, and ranges.

 Navigating the Excel interface

📘 Components of Microsoft Excel Interface with Symbols

1. Quick Access Toolbar (💾 ↩ ↪) – Save, Undo, Redo shortcuts.

2. File Button (📂) – Open menu for Save, Open, Print.

3. Ribbon (📑) – Main toolbar with commands.

4. Tab List () – Tabs like Home, Insert, Data.

5. Title Bar (🔤) – Shows workbook name.

6. Sign In (👤) – Connect to Microsoft account.

7. Ribbon Options (⚙️) – Show/hide ribbon.

8. Window Controls (➖ ⬜ ❌) – Minimize, Maximize, Close.

9. Ribbon Collapse (🔽) – Hide ribbon to save space.

10. Share Button (🔗) – Share workbook online.

11. Cell Name Box (📌) – Shows active cell address (A1, B5).

12. Formula Bar (🔢) – Type or edit formulas/text.

13. Column Letters (🔠) – Columns labeled A, B, C…

14. Row Numbers (🔢) – Rows labeled 1, 2, 3…

15. Select All Box (☑️) – Selects whole sheet.

16. Active Cell (⬛) – Current working cell.

17. Worksheet Window (📊) – Grid for entering data.

18. Vertical Scroll Bar (↕️) – Move worksheet up/down.

19. Horizontal Scroll Bar (↔️) – Move worksheet left/right.

20. Worksheet Tabs (📑) – Switch between sheets.

21. Add Worksheet (+) (➕) – Insert new sheet.

22. Worksheet Tab Scroll (⏩⏪) – Scroll sheet tabs.

23. Page View Buttons (📄) – Normal, Page Layout, Page Break.

24. Zoom Slider (🔍➖➕) – Zoom in/out.

 25. Status Bar (ℹ️) – Displays status (Ready, Sum, Avg).

Creating, Saving, and Editing Workbooks

- Create: File → New → Blank Workbook
- Save: File → Save As → Choose location & name
- Edit: Click a cell → type new data → press Enter

Basic Data Entry and Formatting

Enter text or numbers into cells. Format cells using Home → Font (bold, italic, font size, cell
color).
Example: Enter student marks and change headings to bold & center.

Understanding Cells, Rows, Columns, and Ranges

- Cell: Small box (e.g., A1 = column A, row 1).
- Row: Horizontal line (1, 2, 3…).
- Column: Vertical line (A, B, C…).
- Range: Group of cells (A1:A10).
Module 2: Formatting and Basic Formulas

Cell Formatting, Borders, and Styles

Apply number formats (currency, percentage, date). Add borders & cell styles (Home → Styles).

Copy-Paste, Fill Handle, and Shortcuts

- Copy- Ctrl+C, Paste- Ctrl+V, Cut- Ctrl+X ,
- Fill Handle: Drag small square at cell corner to copy values/patterns.

Flash fill – Ctrl + E

- Shortcuts: Ctrl+Z (Undo), Ctrl+S (Save),Ctrl+Y (Redo)

Basic Formulas
a) SUM → Adds values. Example: =SUM(A1:A5)
b) AVERAGE → Finds mean. Example: =AVERAGE(B1:B5)
c) MIN → Smallest value. Example: =MIN(C1:C5)
d) MAX → Largest value. Example: =MAX(C1:C5)

Relative vs Absolute Referencing

- Relative: Formula adjusts when copied (e.g., =A1+B1 → copied down becomes =A2+B2).
- Absolute: $ keeps reference fixed (e.g., =$A$1+B1 always keeps A1 fixed).
Module 3: Logical and Text Functions

Logical Functions
a) IF → Tests a condition. Syntax: =IF(condition,value_if_true,value_if_false)
Example: =IF(A1>=50,"Pass","Fail")

b) AND → Returns TRUE if all conditions are TRUE. Syntax: =AND(condition1,condition2,…)

Example: =AND(A1>50,B1>50)

c) OR → Returns TRUE if any condition is TRUE. Syntax: =OR(condition1,condition2,…)

Example: =OR(A1="Yes",B1="Yes")

d) NOT → Reverses the result. Syntax: =NOT(condition)

Example: =NOT(A1>50)

Nested Formulas
Definition: A formula inside another formula for complex results.

Example 1: =IF(A1>=75,"Distinction",IF(A1>=50,"Pass","Fail"))
Example 2: =IF(AND(B1>=40,C1>=40),"Promoted","Repeat")
Example 3: =IF(OR(D1="Yes",E1="Yes"),"Allowed","Not Allowed")

Text Functions
a) CONCATENATE (or CONCAT) → Joins text.
Example: =CONCATENATE(A1," ",B1)

b) LEFT → Extracts characters from left.

Example: =LEFT("Excel",2) → Ex

c) RIGHT → Extracts characters from right.

Example: =RIGHT("Excel",3) → cel

d) LEN → Counts characters.

Example: =LEN("Hello") → 5

e) TRIM → Removes extra spaces.

Example: =TRIM(" Excel Functions ") → Excel Functions

Data Validation & Conditional Formatting

Data Validation → Restrict input (e.g., only numbers 1–100). Steps: Data → Data Validation →
Set rules.
Conditional Formatting → Highlight cells based on conditions. Example: Highlight scores >80 in
green.

Detailed Notes on Functions, Nested Formulas, and Logical Functions

What is a Function?
Definition: A function in Excel is a predefined formula that performs a calculation.
Syntax: =FUNCTION_NAME(argument1, argument2,…)

Examples:
1. =SUM(A1:A5) → Adds numbers
2. =AVERAGE(B1:B10) → Average
3. =MAX(C1:C6) → Largest value

What are Nested Formulas?

Definition: A formula inside another formula.

Examples:
1. Nested IF: =IF(A1>=75,"Distinction",IF(A1>=50,"Pass","Fail"))
2. IF with AND: =IF(AND(B1>=40,C1>=40),"Promoted","Repeat")
3. IF with OR: =IF(OR(D1="Yes",E1="Yes"),"Allowed","Not Allowed")

What is a Logical Function?

Definition: Logical functions test conditions and return TRUE/FALSE or custom output.

Examples:
1. IF → =IF(A1>=50,"Pass","Fail")
2. AND → =AND(A1>=50,B1>=50)
3. OR → =OR(A1="Yes",B1="Yes")
4. NOT → =NOT(A1>50)
Error Code Meaning Example Cause How to Fix

Make sure the

=10/0 or dividing by a blank
#DIV/0! Division by zero denominator is
cell
not 0 or blank. 📘 Excel
Error
Wrong data type Check data
(using text types; use
#VALUE! ="Apple"+10
instead of numbers for
number) calculations.

Invalid cell
Update the
reference
Referring to a cell that was formula to
#REF! (deleted
deleted correct cell
cell/invalid
references.
range)

Unrecognized Check spelling

text (misspelled =SMM(A1:A5) instead of of function or
#NAME?
function or =SUM(A1:A5) text (use quotes
missing quotes) for text).

Ensure lookup
Value not =VLOOKUP(5,A1:B3,2,FALSE)
#N/A value exists or
available when 5 isn’t found
use IFERROR.

Use proper
Wrong range
operators:
operator =SUM(A1:A3 B1:B3) (missing
#NULL! comma (union)
(intersection comma/colon)
or colon
error)
(range).

Check math
Invalid numeric =SQRT(-4) or very large logic, avoid
#NUM!
calculation numbers invalid
numbers.

Formula result is Clear the cells

#SPILL! Array formula results blocked
too big for blocking the
(Excel 365) by other data
available space result.

Adjust the
Calculation error
#CALC! Circular dependency in formula or use
in dynamic
(Excel 365) formula different
arrays
Messages and Their Meaning

✅ Examples in Practice

1. =10/0 → #DIV/0!

2. =SUM("Apple",5) → #VALUE!

3. =VLOOKUP(100,A1:B5,2,FALSE) when 100 is not in list → #N/A

4. =SQRT(-9) → #NUM!

5. =SM(A1:A5) (wrong spelling) → #NAME?

📘 Excel Commands and Their Meaning

🔹 1. Basic Excel Terms

 Cell → Smallest unit in Excel (intersection of row & column).

 Row → Horizontal line of cells (numbered 1,2,3,…).

 Column → Vertical line of cells (labeled A,B,C,…).

 Range → Selection of multiple cells (example: A1:C5).

 Worksheet → A single sheet in a workbook.

 Workbook → The entire Excel file that can have many worksheets.

🔹 2. File Commands

 New → Creates a new workbook.

 Open → Opens an existing Excel file.

 Save / Save As → Stores your workbook; Save As lets you change name/type/location.

 Close → Closes the workbook.

🔹 3. Editing Commands

 Cut (Ctrl+X) → Removes content from one cell and lets you paste it elsewhere.

 Copy (Ctrl+C) → Duplicates content.

 Paste (Ctrl+V) → Inserts copied or cut content.

 Undo (Ctrl+Z) → Cancels the last action.

 Redo (Ctrl+Y) → Repeats the last undone action.

 Find (Ctrl+F) → Locates text/numbers in a worksheet.

 Replace (Ctrl+H) → Finds text/numbers and replaces them with another.

🔹 4. Formatting Commands

 Bold (Ctrl+B) → Makes text darker/thicker.

 Italic (Ctrl+I) → Slants the text.

 Underline (Ctrl+U) → Draws a line under the text.

 Font Size & Color → Changes appearance of text.

 Cell Fill Color → Adds background color.

 Borders → Draws lines around cells.

 Number Format → Changes how numbers look (currency, percentage, date, etc.).

 Alignment → Aligns text left, right, center, top, bottom.

🔹 5. Data Handling Commands

 Sort → Arranges data (A→Z, Z→A, largest to smallest).

 Filter → Displays only rows matching certain conditions.

 Data Validation → Restricts data entry (e.g., only numbers between 1–100).

 Remove Duplicates → Deletes repeated values in a column/range.

🔹 6. Formula & Function Commands

  = (Equal Sign) → Every formula starts with =.

 SUM() → Adds values.

 AVERAGE() → Finds the mean.

 MIN() / MAX() → Smallest / Largest value.

 IF() → Returns one value if condition is true, another if false.

 AND(), OR() → Logical tests combining multiple conditions.

 LEFT(), RIGHT(), LEN(), TRIM(), CONCATENATE() → Text functions.

🔹 7. Insert Commands

 Insert Row/Column → Adds new row or column.

 Insert Chart → Creates graphical representation of data.

 Insert Table → Organizes data with headers and filters.

 Insert Picture/Shape → Adds images or drawings.

🔹 8. Review & View Commands

 Spelling Check (F7) → Corrects spelling errors.

 Comments/Notes → Add remarks to cells.

 Freeze Panes → Keeps selected rows/columns visible while scrolling.

 Zoom → Enlarges or reduces view of worksheet.

Data Validation in Excel

What is Data Validation?

Data validation helps control what users enter in a cell, reducing errors and ensuring
consistency. You can restrict values (numbers, dates, text length) or provide a drop-
down list.

Step-by-Step Instructions:

Step 1: Select Cells for Validation

Highlight the cell(s) you want to apply validation to (e.g., B2:B10).
 Step 2: Open Data Validation Dialog
Go to the Data tab > Data Tools group > Click “Data Validation”.

Step 3: Define Validation Criteria

 In the Settings tab:

o “Allow” lets you select the type: Whole Number, Decimal, List, Date, Time, Text
Length, or Custom.

o Example: To allow numbers between 1 and 100 only, select “Whole Number”
and set “Minimum” to 1 and “Maximum” to 100.

 For a List: Choose “List” and type comma-separated values (e.g., Yes,No,Maybe) or
select a cell range.

Step 4: Set Input Message (Optional)

 In the Input Message tab, enter a Title and Message that pops up when the cell is
selected.

 Example: “Enter a value from 1 to 100”.

Step 5: Set Error Alert

 In the Error Alert tab, choose Style (Stop/Warning/Information), and write a custom
message.

 Example: “Invalid entry. Please enter a number between 1 and 100.”

Step 6: Test Your Validation

Try entering values outside the allowed range—they’ll be rejected.

Worknote Example:

 To create a drop-down for “Status” (Pending, Approved, Rejected) in column C:

1. Select C2:C20.

2. Data > Data Validation > Settings tab > “Allow: List” > “Source:
Pending,Approved,Rejected”.

Conditional Formatting in Excel

What is Conditional Formatting?

 Conditional formatting automatically changes cell styles (color, font, border) based on
rules set by the user. This highlights patterns, outliers, or specific conditions.

Step-by-Step Instructions:

Step 1: Select Range to Format

Highlight range (e.g., D2:D20).

Step 2: Access Conditional Formatting

Go to the Home tab > Styles group > Click “Conditional Formatting”.

Step 3: Choose a Rule Type

 Click “Highlight Cells Rules” or “Top/Bottom Rules” for built-in conditions.

 For custom conditions, choose “New Rule” > “Use Formula to Determine Which Cells to
Format”.

Step 4: Define Condition

 Example 1: Highlight values greater than 80.

o “Highlight Cells Rules” > “Greater Than” > Type 80, choose formatting.

 Example 2: Color rows where Status = “Pending”.

o “New Rule” > “Use a formula…”

o Formula: =$C2="Pending" (applies to active selection, e.g., A2:E20)

o Select formatting color (fill, font).

Step 5: Apply and View Results

Click OK; the formatting updates dynamically as cell values change.

Worknote Example:

 In a marksheet (A2:B20), highlight marks below 40 (fail):

1. Select B2:B20.

2. Conditional Formatting > Highlight Cells > Less Than > Enter 40 > Pick red fill.

Example Summary Table

Task Steps Example

Restrict
Data Tab > Data Validation > Whole Only numbers in
numbers 1 to
Number > 1-100 range accepted
100

Create drop- Data Tab > Data Validation > List > Select from list in
down list Source: Yes,No,Maybe cell

Home Tab > Conditional Formatting

Highlight cells Marks >80 get
> Greater Than > Enter value,
>80 colored
format

Rows with
Color “Pending” Conditional Formatting > New Rule
“Pending” turned
rows > Formula: =$C2="Pending"
yellow
UNIT 4

DATA ANALYSIS TOOLS

Sorting and Filtering

 Sorting allows the arrangement of data alphabetically, numerically, or by date. This

makes data easier to read and analyze for patterns.

o To sort data, select any cell within your range and use Data > Sort on the ribbon.

o You can sort by one or multiple columns and in ascending or descending order.

o Sort Function Syntax:

text

=SORT(array, [sort_index], [sort_order], [by_col])

 array: The data range to sort.

 sort_index (optional): The column to sort by (1 for the first, 2 for the
second...).

 sort_order (optional): 1 for ascending, -1 for descending.

 by_col (optional): Sort by columns instead of rows; TRUE or FALSE.

o Example:
=SORT(B3:D11,2,-1) sorts range B3:D11 by the second column in descending
order.

 Filtering lets users view only the data that matches specific criteria, hiding the rest.

o To filter: select your data range, go to Data > Filter and use drop-down arrows in
column headers.

o Filter Function Syntax:

text

=FILTER(array, include, [if_empty])

 array: The data to filter.

 include: Conditions for including rows.

 if_empty (optional): Value to show if nothing matches.

 o Example:
=FILTER(B3:D11, D3:D11>90) filters to show only rows where column D is
greater than [Link]

 Combine Sort and Filter:

text

=SORT(FILTER(B3:D11, D3:D11>90), 3, -1)

This filters for amounts over 90, then sorts by the third column, descending.

 Tables and Structured Referencing

Structured Referencing in Excel

Structured referencing is a special way to reference data in Excel tables using table and
column names instead of cell addresses. This makes formulas easier to read, more
dynamic, and less prone to errors.

 When creating a table, Excel assigns a name to the table and each column
header.
 In formulas, instead of referencing a cell range (like B2:B10), a structured
reference uses the table and column name (for example,
=SUM(SalesTable[Amount])).
 Structured references are automatically created when a formula is entered in a
table. Selecting table cells for formulas automatically inserts the structured
reference.
 References adjust automatically when rows or columns are added, removed, or
renamed, keeping formulas up to date.
 Structured references can be used both inside and outside the table.
 When a formula is entered in a column within the table, Excel uses a format like
[@[Column Name]], which refers to the value in the same row of the specified
column.
 Common structured reference syntax includes:
o TableName[ColumnName] — references an entire column.
o [@ColumnName] — references the value in the same row.
o [[Column1]:[Column3]] — references multiple columns.
o [#Totals], [#Headers], [#Data] — special items for totals, header, and data
rows.
  Structured references simplify copying formulas, make formulas resilient to data
changes, and improve formula readability.
 Example: To multiply the values in the “Sales” and “Commission” columns for
each row, insert a formula like =[@[Sales]]*[@[Commission]], and Excel will
auto-fill the formula down the column.

Table Formatting in Excel

Excel tables provide built-in formatting options that enhance the appearance and
functionality of data.

 Formatting is automatically applied when a range is converted to a table (using

“Format as Table” or Ctrl+T).
 Table formatting includes banded rows or columns, header rows with filter
buttons, bolded headers, borders, and color themes.
 Options are available to toggle features such as header row, total row, banded
rows, first/last column styling, and filter buttons.
 Users can select from a gallery of table styles, create custom styles, or clear
styles to revert to plain formatting.
 Table formatting improves readability, highlights important information, and
provides quick access to sorting and filtering.
 Converting a data range to a table not only applies formatting but also enables
structured referencing, making data management more efficient.

Difference Between Structured and Standard Referencing

 Structured referencing uses descriptive table and column names, making

formulas easier to track and automatically resilient to table changes.
 Standard referencing uses cell addresses (like A1:B15), which can break or
become confusing when data is changed or ranges shift.

Uses of Structured Referencing

 Simplifies formula creation by replacing cell addresses with meaningful names,
enhancing readability.
 Automates formula updates when data structure changes—formulas adjust
automatically if rows/columns are added, removed, or renamed.
  Improves accuracy in calculations by referencing specific columns or rows,
reducing errors from misaligned cell addresses.
 Enables dynamic reporting; summaries and aggregate functions update instantly
when the underlying table changes.
 Facilitates collaboration, as formulas are easier to understand and maintain by
different users.

Applications of Structured Referencing

 Financial modeling: Using table column names for income, expenses, and
projections for transparent calculations.
 Sales analysis: Quick summing, averaging, and filtering sales data across
months/regions with table formulas.
 Inventory tracking: Referencing product tables and quantities for re-order
systems and analysis.
 Project management: Tracking tasks, dates, and statuses using structured
references for progress and reporting.
 Data validation: Ensuring formulas pull from correct columns, especially in large
datasets with frequent updates.

Uses of Table Formatting

 Instantly improves data visibility by differentiating headers and alternating row
colors.
 Speeds up sorting and filtering with built-in dropdowns in table headers.
 Clearly highlights key metrics by enabling total row, bolding first/last column,
and using custom color schemes.
 Supports professional-looking presentations and dashboards.
 Maintains consistent style across reports, increasing overall comprehensibility.

Applications of Table Formatting

 Business presentations: Neatly formatted tables for client and internal reports.
 Auditing datasets: Distinct banded rows and columns for easier cross-checking
and verification.
  Educational settings: Tables for gradebooks, class lists, and assignments with
visual separation.
 Scientific and statistical research: Organizing experiment results and summary
statistics for publication.
 Everyday data management: Household budgets, schedules, and personal
records with clear structure.

Both features are essential for managing, analyzing, and presenting data efficiently
across all fields where Excel is used.

Chart Creation and Formatting

Charts visualize data for easier interpretation.

 To create a chart:

1. Select your data range (e.g., A1:B10).

2. Go to Insert > Charts and pick a chart type (Column, Bar, Pie, Line, etc.).

3. Adjust titles, colors, or labels from Chart Tools.

 To change chart type: Right-click the chart area > Change Chart Type.

 Formatting tips:

o Use chart elements (title, data labels, gridlines) from the Chart Design tab.

o Format data series and axes by right-clicking them and selecting Format.

Example:
To create a column chart for monthly sales data in A1:B13:

 Select A1:B13.

 Insert > Recommended Charts > Column.

Page Layout and Print Settings

Control how data appears on paper or PDF.

 Access settings from the Page Layout and File > Print menus.

 Key options:
 o Orientation (Portrait/Landscape)

o Margins (Normal, Wide, Narrow)

o Scaling (Fit Sheet on One Page)

o Print Area (Select range > Page Layout > Set Print Area)

o Headers/Footers (Insert > Text > Header & Footer)

o Gridlines and Headings visible/hidden for print (Page Layout > Sheet Options).

Example:
Set print area for A1:D20:

 Select A1:D20.

 Go to Page Layout > Set Print Area.

Summary Table: Key Excel Syntax

Task Example Formula Steps/Options

Type =, click the sheet, select a

Link across sheets ='Sheet2'!A1
cell

Link across Type =, switch to workbook, select

='[[Link]]Jan'!B2
workbooks a cell

Create chart No formula; use Insert > Chart Select data, Insert tab, pick chart

No formula; via Page Layout > Set Print

Set print area Select range, set print area
Area
Shortcut Function

Ctrl + C Copy selected data

Ctrl + X Cut selected data

Ctrl + V Paste data

Ctrl + Z Undo last action

Ctrl + Y Redo last action

Ctrl + S Save workbook

Ctrl + N New workbook

Ctrl + O Open workbook

Ctrl + F Find text/data

Ctrl + H Replace text/data

Ctrl + A Select all or current region

Ctrl + P Print preview

Arrow Keys Move selection

Ctrl + Arrow Key Jump to edge of region

Shift + Arrow Key Select cells stepwise

Ctrl + Space Select column

Shift + Space Select row

Ctrl + Page Up / Down Switch between sheets

F2 Edit active cell

Alt + Enter New line in cell

Ctrl + D Fill down

Ctrl + R Fill right

Shortcut Function

Ctrl + ; Insert date

Ctrl + : Insert time

Ctrl + Shift + $ Format as currency

Ctrl + Shift + % Format as percentage

Ctrl + T Create table

Alt + F1 Create embedded chart

F11 Create chart on new sheet

Ctrl + Shift + L Toggle autofilter

Ctrl + 1 Format cells dialog

Ctrl + Shift + O Select cells with comments

F1 Excel Help

Ctrl + F1 Show/hide ribbon

Alt + F8 Open Macro dialog

Alt + F11 Open VBA editor

F12 Save As

Ctrl + Alt + V Paste Special

Ctrl + ` Toggle formula view

Ctrl + 9 Hide selected row

Ctrl + Shift + 9 Unhide selected row

Ctrl + 0 Hide selected column

Ctrl + Shift + 0 Unhide selected column

Alt + Shift + Right Arrow Group rows/columns

Alt + Shift + Left Arrow Ungroup rows/columns