advanced excel 01
TRANSCRIPT
Part: 01
Microsoft Advanced ExcelReferences
References (Relative, Absolute, Mixed)
Naming
Referring Other Sheets
Useful FunctionsVarious Functions
Conditional Sum, Count, Average
If – Testing Condition
Lookups (HLookUp, VLookUp, Index, Match)Database Functions
Data Analysis
Sorting (by Characters, by Color, Custom List)Filtering (incl. Advance Filtering)
Conditional Formatting
Tips & Tricks
Muhammad Faisal Shafi
[email protected] 0333-3054495
Associate member of Institute of Cost and Management Accountant of Pakistan (ICMAP), has over 10 years of experience in Audit, Accounts and CA firms. Currently he has been working with Pakistan Paper Products Limited as Head of Internal Audit since January 2011.
I have delivered different courses of MS Office (incl. MS Excel upto advance level & MS Access) at Audit & Accounts Training Institute of AGPR. Conducted two workshops of two days each at ICMAP H.O. on Financial Modeling using MS Excel.
With the coordination of KBC I will conduct another two days workshop on Dashboard using Excel at ICMAP H.O. in the next month. I’ve been invited as guest speaker at Shaheed Zulfiqar Ali Bhutto Institute of Science and Technology (SZABIST) regarding a session on the topic of “Finance and Financial Analysis using MS Excel” scheduled in the next month.
Join me at Xlab (Excel Lab) a facebook group at https://www.facebook.com/groups/318201971676974/
Your Trainer
Part: 02
Microsoft Advanced ExcelYour Trainer
Creating ReportsSub-TotalScenario ManagerGoal SeekData Table (One & Two Variables)Consolidation (3-D, Internal, External)
Improve Visualization of DataCharts (Popular Types, Adding/ Deleting Data, Sub-chart, Secondary Axis)
Pivot Tables (Basics, Filtering, Options, Grouping, Slicer)
Facilitating & Restricting UsersData Validation (incl. Depended Lists)Controls (Combo Box, List Box, Check Box, Option Button, Spin Button, Scroll Bar)Automating Your Repetitive TasksMacro (Intro, Developer Tab, Record, Run, View Code, Sort to get top 3, Methods to Call)Questions & Answers
Muhammad Faisal Shafi
[email protected] 0333-3054495
Associate member of Institute of Cost and Management Accountant of Pakistan (ICMAP), has over 10 years of experience in Audit, Accounts and CA firms. Currently he has been working with Pakistan Paper Products Limited as Head of Internal Audit since January 2011.
I have delivered different courses of MS Office (incl. MS Excel upto advance level & MS Access) at Audit & Accounts Training Institute of AGPR. Conducted two workshops of two days each at ICMAP H.O. on Financial Modeling using MS Excel.
With the coordination of KBC I will conduct another two days workshop on Dashboard using Excel at ICMAP H.O. in the next month. I’ve been invited as guest speaker at Shaheed Zulfiqar Ali Bhutto Institute of Science and Technology (SZABIST) regarding a session on the topic of “Finance and Financial Analysis using MS Excel” scheduled in the next month.
Join me at Xlab (Excel Lab) a facebook group at https://www.facebook.com/groups/318201971676974/
References (Relative,
Absolute, Mixed)
Naming
Referring Other Sheets
Ref
eren
ces
Unit Price 25
OrderDate Packets Units @ Packet Total Units Total
6-Jan-13 95 10 950 23,750
23-Jan-13 50 60 3000 75,000
9-Feb-13 36 100 3600 90,000
26-Feb-13 27 12 324 8,100
15-Mar-13 56 100 5600 140,000
1-Apr-13 60 12 720 18,000
18-Apr-13 75 12 900 22,500
5-May-13 90 12 1080 27,000
22-May-13 32 100 3200 80,000
8-Jun-13 60 10 600 15,000
25-Jun-13 90 60 5400 135,000
12-Jul-13 29 10 290 7,250
29-Jul-13 81 100 8100 202,500
15-Aug-13 35 12 420 10,500
1-Sep-13 2 12 24 600
18-Sep-13 16 60 960 24,000
5-Oct-13 28 10 280 7,000
22-Oct-13 64 100 6400 160,000
8-Nov-13 15 10 150 3,750
25-Nov-13 96 10 960 24,000
12-Dec-13 67 60 4020 100,500
29-Dec-13 74 100 7400 185,000
15-Jan-14 46 12 552 13,800
1-Feb-14 87 60 5220 130,500
18-Feb-14 4 10 40 1,000
7-Mar-14 7 100 700 17,500
24-Mar-14 50 10 500 12,500
10-Apr-14 66 60 3960 99,000
27-Apr-14 96 10 960 24,000
14-May-14 53 100 5300 132,500
31-May-14 80 12 960 24,000
17-Jun-14 5 10 50 1,250
4-Jul-14 62 10 620 15,500
21-Jul-14 55 60 3300 82,500
7-Aug-14 42 100 4200 105,000
24-Aug-14 3 12 36 900
10-Sep-14 7 60 420 10,500
27-Sep-14 76 10 760 19,000
14-Oct-14 57 100 5700 142,500
31-Oct-14 14 10 140 3,500
17-Nov-14 11 60 660 16,500
4-Dec-14 94 10 940 23,500
21-Dec-14 28 100 2800 70,000
Rel. Ref.
=B3*C3
Abs. Ref.=B3*C3*$
G$1
Estimates of sales (quantity) for the year of 2015
Estimated contribution rate 20% 30% 10% 40%
Month Units Sold Doug Dave Brian Larry
January 720 144 216 72 288
February 900 180 270 90 360
March 1,080 216 324 108 432
April 3,200 640 960 320 1,280
May 600 120 180 60 240
June 5,400 1,080 1,620 540 2,160
July 552 110 166 55 221
August 5,220 1,044 1,566 522 2,088
September 40 8 12 4 16
October 700 140 210 70 280
November 500 100 150 50 200
December 3,960 792 1,188 396 1,584
Total 22,872 4,574 6,862 2,287 9,149
Mixed Ref.
=$J4*K$2
References (Relative, Absolute, Mixed)
Naming
Name Rules:
•Names Must Not Exceed 255 Characters•Cell Names Are Not Allowed To Contain Spaces•Excel Does Not Distinguish Between Capital Letter and Lower
Case•Cell Names Must Be Unique and Must Not Resemble a Number,
Cell Address Or Reserve Word•A Cell Name must start with a Letter, or an Underscore
='Jul-Dec'!D2+'Jan-Jun'!D2
Sheet to be summarized by taking data from other two
sheets
=SUM('Jul-Dec'!D2,'Jan-Jun'!D2)
Referring Other Sheets
External Link: (Link to another excel workbook)
Source file: Module 3 - Inter Ex Data Target file: Module 3
When source file is open ='[Module 3 - Inter Ex Data.xlsx]Jul-Dec'!$C$4
When source file is close ='D:\HOIA\Internal Audit\All About Excel\Excel Practice\Deliverables\Basics to Inter\[Module 3 - Inter Ex Data.xlsx]Jul-Dec'!$C$4
Edit Links:
Referring Other Sheets
Hyper Link: (Internet type link)
Shortcut key to call this dialogue box is Ctrl + K
Referring Other Sheets
Useful Functio
ns
Various Functions
Conditional Sum,
Count, Average
If – Testing Condition
Lookups (HLookUp, VLookUp,
Index, Match)
Database Functions
Various Functions
Days
123456
123456
Various Functions
Various Functions
=LEFT(B80,FIND("-",B80)-1)
=RIGHT(B80,LEN(B80)-FIND("-",B80))
Concatenate function may be replaced with “&”, however be careful that this symbol is to be placed at each join as well as take care of using “” around adding string value w/o any reference.
=CONCATENATE(D80,"-",E80) OR =D80&"-"&E80
Various Functions
Today Now Day Mont
hYea
r
=CHOOSE(WEEKDAY(B99),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
=SUMPRODUCT(D103:D107
,E103:E107)
=REPLACE(B113,7,5,"Fresh")
=SUBSTITUTE(B113,"Leave","Fresh")
Various Functions
Depreciation Methods
SLN : Straight Line MethodSYD : Sum of Years Digits MethodDB : Declining Balance Method
=SLN($D$121,$D$123,$D$122)
=SYD($D$121,$D$123,$D$122,E127)=DB($D$121,$D$123,$D$122,E127)
Various Functions
Syntax:• Abs(Number) Abs = Absolute• Round(No., No. of Digits)• RoundDown(No., No. of Digits)• RoundUp(No., No. of Digits)• Ceiling(No., Significance)• Floor(No., Significance)• Choose(Index No., Value1, Value2,…)• Indirect(Ref)• Large(Array, K)• Small(Array, K)• Rank(No., Ref., [Order]) Order 1 for Ascending• Left(Text, [No. of Characters])• Right(Text, [No. of Characters])• Mid(Text, Start No., [No. of Characters])• Len(Text) Lentgh• Rept(Text, No. of Times) Repeat• Find(Find What, Where, [Start No.])• Search(Find What, Where, [Start No.])
Various Functions
Syntax:• Concatenate(Text1, Text2,…)• Upper(Text)• Lower(Text)• Proper(Text)• Today()• Now()• Day(Serial No.)• Month(Serial No.)• Year(Serial No.)• Date(Year, Month, Day)• Weekday(Serial No.)• SumProduct(Array1, Array2, …)• RandBetween(Bottom, Top) Random Between• Replace(Old Text, Start No., No. of Characters, New Text)• Substitute(Text, Old Text, New Text)• =SLN(Cost, Salvage Value, Life)• =SYD(Cost, Salvage Value, Life, Per[iod])• =DB(Cost, Salvage Value, Life, Period, [Month])
Various Functions
Conditional Sum, Count, Average
Syntax:SumIf(Range, Criteria, [Sum Range])CountIf(Range, Criteria)AverageIf(Range, Criteria, [Average Range])
Lets sum of transactions of “Pear” in terms of amount:2,965 =SUMIF(A5:A26,B36,E5:E26)
Lets sum of transactions of “Pear” in terms of quantity:37 =SUMIF(A5:A26,B36,C5:C26)
Lets count number of entries of “Pear”:3 =COUNTIF(A5:A25,B31)
Lets average of transactions of “Pear” in terms of amount:988.33 =AVERAGEIF(A5:A26,B31,E5:E26)
Lets average of transactions of “Pear” in terms of rate:81.67 =AVERAGEIF(A5:A26,B31,D5:D26)
Criteria may also be as “Pear”
Conditional Sum, Count, Average
Lets Sum of quantity where quantity is 12:36 =SUMIF(C5:C25,12)
Lets Sum of amount where amount is greater than 2000:13,040 =SUMIF(E5:E25,">2000")
Lets Sum of amount where quantity is more than 10:22,266 =SUMIF(C5:C25,">10",E5:E25)
??
? ?
6,310
3
2,875 155
Conditional Sum, Count, Average
Copy (above data of C
ol. A)
Paste Special Values
Select any cell (e.g. A57)
Data R
emove D
uplicates
Now we are going to make a list of items because in our database there are some items entered more than once.
Conditional Sum, Count, Average
List of only unique
entries
List after sorting
(Asc.)
Be careful about referencing; match total of summary with original database.
=SUMIF($A$5:$A$25,$A57,$E$5:$E$25)=SUMIF($A$5:$A$25,$A57,$C$5:$C$25)
Conditional Sum, Count, Average
=SUMIF($D$4:$D$17,A21,$C$4:$C$17)
=COUNTIF($D$4:$D$17,A21)
=SUMIF($B$4:$B$17,A27,$C$4:$C$17)
=COUNTIF(B4:B17,A27)
Conditional Sum, Count, Average
=D4*Normal
=SUMIF(B$4:B4,B4,E$4:E4)
Running Total of Order
16,956,238 =SUMIF(A2:A7,"<4-1-13",B2:B7)
17,344,335 =SUMIF(A2:A7,">4-1-13",B2:B7)
Go to better
Conditional Sum, Count, Average
=SUMIF(E137:E149,">0")
=ABS(SUMIF(E137:E149,"<0"))
=SUMIF(B120:B132,"<>Punjab",C120:C132)
Conditional Sum, Count, Average
=MONTH(B171)
=B$168&"-"&F171&"-"&COUNTIF(F$171:F171,F171)
1
2
=SMALL(D171:D183,COUNTIF(C171:C183,"")+1) 3
=SMALL(D171:D183,COUNTIF(D171:D183,0)+1)
4
Conditional Sum, Count, Average
If – Testing Condition
=IF(B2>C2, "Yes", "No")
Alternatively:=IF(E2>1, "Yes", "No")
Syntax:IF(logical_test, [value_if_true], [value_if_false])
=IF(E19>0,C19*0.01, 0)Alternatively=IF(C19>D19,C19*0.01, 0)
=IF(E39<=35,E39*D39,35*D
39)
=IF(E39<=35,0,(E39-
35)*D39*1.5)
=IF(B39>C39,(B39-
C39)*5%,0)
Alternatively: =MIN(E39,35)
*D39
Alternatively: =IF(E39>35,
(E39-35)*D39*150%,
0)
Alternatively: =MAX(B39-C39,0)*5%
If – Testing Condition
=IF(C55<=DATE(2000,1,1),"AWARD","NO AWARD")
Alternatively:=IF(C55<=36526,"AWARD","NO AWARD")
If – Testing Condition
=IF(F5>=60,"Pass","Fail")
=IF(AND(G5="Pass",L5="Pass",Q5="Pass"),"OK","Not OK")
=IF(OR(B5>80,R5="OK"),"ALLOWED", "NOT ALLOWED")
If – Testing Condition
If – Testing Condition
=IF(C109>=15,10%*B109,IF(C109>17,13%*B109,IF(C109>19,20%*B109,8%*B109)))
=IF(C109>=15,12%*B109,IF(C109>17,15%*B109,IF(C109>19,22%*B109,10%*B109)))
=IF(AND(C109>=15,D109="F",OR(E109="KORANGI",E109="LANDHI",E109="STEEL TOWN")),30%*B109,IF(C109>19,35%*B109,20%*B109))
=IF(C109>=19,"MANAGER",IF(C109>=17,"DEP.MANAGER","CLERK"))
If – Testing Condition
If – Testing Condition
=IF(F140>=30000,"A",IF(F140>=20000,"B",IF(F140>=10000,"C","D")))
=IF(F140>=25000,"Good",IF(F140>=15000,"Fair","Poor"))
=IF(AND(B140>=5000,C140>=5000,D140>=5000,E140>=5000),"A","B")
=IF(OR(B140>=5000,C140>=5000,D140>=5000,E140>=5000),"A","B")
If – Testing Condition
If – Testing Condition
=IF(AND($C167>=1,$C167<31),$D167,0)
=IF(AND($C167>30,$C167<91),$D167,0)
=IF(AND($C167>90,$C167<181),$D167,0)
=IF($C167>180,$D167,0)
=SUM(F167:H167)
If – Testing Condition
=IF(J196>=$B$214,$C$214,IF(J196>=$B$213,$C$213,IF(J196>=$B$212,$C$212,IF(J196>=$B$211,$C$211,IF(J196>=$B$210,$C$210,$C$209)))))
=IF(K196=$C$214,$F$209,IF(K196=$C$213,$F$210,IF(K196=$C$212,$F$211,$F$212)))
Go to better
If – Testing Condition
=IF(J196>=$B$214,$C$214,IF(J196>=$B$213,$C$213,IF(J196>=$B$212,$C$212,IF(J196>=$B$211,$C$211,IF(J196>=$B$210,$C$210,$C$209)))))
=IF(MIN(C196,D196,E196,F196,G196)>=40,IF(J196>=$B$214,$C$214,IF(J196>=$B$213,$C$213,IF(J196>=$B$212,$C$212,IF(J196>=$B$211,$C$211,IF(J196>=$B$210,$C$210,$C$209))))),"Fail")
If – Testing Condition
=IF(ISBLANK(B219),"",COUNTA(B$219:B219))
=IF(AND(ISBLANK(F220),ISBLANK(G220)),"",H219+F220-G220)
=IFERROR(K219/L219,0)
If – Testing Condition
=IF(ISBLANK(D235),"Enter your name please: ","Welcome: "&D235)
Space
=IF(ISTEXT(H235),"","Enter a valid class Name:")
=IF(ISNUMBER(H237),"","Enter a valid Roll No.")
=IF(H239=H235&"."&H237,"","Enter a valid Code:")
If – Testing Condition
=IF(AND(ISBLANK(M235),ISBLANK(M237)),"",M235&"_"&M237)
=IF(OR(M235=T235,M235=T236,M235=T237,M235=T238,M235=T239,M235=U235,M235=U236,M235=U237,M235=U238,M235=U239),"","Enter a valid Class Name:")
=IF(AND(M237>0,M237<100),"","Enter a valid Roll No.")
=IF(OR(ISBLANK(M235),ISBLANK(M237)),"Enter valid Class Name and Roll No.",IF(EXACT(M239,U240),"","Enter a valid Code:"))
If – Testing Condition
=IF(COUNTIF(A$245:A245,A245)<2,SUMIF(A$245:A245,A245,D$245:D245),0)
=IF(COUNTIF(B$245:B245,B245)>1,"",1+MAX(G$244:G244))
Back to simple
If – Testing Condition
Lookups (HLookUp, VLookUp, Index, Match)
=VLOOKUP(J3,$B$16:$C$21,2)
=VLOOKUP(J3,$B$16:$D$21,3)
=IF(MIN(C3,D3,E3,F3,G3)>=40,VLOOKUP(J3,$B$16:$C$21,2),"Fail")
Back to simple
Lookups (HLookUp, VLookUp, Index, Match)
Dept
=VLOOKUP(A28,Dept,2,FALSE)
Lookups (HLookUp, VLookUp, Index, Match)
Stock
=VLOOKUP(A51,Stock,2,FALSE)
=VLOOKUP(A51,Stock,5,FALSE)
Lookups (HLookUp, VLookUp, Index, Match)
=VLOOKUP(B59,$E$59:$H$67,4)*B59
Lookups (HLookUp, VLookUp, Index, Match)
=VLOOKUP(B90,$B$74:$D$86,3)
=LOOKUP(B90,$B$74:$C$86,$D$74:$D$86)
Syntax:Vlookup(Lookup Value, Table Array, Column No., [Range Lookup])
Lookup(Lookup Value, Lookup Vector, [Result Vector])
Lookups (HLookUp, VLookUp, Index, Match)
=HLOOKUP(C102,$B$98:$N$100,3)
=LOOKUP(C102,$B$98:$N$99,$B$100:$N$100)
Syntax:Hlookup(Lookup Value, Table Array, Row No., [Range Lookup])
Lookup(Lookup Value, Lookup Vector, [Result Vector])
Lookups (HLookUp, VLookUp, Index, Match)
Look horizontally (row)=MATCH("Product",L2:O2) 2
Look vertically (column)=MATCH("Plum",M3:M7) 2=MATCH("Plum",M2:M7) 3
Arranged Ascending
=MATCH(10573,L3:L7)It took Match Type as Exact
=MATCH(10572,L3:L7)It took Match Type as Less Than
=MATCH(10572,L3:L7,1)Match Type Less Than given by user
=MATCH("Orange",M3:M7)It took Match Type as Exact
=MATCH("Peach",M3:M7)Wrong result because it is neither ascending nor descending
=MATCH("Peach",M3:M7,0)Match Type Exact given by user
Match
=MATCH("*ple",M3:M7,0) 1
=MATCH(MAX(N3:N7),N3:N7,0) 2Right result, by using Match Type to Exact
=MATCH(LARGE(N3:N7,2),N3:N7,0) 3Right result, by using Match Type to Exact
Lookups (HLookUp, VLookUp, Index, Match)
=INDEX(V3:V7,3)Single Column therefore only Array and Row No. are enough =INDEX(V3:Y7,3,0)
Error because row number is not right
=INDEX(V3:Y7,3,1)All arguments given
=INDEX(V3:Y3,,2)Single Row therefore row reference may be skipped
=INDEX(V3:Y7,,2)Product got from the row where function applied while we wish to get first row’s product
=INDEX(V3:Y7,1,2)All arguments given
=INDEX(V3:Y7,MATCH(W13,W3:W7,0),4)Match will give row No. according to what has been entered at W13 (exact match) in turn index will return respective quantity being 4th column
=INDEX(V3:Y7,MATCH(W17,W3:W7,0),3)List of Data Validation has been applied at W17 to restrict user to select between available list; index will return unit price being 3rd column
Index
Database Functions
Type Date Invoice Name AmountLevel 1 28-Jan-11 595 Poole 8,000.00 Level 1 3-Feb-11 600 Davis 6,000.00 Level 1 17-Feb-11 602 Crowther 7,000.00 Level 1 17-Feb-11 603 Maze 4,000.00 Level 1 18-Feb-11 605 Park 1,000.00 Level 1 24-Feb-11 606 Waterson 6,000.00 Level 1 24-Feb-11 607 Dune 6,000.00 Level 2 19-Jan-11 591 Bryant 34,970.00 Level 2 21-Jan-11 596 Carver 34,545.45 Level 2 25-Feb-11 592 Blythe 25,196.36 Level 2 28-Feb-11 569 Porter 24,913.64 Level 2 28-Feb-11 594 Blair 7,000.00 Level 2 10-Mar-11 611 Boyden 14,545.45 Level 2 21-Mar-11 614 Hayden 17,943.64 Level 2 31-Mar-11 546 Wilson 32,036.36 Level 3 9-Mar-11 610 Dune 84,200.00 Level 4 10-Mar-11 611 Bryant 77,258.18 Level 8 14-Feb-11 575 Constable 8,015.91 Level 8 4-Mar-11 608 Constable 27,272.73 Level 1 6-Apr-11 616 Stuart 6,000.00 Level 2 6-Apr-11 611 Bryant 14,545.45 Level 3 14-Apr-11 618 Hayden 36,533.64 Level 3 24-Apr-11 621 Polter 46,845.45 Level 4 6-Apr-11 611 Blair 77,258.18 Level 4 7-Apr-11 617 Dune 20,719.09 Level 4 12-Apr-11 612 Carver 12,869.09 Level 7 24-May-11 630 Atkins 1,000.00 Level 7 30-May-11 630 Atkins 1,000.00 Level 7 30-May-11 632 Norris 1,000.00 Level 1 10-May-11 623 Arthur 7,000.00 Level 1 11-May-11 623 Arthur 7,000.00 Level 1 24-May-11 630 Atkins 5,000.00
Type Date Invoice Name AmountLevel 1 30-May-11 630 Atkins 5,000.00 Level 2 9-May-11 611 Blair 14,545.45 Level 4 9-May-11 611 Blair 77,258.18 Level 4 19-May-11 627 Hayden 10,023.64 Level 4 20-May-11 627 Hayden 10,023.64 Level 4 24-May-11 626 Wilson 11,909.09 Level 4 27-May-11 625 Blythe 18,946.36 Level 6 23-May-11 628 Craig 707.27 Level 6 23-May-11 629 Polter 126.00 Level 8 2-May-11 608 Craig 27,272.73 Level 8 18-May-11 586 Howard 18,181.82 Level 8 19-May-11 586 Howard 18,181.82 Level 8 23-May-11 608 Craig 27,272.73 Level 8 24-May-11 631 Soul 5,000.00 Level 8 26-May-11 586 Simpson 18,181.82 Level 1 12-Jul-11 653 Roberts 6,000.00 Level 1 22-Jul-11 658 Murray 6,000.00 Level 1 25-Jul-11 658 Murray 6,000.00 Level 4 20-Jul-11 654 Peterson 9,711.82 Level 6 5-Jul-11 646 Barney 2,660.00 Level 6 20-Jul-11 657 Boyden 2,516.36 Level 6 20-Jul-11 657 Boyden 649.09 Level 9 20-Jul-11 652 Soul 35,201.82 Level 9 22-Jul-11 652 Soul 35,201.82 Level 9 26-Jul-11 652 Soul 35,201.82 Level 5 6-Jul-11 648 Simpson 18,181.82 Level 5 25-Jul-11 659 Davis 10,909.09 Level 7 8-Jun-11 630 Atkins 1,000.00 Level 1 8-Jun-11 630 Atkins 5,000.00 Level 1 17-Jun-11 636 Martin 4,000.00 Level 1 20-Jun-11 642 Carter 2,000.00 Level 1 23-Jun-11 643 Carter 5,000.00 Level 6 9-Jun-11 633 Garrison 655.00 Level 5 15-Jun-11 635 Simpson 20,909.09
Extract from Database data of “Atkins” to prove
results
Syntax of DFunctions (Database, Field, Criteria)Criteria
Type Date Date Name AmountLevel 1 >=01/01/2011 <=03/31/2011 Level 2 >=01/01/2011 <=03/31/2011
DSUM DCOUNT DAVERAGE DMAX DMIN
229,151 15 15,277 34,970 1,000
Field: “Amount”
Name Total SalesNo. of
Transactions Average SaleMaximum
SaleMinimum
SaleAtkins 18,000 6 3,000 5,000 1,000
DSUM DCOUNT DAVERAGE DMAX DMIN
Criteria # Data Remove Duplicates# Validation List
Type Date Invoice Name AmountLevel 7 24-May-11 630 Atkins 1,000. Level 1 24-May-11 630 Atkins 5,000 Level 7 30-May-11 630 Atkins 1,000Level 1 30-May-11 630 Atkins 5,000Level 7 8-Jun-11 630 Atkins 1,000Level 1 8-Jun-11 630 Atkins 5,000
Database Functions
Data Analys
is
Sorting (by Characters,
by Color, Custom
List)
Filtering (incl.
Advance Filtering)
Conditional Formatting
Date Region Sales Rep Customer Product Sales COGS
22-Apr-11 MidWest Vivien R Yahoo WFJ Item 1,087 489
20-Jul-11 North Michael G Yahoo ZCN Item 459 430
22-Jun-11 North Hassan M Yahoo YUZ Item 143 97
29-Jul-11 NorthEast Hassan M Yahoo YCR Item 4,578 1,694
5-Dec-11 SouthEast Donald Wayne Yahoo WZQ Item 5,060 2,176
13-Oct-11 NorthEast Sarabeth L Yahoo ZBJ Item 3,296 3,082
14-Dec-11 NorthEast Anastasiya A Yahoo XLN Item 3,122 1,155
16-Jul-11 MidWest Vivien R Yahoo VGE Item 903 845
1-Feb-12 SouthEast Cherry M Yahoo ZUL Item 6,205 3,599
19-Dec-12 NorthEast Toni M Yahoo ZHN Item 3,214 1,382
12-Feb-11 West Hassan M Yahoo ZBJ Item 6,336 2,851
20-Nov-11 SouthEast Daniil N Yahoo YYC Item 5,673 3,290
9-Feb-11 West Vivien R Yahoo YRA Item 6,514 3,843
2-Apr-11 North Cherry M Yahoo YLH Item 8,135 3,498
26-Nov-11 West Barbara C Yahoo XWY Item 4,863 3,647
13-Aug-11 North Abdisamad A Yahoo XKA Item 8,088 5,500
26-Apr-11 NorthEast Dan Yahoo XDA Item 9,527 4,096
19-Aug-11 MidWest Cherry M Yahoo XAQ Item 5,932 2,669
30-Jul-11 NorthEast Abdisamad A Yahoo WYM Item 7,546 2,792
26-Oct-11 West Abdisamad A Yahoo WSV Item 9,099 6,187
27-Jun-11 NorthEast Nikol M Yahoo WPE Item 9,300 5,394
22-Oct-11 MidWest Shuting Yahoo WOW Item 3,907 1,446
5-Apr-11 MidWest Barbara C Yahoo WLI Item 4,092 2,373
18-Apr-11 North Tonya J Yahoo WKL Item 9,195 8,597
9-Sep-11 MidWest Vivien R Yahoo WIP Item 5,592 3,076
25-Jul-11 West Suzanne M Yahoo VVA Item 8,389 4,950
7-Apr-11 NorthEast Abdisamad A Yahoo VTW Item 4,899 2,204
8-Mar-11 SouthEast Barbara C Yahoo VSH Item 6,510 5,729
17-Sep-11 SouthEast Tonya J Yahoo VPC Item 6,253 3,689
18-May-11 SouthEast Tonya J Yahoo VOO Item 1,818 1,054
17-Sep-11 North Anastasiya A Yahoo VNQ Item 1,762 969
30-Aug-11 SouthEast Nikol M Yahoo UYS Item 8,209 4,844
18-Jul-11 MidWest Michael G Yahoo UXX Item 6,786 4,615
13-Jan-11 SouthEast Suzanne M Yahoo UWC Item 9,416 4,049
30-Jan-11 MidWest Daniil N Yahoo UNF Item 1,334 1,000
24-Aug-11 NorthEast Dan Yahoo UMJ Item 6,951 6,499
28-Jul-11 SouthEast Catherine W Yahoo UJQ Item 8,290 4,808
27-Apr-11 West Analyssa C Yahoo UER Item 1,283 872
12-Feb-11 West Sarabeth L Yahoo UCA Item 2,304 1,267
22-Sep-11 North Cherry M Yahoo UAL Item 9,929 5,858
Custom List:MS Office Button Excel Options Popular Edit Custom Lists
Applying Custom List at Sorting:Data Sort Column; Sort On; Order (Custom List)
Sorted by Font Color then by Cell
Color
Getting unique valuesColumn-wise (one by one Col.)
Sorting
Date Region Sales Rep Customer Product Sales COGS 1-Feb-12 SouthEast Cherry M Yahoo ZUL Item 6,205 3,599
12-Feb-11 West Hassan M Yahoo ZBJ Item 6,336 2,851 20-Nov-11 SouthEast Daniil N Yahoo YYC Item 5,673 3,290 9-Feb-11 West Vivien R Yahoo YRA Item 6,514 3,843
26-Nov-11 West Barbara C Yahoo XWY Item 4,863 3,647 5-Dec-11 SouthEast Donald Wayne Yahoo WZQ Item 5,060 2,176 26-Oct-11 West Abdisamad A Yahoo WSV Item 9,099 6,187 25-Jul-11 West Suzanne M Yahoo VVA Item 8,389 4,950 8-Mar-11 SouthEast Barbara C Yahoo VSH Item 6,510 5,729 17-Sep-11 SouthEast Tonya J Yahoo VPC Item 6,253 3,689 18-May-11 SouthEast Tonya J Yahoo VOO Item 1,818 1,054
Custom FilterRegion Greater than “S”
Date Region Sales Rep Customer Product Sales COGS MidWest >5000
Date Region Sales Rep Customer Product Sales COGS MidWest
>5000
And
Or
Data Filter Advanced Filter List Range Criteria Range Copy to
Date Region Sales Rep Customer Product Sales COGS Sales>10-31-2011 >3999 <5001
Date>10-31-2011
A valid criteria for such data
Filtering
Customer*Mart*
Wild Card (*)
Productc?ke
Wild Card (?)
To display data where Customer name consists of “Mart” at any position
To display data where Product name starts
with “C” and ends with “ke”
A B C D E F G
OrderDate Product OrderID Shipped Customer Invoice Paid
1-Jan-02Coke 10456 1-Aug-02MegaMart 278
278
3-Jan-02Good*Eats 10457 2-Aug-02MiniMart
789
500
3-Jan-02 10458 3-Aug-02SuperMart 1,365
1,365
3-Jan-02Produce 10459 4-Aug-02Mart-o-rama
240
240
4-Jan-02Coke 10460 5-Aug-02MegaMart 1,348
1,348
11-Jan-02Produce 10461 6-Aug-02Mart-o-rama
2,023
2,023
11-Jan-02Produce 10462 7-Aug-02MegaMart 293
293
18-Jan-02Produce 10463 8-Aug-02MiniMart 1,803
1,803
20-Jan-02Coke 10464 9-Aug-02MegaMart 1,668
1,668
20-Jan-02Cake 1046510-Aug-
02MegaMart 566
566
21-Jan-02Good*Eats 10466
11-Aug-02MegaMart
422
422
21-Jan-02Produce 1046712-Aug-
02Mart-o-rama
1,336
1,336
25-Jan-02Coke 1046813-Aug-
02MegaMart 176
176
25-Jan-02Cake 1046914-Aug-
02MiniMart 870
870
26-Jan-02Cake 1047015-Aug-
02MiniMart 1,682
1,682
26-Jan-02Produce 1047116-Aug-
02MiniMart 1,704
1,704
3-Feb-02 1047217-Aug-
02SuperMart 460
460
3-Feb-02Cake 1047318-Aug-
02MiniMart 1,607
1,607
4-Feb-02Cake 1047419-Aug-
02MiniMart 1,205
1,205
7-Feb-02Coke 1047520-Aug-
02MiniMart 2,192
2,192
8-Feb-02Cake 1047621-Aug-
02MiniMart 455
455
17-Feb-02Good*Eats 10477
22-Aug-02MegaMart
1,800
1,800
17-Feb-02 1047823-Aug-
02MiniMart 2,207
2,207
22-Feb-02Good*Eats 10479
24-Aug-02MegaMart
122
122
23-Feb-02Coke 1048025-Aug-
02MiniMart 2,374
2,374
23-Feb-03Cake 1048126-Aug-
02MegaMart 1,348
1,348
28-Feb-02Coke 1048227-Aug-
02MegaMart 616
616
Filtering (Advance)
A B C D E F G
OrderDate Product OrderID Shipped Customer Invoice Paid
1-Jan-02Coke 10456 1-Aug-02MegaMart 278
278
3-Jan-02Good*Eats 10457 2-Aug-02MiniMart
789
500
3-Jan-02 10458 3-Aug-02SuperMart 1,365
1,365
3-Jan-02Produce 10459 4-Aug-02Mart-o-rama
240
240
4-Jan-02Coke 10460 5-Aug-02MegaMart 1,348
1,348
11-Jan-02Produce 10461 6-Aug-02Mart-o-rama
2,023
2,023
11-Jan-02Produce 10462 7-Aug-02MegaMart 293
293
18-Jan-02Produce 10463 8-Aug-02MiniMart 1,803
1,803
20-Jan-02Coke 10464 9-Aug-02MegaMart 1,668
1,668
20-Jan-02Cake 1046510-Aug-
02MegaMart 566
566
21-Jan-02Good*Eats 10466
11-Aug-02MegaMart
422
422
21-Jan-02Produce 1046712-Aug-
02Mart-o-rama
1,336
1,336
25-Jan-02Coke 1046813-Aug-
02MegaMart 176
176
25-Jan-02Cake 1046914-Aug-
02MiniMart 870
870
26-Jan-02Cake 1047015-Aug-
02MiniMart 1,682
1,682
26-Jan-02Produce 1047116-Aug-
02MiniMart 1,704
1,704
3-Feb-02 1047217-Aug-
02SuperMart 460
460
3-Feb-02Cake 1047318-Aug-
02MiniMart 1,607
1,607
4-Feb-02Cake 1047419-Aug-
02MiniMart 1,205
1,205
7-Feb-02Coke 1047520-Aug-
02MiniMart 2,192
2,192
8-Feb-02Cake 1047621-Aug-
02MiniMart 455
455
17-Feb-02Good*Eats 10477
22-Aug-02MegaMart
1,800
1,800
17-Feb-02 1047823-Aug-
02MiniMart 2,207
2,207
22-Feb-02Good*Eats 10479
24-Aug-02MegaMart
122
122
23-Feb-02Coke 1048025-Aug-
02MiniMart 2,374
2,374
23-Feb-03Cake 1048126-Aug-
02MegaMart 1,348
1,348
28-Feb-02Coke 1048227-Aug-
02MegaMart 616
616
To display data where Product name is blank
=ISBLANK(B2)
FALSE
Find Blank
Top/ Bottom 10…, Above Average, and Below Average are available as built-in facility.
FALSE
Unmatched
To display data where Paid amount differs
with Invoice amount =F2<>
G2
#VALUE!
Find No.
To display data where Order ID consists of “8”
at any position=FIND(8,C
2)
Filtering (Advance)
G.R. No. Student Math English Urdu Islamiat Total %age Grade
1005 Hashim 74 42 34 96 246 62%B
1030 Bilal 47 55 99 80 281 70%A
1021 Basheer 42 40 52 91 225 56%C
1054 Javaria 78 36 66 27 207 52%Fail
1074 Bushra 42 61 51 58 212 53%C
1084 Saqib 37 31 39 107 27%Fail
1005 Rehana 73 36 54 62 225 56%C
1065 Mubashir 42 47 52 40 181 45%D
1036 Waqar 41 61 91 78 271 68%B
1043 Khalid 78 76 83 99 336 84%A+
1024 Junaid 36 75 90 82 283 71%A
1027 Chaman 34 36 38 33 141 35%Pass
Duplicates
Lower than 33
Grading Criteria33% Pass40% D50% C60% B70% A80% A+
Greater than 79
Top 3
Bottom 3
Containing “a”
Blank
Conditional Formatting
Conditional Formatting
Sales by BuilderDate Builder Total
11 Feb 13 Doug 4,64017 Jan 13 Morgan 2,32831 Jan 13 Dave 3,850
19 Aug 10 Gill 3,81023 Jun 10 Dave 2,31302 Feb 13 Brian 1,56515 Apr 10 Larry 5,74020 Mar 10 Rob 5,84005 Feb 10 Morgan 1,88414 Feb 13 Jones 54811 Jan 10 Brian 4,128
14 Nov 09 Rob 4,64012 Nov 09 Jones 2,12503 Nov 09 Doug 63904 Nov 09 Richard 4,11005 Nov 09 Sime 96912 Dec 09 Mandis 80225 Jan 10 Rogger 3,420
19 Mar 10 Dave 3,56003 May 10 Dave 1,63624 May 10 Dave 3,88802 Jun 10 Larry 6,63003 Jun 10 Brian 3,80023 Jul 10 Larry 1,605
01 Aug 10 Rob 1,98903 Sep 10 Rob 3,04017 Sep 10 Brian 2,76012 Oct 10 Dave 2,44824 Nov 10 Larry 2,223
Highlight other than
“Larry”Highlig
ht Below
AverageHighlight
Dates Occurrin
g This Month
Hide Error
Sales by BuilderDate Builder Total
11 Feb 13 Doug 4,64017 Jan 13 Morgan 2,32831 Jan 13 Dave 3,85019 Aug 10 Gill 3,81023 Jun 10 Dave 2,31302 Feb 13 Brian 1,56515 Apr 10 Larry 5,74020 Mar 10 Rob 5,84005 Feb 10 Morgan 1,88414 Feb 13 Jones 54811 Jan 10 Brian 4,12814 Nov 09 Rob 4,64012 Nov 09 Jones 2,12503 Nov 09 Doug 63904 Nov 09 Richard 4,11005 Nov 09 Sime 96912 Dec 09 Mandis 80225 Jan 10 Rogger19 Mar 10 Dave 3,56003 May 10 Dave 1,63624 May 10 Dave 3,88802 Jun 10 Larry 6,63003 Jun 10 Brian 3,80023 Jul 10 Larry 1,605
01 Aug 10 Rob 1,98903 Sep 10 Rob 3,04017 Sep 10 Brian 2,76012 Oct 10 Dave 2,44824 Nov 10 Larry 2,223
Icon Sets
Data Bars
Tips & Tricks
Tips & TricksF6 to move b/w open filesCtrl + Pg Up/ Pg Dn to move b/w sheetsDrag & Drop (to cut/ paste or copy/ paste or to make place for cut/ paste)Shift + Space to select entire rowCtrl + Space to select entire columnF4 to repeat last commandShift + F2 to edit comments
Insert a Sheet
Right-click on sheet tab
Delete a Sheet
Rename a Sheet
Hide a Sheet
Move or Copy a Sheet
Desired Location of SheetWe can move or copy to the same book or any open book or a new bookDesired Order of SheetCopy of SheetCheck it to make a copy otherwise Excel will move the sheet
Move means Cut & PasteCopy means Copy & Paste
Tips & Tricks
Tips & Tricks
Tips & Tricks
2
1
34
5
6
7
8
Requirement: From the ledger of repair of vehicles (given below), we have to extract entries of vehicle # “AB-0131”
Tips & Tricks
Tips & Tricks
Tips & Tricks
Alternatively: Sorted for “KDN-9779” from the following list
=IF(ISERROR(FIND(9779,C73)),"","KDN-9779")
Tips & Tricks
Tips & TricksGo To SpecialConstants, Formulas, Blanks, Row Differences, Column Differences, Visible Cells Only
Paste SpecialFormulas, Values, Formats, Comments, Validation, Column Widths, Operation, Transpose, Paste Link
Link a picture
Hide functions from view
Custom Formatting (Symbols e.g. Kg, Rounding e.g. in thousands)
Sub-Total
Scenario Manager
Goal Seek
Data Table (One & Two Variables)
Consolidation (3-D, Internal, External)
Cre
atin
g R
epor
ts
Sub-Total
Sort by Region then by Sale Rep; Sub-Total at each change in Region; again Sub-Total at each change in Sales Rep (uncheck replace current sub-totals).Apply formatting to only sub-totals of any level expand or collapse accordingly then ^G Special Visible Cells Only; Sub-Total Remove All
Date Region Sales Rep Customer Product Sales COGS
Abdisamad A Total 105,487 71,869 Analyssa C Total 43,768 26,289 Anastasiya A Total 58,381 35,444 Barbara C Total 69,882 48,002 Catherine W Total 47,563 28,338 Cherry M Total 126,926 74,191 Dan Total 56,677 38,142 Dana L Total 99,612 64,527 Daniil N Total 57,301 32,180 Desiree A Total 63,582 46,800 Dina Total 70,651 40,473 Donald Wayne Total 63,125 33,664 Hassan M Total 103,656 70,381 Michael G Total 56,431 30,055 Nikol M Total 97,765 62,610 Sarabeth L Total 105,861 63,141 Shannon L Total 71,010 39,335
Shuting Total 106,369 57,344 Suzanne M Total 79,980 48,651 Toni M Total 75,657 42,140 Tonya J Total 100,052 67,262 Vivien R Total 46,682 23,305 North Total 1,706,418 1,044,143 Abdisamad A Total 72,645 47,760 Analyssa C Total 82,114 58,072 Anastasiya A Total 65,258 42,246 Barbara C Total 97,979 57,088
Sales Person AreaMonthly Sales
(Dec. 2012)Region 1
Hashim Gulshan 110,000 Basheer Liaquatabad 90,000 Javaria F.B. Area 120,000
320,000 320,000 Region 2
Khalid Nazimabad 125,000 Rehana Hyderi 95,000 Chaman North Karachi 75,000 Waqar Orangi 120,000 Saqib Golimar 75,000
490,000 490,000 Region 3
Bushra Saddar 85,000 Mubashir Shahra-e-Faisal 105,000 Bilal Malir 125,000 Junaid Super Highway 150,000
465,000 465,000
1,275,000 1,275,000
Sales Person AreaMonthly Sales
(Dec. 2012)
Region 1
Hashim Gulshan 110,000
Javaria F.B. Area 120,000
320,000 230,000
Region 2
Khalid Nazimabad 125,000
Waqar Orangi 120,000
490,000 245,000
Region 3
Mubashir Shahra-e-Faisal 105,000
Bilal Malir 125,000
Junaid Super Highway 150,000
465,000 380,000
1,275,000 855,000
9 9
=SUBTOTAL(A$20,C2:C20)
=SUBTOTAL(100+A$20,C2:C20)
Only records greater than 99,999 (below
figure records have been hidden).
Sub-Total
If Sub-Total used on filtered data then it accounts for only visible cells
Take both hidden & unhidden
Ignores hidden ce
lls, however,
hidden row(s)
after fil
tering will n
ot work
Advantages of Sub-TotalIf any range of total is wrong but grand total range is correct
then grand total will be correct.In case of hidden data as a result of filtering then it will
calculate only unhidden data
Disadvantages of Sub-TotalWe have to know function number of the functionIn case of hidden data as a result of filtering then it will
calculate only unhidden data
Sub-Total
Scenario Manager
Changing Cells Resulting Cells
Units Sold 15000 Total Revenue 1,500,000
S.P./ Unit 100 Total Costs 1,050,000
V.C./ Unit 40 Profit 450,000
Fixed Costs 450,000
1) Data-->What-If Analysis-->Scenario ManagerAdd-->Scenario Name: Pessimistic Approach-->Changing Cells: B4:B7 [Rel. Ref.] (1000;100;50;400,000)-->OK-->OK
2) Data-->What-If Analysis-->Scenario ManagerAdd-->Scenario Name: Optimistic Approach-->Changing Cells: B4:B7 [Rel. Ref.] (20000;150;60;1,000,000)-->OK-->OK
3) Data-->What-If Analysis-->Scenario ManagerAdd-->Scenario Name: Normal Approach-->Changing Cells: B4:B7 [Rel. Ref.] (10000;110;55;500,000)-->OK-->OK
4) Data-->What-If Analysis-->Scenario Manager-->SummaryResult Cells-->F4:F6 [Rel. Ref.]-->OK
B4:B7
F4:F6
Scenario Summary
Current Values: Pessimistic Approach Optimistic Approach Normal Approach
Changing Cells:
Units_Sold 15000 1000 20000 10000
S.P.__Unit 100 100 150 110
V.C.__Unit 40 50 60 55
Fixed_Costs 450,000 400,000 1,000,000 500,000
Result Cells:
Total_Revenue 1,500,000 100,000 3,000,000 1,100,000
Total_Costs 1,050,000 450,000 2,200,000 1,050,000
Profit 450,000 (350,000) 800,000 50,000
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Scenario Manager
Sales 145,769 Variable Costs 66,519 Contribution Margin 79,250 Fixed Costs 79,250 Net Income -
Assumptions Units Sold 4,859 Price per Unit 30.00 Variable Cost per Unit 13.69 Fixed Costs 79,250
Sales 170,769 Variable Costs 66,519 Contribution Margin 104,250 Fixed Costs 79,250 Net Income 25,000
Assumptions Units Sold 4,859 Price per Unit 35.15 Variable Cost per Unit 13.69 Fixed Costs 79,250
Sales 237,738 Variable Costs 108,488 Contribution Margin 129,250 Fixed Costs 79,250 Net Income 50,000
Assumptions Units Sold 7,925 Price per Unit 30.00 Variable Cost per Unit 13.69 Fixed Costs 79,250
Original – Break Even PointWe may come to this situation
applying on any other situation by setting value to 0
(“zero”)
Price Per Unit changed to earn a profit of
Rs.25,000
Units Sold changed to earn a profit of
Rs.50,000
Data What-If Analysis Goal Seek Set Cell (having a formula/ function) To Value By Changing Cell (without any formula/ function)
Goal Seek
One VariableInterest Rate Monthly Repayment Total Repayment Total Interest
10.00% 1,718 206,155 76,155 1%
3%
5%
7%
10%
12%
15%
20%
25%
30%
Interest Rate Monthly Repayment Total Repayment Total Interest
10.00% 1,718 206,155 76,155 1%
3%
5%
7%
10%
12%
15%
20%
25%
30%
Step 2
Step 3
Select Table Area
Tractor Loan
Amount of loan 130,000 Interest Rate 10.00%Term of Loan (Years) 10Number of Payments (per Year) 12
Amount of Payment
1,718
Total Paid 206,155 Interest Paid 76,155 To be Repaid by June 2023
Step 1
=K4
=K8
=K10
=K11
Data Table
One VariableStep 4 Data What-If Analysis Data Table
Keep Row Input Cell blankIn Column Input Cell ref. to original cell containing Interest Rate i.e. K4
Interest Rate
Monthly Repayment
Total Repayment
Total Interest
10.00% 396 47,574 17,574
1% 1,139 136,662 6,662
3% 1,255 150,635 20,635
5% 1,379 165,462 35,462
7% 1,509 181,129 51,129
10% 1,718 206,155 76,155
12% 1,865 223,815 93,815 15% 2,097 251,683 121,683
20% 2,512 301,479 171,479
25% 2,957 354,889 224,889
30% 3,427 411,244 281,244
Final Output Value
generated by Data Table
Now we may make this row invisible by changing font color
to white
Data Table
Two Variables
Select Table Area
Tractor Loan
Amount of loan 130,000 Interest Rate 10.00%Term of Loan (Years) 10Number of Payments (per Year) 12
Amount of Payment
1,718
Total Paid 206,155 Interest Paid 76,155 To be Repaid by June 2023
Step 1
T e r m o f L o a n
1,718 5 10 12 151%
3%
5%
7%
10%
12%
15%
20%
25%
30%
Step 3
=K8
T e r m o f L o a n
1,718 5 10 12 151%
3%
5%
7%
10%
12%
15%
20%
25%
30%
Step 2
Data Table
Two VariablesStep 4 Data What-If Analysis Data Table
In Row Input Cell ref. to original cell containing Term of Loan i.e. K5In Column Input Cell ref. to original cell containing Interest Rate i.e. K4
Final Output
Value generated
by Data Table
Now we may make this cell invisible by changing font color similar to fill color
of the row.
Term of Loan
396 5 10 12 151% 2,222 1,139 958 778 3% 2,336 1,255 1,076 898 5% 2,453 1,379 1,202 1,028
7% 2,574 1,509 1,337 1,168 10% 2,762 1,718 1,554 1,397 12% 2,892 1,865 1,707 1,560 15% 3,093 2,097 1,951 1,819 20% 3,444 2,512 2,388 2,283 25% 3,816 2,957 2,855 2,776 30% 4,206 3,427 3,346 3,289
Data Table
Consolidation
Product/Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Carlota 71 158 114 96 142 62 112 103 114 140 115 115 1342
Yanaki 109 161 147 79 97 109 184 114 110 168 146 132 1556
Aspen 143 181 96 138 115 88 111 155 135 179 156 89 1586
Sunset 134 155 89 101 98 72 106 120 141 171 131 142 1460
Delicate Arch 139 139 123 140 115 74 142 150 111 189 128 124 1574
Bellen 156 114 77 148 110 122 181 143 162 119 113 164 1609
Total 752 908 646 702 677 527 836 785 773 966 789 766 9127
3-D Reference
There may be lot of sheets excluding such sheets to be incorporated here. It may give wrong results if any sheet moved from the range sheets. Similar spaces should be used in all sheets.
=SUM('Store(1):Store(5)'!B3)
Type “=Sum(“Go to Store(1) Select relevant cell Shift + Go to Store(5) Enter
We may also put simply =sum('sto*'!B3) then it will converted itself to such 3D Ref.
A more reliable method to select the same cell from all sheet one by one instead of 3-D referencing.
Consolidation
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec TOTAL
Sales
Software 28,175 50,244 52,910 55,480 54,890 54,800 56,380 52,910 52,910 57,360 52,910 24,000 592,969
Books 47,700 74,550 78,000 82,500 81,250 81,420 56,500 78,220 78,220 84,780 78,220 54,700 876,060
Videos 26,500 49,750 51,850 55,850 55,400 53,500 55,500 53,750 53,750 56,500 53,750 25,250 285,600
CD-ROMs 49,532 48,720 51,258 53,998 54,810 54,303 26,460 51,258 51,258 56,840 51,258 26,513 576,205
SALES TOTAL 151,907 223,264 234,018 247,828 246,350 244,023 194,840 236,138 236,138 255,480 236,138 130,463 2,636,584
Expenses
Cost of Goods 12,153 17,861 18,721 19,826 19,708 19,522 15,587 18,891 18,891 20,438 18,891 10,437 210,927
Advertising 8,878 12,306 15,236 14,650 16,115 15,383 10,450 15,236 15,236 13,185 15,236 10,556 162,467
Rent 4,053 6,153 6,153 6,153 6,153 6,153 3,990 6,153 6,153 6,153 6,153 4,263 67,683
Supplies 2,509 3,516 4,102 3,809 3,663 4,102 2,470 4,102 4,102 3,663 3,956 2,842 42,835
Salaries 30,880 46,880 48,345 48,345 48,345 49,810 32,300 49,810 49,810 49,810 51,275 35,525 541,135
Shipping 27,503 40,288 42,485 43,950 42,485 43,218 28,500 42,485 42,485 46,148 44,683 29,435 473,663
Utilities 965 1,758 1,758 1,612 1,758 1,905 1,235 1,758 1,758 1,905 1,758 1,218 19,387
EXPENSES TOTAL 86,940 128,762 136,800 138,345 138,227 140,091 94,532 138,435 138,435 141,301 141,951 94,276 1,518,095
GROSS PROFIT 64,967 94,502 97,217 109,483 108,124 103,931 100,308 97,703 97,703 114,179 94,187 36,187 1,118,489
Consolidated data from three sheets (Div-A, Div-B, and Div-C) all sheets use similar labels for rows and columns, however, there was different patterns in the sheets (e.g. number of columns and rows are different even order of rows were different but consolidation made according to labels used therein.
Created a new sheet (i.e. Div-A2C) Data Consolidate Added (addresses) Use Labels in: Top Row & Left Column Create Links to Source Data
Consolidation
January February March April May June July August September October November December
Revenue
Branch A 16,498 16,549 15,524 15,356 17,657 15,216 15,926 15,621 16,517 18,315 15,613 15,643
Branch B 17,861 15,205 16,090 18,372 18,351 16,799 16,749 18,312 15,097 17,943 17,509 15,869
Branch C 18,807 17,256 17,893 17,686 18,344 15,190 15,377 18,592 15,696 15,641 16,344 18,537
Sale of Shirts 53,166 49,010 49,507 51,414 54,352 47,205 48,052 52,525 47,310 51,899 49,466 50,049
Branch A 12,057 10,078 11,695 12,056 11,525 11,907 10,076 12,054 11,357 13,882 10,376 11,903
Branch B 10,795 13,444 12,355 10,811 10,475 13,834 12,197 11,425 11,290 12,812 10,089 10,996
Branch C 11,401 10,122 12,127 11,534 11,593 13,154 10,739 13,650 13,364 10,038 12,237 13,950
Sale of Pants 34,253 33,644 36,177 34,401 33,593 38,895 33,012 37,129 36,011 36,732 32,702 36,849
Branch A 8,618 8,940 8,142 7,043 7,346 8,911 8,084 8,535 8,682 7,656 8,479 8,092
Branch B 7,670 8,920 8,147 8,631 8,657 8,183 8,062 7,810 8,653 7,964 7,883 7,496
Branch C 8,296 7,047 7,545 8,180 7,989 8,781 7,956 8,050 8,803 7,271 7,376 7,277
Sale of Shoes 24,584 24,907 23,834 23,854 23,992 25,875 24,102 24,395 26,138 22,891 23,738 22,865
Consolidated data from three workbooks(Br-A, Br-B, and Br-C). For convenience in referring ranges have been given name.
Created a new sheet (i.e. Br-A2C) Data Consolidate Browse to file Edit name of range Added (addresses) Use Labels in: Top Row & Left Column Create Links to Source Data
Consolidation with other workbooks (external data)
Improve Visualization of Data
Charts (Popular Types, Adding/ Deleting Data,
Sub-chart, Secondary Axis)
Pivot Tables (Basics,
Filtering, Options,
Grouping, Slicer)
Year Sales Expense Profit
2005 4,689
2,326 2,363
2006 4,946
2,152 2,794
2007 3,383
2,493 890
2008 4,978
2,065 2,913
2009 2,696
2,005 691
2010 3,339
2,190 1,149
Rs. in Thousands
Charts Sample
Data
Charts
2005 2006 2007 2008 2009 2010 -
1,000
2,000
3,000
4,000
5,000
6,000
SalesExpenseProfit
Column Chart
Line Chart2005 2006 2007 2008 2009 2010
-
1,000
2,000
3,000
4,000
5,000
6,000
4,689 4,946
3,383
4,978
2,696 Expense Expense
ExpenseExpense Expense
2005 2006
2007
2008
2009
SalesExpenseProfit
Axi
sData Series
Data LabelData Point
Lege
nd
Gridlines
Axis
Popular Types
Charts
Pie Chart
Bar Chart
Sales 4,689
Expense 2,326
Profit 2,363
Financial Year 2005
2005
2006
2007
2008
2009
2010
- 500 1,000 1,500 2,000 2,500 3,000 3,500 4,000 4,500 5,000
ProfitExpenseSales
Format Data LabelsLabel OptionsCategory Name, Value, Separator
Popular Types
Charts
2005 2006 2007 2008 2009 2010 -
1,000
2,000
3,000
4,000
5,000
6,000
SalesExpenseProfit
Doughnut Chart
Financial Year 2005SalesExpenseProfit
It may be used for more than one data categories but distinction may not be maintained.
Area Chart
Popular Types
Charts
Year Sales Expense Profit
2005 4,689
2,326
2,363
2006 4,946
2,152
2,794
2007 3,383
2,493
890
2008 4,978
2,065
2,913
2009 2,696
2,005
691
2010 3,339
2,190
1,149
2005 2006 2007 2008 2009 2010 -
1,000
2,000
3,000
4,000
5,000
6,000
Sales
Rs. in ThousandsTo add another series select chart then apply Chart Tools Design Tab Data Select Data Add (Series)
Alternatively select cells containing Expense data Copy Paste Special after selecting chart Add Cells as: New Series Values (Y) in: Columns Series Names in the First Row: Check it.
Adding a Series
Charts
Expenses AmountAdministrative Expenses 2,843,916,990
Financial Charges 944,943,570
Selling and Distribution Exp. 1,791,012,930 Sales Promotion 560,828,520
Staff Salaries and Benefits 538,637,000
Depreciation 331,181,000
Vehicle Expenses 174,699,000
Cartage and Forwarding 161,624,000
Other Selling Expenses 23,868,410
Tender Fees 175,000
Administrative Expenses
Financial Charges
Sales Promotion
Staff Salaries and Benefits
DepreciationVehicle Expenses Cartage and Forwarding Other Selling Expenses
Expenses
Bre
ak u
p of
Sal
es
and
Dis
trib
utio
n ex
pens
es
Select Data excl. Selling and Distribution Exp.; Format Data Series Series Options Split Series by:
Sub-Chart
Product Sales 2008Productgroup A 3,216,546
Product B 987,987
Product C 256,446
Product D 565,467
Product E 466,361
Product F 426,450
Product G 386,539
Product H 346,628
3,216,546
987,987
256,446
565,467
466,361
426,450386,539 346,628Sales by Productgroup in 2009
Productgroup AProduct BProduct CProduct DProduct EProduct FProduct GProduct H
Sub-ChartChart
s
Year Production Capacity2001 36,641 48,215 2002 47,761 48,215 2003 41,006 58,632 2004 40,432 58,632 2005 34,622 58,632 2006 38,478 58,632 2007 43,539 58,632 2008 48,439 58,632 2009 54,419 58,632 2010 45,122 58,632 2011 45,690 58,632 2012 53,230 58,632
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 -
10,000
20,000
30,000
40,000
50,000
60,000
70,000
Capacity Production
Area ChartColum
n Chart
Secondary AxisChart
s
Pivot Tables
Basics
A pivot table is a great reporting tool that sorts and sums independent of the
original data layout in the spreadsheet. It can automatically sort, count, and total
spreadsheet data and then create a second table to display the summarized data.
Once you have finished looking at the summarized data, you can quickly re-sort
your data and look at it from a totally different perspective, and all of this can be
done without using functions or formulas.
Introduction
Pivot Tables
Filtering
Field FilteringYear 2006Qtr Q 3 & Q 4Column Labels Direct Sales,
Internet, Retail
We may apply filtering on any one filter or more filters as below:
Pivot Tables
Options
Applying different options by right-click or Options Pivot Table Options
Pivot Tables
Options
Pivot Tables
Options
Pivot Tables
Options
Pivot Tables
Options
Pivot Tables
Options
Pivot Tables
Options
For instance Catering of Jan divided by Feb (72,000/75,990) X 100.
Pivot Tables
Grouping
Pivot Tables
Grouping
Pivot Tables
Grouping
Pivot Tables
Slicer
Facilitating &
Restricting Users
Data Validation (incl. Depended
Lists)
Controls (Combo Box, List Box,
Check Box, Option Button, Spin
Button, Scroll Bar)
G.R. No. Student Math English Urdu Islamiat Total %age Grade
Whole number between (minimum:) 0 & (maximum:) 100
To avoid duplicate entries: =COUNTIF($K$2:$K$13,K2)<2
Prepared by:
Name:
CNIC#:
Date:
Custom: =ISTEXT(L6)Text Length: equal to 15
Date: Between 1-Jan-2011 to Today()
List
Invoice Amount Paid AmountCustom:
<=Paid_Amt
Data Validation
Data Validation
Data
Country Pakistani Cities Indian CitiesBangladeshi
CitiesPakistan Faisalabad Agra BarisalIndia Hyderabad Ahmedabad Chittagong
Bangladesh Islamabad Chanai Comilla
Karachi Colcata DhakaLahore Goa Gazipur
Lodhran Jaipur KhulnaPeshawar Mumbai Rangpur
Quetta New Dehli
Sadiqabad PunaSukkur
Secondary lists
Primary list
Pakistan
India
Bangladesh
Name of Cell(s) containing cities of a country must be same as Country in Primary List (e.g. G4:G13 must be named as "Pakistan" instead of "Pak" because in the Primary List this is "Pakistan")
Data Validation
To be applied at City e.g. B4
Controls
Controls
=IF(M$38=TRUE,M18,"")
=IF($Q$50=5,M18,"")
Controls
=K60/100
=L63*M$60
=K72/100
=L75*M$72
Automating Your Repetitive TasksMacro (Intro, Developer Tab, Record, Run, View Code, Sort to get top 3, Methods to Call)
Macro
What is an Excel Macro?An excel macro is a series of keystrokes, mouse clicks, and other commands that you can record and reuse to save time. The next time you want to perform those steps, you can run the macro and then sit back while Microsoft Excel does the work for you. Suggestions before creating a macro
•Plan your macro•Initially you may use the copy of original work•Before record a macro - once perform desired tasks (as
rehearsal)•Initially it is advisable to use more than one small macros
instead of a single for all your tasks
Uses of Excel MacroEveryday users use the macro recorder to automate simple
tasksApplication developers create completely custom applications
Introduction
Macro
Developer Tab
Adding the Developer Tab in Excel 2007
Click on the Excel Options buttonClick on the Popular optionClick on the Show Developer Tab in the ribbonClick on OK
Macro
Record – Dialogue Box
Macro Name: Use naming conventions; invalid name if starts with digit or having space, “.”, punctuation marks
Shortcut Key: Assign a shortcut without conflict of built-in commands (may be any small/ capital letter or digit)
Store Macro in: 1. This workbook (The macro is
available only in this file.)2. New workbook (This option opens a
new Excel file. The macro is available only in this new file.)
3. Personal macro workbook(This option creates a hidden file Personal.xls which stores your macros and makes them available to you in all Excel files.)
Description - (optional)
Macro
Record – First Example
1. Click on the Home tab of the ribbon.
2. Select cells A1 to F1.
3. Click on the Merge and Center icon to center the title b/w A1 and F1.
4. Click on the Fill Color icon.
5. Choose Blue to turn the background color to blue.
6. Click on the Font Color icon.
7. Choose White to turn the text to white.
8. Click on the Font Size icon.
9. Choose 16 to change the size of text.
10. Click on the Developer tab of the ribbon.
11. Click the Stop Recording button on the ribbon.
Macro
Run – First Example
1. Click on the Sheet2 tab.
2. Click on cell A1.
3. Type the title: Profit & Loss Statement of April 2013.
4. Click on the Developer tab of the ribbon.
5. Click Macros button on the ribbon to bring up Macro dialog box.
Alternatively a short cut to call this dialogue box by applying Alt + F8.
6. Click on the format_titles macro in the Macro name window.
7. Click the Run button.
8. The steps of the macro should run automatically and apply the same
formatting steps applied to the title on sheet 1.
Macro
View Code – First Example
Unnecessary portion of code may be deleted instantly or after making them comments to read it conformably as well as to speed up it execution.
Shortcut to view code is
Alt +F11
Excel Macro-Enabled
Workbook supports a
workbook containing
macros
Macro
Sort to get top 3
G.R. No. Student Math English Urdu Islamiat Total %age Grade1021 Basheer 42 40 52 91 225 56% C1030 Bilal 47 55 99 80 281 70% A1074 Bushra 42 61 51 58 212 53% C1027 Chaman 34 36 38 33 141 35% Pass1017 Fawad 88 52 65 87 292 73% A1005 Hashim 74 42 34 96 246 62% B1004 Jamshed 81 54 - 25 160 40% Fail1054 Javaria 78 36 66 27 207 52% Fail1024 Junaid 36 75 90 82 283 71% A1043 Khalid 78 76 83 99 336 84% A+1046 Mehmood 5 27 43 81 156 39% Fail1065 Mubashir 42 47 52 40 181 45% D1053 Nasir 37 20 20 38 115 29% Fail1032 Rais 13 28 52 76 169 42% Fail1050 Rehana 73 36 54 62 225 56% C1084 Sikandar 37 31 - 39 107 27% Fail1036 Waqar 41 61 91 78 271 68% B1039 Zeeshan 67 29 79 99 274 69% B
This is our original
data which is sorted by
student name.
RequirementsWe need to extract top 3 students particulars in the following
manner:Position G.R. No. Name Total Grade1st 1043 Khalid 336A+2nd 1017 Fawad 292A3rd 1024 Junaid 283A
Macro
Sort to get top 3
1. Select Columns A to I
2. Data Sort Total Values Largest to Smallest
3. Select A2:B4; Copy and Paste to L2:M4
4. Select G2:G4; Copy and Paste Special to N2:N4
5. Select I2:I4; Copy and Paste Special to O2:O4
6. Select Columns A to I
7. Data Sort G.R. No. Values Smallest to Largest
Add a Button to Run Macro
Developer Insert Form Controls Button
Draw the Button
Right-Click Edit Text: Top 3 Students
Right-Click Assign Macro…
Select Macro Name OK
Macro Name: Top_3
Macro
Methods to Call Macro
Adding graphical aids to run macro:
Insert Clip Art Select any image Draw the image & place at a suitable
place Right-Click Assign Macro… Select Macro Name
Adding Button to Run Macro Developer Insert Form Controls Button
Draw the Button & place at a suitable place
Right-Click Edit Text
Right-Click Assign Macro… Select Macro Name OK
Now due to relative references we may apply these macros at any column
where similar kind of data (irrespective of size) with similar error may be
available there. So reach at respective columns heading and apply the
respective macro.
Questions & Answers