Master VISUALLY® Excel® 2010

Master VISUALLY® Excel® 2010

Released July 2010
Publisher(s): Visual
ISBN: 9780470577691

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

The complete visual reference on Excel basics

Aimed at visual learners who are seeking an all-in-one reference that provides in-depth coveage of Excel from a visual viewpoint, this resource delves into all the newest features of Excel 2010. You'll explore Excel with helpful step-by-step instructions that show you, rather than tell you, how to navigate Excel, work with PivotTables and PivotCharts, use macros to streamline work, and collaborate with other users in one document.

  • This two-color guide features screen shots with specific, numbered instructions so you can learn the actions you need to perform in order to execute a wide range of Excel 2010 tasks.

  • Features beginning, intermediate and advanced visual coverage of Excel 2010

  • Shows you how to enter information into a worksheet, use formulas and functions, find and control formulaic errors, and much more

  • Presents more than 1,000 screen shots that demonstrate step-by-step instructions of numerous Excel 2010 tasks

    • You're encouraged to move at your own pace as you acquire confidence and proficiency with the newest version of Excel.

    Table of contents

    1. Copyright
    2. Credits
    3. About the Author
    4. Author's Acknowledgments
    5. I. Excel Basics
      1. 1. Getting Started with Excel
        1. 1.1. What You Can Do in Excel
        2. 1.2. Start and Close Excel
        3. 1.3. Understanding the Excel Screen
        4. 1.4. Learn Excel Terminology
        5. 1.5. Understanding the Ribbon
        6. 1.6. Work with the Mini Toolbar and Context Menu
        7. 1.7. Work with Backstage View
        8. 1.8. Enter Information
        9. 1.9. Undo and Redo
        10. 1.10. Move Around in a Worksheet
      2. 2. Managing Workbooks
        1. 2.1. Save a Workbook
        2. 2.2. Reopen an Unsaved Workbook
        3. 2.3. Open a Workbook
        4. 2.4. Convert an Excel 97-2003 Workbook to Excel 2010
        5. 2.5. Start a New Workbook
        6. 2.6. Switch between Workbooks
        7. 2.7. Add Properties to a Workbook
        8. 2.8. Close a Workbook
      3. 3. Formatting Cells
        1. 3.1. Change Fonts or Font Size
        2. 3.2. Apply Boldface, Italics, or Underlining
        3. 3.3. Change the Font Color
        4. 3.4. Apply Font Effects
        5. 3.5. Understanding Number Formats
        6. 3.6. Assign a Format to a Value
        7. 3.7. Fill Cells with Color
        8. 3.8. Indent Text within Cells
        9. 3.9. Align Cell Content Vertically
        10. 3.10. Align Cell Content Horizontally
        11. 3.11. Wrap Text within Cells
        12. 3.12. Shrink Text within Cells
        13. 3.13. Rotate Text in Cells
        14. 3.14. Add Borders to Cells
        15. 3.15. Apply a Style
        16. 3.16. Create a Style
        17. 3.17. Copy Formatting
        18. 3.18. Clear Formatting
    6. II. Designing Worksheets
      1. 4. Editing and Proofreading Worksheets
        1. 4.1. Work with Themes
        2. 4.2. Select Cells
        3. 4.3. Move or Copy Information
        4. 4.4. Use Paste Options to Control Formatting
        5. 4.5. Find and Replace Information
        6. 4.6. Check Spelling
        7. 4.7. Edit the Dictionary
        8. 4.8. Translate Text
        9. 4.9. Using the Thesaurus
      2. 5. Adjusting Worksheets
        1. 5.1. Insert and Delete Rows
        2. 5.2. Insert and Delete Columns
        3. 5.3. Swap Rows and Columns
        4. 5.4. Adjust Row Height
        5. 5.5. Adjust Column Width
        6. 5.6. Copy Width from One Column to Another
        7. 5.7. Hide and Unhide Rows or Columns
        8. 5.8. Merge Cells in Columns or Rows
      3. 6. Managing the Workbook Structure
        1. 6.1. Set Worksheet Tab Colors
        2. 6.2. Edit Multiple Worksheets Simultaneously
        3. 6.3. Add or Delete a Worksheet
        4. 6.4. Move or Copy a Worksheet
        5. 6.5. Rename a Worksheet
        6. 6.6. Hide and Unhide a Worksheet
        7. 6.7. Hide and Unhide a Workbook
      4. 7. Working with Views
        1. 7.1. Switch Views
        2. 7.2. Zoom In and Zoom Out
        3. 7.3. Create and Use a Custom View
        4. 7.4. Hide or Display Gridlines
        5. 7.5. Hide or Display Row Numbers and Column Letters
        6. 7.6. Hide or Display the Formula Bar
        7. 7.7. Open a New Window
        8. 7.8. Freeze Column and Row Titles
        9. 7.9. Split a Window
      5. 8. Printing Worksheet Information
        1. 8.1. Set Margins
        2. 8.2. Add Headers and Footers to a Worksheet
        3. 8.3. Select an Area to Print
        4. 8.4. Insert, Adjust, or Remove Page Breaks
        5. 8.5. Set Page Orientation
        6. 8.6. Print Row and Column Titles on Each Page
        7. 8.7. Set Paper Size
        8. 8.8. Control the Width and Height of Printed Output
        9. 8.9. Print Gridlines
        10. 8.10. Print Row Numbers and Column Letters
        11. 8.11. Preview and Print
    7. III. Calculating Data
      1. 9. Performing Basic Math
        1. 9.1. The Basics of Formulas in Excel
        2. 9.2. Fill a Range with Information
        3. 9.3. Add Numbers
        4. 9.4. Multiply Numbers
        5. 9.5. Edit a Formula
        6. 9.6. Quickly Compute Common Calculations
        7. 9.7. Absolute and Relative Cell References
        8. 9.8. Copy a Formula
        9. 9.9. Change a Formula to a Value
        10. 9.10. Add Data in One Range to Another
        11. 9.11. Add Data in One Worksheet to Another
        12. 9.12. Using Cell Names and Range Names
        13. 9.13. Edit and Delete Cell or Range Names
        14. 9.14. Create Range Names from Headings
        15. 9.15. Apply Names to Existing Formulas
        16. 9.16. Understanding Arrays
        17. 9.17. Create an Array Formula
        18. 9.18. Using the SUM Function in an Array Formula
      2. 10. Working with Common Formulas
        1. 10.1. Sum Numbers
        2. 10.2. Calculate a Running Balance
        3. 10.3. Calculate a Percentage
        4. 10.4. Calculate an Average
        5. 10.5. Determine a Maximum Value
        6. 10.6. Identify a Minimum Value
        7. 10.7. Calculate a Conditional Sum
        8. 10.8. Round Values
        9. 10.9. Count the Number of Cells Containing Information
        10. 10.10. Create a Frequency Distribution
        11. 10.11. Find the Most Frequently Occurring Value in a Range
        12. 10.12. Calculate a Subtotal
      3. 11. Mastering Date and Time Formulas
        1. 11.1. Calculate Elapsed Days between Dates
        2. 11.2. Insert Today's Date in a Cell
        3. 11.3. Work with Times
        4. 11.4. View the Serial Number for a Date or Time
        5. 11.5. Calculate a Serial Date Number
        6. 11.6. Convert a Text Date to a Date Value
        7. 11.7. Calculate Part of a Date
        8. 11.8. Determine the Week of the Year
        9. 11.9. Calculate a Due Date
        10. 11.10. Convert a Text Time to a Time Value
        11. 11.11. Convert a Time Value into Hours, Minutes, or Seconds
      4. 12. Working with Financial Formulas
        1. 12.1. Calculate the Present Value of an Investment
        2. 12.2. Calculate the Net Present Value of an Investment
        3. 12.3. Calculate the Future Value of an Investment
        4. 12.4. Calculate a Loan Payment
        5. 12.5. Depreciate Assets
      5. 13. Working with Commonly Used Lookup Formulas
        1. 13.1. Look Up a Single Value in a Column
        2. 13.2. Look Up a Single Value in a Row
        3. 13.3. Look Up a Value in a Table
        4. 13.4. Choose a Value from a List
      6. 14. Applying Reference, Information, and Text Formulas
        1. 14.1. Determine the Number of Columns in a Selection
        2. 14.2. Determine the Number of Rows in a Selection
        3. 14.3. Change Text Case
        4. 14.4. Join Text
        5. 14.5. Split Text
        6. 14.6. Change Text to Values
        7. 14.7. Combine Text and Values
        8. 14.8. Remove Spaces from Imported Data
      7. 15. Working with Logical and Error Trapping Formulas
        1. 15.1. Understanding Error Values
        2. 15.2. Make a Decision
        3. 15.3. Test for True or False
        4. 15.4. Avoid Displaying Errors
      8. 16. Analyzing Tabular Information with Functions
        1. 16.1. Understanding Database Functions
        2. 16.2. Sum Records That Meet a Single Criterion
        3. 16.3. Sum Records That Meet Multiple Criteria
        4. 16.4. Count Records That Meet a Single Criterion
        5. 16.5. Count Records That Meet Multiple Criteria
        6. 16.6. Average Records That Meet a Single Criterion
        7. 16.7. Average Records That Meet Multiple Criteria
        8. 16.8. Find the Smallest Record That Meets Criteria
        9. 16.9. Find the Largest Record That Meets Criteria
      9. 17. 17 Analyzing Formulas and Functions
        1. 17.1. Understanding Excel Errors
        2. 17.2. Display and Print Formulas in Cells
        3. 17.3. Identify Cells Containing Formulas
        4. 17.4. Evaluate Formulas
        5. 17.5. Check for Formula Errors
        6. 17.6. Trace Cell Relationships
        7. 17.7. Control Data Entry
    8. IV. Mastering Excel Charts
      1. 18. Discovering Chart Basics
        1. 18.1. Create a Sparkline Chart
        2. 18.2. Modify a Sparkline Chart
        3. 18.3. Understanding Charts
        4. 18.4. Understanding the Chart Window
        5. 18.5. Create a Column Chart
        6. 18.6. Create a Pie Chart
        7. 18.7. Change the Chart Type
        8. 18.8. Select a Chart Layout
        9. 18.9. Change the Chart Style
        10. 18.10. Move a Chart to a Separate Sheet
        11. 18.11. Reposition an Embedded Chart
        12. 18.12. Resize an Embedded Chart
        13. 18.13. Change the Data Included in the Chart
        14. 18.14. Switch Rows and Columns on the Chart
        15. 18.15. Create a Combination Chart
      2. 19. Changing Chart Layout Details
        1. 19.1. Add a Chart Title
        2. 19.2. Format the Chart Title
        3. 19.3. Display the Horizontal Axis Title
        4. 19.4. Format the Horizontal Axis Title
        5. 19.5. Display the Vertical Axis Title
        6. 19.6. Format the Vertical Axis Title
        7. 19.7. Reposition or Hide the Chart Legend
        8. 19.8. Show Data Labels
        9. 19.9. Show the Data Table
        10. 19.10. Format the Plot Area
        11. 19.11. Format the Chart Walls of a 3-D Chart
        12. 19.12. Format the Chart Floor of a 3-D Chart
        13. 19.13. Change the Rotation of a 3-D Chart
        14. 19.14. Set Axis Options
        15. 19.15. Change Horizontal and Vertical Gridlines
        16. 19.16. Add a Trendline
        17. 19.17. Add Line Markers on a Chart
        18. 19.18. Set Chart Element Shape Styles
        19. 19.19. Set Fill Colors for Chart Element Shapes
        20. 19.20. Set the Shape Outline for a Chart Element
        21. 19.21. Set Shape Effects for Chart Elements
    9. V. Analyzing Data
      1. 20. Working with Graphic Elements
        1. 20.1. Understanding Graphic Elements
        2. 20.2. Capture a Screenshot
        3. 20.3. Include a Shape in a Worksheet
        4. 20.4. Add WordArt to a Worksheet
        5. 20.5. Insert a Text Box
        6. 20.6. Insert a Symbol
        7. 20.7. Move or Resize a Graphic Element
        8. 20.8. Change the Shape of a Graphic Element
        9. 20.9. Modify a Graphic Element Border
        10. 20.10. Add an Artistic Effect to a Graphic Element
        11. 20.11. Crop a Graphic Element
        12. 20.12. Position Graphic Elements
        13. 20.13. Align Graphic Elements
        14. 20.14. Rotate Graphic Elements
      2. 21. Working with Tables
        1. 21.1. Create a Table
        2. 21.2. Change the Size of a Table
        3. 21.3. Use a Data Entry Form
        4. 21.4. Create a Drop-Down List for Data Entry
        5. 21.5. Filter or Sort Table Information
        6. 21.6. Change the Table Style
        7. 21.7. Apply Special Formatting
        8. 21.8. Display or Hide Banding
        9. 21.9. Hide or Display the Header Row
        10. 21.10. Work with the Total Row
        11. 21.11. Filter to Hide Duplicates
        12. 21.12. Remove Duplicates from a Table
        13. 21.13. Convert a Table to a Range
      3. 22. Summarizing Data with PivotTables and PivotCharts
        1. 22.1. Understanding PivotTables
        2. 22.2. Create a PivotTable by Using Numeric Data
        3. 22.3. Pivot Elements in a PivotTable
        4. 22.4. Change PivotTable Display Options
        5. 22.5. Create a PivotTable with Nonnumeric Data
        6. 22.6. Filter a PivotTable
        7. 22.7. Sort Information in a PivotTable
        8. 22.8. Group Items in a PivotTable
        9. 22.9. Insert a PivotTable Slicer
        10. 22.10. Apply a Style to the PivotTable
        11. 22.11. Add or Remove Blank Rows
        12. 22.12. Adjust the PivotTable Layout
        13. 22.13. Hide or Display Subtotals
        14. 22.14. Hide or Display Row and Column Grand Totals
        15. 22.15. Change Values in a PivotTable
        16. 22.16. Change Data in the PivotTable
        17. 22.17. Add a Calculated Item to a PivotTable
        18. 22.18. Cell References and PivotTables
        19. 22.19. Working with a PivotChart
      4. 23. Visually Analyzing Data
        1. 23.1. Highlight Cells That Are Greater Than a Specified Value
        2. 23.2. Identify the Top Ten Items in a List
        3. 23.3. Highlight the Bottom 5% of a List
        4. 23.4. Identify Above-Average List Items
        5. 23.5. Highlight Values Falling between Two Numbers
        6. 23.6. Highlight Duplicate Values
        7. 23.7. Highlight Cells Containing Specific Text
        8. 23.8. Highlight Cells Containing a Date
        9. 23.9. Add Data Bars to Represent Data Values
        10. 23.10. Apply Color Scales to Data
        11. 23.11. Use Icon Sets to Highlight Values in a List
        12. 23.12. Clear Conditional Formats
        13. 23.13. Manage Conditional Formatting Rules
        14. 23.14. Create a New Conditional Formatting Rule
      5. 24. Linking and Consolidating Worksheets
        1. 24.1. Paste Links
        2. 24.2. Update Links
        3. 24.3. Switch the Link Source
        4. 24.4. Disable Links
        5. 24.5. Consolidate Data by Position
        6. 24.6. Consolidate Data by Using Labels
      6. 25. Performing What-If Analysis
        1. 25.1. Create Various Scenarios
        2. 25.2. Display a Scenario
        3. 25.3. Switch Scenarios Quickly
        4. 25.4. Merge Scenarios
        5. 25.5. Create a Scenario Report
        6. 25.6. Create a Scenario PivotTable
        7. 25.7. Create a Data Table to Summarize Loan Possibilities
        8. 25.8. Create a Data Table to Model Sales Projections
        9. 25.9. Seek a Goal
      7. 26. Performing Advanced Statistical Analyses
        1. 26.1. Calculate Anova
        2. 26.2. Measure Correlation
        3. 26.3. Measure Covariance
        4. 26.4. Produce Descriptive Statistics
        5. 26.5. Exponentially Smooth Data
        6. 26.6. Perform a Two-Sample F-Test
        7. 26.7. Calculate a Moving Average
        8. 26.8. Generate a Random Number
        9. 26.9. Create a Rank and Percentile Table
        10. 26.10. Perform a Regression Analysis
        11. 26.11. Generate a Sample
        12. 26.12. Analyze the Statistical Significance of Small Samples
    10. VI. Customizing the Excel Environment
      1. 27. Working with Macros
        1. 27.1. Macro Basics
        2. 27.2. Macros and Security
        3. 27.3. Record a Macro
        4. 27.4. Run a Macro
        5. 27.5. Add a Keyboard Shortcut to a Macro
        6. 27.6. Save a Workbook Containing a Macro
        7. 27.7. Add a Macro to the Quick Access Toolbar
        8. 27.8. Handle Workbooks That Contain Macros
      2. 28. Customizing the Excel Environment
        1. 28.1. Manage the Status Bar
        2. 28.2. Change the Location of the Quick Access Toolbar
        3. 28.3. Add a Button to the Quick Access Toolbar
        4. 28.4. Minimize the Ribbon
        5. 28.5. Add a Predefined Group to a Ribbon Tab
        6. 28.6. Create Your Own Ribbon Group
        7. 28.7. Create Your Own Ribbon Tab
        8. 28.8. Understanding General Options
        9. 28.9. Change General Options
        10. 28.10. Understanding Common Calculation Options
        11. 28.11. Change Common Calculation Options
        12. 28.12. Understanding Advanced Calculation Options
        13. 28.13. Set Advanced Calculation Options
        14. 28.14. Understanding Error-Checking Options
        15. 28.15. Work with Error-Checking Options
        16. 28.16. Work with AutoCorrect Options
        17. 28.17. Control Automatic Formatting
        18. 28.18. Change Proofing Options
        19. 28.19. Understanding Save Options
        20. 28.20. Select Options for Saving Files
        21. 28.21. Understanding Editing Options
        22. 28.22. Set Editing Options
        23. 28.23. Understanding Program Display Options
        24. 28.24. Set Program Display Options
        25. 28.25. Understanding Display Options for the Workbook/Worksheet
        26. 28.26. Set Display Options for the Workbook and Worksheet
        27. 28.27. Understanding Advanced General Options
        28. 28.28. Set Advanced General Options
        29. 28.29. Set Web Options
        30. 28.30. Enable Excel Add-Ins
        31. 28.31. Control Security with the Trust Center
    11. VIII. Using Excel in Collaboration with Others
      1. 29. Using Workbooks in a Multiuser Environment
        1. 29.1. Inspect a Workbook Before Sharing
        2. 29.2. Assign a Password to a Workbook
        3. 29.3. Encrypt a Workbook
        4. 29.4. Protect Workbooks
        5. 29.5. Protect a Worksheet
        6. 29.6. Worksheet Actions You Can Protect
        7. 29.7. Add a Signature Line
        8. 29.8. Add an Invisible Digital Signature
        9. 29.9. Add Comments to Cells
        10. 29.10. Print Comments
        11. 29.11. Mark a Workbook as Final
        12. 29.12. Excel and Workbook Sharing
        13. 29.13. Turn on Change Tracking
        14. 29.14. How Change Tracking Works
        15. 29.15. Use the History Sheet to Review Tracked Changes
        16. 29.16. Combine Reviewers' Comments
        17. 29.17. Collaborate through E-mail
      2. 30. Sharing Excel Data with Other Programs
        1. 30.1. Create a Hyperlink in a Workbook
        2. 30.2. Workbooks and the Internet
        3. 30.3. Save a Workbook as a Web Page
        4. 30.4. Import Information from the Web
        5. 30.5. Import a Text File into Excel
        6. 30.6. Copy Excel Data into Word
        7. 30.7. Embed an Excel Workbook into Word
        8. 30.8. Create an Excel Worksheet in Word
        9. 30.9. Use Excel Data to Create Mailing Labels in Word
        10. 30.10. Copy Excel Information into PowerPoint
        11. 30.11. Import Excel Information into Access
        12. 30.12. Connect Excel to Access Information
        13. 30.13. Query Data in an External Source

    Product information

    • Title: Master VISUALLY® Excel® 2010
    • Author(s):
    • Release date: July 2010
    • Publisher(s): Visual
    • ISBN: 9780470577691