Excel 2007: The Missing Manual

Inhaltsverzeichnis The Missing Credits Introduction Part One: Worksheet Basics Chapter 1. Creating and Navigating Worksheets Creating a Basic Worksheet Starting a New Workbook Adding the Column Titles Adding Data Editing Data Navigating in Excel The Tabs of the Ribbon The Formula Bar The Status Bar Excel Options Saving Files The Excel 2007 File Format Saving Your Spreadsheet in Older Formats Saving Your Spreadsheet As a PDF Saving Your Spreadsheet with a Password Disaster Recovery Opening Files Opening Files-with a Twist Opening Multiple Spreadsheets at Once Chapter 2. Adding Information to Worksheets Adding Different Types of Data Controlling Your Data Types Quick Ways to Add Data AutoComplete AutoCorrect AutoFill AutoFit Undo and Redo Chapter 3. Moving Data Around a Worksheet Selecting Cells Making Continuous Range Selections Making Non-Contiguous Selections Automatically Selecting Your Data Making Selections with the Keyboard Moving Cells Around A Simple Cut-and-Paste or Copy-and-Paste A Fancy Cut-and-Paste or Copy-and-Paste The Clipboard Special Pasting Adding and Moving Columns or Rows Inserting Columns Inserting Rows Inserting Copied or Cut Cells Deleting Columns and Rows Chapter 4. Managing Worksheets and Workbooks Worksheets and Workbooks Adding, Removing, and Hiding Worksheets Naming and Rearranging Worksheets Grouping Sheets Moving Worksheets from One Workbook to Another Find and Replace The Basic Find Find All More Advanced Searches Finding Formatted Cells Finding and Replacing Values Spell Check Spell Checking Options Chapter 5. Formatting Cells Formatting Cell Values Formatting Numbers Formatting Dates and Times Special Formats for Special Numbers Custom Formats Formatting Cell Appearance Alignment and Orientation Fonts and Color Borders and Fills Drawing Borders by Hand Chapter 6. Smart Formatting Tricks The Format Painter Styles and Themes Custom Styles Modifying Styles Transferring Styles Between Workbooks Themes: A Package of Styles Modifying Themes Conditional Formatting The Basics of Conditional Formatting Highlighting Specific Values Data Bars Color Scales Icon Sets Fine-Tuning a Formatting Rule Using Multiple Rules Chapter 7. Viewing and Printing Worksheets Controlling Your View Zooming Viewing Distant Parts of a Spreadsheet at Once Freezing Columns or Rows Hiding Data Saving View Settings Viewing Multiple Workbooks at Once Printing How to Print an Excel File Quick Printing Previewing Your Printout Creating Headers and Footers Customizing Print Settings Controlling Pagination Page Breaks Scaling Page Break Preview: A Bird's-Eye View of Your Worksheet Part Two: Formulas and Functions Chapter 8. Building Basic Formulas Creating a Basic Formula Excel's Order of Operations Cell References How Excel Formats Cells That Contain Cell References Functions Formula Errors Logical Operators Formula Shortcuts Point-and-Click Formula Creation Point-and-Click Formula Editing The Formulas Tab Using the Insert Function Button Copying Formulas Absolute Cell References Partially Fixed References Referring to Other Worksheets and Workbooks Chapter 9. Math and Statistical Functions Rounding Numbers ROUND( ), ROUNDDOWN( ), ROUNDUP( ): Rounding Numbers MROUND( ), CEILING( ), and FLOOR( ): More Rounding Functions INT( ) and TRUNC( ): Chopping Off Non-Whole Numbers EVEN( ) and ODD( ): Rounding Up to Even or Odd Values Groups of Numbers SUM( ): Summing Up Numbers COUNT( ), COUNTA( ), and COUNTBLANK( ): Counting Items in a List MAX( ) and MIN( ): Finding Maximum and Minimum Values LARGE( ), SMALL( ), and RANK( ): Ranking Your Numbers AVERAGE( ) and MEDIAN( ): Finding Average or Median Values MODE( ): Finding Numbers That Frequently Occur in a List PERCENTILE( ) and PERCENTRANK( ): Advanced Ranking Functions FREQUENCY( ): Identifying to Which Category a Number Belongs General Math Functions PRODUCT( ), FACT( ), POWER( ), and SQRT( ): Products, Factorials, Powers, and Square Roots QUOTIENT( ) and MOD( ): Higher Division ABS( ) and SIGN( ): Absolute Value and Determining a Number's Sign RAND( ) and RANDBETWEEN( ): Generating Random Numbers GCD( ) and LCM( ): Greatest and Least Common Denominator COMBIN( ) and PERMUT( ): Figuring Combinations and Permutations Trigonometry and Advanced Math Advanced Statistics Chapter 10. Financial Functions The World of Finance Financial Functions FV( ): Future Value PV( ): Present Value PMT( ), PPMT( ), and IPMT( ): Calculating the Number of Payments You Need to Make NPER( ): Figuring Out How Much Time You'll Need to Pay Off a Loan or Meet an Investment Target RATE( ): Figuring the Interest Rate You Need to Achieve Future Value NPV( ) and IRR( ): Net Present Value and Internal Rate of Return Depreciation Other Financial Functions Chapter 11. Manipulating Dates, Times, and Text Manipulating Text CONCATENATE( ): Joining Strings of Text Together LEFT( ), MID( ), and RIGHT( ): Copying Portions of a Text String LEN( ), FIND( ), and SEARCH( ): Counting Characters in a String UPPER( ), LOWER( ), and PROPER( ): Changing Capitalization TRIM( ) and CLEAN( ): Removing Unwanted Spaces and Non-Printing Characters SUBSTITUTE( ): Replacing One Sequence of Characters with Another TEXT( ), VALUE( ), FIXED( ), and DOLLAR( ): Converting Text to Numbers and Vice Versa Other Text Functions Manipulating Dates and Times Math with Dates and Times Using Dates and Times with Ordinary Functions Date and Time Functions TODAY( ) and NOW( ): Inserting the Current Date and Time DATE( ) and TIME( ): Dates and Times in Calculations DAY( ), MONTH( ), and YEAR( ): More Date Calculations HOUR( ), MINUTE( ), SECOND( ): More Time Calculations WEEKDAY( ): Determining the Day of the Week DATEDIF( ): Calculating the Difference Between Dates DATEVALUE( ) and TIMEVALUE( ): Converting Dates and Times into Serial Numbers DAYS360( ): Finding Out the Number of Days Between Two Dates EDATE( ): Calculating Future Dates YEARFRAC( ): Calculating the Percentage of a Year Between Two Dates EOMONTH( ): Finding the Last Day of Any Month NETWORKDAYS( ): Counting the Number of Business Days WORKDAY( ): Figuring Out When Days Will Fall in the Future WEEKNUM( ): Figuring Out in Which Week a Date Falls Chapter 12. Lookup, Reference, and Information Functions The Basic Lookup VLOOKUP( ): Vertical Lookups HLOOKUP( ): Horizontal Lookups Advanced Lookups MATCH( ): Finding the Position of Items in a Range INDEX( ): Retrieving the Value from a Cell Performing a "Left Lookup" Performing a Double Lookup The Lookup Wizard OFFSET( ): Moving Cell References to a New Location Other Reference and Lookup Functions INDIRECT( ) and ADDRESS( ): Working with Cell References Stored As Text TRANSPOSE( ): Changing Rows into Columns and Vice Versa Information Functions The "IS" Functions: Checking the Value Inside a Cell TYPE( ) and ERROR.TYPE( ): Finding a Value's Data Type or Error Type INFO( ) and CELL( ): Gathering Info About Your Computer and Your Worksheet's Cells Tutorial: Quickly Generating Invoices from a Product Catalog Chapter 13. Advanced Formula Writing and Troubleshooting Conditions in Formulas IF( ): Building Conditional Formulas COUNTIF( ): Counting Only the Cells You Specify SUMIF( ): Adding Only the Cells You Specify COUNTIFS( ) and SUMIFS( ): Counting and Summing Using Multiple Criteria Descriptive Names for Cell References Creating and Using a Named Range Creating Slightly Smarter Named Ranges Naming Formulas and Constants Managing Named Ranges Automatically Creating Named Ranges Applying Names to Existing Formulas Variable Data Tables Creating a One-Variable Data Table Creating a Two-Variable Data Table Controlling Recalculation Solving Formula Errors Step-by-Step Evaluation Tracing Precedents and Dependents Error Checking Part Three: Organizing Worksheets Chapter 14. Tables: List Management Made Easy The Basics of Tables Creating a Table Formatting a Table Editing a Table Selecting Parts of a Table Sorting and Filtering a Table Applying a Simple Sort Order Sorting with Multiple Criteria Sorting by Color Filtering with the List of Values Creating Smarter Filters Dealing with Duplicate Rows Highlighting Duplicates Removing Duplicates Automatically Performing Table Calculations Dynamic Calculations Column Names Table Names The Total Row The SUBTOTAL( ) Function The Database Functions Chapter 15. Grouping and Outlining Data Basic Data Grouping Creating a Group Nesting Groups Within Groups Summarizing Your Data Combining Data from Multiple Tables Grouping Timesavers Auto Outline Automatic Subtotaling Chapter 16. Templates Understanding Templates Creating a New Workbook from a Template Downloading Templates (Method 1: The New Workbook Dialog Box) Downloading Templates (Method 2: The Office Online Web Site) Creating Templates Understanding Custom Templates Building a Custom Template Sharing Templates with Others Part Four: Charts and Graphics Chapter 17. Creating Basic Charts Charting 101 Embedded and Standalone Charts Creating a Chart with the Ribbon The Chart Tools Ribbon Tabs Basic Tasks with Charts Moving and Resizing a Chart Creating a Standalone Chart Editing and Adding to Chart Data Changing the Chart Type Printing Charts Practical Charting Charts with Multiple Series of Numbers Controlling the Data Excel Plots on the X-Axis Data That Uses a Date or Time Scale Non-Contiguous Chart Ranges Changing the Order of Your Data Series Changing the Way Excel Plots Blank Values Chart Types Column Bar Line Pie Area XY (Scatter) Stock Surface Donut Bubble Radar Chapter 18. Formatting and Perfecting Charts Chart Styles and Layouts Chart Styles Chart Layouts Adding Chart Elements Adding Titles Adding a Legend Adding Data Labels to a Series Adding Individual Data Labels Adding a Data Table Selecting Chart Elements Formatting Chart Elements Coloring the Background Fancy Fills Fancy Borders and Lines Formatting Data Series and Data Points Reusing Your Favorite Charts with Templates Improving Your Charts Controlling a Chart's Scale Adding a Trendline Adding Error Bars to Scientific Data Formatting 3-D Charts Changing the Shape of a 3-D Column Advanced Charting Exploding Slices in a Pie Grouping Slices in a Pie Gaps, Widths, and Overlays in a Column Chart Creating Combination Charts Chapter 19. Inserting Graphics Adding Pictures to a Worksheet Inserting a Picture Positioning and Resizing a Picture Picture Touch-Up Compressing Pictures Cropping and Shaping a Picture Picture Borders, Effects, and Styles Excel's Clip Art Library Drawing Shapes Drawing a Shape Adding Text to a Shape Selecting and Arranging Shapes Connecting Shapes SmartArt Part Five: Advanced Data Analysis Chapter 20. Scenarios and Goal Seeking Using Scenarios Creating a New Scenario Managing Scenarios Creating a Summary Report Using Goal Seek Goal Seeking with Complex Equations Solver Understanding Solver Defining a Problem in Solver Advanced Solver Solutions Saving Solver Models Configuring Solver Chapter 21. Pivot Tables Summary Tables Revisited Life Without Pivot Tables Life with Pivot Tables Building Pivot Tables Preparing a Pivot Table Pivot Table Regions Laying Out a Pivot Table Formatting a Pivot Table Rearranging a Pivot Table Multi-Layered Pivot Tables Hiding and Showing Details Fine-Tuning Pivot Table Calculations Changing the Type of Calculation Adding a Calculated Field Filtering a Pivot Table Report Filtering Group Filtering Pivot Charts Creating a Pivot Chart Manipulating a Pivot Chart Part Six: Sharing Data with the Rest of the World Chapter 22. Protecting Your Workbooks Understanding Excel's Safeguards Data Validation Settings Input Message Error Alert Data Validation with Formulas and Cell References Data Validation with Lists Locked and Hidden Cells Protecting a Worksheet Protecting the Entire Workbook Protecting Cell Ranges (with More Passwords) Allowing Specific Windows Users to Edit a Range Chapter 23. Worksheet Collaboration Preparing Your Workbook Document Properties Distributing a Document Adding Comments Inserting a Comment Showing and Hiding Comments Fine-Tuning Comments Reviewing Comments Printing Comments Tracking Changes Switching On Change Tracking Understanding the Change Log Highlighting Changes Examining the Change Log Accepting and Rejecting Changes Merging Multiple Revisions into One Workbook Sharing Your Workbook Multiple Users Without Workbook Sharing Turning On Workbook Sharing Workbook Sharing in Action Chapter 24. Querying Databases and XML Files Excel and Databases Connecting to an Access Database Refreshing Data Data Source Security Connecting to a SQL Server Database Reusing Your Database Connection Understanding XML What Is XML, Really? Three Rules of XML XML Files and Schemas Excel and XML Mapping a Simple Document Importing and Exporting XML Mapping Lists Gaining the Benefits of XML Mapping Chapter 25. Exchanging Data with Other Programs Sharing Information in Windows Embedding and Linking Objects Exporting Charts out of Excel Editing a Linked Object Editing an Embedded Object Importing Objects into Excel Transferring Data Exporting Tables of Data Importing Tables of Data Importing Text Files Chapter 26. Connecting Worksheets to the Web Putting Worksheets on the Web Saving an HTML File Performing Web Queries The Limitations of Web Queries Creating a Web Query The Research Pane-a Web Query Alternative Using Hyperlinks Adding a Hyperlink to a Web Page or Document Adding a Hyperlink to a Worksheet Location The HYPERLINK( ) Function: Creating a Link on Your Own Part Seven: Programming Excel Chapter 27. Automating Tasks with Macros Macros 101 Macro-Free and Macro-Enabled Workbooks The Macro Recorder Relative and Absolute Recording Where Macros Live Recording a Macro Playing a Macro Macro Security The Trust Center Setting Up a Trusted Location Creating Practical Macros Inserting a Header Alternating Row Formatting A Combined Task Placing a Macro on the Quick Access Toolbar Attaching a Macro to a Button Inside a Worksheet Chapter 28. Programming Spreadsheets with VBA The Visual Basic Editor The Project Window Modules and Macros Finding and Moving Macros Debugging a Macro Understanding Macro Code The Anatomy of a Macro Objects 101 Using Properties and Methods Hunting for Objects Exploring the VBA Language Entering Text in the Current Cell Moving to Other Cells Editing Specific Cells Formatting Cells Using Variables Making Decisions Repeating Actions with a Loop Creating Custom Functions Part Eight: Appendix Appendix. Customizing the Quick Access Toolbar Index