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 Ceil 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