27,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Make Excel work for you Excel 2019 All-In-One For Dummies offers eight books in one!! It is completely updated to reflect the major changes Microsoft is making to Office with the 2019 release. From basic Excel functions, such as creating and editing worksheets, to sharing and reviewing worksheets, to editing macros with Visual Basic, it provides you with a broad scope of the most common Excel applications and functions--including formatting worksheets, setting up formulas, protecting worksheets, importing data, charting data, and performing statistical functions. The book covers importing data, building and editing worksheets, creating formulas, generating pivot tables, and performing financial functions, what-if scenarios, database functions, and Web queries. More advanced topics include worksheet sharing and auditing, performing error trapping, building and running macros, charting data, and using Excel in conjunction with Microsoft Power BI (Business Intelligence) to analyze, model, and visualize vast quantities of data from a variety of local and online sources. * Get familiar with Worksheet design * Find out how to work with charts and graphics * Use Excel for data management, analysis, modeling, and visualization * Make sense of macros and VBA If you're a new or inexperienced user looking to spend more time on your projects than trying to figure out how to make Excel work for you, this all-encompassing book makes it easy!
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1360
Veröffentlichungsjahr: 2018
Ähnliche
Excel® 2019 All-in-One For Dummies®
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2019 by John Wiley & Sons, Inc., Hoboken, New Jersey
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ.
For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2018956684
ISBN 978-1-119-51794-8 (pbk); ISBN 978-1-119-51815-0 (ebk); ISBN 978-1-119-51821-1 (ebk)
Excel® 2019 All-in-One For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Excel® 2019 All-in-One For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Introduction
About This Book
Foolish Assumptions
How This Book Is Organized
Conventions Used in This Book
Icons Used in This Book
Beyond the Book
Where to Go from Here
Book 1: Excel Basics
Chapter 1: The Excel 2019 User Experience
Excel 2019’s Sleek Look and Feel
Excel’s Start Screen
Excel’s Ribbon User Interface
Getting Help
Launching and Quitting Excel
Chapter 2: Customizing Excel 2019
Tailoring the Quick Access Toolbar to Your Tastes
Exercising Your Options
Using Office Add-ins
Using Excel’s Own Add-ins
Book 2: Worksheet Design
Chapter 1: Building Worksheets
Designer Spreadsheets
It Takes All Kinds (Of Cell Entries)
Data Entry 101
Saving the Data
Document Recovery to the Rescue
Chapter 2: Formatting Worksheets
Making Cell Selections
Adjusting Columns and Rows
Formatting Tables from the Ribbon
Formatting Tables with the Quick Analysis Tool
Formatting Cells from the Ribbon
Formatting Cell Ranges with the Mini-Toolbar
Using the Format Cells Dialog Box
Hiring Out the Format Painter
Using Cell Styles
Conditional Formatting
Chapter 3: Editing and Proofing Worksheets
Opening a Workbook
Cell Editing 101
A Spreadsheet with a View
Copying and Moving Stuff Around
Find and Replace This Disgrace!
Spell Checking Heaven
Looking Up and Translating Stuff
Marking Invalid Data
Eliminating Errors with Text to Speech
Chapter 4: Managing Worksheets
Reorganizing the Worksheet
Reorganizing the Workbook
Working with Multiple Workbooks
Consolidating Worksheets
Chapter 5: Printing Worksheets
Printing from the Excel 2019 Backstage View
Quick Printing the Worksheet
Working with the Page Setup Options
Using the Print Options on the Sheet tab of the Page Setup dialog box
Headers and Footers
Solving Page Break Problems
Printing the Formulas in a Report
Book 3: Formulas and Functions
Chapter 1: Building Basic Formulas
Formulas 101
Copying Formulas
Adding Array Formulas
Range Names in Formulas
Adding Linking Formulas
Controlling Formula Recalculation
Circular References
Chapter 2: Logical Functions and Error Trapping
Understanding Error Values
Using Logical Functions
Error-Trapping Formulas
Whiting-Out Errors with Conditional Formatting
Formula Auditing
Removing Errors from the Printout
Chapter 3: Date and Time Formulas
Understanding Dates and Times
Using Date Functions
Using Time Functions
Chapter 4: Financial Formulas
Financial Functions 101
The PV, NPV, and FV Functions
The PMT Function
Depreciation Functions
Analysis ToolPak Financial Functions
Chapter 5: Math and Statistical Formulas
Math & Trig Functions
Statistical Functions
Chapter 6: Lookup, Information, and Text Formulas
Lookup and Reference
Information, Please …
Much Ado about Text
Book 4: Worksheet Collaboration and Review
Chapter 1: Protecting Workbooks and Worksheet Data
Password-Protecting the File
Protecting the Worksheet
Chapter 2: Using Hyperlinks
Hyperlinks 101
Using the HYPERLINK Function
Chapter 3: Preparing a Workbook for Distribution
Getting Your Workbook Ready for Review
Annotating Workbooks
Chapter 4: Sharing Workbooks and Worksheet Data
Sharing Your Workbooks Online
Excel 2019 Data Sharing Basics
Exporting Workbooks to Other Usable File Formats
Book 5: Charts and Graphics
Chapter 1: Charting Worksheet Data
Worksheet Charting 101
Adding Sparkline Graphics to a Worksheet
Adding Infographics to a Worksheet
Printing Charts
Chapter 2: Adding Graphic Objects
Graphic Objects 101
Inserting Different Types of Graphics
Drawing Graphics
Adding Screenshots of the Windows 10 Desktop
Using Themes
Book 6: Data Management
Chapter 1: Building and Maintaining Data Lists
Data List Basics
Sorting Data
Subtotaling Data
Chapter 2: Filtering and Querying a Data List
Data List Filtering 101
Filtering Data
Using the Database Functions
External Data Query
Book 7: Data Analysis
Chapter 1: Performing What-If Scenarios
Using Data Tables
Exploring Different Scenarios
Hide and Goal Seeking
Using the Solver
Chapter 2: Performing Large-Scale Data Analysis
Creating Pivot Tables
Formatting a Pivot Table
Sorting and Filtering the Pivot Table Data
Modifying the Pivot Table
Creating Pivot Charts
Using the Power Pivot Add-in
Using the 3D Map feature
Creating Forecast Worksheets
Book 8: Macros and VBA
Chapter 1: Recording and Running Macros
Macro Basics
Assigning Macros to the Ribbon and the Quick Access Toolbar
Macro Security
Chapter 2: VBA Programming
Using the Visual Basic Editor
Creating Custom Excel Functions
Index
About the Author
Connect with Dummies
End User License Agreement
Guide
Cover
Table of Contents
Begin Reading
Pages
i
ii
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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
180
181
182
183
184
185
186
187
188
189
190
191
193
194
195
196
197
198
199
200
201
202
203
204
205
206
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
296
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
348
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
380
381
382
383
384
385
387
388
389
390
391
392
393
394
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
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
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
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
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
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
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
676
677
678
679
680
681
682
683
684
685
686
687
688
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
719
720
721
722
723
724
725
726
727
728
729
730
731
732
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
Introduction
Excel 2019 All-in-One For Dummies brings together plain and simple information on using all aspects of the latest-and-greatest version of Microsoft Excel. It’s designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for all sorts of computing devices running Windows 10 (desktops, laptops, tablet PCs and even smartphones), Excel 2019 offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would want to go to all the trouble.
Warning: Excel 2019 marks the first version of Microsoft Excel that runs exclusively under a home or business version of Windows 10. If your computer runs an earlier version of Windows, such as Windows 7 or 8, you must content yourself with using Excel 2016, which is part of an Office 365 subscription has all the power of Excel 2019 but in a slightly different format. If such is the case, you need to put this book down now and instead pick up the Excel 2016 All-In-One For Dummies by Greg Harvey.
The truth is that understanding how to use the abundance of features offered by Excel 2019 is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work; in other words, “what’s in it for you.” I have endeavored to cover both the “how to” and “so what” aspects in all my discussions of Excel features, being as clear as possible and using as little tech-speak as possible.
Fortunately, Excel 2019 is well worth the effort to get to know because it’s definitely one of the best data-processing and analysis tools that has ever come along. Its Quick Analysis tool, Office Add-ins, Flash Fill, and Recommended Charts and PivotTables, along with the tried-and-true Live Preview feature and tons of ready-made galleries, make this version of the program the easiest to use ever. In short, Excel 2019 is a blast to use when you know what you’re doing, and my great hope is that this “fun” aspect of using the program comes through on every page (or, at least, every other page).
About This Book
As the name states, Excel 2019 All-in-One For Dummies is a reference (whether you keep it on your desk or use it to prop up your desk is your business). This means that although the chapters in each book are laid out in a logical order, each stands on its own, ready for you to dig into the information at any point.
As much as possible, I have endeavored to make the topics within each book and chapter stand on their own. When there’s just no way around relying on some information that’s discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you’re of a mind to.
Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You’ll find that although most topics are introduced in a conversational manner, I don’t waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).
Foolish Assumptions
I’m only going to make one foolish assumption about you, and that is that you have some need to use Microsoft Excel 2019 under Windows 10 in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.
As far as your hardware and software go, I’m assuming that you already have Excel 2019 (usually as part of Microsoft Office 2019) installed on your computing device, using a standard home or business installation running under Windows 10. I’m not assuming, however, that when you’re using Excel 2019 that you’re sitting in front of a large screen monitor and making cell entries and command selections with a physical keyboard or connected mouse. With the introduction of Microsoft’s Surface 4 tablet for Windows 10 and the support for a whole slew of different Windows tablets, you may well be entering data and selecting commands with your finger or stylus using the Windows Touch keyboard and Touch pointer.
To deal with the differences between using Excel 2019 on a standard desktop or laptop computer with access only to a physical keyboard and mouse and a touchscreen tablet or smartphone environment with access only to the virtual Touch keyboard, I’ve outlined the touchscreen equivalents to common commands you find throughout the text, such as “click,” “double-click,” “drag,” and so forth, in the section that explains selecting by touch in Book 1, Chapter 1.
Warning: This book is intended only for users of Microsoft Office Excel 2019! Because of the diversity of the devices that Excel 2019 runs on and the places where its files can be saved and used, if you’re using Excel 2007 or Excel 2010 for Windows, much of the file-related information in this book may only confuse and confound you. If you’re still using a version prior to Excel 2007, which introduced the Ribbon interface, this edition will be of no use to you because your version of the program works nothing like the 2016 version this book describes.
How This Book Is Organized
Excel 2019 All-in-One For Dummies is actually eight smaller books rolled into one. That way, you can go after the stuff in the particular book that really interests you at the time, putting all the rest of the material aside until you need to have a look at it. Each book in the volume consists of two or more chapters consisting of all the basic information you should need in dealing with that particular component or aspect of Excel.
In case you’re the least bit curious, here’s the lowdown on each of the eight books and what you can expect to find there.
Book 1: Excel Basics
This book is for those of you who’ve never had a formal introduction to the program’s basic workings. Chapter 1 covers all the orientation material including how to deal with the program’s Ribbon user interface. Of special interest may be the section selecting commands by touch if you’re using Excel 2019 on a Windows touchscreen device that isn’t equipped with either a physical keyboard or mouse.
Chapter 2 is not to be missed, even if you do not consider yourself a beginner by any stretch of the imagination. This chapter covers the many ways to customize Excel and make the program truly your own. It includes information on customizing the Quick Access toolbar as well as great information on how to use and procure add-in programs that can greatly extend Excel’s considerable features.
Book 2: Worksheet Design
Book 2 focuses on the crucial issue of designing worksheets in Excel. Chapter 1 takes up the call on how to do basic design and covers all the many ways of doing data entry (a subject that’s been made all the more exciting with the addition of voice and handwriting input).
Chapter 2 covers how to make your spreadsheet look professional and read the way you want it through formatting. Excel offers you a wide choice of formatting techniques, from the very simple formatting as a table all the way to the now very sophisticated and super-easy conditional formatting.
Chapter 3 takes up the vital subject of how to edit an existing spreadsheet without disturbing its design or contents. Editing can be intimidating to the new spreadsheet user because most spreadsheets contain not only data entries that you don’t want to mess up but also formulas that can go haywire if you make the wrong move.
Chapter 4 looks at the topic of managing the worksheets that contain the spreadsheet applications that you build in Excel. It opens the possibility of going beyond the two-dimensional worksheet with its innumerable columns and rows by organizing data three-dimensionally through the use of multiple worksheets. (Each Excel file already contains three blank worksheets to which you can add more.) This chapter also shows you how to work with and organize multiple worksheets given the limited screen real estate afforded by your monitor and how to combine data from different files and sheets when needed.
Chapter 5 is all about printing your spreadsheets, a topic that ranks only second in importance to knowing how to get the data into a worksheet in the first place. As you expect, you find out not only how to get the raw data to spit out of your printer but also how to gussy it up and make it into a professional report of which anyone would be proud.
Book 3: Formulas and Functions
This book is all about calculations and building the formulas that do them. Chapter 1 covers formula basics from doing the simplest addition to building array formulas and using Excel’s built-in functions courtesy of the Function Wizard. It also covers how to use different types of cell references when making formula copies and how to link formulas that span different worksheets.
Chapter 2 takes up the subject of preventing formula errors from occurring and, barring that, how to track them down and eliminate them from the spreadsheet. This chapter also includes information on circular references in formulas and how you can sometimes use them to your advantage.
Chapters 3 through 6 concentrate on how to use different types of built-in functions. Chapter 3 covers the use of date and time functions, not only so you know what day and time it is, but actually put this knowledge to good use in formulas that calculate elapsed time. Chapter 4 takes up the financial functions in Excel and shows you how you can use them to both reveal and determine the monetary health of your business. Chapter 5 is concerned with math and statistical functions (of which there are plenty). Chapter 6 introduces you to the powerful group of lookup, information, and text functions. Here, you find out how to build formulas that automate data entry by returning values from a lookup table, get the lowdown on any cell in the worksheet, and combine your favorite pieces of text.
Book 4: Worksheet Collaboration and Review
Book 4 looks at the ways you can share your spreadsheet data with others. Chapter 1 covers the important issue of security in your spreadsheets. Here, you find out how you can protect your data so that only those to whom you give permission can open or make changes to their contents.
Chapter 2 takes up the subject of building and using hyperlinks in your Excel spreadsheets (the same kind of links that you know and love on web pages on the World Wide Web). This chapter covers how to create hyperlinks for moving from worksheet to worksheet within the same Excel file as well as for opening other documents on your hard drive, or connecting to the Internet and browsing to a favorite web page.
Chapter 3 introduces Excel’s sophisticated features for sending out spreadsheets and having a team of people review and make comments on them. It also covers techniques for reviewing and reconciling the suggested changes.
Chapter 4 is concerned with sharing spreadsheet data with other programs that you use. It looks specifically at how you can share data with other Office 2019 programs, such as Microsoft Word, PowerPoint, and Outlook. This chapter also discusses the variety of ways to share your workbooks files online, all the way from inviting people to review or co-author them from your OneDrive or SharePoint site, attaching them to e-mail messages, and adding and sharing comments as an Adobe PDF (Portable Document Format) file, using the AdobePDF Maker add-in.
Book 5: Charts and Graphics
Book 5 focuses on the graphical aspects of Excel. Chapter 1 covers charting your spreadsheet data in some depth. Here, you find out not only how to create great-looking charts but also how to select the right type of chart for the data that you’re representing graphically.
Chapter 2 introduces you to all the other kinds of graphics that you can have in your spreadsheets. These include graphic objects that you draw as well as graphic images that you import, including clip art included in Microsoft Office, as well as digital pictures and images imported and created with other hardware and software connected to your computer.
Book 6: Data Management
Book 6 is concerned with the ins and outs of using Excel to maintain large amounts of data in what are known as databases or, more commonly, data lists. Chapter 1 gives you basic information on how to set up a data list and add your data to it. This chapter also gives you information on how to reorganize the data list through sorting and how to total its numerical data with the Subtotal feature.
Chapter 2 is all about how to filter the data and extract just the information you want out of it (a process officially known as querying the data). Here, you find out how to perform all sorts of filtering operations from the simplest, which involves relying upon the AutoFilter feature, to the more complex operations that use custom filters and specialized database functions. Finally, you find out how to perform queries on external data sources, such as those maintained with dedicated database management software for Windows, such as Microsoft Access or dBASE, as well as those that run on other operating systems, such as DB2 and Oracle.
Book 7: Data Analysis
Book 7 looks at the subject of data analysis with Excel; essentially how to use the program’s computational capabilities to project and predict possible future outcomes. Chapter 1 looks at the various ways to perform what-if scenarios in Excel. These include analyses with one- and two-input variable data tables, doing goal seeking, setting a series of different possible scenarios, and using the Solver add-in.
Chapter 2 is concerned with the topic of creating special data summaries called pivot tables that enable you to analyze large amounts of data in an extremely compact and modifiable format. Here, you find out how to create and manipulate pivot tables as well as build pivot charts that depict the summary information graphically. In addition, you’ll get an introduction to using the 3D Maps and ForeCast Sheet features as well as the Power Pivot for Excel Add-in to perform more sophisticated types of data analysis on the Data Model that’s represented in your Excel pivot table.
Book 8: Macros and VBA
Book 8 introduces the subject of customizing Excel through the use of its programming language called Visual Basic for Applications (VBA for short). Chapter 1 introduces you to the use of the macro recorder to record tasks that you routinely perform in Excel for later automated playback. When you use the macro recorder to record the sequence of routine actions (using the program’s familiar menus, toolbars, and dialog boxes), Excel automatically records the sequence in the VBA programming language.
Chapter 2 introduces you to editing VBA code in Excel’s programming editor known as the Visual Basic Editor. Here, you find out how to use the Visual Basic Editor to edit macros that you’ve recorded that need slight modifications as well as how to write new macros from scratch. You also find out how to use the Visual Basic Editor to write custom functions that perform just the calculations you need in your Excel spreadsheets.
Conventions Used in This Book
This book follows a number of different conventions modeled primarily after those used by Microsoft in its various online articles and help materials. These conventions deal primarily with Ribbon command sequences and shortcut or hot key sequences that you encounter.
Excel 2019 is a sophisticated program that uses the Ribbon interface first introduced in Excel 2007. In Chapter 1, I explain all about this Ribbon interface and how to get comfortable with its command structure. Throughout the book, you may find Ribbon command sequences using the shorthand developed by Microsoft whereby the name on the tab on the Ribbon and the command button you select are separated by arrows, as in
Home⇒ Copy
This is shorthand for the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it’s not already displayed) and then click the Copy button, which sports the traditional side-by-side page icon.
Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by vertical bars, as in
Formulas⇒ Calculation Options⇒ Manual
This is shorthand for the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it’s not already displayed) and then click the Calculation Options command button followed by the Manual drop-down menu option.
The book occasionally encourages you to type something specific into a specific cell in the worksheet. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, still have to press the Enter key or click the Enter button on the Formula bar to make the entry stick.
When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the status bar at the bottom of the screen. This book renders messages that you see onscreen like this:
CALCULATE
This is the message that tells you that Excel is in manual recalculation mode (after using the earlier Ribbon command sequence) and that one or more of the formulas in your worksheet are not up to date and are in sore need of recalculation.
Occasionally I give you a hot key combination that you can press in order to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or Ctrl+S. (Both of these hot key combos save workbook changes.)
With the Alt key combos, you press the Alt key until the hot key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start typing the hot key letters. (By the way, you type all lowercase hot key letters — I only put them in caps to make them stand out in the text.)
Hot key combos that use the Ctrl key are of an older vintage, and they work a little bit differently because, on a physical keyboard, you have to hold down the Ctrl key as you type the hot key letter. (Again, type only lowercase letters unless you see the Shift key in the sequence as in Ctrl+Shift+C.)
Finally, if you're really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, option buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.
Icons Used in This Book
The following icons are strategically placed in the margins throughout all eight books in this volume. Their purpose is to get your attention, and each has its own way of doing that.
This icon denotes some really cool information (in my humble opinion) that will pay off by making your work a lot more enjoyable or productive (or both).
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you may end up taking a detour that wastes valuable time.
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you’ll be sorry. I reserve this icon for those times when you can lose data and otherwise screw up your spreadsheet.
This icon denotes a tidbit that makes free use of (oh no!) technical jargon. You may want to skip these sections (or, at least, read them when no one else is around).
Beyond the Book
In addition to what you’re reading right now, this book comes with a free access-anywhere Cheat Sheet. To get this Cheat Sheet, go to www.dummies.com and search for “Excel 2019 All in One For Dummies Cheat Sheet” by using the Search box.
Where to Go from Here
The question of where to go from here couldn’t be simpler: Go to Chapter 1 and find out what you’re dealing with. Which book you go to after that is a matter of personal interest and need. Just go for the gold and don’t forget to have some fun while you’re digging!
Occasionally, Wiley’s technology books are updated. If this book has technical updates, they’ll be posted at www.dummies.com/go/excel2019aioupdates.
Book 1
Excel Basics
Contents at a Glance
Chapter 1: The Excel 2019 User Experience
Excel 2019’s Sleek Look and Feel
Excel’s Start Screen
Excel’s Ribbon User Interface
Getting Help
Launching and Quitting Excel
Chapter 2: Customizing Excel 2019
Tailoring the Quick Access Toolbar to Your Tastes
Exercising Your Options
Using Office Add-ins
Using Excel’s Own Add-ins
Chapter 1
The Excel 2019 User Experience
IN THIS CHAPTER
Getting to know Excel 2019’s Start screen and program window
Selecting commands from the Ribbon
Unpinning the Ribbon
Using Excel 2019 on a touchscreen device
Getting around the worksheet and workbook
Using Excel 2019’s Tell Me feature when you need help
Launching and quitting Excel
Excel 2019 relies primarily on the onscreen element called the Ribbon, which is the means by which you select the vast majority of Excel commands. In addition, Excel 2019 sports a single toolbar (the Quick Access toolbar), some context-sensitive buttons and command bars in the form of the Quick Analysis tool and mini-bar, along with a number of task panes (such as Clipboard, Research, Thesaurus, and Selection to name a few).
Among the features supported when selecting certain style and formatting commands is the Live Preview, which shows you how your actual worksheet data will appear in a particular font, table formatting, and so on before you actually apply it. Excel also supports an honest-to-goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet. Page Layout view has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. The Backstage view attached to the File tab on the Excel Ribbon enables you to get at-a-glance information about your spreadsheet files as well as save, share, preview, and print them. Last but not least, Excel 2019 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially with the program’s Live Preview.
Excel 2019’s Sleek Look and Feel
If you’re coming to Excel 2019 from Excel 2007 or Excel 2010, the first thing you notice about the Excel 2019 user interface is its comparatively flat (as though you’ve gone from 3-D to 2-D) and decidedly less colorful display. Gone entirely are the contoured command buttons and color-filled Ribbon and pull-down menu graphics along with any hint of the gradients and shading so prevalent in the earlier versions. The Excel 2019 screen is so stark that even its worksheet column and row borders lack any color, and the shading is reserved for only the columns and rows that are currently selected in the worksheet itself.
The look and feel for Excel 2019 (indeed, all the Office 2019 apps) is all part of the Windows 10 user experience. This latest version of the Windows operating system was developed primarily to work across a wide variety of devices from desktop and laptop to tablets and smartphones, devices with much smaller screen sizes and where touch often is the means of selecting and manipulating screen objects. With an eye toward making this touch experience as satisfying as possible on all these devices, Microsoft redesigned the interface of both its new operating system and Office 2019 application programs: It attempted to reduce the graphical complexity of many screen elements as well as make them as responsive as possible on touchscreen devices.
The result is a snappy Excel 2019, regardless of what kind of hardware you run it on. And the new, somewhat plainer and definitely flatter look, while adding to Excel 2019’s robustness on any device, takes nothing away from the program’s functionality.
The greatest thing about the look of Office 2019 is that each of its application programs features a different predominant color. Excel 2019 features a green color long associated with the program. Green appears throughout the program’s colored screen elements, including the Excel program and file icon, the Status bar, the outline of the cell pointer, the shading of highlighted and selected Ribbon tabs, and menu items. This is in stark contrast to the last few versions of Excel where the screen elements were all predominately blue, the color traditionally associated with Microsoft Word.
Excel’s Start Screen
When you first launch Excel 2019, the program welcomes you with an Excel Start screen similar to the one shown in Figure 1-1. This screen is divided into two panes.
FIGURE 1-1: The Excel 2019’s Start screen with the Home tab selected that appears immediately after launching the program.
The left green navigation pane with the Home icon selected contains New and Open items at the top and Account, Feedback, and Options at the bottom.
The right pane displays a single row of thumbnails showing some of the different templates you can use to create a new workbook at the top with a list of some of the most recently opened workbooks shown below. To see more templates to use in creating a new workbook, you can click the Find More in New link on the right side of the Home screen or the New icon in the navigation pane on the left.
To open an existing Excel workbook not displayed in the Recent list, click the Find More in Open link on the right side of the Home screen or the Open icon in the navigation pane on the left.
The first template thumbnail displayed on the top row of the Home tab on the Start screen is called Blank Workbook, and you select this thumbnail to start a new spreadsheet of your own design. The second thumbnail is called Welcome to Excel, and you select this thumbnail to open a workbook with ten worksheets that enable you to take a tour and play around with several of the nifty new features in Excel 2019.
I encourage you to take the time to open the Welcome to Excel template and explore its worksheets. When you click the Create button after clicking this thumbnail, Excel opens a new Welcome to Excel1 workbook where you can experiment with using the Flash Fill feature to fill in a series of data entries; the Quick Analysis tool to preview the formatting, charts, totals, pivot tables, and sparklines you can add to a table of data; and the Recommended Charts command to create a new chart, all with a minimum of effort. After you’re done experimenting with these features, you can close the workbook by choosing File⇒ Close or pressing Ctrl+W and then clicking the Don’t Save button in the alert dialog box that asks you whether you want to save your changes.
If none of the Excel templates shown in the Home screen fit the bill, click the Find More in New link to select New in the Navigation pane and display the New screen displaying a whole host of standard templates that you can select to use as the basis for new worksheets. These templates include templates for creating calendars, tracking projects, and creating invoices. (See Book 2, Chapter 1 for more on creating new workbooks from ready-made and custom templates.)
Excel’s Ribbon User Interface
When you first open a new, blank workbook by clicking the New Workbook thumbnail in the Home screen, Excel 2019 opens up a single worksheet (with the generic name, Sheet1) in a new workbook file (with the generic filename, Book1) inside a program window such as the one shown in Figure 1-2.
FIGURE 1-2: The Excel 2019 program window as it appears after first opening a blank workbook when both Ribbon tabs and commands are displayed.
The Excel program window containing this worksheet of the workbook is made up of the following components:
File menu button:
When clicked, Excel opens the Backstage view, which contains a bunch of file-related options including Info, New, Open, Save, Save As, Print, Share, Export, Publish, Close, and Account, as well as Options, which enables you to change Excel’s default settings.
Quick Access toolbar:
This toolbar consists of AutoSave, Save, Undo, and Redo. AutoSave is automatically engaged after you first manually save a workbook to your OneDrive or SharePoint website in the Cloud. You can click the Save, Undo, and Redo buttons to perform common tasks to save your workbook for the first time and save editing changes when AutoSave is not engaged and undo and redo editing changes. You can also click the Customize Quick Access Toolbar button to the immediate right of the Redo button to open a drop-down menu containing additional common commands such as New, Open, Quick Print, and so on, as well as to customize the toolbar, change its position, and minimize the Ribbon.
Ribbon:
Most Excel commands are contained on the Ribbon. They are arranged into a series of tabs ranging from Home through View.
Formula bar:
This displays the address of the current cell along with the contents of that cell.
Worksheet area:
This area contains all the cells of the current worksheet identified by column headings, which use letters along the top, and row headings, which use numbers along the left edge, with tabs for selecting new worksheets. You use a horizontal scroll bar on the bottom to move left and right through the sheet and a vertical scroll bar on the right edge to move up and down through the sheet.
Status bar:
This bar keeps you informed of the program’s current mode and any special keys you engage, and it enables you to select a new worksheet view and to zoom in and out on the worksheet.
When using Excel 2019 on a touchscreen device, the Ribbon Display Options are automatically set to Tabs (so that associated commands appear only when you tap a tab). To make it easier to select Ribbon commands with your finger or a stylus, you can add the Touch/Mouse Mode button to the Quick Access toolbar and simultaneously engage touch mode by tapping the Customize Quick Access Toolbar button before tapping Touch/Mouse Mode option on its drop-down menu. With touch mode engaged, Excel spreads out the tabs and their command buttons on the Ribbon. That way you have a fighting chance of correctly selecting them with your finger or stylus. On a touchscreen tablet such as the Microsoft Surface Pro tablet, Excel automatically adds a Draw tab to the Ribbon containing loads of inking options that enable you to modify settings for drawing with your finger, a stylus, or even the Surface Pen.
Going behind the scenes to Excel’s Backstage view
At the top of the Excel 2019 program window, immediately below the AutoSave button to the immediate left of the Save button on the Quick Access toolbar, you find the File menu button (the green one with “File” in white letters to the immediate left of the Home tab).
When you click the File menu button or Alt+F, the Excel Backstage view appears with the Home screen selected. The screen in this view contains a menu of file-related options running down a column on the left side and, depending upon which option is selected, some panels containing both at-a-glance information and further command options.
At first glance, the File menu button may appear to you like a Ribbon tab — especially in light of its rectangular shape and location immediately left of the Ribbon’s initial Home tab. Keep in mind, however, that this important file control is technically a command button that, when clicked, leads directly to a totally new, nonworksheet screen with the Backstage view. This screen has its own menu options but contains no Ribbon command buttons whatsoever.
After you click the File menu button to switch to the Backstage view, you can then click the Back button (with the left-pointing arrow) that appears above the Info menu item to return to the normal worksheet view or you can simply press the Esc key.
Getting the lowdown on the Info screen
When you click File⇒ Info at the top of File menu in the Backstage view, an Info screen similar to the one shown in Figure 1-3 appears.
FIGURE 1-3: The Excel Backstage view displaying the Info screen with permissions, distribution, version commands, and more.
On the left side of this Info screen, you find the following four command buttons:
Protect Workbook
to encrypt the Excel workbook file with a password, protect its contents, or verify the contents of the file with a digital signature (see Book 4,
Chapters 1
and
3
for more on protecting and signing your workbooks)
Inspect Workbook
to inspect the document for hidden
metadata
(data about the file) and check the file’s accessibility for folks with disabilities and compatibility with earlier versions of Excel (see Book 4,
Chapter 3
for details on using this feature)
Manage Workbook
to recover or delete draft versions saved with Excel’s AutoRecover feature (see Book 2,
Chapter 1
for more on using AutoRecover)
Browser View Options
to control what parts of the Excel workbook can be viewed and edited by users who view it online on the Web
On the right side of the Info screen, you see a list of various and sundry bits of information about the file:
Properties
lists the Size of the file as well as any Title, Tags, and Categories (to help identify the file when doing a search for the workbook) assigned to it. To edit or add to the Title, Tags, or Categories properties, click the appropriate text box and begin typing. To add or change additional file properties, including the Company, Comments, and Status properties, click the Properties drop-down button and then click Show Document Panel or Advanced Properties from its drop-down menu. Click Show Document Panel to open the Document panel in the regular worksheet window where you can edit properties such as Author, Title, Subject, and Keywords and to add comments. Click the Advanced Properties option to open the workbook’s Properties dialog box (with its General, Summary, Statistics, Contents, and Custom tabs) to change and review a ton of file properties. If the workbook file is new and you’ve never saved it on disk, the words “Not Saved Yet” appear after Size.
Related Dates
lists the date the file was Last Modified, Created, and Last Printed.
Related People
lists the name of the workbook’s author as well as the name of the person who last modified the file. To add an author to the workbook file, click the Add an Author link that appears beneath the name of the current author.
The Show All Properties link,
when clicked, expands the list of Properties to include text fields for Comments, Template, Status, Categories, Subject, Hyperlink Base, and Company that you can edit.
Sizing up other File menu options
Above the Info option at the very top of the File menu, you find the commands you commonly need for working with Excel workbook files, such as creating new workbook files as well as opening an existing workbook for editing. The New command displays a thumbnail list of all the available spreadsheet templates you can use to create a workbook. (See Book 2, Chapter 1 for more on creating and using workbook templates.)
Immediately below the Info option, you find a Save and Save As command. You generally use the Save command to manually save the changes you make to a workbook. You generally use the Save As command to saves changes in your workbook with a new filename and/or in a new location on your computer or in the Cloud (See Book 2, Chapter 1 for more on saving and closing files and Book 2, Chapter 3 for more on opening them.)
Beneath the Save As command you find the Print option that, when selected, displays a Print screen. This screen contains the document’s current print settings (that you can modify) on the left side and a preview area that shows you the pages of the printed worksheet report. (See Book 2, Chapter 5 for more on printing worksheets using the Print Settings panel in the Backstage view.)
Below the Print command you find the Share option, which displays a list of commands for sharing your workbook files online. Beneath this, you find an Export option used to open the Export screen, where you find options for converting your workbooks to other file types as well as controlling the browsing options when the workbook is viewed online in a web browser. (See Book 4, Chapter 4 for more about sharing workbook files online as well as converting them to other file formats.)
The Save as Adobe PDF enables you to save a copy of your workbook in Adobe’s open PDF (Portable Document Format) that gives coworkers and clients access to the workbook without having to open it in Excel. (All they need is the free Adobe Reader software on their computer.) The Export option contains an option that enables you to save the workbook in PDF format as well as on for saving the workbook in Microsoft’s alternate open file format called XPS (Open XML Paper Specification).
The Publish option enables you to save your Excel workbooks to a folder on your OneDrive for Business account and then publish it to Microsoft’s Power BI (Business Information) stand-alone application that enables you to create visual dashboards that highlight and help explain the story behind the worksheet data.
Checking user and product information on the Account screen
At the top of the section below the Close option that is used to close a workbook file (hopefully, after saving all your edits) on the File menu, you find the Account option. You can use this option to review account-related information on the Backstage Account screen. When displayed, the Account screen gives you both user and product information.
On the left side of the Account screen, your user information appears, including all the online services to which you’re currently connected. These services include social media sites such as Facebook, Twitter, and LinkedIn, as well as the more corporate services such as your OneDrive, SharePoint team site, and Office 365 account.
To add an online service to this list, click the Add a Service button at the bottom and select the service to add on the Images & Videos, Storage, and Sharing continuation menus. To manage which accounts appear on the list, highlight the name and click the Remove button to take it off the list. To manage the settings for a particular service, click the Manage button and then edit the settings online.
Use the Office Background drop-down list box that appears between your user information and the Connected Services list on the Account screen to change the pattern that appears in the background of the title bar of all your Office 2019 programs. By default, Office 2019 uses no background. You can change the background by clicking a new pattern from the Office Background drop-down menu on the Excel Account screen (and you can always switch back to have no pattern displayed by clicking No Background from the menu). Below this option, you see the Office Theme selection (White by default) that sets the overall color pattern you use. Just be aware that any change you make here affects the title areas of all the Office 2019 programs you run on your device (not just the Excel 2019 program window).
On the right side of the Account screen, you find the Subscription Product information. Here you can see the activation status of your Office programs as well as review the version number of Excel that is installed on your device. Because many Office 365 licenses allow up to five installations of Office 2019 on different devices (desktop computer, laptop, Windows tablet, and smartphone, for example), you can click the Manage Account link that appears to go online. There, you can check how many Office installations you still have available and, if need be, manage the devices on which Office 2019 is activated. If you need more installations for your company, you can use the Change License button to upgrade to another subscription plan that better fits your needs.
Ripping through the Ribbon
The Ribbon (shown in Figure 1-4) groups related commands together with the goal of showing you all the most commonly used options needed to perform a particular Excel task.
FIGURE 1-4: Excel’s Ribbon consists of a series of tabs containing command buttons arranged into different groups.
The Ribbon is made up of the following components:
Tabs:
Excel’s main tasks are brought together and display all the commands commonly needed to perform that core task.
Groups:
Related command buttons can be organized into subtasks normally performed as part of the tab’s larger core task.
Command buttons:
