Microsoft Office Excel® 2007 Data Analysis: Your Visual Blueprint™ for Creating and Analyzing Data, Charts, and PivotTables

Microsoft Office Excel® 2007 Data Analysis: Your Visual Blueprint™ for Creating and Analyzing Data, Charts, and PivotTables

Released July 2007
Publisher(s): Visual
ISBN: 9780470132296

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Welcome to the only guidebook series that takes a visual approach to professional-level computer topics. Open the book and you'll discover step-by-step screen shots that demonstrate over 110 Excel data analysis techniques, including:

* Identifying trends in your data

*Sorting, filtering, and identifying lists

*Creating, editing, and checking formulas

*Calculating interest rates and depreciation

*Performing simple sorts and filters

*Hiding rows or columns in a PivotTable

*Adding and removing chart data

*Querying an Access database

*Assigning digital signatures

*Solving a formula with a data table

"I was stuck on an Excel problem for two days. Finally, I opened one of your books, and there was a macro to accomplish exactly what I needed! You made me look good to the boss."

-Rob L. Meerscheidt(The Woodlands, TX)

* High-resolution screen shots demonstrate each task

*Succinct explanations walk you through step by step

*Two-page lessons break big topics into bite-sized modules

*"Apply It" and "Extra" sidebars highlight useful tips

Table of contents

  1. Copyright
  2. Praise For Visual Books...
  3. Credits
  4. About the Author
  5. Author's Acknowledgments
  6. HOW TO USE THIS BOOK
    1. Who Needs This Book
    2. Book Organization
    3. What You Need to Use This Book
    4. The Conventions in This Book
    5. What's on the Web Site
  7. 1. GETTING STARTED
    1. 1.1. Introduction to Data Analysis with Excel
    2. 1.2. Understanding the Excel Window
    3. 1.3. Enter Data
    4. 1.4. Format Numbers
    5. 1.5. Format Cells
    6. 1.6. Select Data
    7. 1.7. Copy, Cut, and Paste Cells
    8. 1.8. Copy with the Office Clipboard
    9. 1.9. Insert and Delete Cells
    10. 1.10. Find and Replace
    11. 1.11. Find and Replace Formats
  8. 2. CREATING FORMULAS
    1. 2.1. Understanding Formulas
    2. 2.2. Create Formulas
    3. 2.3. Edit Formulas
    4. 2.4. Name Cells and Ranges
    5. 2.5. Define and Display Constants
    6. 2.6. Create Formulas that Include Names
    7. 2.7. Check Formulas for Errors
    8. 2.8. Trace Precedents and Dependents
  9. 3. CREATING AND USING FUNCTIONS
    1. 3.1. Understanding the Function Wizard
    2. 3.2. Round a Number
    3. 3.3. Determine the Nth Largest Value
    4. 3.4. Create a Conditional Formula
    5. 3.5. Calculate a Conditional Sum
    6. 3.6. Calculate Products and Square Roots
    7. 3.7. Look Up Information
    8. 3.8. Determine the Location of a Value
    9. 3.9. Perform Time Calculations
    10. 3.10. Perform Date Calculations
  10. 4. USING FINANCIAL FUNCTIONS
    1. 4.1. Calculate Future Value
    2. 4.2. Calculate Present Value
    3. 4.3. Calculate Loan Payments
    4. 4.4. Calculate Principal or Interest
    5. 4.5. Calculate the Interest Rate
    6. 4.6. Calculate the Internal Rate of Return
    7. 4.7. Calculate Straight-Line Depreciation
    8. 4.8. Calculate Declining Balance Depreciation
    9. 4.9. Calculate Double-Declining Balance Depreciation
    10. 4.10. Calculate Sum-of-the-Years-Digits Depreciation
  11. 5. USING STATISTICAL FUNCTIONS AND TOOLS
    1. 5.1. Calculate an Average
    2. 5.2. Calculate the Median or the Mode
    3. 5.3. Calculate Rank
    4. 5.4. Calculate Frequency
    5. 5.5. Calculate Variance and Standard Deviation
    6. 5.6. Find the Correlation
    7. 5.7. Install Excel Add-Ins
    8. 5.8. Calculate a Moving Average
    9. 5.9. Compare Variances
    10. 5.10. Using the Data Analysis Toolpak to Determine Rank and Percentile
    11. 5.11. Calculate Descriptive Statistics
  12. 6. ORGANIZING WORKSHEET DATA
    1. 6.1. Enter Data with a Form
    2. 6.2. Filter Duplicate Records
    3. 6.3. Perform Simple Sorts and Filters
    4. 6.4. Perform Complex Sorts
    5. 6.5. Sort by Cell Color, Font Color, or Icon
    6. 6.6. Perform Complex Filters
    7. 6.7. Enter Criteria to Find Records
    8. 6.8. Filter by Multiple Criteria
    9. 6.9. Subtotal Sorted Data
    10. 6.10. Count Filtered Records
    11. 6.11. Define Data as a Table
    12. 6.12. Modify a Table Style
  13. 7. WORKING WITH PIVOTTABLES
    1. 7.1. Create a PivotTable
    2. 7.2. Modify PivotTable Data and Layout
    3. 7.3. Compute Subtotals and Grand Totals
    4. 7.4. Create a PivotTable Calculated Field
    5. 7.5. Hide Rows or Columns in a PivotTable
    6. 7.6. Sort a PivotTable
    7. 7.7. Retrieve Values from a PivotTable
  14. 8. CHARTING DATA
    1. 8.1. Create a Chart
    2. 8.2. Add Chart Details
    3. 8.3. Change the Chart Type
    4. 8.4. Add a Trendline
    5. 8.5. Add and Remove Chart Data
    6. 8.6. Add Error Bars
    7. 8.7. Create a Histogram
    8. 8.8. Chart Filtered Data
    9. 8.9. Create a PivotChart
    10. 8.10. Create a Combination Chart
  15. 9. WORKING WITH EXTERNAL DATA
    1. 9.1. Paste Link into Word
    2. 9.2. Embed a Worksheet
    3. 9.3. Hyperlink a Worksheet
    4. 9.4. Query a Web Site
    5. 9.5. Import a Text File
    6. 9.6. Import an Access Database
    7. 9.7. Query an Access Database
  16. 10. USEFUL DATA ANALYSIS TOOLS AND TECHNIQUES
    1. 10.1. Perform What-If Analysis
    2. 10.2. Optimize a Result with Goal Seek
    3. 10.3. Solve a Formula with a Data Table
    4. 10.4. Extend a Series with AutoFill
    5. 10.5. Work with Multiple Windows
    6. 10.6. Let Excel Read Back Your Data
    7. 10.7. Add a Calculator
    8. 10.8. Change Text to Numbers
    9. 10.9. Convert a Row to a Column
    10. 10.10. Consolidate Worksheets
    11. 10.11. Conditionally Format Your Worksheet
    12. 10.12. Change Conditional Formatting Rules
    13. 10.13. Paste with Paste Special
    14. 10.14. Insert Photographs into Your Worksheet
  17. 11. SHARING YOUR WORKBOOK WITH OTHERS
    1. 11.1. Validate with a Validation List
    2. 11.2. Validate with Data Entry Rules
    3. 11.3. Add Comments to Your Worksheet
    4. 11.4. Track Changes
    5. 11.5. Protect Your Worksheet
    6. 11.6. Save Your Workbook as a Template
    7. 11.7. Choose a Format When Saving a Workbook
    8. 11.8. Print Your Workbook
    9. 11.9. Print Multiple Areas of Your Worksheet
    10. 11.10. Add a Form Control to a Worksheet
    11. 11.11. Assign Values to a Form Control
    12. 11.12. Add a Macro to a Form Control
  18. 12. AUTOMATING WITH MACROS
    1. 12.1. Introducing Macros
    2. 12.2. Set Macro Security
    3. 12.3. Create a Digital Signature
    4. 12.4. Record a Macro
    5. 12.5. Assign a Digital Signature to a Macro
    6. 12.6. Run a Macro
    7. 12.7. Create and Launch a Keyboard Shortcut
    8. 12.8. Assign a Macro to the Quick Access Toolbar
    9. 12.9. Delete a Macro
  19. A. EXCEL KEYBOARD SHORTCUTS
    1. A.1. Using Keyboard Shortcuts with the Ribbon
    2. A.2. Excel Keyboard Shortcuts
  20. B. EXCEL FUNCTION QUICK REFERENCE
    1. B.1. Excel Function Quick Reference
  21. C. FORMULA BASICS
    1. C.1. Formula Basics

Product information

  • Title: Microsoft Office Excel® 2007 Data Analysis: Your Visual Blueprint™ for Creating and Analyzing Data, Charts, and PivotTables
  • Author(s):
  • Release date: July 2007
  • Publisher(s): Visual
  • ISBN: 9780470132296