E-Book
ohne Abo 35,99 €

Excel 2016 Bible E-Book

John Walkenbach

0,0

35,99 €

Sammeln Sie Punkte in unserem Gutscheinprogramm und kaufen Sie E-Books und Hörbücher mit bis zu 100% Rabatt.
Mehr erfahren.

Herausgeber: John Wiley & Sons
Kategorie: Wissenschaft und neue Technologien
Serie: Bible
Sprache: Englisch
Veröffentlichungsjahr: 2015

Beschreibung

The complete guide to Excel 2016, from Mr. Spreadsheet himself Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more. Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2016 Bible. * Create functional spreadsheets that work * Master formulas, formatting, pivot tables, and more * Get acquainted with Excel 2016's new features and tools * Customize downloadable templates and worksheets Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel 2016 Bible has you covered with complete coverage and clear expert guidance.

Details

Sie lesen das E-Book in den Legimi-Apps auf:

Android

iOS

von Legimi
zertifizierten E-Readern

Seitenzahl: 1333

Bewertungen

0,0

Rezensionen(0 Rezensionen)

Leseprobe

Introduction

Is This Book for You?

Software Versions

Conventions Used in This Book

How This Book Is Organized

How to Use This Book

What's on the Website

Part I: Getting Started with Excel

Chapter 1: Introducing Excel

Identifying What Excel Is Good For

Seeing What's New in Excel 2016

Understanding Workbooks and Worksheets

Moving Around a Worksheet

Using the Ribbon

Using Shortcut Menus

Customizing Your Quick Access Toolbar

Working with Dialog Boxes

Using Task Panes

Creating Your First Excel Workbook

Chapter 2: Entering and Editing Worksheet Data

Exploring Data Types

Entering Text and Values into Your Worksheets

Entering Dates and Times into Your Worksheets

Modifying Cell Contents

Applying Number Formatting

Chapter 3: Essential Worksheet Operations

Learning the Fundamentals of Excel Worksheets

Controlling the Worksheet View

Working with Rows and Columns

Chapter 4: Working with Cells and Ranges

Understanding Cells and Ranges

Copying or Moving Ranges

Using Names to Work with Ranges

Adding Comments to Cells

Chapter 5: Introducing Tables

What Is a Table?

Creating a Table

Changing the Look of a Table

Working with Tables

Chapter 6: Worksheet Formatting

Getting to Know the Formatting Tools

Using Different Fonts to Format Your Worksheet

Changing Text Alignment

Using Colors and Shading

Adding Borders and Lines

Adding a Background Image to a Worksheet

Using Named Styles for Easier Formatting

Understanding Document Themes

Chapter 7: Understanding Excel Files

Creating a New Workbook

Opening an Existing Workbook

Saving a Workbook

Using AutoRecover

Password-Protecting a Workbook

Organizing Your Files

Other Workbook Info Options

Closing Workbooks

Safeguarding Your Work

Excel File Compatibility

Chapter 8: Using and Creating Templates

Exploring Excel Templates

Understanding Custom Excel Templates

Chapter 9: Printing Your Work

Basic Printing

Changing Your Page View

Adjusting Common Page Setup Settings

Adding a Header or a Footer to Your Reports

Pages

xli

xlii

xliii

xliv

xlv

100

101

102

103

104

105

106

107

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

381

382

383

384

385

386

387

388

389

390

391

392

393

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

445

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

601

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

633

634

635

636

637

638

639

640

641

642

643

644

645

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

677

678

679

680

681

682

683

684

685

686

687

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

731

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

863

864

865

866

867

868

869

870

871

872

873

874

875

877

878

879

880

881

882

883

884

885

886

887

888

889

891

890

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1031

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1053

1054

1055

1056

1057

1058

1059

Guide

Cover

Table of Contents

Begin Reading

List of Illustrations

Chapter 1: Introducing Excel

Figure 1.1 The Excel screen has many useful elements that you will use often.

Figure 1.2 The active cell is the cell with the dark border — in this case, cell C8.

Figure 1.3 The Home tab of the Ribbon.

Figure 1.4 The Home tab when Excel's window is made narrower.

Figure 1.5 The Home tab when Excel's window is made very narrow.

Figure 1.6 When you select an object, contextual tabs contain tools for working with that object.

Figure 1.7 The Merge & Center command is a split button control.

Figure 1.8 Pressing Alt displays the keytips.

Figure 1.9 Click the right mouse button to display a shortcut menu of commands you're most likely to use.

Figure 1.10 Add new icons to your Quick Access toolbar by using the Quick Access Toolbar section of the Excel Options dialog box.

Figure 1.11 Excel uses a dialog box to get additional information about a command.

Figure 1.12 Use the dialog box tabs to select different functional areas of the dialog box.

Figure 1.13 The Format Picture task pane, docked on the right side of the window.

Figure 1.14 Your worksheet after you've entered the column headings and month names.

Figure 1.15 Your worksheet after you've created the formulas.

Figure 1.16 Your worksheet after you've converted the range to a table.

Figure 1.17 The table and chart.

Chapter 2: Entering and Editing Worksheet Data

Figure 2.1 You can use values, text, and formulas to create useful Excel worksheets.

Figure 2.2 The Formula bar, expanded in height to show more information in the cell.

Figure 2.3 When you're editing a cell, the Formula bar enables two new icons: Cancel (X) and Enter (check mark).

Figure 2.4 You can use the Advanced tab in Excel Options to select a number of helpful input option settings.

Figure 2.5 This series was created by using AutoFill.

Figure 2.6 AutoCorrect allows you to create shorthand abbreviations for text you enter often.

Figure 2.7 Excel's built-in data form can simplify many data-entry tasks.

Figure 2.8 Use numeric formatting to make it easier to understand what the values in the worksheet represent.

Figure 2.9 You can find number formatting commands in the Number group of the Home tab.

Figure 2.10 When you need more control over number formats, use the Number tab of the Format Cells dialog box.

Chapter 3: Essential Worksheet Operations

Figure 3.1 Use the Arrange Windows dialog box to quickly arrange all open nonminimized workbook windows.

Figure 3.2 Use the tab scrolling controls to activate a different worksheet or to see additional worksheet tabs.

Figure 3.3 Excel's warning that you might be losing some data.

Figure 3.4 Use the Move or Copy dialog box to move or copy worksheets in the same or another workbook.

Figure 3.5 Use multiple windows to view different sections of a workbook at the same time.

Figure 3.6 You can split the worksheet window into two or four panes to view different areas of the worksheet at the same time.

Figure 3.7 Freeze certain columns and rows to make them remain visible while you scroll the worksheet.

Figure 3.8 When using a table, scrolling down displays the table headings where the column letters normally appear.

Figure 3.9 Use the Watch Window to monitor the value in one or more cells.

Figure 3.10 You can't add a new row or column if it causes nonblank cells to move off the worksheet.

Figure 3.11 You can insert partial rows or columns by using the Insert dialog box.

Chapter 4: Working with Cells and Ranges

Figure 4.1 When you select a range, it appears highlighted, but the active cell within the range is not highlighted.

Figure 4.2 Excel enables you to select noncontiguous ranges.

Figure 4.3 The worksheets in this workbook are laid out identically.

Figure 4.4 In Group mode, you can work with a three-dimensional range of cells that extend across multiple worksheets.

Figure 4.5 Use the Go to Special dialog box to select specific types of cells.

Figure 4.6 The Find and Replace dialog box, with its options displayed.

Figure 4.7 The Find and Replace dialog box, with its results listed.

Figure 4.8 The paste icons on the shortcut menu provide more control over how the pasted information appears.

Figure 4.9 Use the Clipboard task pane to copy and paste multiple items.

Figure 4.10 Excel offers several pasting options, with preview. Here, the information is copied from E4:G7 and is being pasted beginning at cell F11 using the Transpose option.

Figure 4.11 The Paste Special dialog box.

Figure 4.12 Transposing a range changes the orientation as the information is pasted into the worksheet.

Figure 4.13 Create names for cells or ranges by using the New Name dialog box.

Figure 4.14 Use the Create Names from Selection dialog box to name cells using labels that appear in the worksheet.

Figure 4.15 Use the Name Manager to work with range names.

Figure 4.16 You can add comments to cells to help point out specific items in your worksheets.

Figure 4.17 This comment contains a graphics image.

Figure 4.18 Cell comments don't have to be rectangles.

Figure 4.19 Specifying how to print cell comments.

Chapter 5: Introducing Tables

Figure 5.1 This range of data is a good candidate for a table.

Figure 5.2 An Excel table.

Figure 5.3 When you select a cell in a table, you can use the commands located on the Table Tools Design tab.

Figure 5.4 Use the Create Table dialog box to verify that Excel guessed the table dimensions correctly.

Figure 5.5 Excel offers many different table styles.

Figure 5.6 Use this dialog box to create a new table style.

Figure 5.7 Several types of summary formulas are available for the Total Row.

Figure 5.8 Removing duplicate rows from a table is easy.

Figure 5.9 Each column in a table has sorting and filtering options.

Figure 5.10 A table after performing a three-column sort.

Figure 5.11 Using the Sort dialog box to specify a three-column sort.

Figure 5.12 This table is filtered to show only the information for N. County.

Figure 5.13 Specifying a more complex numeric filter.

Figure 5.14 Use the Insert Slicers dialog box to specify which slicers to create.

Figure 5.15 The table is filtered by two slicers.

Chapter 6: Worksheet Formatting

Figure 6.1 In just a few minutes, some simple formatting can greatly improve the appearance of your worksheet.

Figure 6.2 The Mini toolbar appears above or below the right-click shortcut menu.

Figure 6.3 The Font tab of the Format Cells dialog box gives you many additional font attribute options.

Figure 6.4 You can choose many different font formatting options for your worksheets.

Figure 6.5 The full range of alignment options is available on the Alignment tab of the Format Cells dialog box.

Figure 6.6 The same text, displayed with three types of horizontal alignment.

Figure 6.7 Merge worksheet cells to make them act as if they were a single cell.

Figure 6.8 Rotate text for additional visual impact.

Figure 6.9 Use the Borders drop-down list to add lines around worksheet cells.

Figure 6.10 Use the Border tab of the Format Cells dialog box for more control over cell borders.

Figure 6.11 You can add almost any image file as a worksheet background image.

Figure 6.12 Excel displays samples of predefined cell styles.

Figure 6.13 Use the Style dialog box to modify named styles.

Figure 6.14 The elements in this worksheet use the default theme.

Figure 6.15 The worksheet after applying a different theme.

Figure 6.16 Built-in Excel theme choices.

Figure 6.17 Use this dialog box to specify two fonts for a theme.

Figure 6.18 If you're feeling creative, you can specify a set of custom colors for a theme.

Chapter 7: Understanding Excel Files

Figure 7.1 The Encrypt Document dialog box is where you specify a password for your workbook.

Figure 7.2 The Compatibility Checker is a useful tool for those who share workbooks with other people.

Chapter 8: Using and Creating Templates

Figure 8.1 The New page in Backstage view allows you to search for templates.

Figure 8.2 A workbook created from a template.

Chapter 9: Printing Your Work

Figure 9.1 In Normal view, dotted lines indicate page breaks.

Figure 9.2 In Page Layout view, the worksheet resembles printed pages.

Figure 9.3 Page Break Preview mode gives you a bird's-eye view of your worksheet and shows exactly where the page breaks occur.

Figure 9.4 The Margins tab of the Page Setup dialog box.

Figure 9.5 Use the Sheet tab of the Page Setup dialog box to specify rows or columns that will appear on each printed page.

Figure 9.6 This three-part header is one of Excel's predefined headers.

Figure 9.7 Use the Properties tab of the object's Format dialog box to prevent objects from printing.

Figure 9.8 Use the Add View dialog box to create a named view.

Chapter 10: Introducing Formulas and Functions

Figure 10.1 Excel sometimes suggests a syntactically correct formula, but not the formula you had in mind.

Figure 10.2 Excel displays a drop-down list when you enter a formula.

Figure 10.3 Use the Paste Name dialog box to quickly enter a defined name into a formula.

Figure 10.4 You can insert a function by selecting it from one of the function categories.

Figure 10.5 The Insert Function dialog box.

Figure 10.6 The Function Arguments dialog box.

Figure 10.7 Copying a formula that contains relative references.

Figure 10.8 Formula references to the sales tax cell should be absolute.

Figure 10.9 Using mixed cell references.

Figure 10.10 A simple table with three columns of information.

Figure 10.11 A drop-down list enables you to select a summary formula for a table column.

Figure 10.12 The Difference column contains a formula.

Figure 10.13 The formula AutoComplete feature is useful when creating a formula that refers to data in a table.

Figure 10.14 If you see this warning, you know that the formula you entered will result in a circular reference.

Figure 10.15 You can control when Excel calculates formulas.

Figure 10.16 Excel allows you to name a formula that doesn't exist in a worksheet cell.

Figure 10.17 You can use a range intersection formula to determine values.

Figure 10.18 With names, using a range intersection formula to determine values is even more helpful.

Figure 10.19 Use the Apply Names dialog box to replace cell or range references with defined names.

Chapter 11: Creating Formulas That Manipulate Text

Figure 11.1 The ANSI character set (for the Wingdings 3 font).

Figure 11.2 The formula in D3 doesn't display the formatted number.

Figure 11.3 Using the

REPT

function to create a histogram in a worksheet range.

Figure 11.4 Using a formula to pad a number with asterisks.

Figure 11.5 This worksheet uses formulas to extract the first name, last name, and middle name (or initial) from a list of names in column A.

Chapter 12: Working with Dates and Times

Figure 12.1 Use the Number tab of the Format Cells dialog box to change the appearance of dates and times.

Figure 12.2 My Extended Date Functions add-in enables you to work with pre-1900 dates.

Figure 12.3 Using AutoFill to create a series of dates.

Figure 12.4 Using the

NETWORKDAYS

function to calculate the number of workdays between two dates.

Figure 12.5 Using formulas to determine the date for various holidays.

Figure 12.6 Calculating the number of hours worked returns an error if the shift spans midnight.

Figure 12.7 Incorrect cell formatting makes the total appear incorrectly.

Figure 12.8 An employee timesheet workbook.

Figure 12.9 Using a formula to create a series of incremental times.

Figure 12.10 This worksheet uses times not associated with a time of day.

Chapter 13: Creating Formulas That Count and Sum

Figure 13.1 Formulas in column E display various counts of the data in A1:B10.

Figure 13.2 This worksheet demonstrates various counting techniques that use multiple criteria.

Figure 13.3 The MODE function returns the most frequently occurring value in a range.

Figure 13.4 This worksheet demonstrates various ways to count character strings in a range.

Figure 13.5 Creating a frequency distribution for the data in A1:E25.

Figure 13.6 Frequency distributions created by using the FREQUENCY function.

Figure 13.7 Creating a frequency distribution of test scores.

Figure 13.8 The Analysis ToolPak's Histogram dialog box.

Figure 13.9 A frequency distribution and chart generated by the Analysis ToolPak's Histogram option.

Figure 13.10 Using a pivot chart to display a histogram.

Figure 13.11 Simple formulas in column C display a cumulative sum of the values in column B.

Figure 13.12 Using an

function to hide cumulative sums for missing data.

Figure 13.13 Using an array formula to calculate the sum of the 20 largest values in a range.

Figure 13.14 A negative value in column F indicates a past-due payment.

Figure 13.15 This worksheet demonstrates summing based on multiple criteria.

Chapter 14: Creating Formulas That Look Up Values

Figure 14.1 Lookup formulas in row 2 look up the information for the employee name in cell C2.

Figure 14.2 Using

VLOOKUP

to look up a tax rate.

Figure 14.3 Using

HLOOKUP

to look up a tax rate.

Figure 14.4 Using

LOOKUP

to look up a tax rate.

Figure 14.5 Using the

INDEX

and

MATCH

functions to perform a lookup.

Figure 14.6 This lookup table requires an exact match.

Figure 14.7 The

VLOOKUP

function can't look up a value in column B based on a value in column C.

Figure 14.8 Using an array formula to perform a case-sensitive lookup.

Figure 14.9 This worksheet demonstrates the use of multiple lookup tables.

Figure 14.10 Looking up letter grades for test scores.

Figure 14.11 Using multiple formulas to calculate a GPA.

Figure 14.12 This table demonstrates a two-way lookup.

Figure 14.13 This workbook performs a lookup by using information in two columns (D and E).

Figure 14.14 The formula in cell B2 returns the address in the Data range for the value in cell B1.

Figure 14.15 This workbook demonstrates how to perform a lookup by using the closest match.

Chapter 15: Creating Formulas for Financial Applications

Figure 15.1 Using the

PMT

function to calculate a periodic loan payment amount.

Figure 15.2 This chart shows how the interest and principal amounts vary during the payment periods of a loan.

Figure 15.3 This worksheet calculates the number of payments required to pay off a credit card balance by paying the minimum payment amount each month.

Figure 15.4 Column B shows the payment required to pay off the credit card balance for various payoff periods.

Figure 15.5 A loan amortization schedule.

Figure 15.6 Using a one-way data table to display three loan calculations for various interest rates.

Figure 15.7 The Data Table dialog box.

Figure 15.8 Using a two-way data table to display payment amounts for various loan amounts and interest rates.

Figure 15.9 This worksheet tracks loan payments that are made on an irregular basis.

Figure 15.10 This worksheet calculates simple interest payments.

Figure 15.11 Using a series of formulas to calculate compound interest.

Figure 15.12 Using a single formula to calculate compound interest.

Figure 15.13 Calculating interest by using daily compounding.

Figure 15.14 This worksheet contains formulas to calculate annuities.

Figure 15.15 A comparison of four depreciation functions.

Figure 15.16 This chart shows an asset's value over time, using four depreciation functions.

Figure 15.17 Using the VBD function to calculate depreciation for multiple periods.

Figure 15.18 Four years of monthly sales data.

Figure 15.19 The Create Forecast Worksheet dialog box.

Figure 15.20 The forecast worksheet contains a table and a chart.

Chapter 16: Miscellaneous Calculations

Figure 16.1 A table that lists all the units supported by the

CONVERT

function.

Figure 16.2 A right triangle's components.

Chapter 17: Introducing Array Formulas

Figure 17.1 Column D contains formulas to calculate the total for each product.

Figure 17.2 The array formula in cell C9 calculates the total sales without using intermediate formulas.

Figure 17.3 A 3

4 array entered into a range of cells.

Figure 17.4 A 3

4 array entered into a 10

5 cell range.

Figure 17.5 Creating a named array constant.

Figure 17.6 Using a named array constant in an array formula.

Figure 17.7 Excel's warning message reminds you that you can't edit just one cell of a multicell array formula.

Figure 17.8 Creating an array from a range.

Figure 17.9 After you press F9, the Formula bar displays the array constant.

Figure 17.10 Performing a mathematical operation on an array.

Figure 17.11 Multiplying each array element by itself.

Figure 17.12 Using the

TRANSPOSE

function to transpose a rectangular array.

Figure 17.13 Using an array formula to generate consecutive integers.

Figure 17.14 The goal is to count the number of characters in a range of text.

Figure 17.15 An array formula returns the sum of the three smallest values in A1:A10.

Figure 17.16 An array formula returns the number of text cells in the range.

Figure 17.17 Without an array formula, calculating the average change requires intermediate formulas in column D.

Figure 17.18 You can replace the lookup table in D1:E10 with an array constant.

Chapter 18: Performing Magic with Array Formulas

Figure 18.1 An array formula can sum a range of values, even if the range contains errors.

Figure 18.2 The calculated average includes cells that contain a 0.

Figure 18.3 Using an array formula to determine whether a range contains a particular value.

Figure 18.4 Using an array formula to count the number of differences in two ranges.

Figure 18.5 Using an array formula to return the longest text in a range.

Figure 18.6 Using array formula to count and identify items that aren't in a list.

Figure 18.7 Two versions of an array formula calculate the sum of the digits in an integer.

Figure 18.8 Using an array formula to correct rounding errors.

Figure 18.9 An array formula returns the sum of every

th value in the range.

Figure 18.10 An array formula returns the closest match.

Figure 18.11 Using array formulas to return the last nonempty cell in a column or row.

Figure 18.12 Using an array formula to return only the positive values in a range.

Figure 18.13 A multicell array formula displays the entries in A4:A13 in reverse order.

Figure 18.14 A multicell array formula displays the values in column A, sorted.

Figure 18.15 Using an array formula to return unique items from a list.

Figure 18.16 Displaying a calendar by using a single array formula.

Chapter 19: Getting Started Making Charts

Figure 19.1 A simple column chart depicts the monthly sales volume.

Figure 19.2 This line chart displays two data series.

Figure 19.3 The Move Chart dialog box lets you move a chart to a chart sheet.

Figure 19.4 The source data for the hands-on chart example.

Figure 19.5 Letting Excel recommend a chart type.

Figure 19.6 A clustered column chart created from the data in the table.

Figure 19.7 The chart, after selecting a different style and layout.

Figure 19.8 The chart, after changing the row and column orientation.

Figure 19.9 Use this dialog box to change the chart type.

Figure 19.10 The customer satisfaction data, displayed using four different chart types.

Figure 19.11 The same data, plotted by using six chart types.

Figure 19.12 This clustered column chart compares monthly sales for two products.

Figure 19.13 This stacked column chart displays sales by product and depicts the total sales.

Figure 19.14 This 100% stacked column chart displays monthly sales as a percentage.

Figure 19.15 A 3-D column chart.

Figure 19.16 A true 3-D column chart.

Figure 19.17 If you have lengthy category labels, a bar chart may be a good choice.

Figure 19.18 A line chart often can help you spot trends in your data.

Figure 19.19 This line chart displays three series.

Figure 19.20 This 3-D line chart does not present the data very well.

Figure 19.21 A pie chart with one slice exploded.

Figure 19.22 A bar of pie chart that shows detail for one of the pie slices.

Figure 19.23 An XY chart shows the relationship between two variables.

Figure 19.24 A hypocycloid curve, plotted as an XY chart.

Figure 19.25 A stacked area chart.

Figure 19.26 This 3-D area chart is not a good choice.

Figure 19.27 Plotting ski sales using a radar chart with 12 categories and two series.

Figure 19.28 A stacked bar chart is a better choice for the ski sales data.

Figure 19.29 These radar charts depict the red, green, and blue contributions for each of four colors.

Figure 19.30 A surface chart.

Figure 19.31 A bubble chart.

Figure 19.32 This bubble chart depicts a mouse.

Figure 19.33 The four stock chart subtypes.

Figure 19.34 Plotting temperature data with a stock chart.

Figure 19.35 Displaying a student grade distribution using a histogram chart.

Figure 19.36 A Pareto chart displays the number of complaints graphically.

Figure 19.37 A waterfall chart showing positive and negative net cash flows.

Figure 19.38 A box & whisker chart that summarizes data for four groups.

Figure 19.39 A sunburst chart that depicts a music collection by genre and subgenre.

Figure 19.40 A treemap chart that depicts a music collection by genre and subgenre.

Chapter 20: Learning Advanced Charting

Figure 20.1 The Chart Elements control (in the upper-left corner) displays the name of the selected chart element. In this example, the “chart title” is selected.

Figure 20.2 Using the Chart Elements control in the Mini toolbar.

Figure 20.3 Use the Format task pane to set the properties of a selected chart element — in this case, the chart's value axis.

Figure 20.4 Chart customization buttons.

Figure 20.5 The Chart Area element uses No Fill, so the underlying cells are visible.

Figure 20.6 Reducing the size of the Plot Area makes room for the Shape.

Figure 20.7 Use the Select Data Source dialog box to change the name of a data series.

Figure 20.8 Using Shapes as callouts in lieu of a legend.

Figure 20.9 The Format Axis task pane for a value axis.

Figure 20.10 These two charts show the same data but use different value axis bounds.

Figure 20.11 The right chart uses the Values in Reverse Order option

Figure 20.12 These charts display the same data, but the bottom chart uses a logarithmic scale.

Figure 20.13 The chart on the right uses display units of millions.

Figure 20.14 Various ways to display axis labels and crossing points.

Figure 20.15 Some of the options available for a category axis.

Figure 20.16 Excel recognizes dates and creates a time-based category axis.

Figure 20.17 Overriding the Excel time-based category axis.

Figure 20.18 Excel determines the way to display category axis labels.

Figure 20.19 Changing the interval between the Labels setting makes labels display horizontally.

Figure 20.20 This chart uses three columns of text for the category axis labels.

Figure 20.21 This chart needs a new data series.

Figure 20.22 Changing a chart's data series by dragging the range outline.

Figure 20.23 The Edit Series dialog box.

Figure 20.24 These charts use data labels and don't display axes.

Figure 20.25 Data labels linked to text in an arbitrary range.

Figure 20.26 Three options for dealing with missing data.

Figure 20.27 This line chart series displays error bars based on percentage.

Figure 20.28 A line chart with two linear trendlines.

Figure 20.29 The trendline depicts the relationship between height and weight.

Figure 20.30 Variations on a simple 3-D column chart.

Figure 20.31 The Precipitation series is barely visible.

Figure 20.32 Using the Change Chart dialog box to convert a chart into a combination chart.

Figure 20.33 A five-way combination chart.

Figure 20.34 This combination chart includes a data table that displays the values of the data points.

Figure 20.35 The Chart Area contains a photo.

Figure 20.36 The left chart uses clip art, and the right chart uses a Shape that was copied to the Clipboard and pasted to the chart's data series.

Figure 20.37 This single-point chart displays progress toward a goal.

Figure 20.38 This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

Figure 20.39 A comparative histogram.

Figure 20.40 You can create a simple Gantt chart from a bar chart.

Figure 20.41 This chart plots the

SIN(x)

Figure 20.42 Using a surface chart to plot a function with two variables.

Chapter 21: Visualizing Data Using Conditional Formatting

Figure 21.1 This worksheet demonstrates a few conditional formatting rules.

Figure 21.2 One of several different conditional formatting dialog boxes.

Figure 21.3 Use the New Formatting Rule dialog box to create your own conditional formatting rules.

Figure 21.4 The length of the data bars is proportional to the track length in the cell in column D.

Figure 21.5 Comparing data bars conditional formatting (top) with a bar chart.

Figure 21.6 Two examples of color scale conditional formatting.

Figure 21.7 Use the New Formatting Rule dialog box to customize a color scale.

Figure 21.8 This worksheet uses color scale conditional formatting to display daily temperatures.

Figure 21.9 Using an icon set to indicate the status of projects.

Figure 21.10 Changing the icon assignment rule.

Figure 21.11 Using a modified rule and eliminating an icon makes the table more readable.

Figure 21.12 The arrows depict the trend from Test 1 to Test 2.

Figure 21.13 Hiding one of the icons makes the table less cluttered.

Figure 21.14 Creating a conditional formatting rule based on a formula.

Figure 21.15 Highlighting a row, based on a matching name.

Figure 21.16 Using conditional formatting to apply formatting to alternate rows.

Figure 21.17 Conditional formatting produces these groups of alternating shaded rows.

Figure 21.18 The sum is displayed only when all four values have been entered.

Figure 21.19 A missing value causes the sum to be hidden.

Figure 21.20 A Gantt chart created using conditional formatting.

Chapter 22: Creating Sparkline Graphics

Figure 22.1 Three groups of Sparklines.

Figure 22.2 Data to be summarized with Sparklines.

Figure 22.3 Use the Create Sparklines dialog box to specify the data range and the location for the Sparkline graphics.

Figure 22.4 Column Sparklines summarize the precipitation data for nine cities.

Figure 22.5 A Sparkline at various sizes.

Figure 22.6 The Hidden and Empty Cell Settings dialog box.

Figure 22.7 Highlighting options for Line Sparklines.

Figure 22.8 The bottom group of Sparklines shows the effect of using the same axis minimum and maximum values for all Sparklines in a group.

Figure 22.9 Sparklines display the number of pages read per month.

Figure 22.10 Using Win/Loss Sparklines to display goal achievement.

Figure 22.11 The axis in the Sparklines represents the goal.

Figure 22.12 The Sparkline displays the values as if they are at equal time intervals.

Figure 22.13 After specifying a date axis, the Sparkline shows the values accurately.

Figure 22.14 Creating a Sparkline from data in a table.

Figure 22.15 Using a dynamic range name to display only the last seven data points in a Sparkline.

Chapter 23: Enhancing Your Work with Pictures and Drawings

Figure 23.1 The Shapes gallery.

Figure 23.2 This Shape was drawn on the worksheet. Its name (Sun 1) appears in the Name box.

Figure 23.3 A variety of Shapes.

Figure 23.4 Six variations on a Shape.

Figure 23.5 When none of the existing Shapes will do, create your own Freeform Shape.

Figure 23.6 A Shape before and after editing its points.

Figure 23.7 Inserting a SmartArt graphic.

Figure 23.8 This SmartArt needs to be customized.

Figure 23.9 The SmartArt after you add a new element and text.

Figure 23.10 WordArt examples.

Figure 23.11 Use the Insert Pictures window to search for images online.

Figure 23.12 Displaying a picture in a number of different styles.

Figure 23.13 An equation created by the Equation Editor.

Chapter 24: Customizing the Excel User Interface

Figure 24.1 The default location for the Quick Access toolbar is on the left side of the Excel title bar.

Figure 24.2 This drop-down list is one way to add a new command to the Quick Access toolbar.

Figure 24.3 Use the Quick Access Toolbar tab in the Excel Options dialog box to customize the Quick Access toolbar.

Figure 24.4 The Customize Ribbon tab of the Excel Options dialog box.

Figure 24.5 The View tab, with two new groups added.

Chapter 25: Using Custom Number Formats

Figure 25.1 The Custom category of the Number tab in the Format Cells dialog box.

Figure 25.2 Examples of custom number formatting.

Figure 25.3 Selecting a number format to display a value as a fraction.

Chapter 26: Using Data Validation

Figure 26.1 Displaying a message when the user makes an invalid entry.

Figure 26.2 The three tabs of the Data Validation dialog box.

Figure 26.3 Excel can draw circles around invalid entries (in this case, cells that contain values greater than 100).

Figure 26.4 This drop-down list (with an Input Message) was created using data validation.

Figure 26.5 Entering a data validation formula.

Figure 26.6 Using data validation to prevent duplicate entries in a range.

Figure 26.7 Using data validation to ensure that the sum of a range does not exceed a certain value.

Figure 26.8 The items displayed in the list in cell F2 depend on the list item selected in cell E2.

Chapter 27: Creating and Using Worksheet Outlines

Figure 27.1 A simple sales summary with subtotals.

Figure 27.2 The worksheet after creating an outline.

Figure 27.3 The worksheet after collapsing the outline to the second level.

Figure 27.4 The worksheet after adding a column outline.

Figure 27.5 The worksheet with both outlines collapsed at the second level.

Figure 27.6 An outline of this book, created manually.

Figure 27.7 Use the Settings dialog box to adjust the position of the outline symbols.

Chapter 28: Linking and Consolidating Worksheets

Figure 28.1 This confirmation message indicates that the workbook you're saving contains references to a workbook that you haven't yet saved.

Figure 28.2 Excel displays this dialog box when you open a workbook that contains links to other files.

Figure 28.3 The Edit Links dialog box.

Figure 28.4 Use the Startup Prompt dialog box to specify how Excel handles links when the workbook is opened.

Figure 28.5 Choosing the Add operation in the Paste Special dialog box.

Figure 28.6 The Consolidate dialog box enables you to specify ranges to consolidate.

Figure 28.7 Three worksheets to be consolidated.

Figure 28.8 The result of consolidating the information in three workbooks.

Figure 28.9 Collapsing the outline to show only the totals.

Chapter 29: Excel and the Internet

Figure 29.1 A workbook displayed in a browser using Excel Online.

Figure 29.2 This workbook will be saved in a format that be opened in a web browser.

Figure 29.3 Viewing the HTML file in a browser.

Figure 29.4 Viewing the single-file web page in Internet Explorer.

Figure 29.5 Use the Insert Hyperlink dialog box to add hyperlinks to your Excel worksheets.

Figure 29.6 Hyperlinks in a workbook.

Figure 29.7 An example of an Office Add-in embedded in a worksheet.

Chapter 30: Protecting Your Work

Figure 30.1 Use the Protect Sheet dialog box to protect a worksheet.

Figure 30.2 Use the Protection tab in the Format Cells dialog box to change the Locked attribute of a cell or range.

Figure 30.3 Excel warns you if you attempt to change a locked cell.

Figure 30.4 The Allow Users to Edit Ranges dialog box.

Figure 30.5 Specify a workbook password in the Encrypt Document dialog box.

Figure 30.6 Opening this workbook requires a password.

Figure 30.7 The Protect Structure and Windows dialog box.

Figure 30.8 Protecting a VBA Project with a password.

Figure 30.9 The Document Inspector dialog box identifies hidden and personal information in a workbook.

Chapter 31: Making Your Worksheets Error Free

Figure 31.1 Using a formula to identify cells that contain extra space characters.

Figure 31.2

#DIV/0!

errors occur when the data in column B is missing.

Figure 31.3 Formulas in the range C4:C7 use an absolute reference to cell C1.

Figure 31.4 A simple demonstration of numbers that appear to add up incorrectly.

Figure 31.5 Excel's way of asking whether you want to update links in a workbook.

Figure 31.6 The Go to Special dialog box.

Figure 31.7 Zooming out and selecting all formula cells can give you a good overview of how the worksheet is designed.

Figure 31.8 Displaying formulas (bottom window) and their results (top window).

Figure 31.9 This worksheet displays arrows that indicate cell precedents for the formula in cell C13.

Figure 31.10 Excel can check your formulas for potential errors.

Figure 31.11 After you click an error, a drop-down control gives you a list of options.

Figure 31.12 Use the Error Checking dialog box to cycle through potential errors identified by Excel.

Figure 31.13 The Evaluate Formula dialog box shows a formula being calculated one step at a time.

Figure 31.14 Use the Find and Replace dialog box to locate information in a worksheet or workbook.

Figure 31.15 Displaying the result of a search in the Find and Replace dialog box.

Figure 31.16 Use the Find and Replace dialog box to change formatting.

Figure 31.17 Use the Spelling dialog box to locate and correct spelling errors in your worksheets.

Figure 31.18 Use the AutoCorrect dialog box to control the spelling corrections Excel makes automatically.

Chapter 32: Importing and Cleaning Data

Figure 32.1 Filtering by file extension in the Open dialog box.

Figure 32.2 This CSV file will be imported into a range.

Figure 32.3 Using the Import Data dialog box to import a CSV file.

Figure 32.4 This range contains data imported directly from a CSV file.

Figure 32.5 Use the Remove Duplicates dialog box to delete duplicate rows.

Figure 32.6 Using formulas to identify duplicate rows.

Figure 32.7 The imported data was put in one column rather than multiple columns.

Figure 32.8 The first dialog box in the Convert Text to Columns Wizard.

Figure 32.9 The goal is to extract the numbers in column A.

Figure 32.10 Using manually entered examples in B1 and B2, Excel's Flash Fill feature makes some incorrect guesses.

Figure 32.11 After you enter an example of a decimal number, Excel gets all of correct.

Figure 32.12 Using Flash Fill to split names.

Figure 32.13 Using a lookup table to classify ages into age ranges.

Figure 32.14 Using a lookup table to assign a region for a state.

Figure 32.15 The goal is to identify member numbers that are in the resigned members list in column F.

Figure 32.16 Vertical data that needs to be converted to three columns.

Figure 32.17 Headers that are used to convert the vertical data into rows.

Figure 32.18 A single formula transforms the vertical data into rows.

Figure 32.19 This report contains gaps in the Sales Rep column.

Figure 32.20 The gaps are gone, and this list can now be sorted.

Figure 32.21 To replace only the second hyphen in these cells, Find and Replace is not an option.

Figure 32.22 The Trailing Minus for Negative Numbers option makes it easy to fix trailing minus signs in a range of data.

Chapter 33: Introducing Pivot Tables

Figure 33.1 This table is used to create a pivot table.

Figure 33.2 A simple pivot table.

Figure 33.3 A pivot table that uses a report filter.

Figure 33.4 This range is not appropriate for a pivot table.

Figure 33.5 This range contains normalized data and is appropriate for a pivot table.

Figure 33.6 A pivot table created from normalized data.

Figure 33.7 Selecting a Recommended PivotTable.

Figure 33.8 In the Create PivotTable dialog box, you tell Excel where the data is and where you want the pivot table.

Figure 33.9 Use the PivotTable Fields task pane to build the pivot table.

Figure 33.10 After a few simple steps, the pivot table shows a summary of the data.

Figure 33.11 Two fields are used for row labels.

Figure 33.12 The pivot table is filtered by date.

Figure 33.13 This pivot table shows daily totals for each branch.

Figure 33.14 This pivot table shows new account totals by day of the week.

Figure 33.15 This pivot table uses the Count function to summarize the data.

Figure 33.16 This pivot table counts the number of accounts that fall into each value range.

Figure 33.17 This pivot table uses a filter to show only the teller data.

Figure 33.18 This pivot table uses three filters.

Chapter 34: Analyzing Data with Pivot Tables

Figure 34.1 This table doesn't have any numeric fields, but you can use it to generate a pivot table, shown next to the table.

Figure 34.2 The pivot table, after making a few changes.

Figure 34.3 A pivot table with two groups.

Figure 34.4 Pivot tables with options for subtotals and grand totals.

Figure 34.5 You can use a pivot table to summarize the sales data by month.

Figure 34.6 The pivot table, before grouping by month.

Figure 34.7 Use the Grouping dialog box to group pivot table items by dates.

Figure 34.8 The pivot table, after grouping by month and year.

Figure 34.9 This pivot table shows sales by quarter and by year.

Figure 34.10 This pivot table is grouped by hours.

Figure 34.11 Creating a frequency distribution for these test scores is simple.

Figure 34.12 The pivot table and pivot chart show the frequency distribution for the test scores.

Figure 34.13 This data demonstrates calculated fields and calculated items.

Figure 34.14 This pivot table was created from the sales data.

Figure 34.15 The Insert Calculated Field dialog box.

Figure 34.16 This pivot table uses a calculated field.

Figure 34.17 The Insert Calculated Item dialog box.

Figure 34.18 This pivot table uses calculated items for quarterly totals.

Figure 34.19 The pivot table, after creating two groups and adding subtotals.

Figure 34.20 Using slicers to filter the data displayed in a pivot table.

Figure 34.21 Using slicers to filter a pivot table by state and by month.

Figure 34.22 Using a timeline to filter a pivot table by date.

Figure 34.23 The formulas in column F reference cells in the pivot table.

Figure 34.24 After expanding the pivot table, formulas that use the

GETPIVOTDATA

function continue to display the correct result.

Figure 34.25 This data will be used to create a pivot chart.

Figure 34.26 This pivot table summarizes sales by region and by month.

Figure 34.27 The pivot chart uses the data displayed in the pivot table.

Figure 34.28 If you modify the pivot table, the pivot chart is also changed.

Figure 34.29 This table contains data for each county in the United States.

Figure 34.30 This pivot table was created from the county data.

Figure 34.31 This worksheet lists calculated fields and items for the pivot table.

Figure 34.32 This custom list ensures that the region names are sorted correctly.

Figure 34.33 These three tables will be used for a pivot table, using the Data Model.

Figure 34.34 The PivotTable Fields task pane, with three active tables.

Figure 34.35 Creating a relationship between the tables.

Figure 34.36 The pivot table, after adding two slicers.

Chapter 35: Performing Spreadsheet What-If Analysis

Figure 35.1 This simple worksheet model uses four input cells to produce the results.

Figure 35.2 How a one-input data table is set up.

Figure 35.3 Preparing to create a one-input data table.

Figure 35.4 The Data Table dialog box.

Figure 35.5 The result of the one-input data table.

Figure 35.6 The setup for a two-input data table.

Figure 35.7 This worksheet calculates the net profit from a direct-mail promotion.

Figure 35.8 Preparing to create a two-input data table.

Figure 35.9 The result of the two-input data table.

Figure 35.10 A simple production model to demonstrate Scenario Manager.

Figure 35.11 Use the Add Scenario dialog box to create a named scenario.

Figure 35.12 You enter the values for the scenario in the Scenario Values dialog box.

Figure 35.13 Selecting a scenario to display.

Figure 35.14 A Scenario Summary report produced by Scenario Manager.

Chapter 36: Analyzing Data Using Goal Seeking and Solver

Figure 36.1 This worksheet is a good demonstration of goal seeking.

Figure 36.2 The Goal Seek dialog box.

Figure 36.3 Goal Seek has found a solution.

Figure 36.4 Use Solver to determine the number of units to maximize the total profit.

Figure 36.5 The Solver Parameters dialog box.

Figure 36.6 The Add Constraint dialog box.

Figure 36.7 Solver displays this dialog box when it finds a solution to the problem.

Figure 36.8 One of three reports that Solver can produce.

Figure 36.9 You can control many aspects of the way Solver solves a problem.

Figure 36.10 Solver will attempt to solve this series of linear equations.

Figure 36.11 Solver will attempt to solve this series of linear equations.

Figure 36.12 This worksheet determines the least expensive way to ship products from warehouses to retail outlets.

Figure 36.13 The solution that was created by Solver.

Figure 36.14 Using Solver to maximize profit when resources are limited.

Figure 36.15 Solver determined how to use the resources to maximize the total profit.

Figure 36.16 This worksheet is set up to maximize a credit union's investments, given some constraints.

Figure 36.17 The results of the portfolio optimization.

Chapter 37: Analyzing Data with the Analysis ToolPak

Figure 37.1 Select your tool from the Data Analysis dialog box.

Figure 37.2 Specifying parameters for a single-factor analysis of variance.

Figure 37.3 The Correlation dialog box.

Figure 37.4 Descriptive Statistics output.

Figure 37.5 Output from the F-Test tool.

Figure 37.6 Use the Histogram tool to generate distributions and graphical output.

Figure 37.7 A chart produced from data generated by the Moving Average tool.

Figure 37.8 This dialog box enables you to generate a wide variety of random numbers.

Figure 37.9 The Regression dialog box.

Figure 37.10 Output from the paired t-test dialog box.

Chapter 38: Working with Get & Transform

Figure 38.1 Select a data source for Get & Transform.

Figure 38.2 Selecting a data source in the Navigator dialog box.

Figure 38.3 The Query Editor.

Figure 38.4 Choosing the columns to keep.

Figure 38.5 The data, returned to Excel.

Figure 38.6 Creating the formula for a new column.

Figure 38.7 The Query Settings task pane shows each action that you performed.

Figure 38.8 This CSV file is a data source for Get & Transform.

Figure 38.9 The Query Editor displaying data from the CSV file.

Figure 38.10 The query returns data from the CSV file, summarized.

Figure 38.11 Choosing data from a website.

Figure 38.12 The movie information after removing two empty columns.

Figure 38.13 The movie information after making some changes.

Figure 8.14 The movie information, imported into a table.

Figure 38.15 The Navigator dialog box for the first query.

Figure 38.16 Data from a web page query.

Figure 38.17 Results from the second web query.

Figure 38.18 Merging two queries.

Figure 38.19 The two merged queries produce a single table.

Figure 38.20 The Query Editor, displaying a list of files.

Figure 38.21 A pivot table, created from the output of a folder query.

Figure 38.22 Adding a formula for a custom column.

Figure 38.23 The query returns 20 random words, in alphabetical order.

Figure 38.24 This summary table will be converted to a normalized list.

Figure 38.25 A normalized list created from a summary table.

Chapter 39: Introducing Visual Basic for Applications

Figure 39.1 The Developer tab.

Figure 39.2 The Macro Settings section of the Trust Center dialog box.

Figure 39.3 Excel displays a security warning if a workbook contains macros.

Figure 39.4 Excel warns you if your workbook contains macros and you attempt to save it in a nonmacro file format.

Figure 39.5 A simple VBA procedure.

Figure 39.6 This VBA function returns the cube root of its argument.

Figure 39.7 The Record Macro dialog box.

Figure 39.8 The MyName procedure was generated by the Excel macro recorder.

Figure 39.9 The TimeStamp procedure was generated by the Excel macro recorder.

Figure 39.10 This TimeStamp macro works correctly.

Figure 39.11 Use the Macro Options dialog box to add or change a shortcut key for a macro.

Figure 39.12 Adding a button to a worksheet so that it can be used to execute a macro.

Figure 39.13 The ListFormulas macro creates a list of all formulas in a worksheet.

Chapter 40: Creating Custom Worksheet Functions

Figure 40.1 A simple custom worksheet function.

Figure 40.2 Creating a worksheet formula that uses a custom function.

Figure 40.3 Entering a description for a custom function. This description appears in the Insert Function dialog box.

Figure 40.4 Using the Function Arguments dialog box to insert a custom function.

Chapter 41: Creating UserForms

Figure 41.1 A UserForm that asks the user to select an option.

Figure 41.2 This dialog box is displayed by the VBA

InputBox

function.

Figure 41.3 A simple message box, displayed with the VBA

MsgBox

function.

Figure 41.4 The second argument of the

MsgBox

function determines what appears in the message box.

Figure 41.5 A message box with a longer message and a title.

Figure 41.6 An empty UserForm.

Figure 41.7 The Properties window for a CommandButton control.

Figure 41.8 A Label control, after changing its Font properties.

Figure 41.9 The code module for the UserForm.

Figure 41.10 The UserForm after adding controls and adjusting some properties.

Figure 41.11 Displaying the UserForm.

Figure 41.12 Adjusting the tab order in a UserForm.

Chapter 42: Using UserForm Controls in a Worksheet

Figure 42.1 This worksheet uses UserForm controls.

Figure 42.2 Excel's two sets of worksheet controls.

Figure 42.3 Use the Properties window to adjust the properties of a control — in this case, a CommandButton control.

Figure 42.4 Double-clicking a control in Design mode activates the VB Editor and enters an empty event-handler procedure.

Figure 42.5 A

ComboBox

control.

Figure 42.6 This worksheet has three

ScrollBar

controls.

Figure 42.7 A

TextBox

control with a vertical scrollbar.

Chapter 43: Working with Excel Events

Figure 43.1 The best way to create an event procedure is to let the VB Editor do it for you.

Figure 43.2 Moving the cell cursor causes the active cell's row and column to become shaded.

Chapter 44: VBA Examples

Figure 44.1 This range can consist of any number of rows.

Figure 44.2 Using the VBA InputBox function to get a value from the user.

Figure 44.3 You can instruct Excel not to display these types of alerts while a macro is running

Chapter 45: Creating Custom Excel Add-Ins

Figure 45.1 The Add-Ins dialog box.

Figure 45.2 This dialog box enables the user to change the case of text in the selected cells.

Figure 45.3 The custom dialog box.

Figure 45.4 Adding descriptive information about your add-in.

Figure 45.5 The Protection tab of the Project Properties dialog box.

List of Tables

Chapter 1: Introducing Excel

Table 1.1 Parts of the Excel Screen That You Need to Know

Table 1.2 Excel Worksheet Movement Keys

Chapter 2: Entering and Editing Worksheet Data

Table 2.1 Number Formatting Keyboard Shortcuts

Chapter 4: Working with Cells and Ranges

Table 4.1 Go to Special Options

Chapter 9: Printing Your Work

Table 9.1 Where to Change Printer Settings

Table 9.2 Header and Footer Buttons and Their Functions

Chapter 10: Introducing Formulas and Functions

Table 10.1 Operators Used in Formulas

Table 10.2 Operator Precedence in Excel Formulas

Table 10.3 Excel Error Values

Table 10.4 Reference Operators for Ranges

Chapter 12: Working with Dates and Times

Table 12.1 Date Entry Formats Recognized by Excel

Table 12.2 Times of Day and Their Corresponding Serial Numbers

Table 12.3 Time Entry Formats Recognized by Excel

Table 12.4 Date-Related Functions

Table 12.5 Time-Related Functions

Chapter 13: Creating Formulas That Count and Sum

Table 13.1 Excel Counting and Summing Functions

Table 13.2 Examples of Formulas Using the COUNTIF Function

Chapter 14: Creating Formulas That Look Up Values

Table 14.1 Functions Used in Lookup Formulas

Chapter 15: Creating Formulas for Financial Applications

Table 15.1 Financial Function Arguments

Table 15.2 Formulas Used to Calculate an Amortization Schedule

Table 15.3 Formulas to Calculate a Loan with Irregular Payments

Table 15.4 The Annuity Calculator Worksheet

Table 15.5 Excel Depreciation Functions

Chapter 16: Miscellaneous Calculations

Table 16.1 Other Conversion Functions

Table 16.2 Excel Rounding Functions

Table 16.3 Results Using the EVEN and ODD Functions

Chapter 25: Using Custom Number Formats

Table 25.1 Number-Formatting Buttons on the Ribbon

Table 25.2 Number-Formatting Keyboard Shortcuts

Table 25.3 Codes Used to Create Custom Number Formats

Table 25.4 Codes Used in Creating Custom Formats for Dates and Times

Table 25.5 Examples of Displaying Values in Thousands

Table 25.6 Examples of Displaying Values in Hundreds

Table 25.7 Examples of Displaying Values in Millions

Table 25.8 Examples of Displaying a Value with Extra Zeros

Table 25.9 Useful Built-In Date and Time Formats

Tausende von E-Books und Hörbücher

Ihre Zahl wächst ständig und Sie haben eine Fixpreisgarantie.

Sie haben über uns geschrieben: