You are previewing Microsoft® Office Excel® 2007 VISUAL™ Quick Tips.
O'Reilly logo
Microsoft® Office Excel® 2007 VISUAL™ Quick Tips

Book Description

This book is fully updated for Excel 2007! Would you like to make Excel formulas easier to create and read, save time by customizing and automating your Excel worksheets, and learn how to present data with dramatic visual appeal? Then this "Visual Quick Tips" book is for you. This book will increase your productivity by providing you with shortcuts, tricks, and tips to help you work smarter and faster. It includes: straightforward task descriptions; succinct explanations; full-color screen shots; and numbered steps. You can also learn how to: add comments and styles to worksheets; sort and filter complex data; and import, export, and hyperlink content.

Table of Contents

  1. Copyright
  2. Praise for Visual Books
  3. Credits
  4. About the Author
  5. 1. Boosting Efficiency
    1. 1.1. Quick Tips
    2. 1.2. Name Cells and Ranges
      1. 1.2.1. NAME A RANGE OF CELLS
      2. 1.2.2. CREATE NAMED RANGES FROM A SELECTION
    3. 1.3. Validate with a Validation List
      1. 1.3.1. CREATE A VALIDATION LIST
      2. 1.3.2. PASTE YOUR VALIDATION LIST
    4. 1.4. Validate with Data Entry Rules
    5. 1.5. Extend a Series with AutoFill
    6. 1.6. Insert Symbols and Special Characters
      1. 1.6.1. ADD A SYMBOL
      2. 1.6.2. ADD A SPECIAL CHARACTER
    7. 1.7. Hide Rows by Grouping and Outlining
    8. 1.8. Add Comments to a Worksheet
      1. 1.8.1. ADD A COMMENT
      2. 1.8.2. DISPLAY ALL COMMENTS
    9. 1.9. Let Excel Read Back Data
      1. 1.9.1. READ CELLS
      2. 1.9.2. SPEAK CELLS ON ENTER
  6. 2. Working with Formulas and Functions
    1. 2.1. Quick Tips
    2. 2.2. Calculate with the Function Wizard
    3. 2.3. Define a Constant
      1. 2.3.1. DEFINE A CONSTANT
      2. 2.3.2. DISPLAY A CONSTANT
    4. 2.4. Create Formulas That Include Names
      1. 2.4.1. USE A RANGE NAME IN A FORMULA
      2. 2.4.2. USE A CONSTANT IN A FORMULA
    5. 2.5. Figure Out Loan Terms
    6. 2.6. Determine the Internal Rate of Return
      1. 2.6.1. CALCULATE INTERNAL RATE OF RETURN
    7. 2.7. Determine the Nth Largest Value
    8. 2.8. Create a Conditional Formula
    9. 2.9. Calculate a Conditional Sum
    10. 2.10. Add a Calculator
      1. 2.10.1. ADD THE CALCULATOR
      2. 2.10.2. USE THE SCIENTIFIC MODE
    11. 2.11. Find Products and Square Roots
      1. 2.11.1. CALCULATE A PRODUCT
      2. 2.11.2. CALCULATE A SQUARE ROOT
    12. 2.12. Perform Time Calculations
      1. 2.12.1. FIND THE DIFFERENCE BETWEEN TWO TIMES
      2. 2.12.2. CONVERT a SERIAL VALUE TO A TIME
    13. 2.13. Perform Date Calculations
      1. 2.13.1. FIND NUMBER OF DAYS BETWEEN TWO DATES
  7. 3. Copying, Formatting, and More
    1. 3.1. Quick Tips
    2. 3.2. Check Formulas for Errors
    3. 3.3. Change Text to Numbers and Then Calculate
    4. 3.4. Convert a Row to a Column
    5. 3.5. Copy with the Office Clipboard
    6. 3.6. Specify How to Paste with Paste Special
    7. 3.7. Create Your Own Style
      1. 3.7.1. GROUP FORMATS AS A STYLE
      2. 3.7.2. APPLY A FORMAT STYLE
    8. 3.8. Copy Styles to Another Workbook
    9. 3.9. Conditionally Format a Worksheet
      1. 3.9.1. CONDITIONAL FORMAT
      2. 3.9.2. DATA BARS
      3. 3.9.3. MODIFY RULES
    10. 3.10. Track Changes While Editing
    11. 3.11. Consolidate Worksheets
  8. 4. Manipulating Records
    1. 4.1. Quick Tips
    2. 4.2. Enter Data with a Form
    3. 4.3. Filter Duplicate Records
    4. 4.4. Perform Simple Sorts and Filters
      1. 4.4.1. SORT A LIST
      2. 4.4.2. FILTER A LIST
    5. 4.5. Perform Complex Sorts
    6. 4.6. Perform Complex Filters
    7. 4.7. Filter by Multiple Criteria in the Same Column
    8. 4.8. Subtotal Sorted Data
    9. 4.9. Chart Filtered Data Easily
    10. 4.10. Count Filtered Records
    11. 4.11. Look Up Information in a Worksheet
    12. 4.12. Define Data as a Table
    13. 4.13. Modify a Table Style
  9. 5. Explore Patterns in Data
    1. 5.1. Quick Tips
    2. 5.2. Create a PivotTable
    3. 5.3. Modify PivotTable Data and Layout
      1. 5.3.1. REFRESH DATA
      2. 5.3.2. CHANGE LAYOUT
    4. 5.4. Compute Sub- and Grand Totals
    5. 5.5. Create a Calculated Field
    6. 5.6. Hide Columns or Rows in a PivotTable
    7. 5.7. Sort a PivotTable
      1. 5.7.1. SORT FIELD LABELS
      2. 5.7.2. SORT DATA FIELDS
    8. 5.8. Create a PivotChart
    9. 5.9. Describe Data with Statistics
    10. 5.10. Find the Correlation Between Variables
    11. 5.11. Explore Outcomes with What-If Analysis
    12. 5.12. Optimize a Result with Goal Seek
  10. 6. Creating Charts
    1. 6.1. Quick Tips
    2. 6.2. Create a Chart That Has Visual Appeal
    3. 6.3. Add Chart Details
      1. 6.3.1. CHANGE CHART LOCATION
      2. 6.3.2. CHANGE ROTATION AND PERSPECTIVE
      3. 6.3.3. CHANGE THE WALL AND FLOOR
      4. 6.3.4. ADJUST THE AXIS
    4. 6.4. Change the Chart Type
    5. 6.5. Add a Trendline to a Chart
    6. 6.6. Add and Remove Chart Data
      1. 6.6.1. CHANGE THE DATA AREA
      2. 6.6.2. ADD A LEGEND ITEM
    7. 6.7. Add Error Bars
    8. 6.8. Create a Histogram
  11. 7. Presenting Worksheets
    1. 7.1. Quick Tips
    2. 7.2. Format Quickly with Format Painter
    3. 7.3. Insert Shapes into a Worksheet
    4. 7.4. Insert Text Boxes into a Worksheet
    5. 7.5. Insert Photographs into a Worksheet
    6. 7.6. Arrange the Graphics in a Worksheet
      1. 7.6.1. ROTATE AND ALIGN GRAPHICS
    7. 7.7. Insert a Background Image
    8. 7.8. Take a Picture of a Worksheet
  12. 8. Protecting, Saving, and Printing
    1. 8.1. Quick Tips
    2. 8.2. Protect a Worksheet
    3. 8.3. Save a Workbook as a Template
    4. 8.4. Choose a Format when Saving a Workbook
    5. 8.5. Print Multiple Areas of a Worksheet
    6. 8.6. Print Multiple Worksheets from a Workbook
  13. 9. Extending Excel
    1. 9.1. Quick Tips
    2. 9.2. Paste Link into Word or PowerPoint
    3. 9.3. Embed a Worksheet
    4. 9.4. Hyperlink a Worksheet
    5. 9.5. Query a Website
    6. 9.6. Import a Text File
    7. 9.7. Import an Access Database
    8. 9.8. Query an Access Database
    9. 9.9. Reuse a Saved Query
    10. 9.10. Export a Worksheet to Access
  14. 10. Customizing Excel
    1. 10.1. Quick Tips
    2. 10.2. Install Add-Ins
    3. 10.3. Customize the Quick Access Toolbar
    4. 10.4. Work with Multiple Windows
    5. 10.5. Save Time by Creating a Custom View
    6. 10.6. Create a Custom Number Format
    7. 10.7. Automate a Worksheet with Macros
    8. 10.8. Create an Icon to Run a Macro