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

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



Chapter4

DataCommands

TheHeart&SoulOfExcel



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

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

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

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

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

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





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

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



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

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



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

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

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

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



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

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

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

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



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

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

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

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



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

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



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

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

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

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



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

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



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

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

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

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



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

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



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

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



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

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



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

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



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

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



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

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

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loanamount,interestrateandnumberofperiods.



Thenextstepistocreatea“Two‐WayDataTable”displayingtheresultingpaymentamount

givenavarietyoflengthsoftheloan.Thisprocessisstartedbycreatingalistofthe

alternativeloanamounts,asshownbelowinB8,B9,B10,etc.CellC7mustreferencethe

resultsyouwanttobedisplayedinthetable.



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

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



ThenextstepistohighlightthedatatablerangeandusetheDataTablecommandunder

theDatamenu(asshownbelow)togeneratethedesiredtable.



Thisprocesswillgeneratethefollowingtable:



Thistabletellsusthatthesameloanamountwillrequireamonthlypaymentof$3,331to

paytheloanoffinjust10years,andamonthlypaymentof$5,800torepaytheloaninjust

5years.

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

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



Thenextstepinthisexerciseistogeneratealinechartbasedonthedatatablewejust

created.Thislinechartwillprovidesomeinterestingobservationsregardingthebenefits

anddetrimentsofpayingoffloansoverlongerperiods.



Theresultingchartisshownasfollows:



Basedonthis,nooneshouldeverobtainafairmarketloanformorethan15years,the

reductioninpaymentssimplyaren’tworththeadditionallengthoftheloan.Thissamebasic

behaviorisseenwhethertheinterestrateis1%or100%.Theonlytimeyoumightbe

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

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

justifiedinobtainingaloanlonerthan15yearsmightbewhenyouareextendeda

favorableinterest–thisbetterthanafairmarketinterestrate.



Goal Seek



Ifyouknowtheresultthatyouwantfromaformula,butarenotsurewhatinputvaluethe

formulaneedstogetthatresult,usetheGoalSeekfeature.Forexample,supposethatyou

needtoborrowsomemoney.Youknowhowmuchmoneyyouwant,howlongyouwantto

taketopayofftheloan,andhowmuchyoucanaffordtopayeachmonth.YoucanuseGoal

Seektodeterminewhatinterestrateyouwillneedtosecureinordertomeetyourloan

goal.GoalSeekworksonlywithonevariableinputvalue.Ifyouwanttoacceptmorethan

oneinputvalue;forexample,boththeloanamountandthemonthlypaymentamountfora

loan,youusetheSolveradd‐indiscussedattheendofthismanual.



Scenarios



ScenarioManagerallowsyoutocreateandsavemultiple“whatif”scenarios(suchasbest

case,mostlikely,andworstcasesscenarios).Youcanalsocreateasummarytableofthe

scenarioresultsinseconds.Itisparticularlyusefulforworksheetssuchasbudgetsinwhich

usershaveoftensavedmultiplecopiesofthesameworksheettoaccomplishthesame

objective.Anexampleisshownbelow.Inthisexample,atirecompanyhasprepareda

revenuebudgetforthecomingyear,andhascreatedthreealternativescenariosto

generatetherevenuesthatwillresultgivenavarietyofmarkupassumptions–inthiscase

100%,110%and120%markups.



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

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

Pressingthesummarybuttoninthescenariomanagerdialogboxwillcreatethefollowing

PivotTableofpossiblealternativeresults.Hereweseedetailedrevenueprojectionsforall

tiresandlaborfeesgivenallthreepossiblescenariosof100%,110%,and120%markup.



Withafewsimplecopypastecommands,thenewlycreateddatacanbepositionedand

formattednexttotheoriginalprojectionsasshowninthescreenbelow.



Ofcoursethescenariosabovecouldhavebeencreatedeasilyusingsimpleformulasinstead

ofusingthescenariomanagertoolasdescribedabove.Thisunderscoresthatbestpurpose

ofscenariomanagerwhichistokeeptrackofolderandchangingdatathroughtime,rather

thanproducingwhat‐ifscenarios.Forexample,acomplexprojectioncontainingscenarios

basedonoriginalassumptions,revisedassumptions,andfinalassumptionswillallow

managementtogobackandreviewtheassumptionsusedthroughouttheproject,andsee

howthoseassumptionschangedasprojectplanningprogressed.

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

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

Data‐TexttoColumns



Asdiscussedearlierinthismanual,oftenCPAsreceivedatafromtheirclientsorIT

departmentsthatisintextform.Whenthishappens,Excelcansplitthecontentsofoneor

morecellsinacolumnanddistributethosecontentsasindividualpartsacrossothercellsin

adjacentcolumns.Forexample,theworksheetbelowcontainsacolumnoffullnamesand

amountsthatyouwanttosplitintoseparatecolumns.TheTexttoColumnsWizardparses

thedataautomaticallyintoseparate



Selectthecell,range(range:Twoormorecellsonasheet.Thecellsinarangecanbe

adjacentornonadjacent.),orentirecolumnthatcontainsthetextvaluesthatyouwantto

split.NoteArangethatyouwanttosplitcanincludeanynumberofrows,butitcaninclude

nomorethanonecolumn.Youalsoshouldkeepenoughblankcolumnstotherightofthe

selectedcolumntopreventexistingdatainadjacent



DataConsolidate



Excelcancombine,summarize,andreportconsolidatedresultsfromseparateworksheets.

Theunderlyingworksheetscanbeinthesameworkbookorinotherseparateworkbooks.

Therearetwodifferentsitautionsasfollows:



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

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

1. YouAreConsolidatingSimilarData–Suchasdepartmentalbudgetswhereeveryworksheet

containstheexactsamelabelsintheexactsamecells.Inthiscase,youcanwritea“Spearing

Formula”whichcanconsolidatethenecessaryinformationeasily.



2. YouAreConsolidatingDis‐SimilarData–Thevariousworksheetscontaindifferentrowand

columndescriptionslocatedindifferentlocationsontheworksheets.Inthiscaseyoushould

usetheDataConsolidatecommand.

Forexample,assumethatyouhavereceivedbudgetsfrommultipledepartments,andyou

wanttocombinethemtogether.Inthiscase,Excelwilldotheworkforyou.Youcanusea

consolidationtorollupthesefiguresintoacorporatebudgetworksheet,asshownbelow.



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

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

DataGrouping&Outlining



Ifyouhavealistofdatathatyouwanttogroupandsummarize,youcancreateanoutline

ofuptoeightlevels,oneforeachgroup.Eachinnerlevel,representedbyahighernumber

intheoutlinesymbolsdisplaysdetaildatafortheprecedingouterlevel,representedbya

lowernumberintheoutlinesymbols.Useanoutlinetoquicklydisplaysummaryrowsor

columns,ortorevealthedetaildataforeachgroup.Youcancreateanoutlineofrows(as

shownintheexamplebelow),anoutlineofcolumns,oranoutlineofbothrowsand

columns.



WebQueries



Excelincludespre‐designed“queries”thatcanimportcommonlyuseddatain10seconds.

Forexample,youcoulduseawebquerytocreateastockportfolio.Allyouneedisa

connectiontotheInternetandofcourse,somestocktickersymbols.InExcel2003select

“Data,ImportExternalData,ImportData”andwalkthroughthewebquerywizardfor

importingstockquotes.InExcel2007andlaterusetheDataRibbon,ExistingConnections,

StockQuotesoption.Inseconds,Excelwillretrieve20minutedelayedstockpricesfromthe

web(duringthehourswhenthestockmarketisopen)anddisplayagridofcompleteup‐to‐

datestickpriceinformationthatissynchronizedtothestockmarket’schangingstockprices.

Witheachclickofthe“Refresh”button,thestockpriceinformationinExcelisupdated‐

thissurebeatspickingnumbersoutofthenewspaper.

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

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



CompletingtheStockPortfolio–Nextlinkthegriddatatoanotherworksheet,andinsert

newcolumnscontainingthenumberofsharesowned,aswellsasanadditionalcolumnto

computerthetotalvaluebasedonsharesowned,asshownbelow.



RefreshingtheStockPrices‐Onceyouhavecreatedyourportfolio,simplyclicktheRefresh

Databuttononthe“ExternalData”ToolbarinExcel2003oronthe“DataRibbon”inExcel

2007shownbelowtoupdatethecurrentvalueofyourPortfolio.



 



QueryParameters‐Therearenumerousoptionstohelpyouextractexactlythedatayou

wanttheywayyouwantit.The“WebQueryParametersBox”,“WebQueryOptionsbox”

and“ExternalDataPropertiesBox”providenumerousoptionsforcontrollingyourweb

query.

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

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

DatabaseQueries



MicrosoftExcelcanalsoqueryandretrievedatayouwantfromanexternaldatasource.For

example,youcanretrieveMicrosoftExceldataaboutaspecificproductbyregion.Youcan

createasimplequerybyusingtheQueryWizard,oryoucancreateamorecomplexquery

byusingtheadvancedfeaturesofMicrosoftQuery.



TouseMicrosoftQuerytoretrieveexternaldata,youmust:



1. Haveaccesstoanexternaldatasource‐Ifthedataisnotonyourlocalcomputer,

youmayneedtoseetheadministratoroftheexternaldatabaseforapassword,user

permission,orotherinformationabouthowtoconnecttothedatabase.



2. InstallMicrosoftQuery‐IfMicrosoftQueryisnotavailable,youmightneedto

installit.



3. Specifyasourcetoretrievedatafrom,andthenstartusingMicrosoftQuery‐For

example,ifyouwanttoinsertdatabaseinformation,displaytheDatabasetoolbar,

clickInsertDatabase,clickGetData,andthenclickMSQuery.



Forexample,supposewehavesomedatainouraccountingsystem–SageMAS200ERP

thatwewouldliketoanalyzeinExcel.WecanusetheDatabaseQueryWizardtobuilda

querythatwillextractthedataweneedandplaceitinanExcelspreadsheet.



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

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



Thefirststepistoselectthetypeofdatabaseyouwanttoqueryandtoselectthespecific

database.



Upontheselectionofthedesireddatabasealistoftableswillbepresented.Choosethe

desiredtables,andselectthedesireddatafieldstobeimported.Youwillthenhavethe

optiontofilterandsortthedatabeforeitisimported.Finallyyouwillbegiventheoption

tosavethequerysothatyoucanrunitatalaterdatewithouthavingtostartfromscratch.

Excelwillthenreturnatablefullofthedatayourequestedasshowninthescreenbelow.



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

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





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

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



Chapter5

MACROS

AutomatingYourKeyStrokes



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

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

Macros



MacrosofferapowerfulandflexiblewaytoextendthefeaturesofExcel.Theyallowthe

automationofrepetitivetaskssuchasprinting,formatting,configuring,orotherwise

manipulatingdatainExcel.Inits’simplestform,amacroisarecordingofyourkeystrokes.

WhilemacrosrepresentoneofthestrongerfeaturesfoundinExcel,theyarerathereasyto

createanduse.TherearesixmajorpointsthatIliketomakeaboutmacrosasfollows.



1. Record,UseExcel,StopRecording–Tocreateamacro,simplyturnonthemacro

recorder,useExcelasyounormallydo,thenturnofftherecorder.Presto–youhave

createdamacro.Whiletheprocessissimplefromtheuser’spointofview,

underneaththecoversExcelcreatesaVisualBasicsubroutineusingsophisticated

VisualBasicprogrammingcommands.



2. MacroLocation–Macroscanbestoredineitheroftwolocations,asfollows:



a. Theworkbookyouareusing,or

b. YourPersonalMacroWorkbook(whichbydefaultishiddenfromview)



Ifyourmacroappliestoallworkbooks,thenstoreitinthePersonalMacro

WorkbooksoitwillalwaysbeavailableinallofyourExcelworkbooks;otherwise

storeitinyourcurrentworkbook.Amacrostoredinyourcurrentworkbookwillbe

embeddedandincludedintheworkbook,evenifyoue‐mailtheworkbookto

anotheruser.



3. AssignyourMacrotoanIcon,TextoraButton–Tomakeiteasytorunyourmacro,

youshouldassignittoatoolbariconsoitwillalwaysbeavailablenomatterwhich

workbooksyouhaveopen.Ifthemacroappliesonlytoyourcurrentworkbook,then

assignittoTextoramacroButtonsoitwillbequicklyavailableinyourcurrent

workbook.



4. AbsoluteversusRelativeMacros–An“Absolute”macrowillalwaysaffectthesame

cellseachtimewhereasa“Relative”macrowillaffectthosecellsrelativetowhere

yourcursorispositionedwhenyouinvokethemacro.Itiscrucialthatyou

understandthedifference.



5. EditingMacros–Oncecreated,youcanviewand/oredityourmacrousingtheView

Macrosoption.ThiswillopenthemacrosubroutineinaVisualbasicprogramming

windowandprovideyouwithaplethoraofVBtools.



6. AdvancedVisualBasicProgramming–ForthetrulyambitiousCPA,intheVisual

BasicProgrammingwindow,youhavethenecessarytoolsyouneedtobuildvery

sophisticatedmacroswithdialogboxes,dropdownmenuoptions,checkboxes,

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

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

radiobuttons–thewholeworks.Iinviteyoutoknockyourselfout.Toseeallofthis

power,turnonthe“DeveloperTab”in“ExcelOptions”(seebelow).



Presentedbelowaremoredetailedcommentsandstep‐by‐stepinstructionsforcreating

andinvokingmacros,followedbysomeexamplemacros.



1. CreatingaMacro‐Tocreateamacro,click“RecordMacro”inthe“CodeGroup”onthe

DeveloperTab.



a. AssignaName‐Inthe“MacroName”box,enteranameforthemacro.



i. Thefirstcharacterofthemacronamemustbealetter.

ii. Subsequentcharacterscanbeletters,numbers,orunderscore

characters.

iii. Spacescannotbeusedinamacroname(anunderscorecharacter

isoftenusedasawordseparator.

iv. Ifyouuseamacronamethatisalsoacellreference,youmayget

anerrormessagethatthemacronameisnotvalid.



b. AssignaCTRLCombination(optional)–YoucanassignaCTRLcombination

shortcutkeytorunthemacrobytypinganylowercaseletteroruppercase

letterthatyouwanttouseintheShortcutkeybox.



i. TheshortcutkeywilloverrideanyequivalentdefaultExcel

shortcutkeywhiletheworkbookthatcontainsthemacroisopen.



c. MacroLocation‐Inthe“StoreMacroIn”list,selecttheworkbookwhere

youwanttostorethemacro.



i. Asmentionedabove,ifyouwantamacrotobeavailable

wheneveryouuseExcel,select“PersonalMacroWorkbook”.

WhenyouselectPersonalMacroWorkbook,Excelcreatesa

hiddenpersonalmacroworkbook(Personal.xlsb)ifitdoesnot

alreadyexist,andsavesthemacrointhisworkbook.

ii. InWindowsVista,thisworkbookissavedintheC:\Users\user

name\AppData\Local\Microsoft\Excel\XLStartfolder.

iii. InMicrosoftWindowsXP,thisworkbookissavedinthe

C:\DocumentsandSettings\username\Application

Data\Microsoft\Excel\XLStartfolder.

iv. WorkbooksintheXLStartfolderareopenedautomatically

wheneverExcelstarts.

v. Ifyouwantamacrointhepersonalmacroworkbooktoberun

automaticallyinanotherworkbook,youmustalsosavethat

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

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

workbookintheXLStartfoldersothatbothworkbooksare

openedwhenExcelstarts.



d. MacroDescription‐IntheDescriptionbox,typeadescriptionofthe

macro.



e. StartRecording‐ClickOKtostartrecording.



f. StartTyping‐Performtheactionsthatyouwanttorecord.



g. StopRecording–Whenyouaredoneclick“StopRecording”inthe“Code

Group”Onthe“DeveloperTab”.



i. YoucanalsoclickStopRecordingontheleftsideofthestatus

bar.



h. Assignamacrotoanobject,graphic,orcontrol‐Onaworksheet,right‐

clicktheobject,graphic,orcontroltowhichyouwanttoassignan

existingmacro,andthenclickAssignMacro.IntheMacronamebox,click

themacrothatyouwanttoassign.



2. MenuNavigationNotRecorded‐Whenyourecordamacro,themacrorecorder

recordsallthestepsrequiredtocompletetheactionsthatyouwantyourmacroto

perform.NavigationontheRibbonisnotincludedintherecordedsteps,onlythe

commandsthatareexecutedarerecordedinthemacro.



3. TurnOnTheDeveloperTab‐TurnontheDevelopertabbyclickingtheMicrosoft

OfficeStartButton,andthenclickExcelOptions.InthePopularcategory,underTop

optionsforworkingwithExcel,selecttheShowDevelopertabintheRibboncheck

box,andthenclickOK.



4. EnableMacros–Ifthemacrofunctionsaredisabled,youcanenablethemby

selectingMacroSecurityintheCodegroupontheDevelopertabasshownbelow.







UnderMacroSettings,clickEnableallmacros(notrecommended,potentially

dangerouscodecanrun),andthenclickOK.



5. ExampleMacros

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

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



a. PageSetupMacro–Startrecordinganewmacrocalledpagesetup.Select

alloftheworksheetsandthenchoosePageSetupandcustomizetheheader

andfooterstoincludepagenumbers,dateandtimestamps,filelocations,

tabnames,etc.AssignthemacrotoanIcononyourtoolbarorQuickAccess

Barandinsettingheadersandfooterswillbeabreezefortherestofyour

life.



b. PrintMacros–Doyouhaveatemplatethatyouprintfrequentlyfrom?Ifso,

insertseveralmacrobuttonstoprinteachreport,agroupofreports,and

evenmultiplereportsandreportingwillbesnapinthefuture.



c. DeleteDataMacro–Doyouhaveatemplatethatyouuseoftenthat

containsalotofvariables?Ifso,createamacrothatvisitseachcelland

erasesthatdata,resettingtheworksheetforuseinanewsetofcriteria.

Assignthemacrotoamacrobuttonandyouwillneveragainhaveold

assumptionsmixedinwithyournewertemplate.



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

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



Chapter6

Miscellaneous





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

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

Solver



SolverisoneofthemorepowerfulfeaturesinExcelbecauseitcansolveforoptimum

resultsincomplexworksheetswhileobeyingstatedconstraints.WithSolver,youcanfind

anoptimalvalueforaformulabymanipulatingagroupofcellsthatarerelated,either

directlyorindirectly,totheformulainthetargetcell.Solveradjuststhevaluesinthe

adjustablecellstoproducetheresultyouspecifyfromthetargetcellformula.Youcanapply

constraintstorestrictthevaluesSolvercanuseinthemodel,andtheconstraintscanrefer

toothercellsthataffectthetargetcellformula.



Intheportfolioexampleshownbelow,theuserwishestodeterminehowmuchmoneyto

investinvariousinvestmentsinordertomaximizethereturnonthoseinvestments.Of

coursethemaximumresultscanbeachievedbysimplyplaceinallmoniesintheinvestment

thatyieldsthehighestresults,howeverthisapproachviolatesthe“don’tputallofyoureggs

inonebasketrule”.Inthiscasetheuserwantstodiversifytheirfundsacrossmanytypesof

investments.Thereforeconstraintsareestablishedsuchasnomorethan35%offundscan

beinvestedinbluechipstocksandthecheckingaccountingmustcontainatleast$100,000.



Aportfolioisshownbelowandsomeconstraintshavebeenincludedinlustform.These

constraintswillneedtobestatedintermsofformulasinthesolvermanagerdialogbox.



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

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



ThekeytomakingsolverworkisthesolverParametersdialogboxshownbelow.







Thetargetcellissettorefertothetotalportfoliovalueattheendoftheyearbecausethis

isthevaluethatwewanttomaximize.Thechangingcellsaresettoreferencethe

percentagesofeachinvestment.Finallythevariousconstraintsareexpressedasformulasas

showninthelowerlefthandcornerofthedialogbox.Oncesolverhasbeenrunonce,the

solverformulassticktotheworksheetandtheresultsarechangedandupdatedasthe

variousassumptionsintheworksheetarechangedandupdated.Forexample,youmay

laterdeterminethatthegrowthrateforrealestateisadifferentamount.Inputtingthat

newamountintheworksheetwillcauseExceltoautomaticallyadjustallvariablesto

producetheoptimuminvestmentmixthatmaximizesearningswithoutviolatinganystated

constraints.





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



Chapter7

XMLData



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

OverviewofXMLinExcel

HideAll



MicrosoftOfficeExcelmakesiteasytoimportExtensibleMarkupLanguage(XML)(Extensible

MarkupLanguage(XML):AcondensedformofStandardGeneralizedMarkupLanguage(SGML)that

enablesdeveloperstocreatecustomizedtagsthatofferflexibilityinorganizingandpresenting

information.)datathatiscreatedfromotherdatabasesandapplications,tomapXMLelements

fromanXMLschema(XMLSchema:Aformalspecification,writteninXML,thatdefinesthe

structureofanXMLdocument,includingelementnamesandrichdatatypes,whichelementscan

appearincombination,andwhichattributesareavailableforeachelement.)toworksheetcells,and

toexportrevisedXMLdataforinteractionwithotherdatabasesandapplications.Thinkofthese

XMLfeaturesasturningOfficeExcelintoanXMLdatafilegeneratorwithafamiliaruserinterface.



WhyuseXMLinExcel?

a. XMLdataandschemafiles

b. KeyXMLandExcelscenarios

ThebasicprocessofusingXMLdatainExcel

a. WorkingwithXMLmaps

b. UsingtheXMLSourcetaskpane

c. Elementtypesandtheiricons

d. Workingwithsingle‐mappedcells

e. WorkingwithrepeatingcellsinXMLtables

f. XMLmapsecurityconsiderations

g. ImportingXMLdata

h. Workingwithaninferredschema

i. ExportingXMLdata

UsingtheExcelmacro‐enabledOfficeXMLFormatfile



WhyuseXMLinExcel?

XMLisatechnologythatisdesignedformanagingandsharingstructureddatainahuman‐readable

textfile.XMLfollowsindustry‐standardguidelinesandcanbeprocessedbyavarietyofdatabases

andapplications.UsingXML,applicationdesignerscancreatetheirowncustomizedtags,data

structures,andschemas.Inshort,XMLgreatlyeasesthedefinition,transmission,validation,and

interpretationofdatabetweendatabases,applications,andorganizations.



XMLdataandschemafiles

ExcelworksprimarilywithtwotypesofXMLfiles:

XMLdatafiles(.xml),whichcontainthecustomtagsandstructureddata.

Schemafiles(.xsd),whichcontainschematagsthatenforcerules,suchasdatatypeandvalidation.

NoteTheXMLstandardalsodefinesExtensibleStylesheetLanguageTransformation(XSLT)(XSL

Transformation(XSLT):AfilethatisusedtotransformXMLdocumentsintoothertypesof

documents,suchasHTMLorXML.ItisdesignedforuseaspartofXSL.)(.xslt)files,whichareusedto

applystylesandtransformXMLdataintodifferentpresentationformats.Youcanapplythese

transformsbeforeyouimportXMLfilesintoExcelandafteryouexportXMLfilesfromExcel.IfXSLT

filesarelinkedtoXMLdatafilesthatyouimportintoExcel,youdohavetheoptiontoapplyornot

applytheformattingbeforethedataisaddedtotheworksheet,butonlywhenyouopenanXMLfile

byusingtheOpencommandontheMicrosoftOfficeButton.

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



KeyXMLandExcelscenarios

ByusingXMLandExcel,youcanmanageworkbooksanddatainwaysthatwerepreviously

impossibleorverydifficult.ByusingXMLmaps,youcaneasilyadd,identify,andextractspecific

piecesofbusinessdatafromExceldocuments.Forexample,aninvoicethatcontainsthenameand

addressofacustomerorareportthatcontainslastquarter'sfinancialresultsarenolongerjust

staticreports.Youcaneasilyimportthisinformationfromdatabasesandapplications,reviseit,and

exportittothesameorotherdatabasesandapplications.Thefollowingarekeyscenariosthatthe

XMLfeaturesaredesignedtoaddress:



ExtendthefunctionalityofexistingExceltemplatesbymappingXMLelementsontoexisting

cells.ThismakesiteasiertogetXMLdataintoandoutofyourtemplateswithouthavingto

redesignthem.

UseXMLdataasinputtoyourexistingcalculationmodelsbymappingXMLelementsonto

existingworksheets.

ImportXMLdatafilesintoanewworkbook.

ImportXMLdatafromaWebserviceintoyourExcelworksheet.

ExportdatainmappedcellstoXMLdatafilesindependentfromotherdataintheworkbook.



ThebasicprocessofusingXMLdatainExcel

Thefollowingdiagramshowshowthedifferentfilesandoperationsworktogetherwhenyouuse

XMLwithExcel.Essentially,therearefivephasestotheprocess:



AddinganXMLschemafile(.xsd)toaworkbook

MappingXMLschemaelementstoindividualcellsorXMLtables

ImportinganXMLdatafile(.xml)andbindingtheXMLelementstomappedcells

Enteringdata,movingmappedcells,andleveragingExcelfunctionality,whilepreserving

XMLstructureanddefinitions

ExportingreviseddatafrommappedcellstoanXMLdatafile





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

WorkingwithXMLmaps

YoucancreateoropenaworkbookinExcel,attachanXMLschemafile(.xsd)totheworkbook,and

thenusetheXMLSourcetaskpanetomapXMLelementsoftheschematoindividualcellsortables.

AfteryoumaptheXMLelementstoyourworksheet,youcanimportandexportXMLdataintoand

outofthemappedcells.WhenyouaddanXMLschemafile(.xsd)toyourworkbook,youcreatean

XMLmap.Ingeneral,XMLmapsareusedtocreatemappedcellsandtomanagetherelationship

betweenmappedcellsandindividualelementsintheXMLschema.Inaddition,theseXMLmapsare

usedtobindthecontentsofmappedcellstoelementsintheschemawhenyouimportorexport

XMLdatafiles(.xml).

Therearetwokindsofmappedcellsthatyoucancreate:single‐mappedcellsandrepeatingcells

(whichappearasXMLtables).Tomakedesigningyourworksheetmoreflexible,youcandragthe

mappedcellsanywhereonaworksheetandintoanyorder—evenonedifferentfromtheXML

schema.Youcanalsochoosewhichelementstomapandnotmap.



ThefollowingrulesaboutusingXMLmapsareimportanttoknow:

1. AworkbookcancontainoneormoreXMLmaps.

2. Youcanonlymaponeelementtoonelocationinaworkbookatatime.

3. EachXMLmapisanindependententity,evenifmultipleXMLmapsinthesameworkbook

refertothesameschema.

4. AnXMLmapcanonlycontainonerootelement.Ifyouaddaschemathatdefinesmorethan

onerootelement,youarepromptedtochoosetherootelementtouseforthenewXML

map.



UsingtheXMLSourcetaskpane

YouusetheXMLSourcetaskpanetomanageXMLmaps.Toopenit,ontheDevelopertab,inthe

XMLgroup,clickSource.Thefollowingdiagramshowsthemainfeaturesofthistaskpane.



1. ListsXMLmapsthatwereaddedtotheworkbook

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

DisplaysahierarchicallistofXMLelementsinthecurrentlylistedXMLmap

SetsoptionswhenworkingwiththeXMLSourcetaskpaneandtheXMLdata,suchashow

topreviewthedataandcontrolheadings

OpenstheXMLMapsdialogbox,whichyoucanusetoadd,delete,orrenameXMLmaps

VerifieswhetheryoucanexportXMLdatathroughthecurrentXMLmap



Elementtypesandtheiricons

ThefollowingtablesummarizeseachtypeofXMLelementthatExcelcanworkwithandtheicon

thatisusedtorepresenteachtypeofelement.



Elementtype Icon



Parentelement



Requiredparentelement



Repeatingparentelement



Requiredrepeatingparentelement



Childelement



Requiredchildelement



Repeatingchildelement



Requiredrepeatingchildelement



Attribute



Requiredattribute



Simplecontentinacomplexstructure



Requiredsimplecontentinacomplexstructure



Workingwithsingle‐mappedcells

Asingle‐mappedcellisacellthathasbeenmappedtoanonrepeatingXMLelement.Youcreatea

single‐mappedcellbydragginganonrepeatingXMLelementfromtheXMLSourcetaskpaneontoa

singlecellinyourworksheet.WhenyoudraganonrepeatingXMLelementontotheworksheet,you

canuseasmarttagtochoosetoincludetheXMLelementnameasaheadingaboveorjusttothe

leftofthesingle‐mappedcell,oryoucanuseanexistingcellvalueasaheading.Youcanalsousea

formulainasingle‐mappedcell,ifthecellismappedtoanXMLelementwithanXMLSchema

Definition(XSD)datatypethatExcelinterpretsasanumber,date,ortime.



WorkingwithrepeatingcellsinXMLtables

XMLtablesaresimilarinappearanceandfunctionalitytoExceltables.AnXMLtableisanExceltable

thathasbeenmappedtooneormoreXMLrepeatingelements.EachcolumnintheXMLtable

representsanXMLelement.AnXMLtableiscreatedwhenyou:



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

1. UsetheImportcommand(intheXMLgroupontheDevelopertab)toimportanXMLdata

file.

2. UsetheOpencommand(ontheMicrosoftOfficeButton)toopenanXMLdatafile—and

thenselectAsanXMLtableintheOpenXMLdialogbox.

3. UsetheFromXMLDataImportcommand(fromtheFromOtherSourcescommandbutton,

intheGetExternalDatagroup,ontheDatatab)toimportanXMLdatafile—andthen

selectXMLtableinexistingworksheetorNewworksheetintheImportDatadialogbox.

4. DragoneormorerepeatingelementsfromtheXMLSourcetaskpanetoaworksheet.

5. WhenyoucreateanXMLtable,theXMLelementnamesareautomaticallyusedascolumn

headings.Youcanchangethesetoanycolumnheadingsthatyouwant.However,the

originalXMLelementnamesarealwaysusedwhenyouexportdatafromthemappedcells.



TwooptionsundertheOptionsbuttonintheXMLSourcetaskpaneareusefulwhenyouworkwith

XMLtables:

1. AutomaticallyMergeElementsWhenMappingWhenselected,ExcelcreatesoneXML

tablefrommultiplefieldsastheyaredroppedontotheworksheet.Thisoptionworksaslong

asthemultiplefieldsaredroppedonthesamerow,oneadjacenttotheother.Whenthis

optioniscleared,eachelementappearsasitsownXMLtable.

2. MyDataHasHeadingsWhenselected,existingheadingdataisusedascolumnheadingsfor

repeatingelementsthatyoumaptoyourworksheet.Whenthisoptioniscleared,theXML

elementnamesareusedascolumnheadings.



UsingXMLtables,youcaneasilyimport,export,sort,filter,andprintdatabasedonanXMLdata

source.However,XMLtablesdohavesomelimitationsregardinghowtheycanbearrangedonthe

worksheet.

XMLtablesarerow‐based,meaningthattheygrowfromtheheaderrowdown.Youcannotaddnew

entriesaboveexistingrows.YoucannottransposeanXMLtablesothatnewentrieswillbeaddedto

theright.YoucanuseformulasincolumnsthataremappedtoXMLelementswithanXMLSchema

Definition(XSD)datatypethatExcelinterpretsasanumber,date,ortime.JustasinanExceltable,

formulasinanXMLtablearefilleddownthecolumnwhennewrowsareaddedtothetable.



XMLmapsecurityconsiderations

AnXMLmapanditsdatasourceinformationaresavedwiththeExcelworkbook,notaspecific

worksheet.AmalicioususercanviewthismapinformationbyusingaMicrosoftVisualBasicfor

Applications(VBA)macro.Furthermore,ifyousaveyourworkbookasamacro‐enabledExcelOpen

XMLFormatFile,thismapinformationcanbeviewedthroughMicrosoftNotepadorthrough

anothertext‐editingprogram.



Ifyouwanttokeepusingthemapinformationbutremovethepotentiallysensitivedatasource

information,youcandeletethedatasourcedefinitionoftheXMLschemafromtheworkbook,but

stillexporttheXMLdata,byclearingtheSavedatasourcedefinitioninworkbookcheckboxinthe

XMLMapPropertiesdialogbox,whichisavailablefromtheMapPropertiescommandintheXML

groupontheDevelopertab.



Ifyoudeleteaworksheetbeforeyoudeleteamap,themapinformationaboutthedatasources,

andpossiblyothersensitiveinformation,isstillsavedintheworkbook.Ifyouareupdatingthe

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

workbooktoremovesensitiveinformation,makesurethatyoudeletetheXMLmapbeforeyou

deletetheworksheet,sothatthemapinformationispermanentlyremovedfromtheworkbook.



ImportingXMLdata

YoucanimportXMLdataintoanexistingXMLmapinyourworkbook.Whenyouimportdata,you

bindthedatafromthefiletoanXMLmapthatisstoredinyourworkbook.Thismeansthateach

dataelementintheXMLdatafilehasacorrespondingelement,intheXMLschema,thatyou

mappedfromanXMLSchemafileorinferredschema.EachXMLmapcanonlyhaveoneXMLdata

binding,andanXMLdatabindingisboundtoallofthemappingsthatwerecreatedfromasingle

XMLmap.YoucandisplaytheXMLMapPropertiesdialogbox(ClickMapPropertiesintheXML

groupontheDevelopertab.),whichhasthreeoptions,allselectedbydefault,thatyoucansetor

cleartocontrolthebehaviorofanXMLdatabinding:



1. ValidatedataagainstschemaforimportandexportSpecifieswhetherExcelvalidatesdata

againsttheXMLmapwhenimportingdata.Clickthisoptionwhenyouwanttoensurethat

theXMLdatathatyouimportconformstotheXMLschema.

2. OverwriteexistingdatawithnewdataSpecifieswhetherdataisoverwrittenwhenyou

importdata.Clickthisoptionwhenyouwanttoreplacethecurrentdatawithnewdata,for

example,whenup‐to‐datedataiscontainedinthenewXMLdatafile.

3. AppendnewdatatoexistingXMLtablesSpecifieswhetherthecontentsofthedatasource

areappendedtotheexistingdataontheworksheet.Clickthisoption,forexample,when

youareconsolidatingdatafromseveralsimilarXMLdatafilesintoanXMLtable,oryoudo

notwanttooverwritethecontentsofacellthatcontainsafunction.



WhenyouimportXMLdata,youmaywanttooverwritesomemappedcellsbutnotothers.For

example,somemappedcellsmaycontainformulasandyoudon'twanttooverwritetheformula

whenyouimportanXMLfile.Therearetwoapproachesthatyoucantake:



1. Unmaptheelementsthatyoudon'twantoverwritten,beforeyouimporttheXMLdata.

AfteryouimporttheXMLdata,youcanremaptheXMLelementtothecellscontainingthe

formulas,sothatyoucanexporttheresultsoftheformulastotheXMLdatafile.

2. CreatetwoXMLmapsfromthesameXMLschema.UseoneXMLmapforimportingtheXML

data.Inthis"Import"XMLmap,don'tmapelementstothecellsthatcontainformulasor

otherdatathatyoudon'twantoverwritten.UseanotherXMLmapforexportingthedata.In

this"Export"XMLmap,maptheelementsthatyouwanttoexporttoanXMLfile.



TheabilitytoimportXMLdatafromaWebservicebyusingaDataRetrievalServiceConnection

(.uxdc)filetoconnecttoadatasourceisnolongersupportedinMicrosoftOfficeExcel2007through

theuserinterface.IfyouopenaworkbookthatwascreatedinOfficeExcel2003,youcanstillview

thedata,butyoucannoteditorrefreshthesourcedata.



Workingwithaninferredschema

IfyouimportXMLdatawithoutfirstaddingacorrespondingXMLschematocreateanXMLmap,

ExceltriestoinferaschemaforyoubasedonthetagsthataredefinedintheXMLdatafile.The

inferredschemaisstoredwiththeworkbook,andtheinferredschemaallowsyoutoworkwithXML

dataifanXMLschemafileisn'tassociatedwiththeworkbook.WhenyouworkwithimportedXML

datathathasaninferredschema,youcanalsocustomizetheXMLSourcetaskpane.Selectthe

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

PreviewDatainTaskPaneoptionfromtheOptionsbuttontodisplaythefirstrowofdataassample

dataintheelementlist,ifyouimportedXMLdataassociatedwiththeXMLmapinthecurrent

sessionofExcel.

YoucannotexporttheExcelinferredschemaasaseparateXMLschemadatafile(.xsd).Although

thereareXMLschemaeditorsandothermethodsforcreatinganXMLschemafile,youmaynot

haveconvenientaccesstothemorknowhowtousethem.Asanalternative,youcanusetheExcel

2003XMLToolsAdd‐inVersion1.1,whichcancreateaschemafilefromanXMLmap.Formore

information,seeUsingtheExcel2003XMLToolsAdd‐inVersion1.1.



ExportingXMLdata

YouexportXMLdatabyexportingthecontentsofmappedcellsontheworksheet.Whenyouexport

data,Excelappliesthefollowingrulestodeterminewhatdatatosaveandhowtosaveit:



1. Emptyitemsarenotcreatedwhenblankcellsexistforanoptionalelement,butemptyitems

arecreatedwhenblankcellsexistforarequiredelement.

2. UnicodeTransformationFormat‐8(UTF‐8)encodingisusedtowritethedata.

3. AllnamespacesaredefinedintheRootXMLelement.

4. Exceloverwritesexistingnamespaceprefixes.Thedefaultnamespaceisassignedaprefixof

ns0.Successivenamespacesaredesignatedns1,ns2tons<count>where<count>isthe

numberofnamespaceswrittentotheXMLfile.

5. Commentnodesarenotpreserved.

6. YoucandisplaytheXMLMapPropertiesdialogbox(ClickMapPropertiesintheXMLgroup

ontheDevelopertab.)andthenusetheValidatedataagainstschemaforimportand

exportoption(activebydefault)tospecifywhetherExcelvalidatesdataagainsttheXML

mapwhenexportingdata.ClickthisoptionwhenyouwanttoensurethattheXMLdatayou

exportconformstotheXMLschema.



UsingtheExcelMacro‐enabledOfficeXMLFormatFile

YoucansaveanExcelworkbookinavarietyoffileformats,includingtheExcelmacro‐enabledOffice

XMLFormatFile(.xlsm).ExcelhasadefinedXMLschemathatdefinesthecontentsofanExcel

workbook,includingXMLtagsthatstoreallworkbookinformation,suchasdataandproperties,and

definetheoverallstructureoftheworkbook.CustomapplicationscanusethisExcelmacro‐enabled

OfficeXMLFormatFile.Forexample,developersmaywanttocreateacustomapplicationtosearch

fordatainmultipleworkbooksthataresavedinthethisformatandcreateareportingsystembased

onthedatafound.





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



Chapter8

ExcelQuickTips

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

50QuickTips



TheExcelworkbookusedinclasstodemonstratethesequicktipscanbedownloaded

instantlyatthefollowingaddress:www.ExcelAdvisor.netPassword:2007collins

1RightClickStatusBar‐(Viewsums,averages,mins,maxesimmediately)

2CTRL+MouseScroll‐(Zoomin&outwithyourmouse)

3DoubleClicktheFormatPainter‐(Toolsticksuntilclickedagain)

4ReplaceFormatting‐(Findandreplaceoneformattingwithanother)

5ClickonEdgeofCell‐(Navigateinarangeofcells)

6TurnoffTaskPane‐(PutanendtoTaskPane)

7ControlTilde(CTRL+~)‐(Viewunderlyingformulas)

8IndentIcon‐(Indentcellsorcolumnsinstantly)

9ALT+DownArrow(orShift‐F10)‐(Pickfromadropdownlist)

10F4‐(Repeatthelastcommandsuchasinsertrowsorchangerowheight)

11Alt+Enter‐(Wraptextinstantly)

12&‐(Combinetextfrommultiplecells)

13RightClickTab,Copy,CreateCopy‐(Insertnewsheetwithheaders,footers,etc)

14File,SendTo,MailRecipient‐(E‐Mailaworksheet,workbookorchart)

15ComboBoxfromFormsToolbar‐(Insertacombobox)

16DoubleClickFillHandle‐(Copiesformuladowntherelevantrange)

17=Upper,=Lower,=Proper‐(Changetextcase)

18PasteSpecial,Values‐(Convertformulastonumbers)

19F4inEditMode‐(ToggleAbsoluteReferences)

20PasteSpecial,Transpose‐(Invertamatrixofnumbers)

21Ctrl+D‐(CopyDatatothedown)

22Ctrl+R‐(Copydatatotheright)

23DefinedNames‐(Refertonamesratherthancelladdresses)

24Data,AutoFilter,Advanced,Copy,Unique‐(Extractuniquevalues)

25Tools,Options,Calculation,PrecisionasDisplayed(Avoidroundingerrors)

26RightClickToolbar,Options,ShowFullMenus‐(Showallmenuoptions)

27Tools,View,ZeroValues‐(Hidezerovalues)

28Tools,AutoCorrect,SmartTags,None‐(TurnoffSmartTags)

29FilterData,ApplyColor,Un‐filterData‐(Colorfilteredresults)

30PDF2XL‐($95productconvertsPDF'stoExcelfiles)

31DataValidation‐(Insertapopupcommentintoacell)

32Ctrl+Shft+End‐(Selectrowtotheright)

33Ctrl+Shft+Home‐(Selectrowtotheleft)

34F11‐(Produceaquickchart)

35PrintAreainNameBox‐(Quicklyidentifytheprintarea)

36DisplayingtheStylesTool‐(Toolbaraccesstostyles)

37Format,Styles‐(Createnewstyles)

38ControlPanel,RegionalOptions‐(Controlhowdatesaredisplayed)

39=Substitute‐(Removeorreplaceunwantedcharacters)

40View,SizedwithWindow‐(ResizescharttofitWindow)

41Ctrl+Spacebar‐(Selectacolumn)

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

42Shift+Spacebar‐(Selectarow)

43Alt+Tab‐(TogglebetweenapplicationsorExcelworkbooks)

44DeleteBlankRowsandColumns,Ctrl+S‐(Reducerelevantareaandscrollbar)

45CopyFormula,toBlankCells‐(Fillinmissingdatainalist)

46=ISTEXT(A1)‐(Usethisformulainconditionalformattingtoformattextonly)

47Replace,Within‐(Tipforreplacingthroughoutentireworkbook)

48=LEN(A1)‐LEN(SUBSTITUTE(A1,A2,""))‐(Counttheoccurrenceofacharacter)

49=MID(A25,FIND("*",SUBSTITUTE(A25,"\","*",LEN(A25)‐

LEN(SUBSTITUTE(A25,"\",""))))+1,LEN(A25))‐(ExtracttheFilename)

50=SumIF‐(Sumonlythosenumbersthatmeetspecificcriteria)



BonusTips–20AdditionalExcelTips:



1. =Left

2. =Find

3. =Mid

4. =Right

5. =Len

6. Transitionturnedon

7. Moveoneditturnedoff

8. AutoCorrect

9. RenameTab

10. ColorTab

11. ReorderTabs

12. SelectMultipleTabs

13. Fixeddecimalplaces

14. TurnoffAutoComplete‐Tools,Options,Edit

15. EnterFormatsautomatically

16. Show50/9recentlyusedfiles

17. DefaultFileFormat‐Tools,Options,Transition

18. EmbeddedVoiceclips

19. EmbeddedVideoClips

20. OrganizationCharts





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

Chapter9

What’sNewin

Excel2007?

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



EnhancementstoExcel2007



1. MoreColumns‐IncreasedthetotalnumberofavailablecolumnsinExcelfrom256(2^8)to

16,000(2^14).



2. MoreRows‐IncreasedthetotalnumberofavailablerowsinExcelfrom64,000(2^16)to

1,000,000(2^20).



3. MoreMemory‐IncreasedthetotalamountofPCmemorythatExcelcanusefrom1GBtoas

muchRAMasWindowssees.



4. DualCoreChips‐OfficeExcel2007supportsmultiplemicrocomputerprocessorsand

multithreadedchipsets.



5. MoreColors‐Excel2007nowsupportsupto16millioncolors,upfrom256.



6. SortbyColor.Wow!



7. MoreSortingCriteria‐Increasedthenumberoflevelsofsortingonarangeortablefrom3to

64.



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

8. MoreCharactersinacell‐Increasedthetotalnumberofcharactersthatcandisplayinacellby

32‐fold.Morespecifically,thislimitwasincreasedfrom1k(whenthetextisformatted)to32kor

unlimited(regardlessofformatting).



9. FindMoreitems‐Increasedthemaximumnumberofitemsfoundby“FindAll”from65,472to

2Billion.



10. MorePivotRows‐IncreasedthenumberofrowsallowedinaPivotTablefrom64kto1,000,000

(2^20).



11. ArraysReferenceMoreRows‐Eliminatedthelimitonthenumberofrowsofacolumnor

columnsthatcanbereferredtoinanarrayformula.



12. MoreConditionalFormats‐Increasedthenumberofconditionalformatconditionsonacell

from3conditionstolimitedbyavailablememory.



13. MoreAutoFilterResults‐IncreasedthenumberofitemsshownintheAuto‐Filterdropdown

from1,000to10,000.



14. PrintMoreCharactersinaCell‐IncreasedthenumberofcharacterspercellthatExcelcanprint

from1kto32k.



15. MoreStyles‐Increasedthetotalnumberofuniquecellstylesinaworkbook(combinationsof

allcellformatting)from4,000to64,000.



16. LargerFormulas‐Increasedthemaximumlengthofformulas(incharacters)from1,000

charactersto8,000characters.



17. MoreFormulaNesting‐IncreasedthenumberoflevelsofnestingthatExcelallowsinformulas

from7to64.



18. MoreArguments‐Increasedthemaximumnumberofargumentstoafunctionfrom30to255.



19. MorePivotColumns‐IncreasedthenumberofcolumnsallowedinaPivotTablefrom255to

16,000.



20. MoreUniquePivotItems‐IncreasedmaximumnumberofuniqueitemswithinasinglePivot

Fieldfrom32,000to1,000,000.



21. MorePivotFields‐Increasedthenumberoffields(asseeninthefieldlist)thatasingle

PivotTablecanhavefrom255to16,000.



22. LongerPivotNames‐IncreasedlengthoftheMDXnameforaPivotTableitem;alsothestring

lengthforarelationalPivotTablefrom255charactersto32,000.



23. BetterPivotTruncation‐Increasedthelengthatwhichfields’labelsaretruncatedwhenadded

toPivotTable;thisalsoincludescaptionlengthlimitationsfrom255to32,000.



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

24. BetterPartialCalculations‐Increasedthenumberofcellsthatmaydependonasinglearea

beforeExcelmustdofullcalculationsinsteadofpartialcalculations(becauseitcannolonger

trackthedependenciesrequiredtodopartialcalculations)from8,000tolimitedbyavailable

memory.



25. MoreArrayReferences‐Increasedthenumberofarrayformulasinaworksheetthatcanrefer

toanother(given)worksheetfrom65,000tolimitedbyavailablememory.



26. BucketMoreFunctions‐Increasedthenumberofcategoriesthatcustomfunctionscanbe

bucketedintofrom32to255.



27. BetterExternalUpdates‐Increasedthenumberofcharactersthatmaybeupdatedinanon‐

residentexternalworkbookreferencefrom255to32,000.



28. Themes‐Excelallowsuserstoformatdatabyapplyingathemeusingaspecificstyle.



29. ShareThemes‐Themescanbesharedacrossother2007Officereleaseprograms.Youcanalso

customizeathemestyle.



30. ChartStyles–Excelofferspredefinedchartstyles,butyoucannotcreateyourownchartstyles.



31. QuickerStyles‐Excelnowprovidesaquickermethodtoapplyapredefinedcellstyle.



32. BetterConditionalFormatting‐Useconditionalformattingtovisuallyannotateyourdatafor

bothanalyticalandpresentationpurposes.



33. StrongerConditionalFormatting‐Toeasilyfindexceptionsandtospotimportanttrendsinyour

data,youcanimplementandmanagemultipleconditionalformattingrulesthatapplyrichvisual

formattingintheformofgradientcolors,databars,andiconsetstodatathatmeetsthoserules.

Conditionalformatsarealsoeasytoapply—injustafewclicks,youcanseerelationshipsinyour

datathatyoucanuseforyouranalysispurposes.



34. Resizableformulabar‐ Theformulabarautomaticallyresizestoaccommodatelong,complex

formulas,whichpreventstheformulasfromcoveringotherdatainyourworksheet.



35. FunctionAutoComplete‐ FunctionAutoCompletehelpsuserswriteformulasusingtheproper

formulasyntax.



36. StructuredReferences‐Inadditiontocellreferences,suchasA1andR1C1,Excelnowprovides

structuredreferencestonamedrangesandtablesinaformula.



37. EasierAccesstoNamedRanges‐ Excelnamemanagerorganizes,updates,andmanages

multiplenamedrangesinacentrallocation,whichhelpsanyonewhoneedstoworkonyour

worksheetinterpretitsformulasanddata.



38. NewOLAP‐Whenyouworkwithmultidimensionaldatabases(suchasSQLServerAnalysis

Services)ExcelcanuseOLAPformulastobuildcomplex,freeform,OLAPdataboundreports.

NewcubefunctionsareusedtoextractOLAPdata(setsandvalues)fromAnalysisServicesand

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

displayitinacell.OLAPformulascanbegeneratedwhenyouconvertPivotTableformulasto

cellformulasorwhenyouuseAutoCompleteforcubefunctionargumentswhenyoutype

formulas.



39. EnhancedFiltering‐Filterdatabycolororbydates,displaymorethan1000itemsinthe

AutoFilterdrop‐downlist,selectmultipleitemstofilter,andfilterdatainPivotTables.



40. TableEnhancements‐Neworimprovedfunctionalityfortablesincludesthefollowingfeatures:



a. TableHeaderRows‐ Tableheaderrowscanbeturnedonoroff.Whentableheaders

aredisplayed,theystayvisiblewiththedatainthetablecolumnsbyreplacingthe

worksheetheaderswhenyoumovearoundinalongtable.



b. CalculatedColumns‐ Acalculatedcolumnusesasingleformulathatadjustsforeach

row.Itautomaticallyexpandstoincludeadditionalrowssothattheformulais

immediatelyextendedtothoserows.Allthatyouhavetodoisenteraformulaonce—

youdon'tneedtousetheFillorCopycommands.



c. AutomaticAutofiltering‐ AutoFilteristurnedonbydefaultinatabletoenable

powerfulsortingandfilteringoftabledata.



d. StructuredReferences‐Thistypeofreferenceallowsyoutousetablecolumnheader

namesinformulasinsteadofcellreferences,suchasA1orR1C1.



e. TotalRows‐Inatotalrow,youcannowusecustomformulasandtextentries.



f. TableStyles‐ Youcanapplyatablestyletoquicklyadddesigner‐quality,professional

formattingtotables.Ifanalternate‐rowstyleisenabledonatable,Excelwillmaintain

thealternatingstylerulethroughactionsthatwouldhavetraditionallydisruptedthis

layout,suchasfiltering,hidingrows,ormanualrearrangingofrowsandcolumns.



41. PresentationQualityCharts‐Newchartingtoolstocreateprofessional‐lookingcharts.Thenew,

up‐to‐datelookforchartsincludesspecialeffects,suchas3‐D,transparency,andsoftshadows.



42. ChartThemes–Chartsfollowthethemethatisappliedtoyourworkbook.



43. VisualChartElementPickers‐Quicklychangeeveryelementofthecharttobestpresentyour

data.Forexample,inafewclicks,youcanaddorremovetitles,legends,datalabels,trendlines,

andotherchartelements.



44. AModernlookwithOfficeArt‐ BecausechartsinExcelarenowdrawnwithOfficeArt,almost

anythingyoucandotoanOfficeArtshapecanalsobedonetoachartanditselements.For

example,youcanaddasoftshadoworbeveleffecttomakeanelementstandoutoruse

transparencytomakeelementsvisiblethatarepartiallyobscuredinachartlayout.Youcanalso

userealistic3‐Deffects.



45. ClearLinesandFonts‐Linesinchartsappearlessjagged,andClearTypefontsareusedfortext

toimprovereadability.

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



46. ChartTemplates‐Saveyourfavoritechartsasacharttemplate.



47. UsingExcelChartsinOtherPrograms‐ ChartssharedbetweenExcel,Word,andPowerPoint

nowincorporatethepowerfulchartingfeaturesofExcel,includingtheuseofformulas,filtering,

sorting,andtheabilitytolinkacharttoexternaldatasources,suchasMicrosoftSQLServerand

AnalysisServices(OLAP),forup‐to‐dateinformationinyourchart.



48. CopyingchartstootherprogramsChartscanbeeasilycopiedandpastedbetweendocuments

orfromoneprogramtoanother.WhenyoucopyachartfromExceltoWordorPowerPoint,it

automaticallychangestomatchtheWorddocumentorPowerPointpresentation,butyoucan

alsoretaintheExcelchartformat.TheExcelworksheetdatacanbeembeddedintheWord

documentorPowerPointpresentation,butyoucanalsoleaveitintheExcelsourcefile.



49. AnimatingchartsinPowerPoint InPowerPoint,youcanmoreeasilyuseanimationto

emphasizedatainanExcel‐basedchart.Youcananimatetheentirechartorthelegendentry

andaxislabels.Inacolumnchart,youcanevenanimateindividualcolumnstobetterillustratea

specificpoint.Animationfeaturesareeasiertofindandyouhavealotmorecontrol.For

example,youcanmakechangestoindividualanimationsteps,andusemoreanimationeffects.



50. NewPivotTableControls‐NewPivotTablecontrolsprovidebetterdraganddropzonetargets.



51. NewPivotTableFeatures‐Neworimprovedfeaturesareprovidedtosummarize,analyze,and

formatPivotTabledata.



52. UsingUndoinPivotTables‐ Youcannowundomostactionsthatyoutaketocreateor

rearrangeaPivotTable.



53. PlusandMinusDrill‐DownIndicators‐ Theseindicatorsareusedtoindicatewhetheryoucan

expandorcollapsepartsofthePivotTabletoseemoreorlessinformation.



54. SortingandfilteringSortingisnowassimpleasselectinganiteminthecolumnthatyouwant

tosortandusingsortbuttons.YoucanfilterdatabyusingPivotTablefilters,suchasdatefilters,

labelfilters,valuefilters,ormanualfilters.



55. Conditionalformatting YoucanapplyconditionalformattingtoanOfficeExcel2007Pivot

Tablebycellorbyintersectionofcells.



56. PivotTablestyleandlayoutJustlikeyoucanforExceltablesandcharts,youcanquicklyapplya

predefinedorcustomstyletoaPivotTable.AndchangingthelayoutofaPivotTableisalsomuch

easiertodointhenewuserinterface.



57. PivotCharts LikePivotTables,PivotChartsaremucheasiertocreateinthenewuserinterface.

AllofthefilteringimprovementsarealsoavailableforPivotCharts.Whenyoucreatea

PivotChart,specificPivotCharttoolsandcontextmenusareavailablesothatyoucananalyzethe

datainthechart.Youcanalsochangethelayout,style,andformatofthechartoritselements

thesamewaythatyoucanforaregularchart.InOfficeExcel2007,thechartformattingthat

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

youapplyispreservedwhenyoumakechangestothePivotChart,whichisanimprovementover

thewayitworkedinearlierversionsofExcel.



58. QuickConnectionsToExternalDataInOfficeExcel2007,younolongerneedtoknowtheserver

ordatabasenamesofcorporatedatasources.Instead,youcanuseQuickLaunchtoselectfrom

alistofdatasourcesthatyouradministratororworkgroupexperthasmadeavailableforyou.A

connectionmanagerinExcelallowsyoutoviewallconnectionsinaworkbookandmakesit

easiertoreuseaconnectionortosubstituteaconnectionwithanotherone.



59. NewFileFormatsXml‐BasedFileFormat In2007MicrosoftOfficesystem,Microsoftis

introducingnewfileformatsforWord,Excel,andPowerPoint,knownastheOfficeOpenXML

formats.Thesenewfileformatsfacilitateintegrationwithexternaldatasources,andalsooffer

reducedfilesizesandimproveddatarecovery.InOfficeExcel2007,thedefaultformatforan

ExcelworkbookistheOfficeExcel2007XML‐basedfileformat(.xlsx).OtheravailableXML‐based

formatsaretheOfficeExcel2007XML‐basedandmacro‐enabledfileformat(.xlsm),theOffice

Excel2007fileformatforanExceltemplate(.xltx),andtheOfficeExcel2007macro‐enabledfile

formatforanExceltemplate(.xltm).



60. OfficeExcel2007binaryfileformatInadditiontothenewXML‐basedfileformats,OfficeExcel

2007alsointroducesabinaryversionofthesegmentedcompressedfileformatforlargeor

complexworkbooks.Thisfileformat,theOfficeExcel2007Binary(orBIFF12)fileformat(.xls),

canbeusedforoptimalperformanceandbackwardcompatibility.



61. CompatibilitywithearlierversionsofExcel YoucancheckanOfficeExcel2007workbookto

seeifitcontainsfeaturesorformattingthatarenotcompatiblewithanearlierversionofExcel

sothatyoucanmakethenecessarychangesforbetterbackwardcompatibility.Inearlier

versionsofExcel,youcaninstallupdatesandconvertersthathelpyouopenanOfficeExcel2007

workbooksothatyoucaneditit,saveit,andopenitagaininOfficeExcel2007withoutlosing

anyOfficeExcel2007‐specificfunctionalityorfeatures.



62. PageLayoutViewTheNormalviewandPageBreakPreviewview,OfficeExcel2007providesa

PageLayoutView.Youcanusethisviewtocreateaworksheetwhilekeepinganeyeonhowit

willlookinprintedformat.Inthisview,youcanworkwithpageheaders,footers,andmargin

settingsrightintheworksheet,andplaceobjects,suchaschartsorshapes,exactlywhereyou

wantthem.YoualsohaveeasyaccesstoallpagesetupoptionsonthePageLayouttabinthe

newuserinterfacesothatyoucanquicklyspecifyoptions,suchaspageorientation.It'seasyto

seewhatwillbeprintedoneverypage,whichwillhelpyouavoidmultipleprintingattemptsand

truncateddatainprintouts.



63. SavingtoPDFandXPSformatYoucansaveasaPDForXPSfilefroma2007MicrosoftOffice

systemprogramonlyafteryouinstallanadd‐in.



64. UsingExcelServicestoshareyourworkIfyouhaveaccesstoExcelServices,youcanuseitto

shareyourOfficeExcel2007worksheetdatawithotherpeople,suchasexecutivesandother

stakeholdersinyourorganization.InOfficeExcel2007,youcansaveaworkbooktoExcel

Servicesandspecifytheworksheetdatathatyouwantotherpeopletosee.Inabrowser,they

canthenuseMicrosoftOfficeExcelWebAccesstoview,analyze,print,andextractthis

worksheetdata.Theycanalsocreateastaticsnapshotofthedataatregularintervalsoron

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

demand.OfficeExcelWebAccessmakesiteasytoperformactivities,suchasscrolling,filtering,

sorting,viewingcharts,andusingdrill‐downinPivotTables.YoucanalsoconnecttheExcelWeb

AccessWebParttootherWebPartstodisplaydatainalternativeways.Andwiththeright

permissions,ExcelWebAccessuserscanopenaworkbookinOfficeExcel2007sothattheycan

usethefullpowerofExceltoanalyzeandworkwiththedataontheirowncomputersifthey

haveExcelinstalled.Usingthismethodtoshareyourworkensuresthatotherpeoplehave

accesstooneversionofthedatainonelocation,whichyoucankeepcurrentwiththelatest

details.Ifyouneedotherpeople,suchasteammembers,tosupplyyouwithcommentsand

updatedinformation,youmaywanttoshareaworkbookthesamewaythatyoudidinearlier

versionsofExceltocollecttheinformationyouneedbeforeyousaveittoExcelServices.



65. UsingDocumentManagementServer ExcelServicescanbeintegratedwithDocument

ManagementServertocreateavalidationprocessaroundnewExcelreportsandworkbook

calculationworkflowactions,suchasacell‐basednotificationoraworkflowprocessbasedona

complexExcelcalculation.YoucanalsouseDocumentManagementServertoschedulenightly

recalculationofacomplexworkbookmodel.



66. QuickAccessToMoreTemplatesInOfficeExcel2007,youcanbaseanewworkbookona

varietyoftemplatesthatareinstalledwithExcel,oryoucanquicklyaccessanddownload

templatesfromtheMicrosoftOfficeOnlineWebsite.





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

What’sMissinginExcel2007?



WhileitmayappearthatthereisalotofstuffmissinginExcel2007comparedtothe2003edition,

almosteverythingisstillthere–youjustmayneedtolookforitawhiletofindit.Afewofthehard‐

to‐findthingsIhadtroublelocatedwereasfollows:



1. The“CreatePivotTablefromMultiplesources”featureisgonefromtheinsertPivotTable

menu,butyoucanstillfinditbycustomizingyourQuick‐Launchtoolbarandaddingthe

“PivotTableandChartWizard”icon.Yuwillseethatthisfunctionalityisstillthere.



2. Theabilitytosendaworksheetasbodyofe‐mailisalsogonefromtheStart,Send,E‐mail

option.butyoucanstillfinditbycustomizingyourQuick‐Launchtoolbarandaddingthe

“SendtomailRecipient”icon.Youwillseethatthisfunctionalityisalsostillthere.



3. ThedataFormtoolisstillthere–youhavetoadd“Form”totheQuickAccessToolBar.



4. The“SpeakCells”commandismissingfromtheribbons,butthiscommandisstillavailable

bycustomizingtheQuickLaunchtoolbarandaddingthe“SpeakCells”command.



5. Infactthereareatotalof219commandsinExcelthatdonotappearontheExcelRibbon–

youcanviewacompletelistofthesecommandsbycustomizingtheQuickLaunchToolbar

andchoosingtheoptiontoview“CommandsNotintheRibbon”.



However,therearesomethingsthathavedisappearedasfollows:



1. Nomorepublishinginteractivewebpages.



2. EmbeddedvideoclipsandsoundclipsnolongerplaywithinExcel,theyswitchtheuserover

toMediaplayerinstead.



3. TheAutoFormatasweknewitisgone.Ithasbeenreplacedwiththe“FormatasTable”

optionintheStylesgroupoftheHometab,butitdoesmorethanformatyourtable.It

convertsyourtabletowhatExcelusedtocallanExcelList,completewithlistarrowsand

filteroptionsandallkindsofjunkyoureallydon'tneedandprobablydon'twantifyourgoal

isjusttodressupyourdata.Unfortunatelythisnewfunctionalitydoesnotapplyaunique

formattosubtotalrowslikeExcel2003did.Bummer.Thereforeyoumustcollapseyour

rowsinOutline,selectvisiblecells,andapplyadifferentcolor,thenexpandyourrowsagain

topulloffthistypeofformat.



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



What’sNewin

MicrosoftExcel2010



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

What’sNewinMicrosoftOffice2010?

MicrosofthasannouncedthatitwillbeginshippingOffice2010inSpring2010.Thisbegsthe

question,what’snewinOffice2010?Theshortansweristhatthereareadozenorso

interestingimprovementseachinWord,Excel,PowerPoint,andAccess‐butforthemost

partOffice’scoreproductsareunchanged.ThisisbecausewhenOffice2007wasreleasedin

January2007,thenewmenusonlyappearedin4.5oftheapplications–Word,Excel,

Access,PowerPointandhalfofOutlook.WithOffice2010,Microsofthasbroughttheother

applicationsup‐to‐datewiththenewermenustyleaswell.Thereforedon’texpecttosee

manydifferencesinWord,Excel,Access,orPowerPoint.However,theseareafew

improvementsOffice2010’scoreproducts,asfollows:

What’sNewinExcel2010?

1. Sparklines–Sparklinesaresmallcell‐sizedchartsthatyoucanembedinaworksheet

nexttodatatogetaquickvisualrepresentationofthedata.Forexample,ifyouhad

aworksheetthattrackedtheperformanceofseveraldozenstocks,youcouldcreate

aSparklineforeachstockthatgrapheditsperformanceovertime,inaverycompact

way.Hereisanexample:



2. ConditionalFormattingImprovements–Microsofthasimprovedandaddedmore

stylesandiconsregardingtheabilitytoapplyaformattoarangeofcells,andthen

havetheformattingchangeaccordingtothevalueofthecellorformula.



3. ImprovedSharing–AswithalloftheOffice2010applications,Excel2010hasnew

andimprovedtoolsforsharingdatawithotherpeople,includingmultiplepeople

workingonadocumentatatime.



4. MillionsofRows‐MicrosoftnowoffersProjectGeminiadd‐onforExcel2010that

canhandleverylargeamountsofdata‐‐evenworksheetsthatincludehundredsof

millionsofrows.ItwillshipaspartofSQLServer2008R2inthefirsthalfof2010;a

communitytechnologypreviewwillbeavailableinthesecondhalfof2009.



5. EnhancedRibbonToolbar‐TheRibbontoolbarhasbeenenhancedtobehighly

customizablenow.



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



6. Compatibilityof.xlsx‐InExcel2007,MicrosoftintroducedanewXMLformat(.xlsx)which

wasnotcompatiblewithformer.xlsspreadsheetformats.Thisproblemhasbeenresolved‐

ExcelfilescreatedinExcel2010mayeasilybeopenedinversionsofExcelpriortoExcel

2007.NowExcel2010willsavefilesjustassafeastheformerExcel2007,andthe

spreadsheetsizeis75%smallerthantheold.XLSversion.



7.ProtectedMode‐Eachtimeyoudownloadadocument,MicrosoftOffice2010

automaticallyopensitinProtectedModewhichmeansthatExcelwillnotallowyoutoedit

thedocumentsunlessdocumenteditingisenabled.Todothis,clicktheEnableEditing

optioninordertoenabledocumenteditingasshowninthefollowingscreenshot.



7. Slicer‐TheSlicerfeatureprovidesnewsliceanddicecapabilitieswithinPivotTables–this

helpsyoudynamicallysegmentandfilterthedata.ThisfeatureislocatedontheInsertTab.

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



8. Macro‐MacrosinExcel2010nowsupportworkingwithshapes,includingcreating,moving

oreditingshapes.



9. EnhancedChartDiagram‐InExcel2010doubleclickingachartelementautomatically

opensthechart’sformatdialogbox.



10. WebVersionofExcel2010–AnewwebversionofExcel2010allowsyoutocreate,editand

savespreadsheetviayourwebbrowserdirectlyandsharethemonline.Thewebversion

reportedlywillbeavailableforfreetoeveryonewhohasWindowsLiveaccount.Theweb

versionisassameastheExcel2010ondesktop,withsomeofExcelfunctionalitydisabled.



11. 64‐bitversionofExcel2010‐Excel2010isnowavailableina64‐bitversion,whichmeans

thatitcantakefulladvantageofyourcomputer’s64‐bitmotherboardandaccessmorethan

4GBsofRAM.Theresultisevenfasterperformance.



12. MicrosoftSQLServerPowerPivotforExcel‐Excel2010nowincludesanadd‐intoolcalled

MicrosoftSQLServerPowerPivotwhichisaBusinessIntelligencetoolthatenablesyouto

querymultipleSQLServerdatabasesacrossmultiplecorporatesystemsandwebdataona

real‐timebasistoproducePivotTablesthatcanbesharedviaSharePoint.Youcantrythis

onlineatthefollowingVirtualLabswebsite:

https://cmg.vlabcenter.com/prepare.aspx?moduleid=ad3bd3e9‐8d2b‐498d‐94fa‐

e41e1b09730d&ticks=633992819904236083.



13. NamedSets‐NamedSetshavebeenaddedtoMicrosoftExcel2010,allowsyoutocreate

yourownnamedsets.SimplylocatetheFields,Items,&SetsbuttonundertheRibbon,and

itwillallowyoutodefineyourownNamedSets.

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

BioforJ.CarltonCollins,CPA

ASAResearchCarlton@ASAResearch.com770.734.0950



J.CarltonCollins,CPAisaCertifiedPublicAccountantwithexperienceintechnology,

tax,auditing,accountingsystems,financialreporting,andbondfinancing.Heisan

author,lecturer,andtechnology&accountingsystemsconsultant.Hehaspublished

morethantwodozenbooks,twohundredarticles,andthousandsofwebpages.Asa

publicspeaker,Mr.Collinshasdeliveredmorethan2,000lecturesin44statesand5

countriesaddressingmorethan500,000businessprofessionals,includingnumerous

keynotelecturesatnationalandinternationalconferences.Keyawardsinclude:"AICPALifetimeAchievement

Award","TomRadcliffeOutstandingDiscussionLeaderAward","GSCPAOutstandingDiscussionLeaderAward",

and"AccountingTechnologies'TopTenCPATechnologistsAward".Asaconsultant,Mr.Collinshasassisted275+

largeandsmallcompanieswiththeselectionandimplementationofaccountingsystems.Mr.Collinshasa

BachelorsdegreeinAccountingfromtheUniversityofGeorgia,isa26yearmemberoftheAICPAandtheGeorgia

SocietyofCPAs,andisalsoalicensedrealtor.



AttheUniversityofGeorgiaMr.CollinswaselectedPresidentofthePhiEtaSigmaHonorSociety,wasinitiatedinto

theBIFTADHonorSociety,wasamemberofAlphaTauOmegafraternity,andservedthreeyearsintheJudicial

Defender/Advocateprogram.AtGlynnAcademyHighSchoolMr.CollinswasSeniorClassPresident,Class

Valedictorian,andreceivedaprinciplenominationtoAnnapolisNavalAcademy.Mr.Collinshasbeenmarriedfor

25yearsandhastwochildren.Hedevoteshisleisuretimetofamily,travel,tennis,fishing,snowskiing,andriding

motorcycles(dirtandstreet).Mr.Collinsispresidentofhishomeownersassociation,participatesintheGwinnett

CleanandBeautifulprogram,andvolunteersforCooperativeMinistriesfooddrive.



SelectedPositions,Awards&Accomplishments:



1. 2008and2009ChairmanoftheSoutheastAccountingShow‐thesouth'slargestCPAevent.

2. Recipientofthe2008TomRadcliffOutstandingDiscussionLeaderAward.

3. Named“TopTenCPATechnologists”byAccountingTechnologiesMagazine;Named“Top100Most

InfluentialCPAs”byAccountingTechnologiesMagazineinmultipleyears.

4. Haspersonallydeliveredover1,500technologylecturesaroundtheworld.

5. Haspublished80+pagesofaccountingsoftwarearticlesintheJournalofAccountancy.

6. RecipientoftheAICPALifetimeTechnicalContributiontotheCPAProfessionAward.

7. RecipientoftheOutstandingDiscussionLeaderAwardfromtheGeorgiaSocietyofCPAs.

8. LeadauthorforPPC'sGuidetoInstallingMicrocomputerAccountingSystems.

9. Hasinstalledaccountingsystemsformorethan200companies.

10. PastChairpersonoftheAICPATechnologyConference.

11. Hasdeliveredkeynoteandsessionlecturesatdozensofaccountingsoftwareconferencesincludingseven

MicrosoftPartnerConferences,fiveSageConferences,andmultipleconferencesforEpicor,Open

Systems,ExactSoftware,SageACCPACERP,Dynamics.NAV,Dynamics.AX,SouthWare,Axapta.

12. Hasprovidedconsultingservicestomanycomputercompanies(includingCompaq,IBM,Microsoft,Apple,

Novell,Peachtree,Epicor,SageSoftware,Exact,ACCPAC,Intuit,Peachtree,GreatPlains,andothers).



AsanauditorMr.Collinshasauditedbusinessesintheareasofhealthcare,construction,distribution,automobile

dealerships,insurance,manufacturing,andgeneralbusiness. Mr.Collins'taxexperienceincludescorporate,

individual,partnership,fiduciary,andestatetaxplanningwork. Intheareaoffinance,Mr.Collinshasprepared

feasibilitystudiesandfinancialforecastsfornearly300projectsseekingmorethan$3billioninstartupcapital,

includingfieldworkfor80ofthoseprojects.Mr.Collinsisfamiliarwithbondissues,MedicareandMedicaid

reimbursement,andconventionalfinancingmatters.Asaconsultant,Mr.CollinsworkedwiththeentireMicrosoft

Exceldevelopmentteamcontributingmorethan500pagesofdesignimprovements‐manyofwhicharefoundin

Exceltoday.

2010 Excel Functions Macros And Data Commands Manual As Of March

Navigation menu

Versions of this User Manual:

Views

Navigation