| Introduction |
|
1 | (8) |
|
|
|
1 | (1) |
|
|
|
2 | (1) |
|
What You Can Safely Ignore |
|
|
2 | (1) |
|
|
|
2 | (1) |
|
How This Book Is Organized |
|
|
3 | (2) |
|
Part 1: Getting Started with Excel 2019 |
|
|
3 | (1) |
|
Part 2: Editing Without Tears |
|
|
4 | (1) |
|
Part 3: Getting Organized and Staying That Way |
|
|
4 | (1) |
|
Part 4: Digging Data Analysis |
|
|
4 | (1) |
|
Part 5: Life Beyond the Spreadsheet |
|
|
4 | (1) |
|
|
|
5 | (1) |
|
Conventions Used in This Book |
|
|
5 | (2) |
|
Selecting Ribbon commands |
|
|
5 | (1) |
|
|
|
6 | (1) |
|
|
|
7 | (1) |
|
|
|
7 | (2) |
| Part 1: Getting Started With EXCEL 2019 |
|
9 | (86) |
|
Chapter 1 The Excel 2019 User Experience |
|
|
11 | (30) |
|
Excel's Ribbon User Interface |
|
|
12 | (23) |
|
|
|
14 | (1) |
|
|
|
15 | (6) |
|
Customizing the Quick Access toolbar |
|
|
21 | (3) |
|
Having fun with the Formula bar |
|
|
24 | (2) |
|
What to do in the Worksheet area |
|
|
26 | (8) |
|
Showing off the Status bar |
|
|
34 | (1) |
|
Launching and Quitting Excel |
|
|
35 | (3) |
|
Starting Excel from the Windows 10 Start menu |
|
|
35 | (1) |
|
Starting Excel from the Windows 10 Search text box |
|
|
36 | (1) |
|
Starting Excel from the Windows 10 Metro view in Tablet mode |
|
|
36 | (1) |
|
|
|
37 | (1) |
|
|
|
38 | (3) |
|
Using the Tell Me help feature |
|
|
39 | (2) |
|
Chapter 2 Creating a Spreadsheet from Scratch |
|
|
41 | (54) |
|
So What Ya Gonna Put in That New Workbook of Yours? |
|
|
42 | (3) |
|
The ins and outs of data entry |
|
|
44 | (1) |
|
|
|
45 | (1) |
|
Doing the Data-Entry Thing |
|
|
45 | (3) |
|
|
|
48 | (14) |
|
The telltale signs of text |
|
|
48 | (1) |
|
How Excel evaluates its values |
|
|
49 | (7) |
|
Fabricating those fabulous formulas! |
|
|
56 | (3) |
|
If you want it, just point it out |
|
|
59 | (1) |
|
Altering the natural order of operations |
|
|
59 | (1) |
|
|
|
60 | (2) |
|
Fixing Those Data Entry Flub-Ups |
|
|
62 | (3) |
|
You really AutoCorrect that for me |
|
|
62 | (1) |
|
|
|
63 | (2) |
|
Taking the Drudgery Out of Data Entry |
|
|
65 | (13) |
|
I'm just not complete without you |
|
|
65 | (1) |
|
|
|
66 | (8) |
|
|
|
74 | (2) |
|
Inserting special symbols |
|
|
76 | (1) |
|
Entries all around the block |
|
|
76 | (1) |
|
|
|
77 | (1) |
|
How to Make Your Formulas Function Even Better |
|
|
78 | (8) |
|
Inserting a function into a formula with the Insert Function button |
|
|
79 | (3) |
|
Editing a function with the Insert Function button |
|
|
82 | (1) |
|
I'd be totally lost without AutoSum |
|
|
82 | (3) |
|
Sums via Quick Analysis Totals |
|
|
85 | (1) |
|
Making Sure That the Data Is Safe and Sound |
|
|
86 | (5) |
|
Changing the default file location |
|
|
89 | (1) |
|
The difference between the XLSX and XLS file formats |
|
|
90 | (1) |
|
Saving the Workbook as a PDF File |
|
|
91 | (1) |
|
Document Recovery to the Rescue |
|
|
92 | (3) |
| Part 2: Editing Without Tears |
|
95 | (108) |
|
Chapter 3 Making It All Look Pretty |
|
|
97 | (48) |
|
Choosing a Select Group of Cells |
|
|
98 | (8) |
|
Point-and-click cell selections |
|
|
99 | (5) |
|
|
|
104 | (2) |
|
Using the Format as Table Gallery |
|
|
106 | (6) |
|
Customizing table formats |
|
|
109 | (1) |
|
Creating a new custom Table Style |
|
|
110 | (2) |
|
Cell Formatting from the Home Tab |
|
|
112 | (2) |
|
Formatting Cells Close to the Source with the Mini-bar |
|
|
114 | (1) |
|
Using the Format Cells Dialog Box |
|
|
115 | (9) |
|
Understanding the number formats |
|
|
116 | (5) |
|
The values behind the formatting |
|
|
121 | (2) |
|
|
|
123 | (1) |
|
Ogling some of the other number formats |
|
|
123 | (1) |
|
|
|
124 | (3) |
|
|
|
125 | (1) |
|
Now you see it, now you don't |
|
|
126 | (1) |
|
|
|
127 | (2) |
|
|
|
129 | (8) |
|
|
|
130 | (1) |
|
|
|
130 | (1) |
|
Tampering with how the text wraps |
|
|
131 | (1) |
|
|
|
132 | (2) |
|
|
|
134 | (1) |
|
|
|
134 | (2) |
|
Applying fill colors, patterns, and gradient effects to cells |
|
|
136 | (1) |
|
|
|
137 | (2) |
|
Creating a new style for the gallery |
|
|
137 | (1) |
|
Copying custom styles from one workbook into another |
|
|
138 | (1) |
|
Fooling Around with the Format Painter |
|
|
139 | (1) |
|
|
|
140 | (5) |
|
Formatting with scales and markers |
|
|
141 | (1) |
|
Highlighting cells ranges |
|
|
142 | (1) |
|
Formatting via the Quick Analysis tool |
|
|
143 | (2) |
|
Chapter 4 Going Through Changes |
|
|
145 | (32) |
|
Opening Your Workbooks for Editing |
|
|
146 | (6) |
|
Opening files in the Open screen |
|
|
147 | (1) |
|
Operating the Open dialog box |
|
|
148 | (2) |
|
Changing the Recent files settings |
|
|
150 | (1) |
|
Opening multiple workbooks |
|
|
151 | (1) |
|
|
|
151 | (1) |
|
Using the Open file options |
|
|
152 | (1) |
|
|
|
152 | (2) |
|
Undo is Redo the second time around |
|
|
153 | (1) |
|
What to do when you can't Undo? |
|
|
153 | (1) |
|
Doing the Old Drag-and-Drop Thing |
|
|
154 | (4) |
|
Copies, drag-and-drop style |
|
|
156 | (1) |
|
Insertions courtesy of drag and drop |
|
|
157 | (1) |
|
Copying Formulas with AutoFill |
|
|
158 | (4) |
|
|
|
159 | (1) |
|
Some things are absolutes! |
|
|
160 | (2) |
|
Cut and Paste, Digital Style |
|
|
162 | (7) |
|
|
|
163 | (1) |
|
Keeping pace with Paste Options |
|
|
164 | (2) |
|
Paste it from the Clipboard task pane |
|
|
166 | (1) |
|
So what's so special about Paste Special? |
|
|
167 | (2) |
|
Let's Be Clear About Deleting Stuff |
|
|
169 | (2) |
|
|
|
170 | (1) |
|
Get these cells outta here! |
|
|
170 | (1) |
|
Staying in Step with Insert |
|
|
171 | (1) |
|
Stamping Out Your Spelling Errors |
|
|
172 | (2) |
|
Eliminating Errors with Text to Speech |
|
|
174 | (3) |
|
Chapter 5 Printing the Masterpiece |
|
|
177 | (26) |
|
Previewing Pages in Page Layout View |
|
|
178 | (2) |
|
Using the Backstage Print Screen |
|
|
180 | (3) |
|
Printing the Current Worksheet |
|
|
183 | (1) |
|
|
|
184 | (8) |
|
Using the buttons in the Page Setup group |
|
|
185 | (5) |
|
Using the buttons in the Scale to Fit group |
|
|
190 | (2) |
|
Using the Print buttons in the Sheet Options group |
|
|
192 | (1) |
|
|
|
192 | (7) |
|
Adding an Auto Header and Footer |
|
|
193 | (1) |
|
Creating a custom header or footer |
|
|
194 | (5) |
|
Solving Page Break Problems |
|
|
199 | (2) |
|
Letting Your Formulas All Hang Out |
|
|
201 | (2) |
| Part 3: Getting Organized And Staying That Way |
|
203 | (52) |
|
Chapter 6 Maintaining the Worksheet |
|
|
205 | (28) |
|
|
|
206 | (3) |
|
Splitting the Worksheet into Window Panes |
|
|
209 | (1) |
|
Fixed Headings with Freeze Panes |
|
|
210 | (3) |
|
|
|
213 | (4) |
|
Adding a comment to a cell |
|
|
214 | (1) |
|
|
|
215 | (1) |
|
Editing comments in a worksheet |
|
|
216 | (1) |
|
Getting your comments in print |
|
|
217 | (1) |
|
|
|
217 | (5) |
|
|
|
217 | (3) |
|
|
|
220 | (1) |
|
|
|
221 | (1) |
|
|
|
222 | (3) |
|
|
|
225 | (2) |
|
Doing Your Research with Smart Lookup |
|
|
227 | (1) |
|
Controlling Recalculation |
|
|
228 | (1) |
|
Putting on the Protection |
|
|
229 | (4) |
|
Chapter 7 Maintaining Multiple Worksheets |
|
|
233 | (22) |
|
Juggling Multiple Worksheets |
|
|
234 | (4) |
|
Sliding between the sheets |
|
|
235 | (2) |
|
|
|
237 | (1) |
|
|
|
238 | (4) |
|
A worksheet by any other name |
|
|
239 | (1) |
|
A sheet tab by any other color |
|
|
240 | (1) |
|
Getting your sheets in order |
|
|
241 | (1) |
|
Opening Windows on Your Worksheets |
|
|
242 | (5) |
|
Comparing Worksheets Side by Side |
|
|
247 | (1) |
|
Shifting Sheets to Other Workbooks |
|
|
248 | (4) |
|
Summing Stuff on Different Worksheets |
|
|
252 | (3) |
| Part 4: Digging Data Analysis |
|
255 | (38) |
|
Chapter 8 Doing What-If Analysis |
|
|
257 | (14) |
|
Playing What-If with Data Tables |
|
|
258 | (6) |
|
Creating a one-variable data table |
|
|
258 | (3) |
|
Creating a two-variable data table |
|
|
261 | (3) |
|
Playing What-If with Goal Seeking |
|
|
264 | (2) |
|
Making the Case with Scenario Manager |
|
|
266 | (5) |
|
Setting up the various scenarios |
|
|
266 | (3) |
|
Producing a summary report |
|
|
269 | (2) |
|
Chapter 9 Playing with Pivot Tables |
|
|
271 | (22) |
|
Data Analysis with Pivot Tables |
|
|
272 | (7) |
|
Pivot tables via the Quick Analysis tool |
|
|
272 | (2) |
|
Pivot tables by recommendation |
|
|
274 | (2) |
|
Manually producing pivot tables |
|
|
276 | (3) |
|
|
|
279 | (1) |
|
Refining the Pivot Table style |
|
|
279 | (1) |
|
Formatting values in the pivot table |
|
|
280 | (1) |
|
Sorting and Filtering Pivot Table Data |
|
|
280 | (5) |
|
|
|
281 | (1) |
|
Filtering column and row fields |
|
|
281 | (1) |
|
|
|
282 | (1) |
|
|
|
283 | (1) |
|
|
|
284 | (1) |
|
|
|
285 | (3) |
|
Modifying the pivot table fields |
|
|
286 | (1) |
|
Pivoting the table's fields |
|
|
286 | (1) |
|
Modifying the table's summary function |
|
|
287 | (1) |
|
|
|
288 | (7) |
|
Moving pivot charts to separate sheets |
|
|
289 | (2) |
|
|
|
291 | (1) |
|
|
|
291 | (2) |
| Part 5: Life Beyond The Spreadsheet |
|
293 | (90) |
|
Chapter 10 Charming Charts and Gorgeous Graphics |
|
|
295 | (40) |
|
Making Professional-Looking Charts |
|
|
296 | (14) |
|
Charts thanks to Recommendation |
|
|
296 | (1) |
|
|
|
297 | (2) |
|
Charts via the Quick Analysis tool |
|
|
299 | (2) |
|
Charts on their own chart sheets |
|
|
301 | (1) |
|
Moving and resizing embedded charts |
|
|
301 | (2) |
|
Moving embedded charts to chart sheets |
|
|
303 | (1) |
|
Customizing charts from the Design tab |
|
|
303 | (1) |
|
Customizing chart elements |
|
|
304 | (3) |
|
Editing the generic titles in a chart |
|
|
307 | (3) |
|
Adding Great-Looking Graphics |
|
|
310 | (20) |
|
Sparking up the data with sparklines |
|
|
310 | (2) |
|
Telling all with a text box |
|
|
312 | (3) |
|
|
|
315 | (3) |
|
|
|
318 | (1) |
|
|
|
319 | (1) |
|
Editing inserted pictures |
|
|
319 | (1) |
|
Formatting inserted 2-D images |
|
|
320 | (2) |
|
Formatting inserted 3-D images |
|
|
322 | (1) |
|
|
|
323 | (1) |
|
|
|
324 | (1) |
|
|
|
325 | (1) |
|
|
|
326 | (3) |
|
|
|
329 | (1) |
|
|
|
330 | (1) |
|
Controlling How Graphic Objects Overlap |
|
|
330 | (4) |
|
Reordering the layering of graphic objects |
|
|
331 | (1) |
|
|
|
332 | (1) |
|
|
|
333 | (1) |
|
|
|
334 | (1) |
|
Chapter 11 Getting on the Data List |
|
|
335 | (24) |
|
|
|
335 | (9) |
|
Adding records to data lists |
|
|
336 | (6) |
|
Moving through records in the data form |
|
|
342 | (1) |
|
Finding records with the data form |
|
|
342 | (2) |
|
|
|
344 | (5) |
|
Sorting on a single field |
|
|
345 | (1) |
|
Sorting on multiple fields |
|
|
346 | (3) |
|
|
|
349 | (6) |
|
Using ready-made number filters |
|
|
350 | (1) |
|
Using ready-made date filters |
|
|
351 | (1) |
|
|
|
352 | (3) |
|
|
|
355 | (4) |
|
Querying Access database tables |
|
|
355 | (2) |
|
|
|
357 | (2) |
|
Chapter 12 Linking, Automating, and Sharing Spreadsheets |
|
|
359 | (24) |
|
|
|
360 | (2) |
|
|
|
362 | (1) |
|
Adding Hyperlinks to a Worksheet |
|
|
363 | (3) |
|
Automating Commands with Macros |
|
|
366 | (8) |
|
|
|
367 | (4) |
|
|
|
371 | (1) |
|
Assigning macros to the Ribbon and the Quick Access toolbar |
|
|
372 | (2) |
|
|
|
374 | (5) |
|
Sharing workbooks saved on OneDrive |
|
|
374 | (4) |
|
|
|
378 | (1) |
|
|
|
378 | (1) |
|
Editing Worksheets in Excel Online |
|
|
379 | (4) |
| Part 6: The Part Of Tens |
|
383 | (16) |
|
Chapter 13 Top Ten Beginner Basics |
|
|
385 | (2) |
|
Chapter 14 The Ten Commandments Of Excel 2019 |
|
|
387 | (2) |
|
Chapter 15 Top Ten Ways To Manage Your Data |
|
|
389 | (6) |
|
Chapter 16 Top Ten Ways To Analyze Your Data |
|
|
395 | (4) |
| Index |
|
399 | |
