book

Master Visually®: Excel® 2007

by Elaine Marmel

March 2008

Beginner

797 pages

24h 55m

English

Visual

Read now

Unlock full access

How to Use this Master VISUALLY BookWho Needs This BookBook OrganizationChapter OrganizationWhat You Need to Use This BookUsing the MouseThe Conventions in This Book
1.1. What You Can Do in Excel1.2. Start and Close Excel1.3. Understanding the Excel Screen1.4. Learn Excel Terminology1.5. Understanding the Ribbon1.6. Work with the Mini Toolbar and Context Menu1.7. Enter Information1.8. Undo and Redo1.9. Move Around in a Worksheet1.10. Move Around in a Workbook1.11. Manage the Status Bar
2.1. Save a Workbook2.2. Open a Workbook2.3. Convert an Excel 97-2003 Workbook to an Excel 2007 Workbook2.4. Start a New Workbook2.5. Switch between Workbooks2.6. Work with Workspaces2.7. Add Properties to a Workbook2.8. Close a Workbook
3.1. Change Fonts or Font Size3.2. Apply Boldface, Italics, or Underlining3.3. Change Font Color3.4. Apply Font Effects3.5. Assign a Number Format3.6. Assign a Currency Format3.7. Assign an Accounting Format3.8. Assign a Short Date Format3.9. Assign a Long Date Format3.10. Assign a Time Format3.11. Assign a General Format3.12. Assign a Percentage Format3.13. Assign a Fraction Format3.14. Assign a Scientific Format3.15. Assign a Text Format3.16. Assign a Special Format3.17. Assign a Custom Format3.18. Fill Cells with Color3.19. Indent Text within Cells3.20. Align Cell Content Vertically3.21. Align Cell Content Horizontally3.22. Wrap Text within Cells3.23. Shrink Text within Cells3.24. Rotate Text in Cells3.25. Add Borders to Cells3.26. Apply a Style3.27. Create a Style3.28. Copy Formatting3.29. Clear Formatting

4.1. Base a Worksheet on a Template4.2. Apply a Theme4.3. Mix and Match Themes4.4. Select Cells4.5. Move or Copy Information4.6. Find and Replace Information
5.1. Check Spelling5.2. Edit the Dictionary5.3. Translate Text5.4. Using the Thesaurus5.5. Research Online
6.1. Insert and Delete Rows6.2. Insert and Delete Columns6.3. Swap Rows and Columns6.4. Adjust Row Height6.5. Adjust Column Width6.6. Copy Width from One Column to Another6.7. Hide and Unhide Rows or Columns6.8. Merge Cells in Columns or Rows
7.1. Set Worksheet Tab Colors7.2. Edit Multiple Worksheets Simultaneously7.3. Add or Delete a Worksheet7.4. Move or Copy a Worksheet7.5. Rename a Worksheet7.6. Hide and Unhide a Worksheet7.7. Hide and Unhide a Workbook7.8. Arrange Open Worksheets or Workbooks7.9. Switch to Another Workbook7.10. Merge Styles7.11. Outline a Worksheet7.12. Work with Custom Views of Outline Settings7.13. Manually Create an Outline7.14. Remove an Outline
8.1. Switch Views8.2. Zoom In and Zoom Out8.3. Create and Use a Custom View8.4. Hide or Display Gridlines8.5. Hide or Display Row Numbers and Column Letters8.6. Hide or Display the Formula Bar8.7. Open a New Window8.8. Freeze Column and Row Titles8.9. Split a Window
9.1. Set Margins9.2. Add Headers and Footers to a Worksheet9.3. Select an Area to Print9.4. Insert, Adjust, or Remove Page Breaks9.5. Set Page Orientation9.6. Print Row and Column Titles on Each Page9.7. Set Paper Size9.8. Control the Width and Height of Printed Output9.9. Print Gridlines9.10. Print Row Numbers and Column Letters9.11. Preview and Print
10.1. The Basics of Formulas in Excel10.2. Fill a Range with Information10.3. Add Numbers10.4. Multiply Numbers10.5. Edit a Formula10.6. Quickly Calculate Common Values10.7. Absolute and Relative Cell References10.8. Copy a Formula10.9. Change a Formula to a Value10.10. Add Data in One Range to Another10.11. Add Data in One Worksheet to Another10.12. Using Cell Names and Range Names10.13. Edit and Delete Cell or Range Names10.14. Create Range Names from Headings10.15. Apply Names to Existing Formulas10.16. Understanding Arrays10.17. Create an Array Formula10.18. Using the SUM Function in an Array Formula
11.1. Sum Numbers11.2. Calculate a Running Balance11.3. Install and Use the Conditional Sum Wizard11.4. Calculate a Percentage11.5. Calculate an Average11.6. Determine a Maximum Value11.7. Identify a Minimum Value11.8. Round Values11.9. Count the Number of Cells Containing Information11.10. Create a Frequency Distribution11.11. Find the Most Frequently Occurring Value in a Range11.12. Calculate a Subtotal
12.1. Calculate Elapsed Days between Dates12.2. Insert Today's Date in a Cell12.3. Work with Times12.4. View the Serial Number for a Date or Time12.5. Calculate a Serial Date Number12.6. Convert a Text Date to a Date Value12.7. Calculate Part of a Date12.8. Determine the Week of the Year12.9. Calculate a Due Date12.10. Convert a Text Time to a Time Value12.11. Convert a Time Value into Hours, Minutes, or Seconds
13.1. Calculate the Present Value of an Investment13.2. Calculate the Net Present Value of an Investment13.3. Calculate the Future Value of an Investment13.4. Calculate a Loan Payment13.5. Depreciate Assets
14.1. Look up a Single Value in a Column14.2. Look up a Single Value in a Row14.3. Look Up a Value in a Table14.4. Using the Lookup Wizard14.5. Choose a Value from a List
15.1. Determine the Number of Columns in a Selection15.2. Determine the Number of Rows in a Selection15.3. Change Text Case15.4. Join Text15.5. Split Up Text15.6. Change Text to Values15.7. Combine Text and Values15.8. Remove Spaces from Imported Data
16.1. Understanding Error Values16.2. Make a Decision16.3. Test for True or False16.4. Avoid Displaying Errors
17.1. Understanding Database Functions17.2. Sum Records That Meet a Single Criterion17.3. Sum Records That Meet Multiple Criteria17.4. Count Records That Meet a Single Criterion17.5. Count Records That Meet Multiple Criteria17.6. Average Records That Meet a Single Criterion17.7. Average Records That Meet Multiple Criteria17.8. Find the Smallest Record That Meets Criteria17.9. Find the Largest Record That Meets Criteria
18.1. Understanding Excel Errors18.2. Display and Print Formulas in Cells18.3. Identify Cells Containing Formulas18.4. Evaluate Formulas18.5. Check for Formula Errors18.6. Trace Cell Relationships18.7. Control Data Entry
19.1. Understanding Charts19.2. Understanding the Chart Window19.3. Create a Column Chart19.4. Create a Pie Chart19.5. Change the Chart Type19.6. Select a Chart Layout19.7. Change the Chart Style19.8. Move a Chart to a Separate Sheet19.9. Reposition an Embedded Chart19.10. Resize an Embedded Chart19.11. Change the Data Included in the Chart19.12. Switch Rows and Columns on the Chart19.13. Create a Combination Chart19.14. Create a Chart Template
20.1. Add a Chart Title20.2. Format the Chart Title20.3. Display the Horizontal Axis Title20.4. Format the Horizontal Axis Title20.5. Display the Vertical Axis Title20.6. Format the Vertical Axis20.7. Reposition or Hide the Chart Legend20.8. Show Data Labels20.9. Show the Data Table20.10. Format the Plot Area20.11. Format the Chart Walls of a 3-D Chart20.12. Format the Chart Floor of a 3-D Chart20.13. Change the Rotation of a 3-D Chart20.14. Set Axis Options20.15. Change Horizontal and Vertical Gridlines20.16. Add a Trendline20.17. Add Line Markers on a Chart20.18. Add Bars to Charts
21.1. Set Chart Element Shape Styles21.2. Set Fill Colors for Chart Element Shapes21.3. Set the Shape Outline for a Chart Element21.4. Set Shape Effects for Chart Elements21.5. Copy Chart Formatting
22.1. Understanding Graphic Elements22.2. Include a Shape in a Worksheet22.3. Insert a Text Box22.4. Work with SmartArt22.5. Apply a SmartArt Style22.6. Apply a SmartArt Layout22.7. Change the Color of a SmartArt Graphic22.8. Add WordArt to a Worksheet22.9. Insert a Symbol22.10. Add a Picture to a Worksheet22.11. Insert Clip Art in a Worksheet22.12. Move or Resize a Graphic Element22.13. Change the Shape of a Graphic Element22.14. Modify a Graphic Element Border22.15. Add an Effect to a Graphic Element22.16. Change the Brightness of a Graphic Element22.17. Change the Contrast of a Graphic Element22.18. Recolor a Graphic Element22.19. Compress Graphic Elements22.20. Crop a Graphic Element22.21. Position Graphic Elements22.22. Align Graphic Elements22.23. Rotate Graphic Elements
23.1. Create a Table23.2. Change the Size of a Table23.3. Use a Data Entry Form23.4. Create a Drop-Down List for Data Entry23.5. Filter or Sort Table Information23.6. Change the Table Style23.7. Apply Special Formatting23.8. Display or Hide Banding23.9. Hide or Display the Header Row23.10. Work with the Total Row23.11. Filter to Hide Duplicates23.12. Remove Duplicates from a Table23.13. Convert a Table to a Range
24.1. Understanding PivotTables24.2. Create a PivotTable Using Numeric Data24.3. Pivot Elements in a PivotTable24.4. Change PivotTable Display Options24.5. Create a PivotTable with Non-Numeric Data24.6. Filter a PivotTable24.7. Sort Information in a PivotTable24.8. Group Items in a PivotTable24.9. Apply a Style to the PivotTable24.10. Set PivotTable Style Options24.11. Add or Remove Blank Rows24.12. Adjust the PivotTable Layout24.13. Hide or Display Subtotals24.14. Hide or Display Row and Column Grand Totals24.15. Change Values in a PivotTable24.16. Change Data in the PivotTable24.17. Add a Calculated Item to a PivotTable24.18. Cell References and PivotTables24.19. Working with a PivotChart
25.1. Highlight Cells That Are Greater Than a Specified Value25.2. Identify the Top Ten Items in a List25.3. Highlight the Bottom Five Percent of a List25.4. Identify Above-Average List Items25.5. Highlight Values Falling Between Two Numbers25.6. Highlight Duplicate Values25.7. Highlight Cells Containing Specific Text25.8. Highlight Cells Containing a Date25.9. Add Data Bars to Represent Data Values25.10. Apply Color Scales to Data25.11. Use Icon Sets to Highlight Values in a List25.12. Clear Conditional Formats25.13. Manage Conditional Formatting Rules25.14. Create a New Conditional Formatting Rule
26.1. Link Worksheets by Pointing26.2. Paste Links26.3. Update Links26.4. Switch the Link Source26.5. Disable Links26.6. Consolidate Data by Position26.7. Consolidate Data Using Labels
27.1. Create Various Scenarios27.2. Display a Scenario27.3. Switch Scenarios Quickly27.4. Merge Scenarios27.5. Create a Scenario Report27.6. Create a Scenario PivotTable27.7. Create a Data Table to Summarize Loan Possibilities27.8. Create a Data Table to Model Sales Projections27.9. Seek a Goal27.10. Use Solver to Maximize Profit27.11. Create a Solver Report27.12. Save Solver Models27.13. Use Solver to Minimize Costs
28.1. Calculate Anova28.2. Measure Correlation28.3. Measure Covariance28.4. Produce Descriptive Statistics28.5. Exponentially Smooth Data28.6. Perform a Two-Sample "F-Test"28.7. Calculate a Moving Average28.8. Generate a Random Number28.9. Create a Rank and Percentile Table28.10. Perform a Regression Analysis28.11. Generate a Sample28.12. Analyze the Statistical Significance of Small Samples
29.1. Macro Basics29.2. Macros and Security29.3. Record a Macro29.4. Run a Macro29.5. Add a Keyboard Shortcut to a Macro29.6. Save a Workbook Containing a Macro29.7. Add a Macro to the Quick Access Toolbar29.8. Handle Workbooks That Contain Macros
30.1. Minimize the Ribbon30.2. Change the Location of the Quick Access Toolbar30.3. Add a Button to the Quick Access Toolbar30.4. Change Popular Options30.5. Understanding Popular Options30.6. Change Common Calculation Options30.7. Understanding Common Calculation Options30.8. Set Advanced Calculation Options30.9. Understanding Advanced Calculation Options30.10. Work with Error Checking Options30.11. Understanding Error Checking Options30.12. Work with AutoCorrect Options30.13. Control Automatic Formatting30.14. Change Proofing Options30.15. Select Options for Saving Files30.16. Understanding Save Options30.17. Set Editing Options30.18. Understanding Editing Options30.19. Set General Display Options30.20. Understanding General Display Options30.21. Set Display Options for the Current Workbook and Worksheet30.22. Understanding Display Options for the Current Workbook and Worksheet30.23. Set General Options30.24. Understanding General Options30.25. Set Web Options30.26. Enable Excel Add-Ins30.27. Control Security with the Trust Center
31.1. Inspect a Workbook Before Sharing31.2. Assign a Password to a Workbook31.3. Encrypt a Workbook31.4. Protect Workbooks31.5. Protect a Worksheet31.6. Worksheet Actions You Can Protect31.7. Add a Signature Line31.8. Add an Invisible Digital Signature31.9. Add Comments to Cells31.10. Print Comments31.11. Mark a Workbook as Final31.12. Excel and Workbook Sharing31.13. Turn on Change Tracking31.14. How Change Tracking Works31.15. Use the History Sheet to Review Tracked Changes31.16. Combine Reviewers' Comments31.17. Collaborate through E-mail
32.1. Create a Hyperlink in a Workbook32.2. Workbooks and the Internet32.3. Save a Workbook as a Web Page32.4. Import Information from the Web32.5. Import a Text File into Excel32.6. Copy Excel Data into Word32.7. Copy an Excel Chart into Word32.8. Embed an Excel Workbook into Word32.9. Create an Excel Worksheet in Word32.10. Use Excel Data to Create Mailing Labels in Word32.11. Copy Excel Information into PowerPoint32.12. Place an Excel Chart in a PowerPoint Slide32.13. Import Excel Information into Access32.14. Connect Excel to Access Information32.15. Query Data in an External Source
A.1. Excel Functions

Overview

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.