HANOI UNIVERSITY

FACULTY OF MANAGEMENT AND TOURISM

--------oOo-------

Financial Modeling Assignment

EXCEL HANDBOOK
Autumn 2022

Lecturer: Mr. Nguyen Xuan Truong

Tutor: Mr. Tran Son Tung

Group members: Pham Ngoc Van Giang 1904040029

Nguyen Thi Phuong 2004000082

Nguyen Thuy Hien 2004000040

Chu Thi Thuy Dung 1904040021

Dinh Thi Phuong Linh 1904010058

Tutorial class: Group 4 – Tut 6 - FMO

TABLE OF CONTENTS
INTRODUCTION..................................................................................................................................... 3

1. SUMPRODUCT .................................................................................................................................. 4

2. IF………………………………………………………………………………………………………5

3. SUMIF ................................................................................................................................................. 5

4. AVERAGEIF....................................................................................................................................... 7

5. COUNTIF ............................................................................................................................................ 8

6. COUNT ............................................................................................................................................... 8

7. COUNTA .......................................................................................................................................... 10

8. LEFT, RIGHT, MID .......................................................................................................................... 11

8.1. LEFT ................................................................................................................................................ 11

9. VLOOKUP ........................................................................................................................................ 12

10. INDEX ............................................................................................................................................... 13

11. AGGREGATE ................................................................................................................................... 15

12. MATCH ............................................................................................................................................. 16

13. VALUE.............................................................................................................................................. 18

14. DEPRECIATION .............................................................................................................................. 18

15. NPV… ............................................................................................................................................... 19

16. IRR.. .................................................................................................................................................. 21

18. GOAL SEEK – BID PRICE .............................................................................................................. 24

19. ROUND ............................................................................................................................................. 26

20. EDATE .............................................................................................................................................. 27

21. WORKDAY.INL ............................................................................................................................... 28

22. MARK BLOCK - COPY ................................................................................................................... 29

23. MARK BLOCK - TYPE ................................................................................................................... 29

24. MARK BLOCK - DEPENDENCY ................................................................................................... 30

25. MARK BLOCK - FUNCTION ......................................................................................................... 31

27. PIVOT TABLE.................................................................................................................................. 35

29. DATA VALIDATION ...................................................................................................................... 40

30. REMOVE DUPLICATES ................................................................................................................. 42

31. FIND & REPLACE ........................................................................................................................... 44

INTRODUCTION
1. Purpose
Excel is a powerful tool in management and doing business, which is used to organize and
analyze stored data. Excel is applied in budgeting, analysis, forecasting, spotting trends,
reporting, thereby planning to make major business decisions while manager can apply
Excel in order to control in a cost-effective and time-saving way. The handbook will list
out 30 functions and techniques that is the most commonly applicable. The explanation and
practical illustration will be given. The example is surrounding these authors’ department
such as finance, accounting, marketing…

2. Total number of techniques and functions: 31

- Total number of functions: 19
- Total number of techniques: 12
3. Basic functions/techniques
1 Sumproduct 13 Value
2 If 14 Round
3 Sumif 15 Edate
4 Average if 16 Workday.intl
5 Count 17 Data Validation
6 Countif 18 Mark Block Copy
7 Counta 19 Mark Block Type
8 Left, Right, Mid 20 Mark Block Dependency
9 Vlookup 21 Mark Block Function
10 Index 22 Report Workbook – Worksheet
11 Aggregate 23 Find & Replace
12 Match

4. Advanced functions/techniques
24 Depreciation 28 Goal Seek Bid Price
25 NPV 29 Pivot Table
26 IRR 30 Conditional Formatting
27 Goal Seek Break Even Unit 31 Remove Duplicates
1. SUMPRODUCT
a. Syntax: = SUMPRODUCT(array1, [array2], [array3], ...)

b. Usage: SUMPRODUCT function returns the sum of the products of corresponding ranges
or arrays. The default operation is multiplication, but addition, subtraction, and division are
also possible

c. Example:

Objective: Calculate sale invoices Input section:

total amount/calculate total sales

Steps:

 Make a table for data of Item 

names, Cost per unit and Amount for
each

Output section:
 Click on D15 then put the formula 
=SUMPRODUCT(D9:D11;E9:E11)
to get the result

d. Note:

- The array arguments must have the same dimensions. If they do not, SUMPRODUCT
returns the #VALUE! error value.

- For best performance, SUMPRODUCT should not be used with full column references
- SUMPRODUCT treats non-numeric array entries as if they were zeros.

2. IF
a. Syntax: = IF(logical _test, [value_if_true], [value_if_false])

b. Usage: IF function allows you to make logical comparisons between a value and what you
expect

c. Example:

Objective: Categorize customers and Input section:

offer discounts in accordance with their
purchase levels

Steps:

 Make a table for data of Customer
name and Purchase value for each.

Make a reference table for discount

policy

Output section:
 Click on D26 then put the formula  
=IF(C26>$B$19;$C$19;IF(C26>$B$18
;$C$18;IF(C26>$B$17;$C$17;$C$16))
) to get the result

 By changing the conditions, you can

get results for below rows

3. SUMIF
a. Syntax: = SUMIF(range, criteria, [sum_range])
b. Usage: SUMIF function help you to sum the values in a range that meet criteria that you
specify

c. Example:

Objective: Calculate total sale with Input section:

different requirements

Steps:

 Make a table for data of Category

name, Food name and Sales for each

Output section:
 Click on E19 then put the formula 
=SUMIF(C9:C15;C9;E9:E15) to get
the result

 By changing the conditions, you

can get results for below rows

d. Note: The SUMIF function syntax has the following arguments:

- Range (Required): The range of cells that you want evaluated by criteria. Cells in each
range must be numbers or names, arrays, or references that contain numbers. Blank and
text values are ignored

- Criteria (Required): The criteria in the form of a number, expression, a cell reference,
text, or a function that defines which cells will be added. Wildcard characters can be
included - a question mark (?) to match any single character, an asterisk (*) to match any
sequence of characters. If you want to find an actual question mark or asterisk, type a tilde
(~) preceding the character

- Sum_range (Optional): The actual cells to add, if you want to add cells other than those
specified in the range argument. If the sum_range argument is omitted, Excel adds the cells
that are specified in the range argument
4. AVERAGEIF
a. Syntax: = AVERAGEIF(range, criteria, [average_range])

b. Usage: AVERAGEIF function returns the average (arithmetic mean) of all the cells in a
range that meet a given criteria.

c. Example:

Objective: Find the average Input section:

amount of customer spending (loyal

customers are the ones who visit
more than 2 times/week)

Steps:

 Make a table for data of

Customer’s name, gender, visiting
frequency and spending amount Output section:

 Click on C17 then put the  

formula
=AVERAGEIF(C9:C13;"male";
E9:E13) to get the result

 By changing the conditions, you

can get results for below rows

d. Note: The AVERAGEIF function syntax has the following arguments:

- Range (required): One or more cells to average, including numbers or names, arrays, or
references that contain numbers

- Criteria (required): The criteria in the form of a number, expression, cell reference, or text
that defines which cells are averaged

- Average range (optional): The actual set of cells to average. If omitted, range is used

Remarks

- Cells in range that contain TRUE or FALSE are ignored.

- If a cell in average_range is an empty cell, AVERAGEIF ignores it.

- If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
- If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.

- If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.

- You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A
question mark matches any single character; an asterisk matches any sequence of
characters.

- If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

5. COUNTIF
a. Syntax: = COUNTIF(range,criteria)

b. Usage: COUNTIF function is used to count the cells that match specified

c. Example:

Objective: Calculate the number Input section:

of each product

Steps:

 Make a table for data of

Customer’s name, Product, Price

Output section:

 Click on C24 then put the  

formula
=COUNTIF($C10:C$18;B24) to
get the result

 By changing the conditions, you

can get results for below rows

6. COUNT
a. Syntax: = COUNT(value1, [value2], ...)
b. Usage: COUNT function is used to count the number of cells that contain numbers, and
counts numbers within the list of arguments. Use the COUNT function to get the number
of entries in a number field that is in a range or array of numbers.

c. Example:

Objective: To calculate the Input section:

number of employee receiving

salary

Steps:

 Make a table for data of

Employee’s name, Beginning
and ending working day,
Number of working day and
Daily wage

Calculation section:

 Click on C23 then put the  

formula
=IF(MONTH($D9)=2;$E9*$F
9;"") to get salary in February

 By changing number 2 by 3
or 4, you can get salary in
March or April. Press Ctrl +D
for results of below rows

Output section:
 Click on C38 then put the 
formula
=COUNT(C23:C32) to get the
result

 By press Ctrl +R, you can

get results for follow right
columns
7. COUNTA
a. Syntax: = COUNTA(value1, [value2], ...)

b. Usage: COUNTA function is used to count the number of cells that are not empty in a
range

c. Example:

Objective: Count the total number Input section:

of sales order are delivered to

compare to inventory record

Steps:

 Make a table for data of Sale

order No. , Customer’s name and
Status of the order

Output section:


 Click on E17 then put the
formula
=COUNTA(E9:E13) to get the
result

d. Note:

- The COUNTA function counts cells containing any type of information, including error
values and empty text ("").

- For example, if the range contains a formula that returns an empty string, the COUNTA
function counts that value. The COUNTA function does not count empty cells.

- If you do not need to count logical values, text, or error values (in other words, if you want
to count only cells that contain numbers), use the COUNT function.

- If you want to count only cells that meet certain criteria, use the COUNTIF function or the
COUNTIFS function.
8. LEFT, RIGHT, MID

8.1. LEFT

a. Syntax: =LEFT(text,[num_chars])

b. Usage: It is used to extract a specified number of characters from the left side of the text

8.2. RIGHT

a. Syntax: =RIGHT(text,[num_chars])

b. Usage: It is used to extract a specified number of characters from the right side of the text

8.3. MID

a. Syntax: =MID(text,start_num,num_chars)

b. Usage: It is used to extract a specified number of characters from the middle of the text

c. Example:

Objective: Find the product name, Input section:

store Id, product code

Steps:

 Make a table for data of number,

product

Output sections:
 Click on C19 then type -
=LEFT(B19,3) to get the product name
 Click on D19 then type
=RIGHT(B19,4) to get the store Id

 Click on D19 then type

=MID(B19,5,5) to get the product code
 Copy the formula range down
9. VLOOKUP

a. Syntax:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

b. Usage: VLOOKUP stands for Vertical Lookup. It can be used anytime you have a
list of data with the key field in the leftmost column

c. Example
Objective: Fill in comments according to Input section:
the categories

Steps:

 Make a table for data of categories and a

table for data of fruit database

Output section:

 Click on B22 then type = 

=VLOOKUP(B15,$B$14:$C$18,$C$13,0)
to extract the price from the fruit database

 Click on C22 then type

=VLOOKUP(B15,$B$14:$C$18,$C$13,0)
to fill in comments

 Copy the formula range down

d. Note:
- Appropriate match – 1/TRUE
- Exact match – 0/FALSE
10. INDEX

a. Syntax: = INDEX(reference, row_num, [column_num], [area_num])

b. Usage: To return the reference of the cell at the intersection of a particular row and
column. If the reference is made up of non-adjacent selections, you can pick the selection
to look in.

c. Example:
Objective: Search for decision of Input section:
excluding/including the items from
current earnings for forcasting purpose
base on probility of recurring and
materiality

Steps:

 Make a reference table for rate of
probility of recurring, rate of materiality

Make a data validation to create a

drop-down list of rate of probility of
recurring, rate of materiality

Output section:
 Click on D12 then type 
=INDEX($D$8:$F$10,E12,E13) to get
the result

d. Note
- After reference and area_num have selected a particular range, row_num and column_num
select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first
column, and so on. The reference returned by INDEX is the intersection of row_num and
column_num.

- If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire
column or row, respectively
- Row_num, column_num, and area_num must point to a cell within reference; otherwise,
INDEX returns a #REF! error. If row_num and column_num are omitted, INDEX returns
the area in reference specified by area_num.

- The result of the INDEX function is a reference and is interpreted as such by other
formulas. Depending on the formula, the return value of INDEX may be used as a
reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is
equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a
cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the
return value of INDEX into the number in cell B1.
11. AGGREGATE

a. Syntax: =AGGREGATE(function number, option, array)

b. Usage: The AGGREGATE function can apply different aggregate functions to a list or
database with the option to ignore hidden rows and error values.

c. Example:

Objective: Find the total amount Input Section:

of tuition fee (ignore the error).

Steps:

 Make a data table for the
number of student, name, class and
tuition fee

Output Section:
 Click on E23 then type 
=AGGREGATE(9,7,E8:E21)
12. MATCH

a. Syntax: = MATCH(lookup_value, lookup_array, [match_type])

b. Usage: The MATCH function can search for a specified item in a range of cells, and then
returns the relative position of that item in the range.

c. Example:

 Example 1

Objective: Determine Input Section:

product No. match to
searched number of units

Steps:

 Make a data table

for the product number and 
the sales amount
Output Section:

 Click on D14 then

type
=MATCH(D11,D7:D10,1)


 Example 2

Objective: Estimate the favorable Input Section:

product which have the unit sold equal
or more than 30 

Steps:

 Make the data table for the

product code, product name, quantity,
price per unit, discount and total amount
of sales
Output Section:


 Click on D28 then type
=MATCH(D27,D17:D24,0)

d. Notes:
Match_type Behavior

MATCH finds the largest value that is less than

or equal to lookup_value. The values in
1 or omitted the lookup_array argument must be placed in
ascending order, for example: ...-2, -1, 0, 1, 2, ...,
A-Z, FALSE, TRUE.

MATCH finds the first value that is exactly

0 equal to lookup_value. The values in
the lookup_array argument can be in any order.

MATCH finds the smallest value that is greater

than or equal tolookup_value. The values in
-1 the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE,
Z-A, ...2, 1, 0, -1, -2, ..., and so on.
13. VALUE

a. Syntax: =VALUE(text)

b. Usage: The VALUE function make the text enclosed in quotation marks or a reference to a
cell containing the text you want to convert into number.

c. Example:

Objective: find the appropriate discount Input Section:

rate for each type of product

Steps:
 Make the data table for the
product, quantity, unit price.

Make a reference table for discount
policy

Output Section:
 Click on F19 then type 
=VLOOKUP(VALUE(RIGHT(C19,1)
),$B$6:$C$9,2,0) and copy the formula
range down

14. DEPRECIATION
a. Syntax:

= DDB(Initital cost; Salvalue; Useful life; Period;[Factor])

= SLN(Iintital cost; Salvalue; Useful life)

b. Usage: SNL calculate the depreciation per period based on the straight-line method with
(without) the salvage value

DDB calculate the depreciation per period based on the double declining balance method
with (without) the salvage value at the rate of depreciation equal 2 over useful life

c. Example:
Steps: Input section:

 Make a table for data of Initial 

cost, Salvage value, Useful life

Output section:


 Click on C11 then put the formula
=SLN($C$6,$C$7,$C$8) to get the
result

Click on C12 then put the formula

=DDB($C$6,$C$7,$C$8,C$10)

to get the result

d. Note:

- If do not have the salvage value let it 0 or fill not thing.

- Depreciation is the value per period. This is the same for straight line method (SLN) but
the double declining method (DDB).

15. NPV
a. Syntax: = NPV(rate,value1,[value2],...)

b. Usage: NPV function is used for decision making and project evaluation. Estimate net
present value of project in both case equal and unequal cash flow

c. Example:

Objective: Help the firm decide Input section:

whether they should acquire new
machine. The firm would acquire it
if the NPV of projected cash flow
generated from it exceed its cost

Steps:

 Make a table for data summarize 

the given information
 Compute the operating cash Calculation section:
flow for 8 periods


Click on C24 then put the Output section:

formula
=NPV(C13,D21:K21)+C21 

d. Note:

(1) Rate (Required): The discount rate for an entire period.

(2) Value1, value2, ... : Value1 is required, subsequent values are optional. 1 to 254
arguments representing payments and earnings. Value1, value2 etc. must be equally spaced
and occur at the end of each period. The NPV function uses the order of value1, value2 and
so on. to interpret the order of cash flows. Make sure you enter your payment and earnings
values in the correct order.

(3) Arguments that are blank cells, logical values or text representations of numbers,
error values, or text that cannot be converted to numbers are ignored. If the argument is an
array or reference, only the numbers in that array or reference are counted. Empty cells,
logical values, text, or error values in arrays or references are ignored.

(4) The NPV investment begins one period before the date of the value cash flow 1
(period 1) and ends with the last cash flow on the list (period 8). The calculation of NPV is
based on future cash flows. If your first cash flow occurs at the beginning of the first
period (period 0), the first value must be added to the resulting NPV, not the value
arguments.
16. IRR
a. Syntax: = IRR(value; [guess])

b. Usage: IRR function returns the internal rate of return for a series of cash flows expressed
numerically in values.

c. Example:

Objective: Help the firm decide

whether they should acquire
new machine. The firm would
acquire it if the cost of capital of
projected cash flow generated
from it less than the internal
required rate of return.

Steps: Input section:

 Make a table for data 

summarize the given
information

Calculation section:
 Compute the operating cash 
flow for 8 periods

Output section:
 Click on C25 then put the 
formula =IRR(C21:K21)
d. Note:

(1) Values (Required). An array or cell reference that contains the numbers for which you
want to calculate the internal rate of return. The values must contain at least one positive
value and one negative value for the internal rate of return to be calculated. The IRR
function uses the order of values to interpret the order of cash flows. Make sure you enter
your payment and earnings values in the order you want. If an array or reference argument
contains text, logical values, or empty cells, these values are ignored.

(2) Guess (Options). A number that you would guess is close to the outcome of the IRR.

(3) The IRR function is closely related to NPV, the net present value function. The rate of
return calculated using the IRR is the rate that corresponds to a net present value of 0
(zero).

17. GOAL SEEK - BREAK EVEN UNIT

a. Usage: To adjust the value of the component at which the cell the contain that component
will have the target value

b. How to use:

Guideline

1. Apply the formula for the cell that you want to change the value. In here is the NPV
formula

2. Data/ What-if-analysis/ Goal seek

3.1 For set cell: select the cell that contain formula

3.2 For to the value: insert target value for set cell

3.3 For by changing cell: select the cell that need to adjust to meet the target value

Shortcut

c. Example:
Objective: Estimate the financial
and accounting break-even point
for quantity of unit sold.

Steps: Input section:

 Make a table for data summarize 
the given information

Output section:

 Choose the cell as in the 

shortcut. Click on OK to get the
result

d. Note:

(1) The cell in Set must contain the formula linking to the cell in By changing cell

(2) The cell in By changing cell must not contain formula but a numeric value or let blank
18. GOAL SEEK – BID PRICE
a. Usage: To adjust the value of the component at which the cell the contain that component
will have the target value.

b. Application: Estimate the bid price above it the firm add value to the shareholder or NPV
excesses 0.

c. How to use:

Guideline

1. Apply the formula for the cell that you want to change the value. In here is the NPV
formula

2. Data/ What-if-analysis/ Goal seek

3.1 For set cell: select the cell that contain formula

3.2 For to the value: insert target value for set cell

3.3 For by changing cell: select the cell that need to adjust to meet the target value

Shortcut

d. Example:

Objective

The firm decides to import the

components then assembly and
resell instead of manufacturing all
components. The total cost of
importing necessary components for
each finished product, variable cost
per product for assembling and fixed
cost and depreciation of assembly
machine are shown in the table.
Steps: Input section:

 Make a table for data summarize 

the given information.

Calculation section:
 Choose the cell as in the shortcut. 
Click on OK to get the result.

Output section:


19. ROUND
a. Syntax: =ROUND(number, num_digits)

b. Usage: ROUND function help you to rounds a number to a specified number of digits.

c. Example:

Steps: Input section:

 Make a table for data of No., 

Product, Unit Price, Discount, Total

Output section:
 Click on H6, I6, J6, K6 then put
the formula 
=ROUND(G6;2)

=ROUND(G6;0)

=ROUND(G6;-2)

=ROUND(G6;-3)

to get the result.

By dragging, you can get results for

column H, I, J, K.

d. Note: The function syntax has the following arguments:

- Numbers: cell need to be rounded (Total)

- Num_digit: the number of digits to which you want to round the number argument
20. EDATE
a. Syntax: =EDATE(Start_date,months)

b. Usage: EDATE calculate the ending date from a given starting date and the gap between.
The gap is number of the month.

c. Example:

Objective: Estimate the date of Input section:

making salary payable.

Steps:

 Make a table for data of Date of

signing the contract, Due date of
payment (months).

Output section:
 Click on D19 then put the

formula
=EDATE(Start_date,months)

=EDATE(B7,C7)

to get the result

By dragging, you can get results

for column D.
21. WORKDAY.INL
a. Syntax: =WORKDAY.INTL(start_date, days, [weekend], [holidays])

b. Usage: WORKDAY.INL returns the serial number of the date before or after a specified
number of workdays with custom weekend parameters function is used to count the cells
that match specified

c. Example:

Steps: Input section:

 Make 2 tables for data of 

Employee, Starting date, Number
of working day, Holiday

Output section:
 Click on F18 then put the
formula 
=WORKDAY.INTL(D18,E18,1,$
I$6:$I$19) to get the result

By dragging, you can get results for

column F.
22. MARK BLOCK - COPY
a. Usage: Mark block by copied cell

b. How to use:

Guideline

1. Install and open Excel Smart Tool Auditor 20.xlam

2. Auditor/ Copied to show the cell having the same formula and number format in the
same kind of color

Shortcut

c. Example:

Output section:
 Blue: same function
=WORKDAY.INTL()
 Yellow: same function
=IF(MONTH()=2;;)
 Red: same function
=IF(MONTH()=3;;)
 Green: same function
=IF(MONTH()=4;;)
 Violet: same function
=SUM()

23. MARK BLOCK - TYPE

a. Usage: Mark block by cell type

b. How to use:

Guideline

1. Install and open Excel Smart Tool Auditor 20.xlam

2. AuDItor/ Type to show the cell having the same type in the same kind of color
correspoding the note box

Shortcut

c. Example:

Output section:

24. MARK BLOCK - DEPENDENCY

a. Usage: Mark block by cell dependency

b. How to use:

Guideline

1. Install and open Excel Smart Tool Auditor 20.xlam

2. Auditor/ Type to show the cell having the same type in the same kind of color
correspoding the note box
Shortcut

c. Example:

Output section:
 Green: Input cell: contain
data but the function.
 Yellow: Working cell:
contain function but the final
result.
 Orange: Output cell: contain
function and the final result.

25. MARK BLOCK - FUNCTION

a. Usage: Mark block by common function

b. How to use:

Guideline

1. Install and open Excel Smart Tool Auditor 20.xlam

2. AuDItor/ Type to show the cell having the same type in the same kind of color
correspoding the note box

Shortcut
c. Example:

Output section:
 Yellow: same function
=COUNTA()

 Green: same function

=IF(MONTH()=;;)
 Violet: same function
=SUM()

26. REPORT WORKBOOK-WORKSHEET

a. Usage: To get information about Workbook size, Worksheet Size and some potential
problems.

b. How to use: Worksheet Reports provides users information about cells, its formats,
comments and dependency references in a new formatted worksheet.

Guideline

1. Workbook report: Info/Create Report/OK

2. Worksheet report: Worksheet report/Select All

Shortcut
c. Example:

Output section:

 Workbook summary 
will be in a new
sheet looking like
this.

 Worksheet summary

will be in a new
sheet looking like
this.
d. Note

- Worksheet Information provides information about sheet size, the number of references to
sheets on the some workbook and to sheets on other workbooks.

- Potential problems and tips present information of common problems and how to solve it.

- Copied formulas are copied with Copy and Paste commands. If the percentage of cells with
copied formulas is high (> 50%).
27. PIVOT TABLE
a. Usage:

- Pivot table technique summarizes data, analyze, explore, and present summary data
through a table called Pivot table. Unique values in any column of a table are listed, and
allow you to easily see comparisons, patterns, and trends.

- Allowing the user to filter, sort, and drill down data in the reports without writing any
formulas or macros.

- Rearranging the table easily by transposing data (i.e., moving rows to columns or columns
to rows)

b. Example

Objective 1: Report a summary Input section:

of the sales of 2 stores

Steps:

 Make a table for data of

Customer names, Stores,
Products and Purchase Value


 Click on Insert 
PivotTable  Select data range
B1:E135  Click New
Worksheet
 Select Store, Product and 
Purchase value in the
appropriate section

Objective 2:
Output section 1:
Everything is constant, finding
top 5 potential customer to send 
the voucher after 20-hour-
discount program.

Steps:

 Creating the Pivot table with

the same input section

 Select Customer, Store and 

Purchase value in the
appropriate section

 Using conditional formatting


in Home, select the apple rule
that is the all the created pivot
table C9:E119

 In Rule Type, selecting the

“Format only top or bottom
ranked values” for filtering the
top 5 customers.
 In Rule Description, format 
the values and font consistent
with the requirement

Output section 2:
c. Note:

- We need to name each data column really clearly to avoid the misleading when creating
the Pivot table

- We also are required to check and remove redundant or blank data lines to complete the
most optimal efficiency Pivot.

- Put the right data in the right fields in the Pivot Fields to create the most useful Pivot table.

28. CONDITIONAL FORMATING

a. Usage: A tool used to highlight values in cells to help viewers identify data quickly by
changing the color, icon, data bar and color scale of the cell according to a conditional
formatting rule.

b. Example:

Objective 1: Input section:

Highlight the store having the highest

sale of revenue

Steps:

 Making a Pivot Table with the same
input like previous part
 In Home, click to Conditional 
Formatting. Selecting the apple rule that
is the total sale of both store in the
created pivot table C31:D31

 In Rule Type, selecting the “Format 

only top or bottom ranked values” for
finding the top sales.

 In Rule Description, format the values 

and font consistent with the requirement

Output section 1:

Objective 2:

Show the proportion of sold product

Steps:

 From the input above, Conditional 

Formating used one more time, with the
unchange Pivot table

 The needed data range is C18: D20


 Rule type: Format all cells based on 
their values

 Rule Description: Choosing the

format style is “Data Bar”, the Minimum
and Maximum is Automatic and the Bar
Appearance is green 

Output section 2:

c. Note:

- Before determining the design (color, font,…) of the data, it is necessary to determine the
correct format type.

29. DATA VALIDATION

a. Usage: Restricting the type of data or the values that users enter into a cell. If the user tries
to enter a value that is not valid, an Error Alert appears with your customized message.

b. Example:
Objective: Input section:

Showing the exact information of 1 single

customer from given data

Steps: 
 Go to Data/Data Tools/Data Validation


 Settings tab/Allow:

◾ List: to pick data from the drop-down list.

◾ Whole Number/Decimal/Date/Time:
accept only whole number/decimal
number/date/time
◾ Text Length restrict the length of the text.
◾ Custom – for custom formula


 Select the cell(s) you want to create a
rule for, B6:B117

Output section:
 Using Vlookup function to find the
appropriate information of that customer:

=VLOOKUP(lookup value, table array,

col_index_number, range_lookup)

- Lookup value: cells of name customer

- Table array: range of entire the input table

- Col_index_number: the position of the

cell name in this table

- range_lookup: 0 for exact and 1 for

approximate number

30. REMOVE DUPLICATES

a. Usage: Delete the duplicate data

b. Example

Objective: Deleting the duplicate Input section:

data

Steps:

 Copy the column need to be 

removed duplicate into another
zone, still be in this current sheet
and select it.

 Click to Data/ Data tools/

Remove Duplicates 

 That name of chosen column

will be shown and click OK

 Using some functions to find
remain information matching
with each of product (Vlookup,
Sumif)

Output section:

c. Notes

- Users must make a copy of that data before performing the next steps, avoiding doing the
task in this original data.
31. FIND & REPLACE
a. Usage

- FIND: To search for something in your workbook, such as a particular number or text
string.

- REPLACE: To replace something in your workbook, such as a particular number or text

string.

b. Example

Objectives: Find all the particular word and Input section:

replace by another

Steps:

 Calculating the Average, Min and Max of 

Sales for a specified period of time.

 Select the data area. Press Ctrl+F, or go to

Home / Editing / Find & Select /Find

 Click Find All or Find Next to run your

search 
 Click “Options” to further define your search
if needed:

- Within: To search for data in a worksheet or
in an entire workbook, select Sheet or
Workbook.
- Search: You can choose to search either By
Rows (default), or By Columns.
- Look in: To search for data with specific details, in
the box, click Formulas, Values, Notes, or
Comments.
- Match case - Check this if you want to
search for case-sensitive data.

- Match entire cell contents - Check this if

you want to search for cells that contain just
the characters that you typed in the Find
what box.

 Click Format for searching of text or 

numbers make the selections in the Find Format
dialog box.

 Press Ctrl+H, or go to Home > Editing > Find 

& Select > Replace.
 In the Find what box, type the text or
numbers you want to find: Average

In the Replace with box, enter the text or
numbers you want to use to replace the search
text: Max

Click Replace All or Replace

 Click Options to further define your search if


needed:

- Within: To search for data in a worksheet or in

an entire workbook, select Sheet or Workbook.

- Search: You can choose to search either By

Rows (default), or By Columns

- Match case - Check this if you want to search

for case-sensitive data

- Match entire cell contents - Check this if you
want to search for cells that contain just the
characters that you typed in the Find what box.

 If you want to search for text or numbers with

specific formatting, click Format, and then make
your selections in the Find Format dialog box.

Output Section:
c. Notes

- Selecting the value need to be found before opening the Find and Replace dialog box.

- To quickly find all cells with formulas, regardless of formula search results, click Find &
Select > Formulas