Welcome to the learning

session…

Advance Excel Functions

03 March 2019
 Learning Objectives

1. Generic functions
2. IF function
3. VLOOKUP and HLOOKUP functions
4. Pivot Tables
5. Chart
6. Editing multiple worksheets simultaneously
7. Conditional formatting
8. Fill Down and Fill Right Command
9. Keyboard Shortcut
10.Mail Merge
WE GROW TOGETHER!!!
GENERIC FUNCTION
Why you need • Prevents you from wasting time counting items
to know this manually or creating dummy variables to count
such items

Functions • Sum, Average, Count, Max, Min, Round, date &

etc…

• COUNT(Range1,Range2,Value1,...) ==> count the

How you use number of cells containing numbers
this feature • COUNTA(Range1,Range2,Value1,...) ==> count the
number of non-empty cells
• COUNTBLANK(Range) ==> count the number of
empty cells in the range
• COUNTIF(Range,”Criteria”) ==> count the number of
cells in the Range containing the Criteria.
• NOTE: The “ ” signs must be used for the Criteria
value
Exercise • Calculate from your own data

WE GROW TOGETHER!!!
IF FUNCTION

Why you need • Conditional comparisons are used in virtually all

to know this spreadsheets
• Knowing how to use IF in a nested manner and
in combination with other functions will save
hours of time

• IF(Comparison,TrueAction,FalseAction)
How you use • IF(Comparison,TrueAction,) ==> Cell shows 0 if
this feature condition is false
• IF(Comparison,TrueAction,””) ==> Cell shows
blank if condition is false

• Create a “Dhaka” variable

– 1 if the store is in Dhaka
Exercise – 0 if the store is in other places

WE GROW TOGETHER!!!
VLOOKUP & HLOOKUP FUNCTIONS

Why you need • Allows you to automatically lookup a particular cell

to know this of data from a larger data range. This is especially
useful when you have
• A large data section that contains information for
multiple records somewhere on the spreadsheet
(e.g., a small database)
• A calculation area somewhere else, and you need
to refer to some specific data elements for specific
records

WE GROW TOGETHER!!!
VLOOKUP & HLOOKUP FUNCTIONS
• VLOOKUP and HLOOKUP allows you to find a specific cell of data in a
larger data range
How you use – Use VLOOKUP when each row contains a separate record and the
this feature associated columns contain data for that one record
– Use HLOOKUP when each column contains a separate record
• VLOOKUP(SearchValue,Range,ColumnNumber,Error) ==> look for a
value in the row specified by SearchValue and the column specified by
ColumnNumber
– SearchValue indicates the “match key” (i.e., find the row that
contains the SearchValue in the first column)
– Range specifies the cells containing the data
– ColumnNumber specifies the column that contains the data element
you want
– Error determines what happens when Excel does not find the exact
SearchValue you want. FALSE leads Excel to display a #N/A when
an exact match cannot be found. TRUE leads Excel to display the
next smaller value than SearchValue
• HLOOKUP(SearchValue,Range,RowNumber,Error) ==> look for a
value in the column specified by SearchValue and the row specified by
RowNumber

NOTE: The 1st column of data must be sorted in ascending order when
using VLOOKUP, and the 1st row of data must be sorted if using
HLOOKUP

WE GROW TOGETHER!!!
VLOOKUP & HLOOKUP FUNCTIONS
• Define a name for the cells containing the data and use
TIPS
that name as the Range. Do not include the
row/column label in the named range because this
would break the ascending sort rule above.
• Insert an extra row above your column label to number
the columns

• Use VLOOKUP to find out how many seats are in the

KBN store? How many passers-by for the store?
Exercise

WE GROW TOGETHER!!!
VLOOKUP AND HLOOKUP FUNCTIONS (CONTINUED)

Number the
columns to Define a name
easily check for cells in
your your data
formulas Range

Need to sort in
ascending order
for VLOOKUP
function to work
properly

WE GROW TOGETHER!!!
PIVOT TABLES
• Most powerful tool to arrange huge amounts of data in a more
How you use structured way than pure sorting. In particular helpful to run
this feature quick sums, averages, distributions, etc. in combination with a
structure criteria, e.g. total number and average sales per
store size band

• Select Data: PivotTable Report…

How you use
this feature

WE GROW TOGETHER!!!
PIVOT TABLES
• Create a data table for exercise.
Exercise

WE GROW TOGETHER!!!
CREATING CHARTS
 A chart, or graph, is a visual representation of a set of
data
 Select the data source with the range of data you want
to chart
 In the Charts group on the Insert tab, click a chart type,
and then click a chart subtype in the Chart gallery.
 In the Location group on the Chart Tools Design tab,
click the Move Chart button to place the chart in a chart
sheet or embed it into a worksheet

WE GROW TOGETHER!!!
CREATING CHARTS

WE GROW TOGETHER!!!
EDITING MULTIPLE WORKSHEETS SIMULTANEOUSLY

Why you need • Avoid having to redo your work on multiple

to know this spreadsheets in a single workbook

• Select the first spreadsheet to be edited

How you use
• Hold the Ctrl key while clicking on the additional
this feature spreadsheets
• Do your editing

• Try It
Exercise

WE GROW TOGETHER!!!
CONDITIONAL FORMATING

Why you need • Sometimes you would to colour the output of cells
to know this in different colours, e.g. negative numbers in red,
positive numbers in black, or add a frame, etc.

• Mark the relevant fields and select Format: Conditional

How you use Formatting
this feature
• Select the criteria for the format and adjust the format.
You can actually change the font, the border and the
color
• Click on Add to select additional criteria for the
formatting

• Format a cell to be in red font, with blue background for

negative numbers and in bold font with thick border, if
Exercise the value is above 10

WE GROW TOGETHER!!!
FILL DOWN AND FILL RIGHT COMMANDS

Why you need • Saves you lots of time

• Allows for copying of cell content to contiguous cells
to know this
with a single keystroke

How you use • Select the cell with the content to be copied and drag
this feature to select the cells to which the content should be
copied
• Ctrl-R to fill right
• Ctrl-D to fill down

Caution!! • Double-check your formulas for absolute vs. relative

references!!!

Exercise • Calculate the total daily sales for each store

WE GROW TOGETHER!!!
 Common Errors
The following are some errors that may appear in a spreadsheet (there are others too).

 #######
Cell is too narrow to display the results of the formula. To fix this simply make the
column wider and the “real” value will be displayed instead of the ###### signs. Note
that even when the ###### signs are being displayed, Excel still uses the “real” value to
calculate formulas that reference this cell.
 #NAME?
You used a cell reference in the formula that is not formed correctly (e.g. =BB+10 instead
of =B3+10)
 #VALUE!
 Usually the result of trying to do math with a textual value. Example: =A1*3 where
A1 contains the word “hello”
 #DIV/0!
 Trying to divide by zero. Example: =3/A1 where A1 contains 0 (zero)

 Circular Reference
 Using a formula that contains a reference to the cell that the formula “lives in”.
Example: putting the formula =A1+1 in cell A1 or putting the formula
=SUM(A1:B2) in any of the cells A1, B1, A2, B2

WE GROW TOGETHER!!!
KEYBOARD SHORTCUT

Formatting keys
Alt + ‘ • Display the style dialog box
Ctrl + Shift + ~ • General Num. Format
Ctrl + Shift + $ • Currency format
Ctrl + Shift + % • Percentage format
Ctrl + Shift + ! • Comma format
Ctrl + Shift + & • Outline border
Ctrl + Shift + _ • Remove borders
Ctrl + b • Bold
Ctrl + i • Italic
Ctrl + u • Underline
Ctrl + 9 • Hide rows
Ctrl + Shift + 9 • Unhide rows
Ctrl + 0 • Hide columns
Ctrl + Shift + 0 • Unhide columns
Ctrl + 1 • Format Dialog Box
Ctrl + 5 • Strike Through
Shift + Space • Select the entire row
Ctrl + Space • Select the entire column

WE GROW TOGETHER!!!
KEYBOARD SHORTCUT…Contd.

Formatting keys
Ctrl + a • Select the entire worksheet
Ctrl + x/c/v • Cut/copy/paste
Ctrl + d/r • Fill cells down/right
CTRL+SHIFT+* • Select the current region around the active cell (the
current region is an area enclosed by blank rows
and blank columns)
SHIFT+ arrow key • Extend the selection by one cell
CTRL+SHIFT+ arrow key • Extend the selection to the last nonblank cell in the
same column or row as the active cell
SHIFT+HOME • Extend the selection to the beginning of the row
CTRL+SHIFT+HOME • Extend the selection to the beginning of the
worksheet
CTRL+SHIFT+END • Extend the selection to the last cell used on the
worksheet (lower-right corner)

WE GROW TOGETHER!!!
KEYBOARD SHORTCUT…Contd.

Formatting keys
Ctrl + F4 • Closes workbook window
Alt + F4 • Closes Excel
Ctrl + F10 • Maximizes the workbook
Ctrl + F9 • Minimizes the workbook
Ctrl + F5 • Restore window size
Shift + F6 • Previous pane
Ctrl + F6 • Next window
Ctrl + Tab • Next window
Shift + F11 • Inserts a new sheet
F11 • Create a Quick Chart Sheet
Ctrl + s • Saves the workbook
F12 • Saves As
Ctrl + o • Opens a workbook
Ctrl + n • Creates a new workbook
Alt + F8 • Macros Dialog Box
Alt + F11 • Visual Basic Editor

WE GROW TOGETHER!!!
 Any Questions??
Why don’t you ask “Google”……!!!
Thank You 
Jahangir Alom
Deputy Manager, Internal Audit
RUNNER GROUP

WE GROW TOGETHER!!!