Index
Symbols
% of option, 74
% Running Total In option, 70–71
A
activating
PivotChart wizard, 155
PivotTable wizard, 155
ActiveX controls versus form controls, 237
adding
calculated fields
with formulas, 108–109
manual method, 108
fields to pivot table, 29–32
grand total row, 60
layers to pivot table, 32
macro functionality with VBA, 238–242
add-ins, PowerPivot
combination layouts, 228–229
free cost of, 198–199
installing, 200
limitations of, 200
report formatting, 228–231
slicers, 211–212
usage guidelines, 212–213
alternatives to pivot charts, 138–142
analyzing
data sources, 25
multiple consolidation ranges
Column field, 160
Pages field, 161
Row field, 160
Value field, 160
analyzing activity by market, case study, 38–39
applying
Autofilter, 322–323
conditional formatting, 147
data visualizations with VBA, 309–310
multiple number formats, 326–328
numeric format, 48–49
table styles, 47
table styles with VBA, 307–308
themes, 62
areas section
drop-down arrows, 88–89
Report Filter area, 101–105
asymmetric reporting, 228
Autofilter, applying, 322–323
automatically refreshing pivot tables, 310–312
AutoShow method (VBA), 290–292
Autosort, 93
Average function, 64
B
benefits of PowerPivot, 195–198
blank cells
filling in, 25
filling in data area with VBA, 261
incorrect count calculations, troubleshooting, 63–64
replacing with zeros, 49–50
browsers, viewing pivot tables with, 181–183
building
pivot tables with external data sources, 161–168
Microsoft Access data, 162–165
SQL Server data, 165–168
PowerPivot reports, 201–212
calculated columns, adding with DAX, 208–209
copying and pasting data, 205–206
defining relationships, 207–208
linking data, 206
text file, importing, 201–204
buttons, pivot field buttons, 132
C
calculated columns, DAX, 214–218
calculated fields, 107–108
adding, manual method, 108
creating, 109–112
creating with VBA, 284
forecasts, summarizing, 114–118
inserting, 109–110
rules for, 124–125
calculated items, 107–108
creating, 118–122
generating with VBA, 285–286
rules for, 125
solve order, changing, 127
calculating percent of parent items, 71
Calculation options (VBA), 288–289
calculations
Calculation options (VBA), 288–289
constants, 123
deleting, 126
editing, 126
order of operator precedence, 122–123
Show Values As options, 67–68
specifying with VBA, 280–282
case studies
analyzing activity by market, 38–39
converting pivot tables to values, 55–56
forecasts, summarizing, 114–118
GETPIVOTDATA function, 75–78
invoice frequency and revenue distribution report, creating, 136–140
order lead time reports, creating, 85
pivot tables, synchronizing with combo box, 242–246
Top 10 reports, creating, 100–101
changing
field names, 50–51
themes, 62
charts
pivot charts, 129–130
alternatives to using, 142–147
creating, 130–133
slicers, 133
standard charts, creating from pivot tables, 141–142
Clear command, 43
collapsing fields, 52–53
Collie, Rob, 336
column area, 15–16
Column field of multiple consolidation ranges, analyzing, 160
Column Labels drop zone, 30
columns, deleting empty columns, 24
combination layouts (PowerPivot), 228–229
combo boxes, pivot table synchronization, case study, 242–246
commands, Clear, 43
Compact layout, 52–53
comparing ActiveX controls and form controls, 237
compatibility
between Excel 2007 and Excel 2010, 170
slicers, 170–171
Excel 2010 Compatibility mode, 173
with legacy Excel versions, 18–19
parent item calculations, 171–173
VBA, Excel 2010 and older versions, 254
Comptability Checker, 18–19
conceptual filters, 298–300
conditional formatting, 145–151
preprogrammed scenarios, 146–147
connecting
to OLAP cube, 186–188
slicers to pivot tables, 36–37
constants, 123
controlling
grand totals, 57–58
subtotals, 57–58
totals with VBA, 262–263
converting
pivot tables to cube formulas, 193–195
pivot tables to hard data, 313–314
pivot tables to values, 55–56
with VBA, 263–266
Count Distinct formula, generating with VBA, 282–283
Count function, 64
Count Nums function, 64
Create PivotTable dialog box, 27–28
creating
calculated fields, 109–112
calculated items, 118–122
offline cubes (OLAP), 190–193
pivot charts, 130–133
report filters in VBA, 294–297
Top 10 reports, 100–101
cubes (OLAP)
connecting to, 186–188
functions, 193–195
offline, creating, 190–193
structure of, 188–189
custom lists, sorting, 95–96
customizing styles, 60–61
D
data area
blank cells, filling with VBA, 261
fields, adding with VBA, 258
Data Field Settings dialog box, 51
data fields
handling multiple with VBA, 279–280
data sets
exploding
to different tabs, 330–331
to different workbooks, 331–333
transposing, 323–327
data sources
analyzing, 25
external, building pivot tables with, 161–168
OLAP, limitations of, 190
summarizing with multiple consolidation ranges, 154–159
data visualizations, applying with VBA, 309–310
databases, OLAP, 183–186
cube, connecting to, 186–188
cube, structure of, 188–189
named sets, filtering with, 304–306
offline cubes, creating, 190–193
pivot table restrictions, 190
date fields, grouping, 80–83
DAX
calculated columns, calculations, 214–218
measures, creating, 218–225
Time Intelligence functions, 225–228
default style, selecting, 61
deferring layout updates, 42–43
deleting calculations, 126
Design tab (Layout Group), 52
dialog boxes
Data Field Settings, 51
Insert Calculated Field, 111
Label Filter dialog box, 98
Move Chart, 131
Move PivotTable, 43–44
New Formatting Rule, 148–150
Record Macro, 234
Solve Order, 127
Difference From option, 72
dimensions (OLAP), 188
disabling GetPivotData, 336–341
disparate data sources, summarizing with multiple consolidation ranges, 154–159
displaying revenue by category with VBA, 266–278
distributing pictures of pivot charts, 142
docking PivotTable Field List, 86
documenting formulas, 127–128
drag-and-drop functionality, activating, 33
drop zones, 29–30
rearranging, 32–34
drop-down arrows (areas section), 88–89
E
editing calculations, 126
effective tabular design, 24–26
empty cells, filling, 314–316
empty columns, deleting, 24
enabling drag-and-drop functionality, 33
END WITH statement (VBA), 253
Excel 2007
slicers, compatibility with Office 2010, 170–171
VBA, new methods introduced with, 254–255
Excel 2010 Compatibility mode, 173
Excel Services
goals of, 179
limitations of, 180
preparing spreadsheets for, 177
rendering spreadsheets, requirements, 176
spreadsheets, publishing, 177–179
executive overviews, generating with VBA, 290–292
exploding data sets
to different tabs, 330–331
to different workbooks, 331–333
external data sources, building pivot tables with, 161–168
Microsoft Access data, 162–165
SQL Server data, 165–168
F
Field List drop-down, sorting with, 92–93
field names, changing, 50–51
fields
adding to data area with VBA, 258
adding to pivot table, 29–32
adding to Report Filter area, 102
calculated fields, 107–108
adding, 107–108
creating, 111–114
collapsing, 52–53
data fields, handling multiple with VBA, 279–280
date fields, grouping, 80–83
grouping by week, 82–83
rank number, adding to pivot tables, 316–318
text fields, grouping, 86–87
ungrouping, 85
Fields drop-down (PivotTable Field List), 89–90
filling in blank cells, 25, 314–316
filtering
with conceptual filters, 298–300
in Label areas, 97–100
with named sets (VBA), 304–306
PivotFields with VBA, 298
with Report Filter area, 101–105
with search filter (VBA), 301–302
filters
slicers, 35–37
Top 10, case study, 100–101
flattened pivot tables, 228
forecasts, summarizing, 114–118
form controls, creating macro user interface, 236–238
formatting
conditional formatting, 145–151
preprogrammed scenarios, 146–147
multiple number formats, applying, 326–328
numeric format, applying, 48–49
pivot charts, restrictions, 135–136
pivot tables with VBA, 258–260
preparing data for pivot table reports, 24
formulas
calculated fields, adding, 108–109
cube formulas, converting pivot tables to, 193–195
documenting, 127–128
free cost of PowerPivot add-in, reasons for, 198–199
frequency distributions, generating, 328–330
functions
cube functions, 193–195
DAX, 214–218
Time Intelligence functions, 225–228
G
Gainer, Dave, 336
generating
calculated items with VBA, 285–286
Count Distinct formula with VBA, 282–283
executive overviews with VBA, 290–292
frequency distributions, 328–330
GetPivotData function
case study, 75–78
disabling, 336–341
shell reports, populating, 347–351
goals of Excel Services, 179
grand totals, controlling, 57–58
grouping
date fields, 80–83
years, including, 81–82
fields by week, 82–83
numeric fields, 85
text fields, 86–87
groups, calculating with VBA, 286–287
H
handling multiple data fields with VBA, 279–280
hard-coded values
converting pivot tables to, 313–314
creating from pivot tables, 141–142
hierarchies (OLAP), 189
I
incorrect count calculations, troubleshooting, 64
Index option, 74–75
Insert Calculated Field dialog box, 119–120
inserting calculated fields, 109–110
installing PowerPivot, 200
invoice frequency and revenue distribution reports, creating, 136–140
J—K—L
Label areas, filtering, 97–100
Label Filter dialog box, 98
layers, adding to pivot table, 32
Layout Group, 52
layouts
Compact layout, 52–53
modifying with VBA, 308–309
Outline Form layout, 53–54
Tabular layout, 54–55
updates, deferring, 42–43
legacy Excel versions
compatibility issues, 18–19
parent item calculations, compatibility issues, 171–173
PivotTable toolbar, finding commands, 356–361
VBA, compatibility with Excel 2010, 254
levels (OLAP), 189
limitations
of Excel Services, 180
of OLAP pivot tables, 190
of pivot charts, 135–136
of pivot table calculations, 122–125
of pivot table reports, 17
of PowerPivot, 200
linked pivot table cells as source data, 142–144
linking pivot tables to pivot cache, 41
locating commands on legacy PivotTable toolbar, 356–361
M
macros, 231
functionality, adding with VBA, 238–242
recording, 234–236
security issues, 235–236
user interface, creating with form controls, 236–238
manipulating pivot reports with VBA, 261–262
manual sorting, 93–95
Max function, 64
measures (OLAP), 183
creating with DAX, 218–225
members (OLAP), 189
memory
limitations of pivot table reports, 17
pivot cache, 16–17
methods (VBA)
AutoShow, 290–292
in Excel 2007, 254–255
ShowDetail, 292–294
Microsoft Access data, building pivot tables with, 162–165
Min function, 64
modifying layout with VBA, 308–309
Move Chart dialog box, 131
moving pivot tables, 43–44
multiple consolidation ranges
Column field, analyzing, 160
disparate data sources, summarizing, 154–159
Pages field, analyzing, 161
Row field, analyzing, 160
Value field, analyzing, 160
multiple data fields, handling with VBA, 279–280
multiple number formats, applying, 326–328
multiple subtotals, adding, 66
N
named sets
accessing for asymmetric reporting, 228
OLAP pivot tables, filtering with, 304–306
New Formatting Rule dialog box, 148–150
numeric fields, grouping, 85
numeric format, applying, 48–49
O
object variables (VBA), 252–253
Office Web Apps (Excel 2003), saving pivot tables to the web, 173–174
offline cubes (OLAP), creating, 190–193
OLAP (Online Analytical Processing), 183–186
cubes
connecting to, 186–188
functions, 193–195
structure of, 188–189
named sets, filtering with, 304–306
pivot tables, limitation of, 190
operators, order of precedence, 126–128
order lead time reports creation, case study, 84
order of operator precedence, 122–123
Outline Form layout, 53–54
P
Pages field of multiple consolidation ranges, analyzing, 161
parent item calculations in legacy Excel versions, 171–173
percent of parent items, calculating, 74
pictures of pivot charts, distributing, 142
pivot cache, 16–17
sharing, 40–41
updating, 39–40
pivot charts, 127–128
alternatives to using, 140–144
creating, 130–133
rules, 133–136
slicers, 133
pivot field buttons, 132
pivot reports, manipulating with VBA, 261–262
pivot tables
building with VBA, 256–260
converting to values with VBA, 263–266
flattened, 228
formatting with VBA, 258–260
publishing, 175–176
PivotChart wizard, activating, 155
PivotFields, filtering in VBA, 298
PivotTable Field List
docking/undocking, 86
Fields drop-down, 89–90
rearranging, 87–88
PivotTable toolbar (legacy Excel versions), finding commands, 356–361
PivotTable wizard, activating, 155
placement of pivot charts, restrictions, 135–136
populating shell reports with GetPivotData, 347–351
PowerPivot
benefits of, 195–198
combination layouts, 228–229
external data sources, SQL Server data, 231
installing, 200
limitations of, 200
refreshing, 231
report formatting, 228–231
reports, building, 201–212
Server version, 199–200
slicers, 211–212
usage guidelines, 212–213
preparing
data for pivot table reports
empty columns, deleting, 24
formatting, 24
tabular layouts, 22
spreadsheets for Excel Services, 177
preprogrammed scenarios for conditional formatting, 144–145
Product function, 64
properties (VBA) in Excel 2007, 255
publishing
pivot tables, 175–176
spreadsheets to Excel Services, 177–179
Q—R
rank number field, adding to pivot tables, 316–318
ranking options, 72–74
rearranging pivot tables, 32–34
rearranging PivotTable Field List, 91
Record Macro dialog box, 234
recording macros, 234–236
redefining pivot tables, 161
reducing size of reports, 318–320
referencing totals, 124
refreshing
data, 39–40
multiple pivot tables in a workbook, 312
pivot tables, 310–312
PowerPivot, 231
RELATED() function, basing column calculations on another table, 216–218
relocating pivot tables, 43–44
removing subtotals, 65–66
rendering spreadsheets with Excel Services, requirements, 176
replacing blank cells with zeros, 49–50
Report Filter area, 16
filtering with, 101–105
slicers, 104–105
Report Filter drop zone, 30
report filters
creating, 34
VBA, creating, 294–297
reports
PowerPivot, building, 201–212
calculated columns, adding with DAX, 208–209
copying and pasting data, 205–206
defining relationships, 207–208
linking data, 206
text file, importing, 201–204
reducing size of, 318–320
requirements for rendering spreadsheets with Excel Services, 176
restrictions
of Excel Services, 180
of OLAP pivot tables, 190
of pivot charts, 135–136
of pivot table calculations, 122–125
revenue, displaying by category with VBA, 266–278
row area, 15
Row field of multiple consolidation ranges, analyzing, 160
Row Labels drop zone, 30
rules for pivot charts, 133–136
Running Total In option, 70
S
Save As HTML option, saving pivot tables to the web, 174–176
saving pivot tables to the web
Office Web Apps (Excel 2003), 173–174
Save As HTML option, 174–176
search filter (VBA), 301–302
security, macros, 235–236
selecting
default styles, 64
items from Report Filters, 102–104
setting up
custom lists, 95–96
trusted locations, 235–236
sharing
pivot cache, 40–41
pivot tables with other Office versions, 168–170, 173
shell reports
building, 345–346
populating with GetPivotData, 347–351
Show Values As options, 67–68
% of,
Difference From, 72
Index, 74–75
Rank, 72–74
Running Total In, 70
ShowDetail method (VBA), 292–294
side effects of pivot cache sharing, 41
SkyDrive, viewing pivot tables with, 181–183
in Excel 2007, 170–171
generating with VBA, 302–304
in pivot charts, 133
in PowerPivot, 211–212
Solve Order dialog box, 127
solve order of calculated items, changing, 127
sorting
with Autosort, 93
custom lists, 95–96
with Field List drop-down, 92–93
manual sorting, 93–95
with sort icons, 91–92
in unique order, 312–313
source data, linked pivot table cells as, 142–144
specifying
calculation with VBA, 280–282
spreadsheets
preparing for Excel Services, 177
publishing to Excel Services, 177–179
SQL Server data
building pivot tables with, 165–168
for PowerPivot, 231
standard charts, creating from pivot tables, 141–142
StdDev function, 64
StdDevP function, 64
structure of OLAP cubes, 188–189
styles
customizing, 60–61
default, selecting, 61
Styles gallery, 58–60
subtotals
adding multiple, 66
controlling, 57–58
removing, 65–66
Sum function, 64
summarizing
disparate data sources with multiple consolidation ranges, 154–159
forecasts, 114–118
summary calculations, changing, 63–64
suppressing subtotals, 65–66
synchronizing pivot tables with one combo box, case study, 242–246
T
table styles, applying, 47, 307–308
tabular layouts, 54–55
effective design, 24–26
preparing data for pivot table reports, 22
text fields, grouping, 86–87
themes, applying, 62
Time Intelligence functions (DAX), 225–228
Top 10 reports, creating, 100–101
totals
controlling with VBA, 262–263
referencing, 124
transposing data sets, 323–327
troubleshooting incorrect count calculations, 63–64
trusted locations, setting up, 235–236
turning off GetPivotData, 336–341
U
unavailable features in legacy pivot tables, 172
undocking PivotTable Field List, 86
ungrouping fields, 85
unique order sorts, performing, 312–313
updating pivot cache, 39–40
user interface for macros, creating with form controls, 236–238
V
Value field of multiple consolidation ranges, analyzing, 160
values, converting pivot tables to with VBA, 263–266
values area, 14–15
field names, changing, 50–51
Values drop zone, 30
Var function, 64
VarP function, 64
VBA (Visual Basic for Applications)
AutoShow method, 290–292
calculated fields, generating, 284
calculated items, generating, 285–286
Calculation options, 288–289
calculations, specifying, 280–282
code writing techniques, END WITH statement, 253
conceptual filters, 298–300
Count Distinct formula, generating, 282–283
data area, filling blank cells, 261
data visualizations, applying, 309–310
fields, adding to data area, 258
groups, calculating, 286–287
layout, modifying, 308–309
legacy Excel versions, compatibility with Excel 2010, 254
macros, adding functionality, 238–242
methods introduced in Excel 2007, 254–255
multiple data fields, handling, 279–280
pivot reports, manipulating, 261–262
pivot tables
building, 256–260
formatting, 258–260
PivotFields, filtering, 298
report filters, creating, 294–297
revenue, displaying by category, 266–278
search filter, 301–302
ShowDetail method, 292–294
slicers, generating, 302–304
table styles, applying, 307–308
totals, controlling, 262–263
writing code, techniques
large data ranges, handling, 251–252
object variables, 252–253
versions of Office, sharing pivot tables between, 168–170, 173
viewing pivot tables with web browser, 181–183
W
web, saving pivot tables to with Office Web Apps (Excel 2003), 173–174
web browsers, viewing pivot tables with, 181–183
week, grouping fields by, 82–83
when to use Pivot Table, 13–14
Windows Live SkyDrive, viewing pivot tables with, 181–183
workbooks, refreshing multiple pivot tables, 312
writing VBA code, techniques
END WITH statement, 253
large data ranges, handling, 251–252
object variables, 252–253
X—Y—Z
years, including in grouped date fields, 81–82
zeros, replacing blank cells with, 49–50