You are previewing Microsoft® Excel® 2010 Plain & Simple.
O'Reilly logo
Microsoft® Excel® 2010 Plain & Simple

Book Description

Get the guide that makes learning Microsoft Excel 2010 plain and simple! This full color, no-nonsense book shows you the quickest ways to solve a problem or learn a skill, using easy-to-follow steps and concise, straightforward language. You'll analyze, manage, and share information in more ways than ever before.

Here's WHAT you'll learn:

  • Manage your financial data and personal expenses

  • Get started quickly with prebuilt templates

  • Create formulas and functions to do the hard work

  • Sort, filter, update, and copy your data

  • Use charts and graphics to bring data to life

  • Collaborate with colleagues by sharing spreadsheets online

  • Here's HOW you'll learn it:

  • Jump in whenever you need answers

  • Easy-to-follow STEPS and SCREENSHOTS show exactly what to do

  • Handy TIPS teach new techniques and shortcuts

  • Quick TRY THIS! exercises help apply what you learn right away

  • Table of Contents

    1. Microsoft® Excel® 2010 Plain & Simple
    2. Dedication
    3. Acknowledgments
    4. 1. Introduction: About This Book
      1. No Computerese!
      2. Useful Tasks…
      3. …And the Easiest Way to Do Them
      4. A Quick Overview
      5. A Few Assumptions
      6. A Final Word (or Two)
    5. 2. What's New and Improved in Excel 2010
      1. Managing Excel Files and Settings in Backstage View
      2. Previewing Data Using Paste Preview
      3. Customizing the Excel 2010 User Interface
      4. Summarizing Data Using More Accurate Functions
      5. Summarizing Data Using Sparklines
      6. Visualizing Data Using Improved Conditional Formats
      7. Creating and Displaying Math Equations
      8. Editing Pictures Within Excel 2010
    6. 3. Getting Started with Excel 2010
      1. Surveying the Excel Program Window
        1. Working with the User Interface
        2. Working with Galleries
      2. Starting Excel
        1. Start Excel with a Blank Workbook
        2. Open an Existing Workbook
      3. Finding and Opening Existing Workbooks
        1. Open a Workbook
        2. Open a Recently Used Workbook
        3. Search for a Workbook by Name and Open It
      4. Using File Properties
        1. Set File Properties
        2. Define Custom Properties
      5. Creating a New Workbook
        1. Create a New Workbook
      6. Working with Multiple Workbooks
        1. Switch Between Open Workbooks
        2. Show More Than One Workbook
      7. Sizing and Viewing Windows
        1. Resize a Window
      8. Zooming In or Out on a Worksheet
        1. Zoom In or Out
        2. Zoom In or Out to a Custom Zoom Level
      9. Viewing a Worksheet in Full-Screen Mode
        1. Turn Full-Screen Mode On
        2. Turn Full-Screen Mode Off
      10. Saving and Closing an Excel Workbook
        1. Save a Workbook
        2. Save a Workbook with a New Name
        3. Change the Default File Folder
        4. Close a Workbook
        5. Exit Excel
      11. Using the Excel Help System
        1. Get Microsoft Excel Help
        2. Get Suggested Commands from Shortcut Menus
        3. Get Help on the Web
    7. 4. Building a Workbook
      1. Understanding How Excel Interprets Data Entry
      2. Navigating the Worksheet
        1. Navigate Your Workbook Using the Keyboard
      3. Selecting Cells
        1. Select a Contiguous Group of Cells
        2. Select a Noncontiguous Group of Cells
        3. Select Rows or Columns
        4. Select Noncontiguous Rows or Columns
      4. Entering Text in Cells
        1. Enter Text as One Line
        2. Enter Text with Forced Line Breaks
      5. Entering Numbers in Cells
        1. Enter Numbers
        2. Enter Numbers Using Scientific Notation and Exponents
      6. Entering Dates and Times in Cells
        1. Enter a Date
        2. Enter a Time
        3. Enter a Date and Time
        4. Enter the Current Date and Time
      7. Entering Data Using Fills
        1. Fill Data Using AutoFill
        2. Use AutoFill to Enter a Series of Values
      8. Entering Data with Other Shortcuts
        1. Enter Data with AutoComplete
        2. Pick Data from a List
      9. Creating an Excel Table
        1. Create an Excel Table
        2. Add Data to an Excel Table
        3. Rename an Excel Table
        4. Resize an Excel Table
        5. Select an Excel Table Column
      10. Editing Cell Contents
        1. Edit Cell Contents in the Formula Bar
        2. Edit Cell Contents Directly in the Cell
      11. Inserting a Symbol in a Cell
        1. Add a Symbol to a Cell
      12. Creating Hyperlinks
        1. Add a Hyperlink to a Place in the Same File
        2. Add a Hyperlink to Another File
        3. Add a Hyperlink to a Web Page
        4. Add a Mailto Hyperlink
      13. Cutting, Copying, and Pasting Cell Values
        1. Cut a Cell Value
        2. Copy a Cell Value
        3. Pasting Values with More Control
        4. Paste Values with More Control
      14. Clearing Cell Contents
        1. Clear a Cell
      15. Using the Office Clipboard
        1. Display the Contents of the Office Clipboard
        2. Paste an Item from the Office Clipboard
        3. Clear an Item from the Office Clipboard
      16. Undoing or Redoing an Action
        1. Undo or Redo an Action
      17. Finding and Replacing Text
        1. Find a Word or Value
        2. Replace a Word or Value
      18. Checking the Spelling of Your Worksheet
        1. Check Spelling
    8. 5. Managing and Viewing Worksheets
      1. Viewing and Selecting Worksheets
        1. Select Multiple Worksheets
      2. Renaming Worksheets
        1. Change the Name of a Worksheet
      3. Moving Worksheets
        1. Move Worksheets Within the Workbook
        2. Move Worksheets to Another Workbook
      4. Copying Worksheets
        1. Copy Worksheets Within the Workbook
        2. Copy Worksheets to Another Workbook
      5. Inserting and Deleting Worksheets
        1. Insert a Blank Worksheet
        2. Delete One or More Worksheets
      6. Hiding or Showing a Worksheet
        1. Hide or Unhide a Worksheet
        2. Unhide a Hidden Worksheet
      7. Changing Worksheet Tab Colors
        1. Color a Sheet Tab
      8. Inserting, Moving, and Deleting Cells
        1. Inserting Cells in a Worksheet
        2. Move Cells in a Worksheet
        3. Delete Cells in a Worksheet
      9. Inserting, Moving, and Deleting Columns and Rows
        1. Insert a Row in a Worksheet
        2. Insert a Column in a Worksheet
        3. Set Insert Options
        4. Delete Rows or Columns
        5. Move One or More Rows
        6. Move One or More Columns
      10. Hiding and Unhiding Columns and Rows
        1. Hide Rows or Columns
        2. Unhide Rows or Columns
      11. Entering Data and Formatting Many Worksheets at the Same Time
        1. Enter and Format Data on Several Worksheets at One Time
        2. Copy Cells from One Worksheet to a Group of Worksheets
      12. Changing How You Look at Excel Workbooks
        1. View Different Parts of One Worksheet at the Same Time
        2. View Multiple Workbooks at the Same Time
        3. View Multiple Parts of a Worksheet by Freezing Panes
      13. Naming and Using Worksheet Views
        1. Name the Current View of the Worksheet
        2. Switch to Another View of the Worksheet
    9. 6. Using Formulas and Functions
      1. Understanding Formulas and Cell References in Excel
      2. Creating Simple Cell Formulas
        1. Build a Formula
        2. Edit a Formula
      3. Assigning Names to Groups of Cells
        1. Create a Named Range
        2. Go to a Named Range
        3. Delete a Named Range
        4. Rename a Named Range
      4. Using Names in Formulas
        1. Create a Formula with a Named Range
      5. Creating a Formula That References Values in an Excel Table
        1. Create a Formula with an Excel Table Reference
      6. Creating Formulas That Reference Cells in Other Workbooks
        1. Use Cells from Other Workbooks in a Formula
        2. Break Links to Other Workbooks and Convert to Values
        3. Refresh Links
        4. Change Links to Different Workbooks
      7. Summing a Group of Cells Without Using a Formula
        1. Summarize Data in a Group of Cells
        2. Find the Total, Average, or Other Values of Cell Data
      8. Creating a Summary Formula
        1. Create an AutoSum Formula
      9. Summing with Subtotals and Grand Totals
        1. Create a Subtotal
        2. Remove a Subtotal
      10. Exploring the Excel Function Library
        1. List Functions Available from the Excel Library
        2. Use Function ScreenTips
      11. Using the IF Function
        1. Create an IF Function
      12. Checking Formula References
        1. Find Cell Precedents and Dependents
        2. Remove Tracer Arrows
      13. Debugging Your Formulas
        1. Monitor a Formula for Changes
        2. Delete a Watch
        3. Evaluate Parts of a Formula
    10. 7. Formatting the Cell
      1. Formatting Cell Contents
        1. Change Font and Font Size
        2. Change Text Appearance
        3. Format Part of a Cell's Contents
      2. Formatting Cells Containing Numbers
        1. Display Numerical Values as Currency and Percentages
        2. Set the Number of Decimal Places
      3. Formatting Cells Containing Dates
        1. Set a Date Format
      4. Adding Cell Backgrounds and Shading
        1. Add Background Color
        2. Change Background Shading
      5. Formatting Cell Borders
        1. Draw Borders
        2. Format Cell Borders
      6. Defining Cell Styles
        1. Apply a Style
        2. Create a Style
        3. Modify a Style
        4. Delete a Style
      7. Aligning and Orienting Cell Contents
        1. Change Text Alignment
        2. Set Text Orientation and Wrapping
      8. Formatting a Cell Based on Conditions
        1. Change the Format of a Cell Based on Its Value
        2. Change the Format of a Cell Based on the Results of a Formula
        3. Edit a Conditional Formatting Rule
        4. Delete a Conditional Formatting Rule
      9. Changing How Conditional Formatting Rules Are Applied
      10. Stop When a Condition Is Met
        1. Change the Order of Conditions
      11. Displaying Data Bars, Icon Sets, or Color Scales Based on Cell Values
        1. Display Data Bars
        2. Display Icon Sets
        3. Display Color Scales
      12. Deleting Conditional Formats
        1. Delete Conditional Formats
      13. Copying Formats with Format Painter
        1. Copy Styles with Format Painter
      14. Merging or Splitting Cells or Data
        1. Merge Several Cells into One
        2. Split a Merged Cell
    11. 8. Formatting the Worksheet
      1. Applying Workbook Themes
        1. Apply a Workbook Theme
        2. Change Colors Within a Theme
        3. Change Fonts Within a Theme
        4. Change Effects Within a Theme
        5. Create a New Workbook Theme
      2. Coloring Sheet Tabs
        1. Color a Sheet Tab
      3. Changing a Worksheet's Gridlines
        1. Change the Color of Cell Gridlines
        2. Show or Hide Cell Gridlines
      4. Changing Row Heights and Column Widths
        1. Resize a Row
        2. Resize a Column
        3. Resize Multiple Rows or Columns
      5. Inserting Rows or Columns
        1. Insert a Row in a Worksheet
        2. Insert a Column in a Worksheet
        3. Set Insert Options
      6. Moving Rows and Columns
        1. Move One or More Rows
        2. Move One or More Columns
      7. Deleting Rows and Columns
        1. Delete a Row or Column
      8. Outlining to Hide and Show Rows and Columns
        1. Group Rows and Columns
        2. Ungroup Rows and Columns
        3. Hide Grouped Rows and Columns
        4. Show Grouped Rows and Columns
      9. Hiding Rows and Columns
        1. Hide Rows or Columns
        2. Unhide Rows or Columns
      10. Protecting Worksheets from Changes
        1. Protect a Worksheet
      11. Locking Cells to Prevent Changes
        1. Lock Cells
    12. 9. Printing Worksheets
      1. Previewing Worksheets Before Printing
        1. Display a Worksheet in Page Layout View
        2. View and Zoom Worksheets in Backstage View
        3. Change Column Widths and Row Heights in Page Layout View
      2. Printing Worksheets with Current Options
        1. Print Multiple Worksheets from the Same Workbook
      3. Choosing Whether to Print Gridlines and Headings
        1. Choose to Print Gridlines
        2. Choose to Print Headings
      4. Choosing Printers and Paper Options
        1. Choose a Printer
        2. Choose the Paper
      5. Printing Part of a Worksheet
        1. Set a Print Area
        2. Remove a Print Area
      6. Printing Row and Column Headings on Each Page
        1. Identify the Rows and Columns to Repeat
      7. Setting and Changing Print Margins
        1. Set Page Margins
        2. Adjust Page Margins in Backstage View
      8. Setting Page Orientation and Scale
        1. Set Page Orientation
        2. Scale the Printout to a Fixed Number of Pages
      9. Creating Headers and Footers
        1. Add a Premade Header and Footer
        2. Add Predefined Text to the Header or Footer
        3. Adjust Header and Footer Height
      10. Adding Graphics to a Header or a Footer
        1. Include a Graphic in a Header or Footer
        2. Format a Graphic in a Header or Footer
      11. Setting and Viewing Page Breaks
        1. View Current Page Breaks
        2. Set Manual Page Breaks
        3. Change Manual Page Breaks
    13. 10. Customizing Excel to the Way You Work
      1. Opening Ready-to-Use Workbook Templates
        1. Create a Workbook from a Template
        2. Save a Workbook as a Template
        3. Modify a Template
      2. Adding Commands to the Quick Access Toolbar
        1. Add a Command to the Quick Access Toolbar
        2. Remove a Command from the Quick Access Toolbar
        3. Move the Quick Access Toolbar
      3. Modifying the Ribbon User Interface
        1. Add a Command to a Ribbon Tab
        2. Reorder Commands on a Ribbon Tab
        3. Remove a Ribbon Element
        4. Create a Custom Ribbon Tab
        5. Add a New Group to a Ribbon Tab
        6. Rename a Ribbon Element
        7. Hide a Ribbon Element
        8. Redisplay a Hidden Ribbon Element
      4. Controlling Which Error Messages Appear
        1. Choose Which Error Messages Appear
        2. Reset Ignored Errors
        3. Select the Color in Which Excel Displays Errors
      5. Defining AutoCorrect and AutoFormat Entries
        1. Create an AutoCorrect Entry
        2. Delete an AutoCorrect Entry
        3. Control AutoFormat Rules
    14. 11. Sorting and Filtering Worksheet Data
      1. Sorting Worksheet Data
        1. Sort Data in Ascending or Descending Order
        2. Create a Multicolumn Sort
      2. Creating a Custom Sort List
        1. Define a Custom List of Values
        2. Sort Using a Custom List
      3. Filtering Data Quickly with AutoFilter
        1. Create a Selection Filter
        2. Create a Filtering Rule
        3. Create a Search Filter
        4. Clear a Filter from a List
      4. Creating an Advanced Filter
        1. Build an Advanced Filter
        2. Remove an Advanced Filter
      5. Validating Data for Correctness During Entry
        1. Create a Validation Rule
        2. Validate Data According to a List in a Worksheet Range
    15. 12. Summarizing Data Visually Using Charts
      1. Displaying Data Graphically
      2. Creating a Chart Quickly
        1. Create a Chart
        2. Change a Chart's Layout
        3. Change a Chart's Style
      3. Changing a Chart's Appearance
        1. Change a Chart's Type
        2. Change the Formatting of a Chart Element
      4. Formatting Chart Legends and Titles
        1. Show or Hide a Chart Legend
        2. Add Titles
        3. Add and Remove Data Labels
      5. Changing the Body of a Chart
        1. Show or Hide Chart Gridlines
        2. Change the Scale on the Value Axis
        3. Change the Scale on the Category (X) Axis
      6. Customizing Chart Data
        1. Change the Source Data for Your Chart
        2. Add a New Series
        3. Delete a Series
      7. Working with Common Charts
        1. Pull a Slice Out of a Pie Chart
        2. Create a 3-D Exploded Pie Chart
        3. Change the Way You View 3-D Charts
      8. Working with Uncommon Charts
        1. Create a Stock Chart
      9. Adding a Trendline to a Chart
        1. Add a Trendline to a Data Series
      10. Summarizing Data Using Sparklines
        1. Create a Line Sparkline
        2. Create a Column Sparkline
        3. Create a Win/Loss Sparkline
        4. Format a Sparkline
        5. Delete a Sparkline
    16. 13. Enhancing Your Worksheets with Graphics
      1. Working with Graphics in Your Worksheets
      2. Adding Graphics to Worksheets
        1. Add a Picture
        2. Delete a Picture
      3. Adding Drawing Objects to a Worksheet
        1. Add a Simple Shape
        2. Add Text to Any Shape
        3. Format Text in a Shape
      4. Adding Fills to Drawing Objects
        1. Apply a Shape Style
        2. Apply a Fill
        3. Fill an Object with a Picture
      5. Adding Effects to Drawing Objects
        1. Add or Edit an Object's Shadow
        2. Rotate an Object in Three Dimensions
      6. Customizing Pictures and Objects
        1. Resize a Picture or Object
        2. Rotate a Picture or Object
        3. Remove the Background from a Picture
      7. Aligning and Grouping Drawing Objects
        1. Align Objects
        2. Group or Ungroup Objects
        3. Change the Order of Objects
      8. Using WordArt to Create Text Effects in Excel
        1. Add WordArt Text
        2. Change WordArt Text Colors
      9. Inserting Clip Art into a Worksheet
        1. Add Clip Art
      10. Inserting and Changing a Diagram
        1. Insert a Diagram
        2. Change the Style of a Diagram
      11. Creating an Organization Chart
        1. Create an Organization Chart
        2. Add a Shape
        3. Alter the Layout of Your Organization Chart
        4. Change the Design of Your Organization Chart
      12. Adding an Equation to a Shape
        1. Add a Model Equation
        2. Add a Custom Equation
    17. 14. Sharing Excel Data with Other Programs
      1. Introducing Linking and Embedding
      2. Linking and Embedding Other Files
        1. Embed a File in a Worksheet
        2. Link to a File
      3. Exchanging Table Data Between Excel and Word
        1. Bring Word Data into Excel
        2. Copy Excel Data to Word
      4. Copying Excel Charts and Data into PowerPoint
        1. Move Excel Data to PowerPoint
        2. Copy an Excel Chart to PowerPoint
      5. Exchanging Data Between Access and Excel
        1. Bring Access Table Data into an Excel Worksheet
        2. Send Excel Data to Access
      6. Importing a Text File
        1. Bring Text into Excel
    18. 15. Using Excel in a Group Environment
      1. Sharing Workbooks in Excel
        1. Turn on Workbook Sharing
      2. Commenting in Cells
        1. Add a Comment
        2. View a Comment
        3. Edit a Comment
        4. Delete a Comment
      3. Tracking Changes in Workbooks
        1. Turn on Track Changes
      4. Accepting or Rejecting Changes
        1. View a Change
        2. Review Changes
        3. Create a Change History
      5. Saving Worksheets to the Web
        1. Save a Workbook to the Web
      6. Dynamically Update Worksheets Published to the Web
        1. Update Worksheets Published to the Web
      7. Retrieving Web Data Using Excel
        1. Retrieve Data from Web Pages
        2. Copy Data from the Web to Excel
      8. Modifying Web Queries
        1. Schedule Web Query Data Refreshes
      9. Introducing XML
      10. Interacting over the Web Using XML
        1. Save a Workbook as an XML Spreadsheet
        2. Import Spreadsheet XML
    19. Index
    20. About the Author
    21. Copyright