Symbols
% Difference From option (Value Field Settings dialog box, Show Values As tab), 68-69
% Of option (Value Field Settings dialog box, Show Values As tab), 66
% Of Parent Column option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Row option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Total option (Value Field Settings dialog box, Show Values As tab), 69
% Of Total option (Value Field Settings dialog box, Show Values As tab), 66
3D Map, 289
aerial photography maps, 295
animating data over time, 297-298
annotation (text boxes), 294, 299
bubble charts, 294
combining data sets, 297
custom maps, 290
custom shapes, 290
data cards (tooltips), 296
filters, 296
heat maps, 294
hiding
legends, 296
panes, 296
labeling
data points, 294
maps, 295
latitude, 290
legends
hiding, 296
resizing, 296
longitude, 290
pie charts, 294
preparing data for, 289
region maps, 294
relationships (table), 290
resizing legends, 296
store maps, using instead of globes (case study), 299-302
table relationships, 290
timelines, 298
vantage point, moving, 293
video, creating, 299
zooming in/out of maps, 293
32-bit Excel
Data Model and, 287
Power Pivot and, 287
Access (MS), creating pivot tables, 181-184
ActiveX controls versus form controls, 307
adding
calculated columns in Power Pivot grid, 281-282
calculations to OLAP pivot tables, 250
columns
calculated columns in Power Pivot grid, 281-282
to slicers, 100
fields
model fields to pivot tables, 383
numeric fields to Values area, 384
rank number fields to pivot tables, 396-398
functionality to recorded macros, 308-309
layers to reports, 25
linked tables to Data Model, 279
model fields to pivot tables, 383
numeric fields to Values area, 384
rank number fields to pivot tables, 396-398
scrollbars to spreadsheets, 309-312
tables to Data Model, 180, 279, 381
add-ins (Power Query), 188
Advanced Query Editor (Power Query), 195
aerial photography maps in 3D Map, 295
Allington, Matt, 287
analyzing OLAP pivot table data (what-if analysis), 258-259
annotation (text boxes) in 3D Map, 294, 299
applied steps (Power Query), 194-195, 276
Areas section drop-downs (Field list), 78-79
arrow keys in formula creation, 286
artificial intelligence. See Insights feature
AutoComplete tool (VBA), 326
auto date-grouping, 75
AutoFiltering pivot tables, 401-404
AutoFit, column widths, 59
AutoGroup, 330
date groups, 344
determining AutoGroup criteria, 111
using, 112
year-over-year reports, creating, 113-115
AutoShow (VBA), executive overviews of pivot tables, 365-367
AutoSort, pivot table sort order, 347
backward compatibility
Timeline slicer, 35
basic pivot table creation, 19
adding
fields to reports, 22
layers to reports, 25
choosing report location, 20
classic pivot table layout, 26-27
data selection, 20
fields
adding to reports, 24
dragging/dropping in reports, 25-26
filters, creating, 27
deferring layout updates, 39-40
starting over, 40
adding fields to tables, 24
dragging/dropping fields in tables, 25-26
Recommended PivotTables, 28-30
slicers
connecting to multiple pivot tables, 32-33
big data files, processing with Power Query, 273-275
blank cells
deleting from pivot tables, 346-347
filling
in data area, 336
in row area, 336
in data sources, 14
in numeric columns, 24
blank rows (layouts), 50, 56-57
bubble charts in 3D Map, 294
calculated columns, adding in Power Pivot grid, 281-282
calculated fields, 286
calculated data fields, 358-359
DAX measures, using instead of calculated fields (case study), 141-145
defined, 121
inserting into pivot tables, 124-125
manually adding to data sources, 122-123
summarizing next year’s forecast (case study), 129-133
calculated items, 286
changing solve order of, 147
defined, 121
calculated measures (OLAP), 250-253
calculated members (OLAP), 250, 254-257
calculations, 355, 384. See also measures
adding to OLAP pivot tables, 250
calculated fields, 286
calculated data fields, 358-359
creating with formulas outside a pivot table, 123, 124
defined, 121
inserting into pivot tables, 124-125
manually adding to data sources, 122-123
summarizing next year’s forecast (case study), 129-133
using DAX measures instead of calculated fields (case study), 141-145
changing solve order of, 147
defined, 121
calculation functions, 356-358
cell references and, 139
constants and, 139
DAX measures, 139
defined, 121
using DAX measures instead of calculated fields (case study), 141-145
documenting, 148
grand total references, 139
multiple data fields, troubleshooting issues with, 355-356
named ranges and, 139
OLAP pivot tables, managing calculations, 257-258
operator precedence, 138
Show Values As tab (Value Field Settings dialog box), performing calculations with, 362-363
subtotal references, 139
totals, referencing, 139
VBA calculation options per Excel version, 364
worksheet functions and, 139
case studies
calculated fields, using DAX measures instead of, 141-145
converting live pivot tables to static values, 54-55
data sources, cleaning up, 15-19
grouping text fields for redistricting, 106-108
interactive reports showing revenue by product and time period (case study), creating, 157-161
life after pivot tables, xxvii-xxviii, xxix
life before pivot tables, xxiv-xxvi
market activity analysis, 35-36
store maps, using instead of globes, 299-302
summarizing next year’s forecast, 129-133
transposing data sets with Power Query, 202-204
categorizing columns with Power Pivot, 287
cells
blank cells
deleting from pivot tables, 346-347
filling in data area, 336
filling in row area, 336
in data sources, 14
in numeric columns, 24
calculations and cell references, 139
deleting cells, preventing errors, 336-337
empty cells, filling in row fields, 393-396
extracting from pivot tables based on pivot caches, 232
inserting cells, preventing errors, 336-337
changing
default pivot table behaviors for future pivot tables, 392
field names, 49
pivot table names, 33
previewing changes with Live Preview feature, 60
solve order of calculated items, 147
charts
bubble charts in 3D Map, 294
column charts in 3D Map, 292
column charts in 3D Map, 296
cross-filtering charts with Power BI Desktop, 218
pie charts in 3D Map, 294
pivot charts
defined, 149
formatting limitations, 156
interactive reports showing revenue by product and time period (case study), 157-161
pivot field buttons, 152-153, 160
pivot tables and, 154-156, 162
x-axis, 155
y-axis, 155
check boxes, filtering with, 88-89
classic pivot table layout, 26-27
cleaning up data sources (case study), 15-19
clearing pivot table layouts, 40
Code window (Visual Basic Editor), 326
coding (object-oriented)
End+Down arrow versus End+Up arrow, 328
shortening code, 329
With and End With, 329
Collie, Rob, 143, 287, 425, 432
columns
% Of Parent Column option (Value Field Settings dialog box, Show Values As tab), 69
adding
columns to slicers, 100
columns with Power Query, 275-276
multiple subtotals to one field, 72
calculated columns, adding in Power Pivot grid, 281-282
categorizing columns with Power Pivot, 287
column charts in 3D Map, 292, 296
Column From Examples feature (Power Query), 275
empty columns in data sources, 14
filtering fields, 88
groups, repeating as columns, 13
headings, 11
hiding columns in Power Pivot, 287
labels
filtering with Values column information, 91-92
splitting between two rows, 12
multiple subtotals, adding to one field, 72
numeric columns, blank cells in, 24
percentage change column for year-over-year reports, 417-419
Power Pivot
calculated columns, adding in Power Pivot grid, 281-282
categorizing columns with Power Pivot, 287
hiding columns in Power Pivot, 287
renaming columns with Power Pivot, 287
sorting one column by another with Power Pivot, 282
Power Query
removing columns with Power Query Editor, 191
removing columns with Power Query Editor, 191
renaming columns with Power Pivot, 287
slicers, adding columns to, 100
sorting one column by another with Power Pivot, 282
splitting columns with Power Query, 204-206
subtotals (multiple), adding to one field, 72
Values columns, filtering Label columns, 91-92
widths
AutoFit, 59
vertically arranged pivot tables, 59
Comments tool (VBA), 326
Compact layout, 51-53, 58, 75-76
comparing
line-to-line comparisons, Value field calculations, 66
tables with pivot tables, 400-401
compatibility
slicers, 7
timelines, 7
Timeline slicer, 35
conceptual filters in VBA, 374-377
conditional formatting of pivot tables
example of conditional formatting, 165-167
preprogrammed scenarios, 167-168
constants and calculations, 139
converting
live pivot tables to static values (case study), 54-55
pivot tables
copying pivot tables to workbooks, 349-350
Count of Revenue bug, 62
Create PivotTable dialog box, basic pivot table creation
choosing report location, 20
data selection, 20
creating pivot tables
basic pivot table creation, 19
adding fields to reports, 22-24
adding fields to tables, 21-24
adding layers to reports, 25
choosing report location, 20
classic pivot table layout, 26-27
creating filters, 27
data selection, 20
dragging/dropping fields in reports and tables, 25-26
laying out reports, 22-24, 39-40
Recommended PivotTables, 28-30
from Data Model, 283
Pivot Power Premium add-in, 45
adding fields to data areas, 332-333
filling blank cells in data or row area, 336
preventing errors from inserting/deleting cells, 336-337
cube formulas
CUBEMEMBER function, 234
CUBERANKEDMEMBER function, 236
GETPIVOTDATA function versus, 232
pivot tables, converting to cube formulas, 232-238
SORTBY function, 238
CUBEMEMBER function, 234
CUBERANKEDMEMBER function, 236
cubes (OLAP)
dimensions of, 243
levels of, 243
PivotTable Fields list, 244
custom lists, sorting, 84-86, 286
custom maps (3D Map), 290
custom shapes (3D Map), 290
customers, sorting into high-to-low sequences based on revenue, 79-82
customizing
conditional formatting rules, 168-172
pivot tables, 44
changing field names, 49
converting live pivot tables to static values (case study), 54-55
Count of Revenue bug, 62
default pivot tables, 44
formatting cells in Office 365, 72
gridlines, 45
subtotals, 50
number format, thousands separators, 46-47
Pivot Power Premium add-in, 45
previewing changes with Live Preview feature, 60
replacing blank cells with zeros, 47-48
settings, xxiv
Value field calculations, 63-70
Value field calculations, 63-65
% Difference From option (Value Field Settings dialog box, Show Values As tab), 68-69
% Of option (Value Field Settings dialog box, Show Values As tab), 66
% Of Parent Column option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Row option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Total option (Value Field Settings dialog box, Show Values As tab), 69
% Of Total option (Value Field Settings dialog box, Show Values As tab), 66
Index option (Value Field Settings dialog box, Show Values As tab), 69-70
line-to-line comparisons, 66
ranking options, 67
running total calculations, 67-68
visualizations with Power BI Desktop, 221-222
Dalgleish, Debra, 45
data areas
blank cells, filling in data areas, 336
data cards (tooltips), 3D Map, 296
data items, sorting in a unique sort order, 389-390
Data Model, 330
32-bit Excel and, 287
adding
linked tables to Data Model, 279
model fields to pivot tables, 383
numeric fields to Values area, 384
tables to Data Model, 279, 381
cube formulas, converting pivot tables to, 232-238
DAX measures and median calculations, 269-271
Filtered Items in Totals, 269
loading to Data Model instead of Excel grid, 277-278
median calculations with DAX measures, 269-271
pivot caches, 383
pivot tables
adding model fields to, 383
adding numeric fields to Values area, 384
converting to cube formulas, 232-238
Power Query data, outputting data to Data Model, 193
primary keys, 177
relationships
activating, 180
creating between tables, 382
deactivating, 180
deleting, 180
editing, 180
managing, 179
tables
adding linked tables to Data Model, 279
adding to Data Model, 180, 381
creating relationships, 382
defining relationships between tables, 279-280
text, reporting in Values area, 272
Values area, reporting text in, 272
VLOOKUP functions, replacing with Data Model, 261-266
data ranges
automatically expanding data ranges, creating, 399
data sets
combining in 3D Map, 297
exploding
to different tabs in pivot tables, 410-411
to different workbooks, 415-417
transposing with Power Query (case study), 202-204
data sources
blank cells in, 14
calculated fields, manually adding to data sources, 122-123
cleaning up (case study), 15-19
column headings, 11
disparate data sources
defined, 175
ETL processes, 188
external data, creating pivot tables, 181-187
external data, defined, 175
multiple ranges, defined, 175
empty columns in, 14
empty rows in, 14
gaps in, 14
good design techniques, 14
refreshing
changes made to existing data sources, 36
expanded data source ranges, 37
databases
OLAP databases, 239-240, 250-251
transactional databases, 239
date fields, grouping
including years when grouping by months, 110
date groups, xxiv
AutoGroup feature, 344
DAX formulas, 285
DAX functions, xxiv
DAX measures, 139
defined, 121
using DAX measures instead of calculated fields (case study), 141-145
Debugging tool (VBA), 326
default pivot tables
behaviors, changing for future pivot tables, 392
customizing, 44
deferring pivot table layout updates, 39-40
Delete key versus spaces, 24
deleting
cells, preventing errors, 336-337
Data Model relationships, 180
pivot tables (underlying), pivot charts and, 162
source data worksheets, 398
dimensions of OLAP cubes, 243
disabling macros, 324
disparate data sources
defined, 175
ETL processes, 188
external data
creating pivot tables, 181-187
defined, 175
multiple ranges, defined, 175
distributions (frequency), creating with pivot tables, 409-410
docking/undocking Fields list, 77
document themes, modifying styles with, 61-62
documenting
calculations, 148
formulas, 148
dragging/dropping fields in reports, 25-26
drill-down methods, 286
editing
Data Model relationships, 180
empty cells filling in row fields, 393
Go To Special function (Excel), 395-396
Repeat All Item Labels feature, 394-395
empty columns in data sources, 14
empty rows in data sources, 14
End+Down arrow versus End+Up arrow, coding, 328
End With and With, coding, 329
erasing pivot table layouts, 40
Escobar, Miguel, 287
ETL (Extraction, Transformation, Loading) processes, 188
“evil GetPivotData problem”, 426-428
Excel
32-bit Excel
Data Model and, 287
Power Pivot and, 287
Go To Special function, filling empty cells in row fields, 395-396
pivot tables, backward compatibility, 7, 8
versions of, 330
Excel 2019
recommended pivot tables, xxvii
system requirements, xxxii
Excel 2019 In Depth, 306
Excel grid, loading to Data Model instead, 277-278
Excel Help tool (VBA), 326
exploding data sets
external data
defined, 175
pivot tables, creating, 181
from Access (MS) data, 181-184
extracting cells from pivot tables based on pivot caches, 232
extracts, creating, 3
February 29 1900 and Power Pivot, 286-287
Field list, Areas section drop-downs, 78-79
fields
calculated fields, 286, 358-359
defined, 121
inserting into pivot tables, 124-125
manually adding to data sources, 122-123
summarizing next year’s forecast (case study), 129-133
using DAX measures instead of calculated fields (case study), 141-145
changing names, 49
collapsing/expanding in Compact layouts, 51
column fields
adding multiple subtotals to one field, 72
filtering, 88
data areas, adding fields to, 332-333
date fields
including years when grouping by months, 110
dragging/dropping in reports, 25-26
expanding/collapsing in Compact layouts, 51
filter fields, 369-373. See also page fields (VBA)
Filters area, adding fields to, 96
formatting, 14
model fields, adding to pivot tables, 383
multiple data fields, troubleshooting issues with, 355-356
naming fields, 49
numeric fields
adding to Values area, 384
rank number fields, adding to pivot tables, 396-398
page fields (VBA), 369-373. See also filter fields
pivot fields
manually filtering multiple items in, 373-374
adding fields to tables, 24
dragging/dropping fields in tables, 25-26
primary keys, 177
rank number fields, adding to pivot tables, 396-398
row fields
adding multiple subtotals to one field, 72
filtering, 88
suppressing subtotals in, 348
turning subtotals on/off in, 70-71
text fields, grouping for redistricting (case study), 106-108
Value field, customizing calculations, 63-70
Fields list
docking/undocking, 77
filling
blank cells
in data area, 336
in row area, 336
empty cells in row fields, 393
Go To Special function (Excel), 395-396
Repeat All Item Labels feature, 394-395
FILTER function, 238
3D Map, 296
AutoFiltering pivot tables, 401-404
conceptual filters in VBA, 374-377
date filters in VBA, 375
Filtered Items in Totals, 269
filter fields, 369-373. See also page fields (VBA)
label filters in VBA, 374
pivot fields, manually filtering multiple items in, 373-374
pivot tables
AutoFiltering pivot tables, 401-404
column fields, 88
dates, filtering by, 101
replicating reports for each item in a filter, 97-98
row fields, 88
Values columns, filtering Label columns, 91-92
recordsets, filtering with ShowDetail (VBA), 367-373
search filter in VBA, 377, 378
Top 10 filter, 268
value filters in VBA, 375
adding fields to, 96
choosing
multiple items from a filter, 97
one item from a filter, 96
creating filters, 27
fonts and themes, 62
forecasts, summarizing next year’s forecast (case study), 129-133
form controls
ActiveX controls versus, 307
defined, 306
macros, assigning to form controls, 308
scrollbars, adding to spreadsheets, 309-312
user interfaces, creating, 306-308
viewing, 307
formatting
conditional formatting and pivot tables
example of conditional formatting, 165-167
preprogrammed scenarios, 167-168
fields, 14
individual values in pivot tables, 405-407
numbers
multiple number formats in pivot tables, 404-405
pivot charts, 156
sections of pivot tables, 407-408
formulas
arrow keys in formula creation, 286
calculated fields, creating with formulas outside a pivot table, 123-124
documenting, 148
GetPivotData, preventing, 429
List Formulas command, 148
frequency distributions, creating with pivot tables, 409-410
functions (OLAP cube), 248-250
Gainer, Dave, 425
geocoding data in 3D Map, 290-291
GeoFlow. See 3D Map
GetPivotData, 425
default status, determining reasons for its, 430-431
pivot table annoyances, solving, 431
creating shell reports, 435-436
creating ugly pivot tables, 432-435
populating shell reports, 437-440
updating shell reports, 441-442
preventing
by turning off GetPivotData, 429-430
by typing the formula, 429
GETPIVOTDATA function
cube formulas versus, 232
extracting cells from pivot tables based on pivot caches, 232
Get & Transform tools, cleaning up data sources (case studies), 15-19
globes, using store maps instead of (case study), 299-302
Go To Special function (Excel), empty cells, filling in row fields, 395-396
grand totals
calculations, grand total references, 139
gridlines, restoring to pivot tables, 45
grouping
auto date-grouping, 75
AutoGroup
determining AutoGroup criteria, 111
using, 112
year-over-year reports, 113-115
date fields
including years when grouping by months, 110
date grouping, xxiv
pivot tables, 286
repeating as columns, 13
text fields for redistricting (case study), 106-108
hard data, turning pivot tables into, 393
headings
column headings, 11
section headings, storing data in, 12
heat maps in 3D Map, 294
help
Excel Help tool (VBA), 326
support/feedback, xxxii
hiding
columns with Power Pivot, 287
legends in 3D Map, 296
panes in 3D Map, 296
drill-down hierarchies, 219-220
Power Pivot and, 287
high-to-low sequences, sorting customers in based on revenue, 79-82
Hoter, Dany, 238
images in Power BI Desktop, 212
importing
custom visualizations, Power BI Desktop, 221-222
data to Power BI Desktop, 208
Index option (Value Field Settings dialog box, Show Values As tab), 69-70
individual values, formatting in pivot tables, 405-407
Insert Slicers dialog box, 31-32
Insights command (Office 365), xxiv
Insights feature and Power BI Desktop, 224
interactive reports
Power BI Desktop, creating reports with
cross-filtering charts, 218
drill-down hierarchies, 219-220
mobile phones, designing reports for, 223
publishing to Power BI Desktop, 222
visualizations, 213-217, 221-224
showing revenue by product and time period (case study), 157-161
interactive web pages, workbooks as, 226-229
item labels, repeating, 52
Javelin, xxvi
Jelen, Bill, 306
Label columns, filtering with Values column information, 91-92
Label Filters option, filtering with, 90-91
labeling
column labels, splitting between two rows, 12
data points in 3D Map, 294
label filters in VBA, 374
maps in 3D Map, 295
Repeat All Item Labels, 330, 394-395
repeating item labels, 52
row labels, turning on/off in Compact layout, 58
large data files, processing with Power Query, 273-275
latitude (3D Map), 290
layers, adding to reports, 25
classic pivot table layout, 26-27
Compact layout, 51-53, 58, 75-76
deferring layout updates, 39-40
subtotals, 50
Tabular layout, 12-14, 53, 75-76, 344
vertically arranging pivot tables, 59
legends (3D Map), 296
life after pivot tables (case study), xxvii-xxix
life before pivot tables (case study), xxiv-xxvi
line-to-line comparisons, Value field calculations, 66
linked tables, adding to Data Model, 279
links, sharing to workbooks, 229
List Formulas command, 148
lists (custom), sorting, 84-86, 286
live pivot tables, converting to static values (case study), 54-55
Live Preview feature, 60
loading to Data Model instead of Excel grid, 277-278
longitude (3D Map), 290
Lotus, xxvi
Lotus Improv, xxvi
M is for Data Monkey, 195, 287
M language
applied steps (Power Query), 194-195
viewing, 276
Macro Recorder versus Power Query, 277
macros, 330
benefits of, 303
creating
defined, 303
disabling, 324
form controls, assigning macros to, 308
functionality, adding to recorded macros, 308-309
pivot table reports and, 303
scrollbars, adding to spreadsheets, 309-312
managing
calculations, OLAP pivot tables, 257-258
Data Model relationships, 179
queries in Power Query, 196-198
manual sort sequences, 82, 83, 84
manually
adding calculated fields to data sources, 122-123
maps (3D), 289
aerial photography maps, 295
animating data over time, 297-298
annotation (text boxes), 294, 299
bubble charts, 294
combining data sets, 297
custom maps, 290
custom shapes, 290
data cards (tooltips), 296
filters, 296
heat maps, 294
hiding
legends, 296
panes, 296
labeling
data points, 294
maps, 295
latitude, 290
legends
hiding, 296
resizing, 296
longitude, 290
pie charts, 294
preparing data for, 289
region maps, 294
relationships (table), 290
resizing legends, 296
store maps, using instead of globes (case study), 299-302
table relationships, 290
timelines, 298
vantage point, moving, 293
video, creating, 299
zooming in/out of maps, 293
market activity analysis (case study), 35-36
McDaid, Joe, 238
MDX (Multidimensional Expressions), 250
MDX Solutions, 251
measures (DAX), 139
defined, 121
Power Pivot, 384. See also calculations
using DAX measures instead of calculated fields (case study), 141-145
median calculations with DAX measures, 269-271
members of OLAP cubes, 243, 250, 254-257
Merge queries, 421
mobile phones, designing interactive reports for, 223
model fields, adding to pivot tables, 383
modifying styles with document themes, 61-62
months, grouping by, 110
mouse, manual sort sequences, 83-84
Movie PivotTable dialog box, 41
multiple data fields, troubleshooting issues with, 355-356
multiple number formats in pivot tables, 404-405
multiple ranges, defined, 175
named ranges and calculations, 139
named sets, 330
naming
fields, 49
pivot tables, 33
natural language queries and Power BI Desktop, 213
navigating maps in 3D Map, 292-293
next year’s forecast (case study), summarizing, 129-133
number fields (rank), adding to pivot tables, 396-398
number formats
changing in pivot tables, 347-348
multiple formats in pivot tables, 404-405
numeric columns in blank cells, 24
numeric fields
adding to Values area, 384
object-oriented coding
End+Down arrow versus End+Up arrow, 328
shortening code, 329
With and End With, 329
Office 365
costs of, xxxi
formatting cells, 72
Insights command, xxiv
ribbon, xxx
offline cubes, creating, 245-247
OLAP (Online Analytical Processing)
cubes
dimensions of, 243
levels of, 243
offline cubes, creating, 245-247
PivotTable Fields list, 244
pivot tables
adding calculations to, 250
managing calculations, 257-258
what-if analysis of OLAP data, 258-259
OneDrive workbooks, sharing links to, 229
operator precedence and calculations, 138
page breaks, subtotals as, 350-351
page fields (VBA), 369-373. See also filter fields
percentage change column for year-over-year reports, 417-419
pictures of pivot charts, distributing, 163
pie charts in 3D Map, 294
cells, extracting from pivot tables based on pivot caches, 232
Data Model tables and, 383
defining, 383
refreshing, 6
changes made to existing data sources, 36
expanded data source ranges, 37
pivot charts
alternatives to, 161
deleting underlying pivot tables, 162
distributing pictures of pivot charts, 163
pivot table-linked cells as pivot chart source data, 163-165
turning pivot tables into hard values, 162
defined, 149
formatting limitations, 156
interactive reports showing revenue by product and time period (case study), 157-161
pivot field buttons, 152-153, 160
pivot tables and
effects of changes in pivot tables on pivot charts, 154
placement of data fields in pivot tables on pivot charts, 154-156
x-axis, 155
y-axis, 155
pivot field buttons (pivot charts), 152-153, 160
pivot fields
manually filtering multiple items in, 373-374
Pivot Power Premium add-in, 45
fields
adding to tables, 24
dragging/dropping in tables, 25-26
OLAP cubes, 244
pivot table reports, 1
column headings, 11
deferring layout updates, 39-40
starting over, 40
location of, choosing, 20
macros, 303
refreshing
changes made to existing data sources, 36
expanded data source ranges, 37
size of, reducing, 398
snapshots, 6
pivot tables, 1
anatomy of
Columns area, 5
Filters area, 6
Rows area, 5
Values area, 4
annoyances, solving with GetPivotData, 431-432
creating shell reports, 435,-436
creating ugly pivot tables, 432-435
populating shell reports, 437-440
updating shell reports, 441-442
basic creation, 19
adding fields to reports, 22-24
adding layers to reports, 25
choosing report location, 20
classic pivot table layout, 26-27
creating filters, 27
data selection, 20
dragging/dropping fields in reports, 25-26
laying out reports, 22-24, 39-40
Recommended PivotTables, 28-30
blank cells, deleting, 346-347
calculated data fields, 358-359
calculating with, 355
Show Values As tab (Value Field Settings dialog box), 362-363
VBA calculation options per Excel version, 364
calculation functions, 356-358
cells
blank cells, deleting, 346-347
extracting from pivot tables based on pivot caches, 232
comparing tables with, 400-401
conditional formatting
preprogrammed scenarios, 167-168
converting live tables to static values (case study), 54-55
copying as values to workbooks, 349-350
creating. See also basic pivot table creation
from Data Model, 283
Pivot Power Premium add-in, 45
cube formulas, converting pivot tables to, 232-238
custom lists, sorting, 286
customizing, 44
changing field names, 49
converting live pivot tables to static values (case study), 54-55
Count of Revenue bug, 62
default pivot tables, 44
formatting cells in Office 365, 72
gridlines, 45
subtotals, 50
number format, thousands separators, 46-47
Pivot Power Premium add-in, 45
previewing changes with Live Preview feature, 60
replacing blank cells with zeros, 47-48
settings, xxiv
Value field calculations, 63-70
Data Model
adding model fields to, 383
adding numeric fields to Values area, 384
creating pivot tables, 283, 383-386
data sets, exploding
to different workbooks, 415-417
default behaviors, changing for future pivot tables, 392
default settings, xxiv
development of, xxvi
distributions (frequency), creating, 409-410
Excel backward compatibility, 7, 8
extracts, creating, 3
filters
AutoFiltering pivot tables, 401-404
creating, 27
individual values in pivot tables, 405-407
multiple number formats in pivot tables, 404-405
sections of pivot tables, 407-408
VBA and, 350
frequency distributions, creating, 409-410
GetPivotData, solving annoyances with, 431-432
creating shell reports, 435-436
creating ugly pivot tables, 432-435
populating shell reports, 437-440
updating shell reports, 441-442
grouping, 286
hard data, turning pivot tables into, 393
life after (case study), xxvii-xxix
life before (case study), xxiv-xxvi
live tables, converting to static values (case study), 54-55
limitations of, 7
Move PivotTable dialog box, 41
multiple data fields, troubleshooting issues with, 355-356
naming, 33
number fields (rank), adding to pivot tables, 396-398
number format
multiple number formats in, 404-405
OLAP pivot tables
adding calculations to, 250
managing calculations, 257-258
what-if analysis of OLAP data, 258-259
page breaks, subtotals as, 350-351
product calculations, 286
rank number fields, adding to pivot tables, 396-398
Recommended PivotTables, xxvii, 28-30
refreshing, 431
all pivot tables in a workbook at the same time, 388
automatically refreshing, 388
relocating, 41
slicers
connecting to multiple pivot tables, 32-33
sort order, controlling with AutoSort, 347
sorting
static values (case study), converting live tables to, 54-55
subtotals
suppressing for multiple row fields, 348
Tabular layouts and VBA, 344
ugly pivot tables, creating, 432-435
updates, xxxii
VBA
AutoShow and executive overviews, 365-367
calculating with pivot tables, 355-364
changing default number format, 347-348
controlling sort order with AutoSort, 347
copying as values to workbooks, 349-350
creating pivot tables, 330-338
creating reports for each region/model, 369-373
creating revenue by category reports, 341-344
date filters, 375
filtering recordsets with ShowDetail, 367-373
formatting pivot tables, 350
label filters, 374
manually filtering multiple items in pivot fields, 373-374
slicers, filtering pivot tables, 378-380
subtotals as page breaks, 350-351
summary reports, producing, 352-354
suppressing subtotals for multiple row fields, 348
Tabular layouts, 344
value filters, 375
versions of, 330
workbooks, copying pivot tables to, 349-350
Power BI Custom Visuals, 173-174
cross-filtering charts, 218
drill-down hierarchies, 219-220
Excel, preparing data in, 208
images in, 212
importing data to, 208
Insights feature, 224
interactive reports, creating
creating visualizations, 213-217
cross-filtering charts, 218
designing reports for mobile phones, 223
drill-down hierarchies, 219-220
importing custom visualizations, 221-222
publishing to Power BI Desktop, 222
publishing to workspaces, 223-224
natural language queries, 213
publishing to, 222
setting up, 207
signing in, 207
synonyms, defining, 213
visualizations, 209
importing custom visualizations, 221-222
workspaces, publishing to, 223-224
Power Map. See 3D Map
32-bit Excel and, 287
benefits of, 287
columns
calculated columns, adding in Power Pivot grid, 281-282
categorizing, 287
hiding, 287
renaming, 287
sorting one column by another, 282
Data Model
adding model fields to pivot tables, 383
adding numeric fields to Values area, 384
adding tables to, 381
creating pivot tables, 383-386
creating relationships between tables, 382
defining pivot caches, 383
measures, 384. See also calculations
pivot tables, creating from Data Model, 283
relationships, 284
time intelligence, 285
using DAX measures instead of calculated fields (case study), 143
Power Pivot and Power BI, 287
Power Pivot Data Model, 330
Power Pivot for the Data Analyst, 282
Power Query
Advanced Query Editor, 195
big data files, processing, 273-275
cleaning up data sources (case studies), 15-19
columns
Column From Examples feature, 275
removing, 191
connection types, 202
ETL processes, 188
installing, 188
M language
viewing, 276
outputting data to Data Model, 193
Power Query Editor, cleaning up data sources (case studies), 16
previewing data, 190
queries
refreshing data, 196
removing columns, 191
splitting
transposing data sets (case study), 202-204
VBA Macro Recorder versus, 277
Power View, xxiv
previewing changes with Live Preview feature, 60
primary keys, 177
product calculations, 286
Project Explorer (Visual Basic Editor), 325
properties (VBA coding), 327
Properties window (Visual Basic Editor), 326
publishing
interactive reports to workspaces, 223-224
to Power BI Desktop, 222
Q&A feature and Power BI Desktop, 225-226
queries
managing queries with Power Query, 196-198
Merge queries, 421
natural language queries, 213
Power BI Desktop
natural language queries, 213
querying data with Q&A feature, 225-226
starting queries with Power Query, 188-190
web queries (Power Query)
Advanced Query Editor, 195
connection types, 202
ETL processes, 188
installing, 188
outputting data to Data Model, 193
previewing data, 190
refreshing data, 196
removing columns, 191
splitting columns/rows, 204-206
transposing data sets (case study), 202-204
ranges
multiple ranges, defined, 175
named ranges and calculations, 139
rank number fields, adding to pivot tables, 396-398
ranking, Value field calculations, 67
rearranging
Recommended PivotTables, xxvii, 28-30
redistricting, grouping text fields for (case study), 106-108
references and calculations
cell references, 139
totals, 139
refreshing
data in Power Query, 196
data sources
changes made to existing data sources, 36
expanded data source ranges, 37
pivot caches, 6
changes made to existing data sources, 36
expanded data source ranges, 37
pivot table reports
changes made to existing data sources, 36
expanded data source ranges, 37
pivot tables, 431
all pivot tables in a workbook at the same time, 388
automatically refreshing, 388
slow refreshes, 286
region maps in 3D Map, 294
relationships
3D Map table relationships, 290
Data Model
activating relationships, 180
creating relationships, 178-180
deactivating relationships, 180
deleting relationships, 180
editing relationships, 180
managing relationships, 179
Power Pivot and, 284
tables, defining relationships between tables with Data Model, 279-280
relocating pivot tables, 41
removing columns with Power Query Editor, 191
renaming columns with Power Pivot, 287
re-ordering items, 286
Repeat All Item Labels, 330, 394-395
repeating
groups as columns, 13
item labels, 52
replicating reports for each item in a filter, 97-98
report layouts, 50
Compact layout, 51, 53, 75, 76
turning row labels on/off, 58
subtotals, 50
reports
filters, replicating reports for each item in a, 97-98
interactive reports, creating with Power BI Desktop
creating visualizations, 213, 214, 215, 217
cross-filtering charts, 218
designing reports for mobile phones, 223
drill-down hierarchies, 219, 220
importing custom visualizations, 221, 222
publishing to Power BI Desktop, 222
publishing to workspaces, 223, 224
layouts
Compact layout, 51-53, 58, 75-76
subtotals, 50
macros, 303
replicating reports for each item in a filter, 97-98
revenue by category reports, creating, 341-344
shell reports
size of, reducing, 398
summary reports, producing, 352-354
Top 5 Markets reports, 365-367
top-five reports, creating with Top 10 filter, 93-94
VBA, creating reports for each region/model, 369-373
year-over-year reports
percentage change column and, 417-419
requirements (system), xxxii
resizing legends (3D Map), 296
restrictions
revenue
interactive reports showing revenue by product and time period (case study), creating, 157-161
revenue by category reports, creating, 341-344
sorting customers into high-to-low sequences based on revenue, 79-82
ribbon
rows
% Of Parent Row option (Value Field Settings dialog box, Show Values As tab), 69
blank cells, filling in row area, 336
blank rows (layouts), 50, 56-57
column labels, splitting between two rows, 12
empty cells, filling, 393
Go To Special function, 395-396
Repeat All Item Labels feature, 394-395
empty rows in data sources, 14
filtering fields, 88
labels, turning on/off in Compact layout, 58
multiple row fields, suppressing subtotals in, 348
splitting with Power Query, 204-206
subtotals
adding multiple subtotals to one field, 72
suppressing for multiple row fields, 348
turning on/off in row fields, 70,-71
running total calculations, Value field calculations, 67-68
Salas, Pito, xxvi
scrollbars, adding to spreadsheets, 309-312
search box, filtering with, 89-90
section headings, storing data in, 12
security
trusted locations, 306
shapes (custom) in 3D Map, 290
sharing
links to workbooks, 229
shell reports
ShowDetail (VBA), filtering recordsets, 367-369
ShowPages (VBA), 370
Show Values As tab (Value Field Settings dialog box), 65
% Difference From option, 68-69
% Of option, 66
% Of Parent Column option, 69
% Of Parent Row option, 69
% Of Parent Total option, 69
% Of Total option, 66
performing calculations with, 362-363
ranking options, 67
running total calculations, 67-68
size of pivot table reports, reducing, 398
slicers, 330. See also filters
columns, adding to slicers, 100
compatibility, 7
filtering with, 99-101, 378-380
Insert Slicers dialog box, 31-32
multiple pivot tables
slicing the measures, 243
standard slicers, creating, 31-33
Timeline slicers, 101
compatibility, 35
driving multiple tables from, 102-104
interactive reports showing revenue by product and time period (case study), creating, 158-159
snapshots
pivot caches, refreshing, 6
pivot table reports, 6
solve order of calculated items, changing, 147
SORTBY function, 238
sorting
custom lists, 286
data items, unique sort order, 389-390
one column by another with Power Pivot, 282
pivot tables
sorting customers into high-to-low sequences based on revenue, 79-82
sort order, controlling with AutoSort, 347
source data worksheets, deleting, 398
spaces
as text, 24
Delete key versus, 24
splitting
columns
labels between two rows, 12
columns with Power Query, 204-206
rows with Power Query, 204-206
Spofford, George, 251
spreadsheets, adding scrollbars to, 309-312
SQL Server, creating pivot tables from SQL Server data, 184-187
standard slicers, creating, 31-33
starting over, pivot table layouts, 40
static values, converting live pivot tables to (case study), 54-55
store maps, using instead of a globe (case study), 299-302
storing data in section headings, 12
styles
creating, 60
customizing, 60
modifying with document themes, 61-62
pivot table styles, 59
applying, 45
creating styles, 60
customizing styles, 60
modifying with document themes, 61-62
subtotals, 50
calculations, subtotal references, 139
multiple subtotals, adding to one field, 72
row fields, turning subtotals on/off in, 70-71
Sum, Count of Revenue bug, 62
summarizing next year’s forecast (case study), 129-133
summary reports, producing, 352-354
SUMPRODUCT function. See SUMIFS function
Supercharge Excel, 287
super-variables, coding, 328-329
support/feedback, xxxii
synonyms, defining in Power BI Desktop, 213
system requirements, xxxii
tables
adding to Data Model, 180
Data Model
adding tables to, 180
defining relationships, 279-280
linked tables in, 279
pivot tables, comparing with, 400-401
Power Query, table actions, 200-201
relationships
3D Map relationships, 290
defining with Data Model, 279-280
styles, applying, 45
Tabular layout, 12-14, 53, 75-76, 344
text
spaces as, 24
Values area, reporting text in, 272
text boxes (annotation) in 3D Map, 294, 299
text fields, grouping for redistricting (case study), 106-108
themes
document themes, modifying styles with, 61-62
fonts, 62
thousands separators (number format), 46-47
time intelligence (Power Pivot), 285
Timeline slicers, 101
compatibility, 35
driving multiple tables from, 102-104
interactive reports showing revenue by product and time period (case study), creating, 158-159
timelines
3D Map tours, 298
compatibility, 7
tooltips (data cards) in 3D Maps, 296
Top 5 Markets reports, 93-94, 365-367
totals
calculations, referencing in, 139
Filtered Items in Totals, 269
pivot tables, controlling totals, 337-338
tours (3D Map), creating, 298-299
transactional databases, 239
transposing data sets with Power Query (case study), 202-204
Trust Center, enabling macros, 323-324
trusted locations, 306
turning on/off
row labels in Compact layout, 58
subtotals in row fields, 70-71
ugly pivot tables, creating, 432-435
undocking/docking Fields list, 77
updates, xxxii
pivot caches
changes made to existing data sources, 36
expanded data source ranges, 37
pivot table updates, deferring, 39-40
slow refreshes, 286
user interfaces
creating with form controls, 306-308
defined, 306
Value field, customizing calculations, 63-65
% Difference From option (Value Field Settings dialog box, Show Values As tab), 68-69
% Of option (Value Field Settings dialog box, Show Values As tab), 66
% Of Parent Column option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Row option (Value Field Settings dialog box, Show Values As tab), 69
% Of Parent Total option (Value Field Settings dialog box, Show Values As tab), 69
% Of Total option (Value Field Settings dialog box, Show Values As tab), 66
Index option (Value Field Settings dialog box, Show Values As tab), 69-70
line-to-line comparisons, 66
performing calculations with, 362-363
ranking options, 67
running total calculations, 67-68
values
filters in VBA, 375
pivot tables, converting to, 54-55, 338-341
fields, changing names, 49
text, reporting in, 272
Values columns, filtering Label columns, 91-92
vantage point (3D Map), moving, 293
VBA (Visual Basic for Applications)
AutoComplete tool, 326
AutoShow, executive overviews of pivot tables, 365-367
coding, properties, 327
Comments tool, 326
date filters, 375
Debugging tool, 326
Excel Help tool, 326
label filters, 374
Macro Controller
scrollbars, adding to spreadsheets, 310-312
Macro Recorder versus Power Query, 277
object-oriented coding
End+Down arrow versus End+Up arrow, 328
shortening code, 329
With and End With, 329
page fields, 369-373. See also filter fields
pivot fields, manually filtering multiple items in, 373-374
pivot tables
changing default number format, 347-348
copying as values to workbooks, 349-350
formatting, 350
producing summary reports, 352-354
sort order, controlling with AutoSort, 347
revenue by category reports, 341-344
subtotals as page breaks, 350-351
subtotals, suppressing for multiple row fields, 348
Tabular layouts, 344
pivot tables, advanced techniques
AutoShow and executive overviews, 365-367
creating reports for each region/model, 369-373
date filters, 375
filtering recordsets with ShowDetail, 367-373
label filters, 374
manually filtering multiple items in pivot fields, 373-374
slicers, filtering pivot tables, 378-380
value filters, 375
pivot tables, calculating with
calculated data fields, 358-359
calculation functions, 356-358
Show Values As tab (Value Field Settings dialog box), 362-363
troubleshooting issues with multiple data fields, 355-356
VBA calculation options per Excel version, 364
pivot tables, creating, 330-331
adding fields to data areas, 332-333
filling blank cells in data or row area, 336
preventing errors from inserting/deleting cells, 336-337
reports, creating for each region/model, 369-373
ShowDetail, filtering recordsets, 367-369
ShowPages, 370
slicers, filtering pivot tables, 378-380
summary reports, producing, 352-354
value filters, 375
Visual Basic Editor
Code window, 326
Project Explorer, 325
Properties window, 326
vertically arranging pivot tables, 59
video, creating with 3D Map, 299
Visual Basic Editor
Code window, 326
Project Explorer, 325
Properties window, 326
visualizations
customizing with Power BI Desktop, 221-222
pivot charts
creating interactive reports showing revenue by product and time period (case study), 157-161
defined, 149
effects of changes in pivot tables on pivot charts, 154
formatting limitations, 156
pivot field buttons, 152-153, 160
placement of data fields in pivot tables on pivot charts, 154-156
x-axis, 155
y-axis, 155
Power BI Custom Visuals, 173-174
Power BI Desktop, 209
creating visualizations, 213-217
importing custom visualizations, 221-222
VLOOKUP, 206
Data Model, replacing with, 261-266
web pages (interactive),workbooks as, 226-229
web queries (Power Query)
Advanced Query Editor, 195
connection types, 202
ETL processes, 188
installing, 188
outputting data to Data Model, 193
previewing data, 190
refreshing data, 196
removing columns, 191
splitting columns/rows, 204-206
transposing data sets (case study), 202-204
weeks, grouping date fields by, 110-111
what-if analysis, OLAP pivot table data, 258-259
widths (columns)
AutoFit, 59
vertically arranged pivot tables, 59
With and End With, coding, 329
workbooks
data sets, exploding to different workbooks, 415-417
interactive web pages, workbooks as, 226-229
macros, enabling, 325
pivot tables
copying pivot tables to, 349-350
refreshing all pivot tables at the same time, 388
sharing links to, 229
worksheets
functions and calculations, 139
source data worksheets, deleting, 398
workspaces, publishing interactive reports to, 223-224
x-axis (pivot charts), 155
.xls file format, enabling macros, 325
.xlsb file format, enabling macros, 325
.xlsm file format, enabling macros, 325
y-axis (pivot charts), 155
year-over-year reports
percentage change column and, 417-419
years
including when grouping by months, 110
summarizing next year’s forecast (case study), 129-133
zeros, replacing blank cells with, 47-48
zooming in/out of maps in 3D Map, 293