Introduction 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

Use Excel on any deviceShow off your data using all the new features of ExcelStay on top of data and track and organize information Master Excel with this bestselling guide! If spreadsheets are part of your life, Microsoft Excel must be, too. The world's favorite spreadsheet and data analysis tool is newly updated, and this bestselling book has been fully revised to cover all the new features. Everything you need to know is here, whether you're just venturing into the spreadsheet world or upgrading from an earlier Excel incarnation. Learn to format cells, enter formulas, and create charts; add hyperlinks to worksheets; create and use pivot tables; and much more. Designworksheets, enter data, and save workbook files Perform what-if analysis and goal seeking Query, filter, and summarize dataAdd online pictures, icons, and 3D maps to worksheetsExtend the power of Excel with add-ins and macros

Introduction 1 Part 1: Getting Started with Excel 2019 9 Chapter 1: The Excel 2019 User Experience 11 Chapter 2: Creating a Spreadsheet from Scratch 41 Part 2: Editing Without Tears 95 Chapter 3: Making It All Look Pretty 97 Chapter 4: Going Through Changes 145 Chapter 5: Printing the Masterpiece 177 Part 3: Getting Organized and Staying That Way 203 Chapter 6: Maintaining the Worksheet 205 Chapter 7: Maintaining Multiple Worksheets 233 Part 4: Digging Data Analysis 255 Chapter 8: Doing What-If Analysis 257 Chapter 9: Playing with Pivot Tables 271 Part 5: Life Beyond the Spreadsheet 293 Chapter 10: Charming Charts and Gorgeous Graphics 295 Chapter 11: Getting on the Data List 335 Chapter 12: Linking, Automating, and Sharing Spreadsheets 359 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