MODULE 4: EXCEL
UNIT 5: MICROSOFT EXCEL (BASIC) – DATA
Lecturer: M.S. Phạm Thanh Tùng
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 1
CONTENT OF THE LECTURE
general conceptS Fill data automatically
1 GENERAL CONCEPTS 6 FILL DATA AUTOMATICALLY
operation PASTE SPECIAL
2 OPERATION 7 PASTE SPECIAL
DISPLAY FORMAT views
3 DISPLAY FORMAT 8 VIEWS
Conditional formatting PAGE SETUP PRINT
4 CONDITIONAL FORMATTING 9 PAGE SETUP & PRINT
Data Validation
5 DATA VALIDATION
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 2
GENERAL CONCEPTS
Microsoft Excel File
❑ An Excel file is called a workbook.
❑ A workbook contains spreadsheets.
❑ A spreadsheet (sheet or worksheet)
contains data represented as a table
with columns and rows.
❑ Columns are indexed alphabetically
starting with A.
❑ Rows are indexed numerically
starting from 1.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 3
GENERAL CONCEPTS
Worksheet
❑ Use key combination Shift + F11 to create new sheet.
❑ Use key combination Shift + PgDn/PgUp to move to
left/right sheet.
❑ Right-click on Sheet tab to open the context menu.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 4
GENERAL CONCEPTS
Worksheet – Moves
❑ Use Home key to move the cell of the first column.
❑ Use key combination Ctrl + Home to move to the cell of the first column and
the first row, which is cell A1.
❑ Use key combination Ctrl + End to move the cell of the last column with data.
❑ Use key combination Ctrl + Left/Right/Up/Down Arrow Key to move the last
cell in the left/right/up/down direction.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 5
GENERAL CONCEPTS
Row/Column
❑ Use key combination Shift/Ctrl + Space to select entire row/column.
❑ Use key combination Ctrl + 9/0 to hide entire row/column.
❑ Use key combination Ctrl + Shift + (/) to unhide entire row/column.
❑ Select Home > Cells > Format, then select AutoFit Row Height or AutoFit
Column Width.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 6
GENERAL CONCEPTS
Cell
❑ The intersection between a column and a row is called a cell.
❑ Operations on spreadsheets are operations on cells.
❑ Each cell includes three basic attributes:
o Address
o Value
o Format
❑ Use key combination Ctrl + - to delete a cell.
❑ Use key combination Ctrl + Shift + = to insert a cell.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 7
GENERAL CONCEPTS
Cell – Address
❑ The address of a cell is the location or identifier of that cell in the spreadsheet.
❑ Addresses are divided into the following three types:
o Absolute address: $B$3
o Relative address: B3
o Mixed address: B$3 or $B3
❑ Place a dollar sign $ before the line or column in an address prevents that line or
column from being changed when using the autofill function in a formula.
❑ In the formula, use F4 function key to quickly convert a relative address to an absolute
address.
❑ When referring to different sheets, add the sheet name before the address with the
following syntax: 'Sheet Name'!Address
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 8
GENERAL CONCEPTS
Cell – Value
❑ Each cell is formatted with a data type corresponding to the value/content
that the cell contains (or not). These data types are conventionally assigned
by Excel with different properties.
❑ The cell value will be displayed in the Formula Bar.
Name Box Formula Bar
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 9
GENERAL CONCEPTS
Cell – Format
❑ Displays the value/content in an
arbitrary way (can be the same or
different from the original value).
❑ This is the attribute that can be
customized in the Format Cells
dialog box of the cell.
❑ Use key combination Ctrl + 1 to
open the Format Cells dialog box.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 10
GENERAL CONCEPTS
Array
❑ An array is a collection of cells with addresses calculated from the address
of the first cell on the top left to the address of the last cell on the bottom
right is connected by a range operator (colon character :).
For example, A1:B3 or B1:A3
Entire column, B:B
Entire multi-column, B:D
Entire row, 3:3
Entire multi-row, 3:7
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 11
GENERAL CONCEPTS
Define Name – New Name
❑ In addition to using addresses, both cells and arrays can be used with a name.
❑ Select the cell or range to define a name, then right-click and select Define
Name to open the New Name dialog box.
o Name: Specific name without spaces
o Scope: the entire workbook or a specific sheet
o Comment
o Refers to: Absolute address of named array/cell
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 12
GENERAL CONCEPTS
Define Name – Defined Names tab
❑ Select Fomulas > Defined Names…
o Name Manager (Ctrl + F3): Create/Edit/Delete defined names.
o Define Name: Create a new name.
o Use in Fomula: Use defined names.
o Create from Selection (Ctrl + Shift + F3): Create new names from
arrays.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 13
OPERATION
Formula
❑ An expression is made up of characters that follow rules to have a specific
meaning.
❑ An expression can contain one or more operations combined together.
❑ Each operation is a series of operators and operands arranged in a unified
structure, and each operation returns a result (value of data type).
❑ Each expression is called a monomial, many expressions form a
polynomial.
❑ Calculation operations in Excel are performed in the form of a formula
containing a monomial or polynomial, the syntax of the formula will be
started by the equals sign character =.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 14
OPERATION
Data types in Excel
❑ Data types in Excel are divided into 4 main types:
o Text: The default horizontal alignment is left-align
o Number: The default horizontal alignment is right-align
o Logical: The default horizontal alignment is center-align
o Error: The default horizontal alignment is center-align
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 15
OPERATION
Data type: Text
❑ The Text type in Excel is a string of characters belonging to the Unicode
encoding.
❑ Basic characters can be classified into the following three types: letters (A-
Z or a-z), digits (0-9) and special characters.
❑ The default horizontal alignment is left-align.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 16
OPERATION
Data type: Number
❑ The Number types are data types whose value can be converted into a
specific number.
❑ The default horizontal alignment is right-align.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 17
OPERATION
Data type: Number
Data type Meaning Detail
Whole Integer The 64-bit integer, whose value is in the segment: −263 to
number 263 − 1.
Decimal Real number The 64-bit real number, whose value is 0 (zero), −1.79𝐸 +
number 308 to −2.23𝐸 − 308 (negative numbers) and 2.23𝐸 − 308
to 1.79𝐸 + 308 (positive numbers).
Maximum precision is 15 decimal digits.
Fraction Fraction For example: 17/2 or 8 1/2
Percentage Percentage For example: 20%
Scientific Scientific For example: 1.8𝐸 − 3 equivalent to 1.8 × 10−3
notation
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 18
OPERATION
Data type: Number
Data type Meaning Detail
Date Date and Time Valid dates are January 1, 1900 and later.
The date January 1, 1900 has the value 1 in numeric form.
For example: 12/1/2024, 12-1-2024, 13:30:25, 01:30:25 PM
Currency Currency Currencies are real numbers with values from
-922,337,203,685,477.5808 to 922,337,203,685,477.5807,
with a maximum precision of 4 fixed decimal digits.
For example: $200
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 19
OPERATION
Data type: Logical and Error value
❑ The value of the Logical type includes TRUE and FALSE.
❑ The value of the Error type includes: #NULL!, #VALUE!, #DIV/0!, #NUM!,
#NAME?, #N/A, #REF!, …
❑ The default horizontal alignment of Logical and Error value is center-align.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 20
OPERATION
Data type: General type
❑ Excel also uses a data type that represents all data types, which is the
General type.
❑ In addition, if you want values of any data type to be converted to Text
type, use the syntax: place an apostrophe character ‘ before the values, at
this time the apostrophe is not displayed.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 21
OPERATION
Types of operations
❑ Priority between operations in formulas (perform operations from left to right):
o The operations in parentheses are in order from inside to outside
o Function calls
o Arithmetic operations
o String concatenation operation
o Comparison operations
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 22
OPERATION
Types of operations: Arithmetic
❑ The order of precedence of arithmetic operations:
o Percentage: number%
o Exponentiation: base^exponent
o Scientific Exponentiation:
numberEpower, numberE+ exponent, numberE–exponent
o Multiplication/Division: number * number, number / number
o Addition/Subtraction: number + number, number – number
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 23
OPERATION
Types of operations: Arithmetic
❑ In arithmetic operations, the operands are of numeric type, the returned
result is a numeric value.
❑ Logical TRUE has the value 1 and Logical FALSE has the value 0.
❑ Use the double unary operator -- to coerce Logical TRUE has the value 1
and Logical FALSE has the value 0.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 24
OPERATION
Types of operations: String Concatenation
❑ Use the ampersand operator & to connect two sides with any type into a
string with Text type.
❑ In the formula, Text values must be enclosed in double quotes “ ”.
❑ In string concatenation, when entering Logical value with any uppercase or
lowercase letters, the Excel receives and processes are all uppercase
character strings, logical TRUE/FALSE is evaluated as a string
"TRUE"/"FASLE".
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 25
OPERATION
Types of operations: Comparison
❑ Comparison operations include:
o Greater than: Any > Any
o Less than: Any < Any
o Greater than or equal to: Any >= Any
o Less than or equal to: Any <= Any
o Equal to: Any = Any
o Not equal to: Any <> Any
❑ In comparison operations, the operands are of any types, the returned
result is a Logical value (TRUE or FALSE).
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 26
OPERATION
Types of operations: Comparison
❑ Some notes on the comparisons:
o Logical > Letter > Digit > Number
o Logical: FALSE < TRUE
o Letters: “A” < … < “Z”
o Digits: “0” < … < “9”
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 27
GENERAL CONCEPTS
Types of operations: Array Operators
❑ Create arrays in formulas with the following syntax:
o Horizontal arrays with comma separator: {item, item, item, …}
o Vertical arrays with semicolon separator: {item; item; item; …}
❑ Use the Space character to combine two arrays together by intersection.
For example, =A1:B3 B2:C5 => Result: B2:B3
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 28
DISPLAY FORMAT
Format Cells dialog box
❑ Right-click the cell you want to format and
select Format Cells… to open the Format
Celll dialog box.
❑ Formattable cell properties include:
o Number
o Alignment
o Font
o Border
o Fill
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 29
DISPLAY FORMAT
Format Cells dialog box: Format syntax
❑ Data type format, syntax:
[Color]<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
For example, [Blue]#,##0.0;[Red](#,##0.0);[Green]0.00;[Cyan]"Text"@
o Text color only includes 8 colors (case-insensitive)
WHITE RED YELLOW GREEN BLUE CYAN MAGENTA BLACK
o A string of characters enclosed in double quotes.
o @ represents entered letters.
o 0 represents natural numbers.
o # represents natural numbers (may or may not be present).
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 30
CONDITIONAL FORMATTING
Conditional Formatting
❑ Conditional Formatting: Input data will be changed in display format depending
on the setup conditions.
❑ Select Home > Styles > Conditional Formatting > …
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 31
DATA VALIDATION
Data Validation dialog box
❑ Evaluate and filter input data.
❑ Select Data > Data Tools > Data Validation,
open the Data Validation dialog box.
o Settings: Choose the evaluation type
and formula.
o Input Message: Message when entering
data.
o Error Alert: Notify when error or stop.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 32
DATA VALIDATION
Data Validation dialog box: Allow – List
❑ The input data is a list with values that are limited in range.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 33
FILL DATA AUTOMATICALLY
Fill Tool
❑ Ribbon interface of the Fill tool: Select Home >
Editing > Fill
o Fill with Left/Right/Up/Down copy.
o Use key combination Ctrl + D to Fill Down.
o Use key combination Ctrl + R to Fill Right.
o When filling a specific value, the value is
duplicated, but when filling a formula, the
references can change.
o Use key combination Ctrl + E to Flash Fill.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 34
FILL DATA AUTOMATICALLY
Fill Tool: Series dialog box
❑ Select Home > Editing > Fill > Series.
❑ Series in: Fill in Rows or Columns. Type:
o Linear: Arithmetic progression
o Growth: Exponential
o Date (day, month, year): Calculate day/month/year according to an arithmetic
progression. Date unit: Only used for Date fill type, including: Day, Weekday (working
day from Monday to Friday), Month and Year.
o AutoFill: Automatic fill function based on pattern.
❑ Trend option: Automatically calculate trend values when providing the start and end
values of the array.
❑ Step value: Increase/decrease step value (common difference).
❑ Stop value: Stop value (cannot be surpassed or can be equaled).
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 35
FILL DATA AUTOMATICALLY
Fill Handle
❑ AutoFill is quick autofill function to repeat the formula according to the
pattern when using the Fill Handle (the black square in the bottom right
corner when highlighting the pattern).
One-cell pattern Two-cell pattern – same type
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 36
FILL DATA AUTOMATICALLY
Custom Lists
❑ Excel Options > Advanced > General >
Edit Custom Lists to open Custom Lists
dialog box.
❑ Create ordered lists in Excel such as lists
of month names, days of the week, ... .
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 37
PASTE SPECIAL
Paste special dialog box
❑ Copy the object (cell or array), then
right-click the cell to paste and select
Paste Special… to open the Paste
Special dialog box.
❑ Transpose option: Rotate the axis to
convert content on rows/columns to
columns/rows.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 38
VIEWS
Workbook Views
❑ Select View > Workbook Views
o Normal: Standard view
o Page Layout: View print layout with headers/footers, margins, page
numbers, …
o Page Break Preview: View and edit pagination areas
❑ Excel Options > Advanced > Editing options > Use system separators
❑ Excel Options > Advanced > Display options for this worksheet > Uncheck
Show page breaks
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 39
VIEWS
Freeze Panes
❑ Select View > Windows > Freeze Panes
o Freeze an entire row: Select the entire row below the row to freeze.
o Freeze an entire column: Select the entire column to the right of the
column to freeze.
o Freeze both rows and columns: Select the cell with the row below the
row to freeze and the column to the right of the column to freeze.
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 40
PAGE SETUP & PRINT
Page Setup Tools
❑ Select Page Layout > Page Setup
o Margins: Normal, Wide, Narrow
o Orientation: Portrait, Landscape
o Size: Letter, A4, …
o Breaks: Insert Page Break, Remove Page Break
o Print Area: Set Print Area, Clear Print Area
o Background
o Print Titles
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 41
PAGE SETUP & PRINT
Page Setup dialog box – Page tab and Print Preview
❑ Scaling – Adjust to 100%: No Scaling
❑ Scaling – Fit to 1 page wide by 1 tall: Fit Sheet on One Page
❑ Scaling – Fit to 1 page wide by (omitted) tall: Fit All Columns on One Page
❑ Scaling – Fit to (omitted) page wide by 1 tall: Fit All Rows on One Page
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 42
PAGE SETUP & PRINT
Page Setup dialog box – Margins tab
❑ Margins:
o Top
o Bottom
o Left
o Right
o Header
o Footer
❑ Center on page
o Horizontally
o Vertically
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 43
PAGE SETUP & PRINT
Page Setup dialog box – Header/Footer tab
❑ Custom Header/Footer: Left/Center/Right sections
o Page Number, Number of Pages
o Current Date, Current Time
o File Path, File Name, Sheet Name
❑ Different odd and even pages
❑ Different first page
❑ Scale with document
❑ Align with page margins
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 44
PAGE SETUP & PRINT
Page Setup dialog box – Sheet tab
❑ Print area
❑ Print titles:
o Rows to repeat at top
o Colums to repeat at left
❑ Print: Gridlines, Black and White, Draft
quality, Row and column headings,
Comments and notes, Cell errors
❑ Page order:
o Down, then over
o Over, then down
11/07/2025 LECTURES OF APPLIED INFORMATICS Page 45
