Microsoft

Excel
Content

General introduction

Basic Functions

Database functions

Filter the data

Draw a chart

2
Start Excel
 C1: Double click on the icon on the desktop (Desktop)
 C2: Start Menu/Programs/Microsoft Office/Microsoft Excel
 …
 The Excel window appears

08/02/2025 3
Excel work window
Title bar
Main menu bar
Toolbar
Format bar
Formula bar

Vertical scroll bar

Spreadsh
eet

Horizontal scroll
bar

Status bar

08/02/2025 4
Commands about files and data formats

 Commands to open files

 File write commands
 Format (Format)

08/02/2025 5
Commands to open files
a. Open a new file:
 C1: Click the New icon on the Toolbar
 C2: Press Ctrl+N
 C3: Go to menu File / New… / Workbook

08/02/2025 6
Commands to open files
 b. Open an existing file (*.xls):
 C1: Click on the Open icon
 C2: Press Ctrl+O
 C3: Go to menu File / Open…

1. Select the file

location

2. Select the file to

open 3. Click open to open the
file

08/02/2025 7
File write commands
 a. Record files
 C1: Click on the Save icon
 C2: Press Ctrl+S
 C3: Go to File / Save …

1. Enter the file name 2. Click the save

button

08/02/2025 8
File write commands
 b. Burn file with a different name
 Go to menu File / Save As...
 The old file still exists, the new file has the same content as
the old file

1. Enter the file name 2. Click the save

button

08/02/2025 9
Exit Excel (Exit)
 C1: Press the key combination Alt+F4
 C2: Click the Close button in the top right corner of the
Microsoft Excel work window
 C3: Go to File / Exit menu

08/02/2025 10
Work on spreadsheets
 Add, delete, and change worksheet names (Worksheet)

11
Commonly used data types
 Number type
 String type
 Type of date and time
 Recipe type

Type of date
and time
String
type

Recipe type

Numbe
r type

12
Error description table when input data is
invalid
Symbol Describe
###### This symbol will be displayed when the numeric and date data contained in
the cell is not large enough to display the data.
In this case it is necessary to increase the width of the column.

#VALUE This symbol will be displayed when the type of the parameters in the
function or formula is not valid.
Example: A1= “ABC”, A2=A1*10

#Name? This symbol will be displayed when a function or formula that Excel does
not understand or references a cell address that Excel does not know
For example: B1= Sum(A1A4) ; B2= ABC(A1:A4)

#REF! This symbol will be displayed when there is an invalid reference because in
the civil servant cell there is a reference to a deleted cell.

#DIV/0 Sign This signal will be displayed when there is a division by 0 . calculation
#N/A This symbol will appear when the value is not found in the function or
formula
13
Operations on data areas
 Select rows and columns
 Select region
 Select multiple discrete regions

Press and hold Ctrl

while selecting other
areas

14
Operations on data areas (cont'd)
 Numbering order: Hold down the Ctrl key and drag the mouse to
number in ascending order.

15
Spreadsheet Format
 Format cells (cells)
 Spreadsheet example after formatting

16
Spreadsheet Format
 Format of data display

17
Spreadsheet Format
 Change row and column width
 Add, delete rows/columns for spreadsheets

18
Cell and domain addresses
 Cell addresses and domain addresses are used in formulas
 The cell address includes:
 Relative address: includes column name and row name.
Example: A15, C43
 Absolute address: add a $ sign before the column name and /
or row name if you want to freeze that part. Example: $A3,
B$4, $C$5
 Relative addresses change when copying formulas, absolute
addresses do not

08/02/2025 19
Cell and Domain Addresses (Continued)

 A domain is a group of contiguous cells

 The domain address is declared in the following way:
 High left cell address: Low right cell address
 Example: B2:D5


$C$5:$D$8

08/02/2025 20
Move the cell cursor
 Use your mouse to click on the box
 Type F5 (Ctrl+G), type the address of the cell you want to go
to in the Reference box, and then press the OK button

Type the
address of the
cell you want
to go to

08/02/2025 21
Cell cursor movement keys

 , , , move 1 cell in the direction of the arrow

 Page Up moves the cursor up 1 screen page
 Page Down moves down 1 screen page.
 Home first column (column A) of current row

08/02/2025 22
Cell cursor movement keys (continued)

 Ctrl + to the last column (column IV) of the current row

 Ctrl + to the first column (column A) of the current row
 Ctrl + to the last line (line 65536) of the current column
 Ctrl + to the first row (line 1) of the current column

08/02/2025 23
Cell cursor movement keys (continued)

 Ctrl + + to the top left cell (cell A1)

 Ctrl + + to the top right cell (cell IV1 )
 Ctrl + + to bottom left cell (cell A65536 )
 Ctrl + + to the bottom right cell (cell

08/02/2025 24
Enter data in the box
 Method: click on the cell, type the data in, then type Enter
 Normal input text data
 Numeric data enter a dot (.) instead of a comma (,) to
separate the decimal part
 In order for Excel to understand another type of data as
literal data, enter a ' sign before that data.
 Example: ' 04.8766318

08/02/2025 25
Select domain, column, row,
table
 Select a domain: click on the upper left cell, hold and move
to the lower right cell, release the mouse
 Select the whole row: click in the row name box
 Select the whole column: click on the column name box
 Select the whole worksheet: click on the cell that intersects
the row name and column name
 If you select multiple separate domains, hold down the Ctrl
key while selecting them

08/02/2025 26
Address Reference
 Relative address
• <column address><line address>
 Absolute address
• $<column address>$<row address>
 Mixed address
• $<column address><row address>
• <column address>$<row address>
 How to change address reference
• A1 $A$1 A$1 $A1
F4

27
Area Reference

 Reference by address
• The starting and ending cells are on the same or different lines with
the syntax
<start cell address> : <end cell address>
• All cells on the same line or on multiple lines with the syntax
<start line> : <end line>
• All cells on the same column or on multiple columns with the
syntax
<start column> : <end column>
• Cells on another worksheet in the same workbook with the syntax
<worksheet name>!<address>
• The cells in the worksheet on the workbook are different from the
syntax
[<filename.xls>]<worksheet name>!<address>
28
Area Reference
 An example of an address reference
 Reference by domain name (for absolute addresses only)
• Scan to select the area you want to name
• Enter the area name in the Name Box box on the
toolbar.
• Use the newly created area name in an expression or
function
A1:A10 A2:B5

29
Basic Functions in Excel

 General syntax of functions

 Function_name (list of parameters)

Parameters can be:

 Address of a cell, a range, area name
 An expression
 A constant

30
Logical Functions

 AND (< logical expression 1>, < logical expression 2 >, …, < logical
expression n>): intersection (returns TRUE or FALSE)
 OR (< logical expression 1>, < logical expression 2 >, ...) : union (returns
TRUE or FALSE)
 NOT (<logical expression>) : negation (returns TRUE or FALSE)

Wallet illustrative example : _

A1 = 7
A2 = 8
 AND(A1>5,A2>5) : TRUE
 AND(A1>5,A2>9) : FALSE
 OR(A1>5,A2>9) : TRUE
 OR(A1>9,A2>9) : FALSE
 NOT(A1>5) : FALSE
 NOT(OR(A1>9,A2>9)) : TRUE

31
Calculation functions with numbers

 ABS (<numeric expression>): take the absolute value.

 INT (<numeric expression>): get the integer part
 MOD (<first numeric expression>, <second numeric expression>): Divide
by remainder.
 ROUND (<numeric expression >, < n >) : Rounds to n decimal places.
Illustrative example :
 ABS(-7) : 7
 INT(6.87) : 6
 INT (7.12) : 7
 MOD (5,2) : 1
 MOD (28.5) : 3
 ROUND (157.578, 2) : 157.58
 ROUND (157.578, 1) : 157.6
 ROUND (157,578, 0) : 157
32
String handling function
◦ LEFT (< expression awake string >, < n>) : return about n sign on one's own are from left count
through.
◦ RIGHT (< expression awake string >, <n>) : return about n sign on one's own are from right count
through.
◦ MID (< expression awake string >, < i >, < n>): return about n sign on one's own count are from taste
wisdom rank i .
◦ LEN (< expression awake string >): pay about pm long belong to Chain
◦ LOWER (< expression awake string >): switch string to letter usually .
◦ PROPER (< expression awake string >): Switch the sign on one's own head belong to every are from in
string to letter flower .
◦ UPPER (< expression awake string >): switch string to letter flower .
◦ TRIM (< expression awake string >): Cut cancel the about white at the top and last string .
Illustrative example :
A1 = "CENTRAL TAM TIN HOC"
 LEFT(A1, 5) : MEDIUM
 RIGHT(A1, 5) : N HOC
 MID(A1, 7, 3) : TAM
 LEN(A1) : 17
 LOWER(A1) : information center
 PROPER("information center") : Center for information technology
 UPPER(A1) : CENTRAL TIN HOC
 TRIM(" middle center ") : " middle center "
33
Function for date and time
 DATE (<year>, <month>, <day>)
 DAY/MONTH/YEAR (<date>)
 HOUR/MINUTE/SECOND (<hours minute seconds>)
 NOW (), TODAY ()
Illustrative
 TIME example
Illustrative ()example::
WEEKDAY
DATE
DATE(2003,10,13) returns
(<day month
(2003,10,13) 10/13/2003
year>,
returns <first day of week>)
10/13/2003
• First Monday
 WEEKDAY (DATE(2003,10,13)) returns 2
WEEKDAY of the week (Default
(DATE(2003,10,13)) returns 2value is 1)
 WEEKDAY (DATE(2003,10,13),1) returns 2
– 1: Return
WEEKDAY (DATE(2003,10,13),1)
value from 1: Sundayreturns 2
7 : Saturday
 WEEKDAY (DATE(2003,10,13),2) returns 1
– 2: Return
WEEKDAY (DATE(2003,10,13),2)
value from 1: Monday returns 1
7 : Sunday
 WEEKDAY (DATE(2003,10,13),3) returns 0
WEEKDAY
– 3: Return(DATE(2003,10,13),3)
value from 0: Monday returns 0
6 : Sunday

34
Statistical function

 AVERAGE (<1st parameter >, <2nd parameter>, …): average.

 SUM (<1st parameter>, <2nd parameter>, …): sum.
 COUNT (<1st parameter >, <2nd parameter>, …): Counts numeric values.
 COUNTA (<1st parameter>, <2nd parameter>, …): Counts non-null values.
 MAX (<1st parameter>, <2nd parameter>, …): Find the maximum value.
 RANK (<number>, <list of numbers>, <order>): Rank

• Order: 0 or omitted, in descending order, largest number

= SUM(G5:G11 )
ranked 1
Non-zero 0, ascending order, the smallest number is ranked 1

35
Conditional function

 IF (<logical expression>,<return value when the resultRightCT

of the logical
Happy

expression is TRUE>,<return value when the result of the logical

expression is FALSE>)
 SUMIF (<check area>, <condition>, <sum area>)
 COUNTIF (<check area>, <condition>)

=SUMIF(ChucVu,"NV",RightCT)

36
Data type conversion function

 The function converts a number to a string.

• TEXT (<number>, <format expression>)

 Function to convert numeric string to number

• VALUE (<string of numbers>)

37
Search function
 HLOOKUP (<search value>, <search area>, <value line>, <search
method>)
• First line values can be strings, numbers, or logical values
• Functions are not case sensitive
• If the line takes the value < 1: the function returns the #VALUE!
• If the line takes the value > the line number of the search area:
=VLOOKUP(RIGHT(B4,3),
the function returns the #REF! error. $B$13:$D$17.2,0)
• How to find: Find exact or approximate
 VLOOKUP (<search value>,<search area>,<value column>,<search
method>)
• Similar to HLOOKUP

38
Test function
 ISNA (<value>)
• The value you want to check can be null, error, logical, string, number
or reference value
• Returns TRUE if Value is #N/A. Otherwise returns FALSE .
 ISERROR (<value>)
• Value you want to check, can be null, error, logic, string, number or
reference value
• Returns TRUE if Value is an error with the following values: #N/A,
#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. Otherwise
returns FALSE .
Illustrative example :
 ISNA("ABC") returns FALSE
 ISNA(#REF!) returns FALSE
 ISNA(#N/A) returns TRUE
 ISERROR( " ABC " ) returns FALSE
 ISERROR(#N/A) returns TRUE
39
Database (database)
1. Concept
 The database consists of fields and records.
 A field is a database column, each field represents an
attribute of the object and has a certain data type
 A record is a row of data
 The first line of the database domain contains the field
names, the following lines are the records

08/02/2025 40
Database (database)
2. Sort – Menu Data/Sort
 When ordering a list (database), all columns must be
selected to avoid data inaccuracy
 The field that specifies the ordering is called the key . Up to
3 keys can be specified
 How to : Select a domain. Select Menu Data / Sort…

08/02/2025 41
Database (database)
2. Sort – Menu Data/Sort
Choose the first
key Sort up
[Select second key] ascending

Sort
Descending
[Choose a third key]

No field names
The first line is (sort the first line)
the field name
(not sorted)

Sort from top to

bottom

Arrange from
left to right

08/02/2025 42
Database (database)
3. Search (Filter Data) – Menu Data/Filter
 Purpose: Retrieve records (information) that satisfy certain
conditions
 You can filter in two ways:
 AutoFilter: Excel supports filter conditions
 Advanced Filter…: user-defined filter conditions

08/02/2025 43
Database (database)
 3a. Search (Filter Data) – Menu Data/Filter
 Select the database domain including the field name line
 Data / Filter / AutoFilter menu, the field name cell has the
drop-down arrowhead of the listbox
 Click on it, there is a drop-down list:
 All: to show all records again
 Top 10…: greatest values
 Custom…: self-defined filter conditions
 Column values

08/02/2025 44
Database (database)
 3a. Search (Filter Data) – Menu Data/Filter
 If you select Custom..., the Custom AutoFilter dialog box
will appear for the user to specify the filter conditions:

08/02/2025 45
Database (database)
 3b. Filter data using Advanced Filter
 B1: Determine the conditional domain:
 The first line contains the field name to
specify the condition
 The following lines write conditions:
conditions on the same line are AND,
conditions on different lines are OR

08/02/2025 46
Domain dong/k to filter Domain dong/k to filter
the copy take note yes the copy take note yes
number of products sold number of products sold
go out in January = 400 go out in January > 150 _

Domain dong/k to filter Domain dong/k to filter

the copy take note yes the copy take note yes
number of products sold number of products sold
go out in January 150 < go out in January > 150
08/02/2025 Product number 47
or _ in February 200 _ _
Database (database)
 3b. Filter data using Advanced Filter
 B2: Perform filtering
 Go to menu Data / Filter / Advanced Filter…

Show filter results right in

the data domain
Currently, the results are filtered
elsewhere
Select database domain

Select the conditional domain

Select the current domain
KQ
Show only 1 record
among duplicate results

08/02/2025 48
Functions for use with
databases
 Function_name(Database Domain, “field_name”,
standard_domain) : Calculations on the field in the 2nd
argument of the database domain satisfy the criteria domain
 The second argument can be replaced
with the column number of the column
containing that field in the database
 Function_name includes: DSUM, DMAX, DMIN,
DAVERAGE, DCOUNT

08/02/2025 49
Database functions
 General syntax:
• <Function Name> (<data range>, <column name>, <standard range>)
 Functions
• DAVERAGE Calculates the average of the cells on the column name that
meet the criteria of the range
• DCOUNT Counts numeric cells on column names that meet the standard
range condition
• DCOUNTA Counts non-empty cells on column names that meet the
criteria range
• DMAX/DMIN Returns the maximum/minimum value of cells on the
column name that meet the criteria of the specified range.
• DSUM Sum of cells on column name that meet the criteria of the target
range
standard

50
DSUM

08/02/2025 51
DMAX

08/02/2025 52
DAVERAGE

08/02/2025 53
DCOUNT

08/02/2025 54
Calculation by data group
 B1: Sort the database with the key as the clustering field
 B2: Select the database, including the field name line
 Step 3: Go to menu Data / Subtotal…

Select a clustering
field

Select the function to

calculate
Select the fields to calculate

Should leave 2 options as

default as shown

08/02/2025 55
Sort data
 Select the data you want to sort
 Select on the menu Data / Sort

56
Filter data
 AutoFilter
• Select the area you want to perform data filtering
• Select on the menu Data / Filter / AutoFilter
• Select a value on the list, the data is filtered again by the
selected value
• Data after being filtered

57
Filter the data
 AutoFilter
• If you want to filter by other criteria, choose Custom
from the list

58
Filter the data
 Advanced Filter
• For complex filter criteria, we use Advanced Filter

=AND(H5="TP",G5<2
5)

59
Test data
 Select the cell or area to be checked
 Select Data / Validation…

60
Chart
 Create a chart
 Qualify
 Toolbar Chart
 Format
 Menu chart

08/02/2025 61
Types of charts

 Column type
 Bar form
 Line shape
 Round shape
 Coordinate format
 Domain form
 …

62
Elements in the chart

General title of the

chart Captions for charts

Grid chart

Horizontal axis
title
63
Draw a chart

64
Create a chart
 Steps to create a chart:
 Data: Entered by column when number of rows > number of
columns and vice versa
 Select the Chart Wizard icon, which includes 4 steps to
determine the properties of the graph
 At the end, the histogram has an outline with a black dot.
Can change position, size, addition, chart format

08/02/2025 65
Qualify
a. Step 1 – Styling
Choose type yes available :
+ Column: column vertical
+ Line :
+ Pie: cake round
+ XY: road soy sauce mandarin
+ Area: Area volume
+ Doughtnut : Ice round
+ Radar: Location degree pole
+ Surface: Face volume

Select a form of the

selected style

08/02/2025 66
Characterization
(Continued)
 b. Step 2 – Define Data – Tab Data Range
Domain evil Whether
painting expression thing

Column headings as
legend

Item title

Choose evil Whether

painting expression thing
follow row or follow pillar

08/02/2025 67
Characterization
(Continued)
 b. Step 2 – Data Formatting – Series Tab

General header
cell

Domain containing
X-axis labels

08/02/2025 68
Characterization
(Continued)
 c. Step 3 – Options – Tab Titles

Enter a graph
title

Enter X-axis
title

Enter Y-axis
title

08/02/2025 69
Characterization
(Continued)
 c. Step 3 – Options – Tab Legend
Show/hide
legend
Note

Placement of
legend

08/02/2025 70
Step 4: Determine where to
place the chart
The graph shows up on a new sheet

The graph is displayed on an existing sheet

08/02/2025 71
Change chart axis properties

 Change the scale on the axis

Smallest value

The greatest value

Distance of split points

08/02/2025 72
Change chart axis
properties
 Change data display location
Khối lượng của lợn qua các ngày tuổi

20

16

Khối lượng (kg)

12

0
Sơ sinh 10 20 30 40 50 60
Ngày

Móng Cái Yorkshire

08/02/2025 73