2010 Excel Functions Macros And Data Commands Manual As Of March

User Manual:

Open the PDF directly: View PDF .
Page Count: 108

Download
Open PDF In Browser	View PDF

ASA Research

Microsoft Excel
Functions, Macros &
Data Commands

J. Carlton Collins, CPA
ASA Research
Atlanta, Georgia
770.734.0950
Carlton@ASAResearch.com

Excel Functions, Macros & Data Commands

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

www.CarltonCollins.com

Page 2

Excel Functions, Macros & Data Commands

Excel Functions, Macros & Data Commands
Course Information
Learning Objectives
Course Level
Pre‐Requisites
Advanced Preparation
Presentation Method
Recommended CPE Credit
Handouts
Instructors

To increase the productivity of accountants and CPAs
using Excel’s functions, macros, & data commands
Intermediate
Good Familiarity with Microsoft Excel
None
Live lecture using full color projection systems and
live Internet access with follow up course materials
8 hours
Templates, checklists, web examples, manual
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.

Copyright © May 2010, AdvisorCPE and Accounting Software Advisor, LLC
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

www.CarltonCollins.com

Page 3

Excel Functions, Macros & Data Commands

Chapter 1

Excel Functions

www.CarltonCollins.com

Page 4

Excel Functions, Macros & Data Commands

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.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

Database Functions (12)
Date and Time Functions (20)
Engineering Functions (39)
Financial Functions (53)
Information Functions (17)
Logical Functions (6)
Lookup and Reference Functions (18)
Math and Trigonometry Functions (59)
Statistical Functions (80)
Text Functions (27)
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
www.CarltonCollins.com

Page 5

Excel Functions, Macros & Data Commands
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

www.CarltonCollins.com

Page 6

Excel Functions, Macros & Data Commands
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

1
2
3
4
5
6
7
8
9
10

Database Functions
Function
Description
DAVERAGE
Returns the average of selected database entries
DCOUNT
Counts the cells that contain numbers in a database
DCOUNTA
Counts nonblank cells in a database
DGET
Extracts from a database a single record that matches the specified criteria
DMAX
Returns the maximum value from selected database entries
DMIN
Returns the minimum value from selected database entries
DPRODUCT
Multiplies the values in a particular field of records that match the criteria in
a database
DSTDEV
Estimates the standard deviation based on a sample of selected database
entries
DSTDEVP
Calculates the standard deviation based on the entire population of selected
database entries
DSUM
Adds the numbers in the field column of records in the database that match
the criteria

www.CarltonCollins.com

Page 7

Excel Functions, Macros & Data Commands
11
12

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

33
34
35
36
37
38
39

DVAR
DVARP

Estimates variance based on a sample from selected database entries
Calculates variance based on the entire population of selected database
entries

Date and Time Functions
Function
Description
DATE
Returns the serial number of a particular date
DATEVALUE
Converts a date in the form of text to a serial number
DAY
Converts a serial number to a day of the month
DAYS360
Calculates the number of days between two dates based on a 360‐day year
EDATE
Returns the serial number of the date that is the indicated number of months
before or after the start date
EOMONTH
Returns the serial number of the last day of the month before or after a
specified number of months
HOUR
Converts a serial number to an hour
MINUTE
Converts a serial number to a minute
MONTH
Converts a serial number to a month
NETWORKDAYS
Returns the number of whole workdays between two dates
NOW
Returns the serial number of the current date and time
SECOND
Converts a serial number to a second
TIME
Returns the serial number of a particular time
TIMEVALUE
Converts a time in the form of text to a serial number
TODAY
Returns the serial number of today's date
WEEKDAY
Converts a serial number to a day of the week
WEEKNUM
Converts a serial number to a number representing where the week falls
numerically with a year
WORKDAY
Returns the serial number of the date before or after a specified number of
workdays
YEAR
Converts a serial number to a year
YEARFRAC
Returns the year fraction representing the number of whole days between
start_date and end_date
Engineering Functions
Function
Description
BESSELI
Returns the modified Bessel Function In(x)
BESSELJ
Returns the Bessel Function Jn(x)
BESSELK
Returns the modified Bessel Function Kn(x)
BESSELY
Returns the Bessel Function Yn(x)
BIN2DEC
Converts a binary number to decimal
BIN2HEX
Converts a binary number to hexadecimal
BIN2OCT
Converts a binary number to octal

www.CarltonCollins.com

Page 8

Excel Functions, Macros & Data Commands
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

COMPLEX
CONVERT
DEC2BIN
DEC2HEX
DEC2OCT
DELTA
ERF
ERFC
GESTEP
HEX2BIN
HEX2DEC
HEX2OCT
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMDIV
IMEXP
IMLN
IMLOG10
IMLOG2
IMPOWER
IMPRODUCT
IMREAL
IMSIN
IMSQRT
IMSUB
IMSUM
OCT2BIN
OCT2DEC
OCT2HEX

72
73
74

Financial Functions
Function
ACCRINT
ACCRINTM
AMORDEGRC

AMORLINC

Converts real and imaginary coefficients into a complex number
Converts a number from one measurement system to another
Converts a decimal number to binary
Converts a decimal number to hexadecimal
Converts a decimal number to octal
Tests whether two values are equal
Returns the error Function
Returns the complementary error Function
Tests whether a number is greater than a threshold value
Converts a hexadecimal number to binary
Converts a hexadecimal number to decimal
Converts a hexadecimal number to octal
Returns the absolute value (modulus) of a complex number
Returns the imaginary coefficient of a complex number
Returns the argument theta, an angle expressed in radians
Returns the complex conjugate of a complex number
Returns the cosine of a complex number
Returns the quotient of two complex numbers
Returns the exponential of a complex number
Returns the natural logarithm of a complex number
Returns the base‐10 logarithm of a complex number
Returns the base‐2 logarithm of a complex number
Returns a complex number raised to an integer power
Returns the product of from 2 to 29 complex numbers
Returns the real coefficient of a complex number
Returns the sine of a complex number
Returns the square root of a complex number
Returns the difference between two complex numbers
Returns the sum of complex numbers
Converts an octal number to binary
Converts an octal number to decimal
Converts an octal number to hexadecimal

Description
Returns the accrued interest for a security that pays periodic interest
Returns the accrued interest for a security that pays interest at maturity
Returns the depreciation for each accounting period by using a depreciation
coefficient
Returns the depreciation for each accounting period

www.CarltonCollins.com

Page 9

Excel Functions, Macros & Data Commands
76

COUPDAYBS

COUPDAYS

COUPDAYSNC

79
80

COUPNCD
COUPNUM

81
82
83
84

COUPPCD
CUMIPMT
CUMPRINC
DB

DDB

86
87

DISC
DOLLARDE

DOLLARFR

89
90
91
92

DURATION
EFFECT
FV
FVSCHEDULE

93
94
95
96
97

INTRATE
IPMT
IRR
ISPMT
MDURATION

MIRR

99 NOMINAL
100 NPER
101 NPV
102
103
104
105
106

ODDFPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
PMT

Returns the number of days from the beginning of the coupon period to the
settlement date
Returns the number of days in the coupon period that contains the
settlement date
Returns the number of days from the settlement date to the next coupon
date
Returns the next coupon date after the settlement date
Returns the number of coupons payable between the settlement date and
maturity date
Returns the previous coupon date before the settlement date
Returns the cumulative interest paid between two periods
Returns the cumulative principal paid on a loan between two periods
Returns the depreciation of an asset for a specified period by using the fixed‐
declining balance method
Returns the depreciation of an asset for a specified period by using the
double‐declining balance method or some other method that you specify
Returns the discount rate for a security
Converts a dollar price, expressed as a fraction, into a dollar price, expressed
as a decimal number
Converts a dollar price, expressed as a decimal number, into a dollar price,
expressed as a fraction
Returns the annual duration of a security with periodic interest payments
Returns the effective annual interest rate
Returns the future value of an investment
Returns the future value of an initial principal after applying a series of
compound interest rates
Returns the interest rate for a fully invested security
Returns the interest payment for an investment for a given period
Returns the internal rate of return for a series of cash flows
Calculates the interest paid during a specific period of an investment
Returns the Macauley modified duration for a security with an assumed par
value of $100
Returns the internal rate of return where positive and negative cash flows are
financed at different rates
Returns the annual nominal interest rate
Returns the number of periods for an investment
Returns the net present value of an investment based on a series of periodic
cash flows and a discount rate
Returns the price per $100 face value of a security with an odd first period
Returns the yield of a security with an odd first period
Returns the price per $100 face value of a security with an odd last period
Returns the yield of a security with an odd last period
Returns the periodic payment for an annuity

www.CarltonCollins.com

Page 10

Excel Functions, Macros & Data Commands
107
108
109
110

PPMT
PRICE
PRICEDISC
PRICEMAT

111
112
113
114
115

PV
RATE
RECEIVED
SLN
SYD

116
117
118
119

TBILLEQ
TBILLPRICE
TBILLYIELD
VDB

120 XIRR
121 XNPV
122 YIELD
123 YIELDDISC
124 YIELDMAT

125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

Returns the payment on the principal for an investment for a given period
Returns the price per $100 face value of a security that pays periodic interest
Returns the price per $100 face value of a discounted security
Returns the price per $100 face value of a security that pays interest at
maturity
Returns the present value of an investment
Returns the interest rate per period of an annuity
Returns the amount received at maturity for a fully invested security
Returns the straight‐line depreciation of an asset for one period
Returns the sum‐of‐years' digits depreciation of an asset for a specified
period
Returns the bond‐equivalent yield for a Treasury bill
Returns the price per $100 face value for a Treasury bill
Returns the yield for a Treasury bill
Returns the depreciation of an asset for a specified or partial period by using
a declining balance method
Returns the internal rate of return for a schedule of cash flows that is not
necessarily periodic
Returns the net present value for a schedule of cash flows that is not
necessarily periodic
Returns the yield on a security that pays periodic interest
Returns the annual yield for a discounted security; for example, a Treasury bill
Returns the annual yield of a security that pays interest at maturity

Information Functions
Function
Description
CELL
Returns information about the formatting, location, or contents of a cell
ERROR.TYPE
Returns a number corresponding to an error type
INFO
Returns information about the current operating environment
ISBLANK
Returns TRUE if the value is blank
ISERR
Returns TRUE if the value is any error value except #N/A
ISERROR
Returns TRUE if the value is any error value
ISEVEN
Returns TRUE if the number is even
ISLOGICAL
Returns TRUE if the value is a logical value
ISNA
Returns TRUE if the value is the #N/A error value
ISNONTEXT
Returns TRUE if the value is not text
ISNUMBER
Returns TRUE if the value is a number
ISODD
Returns TRUE if the number is odd
ISREF
Returns TRUE if the value is a reference
ISTEXT
Returns TRUE if the value is text
N
Returns a value converted to a number

www.CarltonCollins.com

Page 11

Excel Functions, Macros & Data Commands
140 NA
141 TYPE
17
Logical Functions
Function
142 AND
143 FALSE
144 IF
145 NOT
146 OR
147 TRUE

148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163

164
165

Returns the error value #N/A
Returns a number indicating the data type of a value

Description
Returns TRUE if all of its arguments are TRUE
Returns the logical value FALSE
Specifies a logical test to perform
Reverses the logic of its argument
Returns TRUE if any argument is TRUE
Returns the logical value TRUE

Lookup and Reference Functions
Function
Description
ADDRESS
Returns a reference as text to a single cell in a worksheet
AREAS
Returns the number of areas in a reference
CHOOSE
Chooses a value from a list of values
COLUMN
Returns the column number of a reference
COLUMNS
Returns the number of columns in a reference
GETPIVOTDATA
Returns data stored in a PivotTable
HLOOKUP
Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK
Creates a shortcut or jump that opens a document stored on a network
server, an intranet, or the Internet
INDEX
Uses an index to choose a value from a reference or array
INDIRECT
Returns a reference indicated by a text value
LOOKUP
Looks up values in a vector or array
MATCH
Looks up values in a reference or array
OFFSET
Returns a reference offset from a given reference
ROW
Returns the row number of a reference
ROWS
Returns the number of rows in a reference
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).)
TRANSPOSE
Returns the transpose of an array
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

www.CarltonCollins.com

Page 12

Excel Functions, Macros & Data Commands
166
167
168
169
170
171
172
173
174

ABS
ACOS
ACOSH
ASIN
ASINH
ATAN
ATAN2
ATANH
CEILING

175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204

COMBIN
COS
COSH
DEGREES
EVEN
EXP
FACT
FACTDOUBLE
FLOOR
GCD
INT
LCM
LN
LOG
LOG10
MDETERM
MINVERSE
MMULT
MOD
MROUND
MULTINOMIAL
ODD
PI
POWER
PRODUCT
QUOTIENT
RADIANS
RAND
RANDBETWEEN
ROMAN

Returns the absolute value of a number
Returns the arccosine of a number
Returns the inverse hyperbolic cosine of a number
Returns the arcsine of a number
Returns the inverse hyperbolic sine of a number
Returns the arctangent of a number
Returns the arctangent from x‐ and y‐coordinates
Returns the inverse hyperbolic tangent of a number
Rounds a number to the nearest integer or to the nearest multiple of
significance
Returns the number of combinations for a given number of objects
Returns the cosine of a number
Returns the hyperbolic cosine of a number
Converts radians to degrees
Rounds a number up to the nearest even integer
Returns e raised to the power of a given number
Returns the factorial of a number
Returns the double factorial of a number
Rounds a number down, toward zero
Returns the greatest common divisor
Rounds a number down to the nearest integer
Returns the least common multiple
Returns the natural logarithm of a number
Returns the logarithm of a number to a specified base
Returns the base‐10 logarithm of a number
Returns the matrix determinant of an array
Returns the matrix inverse of an array
Returns the matrix product of two arrays
Returns the remainder from division
Returns a number rounded to the desired multiple
Returns the multinomial of a set of numbers
Rounds a number up to the nearest odd integer
Returns the value of pi
Returns the result of a number raised to a power
Multiplies its arguments
Returns the integer portion of a division
Converts degrees to radians
Returns a random number between 0 and 1
Returns a random number between the numbers you specify
Converts an arabic numeral to roman, as text

www.CarltonCollins.com

Page 13

Excel Functions, Macros & Data Commands
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219

ROUND
ROUNDDOWN
ROUNDUP
SERIESSUM
SIGN
SIN
SINH
SQRT
SQRTPI
SUBTOTAL
SUM
SUMIF
SUMPRODUCT
SUMSQ
SUMX2MY2

220 SUMX2PY2
221 SUMXMY2
222 TAN
223 TANH
224 TRUNC

225
226
227
228
229
230
231
232
233
234
235
236

Rounds a number to a specified number of digits
Rounds a number down, toward zero
Rounds a number up, away from zero
Returns the sum of a power series based on the formula
Returns the sign of a number
Returns the sine of the given angle
Returns the hyperbolic sine of a number
Returns a positive square root
Returns the square root of (number * pi)
Returns a subtotal in a list or database
Adds its arguments
Adds the cells specified by a given criteria
Returns the sum of the products of corresponding array components
Returns the sum of the squares of the arguments
Returns the sum of the difference of squares of corresponding values in two
arrays
Returns the sum of the sum of squares of corresponding values in two arrays
Returns the sum of squares of differences of corresponding values in two
arrays
Returns the tangent of a number
Returns the hyperbolic tangent of a number
Truncates a number to an integer

Statistical Functions
Function
Description
AVEDEV
Returns the average of the absolute deviations of data points from their
mean
AVERAGE
Returns the average of its arguments
AVERAGEA
Returns the average of its arguments, including numbers, text, and logical
values
BETADIST
Returns the beta cumulative distribution Function
BETAINV
Returns the inverse of the cumulative distribution Function for a specified
beta distribution
BINOMDIST
Returns the individual term binomial distribution probability
CHIDIST
Returns the one‐tailed probability of the chi‐squared distribution
CHIINV
Returns the inverse of the one‐tailed probability of the chi‐squared
distribution
CHITEST
Returns the test for independence
CONFIDENCE
Returns the confidence interval for a population mean
CORREL
Returns the correlation coefficient between two data sets
COUNT
Counts how many numbers are in the list of arguments

www.CarltonCollins.com

Page 14

Excel Functions, Macros & Data Commands
237 COUNTA
238 COUNTBLANK
239 COUNTIF
240 COVAR
241 CRITBINOM
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266

DEVSQ
EXPONDIST
FDIST
FINV
FISHER
FISHERINV
FORECAST
FREQUENCY
FTEST
GAMMADIST
GAMMAINV
GAMMALN
GEOMEAN
GROWTH
HARMEAN
HYPGEOMDIST
INTERCEPT
KURT
LARGE
LINEST
LOGEST
LOGINV
LOGNORMDIST
MAX
MAXA

267 MEDIAN
268 MIN
269 MINA
270
271
272
273

MODE
NEGBINOMDIST
NORMDIST
NORMINV

Counts how many values are in the list of arguments
Counts the number of blank cells within a range
Counts the number of nonblank cells within a range that meet the given
criteria
Returns covariance, the average of the products of paired deviations
Returns the smallest value for which the cumulative binomial distribution is
less than or equal to a criterion value
Returns the sum of squares of deviations
Returns the exponential distribution
Returns the F probability distribution
Returns the inverse of the F probability distribution
Returns the Fisher transformation
Returns the inverse of the Fisher transformation
Returns a value along a linear trend
Returns a frequency distribution as a vertical array
Returns the result of an F‐test
Returns the gamma distribution
Returns the inverse of the gamma cumulative distribution
Returns the natural logarithm of the gamma Function, Γ(x)
Returns the geometric mean
Returns values along an exponential trend
Returns the harmonic mean
Returns the hypergeometric distribution
Returns the intercept of the linear regression line
Returns the kurtosis of a data set
Returns the k‐th largest value in a data set
Returns the parameters of a linear trend
Returns the parameters of an exponential trend
Returns the inverse of the lognormal distribution
Returns the cumulative lognormal distribution
Returns the maximum value in a list of arguments
Returns the maximum value in a list of arguments, including numbers, text,
and logical values
Returns the median of the given numbers
Returns the minimum value in a list of arguments
Returns the smallest value in a list of arguments, including numbers, text, and
logical values
Returns the most common value in a data set
Returns the negative binomial distribution
Returns the normal cumulative distribution
Returns the inverse of the normal cumulative distribution

www.CarltonCollins.com

Page 15

Excel Functions, Macros & Data Commands
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

NORMSDIST
NORMSINV
PEARSON
PERCENTILE
PERCENTRANK
PERMUT
POISSON
PROB
QUARTILE
RANK
RSQ
SKEW
SLOPE
SMALL
STANDARDIZE
STDEV
STDEVA

291 STDEVP
292 STDEVPA
293 STEYX
294
295
296
297
298
299
300

TDIST
TINV
TREND
TRIMMEAN
TTEST
VAR
VARA

301 VARP
302 VARPA
303 WEIBULL
304 ZTEST
Text Functions
Function
305 ASC

Returns the standard normal cumulative distribution
Returns the inverse of the standard normal cumulative distribution
Returns the Pearson product moment correlation coefficient
Returns the k‐th percentile of values in a range
Returns the percentage rank of a value in a data set
Returns the number of permutations for a given number of objects
Returns the Poisson distribution
Returns the probability that values in a range are between two limits
Returns the quartile of a data set
Returns the rank of a number in a list of numbers
Returns the square of the Pearson product moment correlation coefficient
Returns the skewness of a distribution
Returns the slope of the linear regression line
Returns the k‐th smallest value in a data set
Returns a normalized value
Estimates standard deviation based on a sample
Estimates standard deviation based on a sample, including numbers, text, and
logical values
Calculates standard deviation based on the entire population
Calculates standard deviation based on the entire population, including
numbers, text, and logical values
Returns the standard error of the predicted y‐value for each x in the
regression
Returns the Student's t‐distribution
Returns the inverse of the Student's t‐distribution
Returns values along a linear trend
Returns the mean of the interior of a data set
Returns the probability associated with a Student's t‐test
Estimates variance based on a sample
Estimates variance based on a sample, including numbers, text, and logical
values
Calculates variance based on the entire population
Calculates variance based on the entire population, including numbers, text,
and logical values
Returns the Weibull distribution
Returns the one‐tailed probability‐value of a z‐test

Description
Changes full‐width (double‐byte) English letters or katakana within a
character string to half‐width (single‐byte) characters

www.CarltonCollins.com

Page 16

Excel Functions, Macros & Data Commands
306
307
308
309
310
311
312
313
314
315

BAHTTEXT
CHAR
CLEAN
CODE
CONCATENATE
DOLLAR
EXACT
FIND, FINDB
FIXED
JIS

316
317
318
319

LEFT, LEFTB
LEN, LENB
LOWER
MID, MIDB

320 PHONETIC
321 PROPER
322 REPLACE,
REPLACEB
323 REPT
324 RIGHT, RIGHTB
325 SEARCH,
SEARCHB
326 SUBSTITUTE
327 T
328 TEXT
329 TRIM
330 UPPER
331 VALUE

Converts a number to text, using the ß (baht) currency format
Returns the character specified by the code number
Removes all nonprintable characters from text
Returns a numeric code for the first character in a text string
Joins several text items into one text item
Converts a number to text, using the $ (dollar) currency format
Checks to see if two text values are identical
Finds one text value within another (case‐sensitive)
Formats a number as text with a fixed number of decimals
Changes half‐width (single‐byte) English letters or katakana within a character
string to full‐width (double‐byte) characters
Returns the leftmost characters from a text value
Returns the number of characters in a text string
Converts text to lowercase
Returns a specific number of characters from a text string starting at the
position you specify
Extracts the phonetic (furigana) characters from a text string
Capitalizes the first letter in each word of a text value
Replaces characters within text
Repeats text a given number of times
Returns the rightmost characters from a text value
Finds one text value within another (not case‐sensitive)
Substitutes new text for old text in a text string
Converts its arguments to text
Formats a number and converts it to text
Removes spaces from text
Converts text to uppercase
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

www.CarltonCollins.com

Page 17

Excel Functions, Macros & Data Commands

Chapter 2

The =IF Function

www.CarltonCollins.com

Page 18

Excel Functions, Macros & Data Commands

=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.

www.CarltonCollins.com

Page 19

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 20

Excel Functions, Macros & Data Commands
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.

www.CarltonCollins.com

Page 21

Excel Functions, Macros & Data Commands
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.)

www.CarltonCollins.com

Page 22

Excel Functions, Macros & Data Commands

Chapter 3

Using Functions To
Crunch & Clean Data

www.CarltonCollins.com

Page 23

Excel Functions, Macros & Data Commands

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.
2.
3.
4.
5.
6.
7.
8.
9.

Import
Text to Columns
Remove Duplicates
Find & Replace
Spell Check
=UPPER
=LOWER
=PROPER
=FIND

www.CarltonCollins.com

10. =SEARCH
11. =LEN
12. =SUBSTITUTE
13. =REPLACE
14. =LEFT
15. =MID
16. =RIGHT
17. =VALUE
18. =CONCATENATE

Page 24

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

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.

www.CarltonCollins.com

Page 26

Excel Functions, Macros & Data Commands

Noteworthy Find and Replace Points:
1.
2.
3.
4.

You can search and replace for an entire worksheet, or the entire workbook.
You can find and replace formats with new formats.
There is a cell chooser option that makes it easier to find and replace formats.
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.

www.CarltonCollins.com

Page 27

Excel Functions, Macros & Data Commands
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.

www.CarltonCollins.com

Page 28

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 29

Excel Functions, Macros & Data Commands

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.
www.CarltonCollins.com

Page 30

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 31

Excel Functions, Macros & Data Commands

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:

www.CarltonCollins.com

Page 32

Excel Functions, Macros & Data Commands

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

a.
b.
c.
d.
e.
f.
g.

Next copy that formula...
Highlight the entire range containing data labels in columns A and B. columns...
Press the F5 key to launch the GoTo dialog box...
Select the Options Box...
Click on the “Blanks” radio button...
Press Enter...
Paste.

www.CarltonCollins.com

Page 33

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 34

Excel Functions, Macros & Data Commands

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.)

www.CarltonCollins.com

Page 35

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 36

Excel Functions, Macros & Data Commands

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

www.CarltonCollins.com

Page 37

Excel Functions, Macros & Data Commands
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"

"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‐"

"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"

www.CarltonCollins.com

Page 38

Excel Functions, Macros & Data Commands
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.

www.CarltonCollins.com

Page 39

Excel Functions, Macros & Data Commands
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
Add‐in Express Ltd.
Wizard
Add‐Ins.com
AddinTools
CDX
Click 2 Convert
DigDB
JKP Application Development
J‐Walk & Associates, Inc.
Office Assistance LLC
PATools
PDF2XL
Spinnaker Software Solutions
Vonnix
WinPure
ListCleaner Pro
Clean and Match 2007

www.CarltonCollins.com

Product
Advanced Find & Replace, Merge Cells
Duplicate Finder
AddinTools Assist
Zip Stream
Converts PDF to Excel formats
Add‐ins for Excel®
Flexfind for Excel
Power Utility Pak Version 7
Similar Data Finder for Excel®
PATools Advanced Find Replace
Converts PDF files to Excel Formats
Spinnaker DB tools for Excel
Excel Power Expander 4.6
ListCleaner Lite

Page 40

Excel Functions, Macros & Data Commands

Chapter 4

Data Commands
The Heart & Soul Of Excel

www.CarltonCollins.com

Page 41

Excel Functions, Macros & Data Commands
The Data Menu ‐ Perhaps the parts of Excel that are of most value to CPAs, but least used
by CPAs are the Data commands found under the Data menu in Excel 2003 and earlier, and
on the data Ribbon in Excel 2007. These commands are shown below, and we will
concentrate the next hour to studying these commands.

Data Sort

‐ The Sort tool does exactly what it implies – it sorts and data. Key sorting
points are as follows:
1. Contiguous Data ‐ The “A to Z” sorting tool can sort large matrix of data
automatically as long as the data is contiguous. In other words, your data should
contain no blank columns, no blank rows, and the columns must all be labeled.
Only then will Excel always correctly select the entire matrix for sorting.
2. A to Z Button ‐ Simply place the cursor in the desired column for sorted, and
press the A to Z or Z to A button as the case may be. Excel will automatically sort
all continuous columns that have headings and all contiguous rows from the top
row under the heading labels down to the last row in the selected column that
contains data. (Note ‐ If you accidently select 2 cells instead of just one, your
results will not be correct.)

www.CarltonCollins.com

Page 42

Excel Functions, Macros & Data Commands
3. Sort by 64 Columns ‐ The “Sort” tool is dramatically enhanced in Excel 2007 as it
now provides the ability to sort by up to 64 columns, instead of just 3 columns.
Presented below is a dialog box which shows this expanded functionality.

4. Sort Left to Right – Excel has always provided the ability to sort left to right. To do
so, select the options box in the Sort Dialog box and click the check box labeled
“Sort left to Right” as shown below.

5. Sort by Color – Excel 2007 now provides the ability to sort by font color or by cell
color, or both. This is handy in many ways. Sometimes CPAs use color to tag or
mark certain cells ‐ and later find it useful to be able to sort by those markings. In
other situations CPAs use conditional formatting to apply color to cells using a
wide variety of rules. Thereafter Excel can sort the data based on the resulting
colors. The sort‐by‐color options are shown below.

www.CarltonCollins.com

Page 43

Excel Functions, Macros & Data Commands

To be accurate, it was possible to sort by color in Excel 2003. To accomplish this
task, you needed to use the =CELL function in order to identify information about
a given cell such as the cell color or font color. Thereafter, the results of that
function could be used to sort rows – which effectively means that you can sort
by color in Excel 2003 – but it takes a bit more effort.
6. Sort By Custom List – Another sorting capability in Excel is the ability to sort by
Custom List. For example, assume a CPA firm has ten partners, and the Managing
partner prefers to be shown at the top of the list, and the remaining Partners
based on seniority. In this case, you could create a Custom List in the excel
Options dialog box listing the partners in the desired order, and then sort future
reports based on that order.
Perhaps a better example use of this feature would be to create a non‐alphabetic
custom list of your chart of accounts, and then sort transactions to produce a
general ledger in chart of account order – even if your preferred chart of accounts
is not alphabetical. the partner seniority does not match the alphabetic names,
nor any

Filtering Data ‐ Using AutoFilter to filter data allows you to view a subset of your data
in a range of cells or table. Once you have filtered the data, you can apply additional filters
to further refine your data view. When you are done, you can clear a filter to once again
redisplay all of the data. To use this tool, start with any list of data and turn on the
AutoFilter tool. Then position your cursor in the column you want to filter and use the drop
down arrows to apply your filters as shown in the screen below.

www.CarltonCollins.com

Page 44

Excel Functions, Macros & Data Commands

Once the filters are applied, you will see a subset your data. For example, the screen
presented below shows filtered data for only Macon and Savannah properties.

As filters are applied, a small funnel appears in the drop down arrow button to indicate that
a filter has been applied. You can apply filters for multiple columns simultaneously.
Key Points Concerning The AutoFilter Command:
1. Contiguous Data – The AutoFilter tools works best when you are working with data
that is contiguous. In other words, your data should contain no blank columns, no
blank rows, and the columns must all be labeled.
www.CarltonCollins.com

Page 45

Excel Functions, Macros & Data Commands

2. Filter by Multiple Columns ‐ You can filter by more than one column.
3. Removing Filters – In Excel 2003 and earlier, a faster way to remove multiple filters is
to turn off filtering and then turn filtering back on. In Excel 2007 you can simple click
the Clear button in the Sort and Filter Group as shown below.

4. Filters are Additive ‐ Each additional filter is based on the current filter and further
reduces the subset of data.
5. Three Types of Filters – You can filter based on list values, by formats, or by criteria.
Each of these filter types is mutually exclusive for each range of cells or column table.
For example, you can filter by cell color or by a list of numbers, but not by both; you
can filter by icon or by a custom filter, but not by both.

6. Filters Enabled ‐ A drop‐down arrow
applied.
7. Filter Applied ‐ A Filter button

www.CarltonCollins.com

means that filtering is enabled but not

means that a filter is applied.

Page 46

Excel Functions, Macros & Data Commands
8. Filter Spanning ‐ The commands under
the All Dates in the Period menu, such
as January or Quarter 2, filter by the
period no matter what the year. This
can be useful, for example, to compare
sales by a period across several years.
9. This Year vs. Year‐to‐Date Filtering ‐
This Year and Year‐to‐Date are
different in the way that future dates
are handled. This Year can return dates
in the future for the current year,
whereas Year to Date only returns
dates up to and including the current
date.
10. Filtering Dates ‐ All date filters are
based on the Gregorian calendar as
decreed by Pope Gregory XIII, after
whom the calendar was named, on 24
February 1582. The Gregorian calendar modifies the Julian calendar's regular four‐
year cycle of leap years as follows: Every year that is exactly divisible by four is a leap
year, except for years that are exactly divisible by 100; the centurial years that are
exactly divisible by 400 are still leap years. For example, the year 1900 is not a leap
year; the year 2000 is a leap year.
11. Filtering By Days of Week ‐ If you want to filter by days of the week, simply format
the cells to show the day of the week.
12. Top & Bottom Filtering ‐ On the Data tab, in
the Sort & Filter group, click Filter. Point to
Number Filters and then select Top 10. To
filter by number, click Items. To filter by
percentage, click Percent. Note ‐ Top and
bottom values are based on the original
range of cells or table column and not the
filtered subset of data.
13. Above & Below Average Filtering ‐ On the
Data tab, in the Sort & Filter group, click
Filter. Point to Filter by Numbers that are
Above/Below Average. Note – These values
are based on the original range of cells or
table column and not the filtered subset of data.
www.CarltonCollins.com

Page 47

Excel Functions, Macros & Data Commands

14. Filtering Out Blanks ‐ To filter for blanks, in the AutoFilter menu at the top of the list
of values, clear (Select All), and then at the bottom of the list of values, select
(Blanks).
15. Filtering By Color ‐ Select Filter by Color, and then depending on the type of format,
select Filter by Cell Color, Filter by Font Color, or Filter by Cell Icon.
16. Filter by Selection ‐ To filter by text, number, or date or time, click Filter by Selected
Cell's Value and then: To filter by cell color, click Filter by Selected Cell's Color. To
filter by font color, click Filter by Selected Cell's Font Color. To filter by icon, click
Filter by Selected Cell's Icon.
17. Refreshing Filters ‐ To reapply a filter after the data changes, click a cell in the range
or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

Data Form – Excel’s 2003 Data Form tool makes Excel look more and behave more like a
database, such as Microsoft Access. (The Form button has not been included on the Office
Fluent user interface Ribbon, but you can still use it in Office Excel 2007 by adding the Form
button to the Quick Access Toolbar.)

A data form provides a convenient means to enter or display one complete row of
information in a range or table without scrolling horizontally. You may find that using a data
form can make data entry easier than moving from column to column when you have more
columns of data than can be viewed on the screen. Use a data form when a simple form of
www.CarltonCollins.com

Page 48

Excel Functions, Macros & Data Commands
text boxes that list the column headings as labels is sufficient and you don't need
sophisticated or custom form features, such as a list box or spin button.
Key Points using data Form:
1. You cannot print a data form.
2. Because a data form is a modal dialog box, you cannot use either the Excel Print
command or Print button until you close the data form.
3. You might consider using the Windows Print Screen key to make an image of the
form, and then paste it into Microsoft Word for printing.

Data Subtotals

– Excel provides an automatic subtotaling which will automatically
calculate and insert subtotals and grand totals in your list or table. Once inserted, Excel
recalculates subtotal and grand total values automatically as you enter and edit the detail
data. The Subtotal command also outlines the list so that you can display and hide the detail
rows for each subtotal. Examples of a the Subtotal dialog box and a resulting subtotaled
table are shown below.

Key points to Consider When Using Subtotaling are as follows:
www.CarltonCollins.com

Page 49

Excel Functions, Macros & Data Commands

1. Contiguous Data – The Subtotal tools works best when you are working with data
that is contiguous. In other words, your data should contain no blank columns, no
blank rows, and the columns must all be labeled.
2. Sort Before Your Subtotal ‐ You must sort the data by the column you wish to
Subtotal by, else you will receive erroneous results.
3. Other Mathematical Applications ‐ The Subtotal tool not only calculates subtotals,
but it can also calculate minimums, maximums, averages, standard deviations, and
other functions.

4. Subtotals in 2007 Tables – Excel 2007 deploys Subtotaling a little differently in that
the Subtotal tool appears at the bottom of each column in each table, as shown in
the screen below.

5. Automatic Outlining ‐ Subtotaling automatically inserts Outlines, which is really
cool. You can then condense and expand the data in total and by subtotal. Some
CPAs also like to copy and paste the condensed subtotal information to another
location but find that this process copies and pastes all of the data. There are two
ways to achieve a clean copy and paste without grabbing all the hidden data as
follows:
www.CarltonCollins.com

Page 50

Excel Functions, Macros & Data Commands
a. CTRL key – Hold the Control Key down while you individually click on each subtotal
row. This will enable you to copy and paste just the subtotal data. This approach can
be problematic because if you mis‐click, you have to start over.
b. Select Visible Cells – A better approach is to use the Select Visible Cells tool. This
tool will select on the data you can see, after which the copy and paste routine will
yield the desired results. This option is better because it is faster and less error
prone.

Data Validation
Data Validation can be used to limit the data that can be entered into a cell. For example,
you might want the user to enter only values between 1% and 99%. You might also use this
tool to enable data input to a drop down list. This has two advantages in that it can be
faster and more accurate. Start with the dialog box below to create your drop down list
functionality.

After making all the necessary selections in the validation list dialog box, your worksheet
will behave as shown below.

www.CarltonCollins.com

Page 51

Excel Functions, Macros & Data Commands

You can also provide messages to define what input you expect for the cell, and instructions
to help users correct any errors. For example, in a marketing workbook, you can set up a cell
to allow only account numbers that are exactly three characters long. When users select the
cell, you can show them a message such as this one:

If users ignore this message and type invalid data in the cell, such as a two‐digit or five‐digit
number, you can show them an actual error message. In a more advanced scenario, you
might use data validation to calculate the maximum allowed value in a cell based on a value
elsewhere in the workbook. In the following example, the user has typed $4,000 in cell E7,
which exceeds the maximum limit specified for commissions and bonuses.

www.CarltonCollins.com

Page 52

Excel Functions, Macros & Data Commands

If the payroll budget were to increase or decrease, the allowed maximum in E7 would
automatically increase or decrease with it.

www.CarltonCollins.com

Page 53

Excel Functions, Macros & Data Commands

PivotTables
The PivotTable report tool provides an interactive way to summarize large amounts of data.
Use should use the PivotTable tools to crunch and analyze numerical data PivotTable
reports are particularly useful in the following situations:
a. Rearranging rows to columns or columns to rows (or "pivoting") to see different
summaries of the source data.
b. Filtering, sorting, grouping, and conditionally formatting your data.
c. Preparing concise, attractive, and annotated online or printed reports
d. Querying large amounts of data.
e. Subtotaling and aggregating numeric data.
f. Summarizing data by categories and subcategories
g. Creating custom calculations and formulas.
h. Expanding and collapsing levels of data.
i. Drilling down to details from the summary data
In essence, PivotTables present multidimensional data views to the user – this process is
often referred to as “modeling”, “data‐cube analysis”, or “OLAP data cubes”. To re‐arrange
the PivotTable data, just drag and drop column and row headings to move data around.
PivotTables are a great data analysis tool for management.
If you have never used a PivotTable before, initially the concept can be difficult to grasp.
The best way to understand a PivotTable is to create a blank Pivot Table and then drag and
drop field names onto that blank table. This way you will see the resulting pivot table
magically appear and it will help you better understand the important relationship between
the pivot pallet and the field name list.
Let’s create a simple PivotTable. Start with an Excel worksheet data that contains several
columns of data – the data must include column and row headings and it helps if the data is
contiguous. Place your cursor anywhere in the data and select PivotTable from the Data
menu in Excel 2003 and click Finish; or from the insert Ribbon in Excel 2007. This process is
shown below: Let’s start with a page of data summarizing the results of tax season as all of
the time sheet entries have been entered onto a single worksheet as shown below.

www.CarltonCollins.com

Page 54

Excel Functions, Macros & Data Commands

Place your cursor anywhere in the data and select PivotTable from the Insert Ribbon as
shown below:

For learning purposes let’s right mouse click on the pivot table and select PivotTable Options,
Display, Classic PivotTable Layout. Your screen will now appear as follows:

www.CarltonCollins.com

Page 55

Excel Functions, Macros & Data Commands

I like for CPAs to learn how to use Pivot Tables in this view because it visually helps them
understand the all important relationship better the blank pivot palette and the PivotTable
field List, both elements of which are shown in the screen above.
To proceed, simply drag and drop field names shown on the right onto the blank Pivot
palette shown on the left. With each drop, your report grows larger. As an alternative you
could use the check boxes next to field names – this functionality is new in Excel 2007. After
added some data to your blank Pivot Palette, your data will look something like this:

www.CarltonCollins.com

Page 56

Excel Functions, Macros & Data Commands
Next format and filter the Pivot Report. Very quickly your report comes together as shown
below. Notice the filter button has been applied and a Pivot table style has also been applied
for appearance.

Double clicking on any number in a pivot report will automatically produce a new worksheet
complete with all supporting detail that comprises the summary number.
There are a multitude of PivotTable options that can be applied to alter the appearance or
behavior of your Pivot table.
Key Points Concerning Pivot Tables are as Follows:
a. You can create as many Pivot Reports as you want from your initial raw data page. Your raw
data remains unchanged as new Pivot tables are created.
b. As your raw data changes, your pivot tables are updated each time you press the refresh
button. Or if you prefer you can set your PivotTables to update themselves at regularly
scheduled intervals – say every ten minutes.
c. A key to understanding PivotTables is understanding the relationship between the Blank
Pivot palette and the PivotTable Field list. As data is selected in the list, it appears on the
Pivot table Report.
d. You can alter the PivotTable simple by dragging and dropping the field names in different
locations on the Pivot palette, or in different locations in the PivotTable Field list Box.
e.

PivotTables can be pivoted.

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.

www.CarltonCollins.com

Page 57

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 58

Excel Functions, Macros & Data Commands

s. Excel also provides a PivotChart function which works similarly to PivotTables. Presented
below is an example PivotChart.

Excel 2003 PivotTables work very similarly as shown below. Excel creates a blank PivotTable,
and the user must drag and drop the various fields from the PivotTable Field List onto the
appropriate column, row, or data section. As you drag and drop these items, the resulting
report is displayed on the fly. Here is the blank Pivot Palette view.

www.CarltonCollins.com

Page 59

Excel Functions, Macros & Data Commands

Now drag and drop field names from the Pivot Table field list onto the Pivot pallet. This
action will automatically create Pivot Table reports – and they will change each time you
drop additional field names, or move field names around. Presented below are but a few
examples of hundreds of possible reports that could be viewed with this data through the
PivotTable format.

This report shown above shows the total resulting sales for each marketing campaign for
each of the 4 months marketing campaigns were conducted.

In this screen we see the same information is shown as a percentage of the total. A few
observations include the fact that overall Radio Spots are the most profitable type of
campaign, but only in April and July. In January and October, local ads and direct mail,
respectively, produce better results. Further, April campaigns had the best response overall.

www.CarltonCollins.com

Page 60

Excel Functions, Macros & Data Commands

Further analysis in the screen above tells us that our results vary widely from one city to the
next. In New York, coupons were least effective, but coupons were most effective in
Columbus. Pivot charts based on PivotTable data can be modified by pivoting and/or
narrowing the data. They can also be published on the Internet (or on an Intranet) as
interactive Web pages. This allows users to “play” with the data. The chart below provides a
visual look at the data shown above.

Filtering Pivot Tables ‐ If you take a close look at your resulting pivot tables, you will notice
that Excel automatically inserts a filter button on each field list as shown by the drop down
arrows in the screen below:

www.CarltonCollins.com

Page 61

Excel Functions, Macros & Data Commands

This drop down filter list makes it easy to refine your report to include just the data you
want.
Drilling Pivot Tables ‐ Another nice feature in pivot tables is that they are automatically
drillable. Simply double click on any number in a pivot report top have Excel automatically
insert a new sheet and produce the detailed report underlying the number you clicked on.
An example of this is shown below:

Pivot Table Options ‐ By right mouse clicking on your pivot table you will reveal several
option settings boxes as shown below. For example, these options boxes control the types of
subtotals produced in your pivot reports. Excel also offers a pivot table options box as well as
a layout wizard that makes producing pivot tables a little easier.

www.CarltonCollins.com

Page 62

Excel Functions, Macros & Data Commands

www.CarltonCollins.com

Page 63

Excel Functions, Macros & Data Commands

Data Table (“What‐if Analysis”)
Data tables are part of a suite of commands that are called what‐if analysis tools. When you
use data tables, you are doing “what‐if analysis”. What‐if analysis is the process of changing
the values in cells to see how those changes will affect the outcome of formulas on the
worksheet. For example, you can use a data table to vary the interest rate and term length
that are used in a loan to determine possible monthly payment amounts.
Three categories of What‐if Analysis Tools ‐ There are three kinds of what‐if analysis tools in
Excel:
1. Data Tables
2. Goal Seek
3. Scenarios
A data table cannot accommodate more than two variables. If you want to analyze more
than two variables, you should instead use scenarios. Although it is limited to only one or
two variables (one for the row input cell and one for the column input cell), a data table can
include as many different variable values as you want. A scenario can have a maximum of 32
different values, but you can create as many scenarios as you want.
Loan Analysis ‐ In this exercise, we start by creating a simple Payment function to calculate
the payment amount of a loan given a 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.

www.CarltonCollins.com

Page 64

Excel Functions, Macros & Data Commands

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.
www.CarltonCollins.com

Page 65

Excel Functions, Macros & Data Commands

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
www.CarltonCollins.com

Page 66

Excel Functions, Macros & Data Commands
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.

www.CarltonCollins.com

Page 67

Excel Functions, Macros & Data Commands
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.
www.CarltonCollins.com

Page 68

Excel Functions, Macros & Data Commands

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:

www.CarltonCollins.com

Page 69

Excel Functions, Macros & Data Commands
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.

www.CarltonCollins.com

Page 70

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 71

Excel Functions, Macros & Data Commands

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.
www.CarltonCollins.com

Page 72

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 73

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 74

Excel Functions, Macros & Data Commands

www.CarltonCollins.com

Page 75

Excel Functions, Macros & Data Commands

Chapter 5

MACROS
Automating Your Key Strokes

www.CarltonCollins.com

Page 76

Excel Functions, Macros & Data Commands

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,

www.CarltonCollins.com

Page 77

Excel Functions, Macros & Data Commands
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

www.CarltonCollins.com

Page 78

Excel Functions, Macros & Data Commands
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
www.CarltonCollins.com

Page 79

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 80

Excel Functions, Macros & Data Commands

Chapter 6

Miscellaneous

www.CarltonCollins.com

Page 81

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 82

Excel Functions, Macros & Data Commands

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.

www.CarltonCollins.com

Page 83

Excel Functions, Macros & Data Commands

Chapter 7

XML Data

www.CarltonCollins.com

Page 84

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.

1. Why use XML in Excel?
a. XML data and schema files
b. Key XML and Excel scenarios
2. 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
3. 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 .

www.CarltonCollins.com

Page 85

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:

1. Extend the functionality of existing Excel templates by mapping XML elements onto existing
2.
3.
4.
5.

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:

1.
2.
3.
4.

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
5. Exporting revised data from mapped cells to an XML data file

www.CarltonCollins.com

Page 86

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
www.CarltonCollins.com

Page 87

Excel Functions, Macros & Data Commands
2. Displays a hierarchical list of XML elements in the currently listed XML map
3. Sets options when working with the XML Source task pane and the XML data, such as how
to preview the data and control headings
4. Opens the XML Maps dialog box, which you can use to add, delete, or rename XML maps
5. 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:

www.CarltonCollins.com

Page 88

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

www.CarltonCollins.com

Page 89

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

www.CarltonCollins.com

Page 90

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
2.
3.
4.
5.
6.

are created when blank cells exist for a required element.
Unicode Transformation Format‐8 (UTF‐8) encoding is used to write the data.
All namespaces are defined in the Root XML element.
Excel overwrites existing namespace prefixes. The default namespace is assigned a prefix of
ns0. Successive namespaces are designated ns1, ns2 to ns where is the
number of namespaces written to the XML file.
Comment nodes are not preserved.
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.

www.CarltonCollins.com

Page 91

Excel Functions, Macros & Data Commands

Chapter 8

Excel Quick Tips

www.CarltonCollins.com

Page 92

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

Right Click Status Bar ‐ (View sums, averages, mins, maxes immediately)
CTRL + Mouse Scroll ‐ (Zoom in & out with your mouse)
Double Click the Format Painter ‐ (Tool sticks until clicked again)
Replace Formatting ‐ (Find and replace one formatting with another)
Click on Edge of Cell ‐ (Navigate in a range of cells)
Turn off Task Pane ‐ (Put an end to TaskPane)
Control Tilde (CTRL + ~) ‐ (View underlying formulas)
Indent Icon ‐ (Indent cells or columns instantly)
ALT + Down Arrow (or Shift‐F10) ‐ (Pick from a drop down list)
F4 ‐ (Repeat the last command such as insert rows or change row height)
Alt + Enter ‐ (Wrap text instantly)
& ‐ (Combine text from multiple cells)
Right Click Tab, Copy, Create Copy ‐ (Insert new sheet with headers, footers, etc)
File, Send To, Mail Recipient ‐ (E‐Mail a worksheet, workbook or chart)
ComboBox from Forms Toolbar ‐ (Insert a combobox)
Double Click Fill Handle ‐ (Copies formula down the relevant range)
=Upper, =Lower, =Proper ‐ (Change text case)
Paste Special, Values ‐ (Convert formulas to numbers)
F4 in Edit Mode ‐ (Toggle Absolute References)
Paste Special, Transpose ‐ (Invert a matrix of numbers)
Ctrl + D ‐ (Copy Data to the down)
Ctrl + R ‐ (Copy data to the right)
Defined Names ‐ (Refer to names rather than cell addresses)
Data, AutoFilter, Advanced, Copy, Unique ‐ (Extract unique values)
Tools, Options, Calculation, Precision as Displayed (Avoid rounding errors)
Right Click Toolbar, Options, Show Full Menus ‐ (Show all menu options)
Tools, View, Zero Values ‐ (Hide zero values)
Tools, AutoCorrect, Smart Tags, None ‐ (Turn off Smart Tags)
Filter Data, Apply Color, Un‐filter Data ‐ (Color filtered results)
PDF2XL ‐ ($95 product converts PDF's to Excel files)
Data Validation ‐ (Insert a pop up comment into a cell)
Ctrl+Shft+End ‐ (Select row to the right)
Ctrl+Shft+Home ‐ (Select row to the left)
F11 ‐ (Produce a quick chart)
Print Area in Name Box ‐ (Quickly identify the print area)
Displaying the Styles Tool ‐ (Toolbar access to styles)
Format, Styles ‐ (Create new styles)
Control Panel, Regional Options ‐ (Control how dates are displayed)
=Substitute ‐ (Remove or replace unwanted characters)
View, Sized with Window ‐ (Resizes chart to fit Window)
Ctrl+Spacebar ‐ (Select a column)

www.CarltonCollins.com

Page 93

Excel Functions, Macros & Data Commands
42
43
44
45
46
47
48
49
50

Shift+Spacebar ‐ (Select a row)
Alt+Tab ‐ (Toggle between applications or Excel workbooks)
Delete Blank Rows and Columns, Ctrl+S ‐ (Reduce relevant area and scroll bar)
Copy Formula, to Blank Cells ‐ (Fill in missing data in a list)
=ISTEXT(A1) ‐ (Use this formula in conditional formatting to format text only)
Replace, Within ‐ (Tip for replacing throughout entire workbook)
=LEN(A1)‐LEN(SUBSTITUTE(A1,A2,"")) ‐ (Count the occurrence of a character)
=MID(A25,FIND("*",SUBSTITUTE(A25,"\","*",LEN(A25)‐
LEN(SUBSTITUTE(A25,"\",""))))+1,LEN(A25)) ‐ (Extract the Filename)
=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

www.CarltonCollins.com

Page 94

Excel Functions, Macros & Data Commands

Chapter 9

What’s New in
Excel 2007?

www.CarltonCollins.com

Page 95

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.

www.CarltonCollins.com

Page 96

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.

www.CarltonCollins.com

Page 97

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

www.CarltonCollins.com

Page 98

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.

www.CarltonCollins.com

Page 99

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

www.CarltonCollins.com

Page 100

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

www.CarltonCollins.com

Page 101

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.

www.CarltonCollins.com

Page 102

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.

www.CarltonCollins.com

Page 103

Excel Functions, Macros & Data Commands

What’s New in
Microsoft Excel 2010

www.CarltonCollins.com

Page 104

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.

www.CarltonCollins.com

Page 105

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.

www.CarltonCollins.com

Page 106

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.

www.CarltonCollins.com

Page 107

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.
2.
3.

2008 and 2009 Chairman of the Southeast Accounting Show ‐ the south's largest CPA event.
Recipient of the 2008 Tom Radcliff Outstanding Discussion Leader Award.
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.

www.CarltonCollins.com

Page 108

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.4
Linearized                      : Yes
XMP Toolkit                     : Adobe XMP Core 4.0-c316 44.253921, Sun Oct 01 2006 17:14:39
Format                          : application/pdf
Creator                         : MLC
Title                           : Microsoft Word - 2010 Excel Functions Macros and Data Commands Manual as of March 2010
Create Date                     : 2010:07:01 11:47:04-04:00
Creator Tool                    : PScript5.dll Version 5.2.2
Modify Date                     : 2010:07:01 11:47:04-04:00
Producer                        : Acrobat Distiller 8.1.0 (Windows)
Document ID                     : uuid:533f12d7-cb7a-4c7c-8686-d7bc5543466b
Instance ID                     : uuid:3429c829-cf76-47fd-b31d-6f22f61181ed
Page Count                      : 108
Author                          : MLC

EXIF Metadata provided by EXIF.tools

2010 Excel Functions Macros And Data Commands Manual As Of March

Navigation menu

Versions of this User Manual:

Views

Navigation