Excel 2019 For Dummies

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