Index
data validation
cell references, 619–621
cr iter ia, specif y ing, 614–617
drop-down lists, creating, 617–618
formulas for, 619
accepting dates by day of week, 623
accepting larger value than previous cell, 621
accepting non-duplicate entries only, 622
accepting text beginning with specific character,
623
accepting text only, 621
accepting value not exceeding a total, 623
creating dependent lists, 623–625
over v iew, 613–614
without restricting data entry, 625–626
Data Validation dialog box, 614–615
Error Alert tab, 615, 626
Input Message tab, 615
Settings tab, 614, 615–616, 619, 621
Data Validation option, Go To Special dialog box, 79
databases
Azure databases, connecting to Power Query, 855
back-end databases, 911
file formats, 580–581
importing database files, 582–583
relational databases
connecting to Power Query, 855
loading data into Power Pivot data model,
795–800
Date category axis, 506
Date data type, 1025
DATE function
converting dates to Julian format, 283–284
returning last day of month, 285
Date number format, 50
date(s)
age, calculating, 276–277
business days excluding holidays, listing, 278–280
calendar quarters, calculating, 286
current, returning, 275–276
custom number formatting, 545–546
entering, 270–271
extracting parts of, 280–281
fiscal month, returning, 288
fiscal quarter, calculating, 287–288
formatting, 273
highlighting cells based on, 393–398
Julian format, converting to, 282–284
last day of month, returning, 285–286
last weekday of month, calculating, 290
of Nth weekday, calculating, 289
number of days between, calculating, 277
number of years and months between, calculating, 282
percent of year completed/remaining, calculating, 284
problems with, 273–275
regional settings and, 34
searching for, 271
serial numbers, 269–270
workdays between, calculating, 277–278
date-stamping worksheets, 44
DATEDIF function
calculating age, 276–277
calculating days between two dates, 277
calculating number of years and months between
dates, 282
DAX (Data Analysis Expression) functions
building DAX-driven calculated columns, 824–827
identifying for calculated columns, 1424
nesting, 829
referencing fields from other tables, 827–829
DAY function, 280, 281
dBase files, 581
Deactivate workbook event, 1000
Deactivate worksheet event, 1003
debugging custom functions, 958
DEC2BIN function, 597
DEC2OCT function, 597
Decimal data type, 1025
decimals
converting hours/minutes/seconds to a time, 294
converting to other number bases, 597–598
entering decimal points automatically, 38–39
precision problems, 431–432
Decrease Decimal button, 25, 538
default
column width, 68
font, 120
print settings, 173–174
Quick Access toolbar tools, 196
row height, 68
templates, 168–170
worksheet names, 56
Define Name command, 94–95, 233
Defined Names
Create from Selection, 95, 235
Define Name, 94–95
Name Manager, 97
Delete Columns option, Protect Sheet dialog box, 772