Introduction 1 About This Book 1 Foolish Assumptions 2 Conventions Used in This Book 3 Icons Used in This Book 5 Beyond the Book 5 Where to Go from Here 5 Book 1: Excel Basics 7 Chapter 1: Excel: The 50-Cent Tour 9 A Bird''s-Eye View of Excel 10 Checking Out Excel''s Start Screen 10 Getting Comfy with the Excel User Interface 12 Going behind the scenes to Excel''s Backstage view 13 Ripping through the Ribbon 17 Working with the Quick Access toolbar 22 Fooling around with the Formula bar 23 What''s up with the Worksheet area? 25 Taking a tour of the Status bar 30 Getting Help 31 Show-and-tell help with the Search feature 31 Getting Help from the Help tab on the Ribbon 32 Launching and Quitting Excel 34 Starting Excel from the Windows Start menu 34 Starting Excel from the Windows Search text box 35 Telling Cortana to Start Excel for you 36 Starting Excel from the Windows Metro view in Tablet mode 36 When it''s quitting time 37 Chapter 2: Customizing Excel 39 Tailoring the Quick Access Toolbar to Your Tastes 40 Adding Ribbon commands to the Quick Access toolbar 41 Adding non-Ribbon commands to the Quick Access toolbar 42 Exercising Your Options 43 Changing some of the more universal settings on the General tab 44 Changing common calculation options on the Formulas tab 47 Digging the options on the Data tab 50 Changing correction options on the Proofing tab 51 Changing save options on the Save tab 53 Changing the Office language preferences 56 Setting some accessibility options 57 Changing a whole lot of other common options on the Advanced tab 58 Customizing the Excel Ribbon 63 Using Office Add-ins 68 Using Excel''s Own Add-ins 70 Managing the standard Excel Add-ins 72 Managing Excel COM add-ins 72 Purchasing third-party add-ins 73 Book 2: Worksheet Design 75 Chapter 1: Building Worksheets 77 Creating Fresh Workbooks 78 Take it from a template 78 Designing a workbook from scratch 84 Getting to Know Excel''s Data Types 86 What''s in a label? 87 What''s the value? 89 Data Entry 101 93 Data entry keyboard style 94 Doing data entry with the Touch keyboard 96 You AutoComplete me 99 You AutoCorrect me 100 Constraining data entry to a cell range 102 Getting Excel to put in the decimal point 103 You AutoFill me 103 Flash Fill to the rescue 110 Linking to Other Documents 116 Hyperlinks: the basics 116 Inserting links 117 Follow that link! 120 Editing links 120 Using the HYPERLINK Function 121 Saving the Data 123 Saving workbooks in other commonly used file formats 126 Changing the default file location 127 Saving a new workbook in the old file format 128 Document Recovery to the Rescue 129 Chapter 2: Formatting Worksheets 131 Selecting Cells and Ranges 132 Selecting cells with the mouse 133 Selecting cells by touch 135 Selecting cells with the keyboard 135 Selecting cells with AutoSelect 136 Selecting cells with Go to 138 Name that range! 139 Adjusting Columns and Rows 141 Fitting the column to its contents automatically 141 Adjusting columns the old-fashioned way 142 Setting a new standard width 143 Hiding out a column or two 143 Rambling rows 144 Formatting Tables from the Ribbon 145 Formatting Tables with the Quick Analysis Tool 149 Formatting Cells from the Ribbon 150 Formatting Cell Ranges with the Mini-Toolbar 152 Using the Format Cells Dialog Box 154 Assigning number formats 154 Altering the alignment 162 Fancy fonts and colors 166 Basic borders, fills, and patterns 169 Hiring Out the Format Painter 173 Easier Formatting with Cell Styles 174 Using the Number Format cell styles 174 Defining a custom cell style by example 176 Creating a new cell style from scratch 176 Merging styles into other workbooks 177 Analyzing Data with Conditional Formatting 178 Graphical conditional formatting 179 Conditional formatting with the Quick Analysis tool 180 Identifying particular values or text entries in a cell range 181 Highlighting duplicate values in a cell range 184 Creating your own conditional formatting rules 184 Managing conditional formatting rules 186 Chapter 3: Editing and Proofing Worksheets 187 Opening a Workbook 188 Using the Open screen in the backstage view 188 Opening more than one workbook at a time 192 Finding misplaced workbooks 192 Using the other Open options 193 Editing a Cell 194 Changing your mind with Undo and Redo 195 Clearing cells 197 Inserting cells 199 A Worksheet with a View 201 Zooming in and zooming back out again 201 Freezing window panes 204 Saving custom views 206 Copying and Moving Stuff Around 208 Copying and moving with drag-and-drop 209 Copying and moving with copy, cut, and paste 210 Finding and Replacing Worksheet Data 218 Finding data 218 Finding and replacing data 221 Getting It Right: Spell Checking Your Worksheets 223 Changing the spelling options 225 Adding words to the custom dictionary 226 Looking Up and Translating Stuff 227 Marking Invalid Data 227 Chapter 4: Managing Worksheets 229 Reorganizing the Worksheet 230 Being cautious when deleting and inserting columns and rows 230 Deleting columns and rows 231 Adding new columns and rows 232 Splitting the worksheet into panes 233 Outlining worksheets 235 Reorganizing the Workbook 244 Renaming a worksheet 245 Designer sheets 245 Adding worksheets 247 Deleting worksheets 248 Changing the sheets 248 Editing and formatting multiple worksheets as a group 249 Hiding worksheets 250 Opening windows on different sheets 251 Viewing workbook stats 254 Working with Multiple Workbooks 255 Comparing windows on different workbooks 255 Transferring data between open windows 256 Moving or copying sheets from one workbook to another 256 Consolidating Worksheets 258 Consolidating by position 259 Consolidating by category 262 Linking consolidated data 263 Chapter 5: Printing Worksheets 265 Printing from the Backstage View 266 Selecting the printer to use 268 Previewing the printout 269 Quick Printing the Worksheet 272 Working with the Page Setup Options 273 Using the buttons in the Page Setup group 273 Using the buttons in the Scale to Fit group 280 Using the Print options on the Sheet tab of the Page Setup dialog box 281 Headers and Footers 282 Adding a ready-made header or footer 282 Creating a custom header or footer 284 Solving Page Break Problems 288 Printing the Formulas in a Report 290 BOOK 3: FORMULAS AND FUNCTIONS 291 Chapter 1: Building Basic Formulas 293 Getting Started with Formulas 294 Pointing at formula cells 296 Editing formulas 297 Using AutoSum to add numbers 298 Using Quick Analysis to add numbers 300 Building formulas with computational operators 301 Augmenting Formulas with Worksheet Functions 305 Inserting a function 305 Entering function arguments 308 Copying Formulas 310 Copying with relative cell references 311 Copying with absolute references 312 Copying with mixed cell references 315 Adding Array Formulas 316 Creating an array formula 318 Editing a dynamic array formula 320 Building old-fashioned array formulas 320 Editing an old-fashioned array formula 321 Naming Cells and Ranges 322 Defining cell and range names 323 Naming constants and formulas 325 Using names in building formulas 326 Creating names from column and row headings 327 Managing names 329 Applying names to existing formulas 330 Adding Linking Formulas 332 Controlling Formula Recalculation 333 Circular References 335 Chapter 2: Logical Functions and Error Trapping 337 Understanding Error Values 338 Using Logical Functions 340 Getting to know the powerful IF function 341 Evaluating the many talents of the IFS function 342 Learning about the SWITCH function 344 Error-Trapping Formulas 345 Error-trapping with IF 346 Upping your error-trapping game with ISERROR 347 Whiting-Out Errors with Conditional Formatting 348 Checking It Twice: Auditing Your Formulas 349 Tracing precedents 351 Tracing dependents 354 Error checking 355 Changing the Error Checking options 357 Error tracing 358 Evaluating a formula 360 Removing Errors from the Printout 361 Chapter 3: Date and Time Formulas 363 Understanding Dates and Times 363 Changing the date locale 364 Building formulas that calculate elapsed dates 364 Building formulas that calculate elapsed times 365 Using Date Functions 366 TODAY 366 DATE and DATEVALUE .3.