2010 Excel Functions Macros And Data Commands Manual As Of March

User Manual:

Open the PDF directly: View PDF .
Page Count: 108 [warning: Documents this large are best viewed by clicking the View PDF Link!]





MicrosoftExcel

Functions,Macros&

DataCommands

J.CarltonCollins,CPA

ASAResearch

Atlanta,Georgia

770.734.0950

Carlton@ASAResearch.com

SA Research

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage2CopyrightMay2010



TableofContents



Chapter1–ExcelFunctions..........................................................................4



Chapter2–TheIfFunction.........................................................................18



Chapter3–UsingFunctionstoClean&CrunchData.................................23



Chapter4–DataCommands......................................................................41



Chapter5‐Macros.....................................................................................76



Chapter6–Miscellaneous.........................................................................81



Chapter7–XMLData.................................................................................84



Chapter8–QuickTips................................................................................92



Chapter9–What’sNewinExcel2007.......................................................95



Chapter10–What’snewinExcel2010....................................................104



Appendix‐Instructor’sBiography............................................................108



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage3CopyrightMay2010



ExcelFunctions,Macros&DataCommands

CourseInformation



LearningObjectives ToincreasetheproductivityofaccountantsandCPAs

usingExcel’sfunctions,macros,&datacommands

CourseLevel Intermediate

Pre‐Requisites GoodFamiliaritywithMicrosoftExcel

AdvancedPreparation None

PresentationMethod Livelectureusingfullcolorprojectionsystemsand

liveInternetaccesswithfollowupcoursematerials

RecommendedCPECredit 8hours

Handouts Templates,checklists,webexamples,manual

Instructors J.CarltonCollins,CPA



AdvisorCPEisregisteredwiththeNationalAssociationof

StateBoardsofAccountancy(NASBA)asasponsorof

continuingprofessionaleducationontheNational

RegistryofCPESponsors.Stateboardsofaccountancy

havefinalauthorityontheacceptanceofindividual

coursesforCPEcredit.Complaintsregardingregistered

sponsorsmaybeaddressedtothenationalRegistryof

CPESponsors,150FourthAvenue,Nashville,TN,37219‐

2417.Telephone:615‐880‐4200.

4480MissendellLane,Norcross,Georgia30092770.734.0450



Allrightsreserved.Nopartofthispublicationmaybereproducedortransmittedinanyformwithouttheexpress

writtenconsentofAdvisorCPEorASAResearch,subsidiariesofAccountingSoftwareAdvisor,LLC.Requestmaybe

e‐mailedtomarylou@advisorcpe.comorfurtherinformationcanbeobtainedbycalling770.734.0450orby

accessingtheAdvisorCPEhomepageat:http://www.advisorcpe.com/



Alltradenamesandtrademarksusedinthesematerialsarethepropertyoftheirrespectivemanufacturersand/or

owners.Theuseoftradenamesandtrademarksusedinthesematerialsarenotintendedtoconveyendorsement

ofanyotheraffiliationswiththesematerials.Anyabbreviationsusedhereinaresolelyforthereader’s

convenienceandarenotintendedtocompromiseanytrademarks.Someofthefeaturesdiscussedwithinthis

manualapplyonlytocertainversionsofExcel,andfromtimetotime,Microsoftmightremovesomefunctionality.

MicrosoftExcelisknowntocontainnumeroussoftwarebugswhichmaypreventthesuccessfuluseofsome

featuresinsomecases.AdvisorCPEmakesnorepresentationsorwarrantywithrespecttothecontentsofthese

materialsanddisclaimsanyimpliedwarrantiesofmerchantabilityoffitnessforanyparticularuse.Thecontentsof

thesematerialsaresubjecttochangewithoutnotice.



ContactInformation:

J.CarltonCollins

carlton@asaresearch.com



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage4CopyrightMay2010



Chapter1

ExcelFunctions





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage5CopyrightMay2010



IntroductiontoExcelFunctions



ExcelFunctionsarepreprogrammedformulasthatmakethetaskofwritingcomplexformulas

easier.Thereareatotalof333functionsinExcel.Thesefunctionsareseparatedinto11

categoriesasfollows:



Categories&NumberOfFunctionsInEachCategory



1. DatabaseFunctions(12)

2. DateandTimeFunctions(20)

3. EngineeringFunctions(39)

4. FinancialFunctions(53)

5. InformationFunctions(17)

6. LogicalFunctions(6)

7. LookupandReferenceFunctions(18)

8. MathandTrigonometryFunctions(59)

9. StatisticalFunctions(80)

10. TextFunctions(27)

11. ExternalFunctions(2)



RelevancetoCPAs‐SomeExcelfunctionsaremorepowerfulthanothersandsomearemore

relevanttotheCPAthanothers.Forexample,mostCPAswillfindtheIF,SUM,COUNT,

SUBTOTAL,TEXT,andVLOOKUPareveryrelevanttotheCPAwhileotherengineeringand

trigonometryfunctionssuchasLOG,PI,RADIENS,DELTA,TAN,COMPLEX,andHAX2DECare

typicallylessrelevanttoCPAs.Ithasbeenmyexperiencethatthefollowing75functionsare

mostrelevanttotheCPA;thereforeinmyopinion,CPAswishingtoincreasetheircommandof

Excelfunctionsshouldconcentrateonthesefunctionsfirst.



Carlton’sListofTheTop75FunctionsMostRelevanttoCPAs

(SortedByCarlton’sOpinionofUsefulness)

OpenanExcelfileContainingFunctionExamples:

http://www.ASAResearch.com/web/functions.xlsx



1. IF‐Specifiesalogicaltesttoperform

2. SUM‐Addsitsarguments

3. SUBTOTAL‐Returnsasubtotalinalistordatabase

4. SUMIF‐Addsthecellsspecifiedbyagivencriteria

5. COUNT‐Countshowmanynumbersareinthelistofarguments

6. COUNTA‐Countshowmanyvaluesareinthelistofarguments

7. AVERAGE‐Returnstheaverageofarangeofnumbers

8. COUNTBLANK‐Countsthenumberofblankcellswithinarange

9. COUNTIF‐Countsthenumberofnonblankcellswithinarangethatmeetthegivencriteria

10. VALUE‐Convertstexttoanumber

11. TEXT‐Formatsanumberandconvertsittotext

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage6CopyrightMay2010



12. VLOOKUP‐Looksinthefirstcolumnofanarrayandmovesacrosstherowtoreturnthevalueof

acell

13. HLOOKUP‐Looksinthetoprowofanarrayandreturnsthevalueoftheindicatedcell

14. TWOWAYLOOKUP–UsingbothVLOOKUPandHLOOKUPtogether

15. LOOKUP‐Looksupvaluesinavectororarray

16. MATCH‐Looksupvaluesinareferenceorarray

17. TRIM‐Removesspacesfromtext

18. PROPER‐Capitalizesthefirstletterineachwordofatextvalue

19. LOWER‐Convertstexttolowercase

20. UPPER‐Convertstexttouppercase

21. LEFT‐Returnstheleftmostcharactersfromatextvalue

22. RIGHT‐Returnstherightmostcharactersfromatextvalue

23. MID‐Returnsaspecificnumberofcharactersfromatextstringstartingatthepositionyou

specify

24. FIND‐Findsonetextvaluewithinanother(case‐sensitive)

25. SUBSTITUTE‐Substitutesnewtextforoldtextinatextstring

26. LEN‐Returnsthenumberofcharactersinatextstring

27. REPLACE‐Replacestextinastringwithalternativetext

28. CONCATENATE‐Joinsseveraltextitemsintoonetextitem

29. CLEAN‐Removesallnonprintablecharactersfromtext

30. NOW‐Returnstheserialnumberofthecurrentdateandtime

31. TODAY‐Returnstheserialnumberoftoday'sdate

32. DATE‐Returnstheserialnumberofaparticulardate

33. MONTH‐Convertsaserialnumbertoamonth

34. DAY‐Convertsaserialnumbertoadayofthemonth

35. YEAR‐Convertsaserialnumbertoayear

36. WEEKDAY‐Convertsaserialnumbertoadayoftheweek

37. ROUND‐Roundsanumbertoaspecifiednumberofdigits

38. ROUNDDOWN‐Roundsanumberdown,towardzero

39. ROUNDUP‐Roundsanumberup,awayfromzero

40. MAX‐Returnsthemaximumvalueinalistofarguments

41. DMAX‐Returnsthemaximumvaluefromatablearraybasedonalistofarguments

42. MIN‐Returnstheminimumvalueinalistofarguments

43. DMIN‐Returnstheminimumvaluefromatablearraybasedonalistofarguments

44. MEDIAN‐Returnsthemedianofthegivennumbers

45. MODE‐Returnsthemostcommonvalueinadataset

46. PERCENTILE‐Returnsthek‐thpercentileofvaluesinarange

47. PERCENTRANK‐Returnsthepercentagerankofavalueinadataset

48. PMT‐Returnstheperiodicpaymentforanannuity

49. NPV‐Returnsthenetpresentvalueofaninvestmentbasedonaseriesofperiodiccashflows

andadiscountrate

50. DSUM‐Addsthenumbersinthefieldcolumnofrecordsinthedatabasethatmatchthecriteria

51. DCOUNT‐Countsthecellsthatcontainnumbersinadatabase

52. DCOUNTA‐Countsnonblankcellsinadatabase

53. AND‐ReturnsTRUEifallofitsargumentsareTRUE

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage7CopyrightMay2010



54. OR‐ReturnsTRUEifanyargumentisTRUE

55. CHOOSE‐Choosesavaluefromalistofvalues

56. TIME‐Returnstheserialnumberofaparticulartime

57. FV‐Returnsthefuturevalueofaninvestment

58. IRR‐Returnstheinternalrateofreturnforaseriesofcashflows

59. YIELD‐Returnstheyieldonasecuritythatpaysperiodicinterest

60. CELL‐Returnsinformationabouttheformatting,location,orcontentsofacell

61. INFO‐Returnsinformationaboutthecurrentoperatingenvironment

62. ERROR.TYPE‐Returnsanumbercorrespondingtoanerrortype

63. ISBLANK‐ReturnsTRUEifthevalueisblank

64. ISNA‐ReturnsTRUEifthevalueisthe#N/Aerrorvalue

65. GETPIVOTDATA‐ReturnsdatastoredinaPivotTable

66. HYPERLINK‐Createsashortcutorjumpthatopensadocumentstoredonanetworkserver,an

intranet,ortheInternet

67. TRANSPOSE‐Returnsthetransposeofanarray

68. ABS‐Returnstheabsolutevalueofanumber

69. RAND‐Returnsarandomnumberbetween0and1

70. RANDBETWEEN‐Returnsarandomnumberbetweenthenumbersyouspecify

71. REPT‐Repeatstextagivennumberoftimes

72. SLN‐Returnsthestraight‐linedepreciationofanassetforoneperiod

73. SYD‐Returnsthesum‐of‐years'digitsdepreciationofanassetforaspecifiedperiod

74. DDB‐Returnsthedoubledecliningbalancedepreciationofanassetforaspecifiedperiod

75. DGET‐Extractsfromadatabaseasinglerecordthatmatchesthespecifiedcriteria



'J.CarltonCollins,CPA‐Copyright,January2007,carlton@asaresearch.com

All333ExcelFunctions

OrganizedByCategory,IncludingADescriptionOfEachFunction



DatabaseFunctions

FunctionDescription

1DAVERAGEReturnstheaverageofselecteddatabaseentries

2DCOUNTCountsthecellsthatcontainnumbersinadatabase

3DCOUNTACountsnonblankcellsinadatabase

4DGETExtractsfromadatabaseasinglerecordthatmatchesthespecifiedcriteria

5DMAXReturnsthemaximumvaluefromselecteddatabaseentries

6DMINReturnstheminimumvaluefromselecteddatabaseentries

7DPRODUCTMultipliesthevaluesinaparticularfieldofrecordsthatmatchthecriteriain

adatabase

8DSTDEVEstimatesthestandarddeviationbasedonasampleofselecteddatabase

entries

9DSTDEVPCalculatesthestandarddeviationbasedontheentirepopulationofselected

databaseentries

10DSUMAddsthenumbersinthefieldcolumnofrecordsinthedatabasethatmatch

thecriteria

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage8CopyrightMay2010



11DVAREstimatesvariancebasedonasamplefromselecteddatabaseentries

12DVARPCalculatesvariancebasedontheentirepopulationofselecteddatabase

entries

 

DateandTimeFunctions

FunctionDescription

13DATEReturnstheserialnumberofaparticulardate

14DATEVALUEConvertsadateintheformoftexttoaserialnumber

15DAYConvertsaserialnumbertoadayofthemonth

16DAYS360Calculatesthenumberofdaysbetweentwodatesbasedona360‐dayyear

17EDATEReturnstheserialnumberofthedatethatistheindicatednumberofmonths

beforeorafterthestartdate

18EOMONTHReturnstheserialnumberofthelastdayofthemonthbeforeoraftera

specifiednumberofmonths

19HOURConvertsaserialnumbertoanhour

20MINUTEConvertsaserialnumbertoaminute

21MONTHConvertsaserialnumbertoamonth

22NETWORKDAYSReturnsthenumberofwholeworkdaysbetweentwodates

23NOWReturnstheserialnumberofthecurrentdateandtime

24SECONDConvertsaserialnumbertoasecond

25TIMEReturnstheserialnumberofaparticulartime

26TIMEVALUEConvertsatimeintheformoftexttoaserialnumber

27TODAYReturnstheserialnumberoftoday'sdate

28WEEKDAYConvertsaserialnumbertoadayoftheweek

29WEEKNUMConvertsaserialnumbertoanumberrepresentingwheretheweekfalls

numericallywithayear

30WORKDAYReturnstheserialnumberofthedatebeforeorafteraspecifiednumberof

workdays

31YEARConvertsaserialnumbertoayear

32YEARFRACReturnstheyearfractionrepresentingthenumberofwholedaysbetween

start_dateandend_date



EngineeringFunctions

FunctionDescription

33BESSELIReturnsthemodifiedBesselFunctionIn(x)

34BESSELJReturnstheBesselFunctionJn(x)

35BESSELKReturnsthemodifiedBesselFunctionKn(x)

36BESSELYReturnstheBesselFunctionYn(x)

37BIN2DECConvertsabinarynumbertodecimal

38BIN2HEXConvertsabinarynumbertohexadecimal

39BIN2OCTConvertsabinarynumbertooctal

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage9CopyrightMay2010



40COMPLEXConvertsrealandimaginarycoefficientsintoacomplexnumber

41CONVERTConvertsanumberfromonemeasurementsystemtoanother

42DEC2BINConvertsadecimalnumbertobinary

43DEC2HEXConvertsadecimalnumbertohexadecimal

44DEC2OCTConvertsadecimalnumbertooctal

45DELTATestswhethertwovaluesareequal

46ERFReturnstheerrorFunction

47ERFCReturnsthecomplementaryerrorFunction

48GESTEPTestswhetheranumberisgreaterthanathresholdvalue

49HEX2BINConvertsahexadecimalnumbertobinary

50HEX2DECConvertsahexadecimalnumbertodecimal

51HEX2OCTConvertsahexadecimalnumbertooctal

52IMABSReturnstheabsolutevalue(modulus)ofacomplexnumber

53IMAGINARYReturnstheimaginarycoefficientofacomplexnumber

54IMARGUMENTReturnstheargumenttheta,anangleexpressedinradians

55IMCONJUGATEReturnsthecomplexconjugateofacomplexnumber

56IMCOSReturnsthecosineofacomplexnumber

57IMDIVReturnsthequotientoftwocomplexnumbers

58IMEXPReturnstheexponentialofacomplexnumber

59IMLNReturnsthenaturallogarithmofacomplexnumber

60IMLOG10Returnsthebase‐10logarithmofacomplexnumber

61IMLOG2Returnsthebase‐2logarithmofacomplexnumber

62IMPOWERReturnsacomplexnumberraisedtoanintegerpower

63IMPRODUCTReturnstheproductoffrom2to29complexnumbers

64IMREALReturnstherealcoefficientofacomplexnumber

65IMSINReturnsthesineofacomplexnumber

66IMSQRTReturnsthesquarerootofacomplexnumber

67IMSUBReturnsthedifferencebetweentwocomplexnumbers

68IMSUMReturnsthesumofcomplexnumbers

69OCT2BINConvertsanoctalnumbertobinary

70OCT2DECConvertsanoctalnumbertodecimal

71OCT2HEXConvertsanoctalnumbertohexadecimal

 

FinancialFunctions

FunctionDescription

72ACCRINTReturnstheaccruedinterestforasecuritythatpaysperiodicinterest

73ACCRINTMReturnstheaccruedinterestforasecuritythatpaysinterestatmaturity

74AMORDEGRCReturnsthedepreciationforeachaccountingperiodbyusingadepreciation

coefficient

75AMORLINCReturnsthedepreciationforeachaccountingperiod

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage10CopyrightMay2010



76COUPDAYBSReturnsthenumberofdaysfromthebeginningofthecouponperiodtothe

settlementdate

77COUPDAYSReturnsthenumberofdaysinthecouponperiodthatcontainsthe

settlementdate

78COUPDAYSNCReturnsthenumberofdaysfromthesettlementdatetothenextcoupon

date

79COUPNCDReturnsthenextcoupondateafterthesettlementdate

80COUPNUMReturnsthenumberofcouponspayablebetweenthesettlementdateand

maturitydate

81COUPPCDReturnsthepreviouscoupondatebeforethesettlementdate

82CUMIPMTReturnsthecumulativeinterestpaidbetweentwoperiods

83CUMPRINCReturnsthecumulativeprincipalpaidonaloanbetweentwoperiods

84DBReturnsthedepreciationofanassetforaspecifiedperiodbyusingthefixed‐

decliningbalancemethod

85DDBReturnsthedepreciationofanassetforaspecifiedperiodbyusingthe

double‐decliningbalancemethodorsomeothermethodthatyouspecify

86DISCReturnsthediscountrateforasecurity

87DOLLARDEConvertsadollarprice,expressedasafraction,intoadollarprice,expressed

asadecimalnumber

88DOLLARFRConvertsadollarprice,expressedasadecimalnumber,intoadollarprice,

expressedasafraction

89DURATIONReturnstheannualdurationofasecuritywithperiodicinterestpayments

90EFFECTReturnstheeffectiveannualinterestrate

91FVReturnsthefuturevalueofaninvestment

92FVSCHEDULEReturnsthefuturevalueofaninitialprincipalafterapplyingaseriesof

compoundinterestrates

93INTRATEReturnstheinterestrateforafullyinvestedsecurity

94IPMTReturnstheinterestpaymentforaninvestmentforagivenperiod

95IRRReturnstheinternalrateofreturnforaseriesofcashflows

96ISPMTCalculatestheinterestpaidduringaspecificperiodofaninvestment

97MDURATIONReturnstheMacauleymodifieddurationforasecuritywithanassumedpar

valueof$100

98MIRRReturnstheinternalrateofreturnwherepositiveandnegativecashflowsare

financedatdifferentrates

99NOMINALReturnstheannualnominalinterestrate

100NPERReturnsthenumberofperiodsforaninvestment

101NPVReturnsthenetpresentvalueofaninvestmentbasedonaseriesofperiodic

cashflowsandadiscountrate

102ODDFPRICEReturnsthepriceper$100facevalueofasecuritywithanoddfirstperiod

103ODDFYIELDReturnstheyieldofasecuritywithanoddfirstperiod

104ODDLPRICEReturnsthepriceper$100facevalueofasecuritywithanoddlastperiod

105ODDLYIELDReturnstheyieldofasecuritywithanoddlastperiod

106PMTReturnstheperiodicpaymentforanannuity

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage11CopyrightMay2010



107PPMTReturnsthepaymentontheprincipalforaninvestmentforagivenperiod

108PRICEReturnsthepriceper$100facevalueofasecuritythatpaysperiodicinterest

109PRICEDISCReturnsthepriceper$100facevalueofadiscountedsecurity

110PRICEMATReturnsthepriceper$100facevalueofasecuritythatpaysinterestat

maturity

111PVReturnsthepresentvalueofaninvestment

112RATEReturnstheinterestrateperperiodofanannuity

113RECEIVEDReturnstheamountreceivedatmaturityforafullyinvestedsecurity

114SLNReturnsthestraight‐linedepreciationofanassetforoneperiod

115SYDReturnsthesum‐of‐years'digitsdepreciationofanassetforaspecified

period

116TBILLEQReturnsthebond‐equivalentyieldforaTreasurybill

117TBILLPRICEReturnsthepriceper$100facevalueforaTreasurybill

118TBILLYIELDReturnstheyieldforaTreasurybill

119VDBReturnsthedepreciationofanassetforaspecifiedorpartialperiodbyusing

adecliningbalancemethod

120XIRRReturnstheinternalrateofreturnforascheduleofcashflowsthatisnot

necessarilyperiodic

121XNPVReturnsthenetpresentvalueforascheduleofcashflowsthatisnot

necessarilyperiodic

122YIELDReturnstheyieldonasecuritythatpaysperiodicinterest

123YIELDDISCReturnstheannualyieldforadiscountedsecurity;forexample,aTreasurybill

124YIELDMATReturnstheannualyieldofasecuritythatpaysinterestatmaturity

 

InformationFunctions

FunctionDescription

125CELLReturnsinformationabouttheformatting,location,orcontentsofacell

126ERROR.TYPEReturnsanumbercorrespondingtoanerrortype

127INFOReturnsinformationaboutthecurrentoperatingenvironment

128ISBLANKReturnsTRUEifthevalueisblank

129ISERRReturnsTRUEifthevalueisanyerrorvalueexcept#N/A

130ISERRORReturnsTRUEifthevalueisanyerrorvalue

131ISEVENReturnsTRUEifthenumberiseven

132ISLOGICALReturnsTRUEifthevalueisalogicalvalue

133ISNAReturnsTRUEifthevalueisthe#N/Aerrorvalue

134ISNONTEXTReturnsTRUEifthevalueisnottext

135ISNUMBERReturnsTRUEifthevalueisanumber

136ISODDReturnsTRUEifthenumberisodd

137ISREFReturnsTRUEifthevalueisareference

138ISTEXTReturnsTRUEifthevalueistext

139NReturnsavalueconvertedtoanumber

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage12CopyrightMay2010



140NAReturnstheerrorvalue#N/A

141TYPEReturnsanumberindicatingthedatatypeofavalue

17

LogicalFunctions

FunctionDescription

142ANDReturnsTRUEifallofitsargumentsareTRUE

143FALSEReturnsthelogicalvalueFALSE

144IFSpecifiesalogicaltesttoperform

145NOTReversesthelogicofitsargument

146ORReturnsTRUEifanyargumentisTRUE

147TRUEReturnsthelogicalvalueTRUE

 

LookupandReferenceFunctions

FunctionDescription

148ADDRESSReturnsareferenceastexttoasinglecellinaworksheet

149AREASReturnsthenumberofareasinareference

150CHOOSEChoosesavaluefromalistofvalues

151COLUMNReturnsthecolumnnumberofareference

152COLUMNSReturnsthenumberofcolumnsinareference

153GETPIVOTDATAReturnsdatastoredinaPivotTable

154HLOOKUPLooksinthetoprowofanarrayandreturnsthevalueoftheindicatedcell

155HYPERLINKCreatesashortcutorjumpthatopensadocumentstoredonanetwork

server,anintranet,ortheInternet

156INDEXUsesanindextochooseavaluefromareferenceorarray

157INDIRECTReturnsareferenceindicatedbyatextvalue

158LOOKUPLooksupvaluesinavectororarray

159MATCHLooksupvaluesinareferenceorarray

160OFFSETReturnsareferenceoffsetfromagivenreference

161ROWReturnstherownumberofareference

162ROWSReturnsthenumberofrowsinareference

163RTDRetrievesreal‐timedatafromaprogramthatsupportsCOM

automation(Automation:Awaytoworkwithanapplication'sobjectsfrom

anotherapplicationordevelopmenttool.FormerlycalledOLEAutomation,

AutomationisanindustrystandardandafeatureoftheComponentObject

Model(COM).)

164TRANSPOSEReturnsthetransposeofanarray

165VLOOKUPLooksinthefirstcolumnofanarrayandmovesacrosstherowtoreturnthe

valueofacell

 

MathandTrigonometryFunctions

FunctionDescription

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage13CopyrightMay2010



166ABSReturnstheabsolutevalueofanumber

167ACOSReturnsthearccosineofanumber

168ACOSHReturnstheinversehyperboliccosineofanumber

169ASINReturnsthearcsineofanumber

170ASINHReturnstheinversehyperbolicsineofanumber

171ATANReturnsthearctangentofanumber

172ATAN2Returnsthearctangentfromx‐andy‐coordinates

173ATANHReturnstheinversehyperbolictangentofanumber

174CEILINGRoundsanumbertothenearestintegerortothenearestmultipleof

significance

175COMBINReturnsthenumberofcombinationsforagivennumberofobjects

176COSReturnsthecosineofanumber

177COSHReturnsthehyperboliccosineofanumber

178DEGREESConvertsradianstodegrees

179EVENRoundsanumberuptothenearesteveninteger

180EXPReturnseraisedtothepowerofagivennumber

181FACTReturnsthefactorialofanumber

182FACTDOUBLEReturnsthedoublefactorialofanumber

183FLOORRoundsanumberdown,towardzero

184GCDReturnsthegreatestcommondivisor

185INTRoundsanumberdowntothenearestinteger

186LCMReturnstheleastcommonmultiple

187LNReturnsthenaturallogarithmofanumber

188LOGReturnsthelogarithmofanumbertoaspecifiedbase

189LOG10Returnsthebase‐10logarithmofanumber

190MDETERMReturnsthematrixdeterminantofanarray

191MINVERSEReturnsthematrixinverseofanarray

192MMULTReturnsthematrixproductoftwoarrays

193MODReturnstheremainderfromdivision

194MROUNDReturnsanumberroundedtothedesiredmultiple

195MULTINOMIALReturnsthemultinomialofasetofnumbers

196ODDRoundsanumberuptothenearestoddinteger

197PIReturnsthevalueofpi

198POWERReturnstheresultofanumberraisedtoapower

199PRODUCTMultipliesitsarguments

200QUOTIENTReturnstheintegerportionofadivision

201RADIANSConvertsdegreestoradians

202RANDReturnsarandomnumberbetween0and1

203RANDBETWEENReturnsarandomnumberbetweenthenumbersyouspecify

204ROMANConvertsanarabicnumeraltoroman,astext

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage14CopyrightMay2010



205ROUNDRoundsanumbertoaspecifiednumberofdigits

206ROUNDDOWNRoundsanumberdown,towardzero

207ROUNDUPRoundsanumberup,awayfromzero

208SERIESSUMReturnsthesumofapowerseriesbasedontheformula

209SIGNReturnsthesignofanumber

210SINReturnsthesineofthegivenangle

211SINHReturnsthehyperbolicsineofanumber

212SQRTReturnsapositivesquareroot

213SQRTPIReturnsthesquarerootof(number*pi)

214SUBTOTALReturnsasubtotalinalistordatabase

215SUMAddsitsarguments

216SUMIFAddsthecellsspecifiedbyagivencriteria

217SUMPRODUCTReturnsthesumoftheproductsofcorrespondingarraycomponents

218SUMSQReturnsthesumofthesquaresofthearguments

219SUMX2MY2Returnsthesumofthedifferenceofsquaresofcorrespondingvaluesintwo

arrays

220SUMX2PY2Returnsthesumofthesumofsquaresofcorrespondingvaluesintwoarrays

221SUMXMY2Returnsthesumofsquaresofdifferencesofcorrespondingvaluesintwo

arrays

222TANReturnsthetangentofanumber

223TANHReturnsthehyperbolictangentofanumber

224TRUNCTruncatesanumbertoaninteger



StatisticalFunctions

FunctionDescription

225AVEDEVReturnstheaverageoftheabsolutedeviationsofdatapointsfromtheir

mean

226AVERAGEReturnstheaverageofitsarguments

227AVERAGEAReturnstheaverageofitsarguments,includingnumbers,text,andlogical

values

228BETADISTReturnsthebetacumulativedistributionFunction

229BETAINVReturnstheinverseofthecumulativedistributionFunctionforaspecified

betadistribution

230BINOMDISTReturnstheindividualtermbinomialdistributionprobability

231CHIDISTReturnstheone‐tailedprobabilityofthechi‐squareddistribution

232CHIINVReturnstheinverseoftheone‐tailedprobabilityofthechi‐squared

distribution

233CHITESTReturnsthetestforindependence

234CONFIDENCEReturnstheconfidenceintervalforapopulationmean

235CORRELReturnsthecorrelationcoefficientbetweentwodatasets

236COUNTCountshowmanynumbersareinthelistofarguments

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage15CopyrightMay2010



237COUNTACountshowmanyvaluesareinthelistofarguments

238COUNTBLANKCountsthenumberofblankcellswithinarange

239COUNTIFCountsthenumberofnonblankcellswithinarangethatmeetthegiven

criteria

240COVARReturnscovariance,theaverageoftheproductsofpaireddeviations

241CRITBINOMReturnsthesmallestvalueforwhichthecumulativebinomialdistributionis

lessthanorequaltoacriterionvalue

242DEVSQReturnsthesumofsquaresofdeviations

243EXPONDISTReturnstheexponentialdistribution

244FDISTReturnstheFprobabilitydistribution

245FINVReturnstheinverseoftheFprobabilitydistribution

246FISHERReturnstheFishertransformation

247FISHERINVReturnstheinverseoftheFishertransformation

248FORECASTReturnsavaluealongalineartrend

249FREQUENCYReturnsafrequencydistributionasaverticalarray

250FTESTReturnstheresultofanF‐test

251GAMMADISTReturnsthegammadistribution

252GAMMAINVReturnstheinverseofthegammacumulativedistribution

253GAMMALNReturnsthenaturallogarithmofthegammaFunction,Γ(x)

254GEOMEANReturnsthegeometricmean

255GROWTHReturnsvaluesalonganexponentialtrend

256HARMEANReturnstheharmonicmean

257HYPGEOMDISTReturnsthehypergeometricdistribution

258INTERCEPTReturnstheinterceptofthelinearregressionline

259KURTReturnsthekurtosisofadataset

260LARGEReturnsthek‐thlargestvalueinadataset

261LINESTReturnstheparametersofalineartrend

262LOGESTReturnstheparametersofanexponentialtrend

263LOGINVReturnstheinverseofthelognormaldistribution

264LOGNORMDISTReturnsthecumulativelognormaldistribution

265MAXReturnsthemaximumvalueinalistofarguments

266MAXAReturnsthemaximumvalueinalistofarguments,includingnumbers,text,

andlogicalvalues

267MEDIANReturnsthemedianofthegivennumbers

268MINReturnstheminimumvalueinalistofarguments

269MINAReturnsthesmallestvalueinalistofarguments,includingnumbers,text,and

logicalvalues

270MODEReturnsthemostcommonvalueinadataset

271NEGBINOMDISTReturnsthenegativebinomialdistribution

272NORMDISTReturnsthenormalcumulativedistribution

273NORMINVReturnstheinverseofthenormalcumulativedistribution

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage16CopyrightMay2010



274NORMSDISTReturnsthestandardnormalcumulativedistribution

275NORMSINVReturnstheinverseofthestandardnormalcumulativedistribution

276PEARSONReturnsthePearsonproductmomentcorrelationcoefficient

277PERCENTILEReturnsthek‐thpercentileofvaluesinarange

278PERCENTRANKReturnsthepercentagerankofavalueinadataset

279PERMUTReturnsthenumberofpermutationsforagivennumberofobjects

280POISSONReturnsthePoissondistribution

281PROBReturnstheprobabilitythatvaluesinarangearebetweentwolimits

282QUARTILEReturnsthequartileofadataset

283RANKReturnstherankofanumberinalistofnumbers

284RSQReturnsthesquareofthePearsonproductmomentcorrelationcoefficient

285SKEWReturnstheskewnessofadistribution

286SLOPEReturnstheslopeofthelinearregressionline

287SMALLReturnsthek‐thsmallestvalueinadataset

288STANDARDIZEReturnsanormalizedvalue

289STDEVEstimatesstandarddeviationbasedonasample

290STDEVAEstimatesstandarddeviationbasedonasample,includingnumbers,text,and

logicalvalues

291STDEVPCalculatesstandarddeviationbasedontheentirepopulation

292STDEVPACalculatesstandarddeviationbasedontheentirepopulation,including

numbers,text,andlogicalvalues

293STEYXReturnsthestandarderrorofthepredictedy‐valueforeachxinthe

regression

294TDISTReturnstheStudent'st‐distribution

295TINVReturnstheinverseoftheStudent'st‐distribution

296TRENDReturnsvaluesalongalineartrend

297TRIMMEANReturnsthemeanoftheinteriorofadataset

298TTESTReturnstheprobabilityassociatedwithaStudent'st‐test

299VAREstimatesvariancebasedonasample

300VARAEstimatesvariancebasedonasample,includingnumbers,text,andlogical

values

301VARPCalculatesvariancebasedontheentirepopulation

302VARPACalculatesvariancebasedontheentirepopulation,includingnumbers,text,

andlogicalvalues

303WEIBULLReturnstheWeibulldistribution

304ZTESTReturnstheone‐tailedprobability‐valueofaz‐test

 

TextFunctions

FunctionDescription

305ASCChangesfull‐width(double‐byte)Englishlettersorkatakanawithina

characterstringtohalf‐width(single‐byte)characters

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage17CopyrightMay2010



306BAHTTEXTConvertsanumbertotext,usingtheß(baht)currencyformat

307CHARReturnsthecharacterspecifiedbythecodenumber

308CLEANRemovesallnonprintablecharactersfromtext

309CODEReturnsanumericcodeforthefirstcharacterinatextstring

310CONCATENATEJoinsseveraltextitemsintoonetextitem

311DOLLARConvertsanumbertotext,usingthe$(dollar)currencyformat

312EXACTCheckstoseeiftwotextvaluesareidentical

313FIND,FINDBFindsonetextvaluewithinanother(case‐sensitive)

314FIXEDFormatsanumberastextwithafixednumberofdecimals

315JISChangeshalf‐width(single‐byte)Englishlettersorkatakanawithinacharacter

stringtofull‐width(double‐byte)characters

316LEFT,LEFTBReturnstheleftmostcharactersfromatextvalue

317LEN,LENBReturnsthenumberofcharactersinatextstring

318LOWERConvertstexttolowercase

319MID,MIDBReturnsaspecificnumberofcharactersfromatextstringstartingatthe

positionyouspecify

320PHONETICExtractsthephonetic(furigana)charactersfromatextstring

321PROPERCapitalizesthefirstletterineachwordofatextvalue

322REPLACE,

REPLACEB

Replacescharacterswithintext

323REPTRepeatstextagivennumberoftimes

324RIGHT,RIGHTBReturnstherightmostcharactersfromatextvalue

325SEARCH,

SEARCHB

Findsonetextvaluewithinanother(notcase‐sensitive)

326SUBSTITUTESubstitutesnewtextforoldtextinatextstring

327TConvertsitsargumentstotext

328TEXTFormatsanumberandconvertsittotext

329TRIMRemovesspacesfromtext

330UPPERConvertstexttouppercase

331VALUEConvertsatextargumenttoanumber

 

ExternalFunctions

FunctionDescription

332EUROCONVERTConvertsanumbertoeuros,convertsanumberfromeurostoaeuro

membercurrency,orconvertsanumberfromoneeuromembercurrencyto

anotherbyusingtheeuroasanintermediary(triangulation)

333SQL.REQUESTConnectswithanexternaldatasourceandrunsaqueryfromaworksheet,

thenreturnstheresultasanarraywithouttheneedformacroprogramming





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage18CopyrightMay2010



Chapter2

The=IFFunction





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage19CopyrightMay2010



=IF



The“IF”functionisthemostpowerfulofallfunctions–notjustinExcel,butinany

programminglanguage.Commonlyreferredtoas“ConditionalProgramming”,itistheIF

functionthatenablesustointroducelogicalthinkingintoanyprogram.Thisfunctionisalso

referredtoasthe“If‐Then‐Else”command,“conditionalexpressions”,or“PropositionalLogic”.

ThefollowingWikisexplainsthisconceptinmoredetail:



http://en.wikipedia.org/wiki/Conditional_(programming).

http://en.wikipedia.org/wiki/Logical_conditional#Conditional_statements



ThecleverCPAcanusetheIFFunctiontobuildelaborateExceltemplatesandfinancialmodels

containinganalmostunlimitedamountofsophisticatedprogramming.Presentedbeloware

severalexamplestohelpyoubetterunderstandtheapplicationofthispowerfultool.



SimpleIF‐TheIFfunctionreturnsonevalueifaconditionyouspecifyevaluatestoTRUE,and

anothervalueifthatconditionevaluatestoFALSE.Presentedbelowisasimpleexample:



SimpleIFwithCalculation–Presentedbelowisanexamplethatisalittlemorecomplex:



SimpleIF–LargerExample‐PresentedbelowisyetanotherIFexampleonalittlelargerscale–

thisexampleshowshowonemightapplytheIFfunctiontoevaluatebudgetversusactual

comparisons.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage20CopyrightMay2010



SimpleIFwithDropDown‐Inthefollowingexample,theIFfunctionischeckingtoseeifthey

havesignedupforinsurance.Iftheyhave,thedeductionamountisentered.







ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage21CopyrightMay2010



SimpleIFwithCalculation‐ThenextIFfunctionexampleisdeterminingeachemployees

earnedvacationdays.Iftheyhaveworkedformorethanayear,theyhaveearned5vacation

daysplusonedayforeachadditionalfullyear. 









NestedIFFunctions‐Inthissample,therearefourpossibilitiesforbonuses.



IFFunctionwithLogicalORArgument‐TeamsAandCmeetonTuesday,TeamsBandDmeet

onThursday.WewanttolistthemeetingdaysincolumnD.





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage22CopyrightMay2010



MoreComplexIFFunctionExample‐ThefollowingIFexampleshowsamorecomplex

applicationinwhichtheuserselectsataxpayerstatusfromadropdownlist,whichthen

retrievesthecorrecttaxbase,threshold,andincrementaltaxratestobeusedincalculatingtax.



ThisexampleillustrateshowaCPAmightprepareanincomestatementtemplatethat

calculatestheappropriateamountoftaxesasnetincomeandthetaxpayerstatuschanges.

Essentiallythetemplatecalculatesthecorrecttaxgivenallfourpossibletaxpayerstatuses,and

theIFstatementsareusedtoselectthecorrectanswersbasedonthetaxpayerstatusselected.



Keepinmindthatdespitethemanyaccoladesmentionedabove,theIFFunctionisnotalways

thebestsolution.Forexample,theVLOOKUPwouldbeabetterandeasierfunctiontouseto

extractdatafromalistasshowninthenestedIFFunctionafewexamplesabove.ManyExcel

Functionsalsoprovidebuilt‐in“IF‐Then‐Else”functionality.



KeyPointersforUsingtheIFFunction:



1. Nesting–Youcanembedupto8nestedIFfunctionsinasingleformulainExcel2003,andupto

64IFnestedfunctionsinExcel2007.

2. AND,OR–YoucanusetheANDand/orORoperatortoaddmoreconditionstoanIFFunction.

3. VariationsofIF‐ExceloffersseveralvariationsoftheIFfunctionasfollows:COUNTIF,

COUNTIFS,SUMIF,SUMIFS.

4. EvaluatinganIFError–SincetheIFstatementprovidesonlyatrueorfalseresult,thereisno

waytoevaluateanIFFunctiontoERROR.Ifyoureceiveanerror,youwrotetheformulawrong.

5. TheNullSet‐TheDoubleQuotesistheNullSet,orabsenceofavalue.Forexample,when

testingforaZerobalanceortestingforablankcell,thefollowingIFfunctionswouldapply:

a. =IF(A1=0,”ZERO”,””)

b. =IF(A1=””,”Blank”,””)

(ExcelalsoprovidesanISBLANKFunctionthatwouldalsowork.)



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage23CopyrightMay2010



Chapter3

UsingFunctionsTo

Crunch&CleanData





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage24CopyrightMay2010



CleaningDataUsingFunctions



CPAsoftenreceiveorretrievedatafrommanysourcesinawidevarietyofformatssuchasText

orCSVformats.Youdon'talwayshavecontrolovertheformatandtypeofdatathatyouimport

fromanexternaldatasource,suchasadatabase,textfile,oraWebpage.Beforeyoucan

analyzethedata,youoftenneedtocleanitup.Fortunately,OfficeExcelhasmanyfeaturesto

helpyougetdatainthepreciseformatthatyouwant.Sometimes,thetaskisstraightforward

andthereisaspecificfeaturethatdoesthejobforyou.



Forexample,youcaneasilyuseSpellCheckertocleanupmisspelledwordsincolumnsthat

containcommentsordescriptions.Or,ifyouwanttoremoveduplicaterows,youcanquicklydo

thisbyusingtheRemoveDuplicatesdialogbox.Atothertimes,youmayneedtomanipulate

oneormorecolumnsbyusingaformulatoconverttheimportedvaluesintonewvalues.



Forexample,ifyouwanttoremovetrailingspaces,youcancreateanewcolumntocleanthe

databyusingaformula,fillingdownthenewcolumn,convertingthatnewcolumn'sformulas

tovalues,andthenremovingtheoriginalcolumn.Excelprovidesmanyfunctionstohelpyou

cleanyourdataasfollows:



1. Import

2. TexttoColumns

3. RemoveDuplicates

4. Find&Replace

5. SpellCheck

6. =UPPER

7. =LOWER

8. =PROPER

9. =FIND

10. =SEARCH

11. =LEN

12. =SUBSTITUTE

13. =REPLACE

14. =LEFT

15. =MID

16. =RIGHT

17. =VALUE

18. =CONCATENATE

19. =TEXT

20. =TRIM

21. =CLEAN

22. =FIXED

23. =DOLLAR

24. =CODE

25. Macros



1. ImportingDataintoExcel–Ofcourseexcelopensupexcelfiles,butwhathappenswhen

youattempttoopendatathatisnotcontainedinanExcelformat?TheansweristhatExcel

automaticallyimportsthatdataontheflyanddisplaysaImportWizardtohelpyou

completetheprocess.TheTextImportWizardexaminesthetextfilethatyouareimporting

andhelpsyouimportthedatathewaythatyouwant.TostarttheTextImportWizard,on

theDatatab,intheGetExternalDatagroup,clickFromText.Then,intheImportTextFile

dialogbox,double‐clickthetextfilethatyouwanttoimport.Thefollowingdialogboxwill

bedisplayed:



Ifitemsinthetextfileareseparatedbytabs,colons,semicolons,spaces,orother

characters,selectDelimited.Ifalloftheitemsineachcolumnarethesamelength,select

Fixedwidth.Instep3,clicktheAdvancedbuttontospecifythatoneormorenumericvalues

maycontainatrailingminussign.Alsoclickthedesireddataformatforeachcolumntobe

imported.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage26CopyrightMay2010



2. TexttoColumns–TheTexttoColumnscommandlocatedontheDataRibbonworksexactly

thesamewayasdescribedabove–theusersimplylaunchesittoconvertdatawithinan

existingworksheet.



3. RemovingDuplicateRows‐Duplicaterowsareacommonproblemwhenyouimportdata.

YoucanidentifyandremoveduplicaterowsbyusingtheData,AdvancedFilter,Unique

RecordsOnlytoolasshowinthescreenbelow.



4. FindandReplaceText–Thistoolcanbeusedtoidentifyandremoveleadingstring,suchas

alabelfollowedbyacolonandspace,orasuffix,suchasaparentheticphraseattheendof

thestringthatisobsoleteorunnecessary.Youcandothisbyfindinginstancesofthattext

andthenreplacingitwithnotextorothertext.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage27CopyrightMay2010



NoteworthyFindandReplacePoints:



1. Youcansearchandreplaceforanentireworksheet,ortheentireworkbook.

2. Youcanfindandreplaceformatswithnewformats.

3. Thereisacellchooseroptionthatmakesiteasiertofindandreplaceformats.

4. Ifyouhighlightarangeofcells,thensearchandreplaceonlysearchesandreplaces

withinthatrangeofcells.

5. Youcanreplaceallatonceoroneatatime.

6. Youcouldalsofindandreplacereferencesinaformula.



5. SpellCheck‐Youcanuseaspellcheckertonotonlyfindmisspelledwords,buttofindvaluesthat

arenotusedconsistently,suchasproductorcompanynames,byaddingthosevaluestoacustom

dictionary.Thespellcheckfunctionalsochecksyourgrammaraswell.



ChangingTheCaseOfText–YoucanuseoneormoreofthethreeCasefunctionsto

converttexttolowercaseletters,suchase‐mailaddresses,uppercaseletters,suchas

productcodes,orpropercase,suchasnamesorbooktitles.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage28CopyrightMay2010

6. =UPPER‐Convertstexttouppercaseletters.



7. =LOWER‐Convertsalluppercaselettersinatextstringtolowercaseletters.



8. =PROPER‐Capitalizesthefirstletterinatextstringandanyotherlettersintextthatfollowany

characterotherthanaletter.Convertsallotherletterstolowercaseletters.



MergingAndSplittingColumns‐Acommontaskafterimportingdatafromanexternaldata

sourceistoeithermergetwoormorecolumnsintoone,orsplitonecolumnintotwoor

morecolumns.Forexample,youmaywanttosplitacolumnthatcontainsafullnameintoa

firstandlastname.Or,youmaywanttosplitacolumnthatcontainsanaddressfieldinto

separatestreet,city,region,andpostalcodecolumns.Thereversemayalsobetrue.

Presentedbelowarefunctionsthattohelpyouaccomplishthesetasks:



9. =FIND–UseReturnsthestartingpositionofacharacter,stringofcharactersorwordwithacell.

Findiscasesensitive.



10. =SEARCH–Returnsthestartingpositionofacharacter,stringofcharactersorwordwithacell.

Searchisnotcasesensitive.



11. =LEN–Displaysthelengthornumberofcharactersinacell.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage29CopyrightMay2010



12. =SUBSTITUTE–Replacesacharacterorcharacterswithacharacterorcharactersthatyouspecify.



13. =REPLACE‐Replacesacharacterorcharacterswithacharacterorcharactersthatyouspecify.



14. =LEFT–Extractsthespecifiednumberofcharactersfromacell,startingfromtheleft.



15. =MID–Extractsthespecifiednumberofcharactersfromacell,startingfromsomewhereinthe

middleofthecell.



16. =RIGHT–Extractsthespecifiednumberofcharactersfromacell,startingfromtheright.



17. =Value–Convertstexttovaluessothedatacanbeadded,subtracted,multiplied,dividedor

referencedinafunction.



18. =CONCATENATE‐Joinstwoormoretextstringsintoonetextstring.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage30CopyrightMay2010



Variationsofthesefunctionsthatareusedwhenworkingwithforeignlanguages:



=FINDB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=SEARCHB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=REPLACEB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=LEFTB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=RIGHTB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=LENB–Usethiswhenworkingwithforeigncharacterslikethese("," )

=MIDB–Usethiswhenworkingwithforeigncharacterslikethese("," )



CleaningText–(RemovingSpacesAndNonprintingCharactersFromText)‐Sometimes

textvaluescontainleading,trailing,ormultipleembeddedspacecharacters(Unicode

charactersetvalues32and160),ornonprintingcharacters(Unicodecharactersetvalues0

to31,127,129,141,143,144,and157).Thesecharacterscansometimescauseunexpected

resultswhenyousort,filter,orsearch.Forexample,intheexternaldatasource,usersmay

maketypographicalerrorsbyinadvertentlyaddingextraspacecharacters,orimportedtext

datafromexternalsourcesmaycontainnonprintingcharactersthatareembeddedinthe

text.Becausethesecharactersarenoteasilynoticed,theunexpectedresultsmaybe

difficulttounderstand.Followingisalistoffunctionsyoucanusetoremovethese

unwantedcharacters:



19. =TEXT‐Convertsavaluetotextinaspecificnumberformat.



20. =TRIM‐Removesthe7‐bitASCIIspacecharacter(value32)fromtext.



21. =CLEAN‐Removesthefirst32nonprintingcharactersinthe7‐bitASCIIcode(values0

through31)fromtext.

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage31CopyrightMay2010



22. =FIXED‐Roundsanumbertothespecifiednumberofdecimals,formatsthenumberin

decimalformatbyusingaperiodandcommas,andreturnstheresult.



23. =DOLLAR‐Convertsanumbertotextformatandappliesacurrencysymbol.



24. =CODE‐Returnsanumericcodeforthefirstcharacterinatextstring.



FixingDatesandTimes‐Therearemanydifferentdateformats,andthesevariedformats

maybeconfusedwithnumberedpartcodesorotherstringsthatcontainslashmarksor

hyphens,datesandtimesoftenneedtobeconvertedandreformatted.Presentedbelowis

alistoffunctionsthathelpyouaccomplishthistask.



25. =DATE‐Returnsthesequentialserialnumberthatrepresentsaparticulardate.Ifthecellformat

wasGeneralbeforethefunctionwasentered,theresultisformattedasadate.



26. =DATEVALUE‐Convertsadaterepresentedbytexttoaserialnumber.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage32CopyrightMay2010



27. =TIME‐ Returnsthedecimalnumberforaparticulartime.IfthecellformatwasGeneral

beforethefunctionwasentered,theresultisformattedasadate.



28. =TIMEVALUE‐ Returnsthedecimalnumberofthetimerepresentedbyatextstring.The

decimalnumberisavaluerangingfrom0(zero)to0.99999999,representingthetimes

from0:00:00(12:00:00AM)to23:59:59(11:59:59P.M.).



TransformingAndRearrangingColumnsAndRows‐Mostoftheanalysisandformatting

featuresinOfficeExcelassumethatthedataexistsinasingle,flattwo‐dimensionaltable.

Sometimesyoumaywanttomaketherowsbecomecolumns,andthecolumnsbecome

rows.Atothertimes,dataisnotevenstructuredinatabularformat,andyouneedawayto

transformthedatafromanontabulartoatabularformat.Thefollowingfunctioncanhelp

youachievethisgoal:



29. =TRANSPOSE‐Returnsaverticalrangeofcellsasahorizontalrange,orviceversa.



30. DataFillInTrick–AclevertrickforfillinginmissingdatacanbeaccomplishedusingtheGOTO,

Special,Blankscommand.Hereishowitworks.Thistrickworkswellwhenyouhavealargevolume

ofdatabutdescriptionsarenotprovidedforeveryrow,asshownintheexamplebelow:



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage33CopyrightMay2010



Startbyenteringasimpleformulareferencingthedatalabelintheabovecell,justlikethis:



a. Nextcopythatformula...

b. HighlighttheentirerangecontainingdatalabelsincolumnsAandB.columns...

c. PresstheF5keytolaunchtheGoTodialogbox...

d. SelecttheOptionsBox...

e. Clickonthe“Blanks”radiobutton...

f. PressEnter...

g. Paste.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage34CopyrightMay2010



Thisactionwillcausealldatalabelstorepeatintheemptycellsbeneath.Next:



h. CopycolumnsA&B...

i. PasteSpecialasvaluestoconverttheformulastotextbaseddatalabels...

j. Youarenowreadytosort,filter,subtotalandpivotyourdata.



FetchingData‐Occasionally,databaseadministratorsuseOfficeExceltofindandcorrect

matchingerrorswhentwoormoretablesarejoined.Thismightinvolvereconcilingtwo

tablesfromdifferentworksheets,forexample,toseeallrecordsinbothtablesorto

comparetablesandfindrowsthatdon'tmatch.



31. =VLOOKUP‐Searchesforavalueinthefirstcolumnofatablearrayandreturnsavaluein

thesamerowfromanothercolumninthetablearray.Forexample,considertheexample

belowwhichusesa=VLOOKUPfunctiontocalculatetheappropriateamountoftaxdue

basedontheIRSrateschedule.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage35CopyrightMay2010



AstheIncomestatementshownintheshadedareaisupdated,theresultingtaxable

incomeamountisreferencedinCellF13.Next,3VLOOKUPfunctionspulltheappropriate

rate,baseandthresholdinformationfromtheratescheduletobeusedincalculating

incometax.Oncecalculated,theresultingtaxisreferencedbacktotheincomestatement

forthepurposesofcomputingNetincomeAftertaxes.



KeypointstoConsiderwhenUsingVLOOKUP:



a. Ifyouarelookingupbasedontext,thefirstcolumncontaininglookup

valuesmustbesortedalphabeticallyindescendingorder–elseitwillnot

workproperly.



b. Ifyouarelookingupbasedontext,youmusthaveanexactmatchbetween

thelookupvalueandthetablearrayvalue.



c. Ifyouarelookingupbasedonvalues,thefirstcolumncontaininglookup

valuesmustbesortednumericallyindescendingorder–elseitwillnotwork

properly.



d. Ifyouarelookingupbasedonvalues,thenExcelwillchoosetheclosest

valuewithoutgoingover.Forexample,ifthelookupvalueis198,000and

thetablearraycontainsvaluesof100,000and200,000,thenexcelwill

choose100,000because200,000goesoverorexceeds198,000.(Itmightbe

helpfultothinkbacktotheoldBobbarkergameshowthePriceisRight.)



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage36CopyrightMay2010



32. =HLOOKUP‐Searchesforavalueinthetoprowofatableoranarrayofvalues,andthen

returnsavalueinthesamecolumnfromarowyouspecifyinthetableorarray.



33. =INDEX‐Returnsavalueorthereferencetoavaluefromwithinatableorrange.Thereare

twoformsoftheINDEXfunction:thearrayformandthereferenceform.



34. =MATCH‐Returnstherelativepositionofaniteminanarraythatmatchesaspecified

valueinaspecifiedorder.UseMATCHinsteadofoneoftheLOOKUPfunctionswhenyou

needthepositionofaniteminarangeinsteadoftheitemitself.



35. =OFFSET‐Returnsareferencetoarangethatisaspecifiednumberofrowsandcolumns

fromacellorrangeofcells.Thereferencethatisreturnedcanbeasinglecellorarangeof

cells.Youcanspecifythenumberofrowsandthenumberofcolumnstobereturned.



36. DataCleaningwithMacros‐Toperiodicallycleanthesamedatasource,considerrecording

amacroorwritingcodetoautomatetheentireprocess.Therearealsoanumberof

externaladd‐inswrittenbythird‐partyvendors,listedintheThird‐partyproviderssection,

thatyoucanconsiderusingifyoudon'thavethetimeorresourcestoautomatetheprocess

onyourown.



37. RAND(),RANDBETWEEN(),ROUND()–InExcel2003,RANDBETWEENisnotinthe

standardEXCELinstallationbutiftheanalysistoolpackisinstalledandtheadd‐inactivated

itisanextremelyusefulfunction.



ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage37CopyrightMay2010



38. InformationalFunctions

CELL(info_type,reference)‐Info_type isatextvaluethatspecifieswhattypeofcell

informationyouwant.Thefollowinglistshowsthepossiblevaluesofinfo_typeandthe

correspondingresults.

Info_typeReturns

"address"Referenceofthefirstcellinreference,astext.

"col"Columnnumberofthecellinreference.

"color"1ifthecellisformattedincolorfornegativevalues;otherwisereturns0

(zero).

"contents"Valueoftheupper‐leftcellinreference;notaformula.

"filename"Filename(includingfullpath)ofthefilethatcontainsreference,astext.

Returnsemptytext("")iftheworksheetthatcontainsreferencehasnotyet

beensaved.

"format"Textvaluecorrespondingtothenumberformatofthecell.Thetextvalues

forthevariousformatsareshowninthefollowingtable.Returns"‐"atthe

endofthetextvalueifthecellisformattedincolorfornegativevalues.

Returns"()"attheendofthetextvalueifthecellisformattedwith

parenthesesforpositiveorallvalues.

"parentheses"1ifthecellisformattedwithparenthesesforpositiveorallvalues;

otherwisereturns0.

"prefix"Textvaluecorrespondingtothe"labelprefix"ofthecell.Returnssingle

quotationmark(')ifthecellcontainsleft‐alignedtext,doublequotation

mark(")ifthecellcontainsright‐alignedtext,caret(^)ifthecellcontains

centeredtext,backslash(\)ifthecellcontainsfill‐alignedtext,andempty

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage38CopyrightMay2010

text("")ifthecellcontainsanythingelse.

"protect"0ifthecellisnotlocked,and1ifthecellislocked.

"row"Rownumberofthecellinreference.

"type"Textvaluecorrespondingtothetypeofdatainthecell.Returns"b"for

blankifthecellisempty,"l"forlabelifthecellcontainsatextconstant,and

"v"forvalueifthecellcontainsanythingelse.

"width"Columnwidthofthecellroundedofftoaninteger.Eachunitofcolumn

widthisequaltothewidthofonecharacterinthedefaultfontsize.

Referencethecellthatyouwantinformationabout.Ifomitted,informationspecifiedin

info_typeisreturnedforthelastcellthatwaschanged.Thefollowinglistdescribesthetext

valuesCELLreturnswheninfo_typeis"format",andreferenceisacellformattedwitha

built‐innumberformat.

IftheMicrosoftExcelformatisCELLreturns

General "G"

0"F0"

#,##0 ",0"

0.00"F2"

#,##0.00 ",2"

$#,##0_);($#,##0) "C0"

$#,##0_);[Red]($#,##0) "C0‐"

$#,##0.00_);($#,##0.00)"C2"

$#,##0.00_);[Red]($#,##0.00) "C2‐"

0% "P0"

0.00% "P2"

0.00E+00 "S2"

#?/?or#??/??"G"

m/d/yyorm/d/yyh:mmormm/dd/yy"D4"

d‐mmm‐yyordd‐mmm‐yy"D1"

d‐mmmordd‐mmm"D2"

ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage39CopyrightMay2010

mmm‐yy "D3"

mm/dd"D5"

h:mmAM/PM "D7"

h:mm:ssAM/PM "D6"

h:mm "D9"

h:mm:ss "D8"



Iftheinfo_typeargumentintheCELLformulais"format",andifthecellisformattedlater

withacustomformat,thenyoumustrecalculatetheworksheettoupdatetheCELLformula.





ExcelFunctions,Macros&DataCommands

www.CarltonCollins.comPage40CopyrightMay2010

Third‐PartySolutions–IncaseExcelsbuiltinfunctionsarenotsufficienttomeetyour

needs,followingisapartiallistofthird‐partyprovidersthathaveproductsthatareusedto

cleandatainavarietyofways.



ProviderProduct

Add‐inExpressLtd.AdvancedFind&Replace,MergeCells

Wizard

Add‐Ins.comDuplicateFinder

AddinToolsAddinToolsAssist

CDXZipStream

Click2ConvertConvertsPDFtoExcelformats

DigDBAdd‐insforExcel®

JKPApplicationDevelopmentFlexfindforExcel

J‐Walk&Associates,Inc.PowerUtilityPakVersion7

OfficeAssistanceLLCSimilarDataFinderforExcel®

PAToolsPAToolsAdvancedFindReplace

PDF2XLConvertsPDFfilestoExcelFormats

SpinnakerSoftwareSolutionsSpinnakerDBtoolsforExcel

VonnixExcelPowerExpander4.6

WinPureListCleanerLite

ListCleanerPro

CleanandMatch2007



ExcelFunctions,Macros&DataCommands



Chapter4

DataCommands

TheHeart&SoulOfExcel



ExcelFunctions,Macros&DataCommands

TheDataMenu‐PerhapsthepartsofExcelthatareofmostvaluetoCPAs,butleastused

byCPAsaretheDatacommandsfoundundertheDatamenuinExcel2003andearlier,and

onthedataRibboninExcel2007.Thesecommandsareshownbelow,andwewill

concentratethenexthourtostudyingthesecommands.



DataSort‐TheSorttooldoesexactlywhatitimplies–itsortsanddata.Keysorting

pointsareasfollows:



1. ContiguousData‐The“AtoZ”sortingtoolcansortlargematrixofdata

automaticallyaslongasthedataiscontiguous.Inotherwords,yourdatashould

containnoblankcolumns,noblankrows,andthecolumnsmustallbelabeled.

OnlythenwillExcelalwayscorrectlyselecttheentirematrixforsorting.



2. AtoZButton‐Simplyplacethecursorinthedesiredcolumnforsorted,and

presstheAtoZorZtoAbuttonasthecasemaybe.Excelwillautomaticallysort

allcontinuouscolumnsthathaveheadingsandallcontiguousrowsfromthetop

rowundertheheadinglabelsdowntothelastrowintheselectedcolumnthat

containsdata.(Note‐Ifyouaccidentlyselect2cellsinsteadofjustone,your

resultswillnotbecorrect.)



ExcelFunctions,Macros&DataCommands

3. Sortby64Columns‐The“Sort”toolisdramaticallyenhancedinExcel2007asit

nowprovidestheabilitytosortbyupto64columns,insteadofjust3columns.

Presentedbelowisadialogboxwhichshowsthisexpandedfunctionality.



4. SortLefttoRight–Excelhasalwaysprovidedtheabilitytosortlefttoright.Todo

so,selecttheoptionsboxintheSortDialogboxandclickthecheckboxlabeled

“SortlefttoRight”asshownbelow.



5. SortbyColor–Excel2007nowprovidestheabilitytosortbyfontcolororbycell

color,orboth.Thisishandyinmanyways.SometimesCPAsusecolortotagor

markcertaincells‐andlaterfinditusefultobeabletosortbythosemarkings.In

othersituationsCPAsuseconditionalformattingtoapplycolortocellsusinga

widevarietyofrules.ThereafterExcelcansortthedatabasedontheresulting

colors.Thesort‐by‐coloroptionsareshownbelow.



ExcelFunctions,Macros&DataCommands





Tobeaccurate,itwaspossibletosortbycolorinExcel2003.Toaccomplishthis

task,youneededtousethe=CELLfunctioninordertoidentifyinformationabout

agivencellsuchasthecellcolororfontcolor.Thereafter,theresultsofthat

functioncouldbeusedtosortrows–whicheffectivelymeansthatyoucansort

bycolorinExcel2003–butittakesabitmoreeffort.



6. SortByCustomList–AnothersortingcapabilityinExcelistheabilitytosortby

CustomList.Forexample,assumeaCPAfirmhastenpartners,andtheManaging

partnerpreferstobeshownatthetopofthelist,andtheremainingPartners

basedonseniority.Inthiscase,youcouldcreateaCustomListintheexcel

Optionsdialogboxlistingthepartnersinthedesiredorder,andthensortfuture

reportsbasedonthatorder.



Perhapsabetterexampleuseofthisfeaturewouldbetocreateanon‐alphabetic

customlistofyourchartofaccounts,andthensorttransactionstoproducea

generalledgerinchartofaccountorder–evenifyourpreferredchartofaccounts

isnotalphabetical.thepartnersenioritydoesnotmatchthealphabeticnames,

norany



FilteringData‐UsingAutoFiltertofilterdataallowsyoutoviewasubsetofyourdata

inarangeofcellsortable.Onceyouhavefilteredthedata,youcanapplyadditionalfilters

tofurtherrefineyourdataview.Whenyouaredone,youcanclearafiltertoonceagain

redisplayallofthedata.Tousethistool,startwithanylistofdataandturnonthe

AutoFiltertool.Thenpositionyourcursorinthecolumnyouwanttofilterandusethedrop

downarrowstoapplyyourfiltersasshowninthescreenbelow.

ExcelFunctions,Macros&DataCommands



Oncethefiltersareapplied,youwillseeasubsetyourdata.Forexample,thescreen

presentedbelowshowsfiltereddataforonlyMaconandSavannahproperties.



Asfiltersareapplied,asmallfunnelappearsinthedropdownarrowbuttontoindicatethat

afilterhasbeenapplied.Youcanapplyfiltersformultiplecolumnssimultaneously.



KeyPointsConcerningTheAutoFilterCommand:



1. ContiguousData–TheAutoFiltertoolsworksbestwhenyouareworkingwithdata

thatiscontiguous.Inotherwords,yourdatashouldcontainnoblankcolumns,no

blankrows,andthecolumnsmustallbelabeled.

ExcelFunctions,Macros&DataCommands



2. FilterbyMultipleColumns‐Youcanfilterbymorethanonecolumn.



3. RemovingFilters–InExcel2003andearlier,afasterwaytoremovemultiplefiltersis

toturnofffilteringandthenturnfilteringbackon.InExcel2007youcansimpleclick

theClearbuttonintheSortandFilterGroupasshownbelow.



4. FiltersareAdditive‐Eachadditionalfilterisbasedonthecurrentfilterandfurther

reducesthesubsetofdata.



5. ThreeTypesofFilters–Youcanfilterbasedonlistvalues,byformats,orbycriteria.

Eachofthesefiltertypesismutuallyexclusiveforeachrangeofcellsorcolumntable.

Forexample,youcanfilterbycellcolororbyalistofnumbers,butnotbyboth;you

canfilterbyiconorbyacustomfilter,butnotbyboth.



6. FiltersEnabled‐Adrop‐downarrowmeansthatfilteringisenabledbutnot

applied.



7. FilterApplied‐AFilterbuttonmeansthatafilterisapplied.



ExcelFunctions,Macros&DataCommands

8. FilterSpanning‐Thecommandsunder

theAllDatesinthePeriodmenu,such

asJanuaryorQuarter2,filterbythe

periodnomatterwhattheyear.This

canbeuseful,forexample,tocompare

salesbyaperiodacrossseveralyears.



9. ThisYearvs.Year‐to‐DateFiltering‐

ThisYearandYear‐to‐Dateare

differentinthewaythatfuturedates

arehandled.ThisYearcanreturndates

inthefutureforthecurrentyear,

whereasYeartoDateonlyreturns

datesuptoandincludingthecurrent

date.



10. FilteringDates‐Alldatefiltersare

basedontheGregoriancalendaras

decreedbyPopeGregoryXIII,after

whomthecalendarwasnamed,on24

February1582.TheGregoriancalendarmodifiestheJuliancalendar'sregularfour‐

yearcycleofleapyearsasfollows:Everyyearthatisexactlydivisiblebyfourisaleap

year,exceptforyearsthatareexactlydivisibleby100;thecenturialyearsthatare

exactlydivisibleby400arestillleapyears.Forexample,theyear1900isnotaleap

year;theyear2000isaleapyear.



11. FilteringByDaysofWeek‐Ifyouwanttofilterbydaysoftheweek,simplyformat

thecellstoshowthedayoftheweek.



12. Top&BottomFiltering‐OntheDatatab,in

theSort&Filtergroup,clickFilter.Pointto

NumberFiltersandthenselectTop10.To

filterbynumber,clickItems.Tofilterby

percentage,clickPercent.Note‐Topand

bottomvaluesarebasedontheoriginal

rangeofcellsortablecolumnandnotthe

filteredsubsetofdata.



13. Above&BelowAverageFiltering‐Onthe

Datatab,intheSort&Filtergroup,click

Filter.PointtoFilterbyNumbersthatare

Above/BelowAverage.Note–Thesevalues

arebasedontheoriginalrangeofcellsor

tablecolumnandnotthefilteredsubsetofdata.

ExcelFunctions,Macros&DataCommands



14. FilteringOutBlanks‐Tofilterforblanks,intheAutoFiltermenuatthetopofthelist

ofvalues,clear(SelectAll),andthenatthebottomofthelistofvalues,select

(Blanks).



15. FilteringByColor‐SelectFilterbyColor,andthendependingonthetypeofformat,

selectFilterbyCellColor,FilterbyFontColor,orFilterbyCellIcon.



16. FilterbySelection‐Tofilterbytext,number,ordateortime,clickFilterbySelected

Cell'sValueandthen:Tofilterbycellcolor,clickFilterbySelectedCell'sColor.To

filterbyfontcolor,clickFilterbySelectedCell'sFontColor.Tofilterbyicon,click

FilterbySelectedCell'sIcon.



17. RefreshingFilters‐Toreapplyafilterafterthedatachanges,clickacellintherange

ortable,andthenontheDatatab,intheSort&Filtergroup,clickReapply.



DataForm–Excel’s2003DataFormtoolmakesExcellookmoreandbehavemorelikea

database,suchasMicrosoftAccess.(TheFormbuttonhasnotbeenincludedontheOffice

FluentuserinterfaceRibbon,butyoucanstilluseitinOfficeExcel2007byaddingtheForm

buttontotheQuickAccessToolbar.)



Adataformprovidesaconvenientmeanstoenterordisplayonecompleterowof

informationinarangeortablewithoutscrollinghorizontally.Youmayfindthatusingadata

formcanmakedataentryeasierthanmovingfromcolumntocolumnwhenyouhavemore

columnsofdatathancanbeviewedonthescreen.Useadataformwhenasimpleformof

ExcelFunctions,Macros&DataCommands

textboxesthatlistthecolumnheadingsaslabelsissufficientandyoudon'tneed

sophisticatedorcustomformfeatures,suchasalistboxorspinbutton.



KeyPointsusingdataForm:



1. Youcannotprintadataform.



2. Becauseadataformisamodaldialogbox,youcannotuseeithertheExcelPrint

commandorPrintbuttonuntilyouclosethedataform.



3. YoumightconsiderusingtheWindowsPrintScreenkeytomakeanimageofthe

form,andthenpasteitintoMicrosoftWordforprinting.



DataSubtotals–Excelprovidesanautomaticsubtotalingwhichwillautomatically

calculateandinsertsubtotalsandgrandtotalsinyourlistortable.Onceinserted,Excel

recalculatessubtotalandgrandtotalvaluesautomaticallyasyouenterandeditthedetail

data.TheSubtotalcommandalsooutlinesthelistsothatyoucandisplayandhidethedetail

rowsforeachsubtotal.ExamplesofatheSubtotaldialogboxandaresultingsubtotaled

tableareshownbelow.



KeypointstoConsiderWhenUsingSubtotalingareasfollows:

ExcelFunctions,Macros&DataCommands



1. ContiguousData–TheSubtotaltoolsworksbestwhenyouareworkingwithdata

thatiscontiguous.Inotherwords,yourdatashouldcontainnoblankcolumns,no

blankrows,andthecolumnsmustallbelabeled.



2. SortBeforeYourSubtotal‐Youmustsortthedatabythecolumnyouwishto

Subtotalby,elseyouwillreceiveerroneousresults.



3. OtherMathematicalApplications‐TheSubtotaltoolnotonlycalculatessubtotals,

butitcanalsocalculateminimums,maximums,averages,standarddeviations,and

otherfunctions.



4. Subtotalsin2007Tables–Excel2007deploysSubtotalingalittledifferentlyinthat

theSubtotaltoolappearsatthebottomofeachcolumnineachtable,asshownin

thescreenbelow.





5. AutomaticOutlining‐SubtotalingautomaticallyinsertsOutlines,whichisreally

cool.Youcanthencondenseandexpandthedataintotalandbysubtotal.Some

CPAsalsoliketocopyandpastethecondensedsubtotalinformationtoanother

locationbutfindthatthisprocesscopiesandpastesallofthedata.Therearetwo

waystoachieveacleancopyandpastewithoutgrabbingallthehiddendataas

follows:

ExcelFunctions,Macros&DataCommands

a. CTRLkey–HoldtheControlKeydownwhileyouindividuallyclickoneachsubtotal

row.Thiswillenableyoutocopyandpastejustthesubtotaldata.Thisapproachcan

beproblematicbecauseifyoumis‐click,youhavetostartover.



b. SelectVisibleCells–AbetterapproachistousetheSelectVisibleCellstool.This

toolwillselectonthedatayoucansee,afterwhichthecopyandpasteroutinewill

yieldthedesiredresults.Thisoptionisbetterbecauseitisfasterandlesserror

prone.



DataValidation



DataValidationcanbeusedtolimitthedatathatcanbeenteredintoacell.Forexample,

youmightwanttheusertoenteronlyvaluesbetween1%and99%.Youmightalsousethis

tooltoenabledatainputtoadropdownlist.Thishastwoadvantagesinthatitcanbe

fasterandmoreaccurate.Startwiththedialogboxbelowtocreateyourdropdownlist

functionality.



Aftermakingallthenecessaryselectionsinthevalidationlistdialogbox,yourworksheet

willbehaveasshownbelow.



ExcelFunctions,Macros&DataCommands



Youcanalsoprovidemessagestodefinewhatinputyouexpectforthecell,andinstructions

tohelpuserscorrectanyerrors.Forexample,inamarketingworkbook,youcansetupacell

toallowonlyaccountnumbersthatareexactlythreecharacterslong.Whenusersselectthe

cell,youcanshowthemamessagesuchasthisone:



Ifusersignorethismessageandtypeinvaliddatainthecell,suchasatwo‐digitorfive‐digit

number,youcanshowthemanactualerrormessage.Inamoreadvancedscenario,you

mightusedatavalidationtocalculatethemaximumallowedvalueinacellbasedonavalue

elsewhereintheworkbook.Inthefollowingexample,theuserhastyped$4,000incellE7,

whichexceedsthemaximumlimitspecifiedforcommissionsandbonuses.



ExcelFunctions,Macros&DataCommands



Ifthepayrollbudgetweretoincreaseordecrease,theallowedmaximuminE7would

automaticallyincreaseordecreasewithit.





ExcelFunctions,Macros&DataCommands

PivotTables



ThePivotTablereporttoolprovidesaninteractivewaytosummarizelargeamountsofdata.

UseshouldusethePivotTabletoolstocrunchandanalyzenumericaldataPivotTable

reportsareparticularlyusefulinthefollowingsituations:



a. Rearrangingrowstocolumnsorcolumnstorows(or"pivoting")toseedifferent

summariesofthesourcedata.

b. Filtering,sorting,grouping,andconditionallyformattingyourdata.

c. Preparingconcise,attractive,andannotatedonlineorprintedreports

d. Queryinglargeamountsofdata.

e. Subtotalingandaggregatingnumericdata.

f. Summarizingdatabycategoriesandsubcategories

g. Creatingcustomcalculationsandformulas.

h. Expandingandcollapsinglevelsofdata.

i. Drillingdowntodetailsfromthesummarydata



Inessence,PivotTablespresentmultidimensionaldataviewstotheuser–thisprocessis

oftenreferredtoas“modeling”,“data‐cubeanalysis”,or“OLAPdatacubes”.Tore‐arrange

thePivotTabledata,justdraganddropcolumnandrowheadingstomovedataaround.

PivotTablesareagreatdataanalysistoolformanagement.



IfyouhaveneverusedaPivotTablebefore,initiallytheconceptcanbedifficulttograsp.

ThebestwaytounderstandaPivotTableistocreateablankPivotTableandthendragand

dropfieldnamesontothatblanktable.Thiswayyouwillseetheresultingpivottable

magicallyappearanditwillhelpyoubetterunderstandtheimportantrelationshipbetween

thepivotpalletandthefieldnamelist.



Let’screateasimplePivotTable.StartwithanExcelworksheetdatathatcontainsseveral

columnsofdata–thedatamustincludecolumnandrowheadingsandithelpsifthedatais

contiguous.PlaceyourcursoranywhereinthedataandselectPivotTablefromtheData

menuinExcel2003andclickFinish;orfromtheinsertRibboninExcel2007.Thisprocessis

shownbelow:Let’sstartwithapageofdatasummarizingtheresultsoftaxseasonasallof

thetimesheetentrieshavebeenenteredontoasingleworksheetasshownbelow.



ExcelFunctions,Macros&DataCommands



PlaceyourcursoranywhereinthedataandselectPivotTablefromtheInsertRibbonas

shownbelow:



Forlearningpurposeslet’srightmouseclickonthepivottableandselectPivotTableOptions,

Display,ClassicPivotTableLayout.Yourscreenwillnowappearasfollows:



ExcelFunctions,Macros&DataCommands



IlikeforCPAstolearnhowtousePivotTablesinthisviewbecauseitvisuallyhelpsthem

understandtheallimportantrelationshipbettertheblankpivotpaletteandthePivotTable

fieldList,bothelementsofwhichareshowninthescreenabove.



Toproceed,simplydraganddropfieldnamesshownontherightontotheblankPivot

paletteshownontheleft.Witheachdrop,yourreportgrowslarger.Asanalternativeyou

couldusethecheckboxesnexttofieldnames–thisfunctionalityisnewinExcel2007.After

addedsomedatatoyourblankPivotPalette,yourdatawilllooksomethinglikethis:



ExcelFunctions,Macros&DataCommands

NextformatandfilterthePivotReport.Veryquicklyyourreportcomestogetherasshown

below.NoticethefilterbuttonhasbeenappliedandaPivottablestylehasalsobeenapplied

forappearance.



Doubleclickingonanynumberinapivotreportwillautomaticallyproduceanewworksheet

completewithallsupportingdetailthatcomprisesthesummarynumber.



ThereareamultitudeofPivotTableoptionsthatcanbeappliedtoaltertheappearanceor

behaviorofyourPivottable.



KeyPointsConcerningPivotTablesareasFollows:



a. YoucancreateasmanyPivotReportsasyouwantfromyourinitialrawdatapage.Yourraw

dataremainsunchangedasnewPivottablesarecreated.



b. Asyourrawdatachanges,yourpivottablesareupdatedeachtimeyoupresstherefresh

button.OrifyoupreferyoucansetyourPivotTablestoupdatethemselvesatregularly

scheduledintervals–sayeverytenminutes.



c. AkeytounderstandingPivotTablesisunderstandingtherelationshipbetweentheBlank

PivotpaletteandthePivotTableFieldlist.Asdataisselectedinthelist,itappearsonthe

PivottableReport.



d. YoucanalterthePivotTablesimplebydragginganddroppingthefieldnamesindifferent

locationsonthePivotpalette,orindifferentlocationsinthePivotTableFieldlistBox.



e. PivotTablescanbepivoted.



f. PivotTablescanbesortedbyanyColumn.(Orbyanyrowwhensortinglefttoright)



g. PivotTablescanbeFiltered.



h. PivotTablescanbeDrilled.



i. PivotTablescanbecopiedandpasted.

ExcelFunctions,Macros&DataCommands



j. PivotTablescanbeformattedusingPivotTableStyles,asshownbelow.



k. Subtotalsandgrandtotalscanbedisplayedorsuppressedattheusersdesire.



l. PivotTableDatacanbeshownasnumbersorpercentagesattheusersdesire.



m. PivotTablecannotonlybesummed,itcanbeaveraged,minimized,maximized,counted,etc.



n. Blankrowscanbedisplayedorsuppressedattheusersdesire.



o. Anewfeaturecalled“CompactForm”organizedmultiplecolumnlabelsintoaneatly

organizedoutlinewhichiseasiertoread.



p. PivotTablescanquerydatadirectlyfromanyODBCcompliantdatabase.ThePivotTabletool

foraccomplishingthistaskisnotincludedintheribbon–youwillfinditbyCustomizingthe

QuickAccessToolBarandsearchingthe“CommandsNotShownintheRibbon”tabtofind

thePivotTableandPivotChartWizardOption.



q. ManyaccountingsystemscanpushdataoutoftheaccountingsystemintoanExcel

PivotTableformat–thisiscommonlyreferredtoasanOLAPDataCube.OLAPdataCubeis

justafancywordforPivotTable–andthereisnodifference.



r. PivotTablescanautomaticallycombinedatafrommultipledatasources.ThePivotTabletool

foraccomplishingthistaskisnotincludedintheribbon–youwillfinditbyCustomizingthe

QuickAccessToolBarandsearchingthe“CommandsNotShownintheRibbon”tabtofind

thePivotTableandPivotChartWizardOption.



ExcelFunctions,Macros&DataCommands



s. ExcelalsoprovidesaPivotChartfunctionwhichworkssimilarlytoPivotTables.Presented

belowisanexamplePivotChart.



Excel2003PivotTablesworkverysimilarlyasshownbelow.ExcelcreatesablankPivotTable,

andtheusermustdraganddropthevariousfieldsfromthePivotTableFieldListontothe

appropriatecolumn,row,ordatasection.Asyoudraganddroptheseitems,theresulting

reportisdisplayedonthefly.HereistheblankPivotPaletteview.



ExcelFunctions,Macros&DataCommands



NowdraganddropfieldnamesfromthePivotTablefieldlistontothePivotpallet.This

actionwillautomaticallycreatePivotTablereports–andtheywillchangeeachtimeyou

dropadditionalfieldnames,ormovefieldnamesaround.Presentedbelowarebutafew

examplesofhundredsofpossiblereportsthatcouldbeviewedwiththisdatathroughthe

PivotTableformat.



Thisreportshownaboveshowsthetotalresultingsalesforeachmarketingcampaignfor

eachofthe4monthsmarketingcampaignswereconducted.



Inthisscreenweseethesameinformationisshownasapercentageofthetotal.Afew

observationsincludethefactthatoverallRadioSpotsarethemostprofitabletypeof

campaign,butonlyinAprilandJuly.InJanuaryandOctober,localadsanddirectmail,

respectively,producebetterresults.Further,Aprilcampaignshadthebestresponseoverall.



ExcelFunctions,Macros&DataCommands



Furtheranalysisinthescreenabovetellsusthatourresultsvarywidelyfromonecitytothe

next.InNewYork,couponswereleasteffective,butcouponsweremosteffectivein

Columbus.PivotchartsbasedonPivotTabledatacanbemodifiedbypivotingand/or

narrowingthedata.TheycanalsobepublishedontheInternet(oronanIntranet)as

interactiveWebpages.Thisallowsusersto“play”withthedata.Thechartbelowprovidesa

visuallookatthedatashownabove.



FilteringPivotTables‐Ifyoutakeacloselookatyourresultingpivottables,youwillnotice

thatExcelautomaticallyinsertsafilterbuttononeachfieldlistasshownbythedropdown

arrowsinthescreenbelow:



ExcelFunctions,Macros&DataCommands



Thisdropdownfilterlistmakesiteasytorefineyourreporttoincludejustthedatayou

want.



DrillingPivotTables‐Anothernicefeatureinpivottablesisthattheyareautomatically

drillable.SimplydoubleclickonanynumberinapivotreporttophaveExcelautomatically

insertanewsheetandproducethedetailedreportunderlyingthenumberyouclickedon.

Anexampleofthisisshownbelow:



PivotTableOptions‐Byrightmouseclickingonyourpivottableyouwillrevealseveral

optionsettingsboxesasshownbelow.Forexample,theseoptionsboxescontrolthetypesof

subtotalsproducedinyourpivotreports.Excelalsooffersapivottableoptionsboxaswellas

alayoutwizardthatmakesproducingpivottablesalittleeasier.

ExcelFunctions,Macros&DataCommands



ExcelFunctions,Macros&DataCommands

DataTable(“What‐ifAnalysis”)



Datatablesarepartofasuiteofcommandsthatarecalledwhat‐ifanalysistools.Whenyou

usedatatables,youaredoing“what‐ifanalysis”.What‐ifanalysisistheprocessofchanging

thevaluesincellstoseehowthosechangeswillaffecttheoutcomeofformulasonthe

worksheet.Forexample,youcanuseadatatabletovarytheinterestrateandtermlength

thatareusedinaloantodeterminepossiblemonthlypaymentamounts.



ThreecategoriesofWhat‐ifAnalysisTools‐Therearethreekindsofwhat‐ifanalysistoolsin

Excel:



1. DataTables

2. GoalSeek

3. Scenarios



Adatatablecannotaccommodatemorethantwovariables.Ifyouwanttoanalyzemore

thantwovariables,youshouldinsteadusescenarios.Althoughitislimitedtoonlyoneor

twovariables(onefortherowinputcellandoneforthecolumninputcell),adatatablecan

includeasmanydifferentvariablevaluesasyouwant.Ascenariocanhaveamaximumof32

differentvalues,butyoucancreateasmanyscenariosasyouwant.



LoanAnalysis‐Inthisexercise,westartbycreatingasimplePaymentfunctiontocalculate

thepaymentamountofaloangivena