Excel Workbook For Dummies

by McFedries, Paul; Harvey, Greg

• ISBN13: 9781119832157
• ISBN10: 1119832152
• Edition: 2nd
• Format: Paperback
• Copyright: 2022-01-19
• Publisher: For Dummies

Summary

Get practical walkthroughs for the most useful Excel features

Looking for easy-to-understand, practical guidance on how to go from Excel newbie to number crunching pro? Excel Workbook For Dummies is the hands-on tutorial you've been waiting for.

This step-by-step guide is packed with exercises that walk you through the basic and advanced functions and formulas included in Excel. At your own speed, you'll learn how to enter data, format your spreadsheet, and apply the mathematical and statistical capabilities of the program.

Work through the book from start to finish or, if you'd prefer, jump right to the section that's giving you trouble, whether that's data visualization, macros, analysis, or anything else. You can also:

• Brush up on and practice time-saving keyboard shortcuts for popular commands and actions
• Get a handle on multi-functional and practical pivot tables with intuitive practice exercises
• Learn to secure your data with spreadsheet password protections

Perfect for new users of Excel, Excel Workbook For Dummies is also the ideal resource for those who know their way around a spreadsheet but need a refresher on some of the more advanced features of this powerful program.

Author Biography

Paul McFedries has published over 100 books, with topics ranging from Windows 10 to Microsoft Office, Apple gadgets, and Amazon Alexa. He is the author of the recently released Excel Data Analysis For Dummies and Google’s G Suite For Dummies.

Greg Harvey, PhD was author of over 30 For Dummies titles covering Excel.

Table of Contents

Introduction 1

About This Book 1

Conventions Used in This Book 2

Foolish Assumptions 2

Beyond the Book 3

Icons Used in This Book 3

Where to Go from Here 4

Part 1: Building Worksheets 5

Chapter 1: Getting Familiar with the Excel Interface 7

Launching Excel 7

Identifying the Parts of the Excel Window 9

Selecting Commands in the Backstage View 10

Selecting Commands from the Ribbon 11

Adding a custom tab to the Excel Ribbon 13

Adding commands to groups on your custom tab 14

Selecting Commands on the Quick Access Toolbar 15

Displaying the Quick Access toolbar 16

Customizing the Quick Access toolbar 16

Adding more commands to the Quick Access toolbar 17

Chapter 2: Entering the Worksheet Data 21

Opening a New Workbook 21

Moving Around the Workbook 24

Moving within the displayed area 24

Moving to a new area of the worksheet 25

Moving to a different sheet in the workbook 27

Selecting Cell Ranges 28

Making Cell Entries 30

Entering data in a single cell 30

Entering data in a range 32

Filling in a data series with the fill handle 33

Copying a formula with the fill handle 34

Saving the Spreadsheet Data in a Workbook File 35

Chapter 3: Formatting the Worksheet 37

Resizing Columns and Rows 37

Making column widths suit the data 38

Manipulating the height of certain rows 39

Cell Formatting Techniques 40

Formatting cells with the Ribbon’s Home tab 41

Formatting cells with the Format Cells dialog box 46

Using cell styles 52

Using conditional formatting 53

Hiding Columns and Rows 56

Chapter 4: Printing Worksheet Reports 59

Previewing Pages in the Worksheet and Backstage View 59

Adjusting Page Breaks 61

Adding Headers and Footers 63

Adding Print Titles to a Report 66

Modifying the Print Settings for a Report 68

Printing All or Part of the Workbook 71

Printing a range 71

Printing the entire workbook 72

Printing charts in the worksheet 74

Printing the worksheet formulas 76

Chapter 5: Modifying the Worksheet 79

Finding and Identifying the Region That Needs Editing 79

Selecting the Ranges to Edit 82

Editing Data Entries 84

Deleting and Inserting Data and Cells 86

Moving and Copying Data and Cells 88

Adding Notes to the Worksheet 92

Using Find and Replace and Spell Checking 93

Editing Multiple Worksheets 97

Part 2: Using Formulas and Functions 101

Chapter 6: Building Formulas 103

Building Formulas 103

Building formulas by hand 104

Building formulas with built-in functions 110

Editing formulas 114

Altering the natural order of operations 115

Using External Reference Links 118

Controlling When Formulas Are Recalculated 120

Chapter 7: Copying and Correcting Formulas 123

Copying Formulas with Relative References 124

Copying Formulas with Absolute References 126

Copying Formulas with Mixed References 128

Using Range Names in Formulas 132

Building Array Formulas 136

Tracing and Eliminating Formula Errors 140

Dealing with Circular References 142

Chapter 8: Creating Date and Time Formulas 145

Constructing Date and Time Formulas 145

Working with Simple Date Functions 148

Working with Excel’s Fancier Date Functions 150

Working with the Time Functions 152

Chapter 9: Financial Formulas and Functions 155

Working with Financial Functions 155

Using the Basic Investment Functions 156

Figuring the Depreciation of an Asset 162

Chapter 10: Using Math Functions 167

Rounding Off Values 167

Finding Products, Powers, and Square Roots 169

Doing Fancier Sums 172

Summing products, squares, and their differences 172

Conditional totals 175

Chapter 11: Using Common Statistical Functions 179

Computing Averages 179

Finding the Highest and Lowest Values 181

Counting Cells 182

Using the Statistical Functions in Analysis ToolPak Add-in 185

Chapter 12: Using Lookup Functions 187

Looking Up Stuff with XLOOKUP 188

Returning Single Values from a Lookup Table 190

Performing a horizontal lookup 191

Performing a vertical lookup 193

Chapter 13: Using Logical Functions 197

Working with the Logical Functions 197

Constructing Decision-Making Formulas 198

Choosing between alternate values 198

Selecting between alternate calculations 202

Nesting IF functions 203

Constructing Error-Trapping Formulas 205

Chapter 14: Text Formulas and Functions 209

Constructing Text Formulas 209

Using Text Functions 213

Part 3: Working with Graphics 217

Chapter 15: Charting Worksheet Data 219

Understanding Excel Charts 219

Creating Charts 224

Formatting Charts 226

Editing Charts 229

Chapter 16: Adding Graphics to Worksheets 231

Understanding Graphic Objects 231

Adding Various Types of Graphic Objects 235

Inserting prefab art 236

Importing graphics files 237

Adding graphic shapes and text boxes 241

Constructing WordArt 243

Constructing SmartArt 245

Part 4: Managing and Securing Data 249

Chapter 17: Building and Maintaining Tables 251

Creating a Table 251

Adding records to a new table 252

Editing records in the data form 255

Sorting Tables 257

Using sorting keys 258

Sorting a table on multiple keys 259

Sorting the fields (columns) in a table 260

Subtotaling a Table 262

Filtering a Table 264

Querying External Database Tables 267

Chapter 18: Protecting Workbooks and Worksheet Data 271

Password-Protecting the Workbook 272

Protecting the Worksheet 274

Doing Data Entry in a Protected Worksheet 278

Protecting the Entire Workbook 280

Part 5: Doing Data Analysis 283

Chapter 19: Performing What-If Analysis 285

Performing What-If Analysis with Data Tables 285

Creating a single-variable data table 286

Creating a two-variable data table 289

Analyzing Data with Scenarios 292

Goal Seeking in a Worksheet 294

Creating Complex Models with Solver 296

Chapter 20: Generating PivotTables 299

Working with PivotTables 299

Creating PivotTables 302

Modifying the PivotTable 304

Modifying the PivotTable formatting 305

Pivoting the PivotTable’s fields 306

Changing the PivotTable summary function and adding calculated fields 307

Creating PivotCharts 310

Part 6: Macros and Visual Basic for Applications 315

Chapter 21: Using Macros 317

Creating Macros 317                                                               

Recording and playing back macros 318

Recording macros with relative cell references 321

Assigning Macros to the Quick Access Toolbar 323

Assigning Macros to the Ribbon 324

Chapter 22: Using the Visual Basic Editor 327

Using the Visual Basic Editor 328

Editing a recorded macro 329

Adding a dialog box that processes user input 331

Creating User-Defined Functions 334

Using a custom function in your worksheet 337

Saving custom functions in add-in files 338

Part 7: The Part of Tens 341

Chapter 23: Top Ten Features in Excel 343

The Excel Ribbon and Backstage View 343

Conditional Formatting 344

PivotTable Filtering with Slicers 344

Home Tab Formatting and Editing 344

Insert Tab Charting 345

Format as Table 345

The Zoom Slider on the Status Bar 345

Page Layout View 345

Style Galleries 346

Live Preview 346

Chapter 24: Top Ten Tips for Using Excel Like a Pro 347

Generating New Workbooks from Templates 347

Organizing Data on Different Worksheets 348

Creating Data Series with AutoFill 349

Using Range Names 349

Freezing Column and Row Headings 350

Preventing Data Entry Errors with Data Validation 350

Trapping Error Values 351

Saving Memory by Using Array Formulas 352

Using Outlines to Control the Display of Data 352

Using View Side by Side to Work with Two Workbooks 353

Index 355