Index

Numbers

2D maps, 224, 227

3D maps, creating, 422–428, 430

Symbols

‘ (single quotes), using with workbook names, 179

- operator, 82

- operator, 82

/ operator, 82

+ operator, 82

#### error code, 95

% operator, 82

& operator, 82

* operator, 82

^ operator, 82

A

absolute references, 79, 83

actions, undoing and redoing, 59

active cell, 47. See also cells

addition operator, 82

AGGREGATE function, 144, 146–148

Alt key. See keyboard shortcuts

analyzing data. See also business intelligence analysis; data; Data Analysis; Quick Analysis Lens

data tables, 196–200

descriptive statistics, 208–209

Goal Seek, 200–202

Quick Analysis Lens, 190–191

Solver, 202–208

app window

arranging workbook windows, 26–27

customizing, 38

Quick Access toolbar, 28–30

ribbon, 30–36

zooming in on worksheets, 24–25

array formulas, 92–93, 101–102. See also formulas

ascending order, sorting in, 394

authenticating workbooks, 366–368, 381

AutoCalculate, 143–144, 147–148

AutoComplete, 41–42

AutoFill, 42–43

automatic calculation, 101. See also Calculation Options

AutoSum, using to create measures, 417

AVERAGE function, 74

AVERAGEIF and AVERAGEIFS functions, 85, 88–89

averages, finding, 144

B

background color, changing for cells, 107. See also color palettes

backgrounds, removing from images, 129, 131

Backstage view

displaying, 7

Open page, 7

using, 300–301

Bing search engine, 58

borders, adding around cells, 105, 108

box-and-whisker charts, 226, 228

business intelligence analysis. See also analyzing data

data model, 386–388, 407

performing, 223–228, 252

Power Pivot, 392–397, 407–408

Power Query, 401–406, 408

relationships between tables, 388–392, 407

timelines, 397–400, 408

button form control, 331

buttons

adding to Quick Access toolbar, 28–30

adding to worksheets, 334

changing appearance of, 324

C

calculated columns, adding, 396, 417. See also columns

Calculation Options, 89–91. See also automatic calculation; errors

cell colors, sorting by, 160

cell groups, moving data in, 18

cell ranges

converting tables to, 64

copying, 51

cutting and moving, 51

displaying, 52

pasting, 52

cell references

changing, 79

in other workbooks, 178

retaining, 81

cells. See also active cell; header cells; ranges of cells

adding borders to, 105

adding to selections, 77

changing attributes, 106

changing shading of, 105

copying, 43

deleting formatting, 108–109

editing contents, 55

entering values in, 43–44

formatting, 104–109, 133

locking and unlocking, 360, 362

merging and unmerging, 22–24, 38

moving in worksheets, 21–22

selecting, 50

centering printed items, 308

character size, changing in cells, 106

chart types

box-and-whisker, 226, 228

dual-axis, 238

funnel, 223, 227

line, 236

Pareto, 225, 228, 238

sunburst, 227–228

treemap, 226, 228

waterfall, 224, 228

charts. See also PivotCharts; trendlines

business intelligence analysis, 223–228, 252

color palettes, 229

combining, 238, 253

creating, 216–223, 252

customizing appearance, 229–235, 253

inserting, 216–217

keyboard shortcut, 216

live preview, 216–217

moving, 220, 222–223

pasting into documents, 349–350, 352

plotting, 221–222

printing, 311–312, 314

repositioning, 222

resizing, 219

types, 224

x- and y-axes, 218–219

check boxes, 331–332, 334

closing workbooks, 8, 12

col_index_num argument, 168

collaboration, 378–379

color palettes, choosing for charts, 229. See also background color

color scales, 124–125, 127

column width, changing, 19

columns. See also calculated columns

adding formulas to, 79

adding to tables, 63

deleting, hiding, and unhiding, 18, 20–21, 397

filtering values in, 394–395

inserting, 17, 19–20

primary key, 167

referring to in formulas, 81

renaming, 397

repeating on printed pages, 310

resizing, 16–17

selecting, 51

sorting data in, 394

using with Power Query, 404–406

combo boxes, 328–329, 333

command buttons, 330

comments

managing, 355–358, 380

using with scenarios, 192

concatenation operator, 82

conditional formats, 122–128. See also formatting

consolidating data, 183–185, 187–188

constraints, setting with Solver, 204, 206–208

copying

cell ranges, 51

cells, 43

formatting between cells, 108

formulas, 81, 83

and pasting formulas, 77–78

workbooks, 14–15

worksheets, 12–13

COUNT and COUNTIF functions, 74, 85, 87–88, 144–146

Ctrl key. See keyboard shortcuts

custom lists, using to sort data, 161–167

cutting cell ranges, 51

Cycle diagrams, 242

D

data. See also analyzing data; external data

changing appearance of, 121–128, 134

correcting and expanding, 67

entering and revising, 40–44, 66

finding and replacing, 57–61, 67

finding trends, 235–238

importing from text files, 281–284

limiting appearance on screen, 138–142, 153

linking, 178–182

managing via Flash Fill, 44–47

manipulating, 153

moving, 47–52, 67

naming groups of, 70–72, 100

organizing into levels, 163–167, 171–172

plotting, 218, 221–222

selecting list rows, 142

summarizing, 100–101, 143–149

viewing by using timelines, 397–400

Data Analysis. See also analyzing data

add-ins, 386

button, 209

data bars, 124, 127

data connections, creating, 401

data entry

canceling, 40

techniques, 40–43

data sets

consolidating, 183–185

defining alternatives, 191–196, 211–212

finding unique values in, 149

data sources

connecting to, 401

selecting, 218

data tables, using for analysis, 196–200, 212

Data View, 390

databases, connecting to, 401

date and time, displaying, 80

days, filling, 43

deleting

3D maps, 428

alternative data sets, 195

backgrounds from images, 129, 131

cell formatting, 104, 108–109

cell styles, 112

columns and rows, 18, 20, 397

comments, 357

conditional formats, 128

custom groups in lists, 167

equations, 251

form controls, 335

hyperlinks, 349

images, 131

KPIs (key performance indicators), 421

macros, 323

measures, 418

personally identifiable info, 365–366

print areas, 309

queries in Power Query, 406

relationships, 392

ribbon elements, 34

rows, 20

shapes, 244, 250

sorting rules, 161

subtotals, 166–167

themes, 116

watches, 98

descending order, sorting in, 394

descriptive statistics, using to analyze data, 208–209, 214

Developer tab, adding to ribbon, 332

Diagram View, 388, 390

diagrams, creating using SmartArt, 241–245, 254. See also SmartArt

dictionary, adding words to, 60

digital certificates and signatures, 367–368

#DIV/0! error code, 95

division operator, 82

Document Inspector, 364–366

document properties, defining values, 10–11

dual-axis charts, creating, 238

E

electronic submission, saving workbooks for, 354–355

embedding files, 342–344

encrypting workbooks, 359

equations

creating and managing, 247–251, 254

deleting, 251

error message, displaying, 89

errors. See also Calculation Options

finding and correcting, 94–98, 102

printing, 307

Evolutionary option, using with Solver, 204

Excel 2019

customizing app window, 38

features, 4–6

Excel Mobile Apps, features, 4–5

Excel Online

features, 4–5

and OneDrive, 374–379, 382

Excel table styles, applying, 113–117. See also tables

Excel versions, 174–175

exponentiation operator, 82

exporting XML data, 371–374, 382

external data, importing, 401–406. See also data

F

F keys. See keyboard shortcuts

file formats, specifying, 7, 10, 174–175

files

creating data connections to, 401

embedding, 342–344

fill colors, sorting by, 157

FillSeries, 42–43. See also Flash Fill

filtered rows, summarizing data in, 143–148. See also rows

filtering data, 138–142

finding and replacing data, 57–61, 67. See also looking up information

Flash Fill, 43–47, 66. See also FillSeries

font

appearance, changing, 108

font color, changing, 105, 107

font default, changing, 109

footers

adding to printed pages, 294–299, 313

viewing, 294

forecast worksheets, creating, 410–415, 429

forecasting future data values, 236

form controls. See also macros

check boxes, 331–332, 334

combo boxes, 328–329, 333

command buttons, 330

creating groups of, 335

deleting, 335

editing text of, 335

inserting, 337–338

list boxes, 328–329, 332–333

option buttons, 331–332, 334–335

parameters, 329

resizing, 335

spin buttons, 330, 333–334

using, 328–332

formats, finding and replacing, 56

formatting. See also conditional formats

applying and clearing, 17

cells, 104–109, 133

displaying, 106

filling, 43

numbers, 117–121

PivotTables, 277–280

Quick Analysis Lens, 191

shape text, 243, 249

shapes, 245

formula precedents, identifying, 97

formulas. See also array formulas; values

adding to columns, 79

AGGREGATE, 148

copying, 81, 83

copying and pasting, 77–78

copying between cells, 108

creating, 75, 80, 100

date and time, 80

entering, 73

evaluating, 98

hiding, 360, 363

moving, 83

named ranges, 81

referring to columns, 81

SUBTOTAL, 148

updating payments, 80

functions

AGGREGATE, 144, 146–147

AVERAGE, 74

AVERAGEIF and AVERAGEIFS, 85–86, 88–89

COUNT, 74, 144–146

COUNT and COUNTIF, 85–88

HLOOKUP, 170

IF, 84

IFERROR, 85–86, 89

LARGE, 146

MAX, 74, 145–146

MEDIAN, 146

MIN, 74, 145–146

MODE.SNGL, 146

PERCENTILE, 147

PRODUCT, 145–146

QUARTILE, 147

RAND, 142–143

SMALL, 147

STDEV, 145–146

SUBTOTAL, 144–145, 147

SUM, 74, 145–146

SUMIF and SUMIFS, 85–86

VAR, 145–146

VLOOKUP, 168–170

funnel charts, 223, 227

fv argument, 74

G

Goal Seek, 200–202, 212

graphics, adding to headers or footers, 297–298. See also SmartArt

GRG Nonlinear option, using with Solver, 204

groups, creating in lists, 166

H

header cells, including in data ranges, 145. See also cells

headers

adding to printed pages, 294–299, 313

viewing, 294

hidden rows, summarizing data in, 143–148. See also rows

hiding and unhiding

columns and rows, 18, 20–21

formulas, 360, 363

ribbon, 32–33

rows, 20–21

worksheets, 15

Hierarchy diagrams, 242

histograms, 225, 228

HLOOKUP function, 170

.htm and .html extensions, 369

hyperlinks

creating, 344–349, 351–352

editing and deleting, 349

I

icon sets, 125, 127

ideas, relating, 242

IF function, 84, 86

IFERROR function, 85–86, 89

images, adding to worksheets, 128–132, 134. See also SmartArt

importing

data from text files, 281–284

and exporting XML data, 371–374, 382

external data, 401–406

Insert Function, using to create formulas, 80, 87

Insert Options, applying, 17

inserting

columns, 19–20

rows, 19–20

integer programming, 204, 207

Intelligent Services, 58

iterative calculation options, 89–91, 101

K

keyboard shortcuts, 431–438

Backstage view, 300

cells for selections, 77

charts, 216, 220

closing workbooks, 8

Copy, 51

creating workbooks, 9

Cut, 51

data entry, 41–42

executing instructions, 320

Find tab, 53

hiding and unhiding ribbon, 32–33

navigating references, 83

Open page of Backstage view, 7

Paste, 52

printing, 306–307

Save As dialog box, 7

saving changes, 323

saving workbooks, 7, 365

selecting worksheets, 21

selections, 77

spell-checking, 57

undoing actions, 49, 57

Visual Basic Editor, 320

KPIs (key performance indicators), 418–422, 430

L

landscape mode, 301–302

languages, translating words to, 58

LARGE function, 146

levels, organizing data into, 163–167

line charts, 236

linear forecasts, creating, 413

linking

data, 178–182, 187

Office documents, 341–342

list boxes, 328–329, 332–333

List diagrams, 242

list rows, selecting, 142–143

lists, using to sort data, 161–167

locking and unlocking cells, 360, 362

logos, adding to worksheets, 129

looking up information, 167–170, 172. See also finding and replacing data

lookup_value argument, 168

M

macro button, adding to Quick Access toolbar, 325–326

macro-enabled templates, saving, 177

macros. See also form controls

creating and modifying, 322–323, 337

deleting, 323

editing, 323

Enable Content button, 317

enabling, 337

examining, 321, 337

executing instructions, 320

exiting, 321

moving through, 320–321

recording, 322–323

running, 321, 323–328, 337–338

security levels, 316–319

viewing and editing, 319

mailto hyperlink, creating, 348–349

margins, specifying for printing, 300

mathematical equations, 247–251, 254

Matrix diagrams, 242

MAX function, 74, 145–146

measures, defining and managing, 416–418, 429. See also Power Pivot

MEDIAN function, 146

merging cells, 22–24

Microsoft Azure, connecting to, 401

MIN function, 74, 84, 145–146

mobile apps, 5

MODE.SNGL function, 146

moving

cell ranges, 50

charts, 220, 222–223

data, 67

data in workbooks, 47–52

formulas, 83

shapes, 247–248

multiplication operator, 82

N

#NAME? error code, 95

named ranges, referring to, 81

naming

groups of data, 100

worksheets, 12, 14

negation operator, 82

NOW() formula, updating, 80

nper argument, 74

numbers, formatting, 117–121, 134

O

Office documents, including, 340–344, 351

Office themes, applying, 113–117, 133

Office.com diagrams, 242

OneDrive and Excel Online, 374–379, 382

operators and precedence, 82–83

option buttons, 331–332, 334–335

Options dialog box, Calculation Options, 90

P

page breaks, changing, 303–304

page printing order, changing, 304–305

Page Setup dialog box, 300

Pareto charts, 225, 228, 238

passwords, using, 358, 361–364

Paste gallery, 48

Paste Live Preview, 48

Paste Options, 49, 349–350

Paste Special dialog box, 50

pasting cell ranges, 52

payments, updating, 80

PDF files, saving workbooks as, 354–355

percentage operator, 82

PERCENTILE function, 147–148

Picture diagrams, 242

PivotCharts, using, 284–286, 290. See also charts

PivotTables. See also tables

changing data display, 274

creating, 258–259

creating dynamic charts, 284–286

creating from external data, 280–284

creating from Power Pivot data, 397

cross-tabular format, 261

custom styles, 278

deferring updates, 263

editing, 273–276

fields, 260, 263

filtering, showing, and hiding data, 264–273

formatting, 277–280

organizing data, 261

slicers, 269–270, 272

using, 256–263, 275–280, 288–289

using KPIs in, 421

plotting data in charts, 218, 221–222

portrait mode, 301–302

Power Pivot, using to analyze data, 392–397, 407–408. See also measures

Power Query, using, 282–283, 401–406, 408

practice files

analyzing data sets, 189

business intelligence analysis, 385

calculations on data, 69

charts and graphics, 215

collaboration, 353

data and tables, 39

data management, 137

data sources, 173

forecasts and visualizations, 409

macros, 315

Microsoft Office apps, 339

PivotTables and PivotCharts, 255

printing, 293

reordering data, 155

summarizing data, 155

workbook appearance, 103

workbook setup, 3

precedence and operators, 82–83

previewing data, 48

primary key column, 167

print area, defining and removing, 309

printed items, centering, 308

printed material, positioning, 309

printed pages

adding headers and footers, 294–299, 313

repeating rows and columns on, 310–311

viewing, 301

printing

charts, 311–312, 314

errors, 307

parts of worksheets, 308–311, 314

preparing worksheets for, 299–305, 313

specific pages, 309

worksheets, 305–307, 314

Process diagrams, 242

PRODUCT function, 145–146

properties

assigning, 8

customizing, 11

protecting workbooks and sheets, 358–364, 380–381

pv argument, 74

Pyramid diagrams, 242

Q

QUARTILE function, 147–148

queries, creating with Power Query, 403–406

Quick Access toolbar

adding buttons, 28–30

adding macro button, 325–326

Quick Analysis Lens, 190–191, 211, 216. See also analyzing data

R

RAND function, 142–143

range_lookup argument, 168–169

ranges of cells, finding valid sets of values, 150–152. See also cells

rate argument, 74

redoing actions, 59

#REF! error code, 95, 179–180

Relationship diagrams, 242

relationships between tables, 389–391

relative references, 77–79, 83

renaming

columns, 397

ribbon elements, 34

worksheets, 14

replacing values, 55–56

resizing

charts, 219

form controls, 335

images, 130

shapes, 246, 248

ribbon, customizing, 30–36

rotating, shapes, 249

row height, changing, 18

rows. See also filtered rows; hidden rows; Total row

adding to tables, 63

deleting, hiding, and unhiding, 18, 20–21

inserting, 17, 19–20

repeating on printed pages, 310–311

resizing, 16–17

selecting, 51

sorting, 157

S

Save As dialog box, opening, 7, 9

saving

changes to macros, 323

charts as templates, 234

macro-enabled templates, 177

as PDF and XPS files, 354–355

workbooks, 7, 9, 174, 365

workbooks as templates, 177

workbooks for electronic distribution, 354–355, 380

workbooks for web, 369–370, 381–382

Scenario Manager, 192–193, 195

Search box, 54

search filter, 139–141

security levels, setting for apps, 318

selecting

cells and ranges, 50

columns, 51

data sources, 218

rows, 51

worksheets, 21

selection filter, creating, 140

selections, expanding, 77

series

extending, 43

filling, 43

shading cells, 105

shape text, formatting, 243, 249. See also text

shapes

adding to worksheets, 248

aligning, 250

creating and managing, 243, 245–247, 254

deleting, 244, 250

formatting, 245

moving, 247–248

reordering, 250

resizing, 246, 248

rotating, 249

sheets. See worksheets

Shift key. See keyboard shortcuts

Simplex LP option, using with Solver, 204

single quotes (‘), using with workbook names, 179

slicers, using with PivotTables, 269–270, 272

SMALL function, 147–148

Smart Lookup, 58, 60

SmartArt, using to create diagrams, 241–245, 254. See also diagrams; graphics; images

Solver, 202–208, 213–214

sorting data, 156–163, 171, 394

sources. See data sources

sparklines, using to summarize data, 239–241, 253

spelling, checking, 57, 59

spin buttons, 330, 333–334

Spreadsheet Solutions tab, 176

Start screen, 7

statistics. See descriptive statistics

STDEV function, 145–146

styles, defining, 109–112, 133. See also table styles

SUBTOTAL function, 144–145, 147–148

subtotals

applying, 164

removing, 166

subtraction operator, 82

SUM function, 74, 76, 145–146

SUMIF and SUMIFS functions, 85

summarizing

data, 100–101, 143–148

data using sparklines, 239–241

data with charts, 253

values, 147

sunburst charts, 227–228

T

tab color, changing for worksheets, 16

Table AutoExpansion, stopping, 62

table styles, applying, 116–117, 133. See also styles

table_array argument, 168

tables. See also Excel table styles; PivotTables

adding using Quick Analysis Lens, 191

converting to cell ranges, 64

defining, 61–64, 68

defining relationships between, 388–392

renaming, 64

resizing, 62

Total row, 62–63

targets

choosing for hyperlinks, 345

displaying for hyperlinks, 349

templates. See also workbooks

saving charts as, 234

using, 174–177

text. See also shape text

adding to number formats, 120–121

adding to shapes, 246, 249

editing in SmartArt, 243

text files, importing data from, 281

themes, applying, 113–117, 133

Thesaurus, 57, 60

time and date, displaying, 80

time periods, filling, 43

timelines, using to view data, 397–400, 408

Top 10 filter, creating, 139–141

Total row. See also rows

adding to tables, 62–63

changing calculation in, 64

totals

adding using Quick Analysis Lens, 191

finding, 144

tracer arrows, displaying and removing, 95, 97

Translator tools, 58, 60–61

treemap charts, 226, 228

trendlines, 236–238. See also charts

trends, finding in data, 235–238, 253

Tufte, Edward, 239

type argument, 74

U

undoing actions, 49, 57, 59

unique values, finding in data sets, 149. See also values

V

validation rules, adding to cells, 151

#VALUE! error code, 95

values. See also formulas; unique values

calculating, 100

defining for ranges of cells, 150–152, 154

displaying, 139

entering in cells, 43–44

looking for, 53

replacing, 55–56

summarizing, 147

watching in cell ranges, 97–98

VAR function, 145–146

Visual Basic Editor, opening, 319–320

VLOOKUP function, 168–170

W

Watch Window, 97–98

waterfall charts, 224, 228

web, saving workbooks for, 369–370

weekdays, filling, 43

words, adding to dictionary, 60

workbook windows, arranging, 26–27

workbooks. See also templates

authenticating, 366–368, 381

closing, 8, 12

copying, 12–15

creating, 6–12, 37

displaying copies of, 27

finalizing, 364–366, 381

modifying, 12–16, 37

opening, 10

printing parts of, 308–311

protecting, 358–364, 380–381

recalculating, 91

repositioning, 13–14

saving, 7, 9, 174, 365

saving for electronic distribution, 354–355, 380

saving for web, 369–370, 381–382

switching to, 27

as templates, 174–177

worksheets

changing tab colors, 16

creating, 13

displaying, 13

hiding and unhiding, 15

modifying, 16–22, 37–38

naming, 12

printing, 305–307, 314

printing parts of, 314

protecting, 358–364, 380–381

recalculating, 91

renaming, 13

scaling for printing, 302

selecting, 21

zooming in on, 24–25

X

.xlsm extension, 316

.xlsx extension, 316

.xlt and .xltm files, saving, 174

.xltm extension, 316

.xltx extension, 316

XML data, importing and exporting, 371–374, 382

XPS files, saving workbooks as, 354–355

Z

zooming in on worksheets, 24–25