Teach Yourself VISUALLY Complete Excel

Get the basics of Excel and then go beyond with this new instructional visual guide While many users need Excel just to create simple worksheets, many businesses and professionals rely on the advanced features of Excel to handle things like database creation and data analysis. Whatever project you have in mind, this visual guide takes you step by step through what each step should look like. Veteran author Paul McFedries first presents the basics and then gradually takes it further with his coverage of designing worksheets, collaborating between worksheets, working with visual data, database management and analysis, VBA, and more. Offers step–by–step instructions on a variety of tasks, some everyday and some more unique Shows you how to create basic spreadsheets, insert functions and formulas, work with pivot tables, and more Features a two–color interior features numerous screen shots to enhance your learning process for tasks such as creating and managing macros, generating and working with analytics, and using the advanced tools Offering you a deep dive into Microsoft Excel, this book is the complete package for learning Excel with step–by–step, clear visual instructions.

Chapter 1 Working with Excel Get to Know Excel 4 Start Excel 6 Tour the Excel Window 8 Work with the Excel Ribbon 9 Work with the Excel Galleries 10 Customize the Quick Access Toolbar 12 Customize the Ribbon 14 Change the View 16 Configure Excel Options 18 Add Excel to the Windows Taskbar 20 Quit Excel 21 Chapter 1 Working with Excel Learn the Layout of a Worksheet 24 Understanding the Types of Data You Can Use 25 Type Text in a Cell 26 Type a Number in a Cell 28 Type a Date or Time in a Cell 30 Insert a Symbol 32 Edit Cell Data 34 Delete Data from a Cell 36 Chapter 2 Inputting and Editing Data Learn the Layout of a Worksheet 24 Understanding the Types of Data You Can Use 25 Type Text in a Cell 26 Type a Number in a Cell 28 Type a Date or Time in a Cell 30 Insert a Symbol 32 Edit Cell Data 34 Delete Data from a Cell 36 Chapter 3 Working with Ranges Select a Range 40 Fill a Range with the Same Data 42 Fill a Range with a Series of Values 44 Flash Fill a Range 46 Move or Copy a Range 48 Insert a Row or Column 50 Insert a Cell or Range 52 Delete Data from a Range 54 Delete a Range 56 Hide a Row or Column 58 Freeze a Row or Column 60 Merge Two or More Cells 62 Transpose a Row or Column 64 Chapter 4 Working with Range Names Understanding the Benefits of Range Names 68 Define a Range Name 70 Use Worksheet Text to Define a Range Name 72 Navigate a Workbook Using Range Names 74 Change a Range Name 76 Delete a Range Name 78 Paste a List of Range Names 80 Chapter 4 Working with Range Names Understanding the Benefits of Range Names 68 Define a Range Name 70 Use Worksheet Text to Define a Range Name 72 Navigate a Workbook Using Range Names 74 Change a Range Name 76 Delete a Range Name 78 Paste a List of Range Names 80 Chapter 5 Formatting Excel Ranges Change the Font or Font Size 84 Apply Font Effects 86 Change the Font Color 88 Align Text Within a Cell 90 Center Text Across Multiple Columns 92 Rotate Text Within a Cell 94 Add a Background Color to a Range 96 Apply a Number Format 98 Change the Number of Decimal Places Displayed 100 Apply an AutoFormat to a Range 102 Apply a Conditional Format to a Range 104 Apply a Style to a Range 106 Change the Column Width 108 Change the Row Height 110 Wrap Text Within a Cell 112 Add Borders to a Range 114 Copy Formatting from One Cell to Another 116 Chapter 6 Maximizing Excel Customization Create Custom Numeric Formats 120 Create Custom Date and Time Formats 122 Set the Default Width for All Columns 124 Create a Custom Fill List 126 Maximize Work Space by Turning Off Window Elements 128 Create a Custom Cell Style 130 Build a Custom Table Style 132 Create a Custom Color Scheme 134 Create a Custom Font Scheme 136 Save a Custom Workbook Theme 138 Customize the Excel Status Bar 140 Unpin the Ribbon 141 Chapter 7 Making Excel More Efficient Export Ribbon Customizations to a File 144 Configure Excel to Use the Mouse Wheel for Zooming 146 Move in a Different Direction When You Press Enter 148 Automatically Insert a Decimal Point 150 Configure When Excel Warns You About Long Operations 152 Make a Workbook Faster by Saving it as Binary 154 Open a New Window for a Workbook 156 Allow Only Certain Values in a Cell 158 Apply Text or Formatting to Multiple Worksheets 160 Use Dialog Box Controls to Input Data 162 Check for Accessibility Problems 166 Chapter 8 Building Formulas and Functions Understanding Excel Formulas 170 Build a Formula 172 Understanding Excel Functions 174 Add a Function to a Formula 176 Add a Row or Column of Numbers 178 Build an AutoSum Formula 180 Add a Range Name to a Formula 182 Reference Another Worksheet Range in a Formula 184 Move or Copy a Formula 186 Switch to Absolute Cell References 188 Hide the Formula Bar or Ribbon 190 Chapter 9 Getting More Out of Formulas Paste a Formula Result 194 Show Formulas Instead of Results 196 Use a Watch Window to Monitor a Cell Value 198 Create an Array Formula 200 Combine Two Ranges Arithmetically 202 Skip Data Tables When Calculating Workbooks 204 Turn On Iterative Calculations 206 Troubleshoot a Formula by Stepping Through Each Part 208 Display Text Instead of Error Values 210 Check for Formula Errors in a Worksheet 212 Audit a Formula to Locate Errors 214 Chapter 10 Manipulating Worksheets Navigate a Worksheet 218 Rename a Worksheet 219 Create a New Worksheet 220 Move a Worksheet 222 Copy a Worksheet 224 Delete a Worksheet 226 Change the Gridline Color 228 Toggle Worksheet Gridlines On and Off 230 Toggle Worksheet Headings On and Off 231 Set the Worksheet Tab Color 232 Set the Worksheet Background 234 Zoom In on or Out of a Worksheet 236 Split a Worksheet into Two Panes 238 Hide or Unhide a Worksheet 240 Chapter 11 Dealing with Workbooks Create a New Blank Workbook 244 Create a New Workbook from a Template 246 Save a Workbook 248 Open a Workbook 249 Specify Workbook Properties 250 Find Text in a Workbook 252 Replace Text in a Workbook 254 Check Spelling and Grammar 256 Close a Workbook 258 Chapter 12 Managing Workbooks Increase the Number of Recent Documents 262 Open Workbooks Automatically at Start–up 264 Create a Workspace of Workbooks 266 Specify a New Default File Location 268 Set the Default Font and Font Size for New Workbooks 270 Set the Default Number of Worksheets for New Workbooks 272 Repair a Corrupted Workbook File 274 Convert a Workbook to a PDF File 276 Create a Workbook Template 278 Create a New Workbook from an Existing File 280 Compare Two Workbooks Side by Side 282 Check for Features Not Supported by Earlier Versions of Excel 284 Chapter 13 Formatting Workbooks Modify Workbook Colors 288 Set Workbook Fonts 290 Choose Workbook Effects 292 Apply a Workbook Theme 294 Add a Workbook Header 296 Add a Workbook Footer 298 Chapter 14 Printing Workbooks Adjust Workbook Margins 302 Change the Page Orientation 304 Insert a Page Break 305 Choose a Paper Size 306 Set the Print Area 308 Configure Titles to Print on Each Page 310 Preview a Printout 312 Print a Workbook 314 Chapter 15 Working with Tables Understanding Tables 318 Get to Know Table Features 319 Convert a Range to a Table 320 Select Table Data 322 Insert a Table Row 324 Insert a Table Column 325 Delete a Table Row 326 Delete a Table Column 327 Add a Column Subtotal 328 Convert a Table to a Range 330 Apply a Table Style 331 Resize a Table 332 Rename a Table 333 Chapter 16 Analyzing Data Sort a Range or Table 336 Filter a Range or Table 338 Calculate a Conditional Sum 340 Calculate a Conditional Count 341 Create a Data Table 342 Summarize Data with Subtotals 344 Group Related Data 346 Analyze Data with Goal Seek 348 Analyze Data with Scenarios 350 Remove Duplicate Values from a Range or Table 354 Highlight Cells That Meet Some Criteria 356 Highlight the Top or Bottom Values in a Range 358 Analyze Cell Values with Data Bars 360 Analyze Cell Values with Color Scales 362 Analyze Cell Values with Icon Sets 364 Create a Custom Conditional Formatting Rule 366 Consolidate Data from Multiple Worksheets 368 Load the Analysis ToolPak 372 Chapter 17 Analyzing Data with PivotTables Understanding PivotTables 376 Explore PivotTable Features 377 Build a PivotTable from an Excel Range or Table 378 Create a PivotTable from External Data 380 Refresh PivotTable Data 384 Add Multiple Fields to the Row or Column Area 386 Add Multiple Fields to the Data Area 388 Move a Field to a Different Area 390 Group PivotTable Values 392 Apply a PivotTable Filter 394 Filter a PivotTable with a Slicer 396 Apply a PivotTable Style 398 Change the PivotTable Summary Calculation 400 Customize the PivotTable Fields Pane 402 Create a PivotChart from a PivotTable 403 Chapter 18 Visualizing Data with Charts Examine Chart Elements 406 Understanding Chart Types 407 Create a Chart 408 Create a Recommended Chart 410 Add Chart Titles 412 Add Data Labels 413 Position the Chart Legend 414 Display Chart Gridlines 415 Display a Data Table 416 Change the Chart Layout and Style 417 Select a Different Chart Type 418 Change the Chart Source Data 420 Move or Resize a Chart 422 Add a Sparkline to a Cell 424 Chapter 19 Working with Worksheet Graphics Draw a Shape 428 Insert Clip Art 430 Insert a Photo 432 Insert WordArt 434 Insert SmartArt 436 Move or Resize a Graphic 438 Crop a Picture 440 Format a Picture 442 Chapter 20 Importing Data into Excel Understanding External Data 446 Import Data from a Data Source 448 Import Data from an Access Table 450 Import Data from a Word Table 452 Import Data from a Text File 454 Import Data from a Web Page 458 Import Data from an XML File 460 Refresh Imported Data 462 Separate Cell Text into Columns 464 Chapter 21 Querying Data Sources Understanding Microsoft Query 468 Define a Data Source 470 Start Microsoft Query 474 Tour the Microsoft Query Window 475 Add a Table to a Query 476 Add Fields to a Query 478 Filter the Records with Query Criteria 480 Sort Query Records 482 Return the Query Results 484 Chapter 22 Collaborating with Others Add a Comment to a Cell 488 Protect a Worksheet’s Data 490 Protect a Workbook’s Structure 492 Share a Workbook with Other Users 494 Track Workbook Changes 496 Accept or Reject Workbook Changes 498 Save a Workbook to Your SkyDrive 500 Send a Workbook as an E–Mail Attachment 502 Save Excel Data as a Web Page 504 Make a Workbook Compatible with Earlier Versions of Excel 506 Mark Up a Worksheet with a Digital Pen 508 Collaborate on a Workbook Online 510 Chapter 23 Protecting Excel Data Open a Read–Only Version of a Workbook 514 Mark a Workbook as Final to Avoid Accidental Editing 516 Protect Workbooks by Shortening the AutoRecover Interval 518 Specify Cells that Users Can Edit 520 Hide a Formula 522 Protect a Range with a Password 524 Protect Worksheet Data 526 Protect the Structure of a Workbook 528 Restore a Previous Version of a Workbook 530 Chapter 24 Maximizing Security Open a Workbook in Protected View 534 Block Dangerous Excel File Types 536 Set the Macro Security Level 538 Digitally Sign Your Excel Macros 540 Create a Trusted Location for Opening Files 542 Inspect a Workbook for Private Data 544 Assign a Password to a Workbook 546 Turn On Excel Parental Control 548 Disable External Data Connections and Links 550 Apply a Digital Signature to a Workbook 552 Chapter 25 Learning VBA Basics Record a Macro 556 Open the VBA Editor 558 Explore the Excel Object Model 560 Add a Macro to a Module 562 Run a Macro 564 Assign a Shortcut Key to a Macro 566 Assign a Macro to the Quick Access Toolbar 568 Assign a Macro to the Ribbon 570 Index 572