F

F1–F12 keys, 927–946

 Fabrikam.xlsx file, 411

face values of securities, 546

FACT function, 962

FACTDOUBLE function, 962

FALSE function, 962

FALSE results in debugging, 864

FALSE value

as argument, 492

conditional test formulas, 478

logical functions and, 508

sorting and, 682

faxing, 800

FDIST function, 962

feasible solutions in Solver add-in, 611

federal regulations, 821

fields. See also columns (worksheet)

adding to query tables, 767

aggregate, 776–777

calculated, 734–738, 777

centering labels on, 723

comparing in queries, 774

database, 122

expanding or collapsing, 724

filtering, 716, 728–731, 774

grouping in PivotTables, 738–741

hiding in queries, 768

inner-field subtotaling, 727

listing for PivotTables, 716

merging labels, 723

naming in PivotTables, 724

percentages-of-total calculations and, 734

rearranging in PivotTables, 718–721

renaming in Query, 768

selecting in Query, 761

sorting in PivotTables, 727–728

subtotaling, 725–727

in Word, 896

file extensions, 53, 56, 70, 71, 72

File Format Compatibility Pack, 56

file formats

ACCDB (Access), 72

ACCDE (Access), 72

Compatibility mode and tables, 670

CSV (Comma Separated), 71, 72, 750

default, 54, 56

DIF (Data Interchange), 72

DQY (Query), 763

HTM (Web Page), 70

HTML (Web Page), 70, 784

IQY (Query), 782

Macintosh, 72

MDB (Access), 749–750

MDE (Access), 749–750

MHT (Single File Web Page), 70

MHTML (Single File Web Page), 70

MS-DOS, 72

natural language formulas in, 456

new and changed features, 17

ODC (Office Data Connection), 745, 763, 764

pasted data, 883

PDF (Portable Document Format), 72

PRN (Formatted Text), 71

recalculations and, 461

saving, 51–52, 56

shared workbooks and, 792

specifying, 57–58

spreadsheetML, 57

supported, 17

SYLK (Symbolic Link), 72

templates, 56–57

text, 71–72

TXT (Tab Delimited), 71, 72

Unicode Text, 71

using older PivotTable features, 721

VBA-enabled workbooks, 57

WAV (sound clips), 363

workbooks, 56–57

XLAM (Excel Add-In), 57

XLK (Excel Backup), 58

XLS (Excel Workbook, 97–2003), 57

XLSB (Excel Binary Workbook), 56

XLSM (Excel Macro-Enabled Workbook), 56, 829–830

XLSX (Excel Workbook, 2007), 56

XLTX (Excel Template), 56, 57

XLTXM (Excel Macro-Enabled Template), 57

XML-based, 57

XPS (XML Paper Specification), 72

File menu commands and options, 907–909

file paths, 410

file sharing. See collaboration

file size, 882, 896

File Transfer Protocol (FTP), 789

files. See documents and files

filling cells. See data series

fills

applying, 376–377

AutoFill feature, 217

in charts, 620, 637–640

colors, 329–334

effects, 331, 385

examples of, 398–401

pictures, 640–641

Shape Styles feature, 631

shapes, 371

textures, 640–641

filter controls on tables, 616

filtering

Advanced, 689–698

AutoFilter options, 9, 15, 16

complex criteria, 687–688

computed criteria, 695–697

criteria ranges, 689–690

Custom Filter options, 688–689

dates, 687

in Excel Web Access, 821

extracting filtered rows, 697–698

keyboard shortcuts, 939

mail merge records, 903

maximum number of items, 9

multiple criteria on same column, 695

new and changed features, 9, 15

OR and AND example, 694

overview, 684–689

percentages-of-total calculations and, 734

PivotChart data, 743

PivotTable fields, 716, 728–731

query result sets, 769–770, 774

records in Query, 761–762, 764

refining filters, 694

removing filters, 686, 762

shared workbook settings, 794

Simple and Advanced, 684

table filter controls, 616

tables, 667

three ORs on a column, 693

two columns joined by OR, 690–693

Unique Records Only option, 698–699

finance rate, 536

financial functions

arguments, 535–536

depreciation, 541–543

Euro Currency Tools add-in, 549–551

investments, 535–541

securities, 544–549

Find And Replace dialog box, 221–226

Find Format dialog box, 223–224

FIND function, 505–506, 962

Find results, 9

FINDB function, 962

finding. See searching

FINV function, 962

FISHER function, 963

FISHERINV function, 963

fitting pages on paper, 405, 421

Fixed Decimal mode, 127

FIXED function, 963

fixed-width text files, 750, 752–753

flashing vertical bar (insertion point), 136

flipping

charts, 618–619, 645

SmartArt, 361

floating workbooks, 25

FLOOR function, 501, 963

folders

AppData folder, 48

enabling macros for files in, 829

hidden, displaying, 48

hyperlinks to, 874

saving to default, 51

Templates folder, 49–51

Web folders, 789

XLStart folder, 65–66, 845

fonts

applying, 275

automatic color, 325

chart text, 629, 641–642

formatting, 323–325

formatting in graphics, 371–373

headers or footers, 412

pasted tables in Word, 891–893, 895

standard display, changing, 100

switching, 707

table styles, 709

themes, 275, 706

footers

default styles, 409

margins and, 407

Page Layout view, 406

setting up for pages, 408–412

For . . . Next structures, 839

For Each statements, 867

FORECAST function, 564–565, 963

foreshortening in drawings, 380–381

Format As Table feature, 269

Format As Table palette, 268–273

Format menu commands and options, 915

Format Painter button, 274–275

formatted text, pasting, 893, 894, 895

Formatted Text (PRN) file format, 71

formatting. See also cell styles

applying with MOD function, 499

array cells, 469

AutoFit feature, 335

basic rules, 266–267

borders, 325–329, 939

cell size, 334–339

cells, 266–267, 280–281, 346

change tracking and, 796

chart areas, 637–641

chart data series, 653

chart elements, 634

chart lines and borders, 635–637

chart text, 629, 641–642

colors, 329–334

column width, 334–335

conditional. See conditional formatting

currency, 299–301, 549–551

custom number formats, 307–315

data as tables, 268–273

data in PivotTables, 722

dates and times, 526–530

default, 298

embedded objects, 384–386

examples of, 398–401

fonts, 323–325

Format Painter button, 274–275

formulas, 454

General format, 298

graphics, 333–334, 368–386, 381–382

grouped objects, 386

hiding rows and columns, 337

losing in pasted tables, 893

merging cells, 337–339

new and changed features, 6

numbers, 299–315. See also numeric formats

overview, 266

pasted tables in Word, 891–893

pasting, 187–188

pasting formats, 185

patterns, 329–334

PivotTables, 723–724

previewing, 326, 375

ranges, 287

recovering, 67

row height, 334–335, 336

searching and replacing, 223–224

shapes, 346, 374–384

shortcuts, 940

special characters, 297–298

storing in templates, 340–341

tables, 269–275, 705–709

text, 297, 306

text in graphics, 371–373, 382–384

themes and, 275–278

toolbar, 924–925

unmerging cells, 337–339

while typing, 297–298

WordArt, 356–358

 Formatting Numbers.xlsx file, 312

Formatting toolbar commands and options, 924–925

Formula AutoComplete feature, 440–441, 449, 459–460, 490

formula bar, 96, 207–208

formulas

3-D references, 453–454

arguments, 243

arrays, 10, 468–473

auditing, 243–245

calculated columns, 455

calculated fields and items, 737–738

calculating portions of, 463–464

cell references in, 428–435

as chart titles, 622

circular references, 464–466

conditional formatting, 294–295

conditional tests, 478–485

converting external references to, 476

converting imported data to, accidentally, 753

copying, 476–477

creating, 12

current row in, 702–703

custom functions in. See custom functions

data tables, 583–588

dates and times in, 530–534

displaying, 43, 98–100, 353

editing, 435–437

EMBED and, 882

error values, 437–438

Euro Currency Tools add-in, 549–551

evaluating, 243–245

external references, 476, 885

finding, 131–132

formatting, 187–188, 454

Formula AutoComplete feature, 11–12, 440–441, 449, 459–460, 490

formula bar, 96, 207–208

functions in, 438–441

informational, 510–512

logical, 507–510

lookup, 512–520

mathematical, 497–502

reference, 512–520

rounding, 500–501

statistical, 553–555

text, 502–507

Goal Seek command, 599–601

green triangles, 201

hyperlinks and, 880

implicit and explicit intersections, 452–453

inserted cells and, 435

iterative calculations, 466

keyboard shortcuts, 937, 944

line breaks in, 454

linking workbooks, 473–477

long, displaying, 43

multiple, in data tables, 585–586

names of cells and ranges, 441–452

naming, 448

natural language, 17, 456

nesting, 9, 243

new and changed features, 9, 10–12, 17

numeric text in, 187–188, 436

operator precedence, 427–428

optional arguments and, 853–854

PivotTables and, 715

placeholder cells, 584

precision in numeric values, 467

recalculations, 460–467

reference syntax, 435

referencing parts of tables, 703–705

referencing table rows, 701–702

replacing with meaningful references, 699

replicating in tables, 667, 672

results, 353

in scenarios, 592

searching and replacing, 222

size of, 9, 43

solve order, 737

sorting cells containing, 680–682

structured references in, 454–461, 701–702

table elements and, 10, 454–461, 671, 699–705

text values in, 436–437

total rows in tables, 675

transposing cells, 190

troubleshooting, 140, 201

types of

depreciation, 541–543

exponential regression, 566–567

investments, 535–541

linear regression, 559–566

securities, 544–549

what-if analysis, 583–588

watching, 245

worksheet-qualified, 622, 623

Formulas tab commands and options, 911, 912, 913–914, 916, 917, 923

four-part formats, 312

Fourier transformations, 553

fractions

data entry, 139

displayed and stored values, 467

formatting, 304–305

fractional security pricing, 545

removing, in Solver add-in, 605–606

of years, 534

freeform shapes, 349–351

frequency

coupon payments, 544

frequency distribution tables, 572–573, 963

histograms, 570, 571

FREQUENCY function, 572–573, 963

FTEST function, 963

FTP (File Transfer Protocol), 789, 874, 876

Full Control access, 805

Function Arguments dialog box, 489, 493

function keys, 927–930

Function Library feature, 494, 856–857

Function Reference (online Help), 487, 488

function ScreenTips, 100

Function statement, 847, 851

functions

accessing in VBA modules, 851

arguments, 9, 438, 489, 491, 493

categories, 10

cell references in, 495

complex filtering, 687–688

custom. See custom functions

cycling through, in Query, 775–776

dates and times in, 530–534

defined, 487

displaying list of, 137, 439–440

entering, 137, 494

Euro Currency Tools add-in, 549–551

Formula AutoComplete feature, 440–441

Function Library, 494, 856–857

included in book, 487

inserting, 440, 492–495

length of, 491

limits on, 10, 491

list of, 947–990

named references in, 495

new and changed features, 9, 10

online Help for, 487, 488

overview, 438–441

in PivotTables, 731–739

random numbers, 574–579

ScreenTips, 100, 489–490

suggesting, 492–493

syntax of, 490–492

total rows in tables, 674–675

types of

depreciation, 541–543

exponential regression, 566–567

information-related, 510–512

investments, 535–541

linear regression, 559–566

logical, 507–510

lookup, 512–520

mathematical, 497–502

reference, 512–520

rounding, 500–501

securities, 544–549

statistical, 553–555

text-related, 502–507

volatile, 532, 575

future values, 535, 536, 537–538

FV function, 537–538, 963

FVSCHEDULE function, 963