You are previewing Excel® 2013 On Demand.
O'Reilly logo
Excel® 2013 On Demand

Book Description

Excel 2013On Demand is built from the ground up for today's beginning-to-intermediate-level Exceluser. Like every book in the On Demand Series, it teaches visually, using an easy, friendly, full-color format designed to "show you how," instead of "telling you how." But that's not all. This book:

* Combines step-by-step training with quick-reference material users can rely on long after they've mastered the core skills they need

* Provides easy-to-follow task-based coverage of the techniques you'll use most often, presenting most tasks in just one or two pages

* Offers in-depth coverage of all new MOS (Microsoft Office Specialist) exam objectives, so you can use it as a study guide to enhance your job prospects or current career

* Provides additional end-of-chapter workshops, plus even more hands-on projects online

* Includes a complete practical troubleshooting guide

* Helps you upgrade by presenting New Feature icons, plus a detailed list of new features indexed to the pages where they're covered

Simply put, no other book offers beginning-to-intermediate-level Excelusers this much simplicity, usable content, flexibility, and value.

Table of Contents

  1. Title Page
  2. Copyright Page
  3. Acknowledgments
    1. Perspection, Inc.
    2. Acknowledgments
    3. About the Author
    4. We Want to Hear from You!
  4. Contents
  5. Introduction
    1. How This Book Works
    2. What’s New
    3. Keyboard Shortcuts
    4. Step-by-Step Instructions
    5. Real World Examples
    6. Workshops
    7. Microsoft Office Specialist
    8. Get More on the Web
  6. 1. Getting Started with Excel
    1. Introduction
    2. Starting Excel
    3. Viewing the Excel Window
    4. Using the Ribbon
    5. Choosing Commands
    6. Working with the Ribbon and Toolbars
    7. Choosing Dialog Box Options
    8. Using the Status Bar
    9. Creating a Blank Workbook
    10. Creating a Workbook Using a Template
    11. Opening an Existing Workbook
    12. Converting an Existing Workbook
    13. Using Task and Window Panes
    14. Moving Around the Workbook
    15. Arranging Windows
    16. Getting Help While You Work
    17. Saving a Workbook
    18. Saving a Workbook with Different Formats
    19. Saving a Workbook to Online Services
    20. Working with Accounts
    21. Checking Compatibility
    22. Checking Accessibility
    23. Documenting Workbook Properties
    24. Switching Views
    25. Recovering a Workbook
    26. Closing a Workbook and Exiting Excel
  7. 2. Basic Workbook Skills
    1. Introduction
    2. Making Label Entries
    3. Selecting Cells
    4. Selecting Rows, Columns, and Special Ranges
    5. Entering Labels on a Worksheet
    6. Entering Values on a Worksheet
    7. Entering Values Quickly with AutoFill
    8. Inserting Content with Flash Fill
    9. Editing Cell Contents
    10. Clearing Cell Contents
    11. Understanding How Excel Pastes Data
    12. Storing Cell Contents
    13. Copying Cell Contents
    14. Moving Cell Contents
    15. Inserting and Deleting Cell Contents
    16. Finding and Replacing Cell Contents
    17. Correcting Cell Contents with AutoCorrect
    18. Inserting Information the Smart Way
    19. Checking Spelling
    20. Changing Proofing Options
    21. Using Custom Dictionaries
    22. Inserting Symbols
    23. Finding the Right Words
    24. Inserting Research Material
    25. Translating Text to Another Language
    26. Using Multiple Languages
    27. Working with Touch Screens
    28. Undoing and Redoing an Action
  8. 3. Working with Formulas and Functions
    1. Introduction
    2. Understanding Formulas
    3. Creating a Simple Formula
    4. Creating a Formula Using Formula AutoComplete
    5. Editing a Formula
    6. Understanding Cell Referencing
    7. Using Absolute Cell References
    8. Using Mixed Cell References
    9. Using 3-D Cell References
    10. Naming Cells and Ranges
    11. Entering Named Cells and Ranges
    12. Managing Names
    13. Simplifying a Formula with Ranges
    14. Displaying Calculations with the Status Bar
    15. Calculating Totals with AutoSum
    16. Calculating Totals with Quick Analysis
    17. Performing One Time Calculations
    18. Converting Formulas and Values
    19. Correcting Calculation Errors
    20. Correcting Formulas
    21. Auditing a Worksheet
    22. Locating Circular References
    23. Performing Calculations Using Functions
    24. Creating Functions
    25. Creating Functions Using the Library
    26. Calculating Multiple Results
    27. Using Nested Functions
    28. Using Constants and Functions in Names
  9. 4. Modifying Worksheets and Workbooks
    1. Introduction
    2. Selecting and Naming a Worksheet
    3. Inserting and Deleting a Worksheet
    4. Moving and Copying a Worksheet
    5. Hiding and Unhiding Worksheets and Workbooks
    6. Selecting a Column or Row
    7. Hiding and Unhiding a Column or Row
    8. Inserting a Column or Row
    9. Deleting a Column or Row
    10. Adjusting Column Width and Row Height
    11. Freezing and Unfreezing a Column or Row
    12. Splitting a Worksheet into Panes
    13. Showing and Hiding Workbook Elements
    14. Zooming the View In and Out
    15. Creating Custom Views
    16. Setting Up for Personal Templates
    17. Creating a Personal Template
    18. Opening a Template
    19. Changing a Template
  10. 5. Formatting a Worksheet
    1. Introduction
    2. Formatting Numbers
    3. Formatting Text
    4. Applying Conditional Formatting
    5. Applying Specialized Conditional Formatting
    6. Creating Conditional Formatting
    7. Clearing Conditional Formatting
    8. Managing Conditional Formatting
    9. Finding Conditional Formatting
    10. Creating Sparkline Formatting
    11. Changing Data Alignment
    12. Controlling Text Flow
    13. Changing Data Color
    14. Adding Color and Patterns to Cells
    15. Adding Custom Colors
    16. Adding Borders to Cells
    17. Formatting Tabs and Background
    18. Copying Cell Formats
    19. Understanding Color Themes
    20. Viewing and Applying a Theme
    21. Creating Theme Colors
    22. Choosing Theme Fonts
    23. Choosing Theme Effects
    24. Creating a Custom Theme
    25. Choosing a Custom Theme
    26. Applying and Creating Cell Styles
    27. Modifying a Cell Style
    28. Finding and Replacing Cell Formatting
  11. 6. Viewing and Printing Worksheets and Workbooks
    1. Introduction
    2. Setting Up the Page
    3. Adjusting Page Margins
    4. Adding Headers and Footers
    5. Inserting Page Breaks
    6. Customizing Worksheet Printing
    7. Setting the Print Area
    8. Previewing a Worksheet
    9. Printing a Worksheet and Workbook
    10. Creating a PDF Document
    11. Creating a XPS Document
  12. 7. Inserting and Modifying Graphics
    1. Introduction
    2. Locating and Inserting Online Pictures
    3. Inserting Pictures from an Online Service
    4. Inserting a Picture from a SkyDrive or SharePoint
    5. Inserting a Picture from a File
    6. Inserting a Picture Screen Shot
    7. Adding an Artistic Style to a Picture
    8. Adding a Quick Style to a Picture
    9. Applying a Shape to a Picture
    10. Applying a Border to a Picture
    11. Applying Picture Effects
    12. Modifying Picture Size
    13. Compressing a Picture
    14. Correcting a Picture
    15. Recoloring a Picture
    16. Cropping and Rotating a Picture
    17. Removing a Picture Background
    18. Creating WordArt Text
    19. Formatting WordArt Text
    20. Applying WordArt Text Effects
    21. Modifying WordArt Text Position
    22. Creating SmartArt Graphics
    23. Using the Text Pane with SmartArt Graphics
    24. Modifying a SmartArt Graphic
    25. Resizing a SmartArt Graphic
    26. Formatting a SmartArt Graphic
    27. Formatting a Shape in a SmartArt Graphic
    28. Adding Pictures to a SmartArt Graphic
    29. Creating an Organization Chart
    30. Modifying an Organization Chart
  13. 8. Drawing and Modifying Shapes
    1. Introduction
    2. Drawing and Resizing Shapes
    3. Inserting Multiple Shapes
    4. Adding Text to a Shape
    5. Drawing Lines and Arrows
    6. Creating and Editing Freeforms
    7. Copying and Moving Objects
    8. Adding a Quick Style to a Shape
    9. Adding a Quick Style to Shape Text
    10. Applying Color Fills
    11. Applying Picture Fills
    12. Applying Texture Fills
    13. Applying Gradient Fills
    14. Applying Shape Effects
    15. Adding 3-D Effects to a Shape
    16. Adding 3-D Rotation Effects to a Shape
    17. Creating Shadows
    18. Aligning and Distributing Objects
    19. Connecting Shapes
    20. Selecting Objects Using the Selection Pane
    21. Changing Stacking Order
    22. Rotating and Flipping Objects
    23. Grouping and Ungrouping Shapes
  14. 9. Creating and Modifying Charts
    1. Introduction
    2. Understanding Chart Terminology
    3. Choosing the Right Type of Chart
    4. Creating a Chart
    5. Editing a Chart
    6. Moving and Resizing a Chart
    7. Selecting Chart Elements
    8. Changing Chart Elements
    9. Changing a Chart Type
    10. Changing a Chart Layout and Style
    11. Formatting Chart Elements
    12. Changing Chart Gridlines and Axes
    13. Changing Chart Titles
    14. Changing Chart Labels
    15. Pulling Out a Pie Slice
    16. Formatting Chart Data Series
    17. Formatting Chart Text
    18. Formatting Line and Bar Charts
    19. Changing the Chart Background
    20. Enhancing a Chart
    21. Editing Chart Data
    22. Adding and Deleting a Data Series
    23. Saving a Chart Template
    24. Managing Chart Templates
  15. 10. Analyzing Worksheet Data
    1. Introduction
    2. Understanding Tables
    3. Creating a Table
    4. Formatting a Table
    5. Creating or Modifying a Table Style
    6. Formatting Table Elements
    7. Creating Calculations in a Table
    8. Working with Tables
    9. Removing Table Rows and Columns
    10. Entering Data in a Table Using a Drop-Down List
    11. Sorting Data in a Table
    12. Displaying Parts of a Table with AutoFilter
    13. Creating Custom Searches
    14. Using Slicers to Filter a Table
    15. Exporting a Table to a SharePoint List
    16. Analyzing Data Using a PivotTable
    17. Analyzing Data Using a PivotChart
    18. Updating a PivotTable or PivotChart
    19. Modifying a PivotTable
    20. Formatting a PivotTable
    21. Working with PivotTable Data
    22. Using Slicers to Filter a PivotTable
    23. Display a Timeline to Filter a PivotTable
    24. Drilling into PivotTable Data
    25. Charting a PivotTable
    26. Presenting Data with Power View
    27. Working with PowerPivot
    28. Creating Groups and Outlines
    29. Converting Text to Columns
    30. Adding Data Validation to a Worksheet
    31. Creating a Drop-Down List
  16. 11. Building More Powerful Worksheets
    1. Introduction
    2. Using Data Analysis Tools
    3. Using the Euro Conversion
    4. Looking at Alternatives  with Data Tables
    5. Asking “What If” with Goal Seek
    6. Creating Scenarios
    7. Using Solver
    8. Using Inquire Tools
    9. Using Lookup and Reference Functions
    10. Using Text Functions
    11. Summarizing Data Using Subtotals
    12. Summarizing Data Using Functions
    13. Using Date & Time Functions
    14. Using Logical Functions
    15. Using Financial Functions
    16. Using Math Functions
    17. Using Statistical Functions
    18. Using Information Functions
    19. Using Compatibility Functions
    20. Using Web Functions
  17. 12. Protecting and Securing a Workbook
    1. Introduction
    2. Inspecting Workbooks
    3. Protecting a Worksheet
    4. Locking or Unlocking Worksheet Cells
    5. Adding Security Encryption to a Workbook
    6. Adding Password Protection to a Workbook
    7. Adding a Digital Signature
    8. Adding a Signature Line
    9. Avoiding Harmful Attacks
    10. Using the Trust Center
    11. Selecting Trusted Publishers and Locations
    12. Setting Document Related Security Options
    13. Setting App Catalog Security Options
    14. Setting Add-in Security Options
    15. Setting ActiveX Security Options
    16. Setting Macro Security Options
    17. Changing Message Bar Security Options
    18. Setting Privacy Options
    19. Setting External Content Security Options
    20. Working with Office Safe Modes
    21. Marking a Workbook as Read-Only
  18. 13. Reviewing and Sharing Workbook Data
    1. Introduction
    2. Sharing Workbooks
    3. Creating and Reading a Cell Comment
    4. Editing and Deleting a Cell Comment
    5. Tracking Workbook Changes
    6. Sending a Workbook Using E-Mail
    7. Sending a Workbook by Internet Fax
    8. Sending a Workbook by Instant Message
    9. Presenting a Workbook Online
    10. Sharing Information Between Programs
    11. Linking Data
    12. Linking and Embedding Files
    13. Exporting and Importing Data
    14. Consolidating Data
    15. Getting External Data
    16. Getting Text Data
    17. Connecting to External Data
    18. Getting Query Data from a Database
    19. Getting Data from Microsoft Access
    20. Working with XML
    21. Creating an XML Data Map
    22. Saving or Exporting Data in XML
  19. 14. Publishing Workbook Data as Web Pages
    1. Introduction
    2. Opening a Workbook as a Web Page
    3. Previewing a Web Page
    4. Creating a Hyperlink
    5. Formatting a Hyperlink
    6. Changing Web Page Options
    7. Saving a Worksheet as a Web Page
    8. Publishing a Web Page
    9. Copying a Web Table to a Worksheet
    10. Creating Refreshable Web Queries
    11. Getting Data from Web Queries
    12. Getting Documents from the Web
  20. 15. Tools for Working More Efficiently
    1. Introduction
    2. Setting General Excel Options
    3. Setting New Workbook and Start Up Options
    4. Setting Editing Options
    5. Setting Image Options
    6. Setting Chart Options
    7. Setting Workbook and Worksheet Display Options
    8. Setting Display View Options
    9. Setting Advanced Options
    10. Setting Formula Options
    11. Changing Advanced Document Properties
    12. Customizing the Way You Create Objects
    13. Accessing Commands Not in the Ribbon
    14. Working with Office Tools
    15. Maintaining and Repairing Office
  21. 16. Expanding Excel Functionality
    1. Introduction
    2. Adding and Inserting Apps for Office
    3. Viewing and Managing Add-ins
    4. Loading and Unloading Add-ins
    5. Enhancing a Workbook with VBA
    6. Viewing the Visual Basic Editor
    7. Setting Developer Options
    8. Understanding How Macros Automate Your Work
    9. Recording a Macro
    10. Creating a Macro
    11. Running a Macro
    12. Controlling a Macro
    13. Adding a Digital Signature to a Macro Project
    14. Assigning a Macro to a Toolbar or Ribbon
    15. Saving a Workbook with Macros
    16. Opening a Workbook with Macros
    17. Inserting ActiveX Controls
    18. Using ActiveX Controls
    19. Setting ActiveX Control Properties
    20. Adding VBA Code to an ActiveX Control
  22. 17. Working Online with Office Documents
    1. Introduction
    2. Working Online with SharePoint and SkyDrive
    3. Signing in to SharePoint or SkyDrive
    4. Saving and Opening on SharePoint or SkyDrive
    5. Accessing Documents on SharePoint
    6. Syncing Documents on SharePoint
    7. Sharing Documents on SkyDrive
    8. Accessing Documents on SkyDrive
    9. Managing Documents on SkyDrive
    10. Downloading or Uploading Documents on SkyDrive
    11. Creating Office Documents on SkyDrive
    12. Sending Links to Documents on SkyDrive
    13. Comparing the Office Desktop App to the Web App
    14. Working with Office Web Apps
    15. Saving or Printing Documents in Office Web Apps
    16. Co-authoring Documents with Office Web Apps
  23. New! Features
    1. Microsoft Excel 2013
  24. Microsoft Office Specialist
    1. About the MOS Program
    2. What Does This Logo Mean?
    3. Preparing for a MOS Exam
    4. Taking a MOS Exam
    5. Getting More Information
  25. Index
  26. Online Workshop