Excel® Data Analysis: Your visual blueprint™ for creating and analyzing data, charts, and PivotTables, 3rd Edition

Excel® Data Analysis: Your visual blueprint™ for creating and analyzing data, charts, and PivotTables, 3rd Edition

by Denise Etheridge
Released July 2010
Publisher(s): Visual
ISBN: 9780470591604

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

Advanced techniques for Excel power users

Crunch and analyze Excel data the way the professionals do with this clean, uncluttered, visual guide to advanced Excel techniques. Using numerous screenshots and easy-to-follow numbered steps, this book clearly shows you how to perform professional-level modeling, charting, data access, data slicing, and other functions. You'll find super techniques for getting the most out of Excel's statistical and financial functions, Excel PivotTables and PivotCharts, Excel Solver, and more.

  • Provides a clear look at power-using Excel, the world's leading spreadsheet application from Microsoft, and part of the new Microsoft Office 2010 suite

  • Expands your Excel knowledge and helps you use Excel data more efficiently

  • Demonstrates how to retrieve data from databases;; cut, slice, and pivot data using PivotTables; model data and chart data; and use advanced formulas

  • Explores all features and functions in friendly, two-color pages packed with screenshots, numbered steps, and other visual graphics that clearly show you how to accomplish tasks

  • Includes practical examples, tips, and advice to help you get the most out of Excel's features and functions

Learn Excel at the highest levels with this practical guide.

Table of contents

  1. Copyright
    1. Trademark Acknowledgments
    2. Contact Us
  2. Credits
  3. About the Author
  4. Author's Acknowledgments
  5. How to Use This Visual Blueprint Book
  6. 1. GETTING STARTED
    1. 1.1. Introducing Data Analysis with Excel
    2. 1.2. Tour the Excel Window
    3. 1.3. Explore the Ribbon
    4. 1.4. Using the Mini Toolbar and Context Menu
    5. 1.5. Select Options on the Status Bar
    6. 1.6. Take a Look at Backstage View
    7. 1.7. Upload a File to Excel's Web-based Application
    8. 1.8. Create a File Using Excel's Web-based Application
    9. 1.9. Enter Data
    10. 1.10. Select Cells
  7. 2. FORMATTING A WORKSHEET
    1. 2.1. Using the Ribbon to Format Numbers
    2. 2.2. Using the Format Cells Dialog Box
    3. 2.3. Understanding Dates and Times
    4. 2.4. Format Percentages
    5. 2.5. Format Fractions
    6. 2.6. Format in Scientific Notation
    7. 2.7. Format as Text
    8. 2.8. Add a Border
    9. 2.9. Change the Font or Font Size
    10. 2.10. Add a Background Color
    11. 2.11. Change the Font Color
    12. 2.12. Bold, Underline, or Italicize
    13. 2.13. Align Data
    14. 2.14. Rotate Data
    15. 2.15. Wrap Text
    16. 2.16. Merge and Center
    17. 2.17. Apply a Style
    18. 2.18. Using Format Painter
    19. 2.19. Clear Formats
  8. 3. STRUCTURING YOUR WORKBOOK
    1. 3.1. Cut, Copy, and Paste Cells
    2. 3.2. Using Live Preview with Paste
    3. 3.3. Paste from the Office Clipboard
    4. 3.4. Insert or Delete
    5. 3.5. Find and Replace Information
    6. 3.6. Change the Name of a Worksheet
    7. 3.7. Change Column Widths or Row Heights
    8. 3.8. Hide Columns or Rows
    9. 3.9. Hide a Worksheet
    10. 3.10. Move or Copy a Worksheet
    11. 3.11. Freeze Worksheet Titles
    12. 3.12. Hide Gridlines, Headings, or the Formula Bar
  9. 4. CREATING FORMULAS
    1. 4.1. Understanding Formulas
    2. 4.2. Calculate with an Operator
    3. 4.3. Calculate Using a Function and Cell Addresses
    4. 4.4. Create an Array Formula
    5. 4.5. Using the Sum, Average, Count, Min, and Max Functions
    6. 4.6. Create a Formula that Refers to Another Worksheet
    7. 4.7. Understanding Relative and Absolute Cell Addresses
    8. 4.8. Edit Formulas
    9. 4.9. Name Cells and Ranges
    10. 4.10. Define and Display Constants
    11. 4.11. Create Formulas That Include Names
    12. 4.12. Check Formulas for Errors
    13. 4.13. Trace Precedents and Dependents
  10. 5. USING FUNCTIONS
    1. 5.1. Understanding the Function Wizard
    2. 5.2. Round a Number
    3. 5.3. Create a Conditional Formula
    4. 5.4. Calculate a Conditional Sum
    5. 5.5. Calculate a Conditional Count
    6. 5.6. Find the Square Root
    7. 5.7. Retrieve Column or Row Numbers
    8. 5.8. Using VLOOKUP
    9. 5.9. Determine the Location of a Value
    10. 5.10. Using INDEX
    11. 5.11. Perform Date and Time Calculations
  11. 6. USING FINANCIAL FUNCTIONS
    1. 6.1. Calculate Future Value
    2. 6.2. Calculate Present Value
    3. 6.3. Calculate Loan Payments
    4. 6.4. Calculate Principal or Interest
    5. 6.5. Calculate the Interest Rate
    6. 6.6. Calculate the Internal Rate of Return
    7. 6.7. Calculate Straight-Line Depreciation
    8. 6.8. Calculate Declining Balance Depreciation
    9. 6.9. Calculate Double-Declining Balance Depreciation
    10. 6.10. Calculate Sum-of-the-Years-Digits Depreciation
  12. 7. USING STATISTICAL FUNCTIONS AND TOOLS
    1. 7.1. Calculate an Average
    2. 7.2. Calculate a Conditional Average
    3. 7.3. Calculate the Median or the Mode
    4. 7.4. Calculate Rank
    5. 7.5. Determine the Nth Largest Value
    6. 7.6. Calculate Frequency
    7. 7.7. Calculate Variance and Standard Deviation
    8. 7.8. Find the Correlation
    9. 7.9. Install Excel Add-Ins
    10. 7.10. Calculate a Moving Average
    11. 7.11. Compare Variances
    12. 7.12. Using the Data Analysis Toolpak to Determine Rank and Percentile
    13. 7.13. Calculate Descriptive Statistics
  13. 8. ORGANIZING WORKSHEET DATA
    1. 8.1. Enter Data with a Form
    2. 8.2. Perform Simple Sorts and Filters
    3. 8.3. Perform Multilevel Sorts
    4. 8.4. Perform a Custom Sort
    5. 8.5. Sort by Cell Color, Font Color, or Cell Icon
    6. 8.6. Perform Complex Filters
    7. 8.7. Enter Criteria to Find Records
    8. 8.8. Using Advanced Filtering Techniques
    9. 8.9. Filter Duplicate Records
    10. 8.10. Count Filtered Records
    11. 8.11. Subtotal Records
    12. 8.12. Using Auto Outline
    13. 8.13. Define Data as a Table
    14. 8.14. Modify a Table Style
    15. 8.15. Using Database Functions with a Table
  14. 9. WORKING WITH PIVOTTABLES
    1. 9.1. Create a PivotTable
    2. 9.2. Modify a PivotTable Layout
    3. 9.3. Summarize PivotTable Values
    4. 9.4. Create a PivotTable Calculated Field
    5. 9.5. Group the Rows or Columns in a PivotTable
    6. 9.6. Apply a Style to a PivotTable
    7. 9.7. Filter a PivotTable
    8. 9.8. Sort a PivotTable
    9. 9.9. Retrieve Values from a PivotTable
    10. 9.10. Using Slicer
  15. 10. CHARTING DATA
    1. 10.1. Create a Chart
    2. 10.2. Add Chart Details
    3. 10.3. Create a Combination Chart
    4. 10.4. Change the Chart Type
    5. 10.5. Add or Remove Chart Data
    6. 10.6. Add Sparklines
    7. 10.7. Create a Trendline
    8. 10.8. Add Error Bars
    9. 10.9. Create a Histogram
    10. 10.10. Chart Filtered Data
    11. 10.11. Create a PivotChart
    12. 10.12. Filter a PivotChart
  16. 11. WORKING WITH EXTERNAL DATA
    1. 11.1. Paste Link into Word
    2. 11.2. Embed a Worksheet
    3. 11.3. Hyperlink a Worksheet
    4. 11.4. Query a Web Site
    5. 11.5. Import a Text File
    6. 11.6. Import an Access Database
    7. 11.7. Query an Access Database
  17. 12. USING DATA ANALYSIS TOOLS AND TECHNIQUES
    1. 12.1. Perform What-If Analysis
    2. 12.2. Optimize a Result with Goal Seek
    3. 12.3. Using Solver
    4. 12.4. Solve a Formula with a Data Table
    5. 12.5. Extend a Series with Auto Fill
    6. 12.6. Join Text
    7. 12.7. Add a Calculator
    8. 12.8. Consolidate Worksheets
    9. 12.9. Highlight Cells that Meet Your Criteria
    10. 12.10. Find the Highest or Lowest Ranked Values
    11. 12.11. Add Data Bars to Your Worksheet
    12. 12.12. Add Icon Sets to Your Worksheet
    13. 12.13. Remove Conditional Formatting
    14. 12.14. Change Conditional Formatting Rules
    15. 12.15. Paste with Paste Special
  18. 13. SHARING YOUR WORKBOOK WITH OTHERS
    1. 13.1. Validate with a Validation List
    2. 13.2. Validate with Data Entry Rules
    3. 13.3. Add Comments to Your Worksheet
    4. 13.4. Track Changes
    5. 13.5. Protect Your Worksheet
    6. 13.6. Save Your Workbook as a Template
    7. 13.7. Choose a Format When Saving a Workbook
    8. 13.8. Print Your Workbook
    9. 13.9. Print Multiple Areas of Your Worksheet
  19. 14. AUTOMATING WITH MACROS
    1. 14.1. Introducing Macros
    2. 14.2. Set Macro Security
    3. 14.3. Create a Digital Signature
    4. 14.4. Record a Macro
    5. 14.5. Assign a Digital Signature to a Macro
    6. 14.6. Run a Macro
    7. 14.7. Create and Launch a Keyboard Shortcut
    8. 14.8. Assign a Macro to the Quick Access Toolbar
    9. 14.9. Delete a Macro
  20. 15. ILLUSTRATING YOUR WORKSHEETS
    1. 15.1. Place a Screenshot in Your Worksheet
    2. 15.2. Insert Clip Art into Your Worksheet
    3. 15.3. Crop a Clip Art Illustration or a Photograph
    4. 15.4. Insert a Picture into Your Worksheet
    5. 15.5. Recolor a Picture
    6. 15.6. Adjust the Sharpness, Brightness, and Contrast
    7. 15.7. Compress a Picture
    8. 15.8. Add a Border
    9. 15.9. Add a Picture Effect
    10. 15.10. Apply a Picture Style
    11. 15.11. Add an Artistic Effect
    12. 15.12. Remove a Background
  21. 16. ILLUSTRATING YOUR IDEAS
    1. 16.1. Insert a Text Box
    2. 16.2. Format a Text Box
    3. 16.3. Insert a Shape
    4. 16.4. Add Text to a Shape
    5. 16.5. Change the Size of an Object
    6. 16.6. Rotate an Object
    7. 16.7. Change the Stacking Order
    8. 16.8. Group Objects
    9. 16.9. Align Objects
    10. 16.10. Insert a Symbol
    11. 16.11. Using SmartArt Graphics
    12. 16.12. Modify a SmartArt Graphic
    13. 16.13. Apply a SmartArt Style
    14. 16.14. Apply a SmartArt Layout
  22. 17. CUSTOMIZING EXCEL
    1. 17.1. Add a Form Control to a Worksheet
    2. 17.2. Assign Values to a Form Control
    3. 17.3. Add a Macro to a Form Control
    4. 17.4. Customize the Quick Access Toolbar
    5. 17.5. Customize the Ribbon
  23. A. USING EXCEL KEYBOARD SHORTCUTS
    1. A.1. Using Excel Keyboard Shortcuts

Product information

  • Title: Excel® Data Analysis: Your visual blueprint™ for creating and analyzing data, charts, and PivotTables, 3rd Edition
  • Author(s): Denise Etheridge
  • Release date: July 2010
  • Publisher(s): Visual
  • ISBN: 9780470591604