Index
Symbols
% (percent), comparing lines with, 71
= (equal sign), 292
3D Map, 245
bubble/pie charts, creating, 266
column charts, formatting, 264
data over time, animating, 269
geographic data, analyzing, 261-273
heat maps/region maps, applying, 266
labeling points, 266
preparing data for, 261
videos, sharing, 271
A
Access (Microsoft), formatting pivot tables, 169-171
accessing functions, OLAP, 211-213
accounts, saving to OneDrive, 195
actions
column-level, 185
activating classic layouts, 30
ActiveX controls, 279
activity by market, analysis of, 38
Add This Data to the Data Model option, 25
Pivot Power Premium, 47
Power Pivot, 225
defining relationships, 236
enabling, 234
linking files, 236
AddFields method, 298
adding
calculated columns using DAX language, 237
calculated fields to data sources, 114
calculated items, 126
calculations to OLAP, 213-215, 222
fields, 251
creating pivot tables with VBA, 298-299
filters areas, 104
Horizontal Multiples, 256
layers, 28
numeric fields to Values areas, 348-349
pages to sectors, 107
paths, 246
regions, 27
slicers, 33
tables to Data Models, 163-165, 228, 346
Timeline slicers, 36
Advanced Editor dialog box, 182
advantages of pivot tables, 11
Access (Microsoft), formatting, 169-171
activity by market, 38
calculations, 113
See also calculations
Data Models
deleting tables, 165
limitations of internal, 167
managing relationships, 162-163
data sets, selecting, 25
geographic data with 3D Map, 261-273
OLAP what-if-analysis, executing, 220-222
Power Query
formatting columns, 185
refreshing, 183
Quick Analysis option, 32
SQL Servers, formatting, 171-174
Analysis Services
options, 204
Tutorial cube, 205
animating scatter charts, 259-260
applying
AutoFilter, 363
AutoSort, 312
cells, references, 129
conditional formatting, 149-153
constants, 129
drop-down lists, Areas section, 88-89
functions, 64
heat/region maps, 3D Map, 266
Index function, 74
manual sequences to sorting, 92-93
named ranges, 129
pivot charts, 135-136, 145-148
styles, restoring gridlines, 47
Tabular layout, 55
Visual Basic Editor, 291
worksheet functions, 129
areas
navigating, 12
Areas section, drop-down lists, 88-89
assigning macros, 279
authentication (Windows Authentication), 172
AutoComplete, 292
AutoFilter, 363
automatic grouping
undoing, 78
AutoSort, applying, 312
avoiding
groups, repeating, 21
section headings, storing, 20
B
backward compatibility, 15-17, 38
benefits of Data Models, 224-225
See also limitations
blank cells, 23
Data areas, filling, 301
data sources, deleting, 22
Row areas, filling, 302
BLANK function, 239
Blank Rows setting, 57
breaks (page), adding subtotals, 315
building. See formatting
C
caches, 15
defining, 347
overwriting, 39
slicers, defining, 344
calculated columns (DAX)
adding, 237
applying calculations for, 239
data sources, adding to, 114
DAX language
filtering, 240
formulas, creating with, 115
inserting, 116
naming, 118
rules, 129
adding, 126
naming, 125
rules, 131
solve order, modifying, 132
customizing, 116
DAX language, applying, 239-243
deleting, 131
editing, 131
fields, 114
See also calculated fields
items, 124
See also calculated items
OLAP (online analytical processing)
managing, 220
percentages of parent items, 73
Power View, modifying, 259
summary calculations, modifying, 63-64
Caption property, 322
case studies
analysis
activity by market, 38
calculated fields, summaries, 120-123
Data Models, applying, 231-232
pivot charts
Power Query, transposing data sets, 190-192
categorizing fields, 245
cells
blank. See blank cells
deleting, 22
inserting, 302
pivot charts, applying as source data, 147-148
preventing errors, 302
references, applying, 129
Change PivotTable Data Source dialog box, 40
charts
3D Maps, creating, 266
columns, formatting, 264
filtering, 255
overview of, 136
See also pivot charts
Power View
copying, 256
filtering, 252
scatter
XY, 141
titles, 141
CHOOSE function. See SWITCH function
classic layouts, activating, 30
See also layouts
cleaning up data for analysis, 23-24
code
Data Models, generating, 349
pivot tables, generating, 300
reports, generating, 319
summaries, generating, 305
VBA (Visual Basic for Applications)
applying object variables, 294-295
editing, 282
shortening, 295
Code window, 291
Collapse Entire Field (+) button, 138
Collapse Entire Field (-) button, 138
colors, selecting, 63
automatic grouping, applying, 77-85
charts, formatting, 264
data sources, expanding, 39
formatting, 246
grand totals, viewing, 58
groups, avoiding repeating, 21
moving, 92
Off for Rows and Columns setting, 59
Power Query, formatting, 185
tabular layouts, 20
commands
Distinct Count, 233
From Web, 177
List Formulas, 133
MDX Calculated Measure, 215
MDX Calculated Member, 218
Query Settings, 181
Share with People, 196
Summarize Values By, 64
comments, 292
Compact layout, 299
calculated items, 124
lines with % (percent), 71
pivot tables/Power Pivot add-in, 238-239
tables, 361
Compatibility Checker, 16
Compatibility mode, 16
compatibility, backward. See backward compatibility
conceptual filters, applying, 339-340
conditional formatting
applying, 153
pivot charts, applying, 149-151
configuring
See also formatting
conditional formatting, 151
connecting
Data Connection Wizard, 172
databases, 171
Existing Connections dialog box, 167
OLAP (online analytical processing), 204-206
slicers, 35
Workbook Connections dialog box, 164
constants, applying, 129
controlling
sort order, 312
controls
ActiveX, 279
forms
formatting interfaces with, 278-280
inserting scrollbars, 281
Convert option, 16
Convert to Formulas option, 212
converting
pivot tables to values, 55-57, 304-305
ranges, 163
tables to charts (Power View), 250-251
charts (Power View), 256
pivot tables to workbooks, 314-315
Count Distinct option, 224
counting
records, 322
Create Cube File wizard, 210
Create PivotChart dialog box, 139
Create PivotTable dialog box, 25, 159, 166, 228
Create Table dialog box, 163
CreatePivotTable method, 297
cubes
Analysis Services Tutorial, 205
functions, navigating, 212-213
naming, 211
OLAP (online analytical processing)
creating offline cubes, 209-211
Custom Lists dialog box, 93
Custom Name dialog box, 51
customizing
calculations, 116
conditional formatting, 152-156
adding/removing subtotals, 64-67
applying functions, 64
configuring calculations, 67-75
naming fields, 51
replacing blanks with zeros, 49-50
restoring gridlines, 47
Tabular layout, 55
D
dashboards, 245
3D Map
analyzing geographic data with, 261-273
animating data over time, 269
applying heat maps/region maps, 266
creating bubble/pie charts, 266
formatting column charts, 264
labeling points, 266
preparing data for, 261
sharing videos, 271
formatting, 196
Map, 257
Power View
adding drill-down to charts, 251-252
animating scatter charts, 259-260
applying tile boxes, 255
converting tables to charts, 250-251
copying charts, 256
creating slicers, 253
filtering charts, 252
modifying calculations, 259
navigating Filters pane, 254-255
overview of, 261
populating with images, 258
sorting, 249
starting elements, 252
sharing, 202
Data areas, filling blank cells, 301
Data Connection Wizard, 172, 204
data mining, 203
See also OLAP
code, generating, 349
DAX language, applying, 224
merging, 223
pivot tables, formatting, 232-233
relationships
tables
deleting, 165
formatting, 166
unlimited rows, importing, 224
data over time, animating 3D Map, 269
data ranges, expanding, 361
data sets, 9
selecting, 25
analysis, 157
blank cells, deleting, 22
calculated fields, adding to, 114
design, 22
pivot tables, formatting, 168-174
databases
connecting, 171
OLAP (online analytical processing)
creating offline cubes, 209-211
pivot tables, formatting, 169-171
tabular layouts, 20
transactional, 203
Date Filters, applying, 103-104
dates
selecting, 37
weeks, grouping, 81
applying, 224
calculated columns
adding, 237
applying calculations for, 239
calculated fields
filtering, 240
calculations, applying, 239-243
days
filtering, 37
grouping, 79
debugging, 292
default number formats, modifying, 312-313
default pivot tables, 46
See also pivot tables
default sorting, overriding, 89
Defer Layout Update option, 41-42, 297
deferring layout updates, 41-42
defining
caches, 347
calculated fields (DAX), 240-241
custom lists, 94
pivot tables, 9
relationships
Power Pivot add-in, 236
deleting
blank cells, 22
calculations, 131
cells, preventing errors, 302
connections, 190
pivot tables, 146
tables, 165
descriptions of macros, 276
design
See also formatting
data sources, 22
workbooks as web pages, 193-196
Design tab, PivotTable Styles gallery, 60
dialog boxes
Advanced Editor, 182
Change PivotTable Data Source, 40
Create PivotChart, 139
Create PivotTable, 25, 159, 166, 228
Create Table, 163
Custom Lists, 93
Custom Name, 51
Existing Connections, 167
Format Cells, 49
Format Object, 281
Go To Special, 357
Insert Calculated Field, 117, 120
Insert Calculated Item, 124
Insert Slicer, 34
Load To, 180
Macro, 286
Manage Relationships, 163
Modify PivotTable Quick Style, 62
More Sort Options, 91
Move Chart, 137
Move PivotTable, 43
New Calculated Measure, 216
New Calculated Member, 218
New Formatting Rule, 153
PivotTable Options, 209
Record Macro, 276
Report Connections, 35
Select Table, 170
Share, 196
Show Detail, 53
Show Report Filter Pages, 106
Solve Order, 132
Sort (Sector), 91
Value Field Settings, 48, 67, 233
Workbook Connections, 164
dimensions (OLAP), 207
Distinct Count command, 233
distributions, frequency, 82
docking fields, 87
documents
formulas, 133
dragging fields, 30
See also moving
drill-down, adding to charts, 251-252
driving multiple pivot tables, slicers, 110-112
drop-down lists, Areas section, 88-89
E
editing
calculations, 131
VBA code, 282
Visual Basic Editor, 291
elements
Power View, starting, 252
resizing, 251
tables, starting, 249
eliminating blank cells, 311-312
enabling
macros, saving workbooks, 290
Power Pivot add-in, 234
VBA (Visual Basic for Applications), 289-290
End key, 294
End With statements, 295
entering
formulas, 115
GetPivotData, preventing, 379
Entire Field (+) button, 138
equal sign (=), 292
errors, preventing, 302
ETL processes, 174
Excel Help, 292
Excel Options dialog box, 78, 276
Excel Pro Plus (Power Pivot add-in), 225, 234-238
executing
calculations, 113
See also calculations
OLAP what-if-analysis, 220-222
executive overviews, formatting, 329-332
Existing Connections dialog box, 167
Expand Entire Field (+) button, 138
expanding
data ranges, 361
ranges, 37
exploding workbooks to pivot tables, 372-373
expressions, MDX (multidimensional expressions), 214
external data sources, formatting, 157, 168-174
See also data sources
F
Field Settings dialog box, 84, 89
fields
buttons, pivot charts, 138-139
calculated. See calculated fields
categorizing, 245
filters areas, adding, 104
formatting, 22
formulas, converting to, 213
lists
docking/undocking, 87
moving, 87
models, adding to pivot tables, 348
modifying, viewing, 73
moving, 29
multiple, troubleshooting, 319-321
numeric, grouping, 82
PivotTable Fields list
searching, 26
viewing, 208
Region, adding, 27
restrictions, applying, 368-371
rows
filling empty cells left by, 356-358
suppressing subtotals, 313-314
values, modifying calculations, 67-75
formatting, 14
importing, 197
linking, 236
naming, 211
filling
blank cells
Data areas, 301
Row areas, 302
ranges, 23
AutoFilter, 363
calculated fields (DAX), 240
charts
applying tile boxes (Power View), 255
Power View, 252
fields
converting to formulas, 213
items
in grand totals, 225
selecting, 105
multiple items, 105
reports
formatting, 31
search filters, applying, 342-343
applying, 34
Top 10 filter, applying, 101-102
fields, adding, 104
Filters pane, navigating Power View, 254-255
fonts, 62
See also themes
Format Cells dialog box, 49
Format Object dialog box, 281
3D Map charts, 266
calculations
columns, 246
charts, 264
Power Query, 185
dashboards, 196
fields, 22
files, 14
formulas, 115
frequency distributions, 82, 366-367
interfaces with form controls, 278-280
numbers, 48-49, 312-313, 364-365
as images, 146
customizing conditional, 152-156
limitations, 141
Power Pivot add-in, 237
preparing data for reporting, 19-24
VBA (Visual Basic for Applications), 296-301
with external data sources, 168-174
reports
filters, 31
viewing by categories, 307-319
slicers
Power View, 253
subtotals, 315
tables
Data Model, 166
views, 9
web pages, sharing pivot tables, 193-196
forms
controls
formatting interfaces with, 278-280
inserting scrollbars, 281
formulas, 11
calculated fields, creating with, 115
Convert to Formulas option, 212
documenting, 133
entering, 115
GetPivotData, preventing, 379
troubleshooting, 377
frames, resizing elements, 251
frequency distributions, 82, 366-367
From Web command, 177
functions, 11
applying, 64
BLANK, 239
CHOOSE. See SWITCH function
GETPIVOTDATA, 225
INDEX, 285
Index, 74
OLAP (online analytical processing), accessing, 211-213
SUMIFS, 239
SWITCH, 240
TEXT, 239
VLOOKUP, 239
worksheets, applying, 129
G
generating code
Data Models, 349
pivot tables, 300
reports, 319
summaries, 305
pivot tables, formatting, 381-391
preventing, 379
GETPIVOTDATA function, 225
Go To Special dialog box, 357
items, filtering, 225
viewing, 58
gridlines, restoring, 47
grouping, 77
automatic, undoing, 78
selecting, 78
weeks, 81
Layout, 52
months, 80
numeric fields, 82
repeating, avoiding, 21
years, 80
H
hard data, formatting, 355-356
See also summaries
hard values, modifying, 145
heat maps, 3D Map, 266
help (Excel Help), 292
hierarchies (OLAP), 207
hours, grouping, 79
How Many Customers to Show slicer, 194
I
pivot charts, formatting as, 146
Power View, populating with, 258
Import Data dialog box, 170, 206
importing
text (Power Pivot add-in), 235-236
unlimited rows with Data Models, 224
INDEX function, 285
Index function, applying, 74
Insert Calculated Field dialog box, 117, 120
Insert Calculated Item dialog box, 124
Insert Slicer dialog box, 34
Insert Timelines dialog box, 36, 143
inserting
calculated fields, 116
cells, preventing errors, 302
scrollbars, 281
slicers, 33
IntelliSense, 298
interactive reports, pivot charts, 142-144
interfaces, formatting, 278-280
items
calculations, 124
See also calculated items
grand totals, filtering, 225
percentages, tracking, 73
selecting, filtering, 105
sorting in unique orders, 355
specifying sorting, 91
J
joining multiple tables with Data Models, 226-233
K
keys
End, 294
primary, 162
L
labels
points (3D Map), 266
repeating, 53
languages
DAX, 224
See also DAX language
VBA. See VBA (Visual Basic for Applications)
layers, adding, 28
Layout group, 52
See also formatting
classic, starting, 30
troubleshooting, 33
updating
levels (OLAP), 207
of internal Data Models, 167
of OLAP (online analytical processing), 208-209
of VLOOKUP, 230
pivot charts, formatting, 141
lines
% (percent), comparing with, 71
trends, 141
linking
files (Power Pivot add-in), 236
Web workbooks, 196
List Formulas command, 133
lists
fields
docking/undocking, 87
moving, 87
PivotTable Fields
searching, 26
viewing, 208
Load button, 176
Load To dialog box, 180
locations
pivot charts, moving, 137
reports, 25
M
Macro dialog box, 286
macros, 275
assigning, 279
enabling, saving workbooks, 290
forms, creating interfaces with controls, 278-280
naming, 276
pivot charts, synchronizing, 283-287
recorders, 292
scrollbars, inserting, 281
shortcuts, 276
storing, 276
VBA. See VBA (Visual Basic for Applications)
maintenance, calculations, 131-133
Manage Relationships dialog box, 163
management
OLAP (online analytical processing), 220
query steps, 182
manual sequences, sorting, 92-93
ManualUpdate property, 297
Map, 257
maps. See 3D Map
MDX (multidimensional expressions), 214
MDX Calculated Measure command, 215
MDX Calculated Member command, 218
measures
calculations, formatting, 214-217
OLAP (online analytical processing), 208
members
calculations, formatting, 217-219
OLAP (online analytical processing), 207
memory, 14
merging multiple tables with Data Models, 223
methods
AddFields, 298
CreatePivotTable, 297
Microsoft Access, formatting, 169-171
minutes, grouping, 79
models
Data Models. See Data Models
fields, adding to pivot tables, 348
modes, Compatibility, 16
Modify PivotTable Quick Style dialog box, 62
modifying
calculations (Power View), 259
default number formats, 312-313
fields
naming, 51
viewing, 73
pivot tables
hard values 145
modifying pivot charts, 139
relationships, 162
months
filtering, 37
More Sort Options dialog box, 91
Move Chart dialog box, 137
Move PivotTable dialog box, 43
columns, 92
fields, 29
lists, 87
pivot tables, 43
multidimensional expressions. See MDX
multiple fields, troubleshooting, 319-321
multiple items, filtering, 105
multiple pivot tables, driving slicers, 110-112
multiple ranges, 157
See also data sources
multiple row fields, suppressing subtotals, 313-314
multiple subtotals, adding fields, 67
multiple tables (Data Model)
merging, 223
N
Name property, 322
named ranges, applying, 129
named sets, 225
naming
calculated fields, 118
calculated items, 125
cubes, 211
macros, 276
pivot tables, 36
navigating
End keys, 294
Filters pane (Power View), 254-255
OLAP (online analytical processing), 207-208
Recommended Pivot Table feature, 31-33
Navigator pane, 176
New Calculated Measure dialog box, 216
New Calculated Member dialog box, 218
new features, 2
New Formatting Rule dialog box, 153
numbers
numeric fields
grouping, 82
Values area, adding to, 348-349
O
objects
PivotLayout, 136
Off for Rows and Columns setting, 59
offline cubes, creating, 209-211
OLAP (online analytical processing)
calculations
managing, 220
offline cubes, creating, 209-211
what-if-analysis, executing, 220-222
One Click, 42
OneDrive
files, importing from, 198
saving to, 195
online analytical processing. See OLAP
operator precedence, 128
optimizing
options
Add This Data to the Data Model, 25
Analysis Services, 204
Convert, 16
Convert to Formulas, 212
Count Distinct, 224
Index, 74
Quick Analysis, 32
Repeat All Item Labels, 53, 357
Summarize Values By command, 64
Value Field Settings dialog box, 67
order
solve, modifying calculated items, 132
sort
controlling, 312
formatting, 90
See also sorting
overriding
Count calculations, 64
default sorting, 89
overwriting caches, 39
P
page breaks, adding subtotals, 315
Page Layout tab, 62
pages, adding sectors, 107
parent items, tracking percentages, 73
paths, adding, 246
percentages
totals, viewing, 70
tracking, 73
percentages, calculating parent items, 73
pictures. See images
pie charts, creating, 266
conditional formatting
fields
images, formatting as, 146
limitations of formatting, 141
locations, moving, 137
source data, applying cells as, 147-148
types of, selecting, 136
viewing, 137
Pivot Power Premium add-in, 47
advantages of, 11
analysis, 157
AutoFilter, 363
automating, refreshing, 353
caches, defining, 347
code, generating, 300
comparing, 361
customizing, 45
adding/removing subtotals, 64-67
applying functions, 64
configuring calculations, 67-75
naming fields, 51
replacing blanks with zeros, 49-50
restoring gridlines, 47
Tabular layout, 55
Data Models, formatting, 232-233
defining, 9
deleting, 146
external data sources, formatting, 168-174
frequency distributions, formatting, 366-367
GetPivotData, formatting, 381-391
hard values, modifying, 145
model fields, adding to, 348
moving, 43
naming, 36
Power Pivot add-in
formatting, 237
preparing data for reporting, 19-24
rank number fields, adding, 359-360
rearranging, 29
refreshing, 276
reports, sizing, 360
sharing, 193
linking Web workbooks, 196
with Power BI Desktop, 196-202
simultaneously, refreshing, 354
SQL Servers, formatting, 171-174
summaries, formatting, 355-356
tabs, exploding data sets to, 367-368
values, converting to, 55-57, 304-305
VBA (Visual Basic for Applications)
workbooks
PivotLayout objects, 136
PivotTable Fields list
searching, 26
viewing, 208
PivotTable Options dialog box, 209
PivotTable Styles gallery, 60
points, labeling (3D Maps), 266
populating
Power View images, 258
Position property, 298
Power BI Desktop
pivot tables, sharing, 196-202
Power Pivot add-in, 225
enabling, 234
files, linking, 236
pivot tables
formatting, 237
relationships, defining, 236
columns, formatting, 185
refreshing, 183
Power View, 245
calculations, modifying, 259
charts
copying, 256
filtering, 252
elements, starting, 252
Filters pane, navigating, 254-255
images, populating with, 258
overview of, 261
scatter charts, animating, 259-260
slicers, creating, 253
sorting, 249
tables, converting to charts, 250-251
tile boxes, filtering charts, 255
precalculations, 114
See also calculations
precedence, operators, 128
preparing data for reporting, 19-24
preventing
errors, 302
GetPivotData, 379
primary keys, 162
processes, ETL, 174
productivity, 11
Project Explorer, 291
properties
Caption, 322
ManualUpdate, 297
Name, 322
Position, 298
Properties window, 291
Q
Q&A, querying with, 200
quarters
filtering, 37
grouping, 79
queries
See also Power Query
Access (Microsoft), 169
Power Query, applying, 174-192
starting, 175
steps, managing, 182
with Q&A, 200
Preview pane, 187
Query Settings command, 181
Quick Analysis option, 32
R
ranges
converting, 163
multiple, 157
named, applying, 129
selecting, 23
updating, 40
rank
number fields, adding, 359-360
viewing, 71
rearranging. See moving
Recommended Pivot Table, navigating, 31-33
Record Macro dialog box, 276
recorders, macros, 292
records, counting, 322
recordsets, filtering, 332-334
references
cells, applying, 129
totals, 129
refreshing
caches, 15
pivot tables, 276
automatically, 353
simultaneously, 354
Power Query, 183
Region field, adding, 27
region maps (3D Maps), 266
regions, formatting reports, 334-338
relationships
Data Models
Power Pivot add-in, defining, 236
relocating. See moving
Repeat All Item Labels option, 53, 357
repeating groups, avoiding, 21
replacing blanks with zeros, 49-50
replicating. See copying
Report Connections dialog box, 35
reports
code, generating, 319
executive overviews, formatting, 329-332
formatting
filters, 31
viewing by categories, 307-319
locations, 25
macros, 275
See also macros
creating interfaces with form controls, 278-280
inserting scrollbars, 281
synchronizing pivot charts, 283-287
shell. See shell reports
sizing, 360
Top 10 filter, applying, 101
resizing elements, 251
restoring gridlines, 47
restrictions, applying, 368-371
Blank Rows setting, 57
data sources, expanding, 39
fields
filling empty cells left by, 356-358
suppressing subtotals, 313-314
grand totals, viewing, 58
Off for Rows and Columns setting, 59
rules
calculated fields, 129
calculated items, 131
New Formatting Rule dialog box, 153
running macros, 276
See also macros
running totals, tracking, 72
S
saving
to OneDrive, 195
workbooks, enabling macros, 290
scrollbars, inserting, 281
search boxes, filtering, 97-98
search filters, applying, 342-343
searching PivotTable Fields lists, 26
section headings, avoiding storing, 20
sectors
pages, adding to, 107
sorting, 91
Select Table dialog box, 170
selecting
colors, 63
data sets, 25
dates, grouping, 78
items, filtering, 105
pivot charts, 136
ranges, 23
separators, adding thousands, 48-49
sequences (manual), applying to sorting, 92-93
services, Analysis Services option, 204
sets, named, 225
shaping (data), 10
See also formatting
Share dialog box, 196
Share with People command, 196
sharing
dashboards, 202
pivot tables, 193
linking Web workbooks, 196
with Power BI Desktop, 196-202
videos (3D Map), 271
shortcuts
macros, 276
See also macros
pivot tables, starting, 25
Recommended Pivot Table feature, 31-33
shortening VBA code, 295
Show Detail dialog box, 53
Show Report Filter Pages dialog box, 106
Show Values As tab, 67-69, 327-329
sizing
reports, 360
slicers
connecting, 35
How Many Customers to Show, 194
inserting, 33
multiple pivot tables, driving, 110-112
Power View, creating, 253
timelines
inserting, 143
Solve Order dialog box, 132
solve order, modifying calculated items, 132
Sort (Sector) dialog box, 91
AutoSort, applying, 312
items
in unique orders, 355
specifying, 91
manual sequences, applying, 92-93
Power View, 249
sectors, 91
source data, applying cells as pivot charts, 147-148
spans, filtering, 101
specifying items, sorting, 91
SQL Servers, formatting, 171-174
starting
classic layouts, 30
elements (Power View), 252
pivot tables, 24
queries, 175
Recommended Pivot Tables, 31
tables, elements, 249
statements, End With, 295
stock charts, 141
storing
macros, 276
section headings, avoiding, 20
styles
gridlines, restoring, 47
layouts, 52
See also layouts
PivotTable Styles gallery, 60
subtotals, 299
formatting, 315
SUMIFS function, 239
calculations, modifying, 63-64
code, generating, 305
pivot tables, formatting, 355-356
Summarize Values By command, 64
summing totals, 298
suppressing subtotals, 64-67, 313-314
SWITCH function, 240
synchronizing pivot charts, 283-287
T
tables
See also pivot tables
comparing, 361
Data Model
deleting, 165
formatting, 166
elements, starting, 249
pivot. See pivot tables
Power Query, formatting, 186-188
Power View, converting to charts, 250-251
relationships, formatting, 346-347
Transactions, 158
tabs
pivot tables, exploding data sets to, 367-368
text
Power Pivot add-in, importing, 235-236
TEXT function, 239
thousands separators, adding, 48-49
tile boxes (Power View), 255
time
data over, animating 3D Map, 269
filtering, 37
grouping, 79
intelligence, applying, 242-243
scatter charts, animating, 259-260
timelines
slicers
inserting, 143
titles, charts, 141
tools
pivot tables. See pivot tables
VBA (Visual Basic for Applications), 291-292
Top 10 filter, applying, 101-102
See also grand totals; subtotals
percentages, viewing, 70
references, 129
running, tracking, 72
summing, 298
tours, building 3D Maps, 270-271
tracking
Index function, 74
percentages, 73
running totals, 72
transactional databases, 203
Transactions table, 158
trend lines, 141
troubleshooting
caches, overwriting, 39
Excel Help, 292
formulas, 377
GetPivotData, avoiding, 376-380
layouts, 33
Trust Center, enabling VBA, 289-290
turning off GetPivotData, 379-380
of pivot charts, selecting, 136
U
undocking fields, 87
undoing automatic grouping, 78
unique orders, sorting items in, 355
unlimited rows, importing Data Models, 224
updating
data sources, modifying, 39-40
ranges, 40
V
Value Field Settings dialog box, 48, 67, 233
values
fields, modifying calculations, 67-75
pivot tables, converting to, 55-57, 304-305
slicers, selecting, 34
blanks, replacing with zeros, 49-50
fields, naming, 51
numeric fields, adding to, 348-349
Values columns, filtering, 99-101
variables, applying objects, 294-295
VBA (Visual Basic for Applications), 289
code
applying object variables, 294-295
editing, 282
shortening, 295
conceptual filters, applying, 339-340
Data Models, applying, 345-351
macro recorders, 292
pivot tables
region reports, formatting, 334-338
search filters, applying, 342-343
Visual Basic Editor, 291
workbooks, saving to enable macros, 290
versions
checking, 16
VBA (Visual Basic for Applications), 295-296
videos, sharing, 271
viewing
fields, modifying, 73
grand totals, 58
percentages of totals, 70
pivot charts, 137
PivotTable Fields list, 208
rank, 71
running totals, 72
views, formatting, 9
Visual Basic for Applications. See VBA
functions, 239
limitations of, 230
tables, merging without, 223
W
web pages, sharing pivot tables, 193-196
weeks, grouping dates, 81
what-if-analysis, executing OLAP, 220-222
windows
Code, 291
Power View, 248
See also Power View
Properties, 291
Windows Authentication, 172
wizards
Create Cube File, 210
Data Connection Wizard, 172, 204
Workbook Connections dialog box, 164
Workbook Queries pane, 183
workbooks
macros, assigning, 279
pivot tables
saving, enabling macros, 290
web pages, designing as, 193-196
Web, linking, 196
worksheets
Data Models, formatting, 158-162
functions, applying, 129
writing VBA code
applying object variables, 294-295
shortening, 295
X
x-axis, 140
XY (scatter) charts, 141
Y
y-axis, 140
years
filtering, 37