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