| Introduction |
|
1 | (10) |
|
|
|
1 | (1) |
|
|
|
2 | (1) |
|
How This Book Is Organized |
|
|
3 | (4) |
|
|
|
3 | (1) |
|
Book II: Worksheet Design |
|
|
3 | (1) |
|
Book III: Formulas and Functions |
|
|
4 | (1) |
|
Book IV: Worksheet Collaboration and Review |
|
|
5 | (1) |
|
Book V: Charts and Graphics |
|
|
5 | (1) |
|
|
|
6 | (1) |
|
|
|
6 | (1) |
|
Book VIII: Macros and VBA |
|
|
6 | (1) |
|
Conventions Used in This Book |
|
|
7 | (1) |
|
|
|
8 | (1) |
|
|
|
9 | (2) |
| Book I: Excel Basics |
|
11 | (64) |
|
Chapter 1 The Excel 2016 User Experience |
|
|
13 | (30) |
|
Excel 2016's Sleek Look and Feel |
|
|
13 | (1) |
|
|
|
14 | (2) |
|
Excel's Ribbon User Interface |
|
|
16 | (19) |
|
Going behind the scenes to Excel's Backstage view |
|
|
17 | (4) |
|
Ripping through the Ribbon |
|
|
21 | (5) |
|
Adjusting to the Quick Access toolbar |
|
|
26 | (2) |
|
Fooling around with the Formula bar |
|
|
28 | (1) |
|
What's up with the Worksheet area? |
|
|
29 | (5) |
|
Taking a tour of the status bar |
|
|
34 | (1) |
|
|
|
35 | (2) |
|
Show-and-tell help with the Tell Me feature |
|
|
35 | (1) |
|
Using the Excel online help |
|
|
36 | (1) |
|
Launching and Quitting Excel |
|
|
37 | (6) |
|
Starting Excel from the Windows 10 Start menu |
|
|
38 | (1) |
|
Starting Excel from the Windows 10 Ask Me Anything text box |
|
|
39 | (1) |
|
Telling Cortana to Start Excel 2016 for you |
|
|
39 | (1) |
|
Starting Excel from the Windows 8 Start screen |
|
|
39 | (1) |
|
Starting Excel from the Windows 7 Start menu |
|
|
40 | (1) |
|
|
|
41 | (2) |
|
Chapter 2 Customizing Excel 2016 |
|
|
43 | (32) |
|
Tailoring the Quick Access Toolbar to Your Tastes |
|
|
43 | (4) |
|
Adding Ribbon commands to the Quick Access toolbar |
|
|
44 | (1) |
|
Adding non-Ribbon commands to the Quick Access toolbar |
|
|
45 | (1) |
|
Adding macros to the Quick Access toolbar |
|
|
46 | (1) |
|
|
|
47 | (21) |
|
Changing some of the more universal settings on the General tab |
|
|
47 | (3) |
|
Changing common calculation options on the Formulas tab |
|
|
50 | (2) |
|
Changing correction options on the Proofing tab |
|
|
52 | (1) |
|
Changing various save options on the Save tab |
|
|
53 | (3) |
|
Changing the Office 2016 language preferences |
|
|
56 | (1) |
|
Changing a whole lot of other common options on the Advanced tab |
|
|
57 | (6) |
|
Customizing the Excel 2016 Ribbon |
|
|
63 | (5) |
|
|
|
68 | (2) |
|
Using Excel's Own Add-Ins |
|
|
70 | (7) |
|
Managing the standard Excel add-ins |
|
|
72 | (1) |
|
Managing Excel COM add-ins |
|
|
73 | (1) |
|
Purchasing third-party add-ins |
|
|
74 | (1) |
| Book II: Worksheet Design |
|
75 | (222) |
|
Chapter 1 Building Worksheets |
|
|
77 | (50) |
|
|
|
77 | (10) |
|
|
|
78 | (6) |
|
Designing a workbook from scratch |
|
|
84 | (3) |
|
It Takes All Kinds (Of Cell Entries) |
|
|
87 | (7) |
|
|
|
88 | (2) |
|
|
|
90 | (4) |
|
|
|
94 | (23) |
|
Data entry keyboard style |
|
|
95 | (1) |
|
Doing data entry with the Touch keyboard |
|
|
96 | (2) |
|
You AutoComplete this for me |
|
|
98 | (1) |
|
You AutoCorrect this right now! |
|
|
99 | (2) |
|
Constraining data entry to a cell range |
|
|
101 | (1) |
|
Getting Excel to put in the decimal point |
|
|
102 | (1) |
|
|
|
102 | (15) |
|
|
|
117 | (7) |
|
Saving workbooks in other commonly used file formats |
|
|
120 | (2) |
|
Changing the default file location |
|
|
122 | (1) |
|
Saving a new workbook in the old file format |
|
|
123 | (1) |
|
Document Recovery to the Rescue |
|
|
124 | (3) |
|
Chapter 2 Formatting Worksheets |
|
|
127 | (58) |
|
|
|
128 | (8) |
|
Selecting cells with the mouse |
|
|
129 | (2) |
|
|
|
131 | (1) |
|
Selecting cells with the keyboard |
|
|
131 | (1) |
|
You AutoSelect that range! |
|
|
132 | (2) |
|
Selecting cells with Go To |
|
|
134 | (1) |
|
|
|
135 | (1) |
|
Adjusting Columns and Rows |
|
|
136 | (5) |
|
You AutoFit the column to its contents |
|
|
137 | (1) |
|
Adjusting columns the old fashioned way |
|
|
138 | (1) |
|
Setting a new standard width |
|
|
139 | (1) |
|
Hiding out a column or two |
|
|
139 | (1) |
|
|
|
140 | (1) |
|
Formatting Tables from the Ribbon |
|
|
141 | (4) |
|
Formatting Tables with the Quick Analysis Tool |
|
|
145 | (1) |
|
Formatting Cells from the Ribbon |
|
|
146 | (4) |
|
Formatting Cell Ranges with the Mini-Toolbar |
|
|
150 | (1) |
|
Using the Format Cells Dialog Box |
|
|
151 | (20) |
|
|
|
151 | (8) |
|
|
|
159 | (4) |
|
|
|
163 | (3) |
|
Basic borders, fills, and patterns |
|
|
166 | (5) |
|
Hiring Out the Format Painter |
|
|
171 | (1) |
|
|
|
172 | (4) |
|
Using the Number Format cell styles |
|
|
173 | (1) |
|
Defining a custom cell style by example |
|
|
173 | (1) |
|
Creating a new cell style from scratch |
|
|
174 | (1) |
|
Merging styles into other workbooks |
|
|
175 | (1) |
|
|
|
176 | (9) |
|
Graphical conditional formatting |
|
|
177 | (1) |
|
Formatting with the Quick Analysis tool |
|
|
178 | (1) |
|
Identifying particular values or text entries in a cell range |
|
|
178 | (4) |
|
Highlighting duplicate values in a cell range |
|
|
182 | (1) |
|
Creating your own conditional formatting rules |
|
|
182 | (1) |
|
Managing conditional formatting rules |
|
|
183 | (2) |
|
Chapter 3 Editing and Proofing Worksheets |
|
|
185 | (48) |
|
|
|
186 | (6) |
|
Using the Open screen in the Backstage view |
|
|
186 | (3) |
|
Using the Open dialog box |
|
|
189 | (2) |
|
Opening more than one workbook at a time |
|
|
191 | (1) |
|
Finding misplaced workbooks |
|
|
191 | (1) |
|
Using the other Open options |
|
|
192 | (1) |
|
|
|
192 | (8) |
|
|
|
194 | (2) |
|
|
|
196 | (2) |
|
Can I just squeeze this in here? |
|
|
198 | (2) |
|
A Spreadsheet with a View |
|
|
200 | (7) |
|
|
|
201 | (2) |
|
|
|
203 | (3) |
|
|
|
206 | (1) |
|
Copying and Moving Stuff Around |
|
|
207 | (11) |
|
Doing it with drag-and-drop |
|
|
208 | (2) |
|
Carried away with cut-and-paste |
|
|
210 | (8) |
|
Find and Replace This Disgrace! |
|
|
218 | (5) |
|
|
|
218 | (3) |
|
Finding and replacing stuff |
|
|
221 | (2) |
|
|
|
223 | (4) |
|
Changing the spelling options |
|
|
225 | (1) |
|
Adding words to the custom dictionary |
|
|
225 | (2) |
|
Looking Up and Translating Stuff |
|
|
227 | (1) |
|
|
|
227 | (2) |
|
Eliminating Errors with Text to Speech |
|
|
229 | (4) |
|
Chapter 4 Managing Worksheets |
|
|
233 | (36) |
|
Reorganizing the Worksheet |
|
|
233 | (16) |
|
Inserting and deleting columns and rows |
|
|
234 | (1) |
|
Eradicating columns and rows |
|
|
235 | (1) |
|
Adding new columns and rows |
|
|
236 | (1) |
|
Splitting the worksheet into panes |
|
|
236 | (4) |
|
|
|
240 | (9) |
|
Reorganizing the Workbook |
|
|
249 | (11) |
|
|
|
250 | (1) |
|
|
|
251 | (2) |
|
Adding and deleting sheets |
|
|
253 | (1) |
|
|
|
254 | (1) |
|
|
|
254 | (1) |
|
"Now you see them; now you don't" |
|
|
255 | (1) |
|
Opening windows on different sheets |
|
|
256 | (4) |
|
Working with Multiple Workbooks |
|
|
260 | (3) |
|
Comparing windows on different workbooks |
|
|
260 | (1) |
|
Transferring data between open windows |
|
|
260 | (1) |
|
Transferring sheets from one workbook to another |
|
|
261 | (2) |
|
|
|
263 | (6) |
|
Consolidating by position |
|
|
264 | (4) |
|
Consolidating by category |
|
|
268 | (1) |
|
Linking consolidated data |
|
|
268 | (1) |
|
Chapter 5 Printing Worksheets |
|
|
269 | (28) |
|
Printing from the Excel 2016 Backstage View |
|
|
270 | (6) |
|
Selecting the printer to use |
|
|
271 | (1) |
|
|
|
272 | (1) |
|
Checking the paging in Page Layout view |
|
|
273 | (1) |
|
Previewing the pages of the report |
|
|
274 | (2) |
|
Quick Printing the Worksheet |
|
|
276 | (1) |
|
Working with the Page Setup Options |
|
|
277 | (8) |
|
Using the buttons in the Page Setup group |
|
|
278 | (6) |
|
Using the buttons in the Scale to Fit group |
|
|
284 | (1) |
|
Using the Print buttons in the Sheet Options group |
|
|
285 | (1) |
|
|
|
285 | (7) |
|
Adding a ready-made header or footer |
|
|
286 | (2) |
|
Creating a custom header or footer |
|
|
288 | (4) |
|
Solving Page Break Problems |
|
|
292 | (3) |
|
Printing the Formulas in a Report |
|
|
295 | (2) |
| Book III: Formulas and Functions |
|
297 | (138) |
|
Chapter 1 Building Basic Formulas |
|
|
299 | (42) |
|
|
|
299 | (14) |
|
|
|
300 | (1) |
|
|
|
301 | (1) |
|
When you AutoSum numbers in a spreadsheet |
|
|
302 | (2) |
|
Totals and sums with the Quick Analysis tool |
|
|
304 | (1) |
|
Building formulas with operators |
|
|
305 | (4) |
|
Using the Insert Function button |
|
|
309 | (4) |
|
|
|
313 | (6) |
|
|
|
316 | (2) |
|
A mixed bag of references |
|
|
318 | (1) |
|
|
|
319 | (5) |
|
Building an array formula |
|
|
321 | (2) |
|
|
|
323 | (1) |
|
|
|
324 | (10) |
|
|
|
325 | (1) |
|
Naming constants and formulas |
|
|
326 | (1) |
|
Using names in building formulas |
|
|
327 | (1) |
|
Creating names from column and row headings |
|
|
328 | (2) |
|
|
|
330 | (1) |
|
Applying names to existing formulas |
|
|
331 | (3) |
|
|
|
334 | (1) |
|
Controlling Formula Recalculation |
|
|
335 | (2) |
|
|
|
337 | (4) |
|
Chapter 2 Logical Functions and Error Trapping |
|
|
341 | (22) |
|
Understanding Error Values |
|
|
341 | (2) |
|
|
|
343 | (2) |
|
|
|
345 | (3) |
|
Whiting-Out Errors with Conditional Formatting |
|
|
348 | (1) |
|
|
|
349 | (12) |
|
|
|
351 | (3) |
|
|
|
354 | (1) |
|
|
|
355 | (2) |
|
Changing the Error Checking options |
|
|
357 | (1) |
|
|
|
358 | (2) |
|
|
|
360 | (1) |
|
Removing Errors from the Printout |
|
|
361 | (2) |
|
Chapter 3 Date and Time Formulas |
|
|
363 | (16) |
|
Understanding Dates and Times |
|
|
363 | (4) |
|
Changing the Regional date settings |
|
|
364 | (1) |
|
Building formulas that calculate elapsed dates |
|
|
365 | (1) |
|
Building formulas that calculate elapsed times |
|
|
366 | (1) |
|
|
|
367 | (8) |
|
|
|
367 | (1) |
|
|
|
368 | (1) |
|
DAY, WEEKDAY, MONTH, and YEAR |
|
|
369 | (2) |
|
|
|
371 | (1) |
|
Other special Date functions |
|
|
371 | (4) |
|
|
|
375 | (4) |
|
|
|
375 | (1) |
|
|
|
376 | (1) |
|
|
|
377 | (2) |
|
Chapter 4 Financial Formulas |
|
|
379 | (14) |
|
|
|
379 | (1) |
|
The PV, NPV, and FV Functions |
|
|
380 | (3) |
|
Calculating the Present Value |
|
|
380 | (1) |
|
Calculating the Net Present Value |
|
|
381 | (1) |
|
Calculating the Future Value |
|
|
382 | (1) |
|
|
|
383 | (4) |
|
|
|
387 | (2) |
|
Analysis ToolPak Financial Functions |
|
|
389 | (4) |
|
Chapter 5 Math and Statistical Formulas |
|
|
393 | (20) |
|
|
|
394 | (9) |
|
|
|
394 | (4) |
|
|
|
398 | (1) |
|
|
|
399 | (1) |
|
|
|
400 | (3) |
|
|
|
403 | (10) |
|
|
|
404 | (1) |
|
|
|
405 | (5) |
|
Using specialized statistical functions |
|
|
410 | (3) |
|
Chapter 6 Lookup, Information, and Text Formulas |
|
|
413 | (22) |
|
|
|
413 | (11) |
|
Looking up a single value with VLOOKUP and HLOOKUP |
|
|
414 | (4) |
|
Performing a two-way lookup |
|
|
418 | (3) |
|
|
|
421 | (3) |
|
|
|
424 | (5) |
|
Getting specific information about a cell |
|
|
425 | (3) |
|
|
|
428 | (1) |
|
|
|
428 | (1) |
|
|
|
429 | (8) |
|
|
|
430 | (3) |
|
|
|
433 | (2) |
| Book IV: Worksheet Collaboration and Review |
|
435 | (84) |
|
Chapter 1 Protecting Workbooks and Worksheet Data |
|
|
437 | (22) |
|
Password-Protecting the File |
|
|
437 | (7) |
|
Protecting the workbook when saving the file |
|
|
438 | (2) |
|
Assigning a password to open from the Info screen |
|
|
440 | (1) |
|
Entering the password to gain access |
|
|
441 | (2) |
|
Entering the password to make changes |
|
|
443 | (1) |
|
Changing or deleting a password |
|
|
443 | (1) |
|
|
|
444 | (15) |
|
Changing a cell's Locked and Hidden Protection formatting |
|
|
445 | (1) |
|
|
|
446 | (4) |
|
Enabling cell range editing by certain users |
|
|
450 | (4) |
|
Doing data entry in the unlocked cells of a protected worksheet |
|
|
454 | (2) |
|
|
|
456 | (1) |
|
Protecting a shared workbook |
|
|
457 | (2) |
|
Chapter 2 Using Hyperlinks |
|
|
459 | (8) |
|
|
|
459 | (6) |
|
|
|
460 | (3) |
|
|
|
463 | (1) |
|
|
|
464 | (1) |
|
Using the HYPERLINK Function |
|
|
465 | (2) |
|
Chapter 3 Sending Workbooks Out for Review |
|
|
467 | (28) |
|
Preparing a Workbook for Distribution |
|
|
467 | (7) |
|
Adding properties to a workbook |
|
|
469 | (1) |
|
Digitally signing a document |
|
|
469 | (5) |
|
|
|
474 | (14) |
|
|
|
476 | (2) |
|
Modifying the Share Workbook options |
|
|
478 | (2) |
|
Turning on change tracking |
|
|
480 | (4) |
|
Merging changes from different users |
|
|
484 | (4) |
|
|
|
488 | (7) |
|
|
|
488 | (3) |
|
Marking up a worksheet with digital ink |
|
|
491 | (4) |
|
Chapter 4 Sharing Workbooks and Worksheet Data |
|
|
495 | (24) |
|
Sharing Your Workbooks Online |
|
|
496 | (9) |
|
Sharing workbooks saved on your OneDrive |
|
|
496 | (3) |
|
|
|
499 | (1) |
|
Sharing workbooks with Instant Message |
|
|
500 | (1) |
|
Presenting worksheets online |
|
|
500 | (1) |
|
Editing worksheets online |
|
|
501 | (4) |
|
Excel 2016 Data Sharing Basics |
|
|
505 | (8) |
|
|
|
506 | (5) |
|
Excel and PowerPoint 2016 |
|
|
511 | (2) |
|
Exporting Workbooks to Other Usable File Formats |
|
|
513 | (8) |
|
Saving and exporting worksheets as PDF files |
|
|
513 | (1) |
|
Saving worksheets as XPS files |
|
|
514 | (1) |
|
Saving worksheets as ODS files |
|
|
515 | (1) |
|
Saving worksheets as HTML files |
|
|
516 | (3) |
| Book V: Charts and Graphics |
|
519 | (62) |
|
Chapter 1 Charting Worksheet Data |
|
|
521 | (32) |
|
|
|
522 | (27) |
|
Embedded charts versus charts on separate chart sheets |
|
|
524 | (1) |
|
Inserting recommended charts |
|
|
524 | (1) |
|
Inserting specific chart types from the Ribbon |
|
|
525 | (1) |
|
Inserting charts with the Quick Analysis tool |
|
|
526 | (2) |
|
Creating a chart on a separate chart sheet |
|
|
528 | (1) |
|
Refining the chart from the Design tab |
|
|
528 | (7) |
|
Customizing chart elements from the Format tab |
|
|
535 | (3) |
|
Customizing the elements of a chart |
|
|
538 | (4) |
|
Formatting elements of a chart |
|
|
542 | (5) |
|
Saving a customized chart as a template |
|
|
547 | (2) |
|
Adding Sparkline Graphics to a Worksheet |
|
|
549 | (1) |
|
|
|
550 | (3) |
|
Chapter 2 Adding Graphic Objects |
|
|
553 | (28) |
|
|
|
553 | (9) |
|
|
|
555 | (1) |
|
Moving graphic objects to new layers |
|
|
556 | (3) |
|
|
|
559 | (1) |
|
|
|
559 | (1) |
|
Managing graphic objects in the Selection task pane |
|
|
560 | (2) |
|
Inserting Different Types of Graphics |
|
|
562 | (5) |
|
|
|
562 | (2) |
|
|
|
564 | (1) |
|
|
|
565 | (1) |
|
|
|
565 | (2) |
|
|
|
567 | (11) |
|
Drawing predefined shapes |
|
|
567 | (1) |
|
|
|
568 | (5) |
|
|
|
573 | (2) |
|
Inserting SmartArt graphics |
|
|
575 | (3) |
|
Adding Screenshots of the Windows Desktop |
|
|
578 | (1) |
|
|
|
579 | (2) |
| Book VI: Data Management |
|
581 | (68) |
|
Chapter 1 Building and Maintaining Data Lists |
|
|
583 | (24) |
|
|
|
583 | (11) |
|
Designing the basic data list |
|
|
584 | (3) |
|
Add new records to a data list |
|
|
587 | (6) |
|
Eliminating records with duplicate fields |
|
|
593 | (1) |
|
|
|
594 | (9) |
|
Sorting records on a single field |
|
|
595 | (1) |
|
Sorting records on multiple fields |
|
|
595 | (4) |
|
Sorting the columns of a data list |
|
|
599 | (3) |
|
Sorting a data list on font and fill colors and cell icons |
|
|
602 | (1) |
|
|
|
603 | (4) |
|
Chapter 2 Filtering and Querying a Data List |
|
|
607 | (42) |
|
|
|
608 | (1) |
|
|
|
609 | (17) |
|
|
|
609 | (9) |
|
Using the Advanced Filter |
|
|
618 | (8) |
|
Using the Database Functions |
|
|
626 | (3) |
|
|
|
629 | (22) |
|
Retrieving data from Access database tables |
|
|
631 | (2) |
|
Retrieving data from the web |
|
|
633 | (2) |
|
Retrieving data from text files |
|
|
635 | (4) |
|
Querying data from other data sources |
|
|
639 | (1) |
|
Retrieving external data with Microsoft Query |
|
|
640 | (9) |
| Book VII: Data Analysis |
|
649 | (72) |
|
Chapter 1 Performing What-If Scenarios |
|
|
651 | (24) |
|
|
|
652 | (6) |
|
Creating a one-variable data table |
|
|
652 | (4) |
|
Creating a two-variable data table |
|
|
656 | (2) |
|
Exploring Different Scenarios |
|
|
658 | (6) |
|
|
|
658 | (4) |
|
Producing a summary report |
|
|
662 | (2) |
|
|
|
664 | (1) |
|
|
|
665 | (10) |
|
Setting up and defining the problem |
|
|
667 | (2) |
|
|
|
669 | (1) |
|
Changing the Solver options |
|
|
670 | (2) |
|
Saving and loading a model problem |
|
|
672 | (1) |
|
|
|
673 | (2) |
|
Chapter 2 Performing Large-Scale Data Analysis |
|
|
675 | (46) |
|
|
|
675 | (10) |
|
Pivot tables with the Quick Analysis tool |
|
|
676 | (3) |
|
|
|
679 | (1) |
|
Manually created pivot tables |
|
|
679 | (6) |
|
|
|
685 | (4) |
|
Refining the pivot table layout and style |
|
|
685 | (2) |
|
Formatting the parts of the pivot table |
|
|
687 | (2) |
|
Sorting and Filtering the Pivot Table Data |
|
|
689 | (6) |
|
|
|
690 | (1) |
|
Filtering individual Column and Row fields |
|
|
690 | (1) |
|
Slicing the pivot table data |
|
|
691 | (2) |
|
|
|
693 | (1) |
|
|
|
694 | (1) |
|
Modifying the Pivot Table |
|
|
695 | (5) |
|
Changing the summary functions |
|
|
696 | (1) |
|
|
|
697 | (2) |
|
Changing the pivot table options |
|
|
699 | (1) |
|
|
|
700 | (3) |
|
Moving a pivot chart to its own sheet |
|
|
701 | (1) |
|
|
|
701 | (1) |
|
|
|
702 | (1) |
|
Using the Power Pivot and Power View Add-Ins |
|
|
703 | (10) |
|
Data modeling with Power Pivot |
|
|
704 | (2) |
|
Switching between the Data View and Diagram View |
|
|
706 | (3) |
|
Adding calculated columns courtesy of DAX |
|
|
709 | (1) |
|
Creating visual reports with Power View |
|
|
710 | (3) |
|
Using the Power Map feature |
|
|
713 | (5) |
|
Creating Forecast Worksheets |
|
|
718 | (3) |
| Book VIII: Macros and VBA |
|
721 | (38) |
|
Chapter 1 Recording and Running Macros |
|
|
723 | (12) |
|
|
|
724 | (6) |
|
|
|
724 | (4) |
|
|
|
728 | (2) |
|
Assigning Macros to the Ribbon and the Quick Access Toolbar |
|
|
730 | (2) |
|
Adding your macros to a custom tab on the Ribbon |
|
|
730 | (1) |
|
Adding your macros to custom buttons on the Quick Access toolbar |
|
|
731 | (1) |
|
|
|
732 | (3) |
|
Chapter 2 VBA Programming |
|
|
735 | (24) |
|
Using the Visual Basic Editor |
|
|
736 | (14) |
|
|
|
738 | (10) |
|
Writing new macros in the Visual Basic Editor |
|
|
748 | (2) |
|
Creating Custom Excel Functions |
|
|
750 | (9) |
|
Adding a description to a user-defined function |
|
|
751 | (2) |
|
Using a custom function in your spreadsheet |
|
|
753 | (2) |
|
Saving custom functions in add-in files |
|
|
755 | (4) |
| Index |
|
759 | |
Lisainfo e-raamatute kohta