Excel® 2010 Workbook for Dummies®

Excel® 2010 Workbook for Dummies®

by Greg Harvey
Released September 2010
Publisher(s): For Dummies
ISBN: 9780470489604

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

Reinforce your understanding of Excel with these Workbook exercises

Boost your knowledge of important Excel tasks by putting your skills to work in real-world situations. The For Dummies Workbook format provides more than 100 exercises that help you create actual results with Excel so you can gain proficiency.

Perfect for students, people learning Excel on their own, and financial professionals who must plan and execute complex projects in Excel, Excel 2010 Workbook For Dummies helps you discover all the ways this program can work for you.

  • Excel is the world's most popular number-crunching program, and For Dummies books are the most popular guides to Excel

  • The Workbook approach offers practical application, with more than 100 exercises to work through and plenty of step-by-step guidance

  • This guide covers the new features of Excel 2010, includes a section on creating graphic displays of information, and offers ideas for financial planners

  • Also provides exercises on using formulas and functions, managing and securing data, and performing data analysis

  • A companion CD-ROM includes screen shots and practice materials

    • Excel 2010 Workbook For Dummies helps you get comfortable with Excel so you can take advantage of all it has to offer.

    Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

    Table of contents

    1. Copyright
    2. About the Author
    3. Author's Acknowledgments
    4. Publisher's Acknowledgments
    5. Introduction
      1. About This Book
      2. Conventions Used in This Book
      3. Foolish Assumptions
      4. How This Book Is Organized
        1. Part I: Creating Spreadsheets
        2. Part II: Using Formulas and Functions
        3. Part III: Working with Graphics
        4. Part IV: Managing and Securing Data
        5. Part V: Doing Data Analysis
        6. Part VI: Macros and Visual Basic for Applications
        7. Part VII: The Part of Tens
      5. Using the Practice Material on the CD-ROM
      6. Icons Used in This Book
      7. Where to Go from Here
    6. I. Creating Spreadsheets
      1. 1. Getting Familiar with the Excel 2010 Interface
        1. 1.1. Identifying the Parts of the Excel Display Screen
        2. 1.2. Selecting Commands on the File Menu in the Backstage View
        3. 1.3. Selecting Commands from the Ribbon
          1. 1.3.1. Adding a custom tab to the Excel Ribbon
          2. 1.3.2. Adding commands to groups on your custom tab
        4. 1.4. Selecting Commands on the Quick Access Toolbar
          1. 1.4.1. Customizing the Quick Access toolbar
          2. 1.4.2. Adding more commands to the Quick Access toolbar
      2. 2. Entering the Spreadsheet Data
        1. 2.1. Launching Excel
        2. 2.2. Opening a New Workbook
        3. 2.3. Moving Around the Workbook
          1. 2.3.1. Moving within the displayed area
          2. 2.3.2. Moving to a new area of the worksheet
          3. 2.3.3. Moving to a different sheet in the workbook
        4. 2.4. Selecting Cell Ranges
        5. 2.5. Making Cell Entries
          1. 2.5.1. Entering data in a single cell
          2. 2.5.2. Entering data in a cell range
          3. 2.5.3. Filling in a data series with the Fill handle
          4. 2.5.4. Copying a formula with the Fill handle
        6. 2.6. Saving the Spreadsheet Data in a Workbook File
      3. 3. Formatting the Spreadsheet
        1. 3.1. Resizing Columns and Rows
          1. 3.1.1. Making column widths suit the data
          2. 3.1.2. Manipulating the height of certain rows
        2. 3.2. Cell Formatting Techniques
          1. 3.2.1. Formatting cells with the Ribbon's Home tab
          2. 3.2.2. Formatting cells with the Format Cells dialog box
          3. 3.2.3. Using cell styles
          4. 3.2.4. Using conditional formatting
        3. 3.3. Hiding Columns and Rows
      4. 4. Printing Spreadsheet Reports
        1. 4.1. Previewing Pages in the Worksheet and Backstage View
        2. 4.2. Adjusting Page Breaks
        3. 4.3. Adding Headers and Footers
        4. 4.4. Adding Print Titles to a Report
        5. 4.5. Modifying the Print Setting for a Report
        6. 4.6. Printing All or Part of the Workbook
          1. 4.6.1. Printing a range of cells
          2. 4.6.2. Printing the entire workbook
          3. 4.6.3. Printing charts in the spreadsheet
          4. 4.6.4. Printing the spreadsheet formulas
      5. 5. Modifying the Spreadsheet
        1. 5.1. Finding and Identifying the Region That Needs Editing
        2. 5.2. Selecting the Ranges to Edit
        3. 5.3. Editing Data Entries
        4. 5.4. Catching Errors with Text to Speech
        5. 5.5. Deleting and Inserting Data and Cells
        6. 5.6. Moving and Copying Data and Cells
        7. 5.7. Using Notes in the Spreadsheet
        8. 5.8. Using Find and Replace and Spell Checking
        9. 5.9. Group Editing
    7. II. Using Formulas and Functions
      1. 6. Building Formulas
        1. 6.1. Building Formulas
          1. 6.1.1. Building formulas by hand
          2. 6.1.2. Building formulas with built-in functions
          3. 6.1.3. Editing formulas
          4. 6.1.4. Altering the natural order of operations
        2. 6.2. Using External Reference Links
        3. 6.3. Controlling When Formulas Are Recalculated
      2. 7. Copying and Correcting Formulas
        1. 7.1. Copying Formulas with Relative References
        2. 7.2. Copying Formulas with Absolute References
        3. 7.3. Copying Formulas with Mixed References
        4. 7.4. Using Range Names in Formulas
        5. 7.5. Building Array Formulas
        6. 7.6. Tracing and Eliminating Formula Errors
        7. 7.7. Dealing with Circular References
      3. 8. Creating Date and Time Formulas
        1. 8.1. Constructing Date and Time Formulas
        2. 8.2. Working with Simple Date Functions
        3. 8.3. Working with Excel's Fancier Date Functions
        4. 8.4. Working with the Time Functions
      4. 9. Financial Formulas and Functions
        1. 9.1. Working with Financial Functions
        2. 9.2. Using the Basic Investment Functions
        3. 9.3. Figuring the Depreciation of an Asset
      5. 10. Using Math Functions
        1. 10.1. Rounding Off Values
        2. 10.2. Finding Products, Powers, and Square Roots
        3. 10.3. Doing Fancier Sums
          1. 10.3.1. Summing products, squares, and their differences
          2. 10.3.2. Conditional totals
      6. 11. Using Common Statistical Functions
        1. 11.1. Computing Averages
        2. 11.2. Finding the Highest and Lowest Values
        3. 11.3. Counting Cells
        4. 11.4. Using the Statistical Functions in Analysis ToolPak Add-in
      7. 12. Using Lookup Functions
        1. 12.1. Returning Single Values from a Lookup Table
          1. 12.1.1. Performing a horizontal lookup
          2. 12.1.2. Performing a vertical lookup
      8. 13. Using Logical Functions
        1. 13.1. Working with the Logical Functions
        2. 13.2. Constructing Decision-Making Formulas
          1. 13.2.1. Choosing between alternate values
          2. 13.2.2. Selecting between alternate calculations
          3. 13.2.3. Nesting IF functions
        3. 13.3. Constructing Error-Trapping Formulas
      9. 14. Text Formulas and Functions
        1. 14.1. Constructing Text Formulas
        2. 14.2. Using Text Functions
    8. III. Working with Graphics
      1. 15. Charting Spreadsheet Data
        1. 15.1. Understanding Excel Charts
        2. 15.2. Creating Charts
        3. 15.3. Formatting Charts
        4. 15.4. Editing Charts
      2. 16. Adding Graphics to Spreadsheets
        1. 16.1. Understanding Graphic Objects
        2. 16.2. Adding Various Types of Graphic Objects
          1. 16.2.1. Inserting clip art
          2. 16.2.2. Importing graphics files
          3. 16.2.3. Adding graphic shapes and text boxes
          4. 16.2.4. Constructing WordArt
          5. 16.2.5. Constructing SmartArt
    9. IV. Managing and Securing Data
      1. 17. Building and Maintaining Data Lists
        1. 17.1. Creating a Data List
          1. 17.1.1. Adding records to a new data list
          2. 17.1.2. Editing records in the data form
        2. 17.2. Sorting Lists
          1. 17.2.1. Using sorting keys
          2. 17.2.2. Sorting a list on multiple keys
          3. 17.2.3. Sorting the fields (columns) in a data list
        3. 17.3. Subtotaling a List
        4. 17.4. Filtering a List
        5. 17.5. Querying External Database Tables
      2. 18. Protecting the Spreadsheet
        1. 18.1. Password-Protecting the Workbook
        2. 18.2. Protecting the Worksheet
        3. 18.3. Doing Data Entry in a Protected Worksheet
        4. 18.4. Protecting the Entire Workbook
    10. V. Doing Data Analysis
      1. 19. Performing What-If Analysis
        1. 19.1. Using Data Tables
          1. 19.1.1. Creating single-variable data tables
          2. 19.1.2. Creating two-variable data tables
        2. 19.2. Exploring Various Scenarios
        3. 19.3. Performing Goal Seeking
        4. 19.4. Creating Complex Models with Solver
      2. 20. Generating Pivot Tables
        1. 20.1. Working with Pivot Tables
        2. 20.2. Creating Pivot Tables
        3. 20.3. Modifying the Pivot Table
          1. 20.3.1. Modifying the table formatting
          2. 20.3.2. Pivoting the table's fields
          3. 20.3.3. Changing the table summary function and adding calculated fields
        4. 20.4. Creating Pivot Charts
    11. VI. Macros and Visual Basic for Applications
      1. 21. Using Macros
        1. 21.1. Creating Macros
          1. 21.1.1. Using the macro recorder
          2. 21.1.2. Recording macros with relative cell references
        2. 21.2. Assigning Macros to the Quick Access Toolbar
        3. 21.3. Assigning Macros to the Ribbon
      2. 22. Using the Visual Basic Editor
        1. 22.1. Using the Visual Basic Editor
          1. 22.1.1. Editing a recorded macro
          2. 22.1.2. Adding a dialog box that processes user input
        2. 22.2. Creating User-Defined Functions
          1. 22.2.1. Using a custom function in your spreadsheet
          2. 22.2.2. Saving custom functions in add-in files
    12. VII. The Part of Tens
      1. 23. Top Ten Features in Excel 2010
        1. 23.1. The Excel Ribbon and Backstage View
        2. 23.2. Conditional Formatting
        3. 23.3. Pivot Table Filtering with Slicers
        4. 23.4. Formatting and Editing from the Home Tab
        5. 23.5. Charts Directly from the Insert Tab
        6. 23.6. Format As Table
        7. 23.7. The Zoom Slider on the Status Bar
        8. 23.8. Page Layout View
        9. 23.9. Style Galleries
        10. 23.10. Live Preview
      2. 24. Top Ten Tips for Using Excel Like a Pro
        1. 24.1. Generating New Workbooks from Templates
        2. 24.2. Organizing Spreadsheet Data on Different Worksheets
        3. 24.3. Creating Data Series with AutoFill
        4. 24.4. Using Range Names
        5. 24.5. Freezing Column and Row Headings
        6. 24.6. Preventing Data Entry Errors with Data Validation
        7. 24.7. Trapping Error Values in Their Original Formulas
        8. 24.8. Saving Memory by Using Array Formulas
        9. 24.9. Controlling the Display of Data in Tables through Outlines
        10. 24.10. Using View Side by Side to Work with Two Workbooks
      3. A. Comparing Worksheets Side by Side
      4. B. About the CD
        1. B.1. System Requirements
        2. B.2. Using the CD
        3. B.3. What You'll Find on the CD
          1. B.3.1. Workbook Exercise Files
          2. B.3.2. Excel Feature Demos
        4. B.4. Troubleshooting
        5. B.5. Customer Care
      5. C. Table of Exercises

    Product information

    • Title: Excel® 2010 Workbook for Dummies®
    • Author(s): Greg Harvey
    • Release date: September 2010
    • Publisher(s): For Dummies
    • ISBN: 9780470489604