MS Excel Overall Power Point Presentatio
dheerajkumar02527289
100 views
178 slides
Aug 31, 2024
Slide 1 of 183
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
About This Presentation
Excel Course
Slide Content
MS Excel
Day 1 -Introduction
Day 1 -Introduction
Versions of Excel…
Limits of Excel…
For more of Excel Specifications & limits:
https://support.microsoft.com/en-gb/office/excel-specifications-and-limits-
1672b34d-7043-467e-8e27-269d656771c3
For more of Excel Specifications & limits:
https://support.microsoft.com/en-gb/office/excel-specifications-and-limits-
1672b34d-7043-467e-8e27-269d656771c3
Interface of Excel…
Excel2010interface:Filetabwith
backstageview.CustomizeRibbonand
more
Excel2007interface:ColourfulOffice
buttonwithdropdownbox.Tabsand
commandsonRibbonintroduced.
Excel2010interface:Filetabwith
backstageview.CustomizeRibbonand
more
Excel2007interface:ColourfulOffice
buttonwithdropdownbox.Tabsand
commandsonRibbonintroduced.
Interface of Excel…
Excel2013interface:Filetabwith
numeroustemplatesinbackstageviewand
moredataanalysisoptions.Flashfill,quick
analysisandmore
Excel2013interface:Filetabwith
numeroustemplatesinbackstageviewand
moredataanalysisoptions.Flashfill,quick
analysisandmore
Interface of Excel…
Customizing the Interface…
Paste & Paste Special…
Formatting in Excel…
ETLstep1iscorrectFormattingto
respectiveVariablesinthedataset.
NumberFormatcommandsarepresentin
‘Home’tab.
ETLstep1iscorrectFormattingto
respectiveVariablesinthedataset.
NumberFormatcommandsarepresentin
‘Home’tab.
Data Fill…
Put the mouse pointer over the bottom right-hand corner of the cell
until it's a black plus sign.
Click and hold the left mouse button, and drag the plus sign over the
cells you want to fill.
And the series is filled in for you automatically using the AutoFill
feature.
The Fill Series tool can readily handle any list of days, months, or
years and take them all in stride.
Filling can be used for:
Copying
Sequences
Dates
Put the mouse pointer over the bottom right-hand corner of the cell
until it's a black plus sign.
Click and hold the left mouse button, and drag the plus sign over the
cells you want to fill.
And the series is filled in for you automatically using the AutoFill
feature.
The Fill Series tool can readily handle any list of days, months, or
years and take them all in stride.
Filling can be used for:
Copying
Sequences
Dates
Fill Series Dialogue box…
The Series dialog box contains loads of options but most of them are automatically selected
because the value in cell A1 is a date. Let’s take a look:
I want to fill my dates down column A, so I need ‘Columns’ selected.
Excel has detected that I’m using Dates because I have a date in cell A1.
It has also assumed I want to increase my series by days, as opposed to weekdays, months or
years.
And the step value is one day at a time.
All I need to do is enter the Stop value, which is the last date in my series.
The Series dialog box contains loads of options but most of them are automatically selected
because the value in cell A1 is a date. Let’s take a look:
I want to fill my dates down column A, so I need ‘Columns’ selected.
Excel has detected that I’m using Dates because I have a date in cell A1.
It has also assumed I want to increase my series by days, as opposed to weekdays, months or
years.
And the step value is one day at a time.
All I need to do is enter the Stop value, which is the last date in my series.
Flash Fill…
Excel Flash Fillis a special tool that analyzes the information you are
entering and automatically fills data when it identifies a pattern.
Excel Flash Fillis a special tool that analyzes the information you are
entering and automatically fills data when it identifies a pattern.
Justify Fill…
An interesting tool within Excel is the Fill Justify.It allows you to select text
from several rows and merge them in to one cell.
An interesting tool within Excel is the Fill Justify.It allows you to select text
from several rows and merge them in to one cell.
Keyboard Shortcuts…
Keyboard Shortcuts…
Keyboard Shortcuts…
For more Shortcuts:
https://support.microsoft.com/en-gb/office/keyboard-shortcuts-in-excel-
1798d9d5-842a-42b8-9c99-9b7213f0040f
For more Shortcuts:
https://support.microsoft.com/en-gb/office/keyboard-shortcuts-in-excel-
1798d9d5-842a-42b8-9c99-9b7213f0040f
MS Excel
Day 2 –Referencing, Named Ranges &
Functions
Day 2 –Referencing, Named Ranges &
Functions
Cell Referencing…
A cell referencerefers to a cell or a range of cells on a worksheet and can be
used in a formula so that Microsoft Office Excel can find the values or data
that you want that formula to calculate.
One Time $A$3 Absolute
Second TimeA$3 Mixed: Row fixed
Third Time$A3 Mixed: Column fixed
Fourth TimeA3 Relative
A cell referencerefers to a cell or a range of cells on a worksheet and can be
used in a formula so that Microsoft Office Excel can find the values or data
that you want that formula to calculate.
One Time $A$3 Absolute
Second TimeA$3 Mixed: Row fixed
Third Time$A3 Mixed: Column fixed
Fourth TimeA3 Relative
Arelativecellreferencereferstocellsinrelationtothecellthatcontainsthe
formula.Whentheformulaismoved,itreferencesnewcellsbasedontheir
locationrelativetotheformula.Arelativecellreferenceisthedefaulttypeof
Excelcellreference.
Arelativecellreferencedoesnotuseanydollarsign($)beforethecolumn
addressortherowaddress.Whenaformulaiscopiedordraggedtoanothercell
orrange,relativecellreferencechangesaccordingly.
Anabsolutecellreferencealwaysreferstoaspecificcellorrangeofcells,
regardlessofwheretheformulaislocatedintheworksheet,evenwhenthe
formulaiscopiedtoanyothercellintheworksheet.Therowandcolumn
referencesdonotchangeaftercopyingtheformulabecausethereferenceistoan
actualcelladdress.
Anabsolutereferenceusestwodollarsigns($)initsaddress:oneforthecolumn
letterandonefortherownumber.
AmixedcellreferenceisanExcelcellreferencethatusesanabsolutecolumnor
rowreference,butnotboth.Onlyoneoftheaddresspartsisabsolute.
Amixedcellreferenceusesadollarsign($)eitherbeforethecolumnaddressor
beforetherowaddress.Itisacombinationofrelativeandabsolutereferences
(mixedreferencetypeinexcel).
Cell Referencing…
formula.Whentheformulaismoved,itreferencesnewcellsbasedontheir
locationrelativetotheformula.Arelativecellreferenceisthedefaulttypeof
Excelcellreference.
Arelativecellreferencedoesnotuseanydollarsign($)beforethecolumn
addressortherowaddress.Whenaformulaiscopiedordraggedtoanothercell
orrange,relativecellreferencechangesaccordingly.
Anabsolutecellreferencealwaysreferstoaspecificcellorrangeofcells,
regardlessofwheretheformulaislocatedintheworksheet,evenwhenthe
formulaiscopiedtoanyothercellintheworksheet.Therowandcolumn
referencesdonotchangeaftercopyingtheformulabecausethereferenceistoan
actualcelladdress.
Anabsolutereferenceusestwodollarsigns($)initsaddress:oneforthecolumn
letterandonefortherownumber.
AmixedcellreferenceisanExcelcellreferencethatusesanabsolutecolumnor
rowreference,butnotboth.Onlyoneoftheaddresspartsisabsolute.
Amixedcellreferenceusesadollarsign($)eitherbeforethecolumnaddressor
beforetherowaddress.Itisacombinationofrelativeandabsolutereferences
(mixedreferencetypeinexcel).
Cell Referencing…
Cell Referencing…
Name Range…
Whenwewriteformulas/functions,weneedtoselectarangeofcells.However,doing
thiscanbetimeconsuminganderrorprone.
Excelallowsustouseacellorarangeofcellsbyitsname.E.g.Sum(Basic)sounds
mucheasiercomparedtosum(H2:H101).
Howevertodothis,firstwewouldneedtonametherangeH2:H10asBasic.
Youcannameasinglecell,groupofcells,Cellranges(Row/s,Column/s).ATable(data
matrix)canalsobenamed.
Top 5 benefits of using names in Excel
Excel names make formulas easier to make and read.
Excel names allow creating expandable formulas.
Excel names make formulas easier to re-use.
Named ranges simplify navigation.
Named ranges allow creating dynamic drop-down lists.
Whenwewriteformulas/functions,weneedtoselectarangeofcells.However,doing
thiscanbetimeconsuminganderrorprone.
Excelallowsustouseacellorarangeofcellsbyitsname.E.g.Sum(Basic)sounds
mucheasiercomparedtosum(H2:H101).
Howevertodothis,firstwewouldneedtonametherangeH2:H10asBasic.
Youcannameasinglecell,groupofcells,Cellranges(Row/s,Column/s).ATable(data
matrix)canalsobenamed.
Top 5 benefits of using names in Excel
Excel names make formulas easier to make and read.
Excel names allow creating expandable formulas.
Excel names make formulas easier to re-use.
Named ranges simplify navigation.
Named ranges allow creating dynamic drop-down lists.
Name Range…
Rules for creating Name Range…
WhencreatinganameinExcel,thereareafewrulestoremember:
AnExcelnameshouldbeunder255characterslong.
Excelnamescannotcontainspacesandmostpunctuationcharacters.
Anamemustbeginwithaletter,underscore(_),orbackslash(\).Ifaname
beginswithanythingelse,Excelwillthrowanerror.
Excelnamesarecase-insensitive.Forexample,"Apples","apples"and
"APPLES"willbetreatedasthesamename.
Youcannotnamerangeslikecellreferences.Thatis,youcan'tgivethename
"A1"or"AA1"toarange.
Youcanuseasinglelettertonamearangelike"a","b","D",etc.exceptfor
theletters"r""R","c",and"C"(thesecharactersareusedasshortcutsfor
selectingaroworcolumnforthecurrentlyselectedcellwhenyoutypethem
intheNameBox).
WhencreatinganameinExcel,thereareafewrulestoremember:
AnExcelnameshouldbeunder255characterslong.
Excelnamescannotcontainspacesandmostpunctuationcharacters.
Anamemustbeginwithaletter,underscore(_),orbackslash(\).Ifaname
beginswithanythingelse,Excelwillthrowanerror.
Excelnamesarecase-insensitive.Forexample,"Apples","apples"and
"APPLES"willbetreatedasthesamename.
Youcannotnamerangeslikecellreferences.Thatis,youcan'tgivethename
"A1"or"AA1"toarange.
Youcanuseasinglelettertonamearangelike"a","b","D",etc.exceptfor
theletters"r""R","c",and"C"(thesecharactersareusedasshortcutsfor
selectingaroworcolumnforthecurrentlyselectedcellwhenyoutypethem
intheNameBox).
Functions…
AfunctioninExcelisapresetformula,thathelpsperformmathematical,
statisticalandlogicaloperations.
Onceyouarefamiliarwiththefunctionyouwanttouse,allyouhavetodois
enteranequalsign(=)inthecell,followedbythenameofthefunctionand
thecellrangeitappliesto.
Differenttypesoffunctionsare:
Text / string functions
Logical functions
Math functions
Statistical functions
Lookup and reference functions
Financial functions
Date functions
Time functions
AfunctioninExcelisapresetformula,thathelpsperformmathematical,
statisticalandlogicaloperations.
Onceyouarefamiliarwiththefunctionyouwanttouse,allyouhavetodois
enteranequalsign(=)inthecell,followedbythenameofthefunctionand
thecellrangeitappliesto.
Differenttypesoffunctionsare:
Text / string functions
Logical functions
Math functions
Statistical functions
Lookup and reference functions
Financial functions
Date functions
Time functions
Function Description
=AND Returns TRUE or FALSE based on two or more conditions
=AVERAGE Calculates the average (arithmetic mean)
=AVERAGEIF Calculates the average of a range based on a TRUE or FALSE condition
=AVERAGEIFS Calculates the average of a range based on one or more TRUE/FALSE conditions
=CONCAT Links together the content of multiple cells
=COUNT Counts cells with numbers in a range
=COUNTA Counts all cells in a range that has values, both numbers and letters
=COUNTBLANK Counts blank cells in a range
=COUNTIF Counts cells as specified
=COUNTIFS Counts cells in a range based on one or more TRUE or FALSE condition
=IF Returns values based on a TRUE or FALSE condition
=IFS Returns values based on one or more TRUE or FALSE conditions
=LEFT Returns values from the left side of a cell
=LOWER Reformats content to lowercase
=MAX Returns the highest value in a range
=MEDIAN Returns the middle value in the data
=MIN Returns the lowest value in a range
=MODE Finds the number seen most times. The function always returns a single number
=NPV The NPV function is used to calculate the Net Present Value (NPV)
=OR Returns TRUE or FALSE based on two or more conditions
=RAND Generates a random number
=RIGHT Returns values from the right side of a cell
=STDEV.P Calculates the Standard Deviation (Std) for the entire population
=STDEV.S Calculates the Standard Deviation (Std) for a sample
=SUM Adds together numbers in a range
=SUMIF Calculates the sum of values in a range based on a TRUE or FALSE condition
=SUMIFS Calculates the sum of a range based on one or more TRUE or FALSE condition
=TRIM Removes irregular spacing, leaving one space between each value
=VLOOKUP Allows vertical searches for values in a table
=XOR Returns TRUE or FALSE based on two or more conditions
=AND Returns TRUE or FALSE based on two or more conditions
=AVERAGE Calculates the average (arithmetic mean)
=AVERAGEIF Calculates the average of a range based on a TRUE or FALSE condition
=AVERAGEIFS Calculates the average of a range based on one or more TRUE/FALSE conditions
=CONCAT Links together the content of multiple cells
=COUNT Counts cells with numbers in a range
=COUNTA Counts all cells in a range that has values, both numbers and letters
=COUNTBLANK Counts blank cells in a range
=COUNTIF Counts cells as specified
=COUNTIFS Counts cells in a range based on one or more TRUE or FALSE condition
=IF Returns values based on a TRUE or FALSE condition
=IFS Returns values based on one or more TRUE or FALSE conditions
=LEFT Returns values from the left side of a cell
=LOWER Reformats content to lowercase
=MAX Returns the highest value in a range
=MEDIAN Returns the middle value in the data
=MIN Returns the lowest value in a range
=MODE Finds the number seen most times. The function always returns a single number
=NPV The NPV function is used to calculate the Net Present Value (NPV)
=OR Returns TRUE or FALSE based on two or more conditions
=RAND Generates a random number
=RIGHT Returns values from the right side of a cell
=STDEV.P Calculates the Standard Deviation (Std) for the entire population
=STDEV.S Calculates the Standard Deviation (Std) for a sample
=SUM Adds together numbers in a range
=SUMIF Calculates the sum of values in a range based on a TRUE or FALSE condition
=SUMIFS Calculates the sum of a range based on one or more TRUE or FALSE condition
=TRIM Removes irregular spacing, leaving one space between each value
=VLOOKUP Allows vertical searches for values in a table
=XOR Returns TRUE or FALSE based on two or more conditions
Inthesamemanner,otherrequiredfunctionscanbe
usedforvariouscalculations.
Youcanusethefunctionsasperyourobjective.
FunctionArgumentbox,withoutandwith
formulainthecell
CTRL+AORSHIFT+f3
KeyboardshortcuttoopenFunction
Argumentbox,withformulainthecell
Creating a Function…
usedforvariouscalculations.
Youcanusethefunctionsasperyourobjective.
FunctionArgumentbox,withoutandwith
formulainthecell
CTRL+AORSHIFT+f3
KeyboardshortcuttoopenFunction
Argumentbox,withformulainthecell
Creating a Function…
Count Function…
SYNTAX OF FUNCTIONS
COUNT=COUNT(VALUE1,VALUE2,…)
COUNTA=COUNTA(VALUE1,VALUE2,…)
COUNTBLANK =COUNTBLANK(RANGE)
COUNTIF=COUNTIF(RANGE,CRITERIA)
COUNT ONLY
CELLS THAT
CONTAINS
NUMBER.
COUNTA
CELLS THAT
ARE NOT
EMPTY.
COUNTBLANK
CELLS THAT
ARE EMPTY.
COUNTIF
CELLS WITH
SPECIFIC
CONDITION
SYNTAX OF FUNCTIONS
COUNT=COUNT(VALUE1,VALUE2,…)
COUNTA=COUNTA(VALUE1,VALUE2,…)
COUNTBLANK =COUNTBLANK(RANGE)
COUNTIF=COUNTIF(RANGE,CRITERIA)
COUNT ONLY
CELLS THAT
CONTAINS
NUMBER.
COUNTA
CELLS THAT
ARE NOT
EMPTY.
COUNTBLANK
CELLS THAT
ARE EMPTY.
COUNTIF
CELLS WITH
SPECIFIC
CONDITION
SumifFunction…
SYNTAXOF SUMIF
=SUMIF(RANGE,CRITERIA,SUM_RANGE)
RANGE-
Range of cells on which conditions are applied.
CRITERIA-
Condition that defines which cell or cells will be added.
SUM RANGE-
Actual cells to sum.
NOTE:-
If sum range is not used then range is used for sum.
SYNTAXOF SUMIF
=SUMIF(RANGE,CRITERIA,SUM_RANGE)
RANGE-
Range of cells on which conditions are applied.
CRITERIA-
Condition that defines which cell or cells will be added.
SUM RANGE-
Actual cells to sum.
NOTE:-
If sum range is not used then range is used for sum.
The absolute value of a number
ABS
The average or arithmetic mean for a group of numbers
AVERAGE
The number of cell locations in a range that contain a numeric character
COUNT
The number of cell locations in a range that contain a text or numeric character
COUNTA
The highest numeric value in a group of numbers
MAX
The lowest numeric value in a group of numbers
MIN
Returns the serial number of the current date and time
NOW
Returns the serial number of today's date
TODAY
Returns the serial number of a particular date
DATE
Returns the year fraction representing the number of whole days between
start_date and end_date
YEARFRAC
Use this function to return the number of days between two dates.
DAYS
Returns the number of whole workdays between two dates using parameters to
indicate which and how many days are weekend days
NETWORKDAYS.INTL
Statistical & Date Functions…
ABS
The average or arithmetic mean for a group of numbers
AVERAGE
The number of cell locations in a range that contain a numeric character
COUNT
The number of cell locations in a range that contain a text or numeric character
COUNTA
The highest numeric value in a group of numbers
MAX
The lowest numeric value in a group of numbers
MIN
Returns the serial number of the current date and time
NOW
Returns the serial number of today's date
TODAY
Returns the serial number of a particular date
DATE
Returns the year fraction representing the number of whole days between
start_date and end_date
YEARFRAC
Use this function to return the number of days between two dates.
DAYS
Returns the number of whole workdays between two dates using parameters to
indicate which and how many days are weekend days
NETWORKDAYS.INTL
Statistical & Date Functions…
Arithmetic & Logical Functions…
ROUNDS a number to a specified number of digits
ROUND
Returns an aggregate list of database , ignores errors and blanks
AGGREGATE
Check whether condition is met, TRUE if met, FALSE, if not
IF
To check more than one conditions, all should met then TRUE
otherwise FALSE
AND
To check more than one conditions, any one should met then TRUE
otherwise FALSE
OR
Returns addition of the range as per conditions specified
SUMIFS
Returns count of the range as per conditions specified
COUNTIFS
ROUNDS a number to a specified number of digits
ROUND
Returns an aggregate list of database , ignores errors and blanks
AGGREGATE
Check whether condition is met, TRUE if met, FALSE, if not
IF
To check more than one conditions, all should met then TRUE
otherwise FALSE
AND
To check more than one conditions, any one should met then TRUE
otherwise FALSE
OR
Returns addition of the range as per conditions specified
SUMIFS
Returns count of the range as per conditions specified
COUNTIFS
MS Excel
Day 3 –Logical Functions, Math & Trig Functions
Day 3 –Logical Functions, Math & Trig Functions
Logical Functions
TheLogicalbuttonfunctionsarebasedonthelogic.Also,thelogicaloperatorsareused.
Theseare,theLessthan(<),Greaterthan(>),EqualTo(=)andtheNotEqualTo(<>)etc.
Additionally,aretheLessThanEqualTo(<=)andtheGreaterThanEqualTo(>=).
Notably,theEqualToLessThan(=<)ortheEqualToGreaterThan(=>)isaninvalidoperator.
Hence,useofthe“EqualTooperator”beforethe“LessorGreaterThan”,shouldn’t
bedone.
TheLogicalbuttonfunctionsarebasedonthelogic.Also,thelogicaloperatorsareused.
Theseare,theLessthan(<),Greaterthan(>),EqualTo(=)andtheNotEqualTo(<>)etc.
Additionally,aretheLessThanEqualTo(<=)andtheGreaterThanEqualTo(>=).
Notably,theEqualToLessThan(=<)ortheEqualToGreaterThan(=>)isaninvalidoperator.
Hence,useofthe“EqualTooperator”beforethe“LessorGreaterThan”,shouldn’t
bedone.
ANDfunctionchecksforargumentswhetherTRUEornot.
FALSEfunctionreturnswithfalseforthelogicalvalues.
IFcheckswhetheraconditionisTRUEorFALSEandreturnstheansweraccordingly.
IFERRORhelpsuserstohidetheresulterrors.Theseareerrorsmightbefromother
functions.Also,wecouldtemporarilyprovidewithourownresultsinplaceoftheerrors.
IFNAdisplaysthevaluespecifiedbyuser,iftheresultoffunctionis#N/A.
NOTfunctionchangestheresulttoTruewithFalseandFalsewithTrue.
ORhelpsuserstowitharesult,checkingwhetherargumentisTrueorFalse.
TRUEfunctiondisplayswithalogicalvalueTrue.
XORshowswithalogical‘ExclusiveOr’foralltheprovidedarguments.
Logical Functions
FALSEfunctionreturnswithfalseforthelogicalvalues.
IFcheckswhetheraconditionisTRUEorFALSEandreturnstheansweraccordingly.
IFERRORhelpsuserstohidetheresulterrors.Theseareerrorsmightbefromother
functions.Also,wecouldtemporarilyprovidewithourownresultsinplaceoftheerrors.
IFNAdisplaysthevaluespecifiedbyuser,iftheresultoffunctionis#N/A.
NOTfunctionchangestheresulttoTruewithFalseandFalsewithTrue.
ORhelpsuserstowitharesult,checkingwhetherargumentisTrueorFalse.
TRUEfunctiondisplayswithalogicalvalueTrue.
XORshowswithalogical‘ExclusiveOr’foralltheprovidedarguments.
Logical Functions
If Function…
SYNTAXOF IF
=IF(LOGICAL TEXT, VALUE IF TRUE, VALUE IF FALSE)
IN COLUMN B DIFFERENT
CONDITIONS ARE USED AND
BASED ON THIS, IN COLUMN C
DIFFERENT RESULTS ARE SHOWN.
LOGICAL TEXT-
Any value or expression that can be evaluated to
TRUE or FALSE.
VALUE IF TRUE-
Value that is returned if logical text is TRUE.
VALUE IF FALSE-
Value that is returned if logical text is FALSE.
SYNTAXOF IF
=IF(LOGICAL TEXT, VALUE IF TRUE, VALUE IF FALSE)
IN COLUMN B DIFFERENT
CONDITIONS ARE USED AND
BASED ON THIS, IN COLUMN C
DIFFERENT RESULTS ARE SHOWN.
LOGICAL TEXT-
Any value or expression that can be evaluated to
TRUE or FALSE.
VALUE IF TRUE-
Value that is returned if logical text is TRUE.
VALUE IF FALSE-
Value that is returned if logical text is FALSE.
Math & Trig Functions…
The Math Trigbutton has currently seventy four functions
The Math Trigbutton has currently seventy four functions
Usage Of Math/Stat And Logical Together…
A function may also be "nested" (or embedded) inside of
an IF-THEN function.
This enables you to carry out complex calculations and
then contrast the real outcomes with what was
anticipated.
Consider a spreadsheet with five pupils' grades listed in
column B for the purposes of this example.
You could use the AVERAGE function to aver these scores.
Cell C2 may yield "Excellent!" or "Needs Work" depending
on the results of the class average. This is how the IF-
THEN code would be entered:
=IF(AVERAGE(B2:B6)>85,"Excellent!","Needs Work")
A function may also be "nested" (or embedded) inside of
an IF-THEN function.
This enables you to carry out complex calculations and
then contrast the real outcomes with what was
anticipated.
Consider a spreadsheet with five pupils' grades listed in
column B for the purposes of this example.
You could use the AVERAGE function to aver these scores.
Cell C2 may yield "Excellent!" or "Needs Work" depending
on the results of the class average. This is how the IF-
THEN code would be entered:
=IF(AVERAGE(B2:B6)>85,"Excellent!","Needs Work")
Nested If…
An IF function inside another IF function is
known as a stacked IF function. When we
need to deal with greater than two
conditions, nested if statements become
useful.
Let's imagine that we want to create a
Formula that verifies the day of the week.
If it's a Saturday, remember to "party
well,"
if it's a Sunday, remember to "take
some time to rest,"
and if it's any day of the week from
Monday to Friday, remember to cross
everything off your to-do list.
The formula for the flowchart is as follows
=IF(B1=”Sunday”,”timeto
rest”,IF(B1=”Saturday”,”partywell”,”todo
list”))
it's a Saturday
party well it's a Sunday
take some time
to rest
If it's any day of
the week
remember to
cross everything
off your to-do list
An IF function inside another IF function is
known as a stacked IF function. When we
need to deal with greater than two
conditions, nested if statements become
useful.
Let's imagine that we want to create a
Formula that verifies the day of the week.
If it's a Saturday, remember to "party
well,"
if it's a Sunday, remember to "take
some time to rest,"
and if it's any day of the week from
Monday to Friday, remember to cross
everything off your to-do list.
The formula for the flowchart is as follows
=IF(B1=”Sunday”,”timeto
rest”,IF(B1=”Saturday”,”partywell”,”todo
list”))
it's a Saturday
party well it's a Sunday
take some time
to rest
If it's any day of
the week
remember to
cross everything
off your to-do list
If Error…
You can indicate what should happen
if a formula or cell reference returns
an error by using the IFERROR
function.
The IFERROR syntax:
=IFERROR(value, value_if_error)
value is the argument that's true and
is verified. It generally includes a
formula or a cell reference.
The ‘value’ that is to be returned in
the event of an error is
value_if_error. #N/A, #REF!, #DIV/0!,
#VALUE!, #NUM!, #NAME?, and #NULL!
were assessed as error types.
You can indicate what should happen
if a formula or cell reference returns
an error by using the IFERROR
function.
The IFERROR syntax:
=IFERROR(value, value_if_error)
value is the argument that's true and
is verified. It generally includes a
formula or a cell reference.
The ‘value’ that is to be returned in
the event of an error is
value_if_error. #N/A, #REF!, #DIV/0!,
#VALUE!, #NUM!, #NAME?, and #NULL!
were assessed as error types.
MS Excel
Day 4 –Lookup Functions
Day 4 –Lookup Functions
Lookup Functions…
Notably, the functionsoftenused, are theVLookUp,Hyperlink,Indexand theMatch.
Notably, the functionsoftenused, are theVLookUp,Hyperlink,Indexand theMatch.
ADDRESSfunctionmakesacellreferenceastext,givenstatedrowandcolumnnumbers.
AREASfunctionshowsthenumberofareasinareference.Especially,Anareaisarangeof
adjoiningcellsorasinglecell.
CHOOSEfunctionselectsavaluefromalistofvalues,basedonanindexnumber.
COLUMNfunctiondisplaysthecolumnnumberofareference.
COLUMNSfunctionshowsthenumberofcolumnsinanarrayorreference.
FORMULATEXTfunctiondisplaysaformulaasastring.
GETPIVOTDATAfunctionextractsdatastoredinaPivotTable.
HLOOKUPfunctionseesforavalueintoprowofatable,andshowsvalueinthesame
column
INDEXfunctionshowsavalueattheintersectionofapreciserowandcolumn,ina
specifiedrange.
Lookup Functions…
AREASfunctionshowsthenumberofareasinareference.Especially,Anareaisarangeof
adjoiningcellsorasinglecell.
CHOOSEfunctionselectsavaluefromalistofvalues,basedonanindexnumber.
COLUMNfunctiondisplaysthecolumnnumberofareference.
COLUMNSfunctionshowsthenumberofcolumnsinanarrayorreference.
FORMULATEXTfunctiondisplaysaformulaasastring.
GETPIVOTDATAfunctionextractsdatastoredinaPivotTable.
HLOOKUPfunctionseesforavalueintoprowofatable,andshowsvalueinthesame
column
INDEXfunctionshowsavalueattheintersectionofapreciserowandcolumn,ina
specifiedrange.
Lookup Functions…
INDIRECTfunctiondisplaysthereferencestatedbyatextstring.
LOOKUPfunctionseesupavalue,eitherfromaone-roworone-columnrangeorfrom
anarray.
MATCHshowscomparativeposition,ofaniteminanarray,thatmatchesaspecified
valueinaspecifiedorder.
OFFSETfunctiondisplaysareferencetoarange,fromagivenreferenceofRow&
Columns.
ROWfunctionshowstherownumberofareference.
ROWSfunctiondisplaysthenumberofrowsinareferenceorarray.
RTDfunctionfetchesreal-timedatafromaprogramthatsupportstheCOMautomation.
TRANSPOSEfunctionchangesaverticalrangeofcellstoahorizontalrange,orvice
versa.
VLOOKUPfunctionseesforavalueintheleftmostcolumnofatable.Andthenreturns
avalueinthesamerow,fromaspecifiedcolumn.
Lookup Functions…
LOOKUPfunctionseesupavalue,eitherfromaone-roworone-columnrangeorfrom
anarray.
MATCHshowscomparativeposition,ofaniteminanarray,thatmatchesaspecified
valueinaspecifiedorder.
OFFSETfunctiondisplaysareferencetoarange,fromagivenreferenceofRow&
Columns.
ROWfunctionshowstherownumberofareference.
ROWSfunctiondisplaysthenumberofrowsinareferenceorarray.
RTDfunctionfetchesreal-timedatafromaprogramthatsupportstheCOMautomation.
TRANSPOSEfunctionchangesaverticalrangeofcellstoahorizontalrange,orvice
versa.
VLOOKUPfunctionseesforavalueintheleftmostcolumnofatable.Andthenreturns
avalueinthesamerow,fromaspecifiedcolumn.
Lookup Functions…
VlookupFunction…
VLOOKUP stands for Vertical Lookup(Search)
V‐lookup is a function that searches for a value (lookup value) in
the leftmost column of a given database (table array)and returns a
value in the same row from a column you specify.
Application of Look up functions
Fetch the values of a column from some other file or sheet based
on a common field.
Merge two datasets
Compare 2 files
However, VLOOKUP() has been known to give
error prone results.
•You should have minimum 2 different data tables/files (Master data and
Source data)
•Master data and Source data should have at least one common column
(same data and format)
•Common Column in the Source data should be placed as ‘Left most column’
within the dataset.
To apply it correctly, ensure following
conditions are met by your data and files
All these conditions must be taken care of
before and while applying the function.
VLOOKUP stands for Vertical Lookup(Search)
V‐lookup is a function that searches for a value (lookup value) in
the leftmost column of a given database (table array)and returns a
value in the same row from a column you specify.
Application of Look up functions
Fetch the values of a column from some other file or sheet based
on a common field.
Merge two datasets
Compare 2 files
However, VLOOKUP() has been known to give
error prone results.
•You should have minimum 2 different data tables/files (Master data and
Source data)
•Master data and Source data should have at least one common column
(same data and format)
•Common Column in the Source data should be placed as ‘Left most column’
within the dataset.
To apply it correctly, ensure following
conditions are met by your data and files
All these conditions must be taken care of
before and while applying the function.
Lookup_valueis the value to be found in the first column of the table. It is the value that you are looking for.
Lookup_valuecan be a value, a reference or a text string.
Table_arrayis the table of information in which data is looked up. It is the source database. Use a reference to a
range or a range name.
Col_index_num is the column number in table_arrayfrom which the matching value must be returned.
Range_lookupis a logical value that specifies whether you want VLOOKUP to find an exact match or an
approximate match. If Range Lookup is set as FALSE or 0, VLOOKUP will find an exact match. If exact match is not
found, the error value #N/A is returned. If it is set to TRUE or non‐zero, it finds the nearest value that is less than
lookup value
VlookupFunction…
Syntax=vlookup(lookup_value, table_array,
col_index_num, range_lookup)
Lookup_valuecan be a value, a reference or a text string.
Table_arrayis the table of information in which data is looked up. It is the source database. Use a reference to a
range or a range name.
Col_index_num is the column number in table_arrayfrom which the matching value must be returned.
Range_lookupis a logical value that specifies whether you want VLOOKUP to find an exact match or an
approximate match. If Range Lookup is set as FALSE or 0, VLOOKUP will find an exact match. If exact match is not
found, the error value #N/A is returned. If it is set to TRUE or non‐zero, it finds the nearest value that is less than
lookup value
VlookupFunction…
Syntax=vlookup(lookup_value, table_array,
col_index_num, range_lookup)
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
Master Table: Where we want to fetch the data == where
VLOOKUP() is applied.
Source table: From where we source the desired data ==
selected in ‘Table_array’ argument. Always fix it or name
it.
VlookupFunction…
Master Table: Where we want to fetch the data == where
VLOOKUP() is applied.
Source table: From where we source the desired data ==
selected in ‘Table_array’ argument. Always fix it or name
it.
VlookupFunction…
HlookupFunction…
•H‐lookup function searches for a value in the top most row of a table, and then returns a value in the same
column from a row you specify.
H‐lookup works the same way as V‐lookup. However, in this case we need to specify the row index number instead
of the column index number
Perquisites to be checked will remain same as Vlookup().
Syntax=hlookup(lookup_value, table_array,
row_index_num, range_lookup)
•H‐lookup function searches for a value in the top most row of a table, and then returns a value in the same
column from a row you specify.
H‐lookup works the same way as V‐lookup. However, in this case we need to specify the row index number instead
of the column index number
Perquisites to be checked will remain same as Vlookup().
Syntax=hlookup(lookup_value, table_array,
row_index_num, range_lookup)
Match Function…
•The MATCH function searches for a specified item in a range of cells, and then
returns the relative cell position of that item in the source table range.
MATCH(lookup_value, lookup_array, [match_type])
•Lookup_valueis the value to be found in the first column of the table. It is the
value that you are looking for. Lookup_valuecan be a value, a reference or a
text string.
•Lookup_arrayis the table of information in which data is looked up. It is the
source database. The range of cells being searched
•Col_index_numis the column number in table_arrayfrom which the
matching value must be returned.
•match_typeis a logical value that specifies whether you want to find an exact
match or an approximate match. (Optional) The number -1, 0, or 1
Here, formula result ‘5’ is the cell position of ‘Units’ Column headers.
•The MATCH function searches for a specified item in a range of cells, and then
returns the relative cell position of that item in the source table range.
MATCH(lookup_value, lookup_array, [match_type])
•Lookup_valueis the value to be found in the first column of the table. It is the
value that you are looking for. Lookup_valuecan be a value, a reference or a
text string.
•Lookup_arrayis the table of information in which data is looked up. It is the
source database. The range of cells being searched
•Col_index_numis the column number in table_arrayfrom which the
matching value must be returned.
•match_typeis a logical value that specifies whether you want to find an exact
match or an approximate match. (Optional) The number -1, 0, or 1
Here, formula result ‘5’ is the cell position of ‘Units’ Column headers.
Vlookupwith Match…
Match identifies the ‘Col_index_num’ of Vlookup(), thus creating a Dynamic lookup. Easy to
merge the tables with one common column and same spelling of headers.
Match identifies the ‘Col_index_num’ of Vlookup(), thus creating a Dynamic lookup. Easy to
merge the tables with one common column and same spelling of headers.
We have a database where our lookup value is in the middle and we need to search towards the left, we
would have to move the column to the left most corner before we use vlookup.
INDEX(array,[MATCH(lookup_value,lookup_array,[match_type])],[Match(lookup_value,
lookup_array,[match_type])])
Index function when used along with match helps us search for the data even if the lookup value is not
present on the left-most column.
Definitions for the arguments remain same
INDEX() output is Cell Value written inside the cell, MATCH() will guide towards the correct Cell position
in the dataset or data matrix.
NO Prerequisites need to be checked before applying this combo.
Index with Match…
would have to move the column to the left most corner before we use vlookup.
INDEX(array,[MATCH(lookup_value,lookup_array,[match_type])],[Match(lookup_value,
lookup_array,[match_type])])
Index function when used along with match helps us search for the data even if the lookup value is not
present on the left-most column.
Definitions for the arguments remain same
INDEX() output is Cell Value written inside the cell, MATCH() will guide towards the correct Cell position
in the dataset or data matrix.
NO Prerequisites need to be checked before applying this combo.
Index with Match…
To pull the extension from column B based on the office number in D2? With this regular Vlookupformula:
=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)
And it will work nicely as long as your users enter a valid number in D2. But what if a user inputs some number
that does not exist?
# N/A Error will appear.
Inthe above formula in the value argument of IFERROR, and put another Vlookupin the value_if_error
argument.
=IFERROR(VLOOKUP($D$2,$A$2:$B$7,2,FALSE),”Not Found”)
Vlookupwith Iferror…
=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)
And it will work nicely as long as your users enter a valid number in D2. But what if a user inputs some number
that does not exist?
# N/A Error will appear.
Inthe above formula in the value argument of IFERROR, and put another Vlookupin the value_if_error
argument.
=IFERROR(VLOOKUP($D$2,$A$2:$B$7,2,FALSE),”Not Found”)
Vlookupwith Iferror…
Nested Vlookup…
Assuming the lookup value is in cell A2 in the current sheet, and the lookup range is A2:B5 in 3
different worksheets (North, South and West), the following formula works a treat:
=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE),
IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE),
IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),"Not found")))
Assuming the lookup value is in cell A2 in the current sheet, and the lookup range is A2:B5 in 3
different worksheets (North, South and West), the following formula works a treat:
=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE),
IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE),
IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),"Not found")))
Indirect Function…
The INDIRECT function in Excel returns a cell reference from a text string. It has two
arguments, the first is required and the second is optional:
INDIRECT(ref_text, [a1])
ref_text-is a cell reference, or a reference to a cell in the form of a text string, or a
named range.
a1-is a logical value that specifies what type of reference is contained in the ref_text
argument:
If TRUE or omitted, ref_textis interpreted as an A1-style cell reference.
If FALSE, ref_textis treated as a R1C1 reference.
You can switch between the two reference types viaFile > Options > Formulas>R1C1
check box.
The INDIRECT function in Excel returns a cell reference from a text string. It has two
arguments, the first is required and the second is optional:
INDIRECT(ref_text, [a1])
ref_text-is a cell reference, or a reference to a cell in the form of a text string, or a
named range.
a1-is a logical value that specifies what type of reference is contained in the ref_text
argument:
If TRUE or omitted, ref_textis interpreted as an A1-style cell reference.
If FALSE, ref_textis treated as a R1C1 reference.
You can switch between the two reference types viaFile > Options > Formulas>R1C1
check box.
Offset Function…
TheOFFSETfunctioninExcelreturnsacellorrangeofcellsthatisagivennumberof
rowsandcolumnsfromagivencellorrange.
ThesyntaxoftheOFFSETfunctionisasfollows:
OFFSET(reference,rows,cols,[height],[width])
Thefirst3argumentsarerequiredandthelast2areoptional.Alloftheargumentscan
bereferencestoothercellsorresultsreturnedbyotherformulas.
Reference-acellorarangeofadjacentcellsfromwhichyoubasetheoffset.Youcan
thinkofitasthestartingpoint.
Rows-Thenumberofrowstomovefromthestartingpoint,upordown.Ifrowsisa
positivenumber,theformulamovesbelowthestartingreference,incaseofanegative
numberitgoesabovethestartingreference.
Cols-Thenumberofcolumnsyouwanttheformulatomovefromthestartingpoint.As
wellasrows,colscanbepositive(totherightofthestartingreference)ornegative(to
theleftofthestartingreference).
Height-thenumberofrowstoreturn.
Width-thenumberofcolumnstoreturn.
Boththeheightandwidthargumentsmustalwaysbepositivenumbers.
TheOFFSETfunctioninExcelreturnsacellorrangeofcellsthatisagivennumberof
rowsandcolumnsfromagivencellorrange.
ThesyntaxoftheOFFSETfunctionisasfollows:
OFFSET(reference,rows,cols,[height],[width])
Thefirst3argumentsarerequiredandthelast2areoptional.Alloftheargumentscan
bereferencestoothercellsorresultsreturnedbyotherformulas.
Reference-acellorarangeofadjacentcellsfromwhichyoubasetheoffset.Youcan
thinkofitasthestartingpoint.
Rows-Thenumberofrowstomovefromthestartingpoint,upordown.Ifrowsisa
positivenumber,theformulamovesbelowthestartingreference,incaseofanegative
numberitgoesabovethestartingreference.
Cols-Thenumberofcolumnsyouwanttheformulatomovefromthestartingpoint.As
wellasrows,colscanbepositive(totherightofthestartingreference)ornegative(to
theleftofthestartingreference).
Height-thenumberofrowstoreturn.
Width-thenumberofcolumnstoreturn.
Boththeheightandwidthargumentsmustalwaysbepositivenumbers.
Offset Function…
MS Excel
Day 5 –Combination of Functions &
Data Validation
Day 5 –Combination of Functions &
Data Validation
Combination of Functions…
=VLOOKUP(G4,CHOOSE(IF(H2="Unit
Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$1
1:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0
),0)
ThisformulausestheCHOOSEfunctionto
makesuretherighttableisreferredto.Let’s
analyzetheCHOOSEpartoftheformula:
CHOOSE(IF(H2=”Unit
Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$
11:$E$15,$A$19:$E$23)
Thefirstargumentoftheformula
isIF(H2=”UnitTest”,1,IF(H2=”Midterm”,2,3)),
whichchecksthecellH2andseewhatlevelof
examisbeingreferredto.Ifit’sUnitTest,it
returns$A$3:$E$7,whichhasthescoresfor
UnitTest.Ifit’sMidterm, it
returns $A$11:$E$15, else it
returns$A$19:$E$23.
=VLOOKUP(G4,CHOOSE(IF(H2="Unit
Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$1
1:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0
),0)
ThisformulausestheCHOOSEfunctionto
makesuretherighttableisreferredto.Let’s
analyzetheCHOOSEpartoftheformula:
CHOOSE(IF(H2=”Unit
Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$
11:$E$15,$A$19:$E$23)
Thefirstargumentoftheformula
isIF(H2=”UnitTest”,1,IF(H2=”Midterm”,2,3)),
whichchecksthecellH2andseewhatlevelof
examisbeingreferredto.Ifit’sUnitTest,it
returns$A$3:$E$7,whichhasthescoresfor
UnitTest.Ifit’sMidterm, it
returns $A$11:$E$15, else it
returns$A$19:$E$23.
Excel'swildcardsymbolscomeinhandyina
varietyofcircumstances.
Whensearchingforavalueinalistandthere
isn'taprecisematch,partiallook-upisnecessary.
Forinstance,let'ssayyouhavethedataset
belowandyouwanttosearchforthebusiness
ABCinalist,butthelistcontainsABCLtdrather
thanABC.Sincethereisnoprecisematchin
columnA,youcannotuseABCasthelookup
value.
Additionally,approximatematchproduces
inaccurateresultsandnecessitatessortingthe
listinincreasingorder.
Tofindthematch,youcanuseawildcard
characterwithintheVLOOKUPmethod.
=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)
Combination of Functions…
varietyofcircumstances.
Whensearchingforavalueinalistandthere
isn'taprecisematch,partiallook-upisnecessary.
Forinstance,let'ssayyouhavethedataset
belowandyouwanttosearchforthebusiness
ABCinalist,butthelistcontainsABCLtdrather
thanABC.Sincethereisnoprecisematchin
columnA,youcannotuseABCasthelookup
value.
Additionally,approximatematchproduces
inaccurateresultsandnecessitatessortingthe
listinincreasingorder.
Tofindthematch,youcanuseawildcard
characterwithintheVLOOKUPmethod.
=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)
Combination of Functions…
Case Sensitive Vlookup…
Note:InallExcelversionsotherthanExcel365,thisonlyworksasanarray
formula,soremembertopressCtrl+Shift+Entertocompleteitcorrectly.In
Excel365,duetosupportfordynamicarrays,italsoworksasaregularformula.
How this formula works:
The core part that does the trick is theCHOOSEformula with nested
EXACT:
CHOOSE({1,2}, EXACT(F2, A2:A7), B2:B7)
Here, the EXACT function compares the value in F2 against each value in
A2:A7 and returns TRUE if they are exactly the same including the letter
case, FALSE otherwise:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
For theindex_numargument of CHOOSE, we use the array constant {1,2}.
As the result, the function combines the logical values from the above
array and the values from C2:C7 into atwo-dimensional arraylike this:
{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}
The VLOOKUP function takes it from there and searches for the lookup
value (which is TRUE) in the 1st column of the 2-dimensional array
(represented by the logical values) and returns a match from the 2nd
column, which is the price we are looking for:
VLOOKUP(TRUE,
{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}, 2, 0)
Note:InallExcelversionsotherthanExcel365,thisonlyworksasanarray
formula,soremembertopressCtrl+Shift+Entertocompleteitcorrectly.In
Excel365,duetosupportfordynamicarrays,italsoworksasaregularformula.
How this formula works:
The core part that does the trick is theCHOOSEformula with nested
EXACT:
CHOOSE({1,2}, EXACT(F2, A2:A7), B2:B7)
Here, the EXACT function compares the value in F2 against each value in
A2:A7 and returns TRUE if they are exactly the same including the letter
case, FALSE otherwise:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
For theindex_numargument of CHOOSE, we use the array constant {1,2}.
As the result, the function combines the logical values from the above
array and the values from C2:C7 into atwo-dimensional arraylike this:
{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}
The VLOOKUP function takes it from there and searches for the lookup
value (which is TRUE) in the 1st column of the 2-dimensional array
(represented by the logical values) and returns a match from the 2nd
column, which is the price we are looking for:
VLOOKUP(TRUE,
{FALSE,155;FALSE,186;FALSE,90;TRUE,54;FALSE,159;FALSE,28}, 2, 0)
Data Validation…
Supposeyoudonotwanttheusertoentera
nontextvalueinacelloryouwanttorestrict
dataentrytocertainvalues.YoumayuseData
Validationforthese.
DataValidationisaprocesswhichrestrictsthe
usersfromenteringinvaliddataforindividual
cellsorcellranges.
Errorpop-upboxindicatescorrectvaluetobe
entered.
Itlimitsthedataentrytoaparticulartype,
suchaswholenumbers,decimalnumbersor
textandsetslimitonvalidentries.
Note:-Ifyoudonotenteratitleortext,thetitledefaultsto"MicrosoftExcel"andthe
messageto"Thevalueyouenteredisnotvalid.Auserhasrestrictedvaluesthatcan
beenteredintothiscell."
Supposeyoudonotwanttheusertoentera
nontextvalueinacelloryouwanttorestrict
dataentrytocertainvalues.YoumayuseData
Validationforthese.
DataValidationisaprocesswhichrestrictsthe
usersfromenteringinvaliddataforindividual
cellsorcellranges.
Errorpop-upboxindicatescorrectvaluetobe
entered.
Itlimitsthedataentrytoaparticulartype,
suchaswholenumbers,decimalnumbersor
textandsetslimitonvalidentries.
Note:-Ifyoudonotenteratitleortext,thetitledefaultsto"MicrosoftExcel"andthe
messageto"Thevalueyouenteredisnotvalid.Auserhasrestrictedvaluesthatcan
beenteredintothiscell."
Data Validation Rules…
Setting Data Validation Rules
Select the cells for which you want to create a
validation rule.
On the Data tab, in the Data Tools group, click Data
Validation to open the Data Validation dialog box.
Activate the Settings tab.
From the Allow list, select a data validation option.
From the Data list, select the operator you want.
Then complete the remaining entries.
Enter the Input Message if required in Input Message
tab.
Enter the error message if required in Error Alert tab.
Click OK to set the validation rule and close the
dialog box.
Setting Data Validation Rules
Select the cells for which you want to create a
validation rule.
On the Data tab, in the Data Tools group, click Data
Validation to open the Data Validation dialog box.
Activate the Settings tab.
From the Allow list, select a data validation option.
From the Data list, select the operator you want.
Then complete the remaining entries.
Enter the Input Message if required in Input Message
tab.
Enter the error message if required in Error Alert tab.
Click OK to set the validation rule and close the
dialog box.
Data Validation List…
Creating a Drop down List
A list is an effective form of data validation where the user
is allowed to select an option from a drop‐down list which is
built‐in to the cell.
The data source may be written manually by the user or
selected from the same sheet.
Steps are as follows:
Select a blank cell
Select Data Tab
Select Data Validation from Data Tool group
Select List
In Source, select the cell with values, or type the data with
comm
Tip:Ifthesourceisfromadifferentsheet,createa‘named
range’forallthevaluesandusethenameintheSourcefieldfor
DataValidation.
Note:-IfyouareworkingonMSOffice2007,youneedtocopy
pastelistinthesamesheetasdropdownlist.Inthisoldversion,
DVisnotequippedtoreferencecellrangefromanothersheet.
Creating a Drop down List
A list is an effective form of data validation where the user
is allowed to select an option from a drop‐down list which is
built‐in to the cell.
The data source may be written manually by the user or
selected from the same sheet.
Steps are as follows:
Select a blank cell
Select Data Tab
Select Data Validation from Data Tool group
Select List
In Source, select the cell with values, or type the data with
comm
Tip:Ifthesourceisfromadifferentsheet,createa‘named
range’forallthevaluesandusethenameintheSourcefieldfor
DataValidation.
Note:-IfyouareworkingonMSOffice2007,youneedtocopy
pastelistinthesamesheetasdropdownlist.Inthisoldversion,
DVisnotequippedtoreferencecellrangefromanothersheet.
MS Excel
Day 6 –Date Function & Text
Function
Day 6 –Date Function & Text
Function
Date & Time Function…
DATEfunctionshowsthenumberthatdenotesthedateinMicrosoftExcelDate-Time
Code.
DATEVALUEfunctionchangesadate,intheformoftexttoanumber,thatdenotesthe
date.
DAYfunctionshowsthedayofthemonth,anumberfromOne(1)toThirtyOne(31).
DAYSfunctionshowsthenumberofdaysamongthetwodates.
DAYS360functionshowsthenumberofdaysbetweentwodates,basedonayeari.e.
(360-day).
EDATEdisplaystheserialnumberofdate,beforeorafterstartdate,i.e.the
markednumberofmonths.
EOMONTHshowstheserialnumberoflastdayofmonth,beforeoraftera
statednumberofmonths.
HOUR functionshowsthe hour as a number, from Zero (0) to Twenty Three (23) i.e.
(12:00 A.M.) to (11:00 P.M.).
ISOWEEKNUM functiondisplaysthe ISO Week number in theyear for a specifieddate.
MINUTE functionshowsthe minute, a number from Zero (0) to Fifty Nine (59).
Date & Time Function…
Code.
DATEVALUEfunctionchangesadate,intheformoftexttoanumber,thatdenotesthe
date.
DAYfunctionshowsthedayofthemonth,anumberfromOne(1)toThirtyOne(31).
DAYSfunctionshowsthenumberofdaysamongthetwodates.
DAYS360functionshowsthenumberofdaysbetweentwodates,basedonayeari.e.
(360-day).
EDATEdisplaystheserialnumberofdate,beforeorafterstartdate,i.e.the
markednumberofmonths.
EOMONTHshowstheserialnumberoflastdayofmonth,beforeoraftera
statednumberofmonths.
HOUR functionshowsthe hour as a number, from Zero (0) to Twenty Three (23) i.e.
(12:00 A.M.) to (11:00 P.M.).
ISOWEEKNUM functiondisplaysthe ISO Week number in theyear for a specifieddate.
MINUTE functionshowsthe minute, a number from Zero (0) to Fifty Nine (59).
Date & Time Function…
MONTHfunctiondisplaysthemonth,anumberfromOne(1)forJanuarytoTwelve(12)for
December.
NETWORKDAYSfunctionshowsthenumberofentireworkdaysamongtwodates.
NETWORKDAYS.INTLshowsthenumberofentireworkdaysamongtwodateswithcustomweekend
limits.
NOWfunctiondisplaysthecurrentdateandtime,formattedasadateandtime.
SECONDfunctionshowsthesecond,anumberfromZero(0)toFiftyNine(59).
TIMEfunctionchangesspecifiednumbersi.e.Hours,Minutes,andSecondstoanExcelserialnumber,
formattedwithatimeformat.
TIMEVALUEfunctionchangesatexttimetoanExcelserialnumberforatime.
TODAYfunctionshowsthecurrentdateformattedasadate.
WEEKDAYdisplaysanumber,recognizingdayoftheweekofadate,fromOne(1)toSeven(7).
WEEKNUMfunctionshowstheweeknumberintheyear.
WORKDAYfunctiondisplaystheserialnumberofthedate,beforeorafteraspecifiednumber
ofworkdays.
WORKDAY.INTLshowstheserialnumberofthedate,beforeorafterastatednumberofworkdays
withcustomweekendlimits.
YEARfunctiondisplaystheyearofadate.
YEARFRACfunctionshowstheyearfraction,illustratingthenumberofentiredays,amongstartdate
andenddate.
Date & Time Function…
December.
NETWORKDAYSfunctionshowsthenumberofentireworkdaysamongtwodates.
NETWORKDAYS.INTLshowsthenumberofentireworkdaysamongtwodateswithcustomweekend
limits.
NOWfunctiondisplaysthecurrentdateandtime,formattedasadateandtime.
SECONDfunctionshowsthesecond,anumberfromZero(0)toFiftyNine(59).
TIMEfunctionchangesspecifiednumbersi.e.Hours,Minutes,andSecondstoanExcelserialnumber,
formattedwithatimeformat.
TIMEVALUEfunctionchangesatexttimetoanExcelserialnumberforatime.
TODAYfunctionshowsthecurrentdateformattedasadate.
WEEKDAYdisplaysanumber,recognizingdayoftheweekofadate,fromOne(1)toSeven(7).
WEEKNUMfunctionshowstheweeknumberintheyear.
WORKDAYfunctiondisplaystheserialnumberofthedate,beforeorafteraspecifiednumber
ofworkdays.
WORKDAY.INTLshowstheserialnumberofthedate,beforeorafterastatednumberofworkdays
withcustomweekendlimits.
YEARfunctiondisplaystheyearofadate.
YEARFRACfunctionshowstheyearfraction,illustratingthenumberofentiredays,amongstartdate
andenddate.
Date & Time Function…
DateDifFunction…
SYNTAX OF DATEDIF
=DATEDIF(START_DATE,END_DATE,
”INTERVAL”)
“D”-DAYS
“M”-MONTHS
“ Y”-YEARS
“ YM”-MONTHS OVER YEAR
“MD”-DAYS OVER MONTH
START DATE-
Date from which u want to calculate difference.
END DATE-
Date up to which u want to calculate difference.
INTERVAL-
Form in which u want to calculate difference.
SYNTAX OF DATEDIF
=DATEDIF(START_DATE,END_DATE,
”INTERVAL”)
“D”-DAYS
“M”-MONTHS
“ Y”-YEARS
“ YM”-MONTHS OVER YEAR
“MD”-DAYS OVER MONTH
START DATE-
Date from which u want to calculate difference.
END DATE-
Date up to which u want to calculate difference.
INTERVAL-
Form in which u want to calculate difference.
Text Functions…
BAHTTEXTfunctionhelpstoconvertsanumbertotexti.e.BAHT.
CHARfunctiondisplaysthecharacterfromthecharacterset,statedbythecodenumber.
CLEANfunctioneliminatesallthenon-printablecharactersfromthetexts.
CODEfunctionshowsanumericcodeforinitialcharacterinatextstring,inthecharacterset.
CONCATENATEfunctioncombinesmultipletextstringsintoonetextstring.
DOLLARfunctionconvertsanumbertotextbyusingtheCurrencyFormat.
EXACTreturnsTrueorFalsebycheckingwhethertwotextstringsaresameornot.
FINDfunctionreturnstheforemostpositionofonetextstringwithinanother.Notably,thisFIND
functionisCase-Sensitivefunction.
FIXEDfunctionshowstextresultwithorwithoutcommas,byroundinganumbertostatednumberof
decimals.
LEFTfunctionshowsspecifiednumberofcharactersfromthestartingfromtheLeftHandSideofa
textstring.
LENfunctiondisplaysthenumbersofcharactersinatextstring.
LOWERfunctionchangesallthelettersinatextstringtolowercase.
MIDfunctionshowscharactersfromthemiddleoftextstring,providedwithainitialpositionand
length.
NUMBERVALUEfunctionchangestexttonumberinalocale-independentmanner.
Text Functions…
CHARfunctiondisplaysthecharacterfromthecharacterset,statedbythecodenumber.
CLEANfunctioneliminatesallthenon-printablecharactersfromthetexts.
CODEfunctionshowsanumericcodeforinitialcharacterinatextstring,inthecharacterset.
CONCATENATEfunctioncombinesmultipletextstringsintoonetextstring.
DOLLARfunctionconvertsanumbertotextbyusingtheCurrencyFormat.
EXACTreturnsTrueorFalsebycheckingwhethertwotextstringsaresameornot.
FINDfunctionreturnstheforemostpositionofonetextstringwithinanother.Notably,thisFIND
functionisCase-Sensitivefunction.
FIXEDfunctionshowstextresultwithorwithoutcommas,byroundinganumbertostatednumberof
decimals.
LEFTfunctionshowsspecifiednumberofcharactersfromthestartingfromtheLeftHandSideofa
textstring.
LENfunctiondisplaysthenumbersofcharactersinatextstring.
LOWERfunctionchangesallthelettersinatextstringtolowercase.
MIDfunctionshowscharactersfromthemiddleoftextstring,providedwithainitialpositionand
length.
NUMBERVALUEfunctionchangestexttonumberinalocale-independentmanner.
Text Functions…
PROPERchangesthefirstletterineachwordto“UPPERCASE”,andallotherlettersto
“lowercase”.
REPLACEfunctionwithadifferenttextstringinapartoftextstring.
REPTfunctionhelpstorepeatacertaintextprovidednumberoftimes.
RIGHTfunctionshowsspecifiednumberofcharactersfromthestartingfromtheRightHand
Sideofatextstring.
SEARCHshowsthenumberofthecharacterfromlefttoright,atwhichaspecificcharacter
getsfirstfound.
SUBSTITUTEfunctionreplacesexistingtextwithnewtextinatextstring.
“T”functioncheckswhetheravalueistext,andreturnstext,orblanktext,ifitisorisn’t.
TEXTfunctionchangesavaluetotextinastatednumberformat.
TRIMfunctioneliminatesallthespacesexceptforsinglespacesbetweenwords,fromatext
string.
UNICHARfunctionshowstheUnicodecharacterreferencedbytheprovidednumericvalue.
UNICODEfunctiondisplaysthenumber(CodePoint)correspondingtotheinitialcharacterof
thetext.
UPPERfunctionschangesatextstringtoalluppercaseletters.
VALUEfunctionchangesatextstringthatdenotesanumbertoanumber.
Text Functions…
“lowercase”.
REPLACEfunctionwithadifferenttextstringinapartoftextstring.
REPTfunctionhelpstorepeatacertaintextprovidednumberoftimes.
RIGHTfunctionshowsspecifiednumberofcharactersfromthestartingfromtheRightHand
Sideofatextstring.
SEARCHshowsthenumberofthecharacterfromlefttoright,atwhichaspecificcharacter
getsfirstfound.
SUBSTITUTEfunctionreplacesexistingtextwithnewtextinatextstring.
“T”functioncheckswhetheravalueistext,andreturnstext,orblanktext,ifitisorisn’t.
TEXTfunctionchangesavaluetotextinastatednumberformat.
TRIMfunctioneliminatesallthespacesexceptforsinglespacesbetweenwords,fromatext
string.
UNICHARfunctionshowstheUnicodecharacterreferencedbytheprovidednumericvalue.
UNICODEfunctiondisplaysthenumber(CodePoint)correspondingtotheinitialcharacterof
thetext.
UPPERfunctionschangesatextstringtoalluppercaseletters.
VALUEfunctionchangesatextstringthatdenotesanumbertoanumber.
Text Functions…
Text Function…
SYNTAX OF FUNCTIONS
1. LOWER FUNCTION
=LOWER(TEXT)
2. UPPER FUNCTION
=UPPER(TEXT)
3. PROPER FUNCTION
=PROPER(TEXT)
LOWER
TO CONVERT
TEXT FROM
CAPITAL TO
SMALL.
UPPER
TO CONVERT
TEXT FROM
SMALL TO
CAPITAL.
PROPER
TO CAPITALISED
EACH WORD OF
TEXT.
SYNTAX OF FUNCTIONS
1. LOWER FUNCTION
=LOWER(TEXT)
2. UPPER FUNCTION
=UPPER(TEXT)
3. PROPER FUNCTION
=PROPER(TEXT)
LOWER
TO CONVERT
TEXT FROM
CAPITAL TO
SMALL.
UPPER
TO CONVERT
TEXT FROM
SMALL TO
CAPITAL.
PROPER
TO CAPITALISED
EACH WORD OF
TEXT.
Text Functions…
SYNTAX OF FUNCTIONS
1.LEFT FUNCTION
=LEFT(TEXT,NUM_CHARS)
2.RIGHT FUNCTION
=RIGHT(TEXT,NUM_CHARS)
3. MID FUNCTION
=MID(TEXT,STARTNUM,NUM_CHAR)
LEFT
RETURN
SPECIFIED NO.
OF CHARACTER
FROM START OF
TEXT.
RIGHT
RETURN
SPECIFIED NO.
OF CHRACTER
FROM END OF
TEXT.
MID
RETURN
CHARACTER FROM
MIDDLE OF
TEXT,GIVEN A
STARTING
POSITION.
SYNTAX OF FUNCTIONS
1.LEFT FUNCTION
=LEFT(TEXT,NUM_CHARS)
2.RIGHT FUNCTION
=RIGHT(TEXT,NUM_CHARS)
3. MID FUNCTION
=MID(TEXT,STARTNUM,NUM_CHAR)
LEFT
RETURN
SPECIFIED NO.
OF CHARACTER
FROM START OF
TEXT.
RIGHT
RETURN
SPECIFIED NO.
OF CHRACTER
FROM END OF
TEXT.
MID
RETURN
CHARACTER FROM
MIDDLE OF
TEXT,GIVEN A
STARTING
POSITION.
Other Functions…
NOW RETURNS CURRENT DATE AND TIME.
TODAY RETURNS CURRENT DATE ONLY.
MOD RETURNS THE REMAINDER AFTER A NO.
IS DIVIDED BY A DIVISOR.
LEN RETURNS THE NO. OF CHARACTERS IN A
TEXT STRING.
SUM ADD ALL THE NUMBERS.
NOW RETURNS CURRENT DATE AND TIME.
TODAY RETURNS CURRENT DATE ONLY.
MOD RETURNS THE REMAINDER AFTER A NO.
IS DIVIDED BY A DIVISOR.
LEN RETURNS THE NO. OF CHARACTERS IN A
TEXT STRING.
SUM ADD ALL THE NUMBERS.
MS Excel
Day 7 –Data Cleaning, Data
Formatting, Filtering & Sorting
Day 7 –Data Cleaning, Data
Formatting, Filtering & Sorting
Number Formatting…
Customer Number Formatting…
Number Formatting shortcuts…
Creating Table in Excel…
Removing a table in Excel…
Below are the steps to remove the Excel table formatting:
Select any cell in the Excel table.
Click the Design tab (this is a contextual tab and only appears when you click
any cell in the table)
In Table Styles, click on the More icon (the one at the bottom of the small
scrollbar.
Click on the Clear option.
Below are the steps to remove the Excel table formatting:
Select any cell in the Excel table.
Click the Design tab (this is a contextual tab and only appears when you click
any cell in the table)
In Table Styles, click on the More icon (the one at the bottom of the small
scrollbar.
Click on the Clear option.
Table Design Tab…
Filtering in Excel…
Thefilterinexcelhelpsdisplayrelevantdataby
eliminatingtheirrelevantentriestemporarily
fromtheview.
Thedataisfilteredasperthegivencriteria.
Thepurposeoffilteringistofocusonthe
crucialareasofadataset.
Keyboardshortcuts
•SHIFT+CTRL+L
•ALT+D+F+F
Filtering in Excel…
eliminatingtheirrelevantentriestemporarily
fromtheview.
Thedataisfilteredasperthegivencriteria.
Thepurposeoffilteringistofocusonthe
crucialareasofadataset.
Keyboardshortcuts
•SHIFT+CTRL+L
•ALT+D+F+F
Filtering in Excel…
Sorting in Excel…
TOSORTINALPHABETICALORDER:
Selectacellinthecolumnyouwanttosort(Inthis
example,wechooseacellincolumnQ).
ClicktheSort&FiltercommandintheEditinggroupon
theHometab.
SelectSortAtoZ.NowtheinformationintheCategory
columnisorganizedinalphabeticalorder.
TOSORTFROMSMALLESTTOLARGEST:
Selectacellinthecolumnyouwanttosort(Inthis
example,wechooseacellincolumnQ).
ClicktheSort&FiltercommandintheEditinggroupon
theHometab.
SelectFromSmallesttoLargest.Nowtheinformationis
organizedfromthesmallesttolargestamount.
TOSORTINALPHABETICALORDER:
Selectacellinthecolumnyouwanttosort(Inthis
example,wechooseacellincolumnQ).
ClicktheSort&FiltercommandintheEditinggroupon
theHometab.
SelectSortAtoZ.NowtheinformationintheCategory
columnisorganizedinalphabeticalorder.
TOSORTFROMSMALLESTTOLARGEST:
Selectacellinthecolumnyouwanttosort(Inthis
example,wechooseacellincolumnQ).
ClicktheSort&FiltercommandintheEditinggroupon
theHometab.
SelectFromSmallesttoLargest.Nowtheinformationis
organizedfromthesmallesttolargestamount.
Sorting Date & Number…
Custom Sort…
MS Excel
Day 8 –Conditional formatting &
Data Visualisation
Day 8 –Conditional formatting &
Data Visualisation
Conditional Formatting…
Conditional Formatting…
Custom Conditional Formatting…
Data Tab in Excel…
Text to Column (Data Split)…
Text to Column (Data Split)…
Bar Chart…
Formatting the Charts…
Formatting the Charts…
Formatting the Charts…
Column & Line Chart…
Scatter Plot…
Combo & Pie Chart…
Gantt Chart…
Steps of Gantt Chart…
Steps of Gantt Chart…
Steps of Gantt Chart…
Water Fall Chart…
MS Excel
Day 9 –Data Summarization: Pivot
Day 9 –Data Summarization: Pivot
Pivot…
Click here
to open
APivotTableisaninteractiveworksheetbasedtablethatquicklysummarizes
largeamountsofdatausingtheformatandcalculationmethodsyou
choose.
ItiscalledaPivotTablebecauseyoucanrotateitsrowandcolumnheadings
aroundthecoredataareatogiveyoudifferentviewsofthesourcedata.
Assourcedatachanges,youcanupdateapivottable.Itresidesona
worksheetthus;youcanintegrateaPivotTableintoalargerworksheetmodel
usingstandardformulas.
APivotTableisapowerfultooltocalculate,summarize,andanalyzedatathat
letsyouseecomparisons,patterns,andtrendsindata.Whichcanbepresent
inanExcelworkbookorimportedfromanexternaldatabasesuchasMicrosoft
AccessorSQLServerand98moresources
PivotTablesworkalittlebitdifferentlydependingonwhatplatformyouare
usingtorunExcel.
Tip:Pivottableoptionsandfeaturesdifferfromversiontoversion.Basicfunctionswillbe
availablewithallversions.AdvancedfeatureslikeRecommendedPivottables,PowerPivotwas
introducedwith2013version.
Click here
to open
Click here
to open
APivotTableisaninteractiveworksheetbasedtablethatquicklysummarizes
largeamountsofdatausingtheformatandcalculationmethodsyou
choose.
ItiscalledaPivotTablebecauseyoucanrotateitsrowandcolumnheadings
aroundthecoredataareatogiveyoudifferentviewsofthesourcedata.
Assourcedatachanges,youcanupdateapivottable.Itresidesona
worksheetthus;youcanintegrateaPivotTableintoalargerworksheetmodel
usingstandardformulas.
APivotTableisapowerfultooltocalculate,summarize,andanalyzedatathat
letsyouseecomparisons,patterns,andtrendsindata.Whichcanbepresent
inanExcelworkbookorimportedfromanexternaldatabasesuchasMicrosoft
AccessorSQLServerand98moresources
PivotTablesworkalittlebitdifferentlydependingonwhatplatformyouare
usingtorunExcel.
Tip:Pivottableoptionsandfeaturesdifferfromversiontoversion.Basicfunctionswillbe
availablewithallversions.AdvancedfeatureslikeRecommendedPivottables,PowerPivotwas
introducedwith2013version.
Click here
to open
Pivot Reports…
The data on which a PivotTable is
based is called the Source Data.
Each column represents a field or category
of information, which you can assign to
different parts of the PivotTable to
determine how the data is arranged.
You can add four types of fields, The
fields are explained in the following
tables.
The data on which a PivotTable is
based is called the Source Data.
Each column represents a field or category
of information, which you can assign to
different parts of the PivotTable to
determine how the data is arranged.
You can add four types of fields, The
fields are explained in the following
tables.
Sample Pivot reports…Unique_Advisor_IdSum of AUMAverage of Units
1000103245350.67 2256.07
1000373284189.18 2140.27
1000474 68185.38 674.90
1000541 61997.18 1070.29
1000973120859.32 1885.18
1001058281424.73 3762.53
1001478 41411.28 3445.20
1001667395481.61 3034.54
1001726402228.92 3714.59
1002143782189.49 4799.98
Grand Total 2683317.74 2954.04 Unique_Advisor_IdCount of Unique_Investment_Id
1000103 6
1000373 10
1000474 5
1000541 4
1000973 6
1001058 6
1001478 1
1001667 9
1001726 10
1002143 13
Grand Total 70 category Average of category_return_10yearsAverage of rating
Allocation - 50% to 70% Equity 7.13 4
Allocation - 70% to 85% Equity 7.46 5
Diversified Emerging Mkts 2.3 3
Emerging Markets Bond 3.95 3
Foreign Large Blend 3.65 3
Global Real Estate 4.33 2
High Yield Bond 5.06 3
Intermediate Core Bond 3.4 3
Large Blend 11.28 3
Large Growth 14.19 3
Large Value 8.64 2
Mid-Cap Growth 12.76 1
Multialternative 2.31 2
Muni New York Long 3.56 1
Muni Pennsylvania 3.52 2
Muni Single State Long 3.26 1
Small Blend 8.48 2
Small Value 6.39 3
Tactical Allocation 4.26 3
Technology 16.17 5
World Allocation 4.61 3
World Large Stock 7.53 2 fund_mean_annual_return_10yearsCount of category
0-0.5 15
0.5-1 12
1-1.5 6
1.5-2 1
Grand Total 34
1000103245350.67 2256.07
1000373284189.18 2140.27
1000474 68185.38 674.90
1000541 61997.18 1070.29
1000973120859.32 1885.18
1001058281424.73 3762.53
1001478 41411.28 3445.20
1001667395481.61 3034.54
1001726402228.92 3714.59
1002143782189.49 4799.98
Grand Total 2683317.74 2954.04 Unique_Advisor_IdCount of Unique_Investment_Id
1000103 6
1000373 10
1000474 5
1000541 4
1000973 6
1001058 6
1001478 1
1001667 9
1001726 10
1002143 13
Grand Total 70 category Average of category_return_10yearsAverage of rating
Allocation - 50% to 70% Equity 7.13 4
Allocation - 70% to 85% Equity 7.46 5
Diversified Emerging Mkts 2.3 3
Emerging Markets Bond 3.95 3
Foreign Large Blend 3.65 3
Global Real Estate 4.33 2
High Yield Bond 5.06 3
Intermediate Core Bond 3.4 3
Large Blend 11.28 3
Large Growth 14.19 3
Large Value 8.64 2
Mid-Cap Growth 12.76 1
Multialternative 2.31 2
Muni New York Long 3.56 1
Muni Pennsylvania 3.52 2
Muni Single State Long 3.26 1
Small Blend 8.48 2
Small Value 6.39 3
Tactical Allocation 4.26 3
Technology 16.17 5
World Allocation 4.61 3
World Large Stock 7.53 2 fund_mean_annual_return_10yearsCount of category
0-0.5 15
0.5-1 12
1-1.5 6
1.5-2 1
Grand Total 34
Charts in Pivot…
Sometimesit'shardtoseethebigpicturewhenyourrawdatahasn’tbeen
summarized.YourfirstinstinctmaybetocreateaPivotTable,butnot
everyonecanlookatnumbersinatableandquicklyseewhat'sgoingon.
PivotChartsareagreatwaytoadddatavisualizationstoyourdata.
ApivotchartisthevisualrepresentationofapivottableinExcel.Pivot
chartsandpivottablesareconnectedwitheachother.
A‘pivotchart’addsvalueandcategoryseriesbydraggingthefieldname
ontoaxesinsteadofchoosingthechartwizard.A'normal'chartisusually
basedonarangeofdatainrowandcolumns.
Sourcedataofstandardchartsarelinkeddirectlytoworksheetcells,while
PivotChartsarebasedontheirassociatedPivotTable'sdatasource.Unlikea
standardchart,youcannotchangethechartdatarangeinaPivotChart's
‘SelectDataSource’dialogbox.
Keepitsimple,avoid3Dgraphs
Sometimesit'shardtoseethebigpicturewhenyourrawdatahasn’tbeen
summarized.YourfirstinstinctmaybetocreateaPivotTable,butnot
everyonecanlookatnumbersinatableandquicklyseewhat'sgoingon.
PivotChartsareagreatwaytoadddatavisualizationstoyourdata.
ApivotchartisthevisualrepresentationofapivottableinExcel.Pivot
chartsandpivottablesareconnectedwitheachother.
A‘pivotchart’addsvalueandcategoryseriesbydraggingthefieldname
ontoaxesinsteadofchoosingthechartwizard.A'normal'chartisusually
basedonarangeofdatainrowandcolumns.
Sourcedataofstandardchartsarelinkeddirectlytoworksheetcells,while
PivotChartsarebasedontheirassociatedPivotTable'sdatasource.Unlikea
standardchart,youcannotchangethechartdatarangeinaPivotChart's
‘SelectDataSource’dialogbox.
Keepitsimple,avoid3Dgraphs
Slicer in Pivot…
SlicersinExcelaresoftwarefiltersusedalongwith
exceltablesorpivottablesoveralargeamountof
data.
Itisusedforfilteringoutthedataplusithelpswithan
easyunderstandingoftheinformationbeingextracted
anddisplayedonthescreen.
Addslicertoyourreportby
GotoInserttab>Slicer.
IntheInsertSlicersdialogbox,selectthecheck
boxesforthefieldsyouwanttodisplay,then
selectOK.
Aslicerwillbecreatedforeveryfieldthatyou
selected.Clickinganyoftheslicerbuttonswill
automaticallyapplythatfiltertothelinkedtable
orPivotTable.
Tip:Slicerwasintroducedin2010,thenenhancedin2013withintroducing
‘Timeline’.
WhenaddingSlicerInDashboard,youneedto‘ReportConnections’toconnect
multiplereportstooneslicerortimeline.
Interestingly,thisoptionin2010,wascalled‘Pivottableconnections’.
Slicer to be added in standard excel report
Slicer to be added in Pivot report
SlicersinExcelaresoftwarefiltersusedalongwith
exceltablesorpivottablesoveralargeamountof
data.
Itisusedforfilteringoutthedataplusithelpswithan
easyunderstandingoftheinformationbeingextracted
anddisplayedonthescreen.
Addslicertoyourreportby
GotoInserttab>Slicer.
IntheInsertSlicersdialogbox,selectthecheck
boxesforthefieldsyouwanttodisplay,then
selectOK.
Aslicerwillbecreatedforeveryfieldthatyou
selected.Clickinganyoftheslicerbuttonswill
automaticallyapplythatfiltertothelinkedtable
orPivotTable.
Tip:Slicerwasintroducedin2010,thenenhancedin2013withintroducing
‘Timeline’.
WhenaddingSlicerInDashboard,youneedto‘ReportConnections’toconnect
multiplereportstooneslicerortimeline.
Interestingly,thisoptionin2010,wascalled‘Pivottableconnections’.
Slicer to be added in standard excel report
Slicer to be added in Pivot report
Multiple Pivot Connections using Slicer…
STEP 1:Create 2Pivot Tablesby clicking in your data set and selectingInsert >
Pivot Table > New Worksheet/Existing Worksheet
STEP 2:Click in Pivot Table #1 and insert a MONTH Slicerby going toPivotTable
Tools > Analyze/Options > Insert Slicer > Any Column > OK
STEP 3:Click in Pivot Table #2 and insert a YEAR Slicerby going toPivotTable Tools
> Analyze/Options > Insert Slicer > Any other Column > OK
STEP 4:Right Click on Slicer #1and go toReport Connections(Excel 2013, 2016,
2019 & Office 365)/PivotTable Connections (Excel 2010)
>“check”thePivotTable2boxand pressOK
STEP 5:Right Click on Slicer #2and go toReport Connections(Excel 2013, 2016,
2019 & Office 365)/PivotTable Connections (Excel 2010)
>“check”thePivotTable1boxand pressOK
Now as you select each Slicer’s items, both Pivot Tables will change!
STEP 1:Create 2Pivot Tablesby clicking in your data set and selectingInsert >
Pivot Table > New Worksheet/Existing Worksheet
STEP 2:Click in Pivot Table #1 and insert a MONTH Slicerby going toPivotTable
Tools > Analyze/Options > Insert Slicer > Any Column > OK
STEP 3:Click in Pivot Table #2 and insert a YEAR Slicerby going toPivotTable Tools
> Analyze/Options > Insert Slicer > Any other Column > OK
STEP 4:Right Click on Slicer #1and go toReport Connections(Excel 2013, 2016,
2019 & Office 365)/PivotTable Connections (Excel 2010)
>“check”thePivotTable2boxand pressOK
STEP 5:Right Click on Slicer #2and go toReport Connections(Excel 2013, 2016,
2019 & Office 365)/PivotTable Connections (Excel 2010)
>“check”thePivotTable1boxand pressOK
Now as you select each Slicer’s items, both Pivot Tables will change!
MS Excel
Day 10 –Data Summarization:
Dashboards
Day 10 –Data Summarization:
Dashboards
What is a Dashboard…?
AdashboardisaninformationmanagementtoolusedtotrackKPIs,metrics,and
keydatapointsthatarerelevanttoyourbusiness,department,oraspecific
process.Dashboardsaggregateandvisualizedatafrommultiplesources,suchas
databases,locallyhostedfiles,andwebservices.
Areportwouldonlycollectandshowdatainasingletable.Forexample,ifa
managerwantstoknowhowthesaleshavegrownoverthelastperiodandwhich
regionwerethemostprofitable,areportwouldnotbeabletoanswerit.Itwould
simplyreportalltherelevantsalesdata.Thesereportsarethenusedtocreate
thatwillaidindecisionmaking.
Adashboard,ontheotherhand,wouldinstantlyanswerimportantquestionssuch
whichregionsareperformingbetterandwhichproductsshouldthemanagement
focuson.
Thesedashboardscouldbestaticorinteractive(wheretheusercanmake
selectionsandchangeviewsandthedatawoulddynamicallyupdate).
AdashboardisaninformationmanagementtoolusedtotrackKPIs,metrics,and
keydatapointsthatarerelevanttoyourbusiness,department,oraspecific
process.Dashboardsaggregateandvisualizedatafrommultiplesources,suchas
databases,locallyhostedfiles,andwebservices.
Areportwouldonlycollectandshowdatainasingletable.Forexample,ifa
managerwantstoknowhowthesaleshavegrownoverthelastperiodandwhich
regionwerethemostprofitable,areportwouldnotbeabletoanswerit.Itwould
simplyreportalltherelevantsalesdata.Thesereportsarethenusedtocreate
thatwillaidindecisionmaking.
Adashboard,ontheotherhand,wouldinstantlyanswerimportantquestionssuch
whichregionsareperformingbetterandwhichproductsshouldthemanagement
focuson.
Thesedashboardscouldbestaticorinteractive(wheretheusercanmake
selectionsandchangeviewsandthedatawoulddynamicallyupdate).
Types of Dashboard..
Dashboard
Operational
To monitor real-time or transactional data
against key metrics and KPIs
Ex. Website
performance metrics
like new users or
bounce rate
Follower count or
comments across your
social media channels
Return on ad spend to
track the effectiveness
of your digital
advertising dollars
Strategic
To summarize performance over a period of
time (month, quarter, year).
Monthly, quarterly, or
yearly fiscal
performance
Account and MRR
growth rate
Earnings before
interest, tax,
depreciation, and
amortization (otherwise
known as EBITDA)
Analytical
To investigate trends, predict outcomes, and
discover insights and establish targets based
on insights into historical data.
Annual contract value
to track the dollar
amount an average
customer contract is
worth
Measure your company
spending habits with
the Bessemer Efficiency
Score
Understand the
increase in daily active
users over a period time
Dashboard
Operational
To monitor real-time or transactional data
against key metrics and KPIs
Ex. Website
performance metrics
like new users or
bounce rate
Follower count or
comments across your
social media channels
Return on ad spend to
track the effectiveness
of your digital
advertising dollars
Strategic
To summarize performance over a period of
time (month, quarter, year).
Monthly, quarterly, or
yearly fiscal
performance
Account and MRR
growth rate
Earnings before
interest, tax,
depreciation, and
amortization (otherwise
known as EBITDA)
Analytical
To investigate trends, predict outcomes, and
discover insights and establish targets based
on insights into historical data.
Annual contract value
to track the dollar
amount an average
customer contract is
worth
Measure your company
spending habits with
the Bessemer Efficiency
Score
Understand the
increase in daily active
users over a period time
Sample Dashboard…
Steps for creating Excel Dashboard…
Excel Dashboard in 8 Simple Steps
Start with a Clean Dataset.
Format data as a Table.
Create the first Pivot table and Pivot Charts.
Create Multiple Pivot table and Pivot Charts for other variables.
Assemble the Excel dashboard.
Add Slicers & Timelines.
Connect Slicers to data.
Update the Excel Dashboard.
Excel Dashboard in 8 Simple Steps
Start with a Clean Dataset.
Format data as a Table.
Create the first Pivot table and Pivot Charts.
Create Multiple Pivot table and Pivot Charts for other variables.
Assemble the Excel dashboard.
Add Slicers & Timelines.
Connect Slicers to data.
Update the Excel Dashboard.
Points to Remember in Dashboard…
Remember, human brains are more perceptible towards images and colours rather than
numbers
For one report, either show numbered report OR Chart/Graph, never both
For too big report, Use conditional formatting to highlight important numbers/facts
Always include one summary report
Dashboard doesn’t necessarily need to be only in one sheet
No redundant information to be on dashboard
Identify what you want to show
Think from user’s perspective
Keep it interactive
Write conclusions in the ‘Text boxes’ in the separate sheet, you can link respective
charts to them.
Place the dashboard elements as per your storyline, add/delete more elements if
needed.
Keep it Simple
Practice your script before you present.
Remember, human brains are more perceptible towards images and colours rather than
numbers
For one report, either show numbered report OR Chart/Graph, never both
For too big report, Use conditional formatting to highlight important numbers/facts
Always include one summary report
Dashboard doesn’t necessarily need to be only in one sheet
No redundant information to be on dashboard
Identify what you want to show
Think from user’s perspective
Keep it interactive
Write conclusions in the ‘Text boxes’ in the separate sheet, you can link respective
charts to them.
Place the dashboard elements as per your storyline, add/delete more elements if
needed.
Keep it Simple
Practice your script before you present.
MS Excel
Day 11 –Introduction to
Power Query
Day 11 –Introduction to
Power Query
What is Power Query…?
PowerQueryisanexceltoolusedtoimportdatafromdifferentsources,transform(change)
itasrequired,andreturnarefineddatasetintheworkbook.
Everychangemadetothedataisrecordedandsavedasastep.
Infuture,wheneverthedatasourceisupdated,thesamechangesareperformed
automaticallywiththeclickofthe“refresh”button.
Forexample,anorganizationhas180filescontainingthepurchasesmadeinthelast15
years.Toconsolidateandanalyzethesenumbers,eitherofthefollowingstepscanbe
performed:
Openthedifferentfilesandcopy-pastetheentiredatainoneworksheet.Applythe
variousfunctionsofExceltoconvertthedataintomeaningfulreports.
UsePowerQuerytoimportdatafromthedifferentfiles.Setupaquerywhichconsists
ofmakingstep-by-stepchangestothedata.Loadthetransformeddatainaworksheet
tocreatereports.
Iftheorganizationfollowsthepointer“a,”itwillhavetoperformalotofmanualwork.
Thesetasksareoftentediousandrepetitive.
However,ifpointer“b”isfollowed,thetransformationsareperformedautomaticallyevery
timedataisupdated.
Thissavesalotoftimeandspeedsuptheprocessofconsolidatingexceldata.
PowerQueryisanexceltoolusedtoimportdatafromdifferentsources,transform(change)
itasrequired,andreturnarefineddatasetintheworkbook.
Everychangemadetothedataisrecordedandsavedasastep.
Infuture,wheneverthedatasourceisupdated,thesamechangesareperformed
automaticallywiththeclickofthe“refresh”button.
Forexample,anorganizationhas180filescontainingthepurchasesmadeinthelast15
years.Toconsolidateandanalyzethesenumbers,eitherofthefollowingstepscanbe
performed:
Openthedifferentfilesandcopy-pastetheentiredatainoneworksheet.Applythe
variousfunctionsofExceltoconvertthedataintomeaningfulreports.
UsePowerQuerytoimportdatafromthedifferentfiles.Setupaquerywhichconsists
ofmakingstep-by-stepchangestothedata.Loadthetransformeddatainaworksheet
tocreatereports.
Iftheorganizationfollowsthepointer“a,”itwillhavetoperformalotofmanualwork.
Thesetasksareoftentediousandrepetitive.
However,ifpointer“b”isfollowed,thetransformationsareperformedautomaticallyevery
timedataisupdated.
Thissavesalotoftimeandspeedsuptheprocessofconsolidatingexceldata.
PowerQueryinexcelperformstheextract,transform,andloadoperations
(ETL)onadataset.
Alltransformations(stepsorchanges)appliedtothedataarecollectively
knownasaquery.
EachstepperformedisrecordedandwritteninacodeofMlanguage.
Byperformingthesetransformations,thedataissaidtobeshaped.
ThemajoradvantageofPowerQueryinexcelisthatitisafastandefficient
wayofworkingonlargedatasets.
Besides,itisreusableasthesamequerycanbeusedagainonanewdataset.
Moreover,withjustafewclicks,onecanhaveaccesstocleansedandsorted
data.
PowerQuerycanbeinstalledasanadd-ininExcel2010and2013.
InExcel2016andthesubsequentversions,PowerQueryisabuilt-inexcel
feature.
Itcanbeaccessedfromthe“getdata”drop-down(inthe“getandtransform
data”group)oftheDatatabofExcel.
What is Power Query…?
(ETL)onadataset.
Alltransformations(stepsorchanges)appliedtothedataarecollectively
knownasaquery.
EachstepperformedisrecordedandwritteninacodeofMlanguage.
Byperformingthesetransformations,thedataissaidtobeshaped.
ThemajoradvantageofPowerQueryinexcelisthatitisafastandefficient
wayofworkingonlargedatasets.
Besides,itisreusableasthesamequerycanbeusedagainonanewdataset.
Moreover,withjustafewclicks,onecanhaveaccesstocleansedandsorted
data.
PowerQuerycanbeinstalledasanadd-ininExcel2010and2013.
InExcel2016andthesubsequentversions,PowerQueryisabuilt-inexcel
feature.
Itcanbeaccessedfromthe“getdata”drop-down(inthe“getandtransform
data”group)oftheDatatabofExcel.
What is Power Query…?
Using Power Query in Excel…
TousePowerQueryinExcel,thefollowingstepsneedtobeperformed:
Importdata:Importdatafromthedifferentsources.Thedatasourcecan
beatextfile,Excelworkbook,web,pdf,andsoon.WithPowerQuery,
onecanworkwithdatafromanysourcehavinganysizeandshape.
Transformdata:Change,sortandshapedataaspertherequirements.
Forinstance,onecandeleteorinsertarowand/orcolumn,replacea
missingvalue,deleteaduplicateentry,filteracolumn,andsoon.These
changesarerecordedasaqueryinthesequenceinwhichtheyare
appliedtothedata.
Consolidatedata:Consolidateorcombinethedatafromthedifferent
sources.Onceintegrated,aconsolidateddatabasecanbegenerated.The
mergingandappendingofqueriesarecarriedoutatthisstage.
Loaddata:Loadthedataonaworksheetonceithasbeentransformed
andconsolidated.Loadingthedatahelpsreturnanoutputinthe
workbook.Theoutputcanbeintheformofatable,pivotchartorapivot
table.Priortoloading,onecanpreviewthedatatoensureitisonthe
righttrack.
TousePowerQueryinExcel,thefollowingstepsneedtobeperformed:
Importdata:Importdatafromthedifferentsources.Thedatasourcecan
beatextfile,Excelworkbook,web,pdf,andsoon.WithPowerQuery,
onecanworkwithdatafromanysourcehavinganysizeandshape.
Transformdata:Change,sortandshapedataaspertherequirements.
Forinstance,onecandeleteorinsertarowand/orcolumn,replacea
missingvalue,deleteaduplicateentry,filteracolumn,andsoon.These
changesarerecordedasaqueryinthesequenceinwhichtheyare
appliedtothedata.
Consolidatedata:Consolidateorcombinethedatafromthedifferent
sources.Onceintegrated,aconsolidateddatabasecanbegenerated.The
mergingandappendingofqueriesarecarriedoutatthisstage.
Loaddata:Loadthedataonaworksheetonceithasbeentransformed
andconsolidated.Loadingthedatahelpsreturnanoutputinthe
workbook.Theoutputcanbeintheformofatable,pivotchartorapivot
table.Priortoloading,onecanpreviewthedatatoensureitisonthe
righttrack.
Power Query Editor…
PowerQueryhasitsowneditorwhereyoucangetthedata,performallthe
stepstocreatequeries,andthenloadittotheworksheet.
Toopenthepowerqueryeditor,youneedtogototheDataTabandinthe
Get&Transform➜GetData➜LaunchPowerQueryEditor.
PowerQueryhasitsowneditorwhereyoucangetthedata,performallthe
stepstocreatequeries,andthenloadittotheworksheet.
Toopenthepowerqueryeditor,youneedtogototheDataTabandinthe
Get&Transform➜GetData➜LaunchPowerQueryEditor.
Data Sources for Power Query…
Thebestpartofthepowerqueryisyouhavethe
optiontogetdatafrommultiplesourcesand
transformthatdataandthenloaditintothe
worksheet.
WhenyouclickontheGetDataintheGET&
TRANSFORMyoucanseethecompletelistof
datasourcesthatyoucangetdataloadintothe
editor.
Thebestpartofthepowerqueryisyouhavethe
optiontogetdatafrommultiplesourcesand
transformthatdataandthenloaditintothe
worksheet.
WhenyouclickontheGetDataintheGET&
TRANSFORMyoucanseethecompletelistof
datasourcesthatyoucangetdataloadintothe
editor.
Different Data Sources…
FromTable/Range:Withthisoption,youcanloaddataintothepowerquery
editordirectlyfromtheactiveworksheet.
FromWorkbook:Fromadifferentworkbookthatyouhaveonyourcomputer.
Youjustneedtolocatethatfileusinganopendialogboxanditwillgetdata
fromthatfileautomatically.
FromText/CSV:Getdatafromatextfileoracomma-separatedfileandthen
youcanloaditintotheworksheet.
FromFolder:Ittakesallthefilesfromthefolderandloaddatafromthem
intothepowerqueryeditor.
FromWeb:Withthisoption,yougetdatafromawebaddress,imagineyou
haveaFilethatisstoredontheweboryouhaveawebpagefromwhereyou
needtogetthedata.
FromTable/Range:Withthisoption,youcanloaddataintothepowerquery
editordirectlyfromtheactiveworksheet.
FromWorkbook:Fromadifferentworkbookthatyouhaveonyourcomputer.
Youjustneedtolocatethatfileusinganopendialogboxanditwillgetdata
fromthatfileautomatically.
FromText/CSV:Getdatafromatextfileoracomma-separatedfileandthen
youcanloaditintotheworksheet.
FromFolder:Ittakesallthefilesfromthefolderandloaddatafromthem
intothepowerqueryeditor.
FromWeb:Withthisoption,yougetdatafromawebaddress,imagineyou
haveaFilethatisstoredontheweboryouhaveawebpagefromwhereyou
needtogetthedata.
Loading Data in Power Query…
Youwillbeloadingdatadirectlyfromtheworksheet,soyouneedtoopenthefilefirstandthen
followthebelowsteps:
First,applyanExceltabletothedata(Evenifyoudon’tdoitExcelwilldoitforyoubefore
loadingdataintoPQeditor).
Now,selectacellfromthetableandclickonthe“FromTable/Range”(DataTabGet&Transform).
Onceyouclickonthebutton,ExcelconfirmstherangeofdatatoapplyanExceltabletoit.
Atthispoint,youhavethedataintothepowerqueryeditor,anditlookssomethingliketheimage.
Youwillbeloadingdatadirectlyfromtheworksheet,soyouneedtoopenthefilefirstandthen
followthebelowsteps:
First,applyanExceltabletothedata(Evenifyoudon’tdoitExcelwilldoitforyoubefore
loadingdataintoPQeditor).
Now,selectacellfromthetableandclickonthe“FromTable/Range”(DataTabGet&Transform).
Onceyouclickonthebutton,ExcelconfirmstherangeofdatatoapplyanExceltabletoit.
Atthispoint,youhavethedataintothepowerqueryeditor,anditlookssomethingliketheimage.
Loading Data in Power Query…
Hereyoucansee:
IntheFormulabar,PQhasgeneratedtheMcodeforthetableyouhavejustloadedinto
theeditor.
Ontheleftsideoftheeditor,youhavethequeriespanewhereyouhavethelistofthe
queries.
Ontherightside,inthequerysettings,youhavethesectioncalled“AppliedSteps”where
youhaveallthestepslisted.Note:Youmustbethinkingthatyouhaven’tperformedany
“ChangedType”butthere’sastepcalled“ChangedType”isthere.Letmetellyou
theSMARTNESSofPOWERQUERYwhenyouloaddataintotheeditoritchecksandapplies
thecorrectdatatypesforallthecolumnsautomatically.
Hereyoucansee:
IntheFormulabar,PQhasgeneratedtheMcodeforthetableyouhavejustloadedinto
theeditor.
Ontheleftsideoftheeditor,youhavethequeriespanewhereyouhavethelistofthe
queries.
Ontherightside,inthequerysettings,youhavethesectioncalled“AppliedSteps”where
youhaveallthestepslisted.Note:Youmustbethinkingthatyouhaven’tperformedany
“ChangedType”butthere’sastepcalled“ChangedType”isthere.Letmetellyou
theSMARTNESSofPOWERQUERYwhenyouloaddataintotheeditoritchecksandapplies
thecorrectdatatypesforallthecolumnsautomatically.
Power Query Editor…
Ribbon…
1. Ribbon
Let’s look at all the available tabs:
File:From the file tab, you can load the data, discard the editor, and open
the query settings.
Home: In the HOME Tab, you have options to manage the loaded data, like,
delete and move columns and rows.
Transform: This tab has all the options which you need to transform and
clean the data, like merge columns, transpose, etc.
Add Column: Here you have the option to add new columns to the data you
have in the power editor.
View: From this tab, you can make changes to the view for the power query
editor and data loaded.
1. Ribbon
Let’s look at all the available tabs:
File:From the file tab, you can load the data, discard the editor, and open
the query settings.
Home: In the HOME Tab, you have options to manage the loaded data, like,
delete and move columns and rows.
Transform: This tab has all the options which you need to transform and
clean the data, like merge columns, transpose, etc.
Add Column: Here you have the option to add new columns to the data you
have in the power editor.
View: From this tab, you can make changes to the view for the power query
editor and data loaded.
Applied Steps…
2.AppliedSteps
Ontherightsideoftheeditor,youhaveaquerysettingpanewhichincludesthenameofthequeryandallthe
appliedstepsinasequence.
Whenyouright-clickonastepyouhavealistofoptionsthatyoucanperform,like,rename,delete,edit,move
upordown,etc.andwhenyouclickonastep,theeditorwilltakeyoutothetransformationdoneonthatstep.
LookattheimagewhereyouhavethetotalfivestepsappliedandwhenIclickonthe4thstepittakesmeto
stepfour’stransformationwherethecolumnsnamehasn’tchanged.
2.AppliedSteps
Ontherightsideoftheeditor,youhaveaquerysettingpanewhichincludesthenameofthequeryandallthe
appliedstepsinasequence.
Whenyouright-clickonastepyouhavealistofoptionsthatyoucanperform,like,rename,delete,edit,move
upordown,etc.andwhenyouclickonastep,theeditorwilltakeyoutothetransformationdoneonthatstep.
LookattheimagewhereyouhavethetotalfivestepsappliedandwhenIclickonthe4thstepittakesmeto
stepfour’stransformationwherethecolumnsnamehasn’tchanged.
Queries…
3.Queries
Thequeriespaneontheleftsidelistsallthequeriesyouhaveintheworkbookright
now.It’sbasicallyoneplacewhereyoucanmanageallthequeries.
Whenyouright-clickonaquerynameyoucanseealltheoptionsthatyoucanuse
(copy,delete,duplicate,etc.)
Youcanalsocreateanewquerybysimplyrightclickontheblankspaceonthequeries
paneandthenselecttheoptionforthedatasource.
3.Queries
Thequeriespaneontheleftsidelistsallthequeriesyouhaveintheworkbookright
now.It’sbasicallyoneplacewhereyoucanmanageallthequeries.
Whenyouright-clickonaquerynameyoucanseealltheoptionsthatyoucanuse
(copy,delete,duplicate,etc.)
Youcanalsocreateanewquerybysimplyrightclickontheblankspaceonthequeries
paneandthenselecttheoptionforthedatasource.
Formula Bar…
4.FormulaBar
AsIsaid,wheneveryouapplyastepintheeditoritgeneratesMcodeforthat
step,andyoucanseethatcodeintheformulabar.Youcansimplyclickon
theformulabartoeditthecode.
OnceyoulearntouseMcodeyoucanalsocreatestepbywritingthecode
andsimplyclickingonthe“FX”buttontoenteracustomstep.
4.FormulaBar
AsIsaid,wheneveryouapplyastepintheeditoritgeneratesMcodeforthat
step,andyoucanseethatcodeintheformulabar.Youcansimplyclickon
theformulabartoeditthecode.
OnceyoulearntouseMcodeyoucanalsocreatestepbywritingthecode
andsimplyclickingonthe“FX”buttontoenteracustomstep.
Data Preview…
5.DataPreview
ThedatapreviewarealookslikeanExcelworksheetbutthere’salittledifferentthananormalworksheet
whereyoucaneditacellordatadirectly.Whenyouloaddataintotheeditor(wewilldoitinawhile)it
showsallthecolumnswiththeheaderswiththecolumnsnameandthenrowswithdata.
Atthetopofeachcolumn,youcanseethedatatypeofthedatainthecolumn.Whenyouloaddataintothe
editorthepowerqueryappliestherightdatatype(almosteverytime)toeachcolumnautomatically.
Youcanclickonthetopleftbuttononthecolumnheadertochangethedatatypeappliedtothecolumn.It
hasalistofallthedatatypesfromwhereyoucan.
5.DataPreview
ThedatapreviewarealookslikeanExcelworksheetbutthere’salittledifferentthananormalworksheet
whereyoucaneditacellordatadirectly.Whenyouloaddataintotheeditor(wewilldoitinawhile)it
showsallthecolumnswiththeheaderswiththecolumnsnameandthenrowswithdata.
Atthetopofeachcolumn,youcanseethedatatypeofthedatainthecolumn.Whenyouloaddataintothe
editorthepowerqueryappliestherightdatatype(almosteverytime)toeachcolumnautomatically.
Youcanclickonthetopleftbuttononthecolumnheadertochangethedatatypeappliedtothecolumn.It
hasalistofallthedatatypesfromwhereyoucan.
Andontheleftsideofthecolumnheaderthereyouhavethefilterbuttonwhichyou
canusetofiltervaluesfromthecolumn.
Note:Whenyoufiltervaluesfromacolumn,thepowerquerytakesitasonestepand
listsitintheappliedsteps.
Ifyouright-clickontheheaderofthecolumnyoucanseethatthereisamenuthat
includesalistoftheoptionswhichyoucanusetotransformthedataanduseanyof
theoptionsandPQstoresitasastepintheappliedsteps.
Data Preview…
canusetofiltervaluesfromthecolumn.
Note:Whenyoufiltervaluesfromacolumn,thepowerquerytakesitasonestepand
listsitintheappliedsteps.
Ifyouright-clickontheheaderofthecolumnyoucanseethatthereisamenuthat
includesalistoftheoptionswhichyoucanusetotransformthedataanduseanyof
theoptionsandPQstoresitasastepintheappliedsteps.
Data Preview…
MS Excel
Day 12 –Data Manipulations using
Power Query
Day 12 –Data Manipulations using
Power Query
Key Data manipulations in PQ…
1. Replace Values
2. Sort Data
3. Remove Columns
4. Split Column
5. Rename a Column
6. Duplicate Column
7. Transpose Column or Row
8. Replace/Remove Errors
9. Change Data Type
10. Add Column from Examples
11. Change Case
12. Trim and Clean
13. Add Prefix/Suffix
14. Extract Values
15. Only Date or Time
16. Combine Date and Time
17. Rounding Numbers
18. Calculations
19. Group by
20. Remove Negative Values
1. Replace Values
2. Sort Data
3. Remove Columns
4. Split Column
5. Rename a Column
6. Duplicate Column
7. Transpose Column or Row
8. Replace/Remove Errors
9. Change Data Type
10. Add Column from Examples
11. Change Case
12. Trim and Clean
13. Add Prefix/Suffix
14. Extract Values
15. Only Date or Time
16. Combine Date and Time
17. Rounding Numbers
18. Calculations
19. Group by
20. Remove Negative Values
Replace Values…
Youhavealistofvalues,andyouwanttoreplaceavalueorsomevalueswithsomethingelse.Well,withthe
helpofthepowerqueryyoucancreateaqueryandreplacesthosevalues,innotime.
Inthebelowlist,youneedtoreplacemyname“Puneet”with“Punit”.
First,editthelistinthepowerqueryeditor.
Afterthat,inthepowerqueryeditor,goto“TransformTab”andclick“ReplaceValues”.
Now,in“ValuetoFind”,enter“Puneet”andin“ReplaceWith”enter“Punit”andafterthat,clickOK.
OnceyouclickOKallthevaluesgetreplacedwiththenewvaluesandnow,clickon“CloseandLoad”toload
dataintheworksheet.
Youhavealistofvalues,andyouwanttoreplaceavalueorsomevalueswithsomethingelse.Well,withthe
helpofthepowerqueryyoucancreateaqueryandreplacesthosevalues,innotime.
Inthebelowlist,youneedtoreplacemyname“Puneet”with“Punit”.
First,editthelistinthepowerqueryeditor.
Afterthat,inthepowerqueryeditor,goto“TransformTab”andclick“ReplaceValues”.
Now,in“ValuetoFind”,enter“Puneet”andin“ReplaceWith”enter“Punit”andafterthat,clickOK.
OnceyouclickOKallthevaluesgetreplacedwiththenewvaluesandnow,clickon“CloseandLoad”toload
dataintheworksheet.
Sort Data…
Just like normal sorting, you can sort data by using power query and I’m using the same
name list which you have used in the above example.
First, load data in the power query editor.
In the Home tab, you have two sorting buttons (Ascending and Descending).
Click on any of these buttons to sort.
Remove Negative Values…
Just right click on a column and go to transform option and then click on “Absolute
value”.
This instantly removes all the negative signs from the values.
Just like normal sorting, you can sort data by using power query and I’m using the same
name list which you have used in the above example.
First, load data in the power query editor.
In the Home tab, you have two sorting buttons (Ascending and Descending).
Click on any of these buttons to sort.
Remove Negative Values…
Just right click on a column and go to transform option and then click on “Absolute
value”.
This instantly removes all the negative signs from the values.
Remove Columns…
Let’ssayyougotdatafromsomewhereandyouneedtodeletesomecolumns
fromit.Thethingis,youhavetodeletethosecolumnseverytimeyouadd
newdata,right?But,powerquerycantakecareofthis.
Selectthecolumnormultiplecolumnsthatyouwanttodelete.
Now,right-clickandselect“Remove”.
QuickTip:There’salsoanoptionto“RemoveOtherColumns”whereyoucan
deletealltheunselectedcolumns.
Let’ssayyougotdatafromsomewhereandyouneedtodeletesomecolumns
fromit.Thethingis,youhavetodeletethosecolumnseverytimeyouadd
newdata,right?But,powerquerycantakecareofthis.
Selectthecolumnormultiplecolumnsthatyouwanttodelete.
Now,right-clickandselect“Remove”.
QuickTip:There’salsoanoptionto“RemoveOtherColumns”whereyoucan
deletealltheunselectedcolumns.
Split Columns…
Just like the text to column option, you have “Split Column” in power query.
Select the column and go to the Home Tab ➜Transform ➜Split Column ➜By Delimiter.
Select the custom from the drop-down and enter “-” into it.
Now, here you have three different options to split a column.
Left-mostDelimiter
Right-most Delimiter
Each occurrence of the delimiter
If you have only one delimiter in a cell, all three will work in the same way, but if you have more
than one delimiter then you have to choose accordingly.
Just like the text to column option, you have “Split Column” in power query.
Select the column and go to the Home Tab ➜Transform ➜Split Column ➜By Delimiter.
Select the custom from the drop-down and enter “-” into it.
Now, here you have three different options to split a column.
Left-mostDelimiter
Right-most Delimiter
Each occurrence of the delimiter
If you have only one delimiter in a cell, all three will work in the same way, but if you have more
than one delimiter then you have to choose accordingly.
Rename & Duplicate a Column…
You can simply rename a column by right click and then click on the
“Rename”.
Quick Tip:Let say you have a query for renaming a column and someone else
rename it by mistake. You can restore that name just with a click.
In Power Query, there is a simple option to create a duplicate column. All you
need to do is right-click on the column for which you want to create a
duplicate column and then click on “Duplicate Column”.
You can simply rename a column by right click and then click on the
“Rename”.
Quick Tip:Let say you have a query for renaming a column and someone else
rename it by mistake. You can restore that name just with a click.
In Power Query, there is a simple option to create a duplicate column. All you
need to do is right-click on the column for which you want to create a
duplicate column and then click on “Duplicate Column”.
Transpose Column or Row…
In the power query, transposing is a cup of cake. Yes, just one click.
Once you load data into the power query editor, you just need to select the
column(s) or row(s).
Go to Transform Tab ➜Table ➜Transpose.
Change Data Type…
You have data in a column but it’s not in the right format. So, every time you need to
change its format.
First, edit data into the power query editor.
After that, select the column and go to the Transform Tab.
Now, from data type select the “Date” as a type.
In the power query, transposing is a cup of cake. Yes, just one click.
Once you load data into the power query editor, you just need to select the
column(s) or row(s).
Go to Transform Tab ➜Table ➜Transpose.
Change Data Type…
You have data in a column but it’s not in the right format. So, every time you need to
change its format.
First, edit data into the power query editor.
After that, select the column and go to the Transform Tab.
Now, from data type select the “Date” as a type.
Replace/Remove Errors…
Normally for replacing or removing errors in Excel you can use find and
replace option or a VBA code. But in power query, it’s a whole lot easier. Look
at the below column where you have some errors and you can remove as well
as replace them.
When you right-click on the column, you’ll have both of the options.
Replace Errors
Remove Errors
Normally for replacing or removing errors in Excel you can use find and
replace option or a VBA code. But in power query, it’s a whole lot easier. Look
at the below column where you have some errors and you can remove as well
as replace them.
When you right-click on the column, you’ll have both of the options.
Replace Errors
Remove Errors
Add Column from Examples…
Inthepowerquery,thereisanoptiontoaddasamplecolumnwhichisnotactuallyasamplerelatedtothe
currentcolumn.
Imagineyouneeddaynamesfromadatecolumn.Insteadofusingaformulaoranyotheroption,youcanuse,
youcanusethe“AddColumnfromExamples”.
Here’showtodothis:
Right-clickonacolumnandclickon“AddColumnfromExamples”.
Hereyou’llgetablankcolumn.Clickonthefirstcellofthecolumntogetthelistofvaluesyoucaninsert.
Select“DayofWeekNamefromDate”andclickOK.
Inthepowerquery,thereisanoptiontoaddasamplecolumnwhichisnotactuallyasamplerelatedtothe
currentcolumn.
Imagineyouneeddaynamesfromadatecolumn.Insteadofusingaformulaoranyotheroption,youcanuse,
youcanusethe“AddColumnfromExamples”.
Here’showtodothis:
Right-clickonacolumnandclickon“AddColumnfromExamples”.
Hereyou’llgetablankcolumn.Clickonthefirstcellofthecolumntogetthelistofvaluesyoucaninsert.
Select“DayofWeekNamefromDate”andclickOK.
Change Case…
You have the following options for changing the case of text in power query.
Lower Case
Upper Case
Capitalize Each Word
You can do it by right click on a column and select any of the above three options. Or,
go to the Transform Tab ➜Text Column ➜Format.
Trim & Clean…
To clear data ordelete unwanted spacesyou can use TRIM and CLEAN options
in power query. Steps are simple:
Right-click on a column or select all the columns if you have multiple
columns.
Go to Transform Tab ➜Text Column ➜Format.
TRIM: To remove trailing and leading whitespaces from a cell.
CLEAN: To remove non-printable characters from a cell.
You have the following options for changing the case of text in power query.
Lower Case
Upper Case
Capitalize Each Word
You can do it by right click on a column and select any of the above three options. Or,
go to the Transform Tab ➜Text Column ➜Format.
Trim & Clean…
To clear data ordelete unwanted spacesyou can use TRIM and CLEAN options
in power query. Steps are simple:
Right-click on a column or select all the columns if you have multiple
columns.
Go to Transform Tab ➜Text Column ➜Format.
TRIM: To remove trailing and leading whitespaces from a cell.
CLEAN: To remove non-printable characters from a cell.
Add Prefix/Suffix…
So you have a list of values and from this list, you want to add a Prefix/Suffix
in each cell. In Excel, you can use the concatenate method but in power
query, there is a simple to use option for both.
First, select the column where you need to add Prefix/Suffix.
Then, go to the Transform Tab ➜Text Column ➜Format ➜Add Prefix/ Add
Suffix.
Once you click on one of the options, you’ll get a dialog box to enter text.
And after entering the text, click OK.
So you have a list of values and from this list, you want to add a Prefix/Suffix
in each cell. In Excel, you can use the concatenate method but in power
query, there is a simple to use option for both.
First, select the column where you need to add Prefix/Suffix.
Then, go to the Transform Tab ➜Text Column ➜Format ➜Add Prefix/ Add
Suffix.
Once you click on one of the options, you’ll get a dialog box to enter text.
And after entering the text, click OK.
Extract Values…
If you are a formula savvy, then I’m sure you agree with me that extracting
text or number from a cell requires to combine different functions.
But power query has solved a lot of these things in a good way. You have
seven ways to extract values from a cell.
Only Date or Time…
It happens a lot of times that you have date and time, both in a single cell,
but needs one of them.
Select the column where you have the date and time combined.
If you want:
Date:Right Click ➜Transform ➜Date Only.
Time:Right Click ➜Transform ➜Time Only.
If you are a formula savvy, then I’m sure you agree with me that extracting
text or number from a cell requires to combine different functions.
But power query has solved a lot of these things in a good way. You have
seven ways to extract values from a cell.
Only Date or Time…
It happens a lot of times that you have date and time, both in a single cell,
but needs one of them.
Select the column where you have the date and time combined.
If you want:
Date:Right Click ➜Transform ➜Date Only.
Time:Right Click ➜Transform ➜Time Only.
Combine Date & Time…
Now you know how to separate date and time. But the next you need to know
how to combine them.
First, select the date column and click on the “Date Only” option.
After that, select both columns (Date and Time) and go to the transform tab
and from the “Date and Time Column” Group go to Date and click “Combine
Date and Time”.
Now you know how to separate date and time. But the next you need to know
how to combine them.
First, select the date column and click on the “Date Only” option.
After that, select both columns (Date and Time) and go to the transform tab
and from the “Date and Time Column” Group go to Date and click “Combine
Date and Time”.
Rounding Numbers…
Here are the following options which you have for rounding numbers.
Round Down:To round down a number.
Round Up:To round up a number.
Round:You can choose up to how my decimals you can round.
Here are the following options which you have for rounding numbers.
Round Down:To round down a number.
Round Up:To round up a number.
Round:You can choose up to how my decimals you can round.
Note:When you select the “Round” option you need to enter the number of
decimals to round.
Here are the following options which you have for rounding numbers.
Round Down:To round down a number.
Round Up:To round up a number.
Round:You can choose up to how my decimals you can round.
Here are the following options which you have for rounding numbers.
Round Down:To round down a number.
Round Up:To round up a number.
Round:You can choose up to how my decimals you can round.
Note:When you select the “Round” option you need to enter the number of
decimals to round.
Calculations…
There are options that you can use to perform calculations (a lot of). You can
find all these options on the Transform Tab (in Number Column group).
Basic
Statistics
Scientific
Trigonometry
Rounding
Information
To perform any of this calculation you need to select the column and then the
option.
There are options that you can use to perform calculations (a lot of). You can
find all these options on the Transform Tab (in Number Column group).
Basic
Statistics
Scientific
Trigonometry
Rounding
Information
To perform any of this calculation you need to select the column and then the
option.
Group by…
Let’ssayyouhavealargedatasetandyouwanttocreateasummarytable.
IntheTransformtab,clickonthe‘Groupby“buttonandyou’llgetadialogbox.
Now,fromthisdialogboxselectthecolumnwithwhichyouwanttogroupandafter
that,addaname,selecttheoperation,andthecolumnwhereyouhavevalues.
Intheend,clickOK.
Note:Therearealsosomeadvancedoptionsinthe“Groupby”optionwhichyoucan
usetocreateamulti-levelgrouptable.
Let’ssayyouhavealargedatasetandyouwanttocreateasummarytable.
IntheTransformtab,clickonthe‘Groupby“buttonandyou’llgetadialogbox.
Now,fromthisdialogboxselectthecolumnwithwhichyouwanttogroupandafter
that,addaname,selecttheoperation,andthecolumnwhereyouhavevalues.
Intheend,clickOK.
Note:Therearealsosomeadvancedoptionsinthe“Groupby”optionwhichyoucan
usetocreateamulti-levelgrouptable.
Load Data Back to Worksheet…
Once you transform your data, you can load it to the worksheet and use it for further
analysis. On the home tab there is a button called “Close and Load” when you click on
it you get a drop-down which has options further:
Close and Load
Close and Load To
Once you click on the button, it will show the following options:
Select how you want to view this data in your worksheet.
Just select the table option and new worksheet and don’t tick mark the data model and
click OK.
The moment you click OK, it adds a new worksheet with the data.
Once you transform your data, you can load it to the worksheet and use it for further
analysis. On the home tab there is a button called “Close and Load” when you click on
it you get a drop-down which has options further:
Close and Load
Close and Load To
Once you click on the button, it will show the following options:
Select how you want to view this data in your worksheet.
Just select the table option and new worksheet and don’t tick mark the data model and
click OK.
The moment you click OK, it adds a new worksheet with the data.
Auto Refresh a Query…
When you create a query, you can make it auto-refresh (you can set a timer).
On the Data tab, click on “Queries & Connections” and you’ll get the Queries
and Connection pane on the right side of the window.
Now, right-click on the query and tick mark “Refresh every” and enter the
minutes.
When you create a query, you can make it auto-refresh (you can set a timer).
On the Data tab, click on “Queries & Connections” and you’ll get the Queries
and Connection pane on the right side of the window.
Now, right-click on the query and tick mark “Refresh every” and enter the
minutes.
Functions &Formulas in PQ…
JustlikeyoucanusefunctionsandformulasinExcelworksheet,thepowerqueryhasitsown
listoffunctionsthatyoucanuse.Thebasicsoffunctionandformulasinpowerqueryarethe
sameasExcel’sworksheetfunctions.
InPQ,youneedtoaddanewcustomcolumntoaddafunctionoraformula.Let’stakean
example:Inthedata(alreadyinthePQeditor)youhavethefirstnameandlastname
Imagineyouneedtomergebothnamesandcreateacolumnforthefullname.Inthiscase,you
canenterasimpleformulatoconcatenatenamesfrombothcolumns.
First,gototheAddColumntabandclickonthe“CustomColumn”.
Nowinthecustomcolumndialogbox,enterthenameofthenewcolumn“FullName”or
anythingyouwanttonamethenewcolumn.
Thecustomcolumnformulaistheplacewhereyouneedtoentertheformula.Soenterthe
belowformulainit:[FirstName]&""&[LastName]
Whenyouenteraformulainthe“customcolumnformula”,PQverifytheformulathatyouhave
enteredandshowsamessage“Nosyntaxerrorhavebeendetected”andifthere’sanerrorit
willshowanerrormessagebasedonthetypeoftheerror.
Onceyouentertheformulaandthatformuladoesn’thaveanyerrorsinit,simplypressOK.
Nowyouhaveanewcolumnattheendofthedatawhichhasvaluesfromtwocolumns(first
nameandthelastname).
JustlikeyoucanusefunctionsandformulasinExcelworksheet,thepowerqueryhasitsown
listoffunctionsthatyoucanuse.Thebasicsoffunctionandformulasinpowerqueryarethe
sameasExcel’sworksheetfunctions.
InPQ,youneedtoaddanewcustomcolumntoaddafunctionoraformula.Let’stakean
example:Inthedata(alreadyinthePQeditor)youhavethefirstnameandlastname
Imagineyouneedtomergebothnamesandcreateacolumnforthefullname.Inthiscase,you
canenterasimpleformulatoconcatenatenamesfrombothcolumns.
First,gototheAddColumntabandclickonthe“CustomColumn”.
Nowinthecustomcolumndialogbox,enterthenameofthenewcolumn“FullName”or
anythingyouwanttonamethenewcolumn.
Thecustomcolumnformulaistheplacewhereyouneedtoentertheformula.Soenterthe
belowformulainit:[FirstName]&""&[LastName]
Whenyouenteraformulainthe“customcolumnformula”,PQverifytheformulathatyouhave
enteredandshowsamessage“Nosyntaxerrorhavebeendetected”andifthere’sanerrorit
willshowanerrormessagebasedonthetypeoftheerror.
Onceyouentertheformulaandthatformuladoesn’thaveanyerrorsinit,simplypressOK.
Nowyouhaveanewcolumnattheendofthedatawhichhasvaluesfromtwocolumns(first
nameandthelastname).
Functions &Formulas in PQ…
Edit Query in PQ…
Ifyouwanttomakesomechangesinthequerywhichisalreadyinyourworkbookyoucansimplyedititandthenmake
thosechanges.OntheDatatab,there’sabuttonnamedQueriesandConnections.
Whenyouclickonthisbutton,itopensapaneontherightsidethatlistsallthequeriesthatyouhaveinthecurrent
workbook.
Youcanright-clickonthequerynameandselecteditandyouwillgetitinthepowerqueryeditortoedit.
Whenyoueditaquery,youcanseethatallthestepswhichyouhaveperformedearlierarelistedinthe“Applied
Steps”thatyoucanalsoeditoryoucanperformnewsteps.
Andonceyouaredonewithyourchangesyoucansimplyclickonthe“Close&Load”button.
Ifyouwanttomakesomechangesinthequerywhichisalreadyinyourworkbookyoucansimplyedititandthenmake
thosechanges.OntheDatatab,there’sabuttonnamedQueriesandConnections.
Whenyouclickonthisbutton,itopensapaneontherightsidethatlistsallthequeriesthatyouhaveinthecurrent
workbook.
Youcanright-clickonthequerynameandselecteditandyouwillgetitinthepowerqueryeditortoedit.
Whenyoueditaquery,youcanseethatallthestepswhichyouhaveperformedearlierarelistedinthe“Applied
Steps”thatyoucanalsoeditoryoucanperformnewsteps.
Andonceyouaredonewithyourchangesyoucansimplyclickonthe“Close&Load”button.
Export & Import Connections…
Ifyouhaveaconnectionwhichyouhaveusedforaqueryandnowyou
wanttosharethatconnectionwithsomeoneelse,youcanexportthat
connectionasanodcfile.
Onthequerytable,there’sabuttoncalled“ExportConnection”and
whenyouclickonit,itallowsyoutosavethatquery’sconnectionin
yoursystem.
Andifyouwanttoimportaconnectionthatissharedbysomeone
else,youcansimplygototheDatatabandintheGet&
Transformclickontheexistingconnections.
Andthenclickonthe“BrowseforMore”buttonfromwhereyoucan
locatetheconnectionfilewhichhasbeensharedwithyouandimport
ittoyourworkbook.
Ifyouhaveaconnectionwhichyouhaveusedforaqueryandnowyou
wanttosharethatconnectionwithsomeoneelse,youcanexportthat
connectionasanodcfile.
Onthequerytable,there’sabuttoncalled“ExportConnection”and
whenyouclickonit,itallowsyoutosavethatquery’sconnectionin
yoursystem.
Andifyouwanttoimportaconnectionthatissharedbysomeone
else,youcansimplygototheDatatabandintheGet&
Transformclickontheexistingconnections.
Andthenclickonthe“BrowseforMore”buttonfromwhereyoucan
locatetheconnectionfilewhichhasbeensharedwithyouandimport
ittoyourworkbook.
Power Query Language (M Code)…
AsImentionedearlierthatforeverystepyouperformedinpowerqueryitgeneratesacode(atthebackend)whichis
calledMCode.OntheHometab,thereisabuttoncalled“AdvancedEditor”whichyoucanusetoseethecode.
Andwhenyouclickontheadvancededitoritwillshowyouthecodeeditorandthatcodelookssomethinglikethe
image.
Misacasesensitivelanguageandlikealltheotherlanguagesitusesvariablesandexpressions.Thebasicstructureof
codelookslikebelowwherethecodestartswiththeLETexpression.
Inthiscode,wehavetwovariablesandthevaluesdefinedtothem.Intheend,togetthevalue,INexpressionhasbeen
used.NowwhenyouclickOKitwillreturnthevalueassignedtothevariable“Variablename”intheresult.
AsImentionedearlierthatforeverystepyouperformedinpowerqueryitgeneratesacode(atthebackend)whichis
calledMCode.OntheHometab,thereisabuttoncalled“AdvancedEditor”whichyoucanusetoseethecode.
Andwhenyouclickontheadvancededitoritwillshowyouthecodeeditorandthatcodelookssomethinglikethe
image.
Misacasesensitivelanguageandlikealltheotherlanguagesitusesvariablesandexpressions.Thebasicstructureof
codelookslikebelowwherethecodestartswiththeLETexpression.
Inthiscode,wehavetwovariablesandthevaluesdefinedtothem.Intheend,togetthevalue,INexpressionhasbeen
used.NowwhenyouclickOKitwillreturnthevalueassignedtothevariable“Variablename”intheresult.
MS Excel
Day 13 –VBA & Macros
Introduction
Day 13 –VBA & Macros
Introduction
What is VBA Macro…?
AVBA Macrois nothing but a line of code to instruct
Excel to do a specific task.
Once we write the code in VBA, we can execute the
same task at any time in the workbook.
The macro code can eliminate repetitive, boring tasks
and automate the process.
VBAVisual Basic for Applicationsis the Microsoft
programming language for Microsoft products like Excel,
Word, and PowerPoint.
It will do all the programming we wish to do in the VBE
(Visual Basic Editor).
It is the platform to write our code of tasks to execute in
Excel.
To start withVBA codingin Excel, you need to record a
Macro.
VBA coding is available under theDeveloper tab in excel.
AVBA Macrois nothing but a line of code to instruct
Excel to do a specific task.
Once we write the code in VBA, we can execute the
same task at any time in the workbook.
The macro code can eliminate repetitive, boring tasks
and automate the process.
VBAVisual Basic for Applicationsis the Microsoft
programming language for Microsoft products like Excel,
Word, and PowerPoint.
It will do all the programming we wish to do in the VBE
(Visual Basic Editor).
It is the platform to write our code of tasks to execute in
Excel.
To start withVBA codingin Excel, you need to record a
Macro.
VBA coding is available under theDeveloper tab in excel.
Record a Macro…
Under the “Developer” tab, click onRecord Macro.
As soon as you click on the “Record Macro,” Excel asks
you to give a name to your Macro.
Give a proper name to the Macro. It should not
contain any space characters or special characters.
For example, you can give underscore (_) as the word
separator and then click on “OK” to start the
recording.
From now onwards, the macro recorder keeps
recording all your activities in the Excel sheet.
Firstly, we will select cell A1.
Now, we will type“Welcome to VBA”in the A1 cell.
Now, we will stop recording under the “Developer”
tab.
So, Excel stops recording the activities we do in
Excel.
Now, let us see how Excel recorded the activities.
Under the “Developer” tab, click on “Visual Basic.”
Under the “Developer” tab, click onRecord Macro.
As soon as you click on the “Record Macro,” Excel asks
you to give a name to your Macro.
Give a proper name to the Macro. It should not
contain any space characters or special characters.
For example, you can give underscore (_) as the word
separator and then click on “OK” to start the
recording.
From now onwards, the macro recorder keeps
recording all your activities in the Excel sheet.
Firstly, we will select cell A1.
Now, we will type“Welcome to VBA”in the A1 cell.
Now, we will stop recording under the “Developer”
tab.
So, Excel stops recording the activities we do in
Excel.
Now, let us see how Excel recorded the activities.
Under the “Developer” tab, click on “Visual Basic.”
Assoonasyouclickon“VisualBasic,”wewill
seethewindowasinimage.
Doubleclickon“Modules.”
Now,wewillseethecodeontheright-hand
side.
MacrocodestartedwiththewordSUB.
Allthemacrohastwoparts:the“Head”andthe
“Tail”becauseeverymacrohasaname
Inbetweentheheadandtailofthemacro,
excelrecordedalltheactivities.
Thefirstthingwedidafterstartingrecording
themacrowasweselectedcellA1,andExcel
recordeditasRange(“A1”).Select.
Thesecondactivitywaswhenweenteredthe
value“WelcometoVBA.”Whenweselectedit,
itbecameanactivecell,soExcelrecordedthe
activityasActiveCell.FormulaR1C1 =
“WelcometoVBA.”
Note:R1C1isrow1,column1.
Record a Macro…
seethewindowasinimage.
Doubleclickon“Modules.”
Now,wewillseethecodeontheright-hand
side.
MacrocodestartedwiththewordSUB.
Allthemacrohastwoparts:the“Head”andthe
“Tail”becauseeverymacrohasaname
Inbetweentheheadandtailofthemacro,
excelrecordedalltheactivities.
Thefirstthingwedidafterstartingrecording
themacrowasweselectedcellA1,andExcel
recordeditasRange(“A1”).Select.
Thesecondactivitywaswhenweenteredthe
value“WelcometoVBA.”Whenweselectedit,
itbecameanactivecell,soExcelrecordedthe
activityasActiveCell.FormulaR1C1 =
“WelcometoVBA.”
Note:R1C1isrow1,column1.
Record a Macro…
A third activity is after typing the word “Welcome to VBA,”
we press the “Enter” key, and Excel selects the A2 cell.
Like this, the Macro recorder recorded all our activities in
the Excel sheet. Now, delete the word in cell A1.
After deleting the word, go to VBE, where our code is once
again. Click on the “Run” button to enter the same text
value to cell A1.
Note:The shortcut key to run the code is F5.
So the macro is executed, and we get the same value
again. Like this, we can automate our daily routine work to
save time and eliminate boring daily tasks.
Record a Macro…
we press the “Enter” key, and Excel selects the A2 cell.
Like this, the Macro recorder recorded all our activities in
the Excel sheet. Now, delete the word in cell A1.
After deleting the word, go to VBE, where our code is once
again. Click on the “Run” button to enter the same text
value to cell A1.
Note:The shortcut key to run the code is F5.
So the macro is executed, and we get the same value
again. Like this, we can automate our daily routine work to
save time and eliminate boring daily tasks.
Record a Macro…
How to Save a Macro Excel..?
One must save the Excel workbook containing macro code as “Macro-Enabled
Workbook.”
Then, click “Save As” and select thefile’s extensionas “Macro-Enabled
Workbook.
One must save the Excel workbook containing macro code as “Macro-Enabled
Workbook.”
Then, click “Save As” and select thefile’s extensionas “Macro-Enabled
Workbook.
Relative Reference in Macro…
We have two options to refer a cell in excel VBA Absolute references and Relative
references.
Default Excel records macro in Absolute mode.
We select a cell “A1”, turn on “Use Relative Reference” and record a macro to
type some text in cells B2:B4.
Since we turn on the “Relative reference” option. Macro considers the number of
rows and number of columns from active cells.
In our example, we select cell A1 and start type B2 which is to move one column
and one row from A1 (Active cell).
Step 1:Open Excel and Select Cell “A1”.
Step 2:Go to “Developer” Tab >> Press “Use Relative References” >> Click
“Record Macro” .
Step 3:Enter the Macro name “relativeReference” and Press “OK”.
Step 4:Type “Australia” in cell B2
Step 5:Type “Brazil” in cell B3
Step 6:Type “Mexico” in cell B4
We have two options to refer a cell in excel VBA Absolute references and Relative
references.
Default Excel records macro in Absolute mode.
We select a cell “A1”, turn on “Use Relative Reference” and record a macro to
type some text in cells B2:B4.
Since we turn on the “Relative reference” option. Macro considers the number of
rows and number of columns from active cells.
In our example, we select cell A1 and start type B2 which is to move one column
and one row from A1 (Active cell).
Step 1:Open Excel and Select Cell “A1”.
Step 2:Go to “Developer” Tab >> Press “Use Relative References” >> Click
“Record Macro” .
Step 3:Enter the Macro name “relativeReference” and Press “OK”.
Step 4:Type “Australia” in cell B2
Step 5:Type “Brazil” in cell B3
Step 6:Type “Mexico” in cell B4
Relative Reference in Macro…
Step 7:Select cell B5 and Press “Stop Recording”
Step 8:You just delete the contents in cells B2:B4, Select Cell B1.
Step 9:Go to View >> Macros >> View Macros –to popup Macro dialog box
[keyboard shortcut –Alt+F8].
Step 10:Select Macro from list (eg. relativeReference) and Press “Run”.
Output: The active cell is B1 and run the macro. So, the outputs (C2:C4) are
placed one row and one column from the active cell B1.
Step 7:Select cell B5 and Press “Stop Recording”
Step 8:You just delete the contents in cells B2:B4, Select Cell B1.
Step 9:Go to View >> Macros >> View Macros –to popup Macro dialog box
[keyboard shortcut –Alt+F8].
Step 10:Select Macro from list (eg. relativeReference) and Press “Run”.
Output: The active cell is B1 and run the macro. So, the outputs (C2:C4) are
placed one row and one column from the active cell B1.
MS Excel
Day 14 –Working with VBA
Day 14 –Working with VBA
What is VBA used for..?
In Excel, you can use VBA for different things. Here are a few:
Enter Data: You can enter data in a cell, range of cells. You can
alsocopy and pastedata from one section to another.
Task Automation: You can automate tasks that want you to spend a
lot of time. The best example I can give is using a macro to create a
pivot table.
Create a Custom Excel Function: With VBA, you can also create a
Custom User Defined Function and use it in the worksheet.
Create Add-Ins: In Excel, you can convert your VBA codes into add-ins
and share them with others as well.
Integrate with other Microsoft Applications: You can also integrate
Excel with other Microsoft applications. Like, you can enter data into
a text file.
In Excel, you can use VBA for different things. Here are a few:
Enter Data: You can enter data in a cell, range of cells. You can
alsocopy and pastedata from one section to another.
Task Automation: You can automate tasks that want you to spend a
lot of time. The best example I can give is using a macro to create a
pivot table.
Create a Custom Excel Function: With VBA, you can also create a
Custom User Defined Function and use it in the worksheet.
Create Add-Ins: In Excel, you can convert your VBA codes into add-ins
and share them with others as well.
Integrate with other Microsoft Applications: You can also integrate
Excel with other Microsoft applications. Like, you can enter data into
a text file.
Excel Programming Fundamentals…
A procedure in VBA is a set of codes or a single line of code that performs a
specific activity.
SUB: Sub procedure can perform actions but doesn’t return a value (but you
can use an object to get that value).
Function: With the help of the Function procedure, you create your function,
which you can use in the worksheet or the other SUB and FUNCTION
procedures
You need variables and constants to use values in the code multiple times.
Variable: A Variable can store a value, it has a name, you need to define its
data type, and you can change the value it stores. As the name suggests,
“VARIABLE” has no fixed value. It is like a storage box that is stored in the
system.
Constant:A constant also can store a value, but you can’t change the value
during the execution of the code.
Variables and Constants
A procedure in VBA is a set of codes or a single line of code that performs a
specific activity.
SUB: Sub procedure can perform actions but doesn’t return a value (but you
can use an object to get that value).
Function: With the help of the Function procedure, you create your function,
which you can use in the worksheet or the other SUB and FUNCTION
procedures
You need variables and constants to use values in the code multiple times.
Variable: A Variable can store a value, it has a name, you need to define its
data type, and you can change the value it stores. As the name suggests,
“VARIABLE” has no fixed value. It is like a storage box that is stored in the
system.
Constant:A constant also can store a value, but you can’t change the value
during the execution of the code.
Variables and Constants
Dim in VBA…
VBA DIM statement stands for “declare” that you must use when you need to
declare a variable. When you use it, it tells VBA that you are declaring a specific
name as a variable as assigning a specific data type to it. It can be used to
declare the following:
Basic Variables
Variants
Objects
Array
Use DIM Statement in VBA
Type the keyword “Dim” at the start.
After that, enter the name of the variable that you want to use.
Next, you need to use the word “as” and you’ll get an instant list ofdata
types.
In the end, choose the data type that you want to assign to the variable.
VBA DIM statement stands for “declare” that you must use when you need to
declare a variable. When you use it, it tells VBA that you are declaring a specific
name as a variable as assigning a specific data type to it. It can be used to
declare the following:
Basic Variables
Variants
Objects
Array
Use DIM Statement in VBA
Type the keyword “Dim” at the start.
After that, enter the name of the variable that you want to use.
Next, you need to use the word “as” and you’ll get an instant list ofdata
types.
In the end, choose the data type that you want to assign to the variable.
Data Types…
You need to declare the data type for VARIABLES and CONSTANTS.
When you specify the data type for a variable or a constant, it ensures the
validity of your data.
If you omit the data type, VBA applies the Variant data type to your variable (it’s
the most flexible), VBA won’t guess what the data type should be.
You need to declare the data type for VARIABLES and CONSTANTS.
When you specify the data type for a variable or a constant, it ensures the
validity of your data.
If you omit the data type, VBA applies the Variant data type to your variable (it’s
the most flexible), VBA won’t guess what the data type should be.
Events in Excel…
WheneveryoudosomethinginExcel,that’sanevent:enteravalueinacell,inserta
newworksheet,orinsertachart.Belowistheclassificationofeventsbasedonthe
objects:
ApplicationEvents:TheseareeventsthatareassociatedwiththeExcel
applicationitself.
WorkbookEvents:Theseareeventsthatareassociatedwiththeactionsthat
happeninaworkbook.
WorksheetEvents:Theseeventsareassociatedwiththeactionthathappensina
worksheet.
ChartEvents:Theseeventsareassociatedwiththechartsheets(whichare
differentfromworksheets).
UserformEvents:Theseeventsareassociatedwiththeactionthathappenswitha
userform.
OnTimeEvents:OnTimeeventsarethosewhichcantriggercodeataparticular
pointintime.
OnKeyEvents:OnKeyeventsarethosewhichcantriggercodewhenaparticular
keyispressed.
WheneveryoudosomethinginExcel,that’sanevent:enteravalueinacell,inserta
newworksheet,orinsertachart.Belowistheclassificationofeventsbasedonthe
objects:
ApplicationEvents:TheseareeventsthatareassociatedwiththeExcel
applicationitself.
WorkbookEvents:Theseareeventsthatareassociatedwiththeactionsthat
happeninaworkbook.
WorksheetEvents:Theseeventsareassociatedwiththeactionthathappensina
worksheet.
ChartEvents:Theseeventsareassociatedwiththechartsheets(whichare
differentfromworksheets).
UserformEvents:Theseeventsareassociatedwiththeactionthathappenswitha
userform.
OnTimeEvents:OnTimeeventsarethosewhichcantriggercodeataparticular
pointintime.
OnKeyEvents:OnKeyeventsarethosewhichcantriggercodewhenaparticular
keyispressed.
Range…
The range object is the most common and popular way to refer to a range in your VBA codes. You need to refer to the cell
address, let me tell you the syntax.
Worksheets(“Sheet1”).Range(“A1”)
Just like any other programming language, you can also write codes to test conditions in
VBA. It allows you to do it in two different ways.
IF THENELSE: It’s an IF statement that you can use to test a condition and then run a
line of code if that condition is TRUE. You can also write nesting conditions with it
SELECTCASE: In the select case, you can specify a condition and then different cases
for outcomes to test to run different lines of code to run. It’s a little more structured than
the IF statement.
Conditions…
The range object is the most common and popular way to refer to a range in your VBA codes. You need to refer to the cell
address, let me tell you the syntax.
Worksheets(“Sheet1”).Range(“A1”)
Just like any other programming language, you can also write codes to test conditions in
VBA. It allows you to do it in two different ways.
IF THENELSE: It’s an IF statement that you can use to test a condition and then run a
line of code if that condition is TRUE. You can also write nesting conditions with it
SELECTCASE: In the select case, you can specify a condition and then different cases
for outcomes to test to run different lines of code to run. It’s a little more structured than
the IF statement.
Conditions…
VBA Loops…
You can write codes that can repeat and re-repeat an action in VBA, and there are multiple ways that
you can use to write code like this.
For Next: The best fit for using For Next is when you want to repeat a set of actions a fixed number
of times.
For Each Next: It’s perfect to use when you want to loop through a group of objects from a
collection of objects.
Do While Loop: The simple idea behind the Do While Loop is to perform an activity while a condition
is true.
Do Until Loop: In the Do Until, VBA runs a loop and continues to run it if the condition is FALSE.
You can write codes that can repeat and re-repeat an action in VBA, and there are multiple ways that
you can use to write code like this.
For Next: The best fit for using For Next is when you want to repeat a set of actions a fixed number
of times.
For Each Next: It’s perfect to use when you want to loop through a group of objects from a
collection of objects.
Do While Loop: The simple idea behind the Do While Loop is to perform an activity while a condition
is true.
Do Until Loop: In the Do Until, VBA runs a loop and continues to run it if the condition is FALSE.
Input and Message Box…
Input Box: The input Box is a function that shows an input box to the user and
collects a response.
Message Box: Message Box helps you show a message to the user but, you
have an option to add buttons to the message box to get the response of the
user.
Excel has no luck when it comes to programming errors, and you have to deal
with them, no matter what.
Syntax Errors: It’s like typos that you do while writing codes, but VBA can help
you by pointing out these errors.
Compile Errors: It comes when you write code to perform an activity, but that
activity is not valid.
Runtime Errors: A RUNTIME error occurs at the time of executing the code. It
stops the code and shows you the error dialog box.
Logical Error: It’s not an error but a mistake while writing code and sometimes
can give you nuts while finding and correcting them.
Errors…
Input Box: The input Box is a function that shows an input box to the user and
collects a response.
Message Box: Message Box helps you show a message to the user but, you
have an option to add buttons to the message box to get the response of the
user.
Excel has no luck when it comes to programming errors, and you have to deal
with them, no matter what.
Syntax Errors: It’s like typos that you do while writing codes, but VBA can help
you by pointing out these errors.
Compile Errors: It comes when you write code to perform an activity, but that
activity is not valid.
Runtime Errors: A RUNTIME error occurs at the time of executing the code. It
stops the code and shows you the error dialog box.
Logical Error: It’s not an error but a mistake while writing code and sometimes
can give you nuts while finding and correcting them.
Errors…
Select Case Statement…
Various If Statements..
IF THEN is the simplest form of an IF statement. All we need to do is specify a
condition to check and if that condition is TRUE it will perform a task. But, if that
condition is FALSE it will do nothing and skip the line instantly.
IF condition Then statement[s] End If
You can use the IF-Then-Else statement where you want to perform a specific task
if a condition is TRUE and a different task if a condition is FALSE.
IF Condition Then Statement[s] Else Statement[s] End If
IF-Then-Elseif-Elseis the most useful and importanttype of IFwhich will help
you to write advanced condition statements. In this type, you can specify the
second condition after evaluating your first condition.
IF Condition Then Statement[s] ElseifCondition Then Statement[s] Else
Statement[s] End If
IF THEN is the simplest form of an IF statement. All we need to do is specify a
condition to check and if that condition is TRUE it will perform a task. But, if that
condition is FALSE it will do nothing and skip the line instantly.
IF condition Then statement[s] End If
You can use the IF-Then-Else statement where you want to perform a specific task
if a condition is TRUE and a different task if a condition is FALSE.
IF Condition Then Statement[s] Else Statement[s] End If
IF-Then-Elseif-Elseis the most useful and importanttype of IFwhich will help
you to write advanced condition statements. In this type, you can specify the
second condition after evaluating your first condition.
IF Condition Then Statement[s] ElseifCondition Then Statement[s] Else
Statement[s] End If
MS Excel
Day 15 –Power Pivot
Day 15 –Power Pivot
Power Pivot…
IntroducedtoExcel2010and2013asanadd-on,butnownativetotheapplication,
PowerPivotispartofMicrosoft'sbusinessintelligencestackcapableof(butnot
limitedto)bigdataanalyticsworkwithoutspecialtyinfrastructureorsoftware.
AccordingtoMicrosoft,"PowerPivotenablesyoutoimportmillionsofrowsofdata
frommultipledatasourcesintoasingleExcelworkbook,createrelationships
betweenheterogeneousdata,createcalculatedcolumnsandmeasuresusing
formulas,buildPivotTablesandPivotCharts,andfurtheranalyzethedatasothatyou
canmaketimelybusinessdecisionswithoutrequiringITassistance."
PowerPivotwascreatedindirectresponsetothebigdatademandsofcontemporary
businessintelligenceneeds,whichpriorgenerationsofExcel—giventheir1,048,576
rowlimitorprocessingspeedshortcomings—struggledtocopewith.
PowerPivotisexpressedbyMicrosoftusingDAX(DataAnalysisExpressions),whichis
acollectionoffunctions,operators,andconstantsusableinaformulaorexpression
tocalculate/returnoneormorevalues.
IntroducedtoExcel2010and2013asanadd-on,butnownativetotheapplication,
PowerPivotispartofMicrosoft'sbusinessintelligencestackcapableof(butnot
limitedto)bigdataanalyticsworkwithoutspecialtyinfrastructureorsoftware.
AccordingtoMicrosoft,"PowerPivotenablesyoutoimportmillionsofrowsofdata
frommultipledatasourcesintoasingleExcelworkbook,createrelationships
betweenheterogeneousdata,createcalculatedcolumnsandmeasuresusing
formulas,buildPivotTablesandPivotCharts,andfurtheranalyzethedatasothatyou
canmaketimelybusinessdecisionswithoutrequiringITassistance."
PowerPivotwascreatedindirectresponsetothebigdatademandsofcontemporary
businessintelligenceneeds,whichpriorgenerationsofExcel—giventheir1,048,576
rowlimitorprocessingspeedshortcomings—struggledtocopewith.
PowerPivotisexpressedbyMicrosoftusingDAX(DataAnalysisExpressions),whichis
acollectionoffunctions,operators,andconstantsusableinaformulaorexpression
tocalculate/returnoneormorevalues.
Pivot vs Power Pivot…
Power Languages…
What’s the problem with Normal Pivot.?
HistoricallyPivotTablesonlyallowtheusertouseasinglesourcedatatable.
Ifthedataaresplitindifferenttables,thenithasbeenhistoricallyhardto
useinaPivotTable.
TheExceluserthenneedstouseVLOOKUP(orsimilar)tobringthedatainto
asingletablebeforeitispossibletocreateaPivotTable.
Excelcanonlystore1millionrowsofdata,soforgettryingtousePivot
Tablesonreallylargedatasets.
Wellbeforeyouhitthe1millionrowlimit,Excelwillstartperformingvery
poorly,particularlyifyouhavelotsofVLOOKUPsetc.Excelitisnot
optimisedforlargedatabasecrunchingwork.
Pivottablesareverygoodataggregatingdatathatexistinasuitableraw
formintheunderlyingtable,butareterribleinreportingonanythingelse
thatcouldbederivedfromthesourcedata.
Soinsummary,PivotTablesaregreatforvisualisationaslongasyourdatais
optimisedinawaytoleveragethepowerofPivotTables.
HistoricallyPivotTablesonlyallowtheusertouseasinglesourcedatatable.
Ifthedataaresplitindifferenttables,thenithasbeenhistoricallyhardto
useinaPivotTable.
TheExceluserthenneedstouseVLOOKUP(orsimilar)tobringthedatainto
asingletablebeforeitispossibletocreateaPivotTable.
Excelcanonlystore1millionrowsofdata,soforgettryingtousePivot
Tablesonreallylargedatasets.
Wellbeforeyouhitthe1millionrowlimit,Excelwillstartperformingvery
poorly,particularlyifyouhavelotsofVLOOKUPsetc.Excelitisnot
optimisedforlargedatabasecrunchingwork.
Pivottablesareverygoodataggregatingdatathatexistinasuitableraw
formintheunderlyingtable,butareterribleinreportingonanythingelse
thatcouldbederivedfromthesourcedata.
Soinsummary,PivotTablesaregreatforvisualisationaslongasyourdatais
optimisedinawaytoleveragethepowerofPivotTables.
What is Power Pivot.?
PowerPivotisactuallyadatamodellingtool.
Takedatafromoneormoresourcesandcombineittogetherintoasingledata
source(thisiscalledthedatamodel).
Enhancetheunderlyingdatasothatyoucancreatenewderivedconceptsoutof
thedata.SoifyouhaveSales$andCost$inyoursourcedata,youcancreate
Margin$andMargin%andmakethesenewconceptsavailabletoaPivotTable.
ThenewconceptsyoubuildinPowerPivotareinfinitelyreusableinyourPivot
Tables.
PowerPivotcompressesthesourcedataandstoresitasadatabasewithinthe
Excelworkbook.Thismeansthatyoucanstore10s(even100s)ofmillionsof
rowsofdatadirectlyinanExcelworkbookinahighlycompressedandperformant
way,anddistributeittootherpeoplewithoutthemneededtohaveaccesstothe
sourcedatabase.
WhatPowerPivotdoesischangethesourcedatasothatyoucanusedatathat
couldnotpreviouslybeused,andyoucangetmoreoutofdatathatyoupreviously
usedinsideofaPivotTable.SoPowerPivotisactuallyyoursourcedataon
steroids.
PowerPivotisactuallyadatamodellingtool.
Takedatafromoneormoresourcesandcombineittogetherintoasingledata
source(thisiscalledthedatamodel).
Enhancetheunderlyingdatasothatyoucancreatenewderivedconceptsoutof
thedata.SoifyouhaveSales$andCost$inyoursourcedata,youcancreate
Margin$andMargin%andmakethesenewconceptsavailabletoaPivotTable.
ThenewconceptsyoubuildinPowerPivotareinfinitelyreusableinyourPivot
Tables.
PowerPivotcompressesthesourcedataandstoresitasadatabasewithinthe
Excelworkbook.Thismeansthatyoucanstore10s(even100s)ofmillionsof
rowsofdatadirectlyinanExcelworkbookinahighlycompressedandperformant
way,anddistributeittootherpeoplewithoutthemneededtohaveaccesstothe
sourcedatabase.
WhatPowerPivotdoesischangethesourcedatasothatyoucanusedatathat
couldnotpreviouslybeused,andyoucangetmoreoutofdatathatyoupreviously
usedinsideofaPivotTable.SoPowerPivotisactuallyyoursourcedataon
steroids.
How it works with Power Pivot.?
Importmillionsofrowsofdatafrommultipledatasources
Fastcalculationsandanalysis
VirtuallyUnlimitedSupportofDataSources
SecurityandManagement
DataAnalysisExpressions(DAX)
Raw Data Power Query Power Pivot
Excel pivot
reports/chart
s/grid
Power view
Power map
Importmillionsofrowsofdatafrommultipledatasources
Fastcalculationsandanalysis
VirtuallyUnlimitedSupportofDataSources
SecurityandManagement
DataAnalysisExpressions(DAX)
Raw Data Power Query Power Pivot
Excel pivot
reports/chart
s/grid
Power view
Power map
How to enable Power Pivot.?
GotoFile>Options>Add-Ins.
IntheManagebox,clickCOMAdd-ins>
Go.
ChecktheMicrosoftOfficePowerPivot
box,andthenclickOK.Ifyouhave
otherversionsofthePowerPivotadd-
ininstalled,thoseversionsarealso
listedintheCOMAdd-inslist.
GotoFile>Options>Add-Ins.
IntheManagebox,clickCOMAdd-ins>
Go.
ChecktheMicrosoftOfficePowerPivot
box,andthenclickOK.Ifyouhave
otherversionsofthePowerPivotadd-
ininstalled,thoseversionsarealso
listedintheCOMAdd-inslist.
Tags
Categories
TechnologyDownload
Download Slideshow Get the original presentation fileQuick Actions
Statistics
- Views 100
- Slides 178
- Age 456 days
Related Slideshows
11
8-top-ai-courses-for-customer-support-representatives-in-2025.pptx
JeroenErne2
44 views
10
7-essential-ai-courses-for-call-center-supervisors-in-2025.pptx
JeroenErne2
44 views
13
25-essential-ai-courses-for-user-support-specialists-in-2025.pptx
JeroenErne2
36 views
11
8-essential-ai-courses-for-insurance-customer-service-representatives-in-2025.pptx
JeroenErne2
33 views
21
Know for Certain
DaveSinNM
19 views
17
PPT OPD LES 3ertt4t4tqqqe23e3e3rq2qq232.pptx
novasedanayoga46
23 views