| Introduction |
|
1 | (10) |
|
|
|
2 | (1) |
|
|
|
2 | (1) |
|
How This Book Is Organized |
|
|
3 | (4) |
|
|
|
3 | (1) |
|
|
|
4 | (1) |
|
Book 3: Formulas and Functions |
|
|
4 | (1) |
|
Book 4: Worksheet Collaboration and Review |
|
|
5 | (1) |
|
Book 5: Charts and Graphics |
|
|
5 | (1) |
|
|
|
6 | (1) |
|
|
|
6 | (1) |
|
|
|
7 | (1) |
|
Conventions Used in This Book |
|
|
7 | (2) |
|
|
|
9 | (1) |
|
|
|
9 | (1) |
|
|
|
10 | (1) |
| Book 1: Excel Basics |
|
11 | (68) |
|
Chapter 1 The Excel 2019 User Experience |
|
|
13 | (32) |
|
Excel 2019's Sleek Look and Feel |
|
|
14 | (1) |
|
|
|
15 | (1) |
|
Excel's Ribbon User Interface |
|
|
16 | (20) |
|
Going behind the scenes to Excel's Backstage view |
|
|
18 | (4) |
|
Ripping through the Ribbon |
|
|
22 | (6) |
|
Adjusting to the Quick Access toolbar |
|
|
28 | (1) |
|
Fooling around with the Formula bar |
|
|
29 | (1) |
|
What's up with the Worksheet area? |
|
|
30 | (5) |
|
Taking a tour of the Status bar |
|
|
35 | (1) |
|
|
|
36 | (3) |
|
Show-and-tell help with the Tell Me feature |
|
|
36 | (2) |
|
Getting Help from the Help tab on the Ribbon |
|
|
38 | (1) |
|
Launching and Quitting Excel |
|
|
39 | (6) |
|
Starting Excel from the Windows 10 Start menu |
|
|
40 | (1) |
|
Starting Excel from the Windows 10 Search text box |
|
|
41 | (1) |
|
Telling Cortana to Start Excel for you |
|
|
41 | (1) |
|
Starting Excel from the Windows 10 Metro view in Tablet mode |
|
|
41 | (1) |
|
|
|
42 | (3) |
|
Chapter 2 Customizing Excel 2019 |
|
|
45 | (34) |
|
Tailoring the Quick Access Toolbar to Your Tastes |
|
|
46 | (3) |
|
Adding Ribbon commands to the Quick Access toolbar |
|
|
46 | (2) |
|
Adding non-Ribbon commands to the Quick Access toolbar |
|
|
48 | (1) |
|
Adding macros to the Quick Access toolbar |
|
|
49 | (1) |
|
|
|
49 | (23) |
|
Changing some of the more universal settings on the General tab |
|
|
49 | (4) |
|
Changing common calculation options on the Formulas tab |
|
|
53 | (2) |
|
Digging the options on the Data tab |
|
|
55 | (1) |
|
Changing correction options on the Proofing tab |
|
|
56 | (2) |
|
Changing various save options on the Save tab |
|
|
58 | (3) |
|
Changing the Office 2019 language preferences |
|
|
61 | (1) |
|
Changing a whole lot of other common options on the Advanced tab |
|
|
62 | (6) |
|
Customizing the Excel 2019 Ribbon |
|
|
68 | (4) |
|
|
|
72 | (2) |
|
Using Excel's Own Add-ins |
|
|
74 | (7) |
|
Managing the standard Excel Add-ins |
|
|
77 | (1) |
|
Managing Excel COM add-ins |
|
|
77 | (1) |
|
Purchasing third-party add-ins |
|
|
78 | (1) |
| Book 2: Worksheet Design |
|
79 | (226) |
|
Chapter 1 Building Worksheets |
|
|
81 | (52) |
|
|
|
82 | (10) |
|
|
|
82 | (7) |
|
Designing a workbook from scratch |
|
|
89 | (3) |
|
It Takes All Kinds (Of Cell Entries) |
|
|
92 | (7) |
|
|
|
93 | (2) |
|
|
|
95 | (4) |
|
|
|
99 | (25) |
|
Data entry keyboard style |
|
|
100 | (1) |
|
Doing data entry with the Touch keyboard |
|
|
101 | (3) |
|
You AutoComplete this for me |
|
|
104 | (2) |
|
You AutoCorrect this right now! |
|
|
106 | (1) |
|
Constraining data entry to a cell range |
|
|
107 | (1) |
|
Getting Excel to put in the decimal point |
|
|
108 | (1) |
|
|
|
109 | (8) |
|
|
|
117 | (7) |
|
|
|
124 | (6) |
|
Saving workbooks in other commonly used file formats |
|
|
127 | (1) |
|
Changing the default file location |
|
|
128 | (1) |
|
Saving a new workbook in the old file format |
|
|
129 | (1) |
|
Document Recovery to the Rescue |
|
|
130 | (3) |
|
Chapter 2 Formatting Worksheets |
|
|
133 | (60) |
|
|
|
134 | (9) |
|
Selecting cells with the mouse |
|
|
135 | (2) |
|
|
|
137 | (1) |
|
Selecting cells with the keyboard |
|
|
137 | (1) |
|
You AutoSelect that range! |
|
|
138 | (3) |
|
Selecting cells with Go To |
|
|
141 | (1) |
|
|
|
142 | (1) |
|
Adjusting Columns and Rows |
|
|
143 | (5) |
|
You AutoFit the column to its contents |
|
|
144 | (1) |
|
Adjusting columns the old fashioned way |
|
|
145 | (1) |
|
Setting a new standard width |
|
|
146 | (1) |
|
Hiding out a column or two |
|
|
146 | (1) |
|
|
|
147 | (1) |
|
Formatting Tables from the Ribbon |
|
|
148 | (4) |
|
Formatting Tables with the Quick Analysis Tool |
|
|
152 | (1) |
|
Formatting Cells from the Ribbon |
|
|
153 | (3) |
|
Formatting Cell Ranges with the Mini-Toolbar |
|
|
156 | (2) |
|
Using the Format Cells Dialog Box |
|
|
158 | (19) |
|
|
|
158 | (8) |
|
|
|
166 | (5) |
|
|
|
171 | (2) |
|
Basic borders, fills, and patterns |
|
|
173 | (4) |
|
Hiring Out the Format Painter |
|
|
177 | (1) |
|
|
|
178 | (5) |
|
Using the Number Format cell styles |
|
|
178 | (2) |
|
Defining a custom cell style by example |
|
|
180 | (1) |
|
Creating a new cell style from scratch |
|
|
181 | (1) |
|
Merging styles into other workbooks |
|
|
182 | (1) |
|
|
|
183 | (10) |
|
Graphical conditional formatting |
|
|
183 | (2) |
|
Formatting with the Quick Analysis tool |
|
|
185 | (1) |
|
Identifying particular values or text entries in a cell range |
|
|
186 | (2) |
|
Highlighting duplicate values in a cell range |
|
|
188 | (1) |
|
Creating your own conditional formatting rules |
|
|
189 | (1) |
|
Managing conditional formatting rules |
|
|
190 | (3) |
|
Chapter 3 Editing and Proofing Worksheets |
|
|
193 | (48) |
|
|
|
194 | (7) |
|
Using the Open screen in the Backstage view |
|
|
194 | (4) |
|
Using the Open dialog box |
|
|
198 | (2) |
|
Opening more than one workbook at a time |
|
|
200 | (1) |
|
Finding misplaced workbooks |
|
|
200 | (1) |
|
Using the other Open options |
|
|
200 | (1) |
|
|
|
201 | (8) |
|
|
|
202 | (3) |
|
|
|
205 | (2) |
|
Can I just squeeze this in here? |
|
|
207 | (2) |
|
A Spreadsheet with a View |
|
|
209 | (7) |
|
|
|
209 | (3) |
|
|
|
212 | (2) |
|
|
|
214 | (2) |
|
Copying and Moving Stuff Around |
|
|
216 | (11) |
|
Doing it with drag-and-drop |
|
|
217 | (2) |
|
Carried away with cut-and-paste |
|
|
219 | (8) |
|
Find and Replace This Disgrace! |
|
|
227 | (5) |
|
|
|
227 | (3) |
|
Finding and replacing stuff |
|
|
230 | (2) |
|
|
|
232 | (4) |
|
Changing the spelling options |
|
|
234 | (1) |
|
Adding words to the custom dictionary |
|
|
234 | (2) |
|
Looking Up and Translating Stuff |
|
|
236 | (1) |
|
|
|
237 | (1) |
|
Eliminating Errors with Text to Speech |
|
|
238 | (3) |
|
Chapter 4 Managing Worksheets |
|
|
241 | (36) |
|
Reorganizing the Worksheet |
|
|
242 | (15) |
|
Inserting and deleting columns and rows |
|
|
242 | (1) |
|
Eradicating columns and rows |
|
|
243 | (1) |
|
Adding new columns and rows |
|
|
244 | (1) |
|
Splitting the worksheet into panes |
|
|
245 | (3) |
|
|
|
248 | (9) |
|
Reorganizing the Workbook |
|
|
257 | (10) |
|
|
|
258 | (1) |
|
|
|
259 | (2) |
|
Adding and deleting sheets |
|
|
261 | (1) |
|
|
|
262 | (1) |
|
|
|
262 | (1) |
|
"Now you see them; now you don't" |
|
|
263 | (1) |
|
Opening windows on different sheets |
|
|
264 | (3) |
|
Working with Multiple Workbooks |
|
|
267 | (3) |
|
Comparing windows on different workbooks |
|
|
268 | (1) |
|
Transferring data between open windows |
|
|
268 | (1) |
|
Transferring sheets from one workbook to another |
|
|
269 | (1) |
|
|
|
270 | (7) |
|
Consolidating by position |
|
|
272 | (3) |
|
Consolidating by category |
|
|
275 | (1) |
|
Linking consolidated data |
|
|
275 | (2) |
|
Chapter 5
Chapter Printing Worksheets |
|
|
277 | (28) |
|
Printing from the Excel 2019 Backstage View |
|
|
278 | (6) |
|
Selecting the printer to use |
|
|
280 | (1) |
|
|
|
281 | (1) |
|
Checking the paging in Page Layout view |
|
|
281 | (1) |
|
Previewing the pages of the report |
|
|
282 | (2) |
|
Quick Printing the Worksheet |
|
|
284 | (1) |
|
Working with the Page Setup Options |
|
|
285 | (8) |
|
Using the buttons in the Page Setup group |
|
|
285 | (7) |
|
Using the buttons in the Scale to Fit group |
|
|
292 | (1) |
|
Using the Print Options on the Sheet tab of the Page Setup dialog box |
|
|
293 | (1) |
|
|
|
294 | (7) |
|
Adding a ready-made header or footer |
|
|
295 | (2) |
|
Creating a custom header or footer |
|
|
297 | (4) |
|
Solving Page Break Problems |
|
|
301 | (2) |
|
Printing the Formulas in a Report |
|
|
303 | (2) |
| Book 3: Formulas And Functions |
|
305 | (140) |
|
Chapter 1 Building Basic Formulas |
|
|
307 | (40) |
|
|
|
308 | (13) |
|
|
|
308 | (1) |
|
|
|
309 | (1) |
|
When you AutoSum numbers in a spreadsheet |
|
|
310 | (2) |
|
Totals and sums with the Quick Analysis tool |
|
|
312 | (2) |
|
Building formulas with computational operators |
|
|
314 | (3) |
|
Using the Insert Function button |
|
|
317 | (4) |
|
|
|
321 | (6) |
|
|
|
324 | (2) |
|
A mixed bag of references |
|
|
326 | (1) |
|
|
|
327 | (5) |
|
Building an array formula |
|
|
329 | (2) |
|
|
|
331 | (1) |
|
|
|
332 | (9) |
|
|
|
333 | (1) |
|
Naming constants and formulas |
|
|
334 | (1) |
|
Using names in building formulas |
|
|
335 | (1) |
|
Creating names from column and row headings |
|
|
336 | (2) |
|
|
|
338 | (1) |
|
Applying names to existing formulas |
|
|
339 | (2) |
|
|
|
341 | (2) |
|
Controlling Formula Recalculation |
|
|
343 | (2) |
|
|
|
345 | (2) |
|
Chapter 2 Logical Functions and Error Trapping |
|
|
347 | (24) |
|
Understanding Error Values |
|
|
348 | (1) |
|
|
|
349 | (4) |
|
Evaluating the many talents of the IFS function |
|
|
351 | (1) |
|
|
|
352 | (1) |
|
|
|
353 | (3) |
|
Whiting-Out Errors with Conditional Formatting |
|
|
356 | (2) |
|
|
|
358 | (12) |
|
|
|
359 | (4) |
|
|
|
363 | (1) |
|
|
|
363 | (2) |
|
Changing the Error Checking options |
|
|
365 | (1) |
|
|
|
366 | (2) |
|
|
|
368 | (2) |
|
Removing Errors from the Printout |
|
|
370 | (1) |
|
Chapter 3 Date and Time Formulas |
|
|
371 | (16) |
|
Understanding Dates and Times |
|
|
371 | (3) |
|
Changing the Regional date settings |
|
|
372 | (1) |
|
Building formulas that calculate elapsed dates |
|
|
373 | (1) |
|
Building formulas that calculate elapsed times |
|
|
373 | (1) |
|
|
|
374 | (9) |
|
|
|
374 | (1) |
|
|
|
375 | (2) |
|
DAY, WEEKDAY, MONTH, and YEAR |
|
|
377 | (1) |
|
|
|
378 | (1) |
|
Other special Date functions |
|
|
379 | (4) |
|
|
|
383 | (4) |
|
|
|
383 | (1) |
|
|
|
383 | (1) |
|
|
|
384 | (3) |
|
Chapter 4 Financial Formulas |
|
|
387 | (14) |
|
|
|
387 | (1) |
|
The PV, NPV, and FV Functions |
|
|
388 | (4) |
|
Calculating the Present Value |
|
|
388 | (2) |
|
Calculating the Net Present Value |
|
|
390 | (1) |
|
Calculating the Future Value |
|
|
391 | (1) |
|
|
|
392 | (3) |
|
|
|
395 | (3) |
|
Analysis ToolPak Financial Functions |
|
|
398 | (3) |
|
Chapter 5 Math and Statistical Formulas |
|
|
401 | (20) |
|
|
|
402 | (10) |
|
|
|
402 | (4) |
|
|
|
406 | (2) |
|
|
|
408 | (1) |
|
|
|
408 | (4) |
|
|
|
412 | (9) |
|
|
|
412 | (1) |
|
|
|
413 | (6) |
|
Using specialized statistical functions |
|
|
419 | (2) |
|
Chapter 6 Lookup, Information, and Text Formulas |
|
|
421 | (24) |
|
|
|
422 | (11) |
|
Looking up a single value with VLOOKUP and HLOOKUP |
|
|
422 | (4) |
|
Performing a two-way lookup |
|
|
426 | (3) |
|
|
|
429 | (4) |
|
|
|
433 | (5) |
|
Getting specific information about a cell |
|
|
433 | (3) |
|
|
|
436 | (1) |
|
|
|
437 | (1) |
|
|
|
438 | (9) |
|
|
|
438 | (2) |
|
|
|
440 | (1) |
|
|
|
441 | (2) |
|
|
|
443 | (2) |
| Book 4: Worksheet Collaboration And Review |
|
445 | (72) |
|
Chapter 1 Protecting Workbooks and Worksheet Data |
|
|
447 | (22) |
|
Password-Protecting the File |
|
|
448 | (6) |
|
Protecting the workbook when saving the file |
|
|
448 | (3) |
|
Assigning a password to open from the Info screen |
|
|
451 | (1) |
|
Entering the password to gain access |
|
|
452 | (1) |
|
Entering the password to make changes |
|
|
453 | (1) |
|
Changing or deleting a password |
|
|
454 | (1) |
|
|
|
454 | (15) |
|
Changing a cell's Locked and Hidden Protection formatting |
|
|
455 | (1) |
|
|
|
456 | (4) |
|
Enabling cell range editing by certain users |
|
|
460 | (4) |
|
Doing data entry in the unlocked cells of a protected worksheet |
|
|
464 | (2) |
|
|
|
466 | (3) |
|
Chapter 2 Using Hyperlinks |
|
|
469 | (8) |
|
|
|
469 | (6) |
|
|
|
470 | (3) |
|
|
|
473 | (1) |
|
|
|
473 | (2) |
|
Using the HYPERLINK Function |
|
|
475 | (2) |
|
Chapter 3 Preparing a Workbook for Distribution |
|
|
477 | (14) |
|
Getting Your Workbook Ready for Review |
|
|
478 | (6) |
|
Adding properties to a workbook |
|
|
479 | (1) |
|
Digitally signing a document |
|
|
479 | (5) |
|
|
|
484 | (7) |
|
|
|
484 | (3) |
|
Marking up a worksheet with digital ink |
|
|
487 | (4) |
|
Chapter 4 Sharing Workbooks and Worksheet Data |
|
|
491 | (26) |
|
Sharing Your Workbooks Online |
|
|
492 | (11) |
|
Sharing workbooks saved on your OneDrive |
|
|
493 | (3) |
|
|
|
496 | (1) |
|
Sending Workbooks as Adobe PDF Files for Shared Commenting |
|
|
497 | (2) |
|
Editing worksheets in Excel Online |
|
|
499 | (4) |
|
Excel 2019 Data Sharing Basics |
|
|
503 | (8) |
|
|
|
504 | (5) |
|
Excel and PowerPoint 2019 |
|
|
509 | (2) |
|
Exporting Workbooks to Other Usable File Formats |
|
|
511 | (8) |
|
Saving and exporting worksheets as PDF files |
|
|
511 | (2) |
|
Saving worksheets as XPS files |
|
|
513 | (1) |
|
Saving worksheets as ODS files |
|
|
514 | (1) |
|
Saving worksheets as HTML files |
|
|
514 | (3) |
| Book 5: Charts And Graphics |
|
517 | (66) |
|
Chapter 1 Charting Worksheet Data |
|
|
519 | (34) |
|
|
|
520 | (26) |
|
Embedded charts versus charts on separate chart sheets |
|
|
521 | (1) |
|
Inserting recommended charts |
|
|
522 | (1) |
|
Inserting specific chart types from the Ribbon |
|
|
523 | (2) |
|
Inserting charts with the Quick Analysis tool |
|
|
525 | (2) |
|
Creating a chart on a separate chart sheet |
|
|
527 | (1) |
|
Refining the chart from the Design tab |
|
|
528 | (5) |
|
Customizing chart elements from the Format tab |
|
|
533 | (2) |
|
Customizing the elements of a chart |
|
|
535 | (4) |
|
Formatting elements of a chart |
|
|
539 | (6) |
|
Saving a customized chart as a template |
|
|
545 | (1) |
|
Adding Sparkline Graphics to a Worksheet |
|
|
546 | (2) |
|
Adding Infographics to a Worksheet |
|
|
548 | (4) |
|
|
|
552 | (1) |
|
Chapter 2 Adding Graphic Objects |
|
|
553 | (30) |
|
|
|
554 | (8) |
|
|
|
554 | (3) |
|
Moving graphic objects to new layers |
|
|
557 | (1) |
|
|
|
558 | (2) |
|
|
|
560 | (1) |
|
Managing graphic objects in the Selection task pane |
|
|
561 | (1) |
|
Inserting Different Types of Graphics |
|
|
562 | (7) |
|
Inserting 2-D online images |
|
|
563 | (1) |
|
Inserting 3-D online images |
|
|
564 | (1) |
|
|
|
565 | (1) |
|
|
|
566 | (1) |
|
Formatting photos and line art pictures |
|
|
566 | (2) |
|
Formatting 3-D model images |
|
|
568 | (1) |
|
|
|
569 | (11) |
|
Drawing predefined shapes |
|
|
569 | (1) |
|
|
|
570 | (5) |
|
|
|
575 | (2) |
|
Inserting SmartArt graphics |
|
|
577 | (3) |
|
Adding Screenshots of the Windows 10 Desktop |
|
|
580 | (1) |
|
|
|
581 | (2) |
| Book 6: Data Management |
|
583 | (64) |
|
Chapter 1 Building and Maintaining Data Lists |
|
|
585 | (24) |
|
|
|
586 | (10) |
|
Designing the basic data list |
|
|
586 | (3) |
|
Add new records to a data list |
|
|
589 | (6) |
|
Eliminating records with duplicate fields |
|
|
595 | (1) |
|
|
|
596 | (9) |
|
Sorting records on a single field |
|
|
597 | (1) |
|
Sorting records on multiple fields |
|
|
597 | (4) |
|
Sorting the columns of a data list |
|
|
601 | (3) |
|
Sorting a data list on font and fill colors and cell icons |
|
|
604 | (1) |
|
|
|
605 | (4) |
|
Chapter 2 Filtering and Querying a Data List |
|
|
609 | (38) |
|
|
|
610 | (1) |
|
|
|
611 | (17) |
|
|
|
611 | (9) |
|
Using the Advanced Filter |
|
|
620 | (8) |
|
Using the Database Functions |
|
|
628 | (3) |
|
|
|
631 | (18) |
|
Retrieving data from Access database tables |
|
|
633 | (4) |
|
Retrieving data from the web |
|
|
637 | (1) |
|
Retrieving data from text files |
|
|
638 | (4) |
|
Querying data from other data sources |
|
|
642 | (1) |
|
Transforming a data query in the Power Query Editor |
|
|
643 | (4) |
| Book 7: Data Analysis |
|
647 | (72) |
|
Chapter 1 Performing What-If Scenarios |
|
|
649 | (24) |
|
|
|
650 | (6) |
|
Creating a one-variable data table |
|
|
651 | (3) |
|
Creating a two-variable data table |
|
|
654 | (2) |
|
Exploring Different Scenarios |
|
|
656 | (6) |
|
|
|
656 | (4) |
|
Producing a summary report |
|
|
660 | (2) |
|
|
|
662 | (2) |
|
|
|
664 | (9) |
|
Setting up and defining the problem |
|
|
665 | (3) |
|
|
|
668 | (1) |
|
Changing the Solver options |
|
|
669 | (1) |
|
Saving and loading a model problem |
|
|
670 | (1) |
|
|
|
671 | (2) |
|
Chapter 2 Performing Large-Scale Data Analysis |
|
|
673 | (46) |
|
|
|
674 | (9) |
|
Pivot tables with the Quick Analysis tool |
|
|
674 | (3) |
|
|
|
677 | (1) |
|
Manually created pivot tables |
|
|
678 | (5) |
|
|
|
683 | (5) |
|
Refining the pivot table layout and style |
|
|
684 | (1) |
|
Formatting the parts of the pivot table |
|
|
685 | (3) |
|
Sorting and Filtering the Pivot Table Data |
|
|
688 | (5) |
|
|
|
688 | (1) |
|
Filtering individual Column and Row fields |
|
|
689 | (1) |
|
Slicing the pivot table data |
|
|
689 | (2) |
|
|
|
691 | (2) |
|
|
|
693 | (1) |
|
Modifying the Pivot Table |
|
|
693 | (6) |
|
Changing the summary functions |
|
|
695 | (1) |
|
|
|
696 | (2) |
|
Changing the pivot table options |
|
|
698 | (1) |
|
|
|
699 | (3) |
|
Moving a pivot chart to its own sheet |
|
|
700 | (1) |
|
|
|
701 | (1) |
|
|
|
701 | (1) |
|
Using the Power Pivot Add-in |
|
|
702 | (7) |
|
Data modeling with Power Pivot |
|
|
703 | (2) |
|
Switching between the Data View and Diagram View |
|
|
705 | (2) |
|
Adding calculated columns courtesy of DAX |
|
|
707 | (2) |
|
|
|
709 | (5) |
|
Creating Forecast Worksheets |
|
|
714 | (5) |
| Book 8: Macros And VBA |
|
719 | (38) |
|
Chapter 1 Recording and Running Macros |
|
|
721 | (12) |
|
|
|
722 | (6) |
|
|
|
722 | (5) |
|
|
|
727 | (1) |
|
Assigning Macros to the Ribbon and the Quick Access Toolbar |
|
|
728 | (2) |
|
Adding your macros to a custom tab on the Ribbon |
|
|
728 | (1) |
|
Adding your macros to custom buttons on the Quick Access toolbar |
|
|
729 | (1) |
|
|
|
730 | (3) |
|
Chapter 2 VBA Programming |
|
|
733 | (24) |
|
Using the Visual Basic Editor |
|
|
734 | (13) |
|
|
|
736 | (10) |
|
Writing new macros in the Visual Basic Editor |
|
|
746 | (1) |
|
Creating Custom Excel Functions |
|
|
747 | (10) |
|
Adding a description to a user-defined function |
|
|
750 | (1) |
|
Using a custom function in your spreadsheet |
|
|
751 | (2) |
|
Saving custom functions in add-in files |
|
|
753 | (4) |
| Index |
|
757 | |
