Master Visually®: Excel® 2007

Master Visually®: Excel® 2007

by Elaine Marmel
Released March 2008
Publisher(s): Visual
ISBN: 9780470181706

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

If you prefer instructions that show you how rather than tell you why, then this visual reference is for you. Hundreds of succinctly captioned, step-by-step screen shots reveal how to accomplish more than 375 Excel 2007 tasks, including creating letters with Mail Merge, assigning formats to cells, editing multiple worksheets at once, and summarizing with PivotTables and PivotCharts. While high-resolution screen shots demonstrate each task, succinct explanations walk you through step by step so that you can digest these vital lessons in bite-sized modules.

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. How to Use this Master VISUALLY Book
    2. Who Needs This Book
    3. Book Organization
    4. Chapter Organization
    5. What You Need to Use This Book
    6. Using the Mouse
    7. The Conventions in This Book
  7. 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. Enter Information
      8. 1.8. Undo and Redo
      9. 1.9. Move Around in a Worksheet
      10. 1.10. Move Around in a Workbook
      11. 1.11. Manage the Status Bar
    2. 2. Managing Workbooks
      1. 2.1. Save a Workbook
      2. 2.2. Open a Workbook
      3. 2.3. Convert an Excel 97-2003 Workbook to an Excel 2007 Workbook
      4. 2.4. Start a New Workbook
      5. 2.5. Switch between Workbooks
      6. 2.6. Work with Workspaces
      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 Font Color
      4. 3.4. Apply Font Effects
      5. 3.5. Assign a Number Format
      6. 3.6. Assign a Currency Format
      7. 3.7. Assign an Accounting Format
      8. 3.8. Assign a Short Date Format
      9. 3.9. Assign a Long Date Format
      10. 3.10. Assign a Time Format
      11. 3.11. Assign a General Format
      12. 3.12. Assign a Percentage Format
      13. 3.13. Assign a Fraction Format
      14. 3.14. Assign a Scientific Format
      15. 3.15. Assign a Text Format
      16. 3.16. Assign a Special Format
      17. 3.17. Assign a Custom Format
      18. 3.18. Fill Cells with Color
      19. 3.19. Indent Text within Cells
      20. 3.20. Align Cell Content Vertically
      21. 3.21. Align Cell Content Horizontally
      22. 3.22. Wrap Text within Cells
      23. 3.23. Shrink Text within Cells
      24. 3.24. Rotate Text in Cells
      25. 3.25. Add Borders to Cells
      26. 3.26. Apply a Style
      27. 3.27. Create a Style
      28. 3.28. Copy Formatting
      29. 3.29. Clear Formatting
  8. II. DESIGNING WORKSHEETS
    1. 4. Editing Worksheets
      1. 4.1. Base a Worksheet on a Template
      2. 4.2. Apply a Theme
      3. 4.3. Mix and Match Themes
      4. 4.4. Select Cells
      5. 4.5. Move or Copy Information
      6. 4.6. Find and Replace Information
    2. 5. Proofreading a Worksheet
      1. 5.1. Check Spelling
      2. 5.2. Edit the Dictionary
      3. 5.3. Translate Text
      4. 5.4. Using the Thesaurus
      5. 5.5. Research Online
    3. 6. Adjusting Worksheets
      1. 6.1. Insert and Delete Rows
      2. 6.2. Insert and Delete Columns
      3. 6.3. Swap Rows and Columns
      4. 6.4. Adjust Row Height
      5. 6.5. Adjust Column Width
      6. 6.6. Copy Width from One Column to Another
      7. 6.7. Hide and Unhide Rows or Columns
      8. 6.8. Merge Cells in Columns or Rows
    4. 7. Managing Workbook Structure
      1. 7.1. Set Worksheet Tab Colors
      2. 7.2. Edit Multiple Worksheets Simultaneously
      3. 7.3. Add or Delete a Worksheet
      4. 7.4. Move or Copy a Worksheet
      5. 7.5. Rename a Worksheet
      6. 7.6. Hide and Unhide a Worksheet
      7. 7.7. Hide and Unhide a Workbook
      8. 7.8. Arrange Open Worksheets or Workbooks
      9. 7.9. Switch to Another Workbook
      10. 7.10. Merge Styles
      11. 7.11. Outline a Worksheet
      12. 7.12. Work with Custom Views of Outline Settings
      13. 7.13. Manually Create an Outline
      14. 7.14. Remove an Outline
    5. 8. Working with Views
      1. 8.1. Switch Views
      2. 8.2. Zoom In and Zoom Out
      3. 8.3. Create and Use a Custom View
      4. 8.4. Hide or Display Gridlines
      5. 8.5. Hide or Display Row Numbers and Column Letters
      6. 8.6. Hide or Display the Formula Bar
      7. 8.7. Open a New Window
      8. 8.8. Freeze Column and Row Titles
      9. 8.9. Split a Window
    6. 9. Printing Worksheet Information
      1. 9.1. Set Margins
      2. 9.2. Add Headers and Footers to a Worksheet
      3. 9.3. Select an Area to Print
      4. 9.4. Insert, Adjust, or Remove Page Breaks
      5. 9.5. Set Page Orientation
      6. 9.6. Print Row and Column Titles on Each Page
      7. 9.7. Set Paper Size
      8. 9.8. Control the Width and Height of Printed Output
      9. 9.9. Print Gridlines
      10. 9.10. Print Row Numbers and Column Letters
      11. 9.11. Preview and Print
  9. III. CALCULATING DATA
    1. 10. Performing Basic Math
      1. 10.1. The Basics of Formulas in Excel
      2. 10.2. Fill a Range with Information
      3. 10.3. Add Numbers
      4. 10.4. Multiply Numbers
      5. 10.5. Edit a Formula
      6. 10.6. Quickly Calculate Common Values
      7. 10.7. Absolute and Relative Cell References
      8. 10.8. Copy a Formula
      9. 10.9. Change a Formula to a Value
      10. 10.10. Add Data in One Range to Another
      11. 10.11. Add Data in One Worksheet to Another
      12. 10.12. Using Cell Names and Range Names
      13. 10.13. Edit and Delete Cell or Range Names
      14. 10.14. Create Range Names from Headings
      15. 10.15. Apply Names to Existing Formulas
      16. 10.16. Understanding Arrays
      17. 10.17. Create an Array Formula
      18. 10.18. Using the SUM Function in an Array Formula
    2. 11. Working with Common Formulas
      1. 11.1. Sum Numbers
      2. 11.2. Calculate a Running Balance
      3. 11.3. Install and Use the Conditional Sum Wizard
      4. 11.4. Calculate a Percentage
      5. 11.5. Calculate an Average
      6. 11.6. Determine a Maximum Value
      7. 11.7. Identify a Minimum Value
      8. 11.8. Round Values
      9. 11.9. Count the Number of Cells Containing Information
      10. 11.10. Create a Frequency Distribution
      11. 11.11. Find the Most Frequently Occurring Value in a Range
      12. 11.12. Calculate a Subtotal
    3. 12. Mastering Date and Time Formulas
      1. 12.1. Calculate Elapsed Days between Dates
      2. 12.2. Insert Today's Date in a Cell
      3. 12.3. Work with Times
      4. 12.4. View the Serial Number for a Date or Time
      5. 12.5. Calculate a Serial Date Number
      6. 12.6. Convert a Text Date to a Date Value
      7. 12.7. Calculate Part of a Date
      8. 12.8. Determine the Week of the Year
      9. 12.9. Calculate a Due Date
      10. 12.10. Convert a Text Time to a Time Value
      11. 12.11. Convert a Time Value into Hours, Minutes, or Seconds
    4. 13. Working with Financial Formulas
      1. 13.1. Calculate the Present Value of an Investment
      2. 13.2. Calculate the Net Present Value of an Investment
      3. 13.3. Calculate the Future Value of an Investment
      4. 13.4. Calculate a Loan Payment
      5. 13.5. Depreciate Assets
    5. 14. Working with Lookup Formulas
      1. 14.1. Look up a Single Value in a Column
      2. 14.2. Look up a Single Value in a Row
      3. 14.3. Look Up a Value in a Table
      4. 14.4. Using the Lookup Wizard
      5. 14.5. Choose a Value from a List
    6. 15. Applying Reference, Information, and Text Formulas
      1. 15.1. Determine the Number of Columns in a Selection
      2. 15.2. Determine the Number of Rows in a Selection
      3. 15.3. Change Text Case
      4. 15.4. Join Text
      5. 15.5. Split Up Text
      6. 15.6. Change Text to Values
      7. 15.7. Combine Text and Values
      8. 15.8. Remove Spaces from Imported Data
    7. 16. Working with Logical and Error Trapping Formulas
      1. 16.1. Understanding Error Values
      2. 16.2. Make a Decision
      3. 16.3. Test for True or False
      4. 16.4. Avoid Displaying Errors
    8. 17. Analyzing Tabular Information with Functions
      1. 17.1. Understanding Database Functions
      2. 17.2. Sum Records That Meet a Single Criterion
      3. 17.3. Sum Records That Meet Multiple Criteria
      4. 17.4. Count Records That Meet a Single Criterion
      5. 17.5. Count Records That Meet Multiple Criteria
      6. 17.6. Average Records That Meet a Single Criterion
      7. 17.7. Average Records That Meet Multiple Criteria
      8. 17.8. Find the Smallest Record That Meets Criteria
      9. 17.9. Find the Largest Record That Meets Criteria
    9. 18. Analyzing Formulas and Worksheets
      1. 18.1. Understanding Excel Errors
      2. 18.2. Display and Print Formulas in Cells
      3. 18.3. Identify Cells Containing Formulas
      4. 18.4. Evaluate Formulas
      5. 18.5. Check for Formula Errors
      6. 18.6. Trace Cell Relationships
      7. 18.7. Control Data Entry
  10. IV. MASTERING EXCEL CHARTS
    1. 19. Discovering Chart Basics
      1. 19.1. Understanding Charts
      2. 19.2. Understanding the Chart Window
      3. 19.3. Create a Column Chart
      4. 19.4. Create a Pie Chart
      5. 19.5. Change the Chart Type
      6. 19.6. Select a Chart Layout
      7. 19.7. Change the Chart Style
      8. 19.8. Move a Chart to a Separate Sheet
      9. 19.9. Reposition an Embedded Chart
      10. 19.10. Resize an Embedded Chart
      11. 19.11. Change the Data Included in the Chart
      12. 19.12. Switch Rows and Columns on the Chart
      13. 19.13. Create a Combination Chart
      14. 19.14. Create a Chart Template
    2. 20. Changing Chart Layout Details
      1. 20.1. Add a Chart Title
      2. 20.2. Format the Chart Title
      3. 20.3. Display the Horizontal Axis Title
      4. 20.4. Format the Horizontal Axis Title
      5. 20.5. Display the Vertical Axis Title
      6. 20.6. Format the Vertical Axis
      7. 20.7. Reposition or Hide the Chart Legend
      8. 20.8. Show Data Labels
      9. 20.9. Show the Data Table
      10. 20.10. Format the Plot Area
      11. 20.11. Format the Chart Walls of a 3-D Chart
      12. 20.12. Format the Chart Floor of a 3-D Chart
      13. 20.13. Change the Rotation of a 3-D Chart
      14. 20.14. Set Axis Options
      15. 20.15. Change Horizontal and Vertical Gridlines
      16. 20.16. Add a Trendline
      17. 20.17. Add Line Markers on a Chart
      18. 20.18. Add Bars to Charts
    3. 21. Formatting Charts
      1. 21.1. Set Chart Element Shape Styles
      2. 21.2. Set Fill Colors for Chart Element Shapes
      3. 21.3. Set the Shape Outline for a Chart Element
      4. 21.4. Set Shape Effects for Chart Elements
      5. 21.5. Copy Chart Formatting
    4. 22. Working with Graphic Elements
      1. 22.1. Understanding Graphic Elements
      2. 22.2. Include a Shape in a Worksheet
      3. 22.3. Insert a Text Box
      4. 22.4. Work with SmartArt
      5. 22.5. Apply a SmartArt Style
      6. 22.6. Apply a SmartArt Layout
      7. 22.7. Change the Color of a SmartArt Graphic
      8. 22.8. Add WordArt to a Worksheet
      9. 22.9. Insert a Symbol
      10. 22.10. Add a Picture to a Worksheet
      11. 22.11. Insert Clip Art in a Worksheet
      12. 22.12. Move or Resize a Graphic Element
      13. 22.13. Change the Shape of a Graphic Element
      14. 22.14. Modify a Graphic Element Border
      15. 22.15. Add an Effect to a Graphic Element
      16. 22.16. Change the Brightness of a Graphic Element
      17. 22.17. Change the Contrast of a Graphic Element
      18. 22.18. Recolor a Graphic Element
      19. 22.19. Compress Graphic Elements
      20. 22.20. Crop a Graphic Element
      21. 22.21. Position Graphic Elements
      22. 22.22. Align Graphic Elements
      23. 22.23. Rotate Graphic Elements
  11. V. ANALYZING DATA
    1. 23. Working with Tables
      1. 23.1. Create a Table
      2. 23.2. Change the Size of a Table
      3. 23.3. Use a Data Entry Form
      4. 23.4. Create a Drop-Down List for Data Entry
      5. 23.5. Filter or Sort Table Information
      6. 23.6. Change the Table Style
      7. 23.7. Apply Special Formatting
      8. 23.8. Display or Hide Banding
      9. 23.9. Hide or Display the Header Row
      10. 23.10. Work with the Total Row
      11. 23.11. Filter to Hide Duplicates
      12. 23.12. Remove Duplicates from a Table
      13. 23.13. Convert a Table to a Range
    2. 24. Summarizing Data with PivotTables and PivotCharts
      1. 24.1. Understanding PivotTables
      2. 24.2. Create a PivotTable Using Numeric Data
      3. 24.3. Pivot Elements in a PivotTable
      4. 24.4. Change PivotTable Display Options
      5. 24.5. Create a PivotTable with Non-Numeric Data
      6. 24.6. Filter a PivotTable
      7. 24.7. Sort Information in a PivotTable
      8. 24.8. Group Items in a PivotTable
      9. 24.9. Apply a Style to the PivotTable
      10. 24.10. Set PivotTable Style Options
      11. 24.11. Add or Remove Blank Rows
      12. 24.12. Adjust the PivotTable Layout
      13. 24.13. Hide or Display Subtotals
      14. 24.14. Hide or Display Row and Column Grand Totals
      15. 24.15. Change Values in a PivotTable
      16. 24.16. Change Data in the PivotTable
      17. 24.17. Add a Calculated Item to a PivotTable
      18. 24.18. Cell References and PivotTables
      19. 24.19. Working with a PivotChart
    3. 25. Visually Analyzing Data
      1. 25.1. Highlight Cells That Are Greater Than a Specified Value
      2. 25.2. Identify the Top Ten Items in a List
      3. 25.3. Highlight the Bottom Five Percent of a List
      4. 25.4. Identify Above-Average List Items
      5. 25.5. Highlight Values Falling Between Two Numbers
      6. 25.6. Highlight Duplicate Values
      7. 25.7. Highlight Cells Containing Specific Text
      8. 25.8. Highlight Cells Containing a Date
      9. 25.9. Add Data Bars to Represent Data Values
      10. 25.10. Apply Color Scales to Data
      11. 25.11. Use Icon Sets to Highlight Values in a List
      12. 25.12. Clear Conditional Formats
      13. 25.13. Manage Conditional Formatting Rules
      14. 25.14. Create a New Conditional Formatting Rule
    4. 26. Linking and Consolidating Worksheets
      1. 26.1. Link Worksheets by Pointing
      2. 26.2. Paste Links
      3. 26.3. Update Links
      4. 26.4. Switch the Link Source
      5. 26.5. Disable Links
      6. 26.6. Consolidate Data by Position
      7. 26.7. Consolidate Data Using Labels
    5. 27. Performing What-If Analysis
      1. 27.1. Create Various Scenarios
      2. 27.2. Display a Scenario
      3. 27.3. Switch Scenarios Quickly
      4. 27.4. Merge Scenarios
      5. 27.5. Create a Scenario Report
      6. 27.6. Create a Scenario PivotTable
      7. 27.7. Create a Data Table to Summarize Loan Possibilities
      8. 27.8. Create a Data Table to Model Sales Projections
      9. 27.9. Seek a Goal
      10. 27.10. Use Solver to Maximize Profit
      11. 27.11. Create a Solver Report
      12. 27.12. Save Solver Models
      13. 27.13. Use Solver to Minimize Costs
    6. 28. Performing Advanced Statistical Analysis
      1. 28.1. Calculate Anova
      2. 28.2. Measure Correlation
      3. 28.3. Measure Covariance
      4. 28.4. Produce Descriptive Statistics
      5. 28.5. Exponentially Smooth Data
      6. 28.6. Perform a Two-Sample "F-Test"
      7. 28.7. Calculate a Moving Average
      8. 28.8. Generate a Random Number
      9. 28.9. Create a Rank and Percentile Table
      10. 28.10. Perform a Regression Analysis
      11. 28.11. Generate a Sample
      12. 28.12. Analyze the Statistical Significance of Small Samples
  12. VI. CUSTOMIZING THE EXCEL ENVIRONMENT
    1. 29. Working with Macros
      1. 29.1. Macro Basics
      2. 29.2. Macros and Security
      3. 29.3. Record a Macro
      4. 29.4. Run a Macro
      5. 29.5. Add a Keyboard Shortcut to a Macro
      6. 29.6. Save a Workbook Containing a Macro
      7. 29.7. Add a Macro to the Quick Access Toolbar
      8. 29.8. Handle Workbooks That Contain Macros
    2. 30. Changing Excel Behavior
      1. 30.1. Minimize the Ribbon
      2. 30.2. Change the Location of the Quick Access Toolbar
      3. 30.3. Add a Button to the Quick Access Toolbar
      4. 30.4. Change Popular Options
      5. 30.5. Understanding Popular Options
      6. 30.6. Change Common Calculation Options
      7. 30.7. Understanding Common Calculation Options
      8. 30.8. Set Advanced Calculation Options
      9. 30.9. Understanding Advanced Calculation Options
      10. 30.10. Work with Error Checking Options
      11. 30.11. Understanding Error Checking Options
      12. 30.12. Work with AutoCorrect Options
      13. 30.13. Control Automatic Formatting
      14. 30.14. Change Proofing Options
      15. 30.15. Select Options for Saving Files
      16. 30.16. Understanding Save Options
      17. 30.17. Set Editing Options
      18. 30.18. Understanding Editing Options
      19. 30.19. Set General Display Options
      20. 30.20. Understanding General Display Options
      21. 30.21. Set Display Options for the Current Workbook and Worksheet
      22. 30.22. Understanding Display Options for the Current Workbook and Worksheet
      23. 30.23. Set General Options
      24. 30.24. Understanding General Options
      25. 30.25. Set Web Options
      26. 30.26. Enable Excel Add-Ins
      27. 30.27. Control Security with the Trust Center
  13. VII. USING EXCEL IN COLLABORATION WITH OTHERS
    1. 31. Using Workbooks in a Multi-User Environment
      1. 31.1. Inspect a Workbook Before Sharing
      2. 31.2. Assign a Password to a Workbook
      3. 31.3. Encrypt a Workbook
      4. 31.4. Protect Workbooks
      5. 31.5. Protect a Worksheet
      6. 31.6. Worksheet Actions You Can Protect
      7. 31.7. Add a Signature Line
      8. 31.8. Add an Invisible Digital Signature
      9. 31.9. Add Comments to Cells
      10. 31.10. Print Comments
      11. 31.11. Mark a Workbook as Final
      12. 31.12. Excel and Workbook Sharing
      13. 31.13. Turn on Change Tracking
      14. 31.14. How Change Tracking Works
      15. 31.15. Use the History Sheet to Review Tracked Changes
      16. 31.16. Combine Reviewers' Comments
      17. 31.17. Collaborate through E-mail
    2. 32. Sharing Excel Data with Other Programs
      1. 32.1. Create a Hyperlink in a Workbook
      2. 32.2. Workbooks and the Internet
      3. 32.3. Save a Workbook as a Web Page
      4. 32.4. Import Information from the Web
      5. 32.5. Import a Text File into Excel
      6. 32.6. Copy Excel Data into Word
      7. 32.7. Copy an Excel Chart into Word
      8. 32.8. Embed an Excel Workbook into Word
      9. 32.9. Create an Excel Worksheet in Word
      10. 32.10. Use Excel Data to Create Mailing Labels in Word
      11. 32.11. Copy Excel Information into PowerPoint
      12. 32.12. Place an Excel Chart in a PowerPoint Slide
      13. 32.13. Import Excel Information into Access
      14. 32.14. Connect Excel to Access Information
      15. 32.15. Query Data in an External Source
    3. A. Appendix
      1. A.1. Excel Functions

Product information

  • Title: Master Visually®: Excel® 2007
  • Author(s): Elaine Marmel
  • Release date: March 2008
  • Publisher(s): Visual
  • ISBN: 9780470181706