Microsoft® Excel® 2010 Inside Out

Microsoft® Excel® 2010 Inside Out

by Mark Dodge and Craig Stinson
Released August 2010
Publisher(s): Microsoft Press
ISBN: 9780735647824

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

You're beyond the basics, so dive in and really put your spreadsheet skills to work! This supremely organized reference is packed with hundreds of timesaving solutions, troubleshooting tips, and workarounds. It's all muscle and no fluff. Discover how the experts tackle Excel 2010-and challenge yourself to new levels of mastery!

  • Learn expert techniques for designing powerful spreadsheets

  • Apply built-in functions-or write your own-and carry out complex calculations

  • Use rich charting and graphic capabilities to visualize data

  • Perform sophisticated data analysis: financial, statistical, and "what-if"

  • Design PivotTable reports to dynamically analyze data

  • Share and collaborate with others-while managing sensitive data

  • Link and embed Excel data into other documents

  • Create macros with Microsoft Visual Basic for Applications

  • Sample spreadsheets from inside the book

  • Add-ins and other resources to help you extend Microsoft Office programs

  • Links to demos, user communities, and product support

    •

    Table of contents

    1. Microsoft® Excel® 2010 Inside Out
    2. Dedication
    3. A Note Regarding Supplemental Files
    4. Acknowledgments
    5. Questions and Support
      1. Using the Companion Content
      2. Your Companion eBook
      3. Support Information
      4. We Want to Hear from You
    6. Conventions and Features Used in This Book
      1. Text Conventions
      2. Design Conventions
    7. 1. Examining the Excel Environment
      1. 1. What’s New in Microsoft Excel 2010
        1. New and Improved for 2010
          1. Backstage View
          2. Ribbon Customization
          3. Sparklines
          4. Paste Preview
          5. Improved Picture Editing
          6. Office Web Apps
          7. Slicers
          8. Improved Conditional Formatting
          9. New Functions and Functional Consistency
          10. Improved Math Equation Support
          11. Improved Charting Capacity
          12. Additional SmartArt Graphics
          13. 64-Bit Edition
          14. Office Mobile 2010
        2. If You Missed the Last Upgrade
        3. Retired in 2007
        4. If You Missed the Last Two Upgrades
        5. Onward
      2. 2. Exploring Excel Fundamentals
        1. What Happens After You Install Excel?
          1. Activating Excel
          2. Getting Updates
        2. Examining the Excel 2010 Workspace
          1. Facts About Worksheets
          2. Using the Workbook Window
            1. The Title Bar
            2. Getting Around in the Workbook
            3. Workbook Navigation Tips
            4. Resizing the Window
          3. Exploring the Ribbon
            1. The Ribbon Speaks
            2. Drop-Down Lists and Dialog Box Launchers
            3. Galleries and Live Preview
            4. Understanding Contextual Tool Sets
          4. Using the Quick Access Toolbar
          5. Accessing Commands with the Keyboard
          6. The Joy of Shortcut Menus
          7. Meet the Formula Bar
          8. Facts About the Status Bar
          9. Introducing Backstage View
        3. Exploring File Management Fundamentals
          1. Creating Workbooks
            1. Installing Your Own Templates
            2. Create Your Own Template Tabs
          2. Saving Files
            1. Rules for File Naming
            2. File Formats
            3. Ensuring File Compatibility with Previous Versions of Excel
            4. Understanding the “XL” Formats
            5. Specifying the Default File Format
            6. Creating Automatic Backup Files
            7. Protecting Files
            8. Adding Summary Information to Files
            9. Saving the Entire Workspace
          3. Opening Files
            1. Special Ways to Open Files
            2. Opening Files When You Start Excel
            3. Recovering Corrupted Files
        4. Importing and Exporting Files
          1. Using the Open and Save As Commands to Import and Export Files
          2. Sharing Data with Excel for the Macintosh
          3. Sharing Data Beyond Excel
            1. Introducing Office Web Apps
            2. Using Web File Formats
            3. Importing and Exporting Text Files
            4. Other File Formats
        5. Using the Help System
          1. Help on the Surface
          2. Help in Depth
        6. Recovering from Crashes
          1. Understanding AutoRecover
          2. Managing Versions
      3. 3. Custom-Tailoring the Excel Workspace
        1. Customizing the Ribbon
          1. Identifying Items in the Customize Ribbon and Quick Access Toolbar Categories
          2. Creating a Custom Ribbon Tab
          3. Creating a Custom Command Group
          4. Adding Existing Command Groups to Custom Tabs
          5. Resetting and Recycling Your Customizations
        2. Customizing the Quick Access Toolbar
          1. Positioning the Toolbar
          2. Adding Tools to the Toolbar
            1. Adding Tools as You Work
            2. Removing Tools
            3. Adding and Organizing Tools
            4. Too Many Tools?
          3. Creating Your Own Buttons
          4. Saving and Resetting Your Custom Quick Access Toolbar
          5. Restoring the Toolbar
          6. Exploring Other Toolbar and Ribbon Options
        3. Controlling Other Elements of the Excel 2010 Interface
          1. Displaying Underlying Formulas
          2. Hiding Zeros
        4. Enhancing Accessibility
      4. 4. Security and Privacy
        1. The Trust Center
          1. Trusted Publishers, Locations, and Documents
          2. Add-Ins, ActiveX Settings, and Macro Settings
          3. Message Bar
          4. External Content
          5. File Block Settings
          6. Privacy Options
        2. File Security
          1. Removing Personal Information from Your Workbooks
          2. Using Digital Signatures
        3. Other Security Features
    8. 2. Building Worksheets
      1. 5. Planning Your Worksheet Design
        1. Which Data Should Be in Rows, and Which in Columns?
        2. Will You Need to Print the Worksheet?
        3. Who Is the Audience?
        4. Would Your Worksheet Survive Without You?
        5. Does the Worksheet Rely on Imported Data?
        6. Do You Need More Than One Worksheet?
        7. Have You Allowed Room for New Data?
      2. 6. How to Work a Worksheet
        1. Moving Around Regions
          1. Navigating Regions with the Keyboard
          2. Navigating Regions with the Mouse
          3. Navigating with Special Keys
        2. Understanding Selection
          1. Selecting with the Mouse
            1. Zooming to Select Large Worksheet Areas
            2. Selecting Columns, Rows, and Nonadjacent Ranges
            3. Selecting Regions
          2. Using the Find & Select Commands
          3. Selecting with Go To Special
            1. Selecting Precedents and Dependents
            2. Selecting Row or Column Differences
        3. Techniques for Entering Data
          1. Making Entries in Cells and in the Formula Bar
            1. Entering Simple Numeric and Text Values
            2. Using Special Characters
            3. Understanding the Difference Between Displayed Values and Underlying Values
            4. Creating Long Text Values
            5. Using Text Wrapping
            6. Understanding Numeric Text Entries
            7. Entering Symbols
          2. Making Entries in Ranges
          3. Editing and Undoing Entries
        4. Managing Worksheets
          1. Inserting and Deleting Worksheets
          2. Naming and Renaming Worksheets
          3. Moving and Copying Worksheets
        5. Viewing Worksheets
          1. Splitting Worksheets into Panes
          2. Freezing Panes
          3. Zooming Worksheets
          4. Using Custom Views
        6. Protecting Worksheets
          1. Unlocking Individual Cells
          2. Protecting the Workbook
          3. Allowing Password Access to Specific Cell Ranges
          4. Hiding Cells and Worksheets
          5. Using Passwords
      3. 7. How to Work a Workbook
        1. Managing Multiple Workbooks
          1. Navigating Between Open Workbooks
          2. Arranging Workbook Windows
          3. Getting the Most Out of Your Screen
          4. Comparing Worksheets Side by Side
        2. Opening Multiple Windows for the Same Workbook
          1. Useful Inconsistencies of New Windows
        3. Hiding and Protecting Workbooks
          1. Hiding Workbooks
          2. Protecting Workbooks
          3. Encrypting Workbooks
          4. Saving Workbooks or Windows as Hidden
          5. Hiding Worksheets
          6. Marking as Final
    9. 3. Formatting and Editing Worksheets
      1. 8. Worksheet Editing Techniques
        1. Copying, Cutting, and Pasting
          1. Copying and Pasting
            1. Collecting Multiple Items on the Clipboard
            2. Pasting Multiples
            3. Using the Paste Options Button
          2. Cutting and Pasting
            1. Pasting Selectively Using Paste Special
            2. Pasting Using Math Operators
            3. Pasting Links
            4. Skipping Blank Cells
            5. Transposing Entries
          3. Pasting Hyperlinks
          4. Moving and Copying with the Mouse
        2. Inserting and Deleting
          1. Inserting Columns and Rows
          2. Inserting Cells
            1. Inserting Copied or Cut Cells
          3. Deleting Cells, Columns, and Rows
          4. Clearing Cells
          5. Inserting, Deleting, and Clearing Cells with the Mouse
          6. Dragging with the Right Mouse Button
        3. Undoing Previous Actions
          1. Redoing What You’ve Undone
          2. Repeating Your Last Action
        4. Editing Cell Contents
          1. Editing in Cells or in the Formula Bar
          2. Editing Options
          3. Filling and Creating Data Series
            1. Extending with Auto Fill
            2. Dragging the Fill Handle with the Right Mouse Button
            3. Using the Series Command
            4. Using the Fill Menu Commands
            5. Distributing Long Entries Using the Justify Command
            6. Creating Custom Lists
            7. Importing Custom Lists
          4. Extending Existing Formatting
        5. Finding and Replacing Stuff
          1. Finding Formatting
          2. Specifying Variables Using Wildcard Characters
          3. Replacing What You Find
        6. Getting the Words Right
          1. Fixing Errors as You Type
            1. Typing Internet and Network Addresses
            2. Using Custom AutoCorrect Actions
          2. Letting Excel Help with Typing Chores
          3. Cheking Yer Speling
          4. Research Resources
        7. Editing Multiple Worksheets
          1. Grouping Worksheets for Editing
            1. What You Can Do in Group-Editing Mode
          2. Filling a Group
        8. Auditing and Documenting Worksheets
          1. Checking for Errors
            1. Checking for Errors
            2. Evaluating and Auditing Formulas
            3. Watching Formulas
          2. Tracing Cell References
            1. Tracing Dependent Cells
            2. Clearing Tracer Arrows
            3. Tracing Precedent Cells
            4. Tracing Errors
            5. Tracing References to Other Worksheets
          3. Adding Comments to Cells
            1. Tweaking Your Comments
            2. Printing Comments
        9. Outlining Worksheets
          1. Outlining a Worksheet with Nonstandard Layout
          2. Extending the Outline to New Worksheet Areas
          3. Hiding an Outline
          4. Collapsing and Expanding Outline Levels
          5. Displaying a Specific Outline Level
          6. Ungrouping and Grouping Columns and Rows
        10. Consolidating Worksheets
          1. Consolidating by Position
          2. Consolidating by Category
            1. Creating Links to the Source Worksheets
      2. 9. Worksheet Formatting Techniques
        1. Formatting Fundamentals
          1. Formatting Tables
            1. Options for Applying Table Formats
            2. Creating Custom Table Formats
            3. Removing the Automatic Table Features
          2. Painting Formats
        2. Using Themes and Cell Styles
          1. Formatting with Themes
            1. Creating Custom Themes
          2. Formatting with Cell Styles
            1. Creating Custom Cell Styles
        3. Formatting Conditionally
          1. Creating Conditional Formatting Rules
          2. Managing Conditional Formatting Rules
            1. Copying, Clearing, and Finding Conditional Formats
          3. Creating Conditional Formatting Formulas
        4. Formatting in Depth
          1. Formatting Individual Characters
          2. Formatting as You Type
          3. Understanding the General Format
          4. Formatting Numbers
            1. Using Currency Formats
            2. Using Accounting Formats
            3. Formatting Percentages
            4. Formatting Fractions
            5. Formatting Scientific (Exponential) Values
            6. Understanding the Text Format
            7. Using the Special Formats
            8. Creating Custom Number Formats
          5. Aligning Data in Cells
            1. Aligning Text Horizontally
          6. Aligning Text Vertically
            1. Controlling Text Orientation
            2. Shrinking Text to Fit in Cells
          7. Using Fonts
          8. Customizing Borders
          9. Applying Colors and Patterns
            1. Adding Graphic Backgrounds to Worksheets
          10. Controlling the Size of Cells
            1. Changing Column Widths
            2. Changing Row Heights
            3. Merging and Unmerging Cells
        5. Using Template Files to Store Formatting
    10. 4. Adding Graphics and Printing
      1. 10. Creating and Formatting Graphics
        1. Using the Shapes Tools
          1. Drawing Constrained Objects
          2. Drawing Freehand Lines and Polygons
            1. Adjusting Freehand Shapes with the Edit Points Command
            2. Working with Curves
          3. Working with Text Boxes
            1. Adding Text to Other Shapes
          4. Working with Shapes
            1. Using Connectors and Callouts
        2. Creating WordArt
        3. Creating SmartArt
        4. Inserting Other Graphics
          1. Inserting Clip Art and Media Files
          2. Inserting Pictures
          3. Inserting Other Objects
        5. Formatting Graphics
          1. Using Picture Tools
            1. Making Adjustments to Your Images
          2. Using Drawing Tools
          3. Using SmartArt Tools
          4. Formatting Text in Graphics
          5. Applying Compression to Pictures
          6. Using Advanced Object Formatting Effects
            1. Formatting Fills and Lines
            2. Applying Shadows
            3. Applying 3-D Effects
            4. Formatting Objects That Contain Text
          7. Formatting Embedded Objects
        6. Working with Graphic Objects
          1. Selecting and Grouping Objects
          2. Positioning Objects
            1. Tools to Help You Position Objects on the Worksheet
          3. Protecting Objects
        7. More Tricks with Graphic Objects
          1. Assigning Macros to Objects
          2. Creating Screenshots or Pictures of Your Worksheets
            1. Taking Screenshots
            2. Copying Cells as a Picture
            3. Pasting Cells as a Picture
            4. Creating Linked Images of Cells Using the Camera Button
      2. 11. Printing and Presenting
        1. Controlling the Appearance of Your Pages
          1. Setting Page Options
            1. Printing Wide or Tall
            2. Specifying Paper Size and Print Quality
            3. Setting a Reduction (Scaling) Ratio
            4. Setting the First Page Number
          2. Working in Page Layout View
          3. Setting Margins
          4. Creating a Header and Footer
            1. Using the Header/Footer Tab
            2. Adding Pictures to Headers and Footers
          5. Setting Worksheet Options
            1. Specifying the Area to Be Printed
            2. Specifying Rows and Columns to Print on Every Page
            3. Printing Gridlines and Headings
            4. Printing Comments and Errors
            5. Printing Drafts
            6. Translating Screen Colors to Black and White
            7. Setting the Printing Order of Large Print Ranges
        2. Controlling What and Where to Print
        3. Adjusting Page Breaks
          1. Using Page Break Preview
          2. Inserting and Removing Manual Page Breaks
        4. Using Print Preview
        5. Creating Portable Documents
    11. 5. Creating Formulas and Performing Data Analysis
      1. 12. Building Formulas
        1. Formula Fundamentals
          1. Understanding the Precedence of Operators
          2. Using Cell References in Formulas
            1. Entering Cell References by Clicking
            2. Understanding Relative, Absolute, and Mixed References
            3. Creating References to Other Worksheets in the Same Workbook
            4. Creating References to Worksheets in Other Workbooks
            5. How Copying Affects Cell References
          3. Editing Formulas
            1. Understanding Reference Syntax
            2. Using Numeric Text in Formulas
            3. About Text Values
          4. Understanding Error Values
        2. Using Functions: A Preview
          1. Using the Sum Button
          2. Inserting a Function
          3. Using Formula AutoComplete
        3. Working with Formulas
          1. Naming Cells and Cell Ranges
            1. Using Names in Formulas
            2. Defining and Managing Names
            3. Editing Names
            4. Workbook-Wide vs. Worksheet-Only Names
            5. Creating Names Semiautomatically
            6. Naming Constants and Formulas
            7. Using Relative References in Named Formulas
            8. Creating Three-Dimensional Names
            9. Inserting Names in Formulas
            10. Creating a List of Names
            11. Replacing References with Names
            12. Using Go To with Names
          2. Getting Explicit About Intersections
          3. Creating Three-Dimensional Formulas
          4. Formula-Bar Formatting
          5. Using Structured References
            1. Understanding Structured Reference Syntax
            2. Using Operators with Column Specifiers
            3. About the Special Item Specifiers
            4. Using Formula AutoComplete with Structured References
            5. Filling and Copying Structured References
        4. Worksheet Calculation
          1. Recalculating Manually
          2. Calculating Part of a Formula
          3. Working with Circular References
          4. Understanding the Precision of Numeric Values
        5. Using Arrays
          1. One-Dimensional Arrays
          2. Array Formula Rules
          3. Two-Dimensional Arrays
          4. Single-Cell Array Formulas
          5. Using Array Constants
          6. Understanding Array Expansion
        6. Linking Workbooks
          1. Saving Linked Workbooks
          2. Opening a Dependent Workbook
          3. Editing Links
          4. Copying, Cutting, and Pasting in Linked Workbooks
            1. Copying and Pasting Between Workbooks
            2. Cutting and Pasting Between Workbooks
        7. Creating Conditional Tests
          1. Using Conditional Functions
          2. Using Lookup Functions
      2. 13. Using Functions
        1. Using the Built-In Function Reference in Excel
        2. Exploring the Syntax of Functions
          1. Expressions as Arguments
          2. Types of Arguments
            1. Numeric Values
            2. Text Values
            3. Logical Values
            4. Named References
            5. Arrays
            6. Mixed Argument Types
        3. Inserting Functions
          1. Inserting References and Names
      3. 14. Everyday Functions
        1. Understanding Mathematical Functions
          1. Using the SUM Function
            1. The Sum Button
            2. The SUMIF, SUMIFS, and COUNTIF Functions
          2. Using Selected Mathematical Functions
            1. The PRODUCT and SUMPRODUCT Functions
            2. The MOD Function
            3. The COMBIN Function
            4. The RAND and RANDBETWEEN Functions
          3. Using the Rounding Functions
            1. The ROUND, ROUNDDOWN, and ROUNDUP Functions
            2. The EVEN and ODD Functions
            3. The FLOOR and CEILING Functions
            4. The INT Function
            5. The TRUNC Function
        2. Understanding Text Functions
          1. Using Selected Text Functions
            1. The TEXT Function
            2. The DOLLAR Function
            3. The LEN Function
            4. The ASCII Functions: CHAR and CODE
            5. The Cleanup Functions: TRIM and CLEAN
            6. The EXACT Function
            7. The Case Functions: UPPER, LOWER, and PROPER
          2. Using the Substring Text Functions
            1. The FIND and SEARCH Functions
            2. The RIGHT and LEFT Functions
            3. The MID Function
            4. The REPLACE and SUBSTITUTE Functions
            5. The CONCATENATE Function
        3. Understanding Logical Functions
          1. Using Selected Logical Functions
            1. The IF Function
            2. The AND, OR, and NOT Functions
            3. Nested IF Functions
            4. Other Uses for Conditional Functions
        4. Understanding Information Functions
          1. Using Selected Information Functions
            1. The TYPE and ERROR.TYPE Functions
            2. The COUNTBLANK Function
          2. Using the IS Information Functions
        5. Understanding Lookup and Reference Functions
          1. Using Selected Lookup and Reference Functions
            1. The VLOOKUP and HLOOKUP Functions
            2. The LOOKUP Function
            3. The ADDRESS Function
            4. The CHOOSE Function
            5. The MATCH Function
            6. The INDEX Function
            7. The INDIRECT Function
            8. The ROW and COLUMN Functions
            9. The ROWS and COLUMNS Functions
            10. The AREAS Function
            11. The TRANSPOSE Function
      4. 15. Formatting and Calculating Date and Time
        1. Understanding How Excel Records Dates and Times
        2. Entering Dates and Times
          1. Entering a Series of Dates
            1. Extending an Existing Date Series
        3. Formatting Dates and Times
          1. Creating Your Own Date and Time Formats
            1. Measuring Elapsed Time
        4. Calculating with Date and Time
        5. Working with Date and Time Functions
          1. Using the TODAY and NOW Functions
            1. Using the TODAY and NOW Functions
            2. Using the WEEKDAY Function
            3. Using the YEAR, MONTH, and DAY Functions
            4. Using the HOUR, MINUTE, and SECOND Functions
            5. Using the DATEVALUE and TIMEVALUE Functions
          2. Working with Specialized Date Functions
            1. Using the EDATE and EOMONTH Functions
            2. Using the YEARFRAC Function
            3. Using the WORKDAY, NETWORKDAYS, WORKDAY.INTL and NETWORKDAYS.INTL Functions
      5. 16. Functions for Financial Analysis
        1. Calculating Investments
          1. The PV Function
          2. The NPV Function
          3. The FV Function
          4. The PMT Function
          5. The IPMT Function
          6. The PPMT Function
          7. The NPER Function
          8. The RATE Function
          9. The IRR Function
          10. The MIRR Function
        2. Calculating Depreciation
          1. The SLN Function
          2. The DDB and DB Functions
          3. The VDB Function
          4. The SYD Function
        3. Analyzing Securities
          1. The DOLLARDE and DOLLARFR Functions
          2. The ACCRINT and ACCRINTM Functions
          3. The INTRATE and RECEIVED Functions
          4. The PRICE, PRICEDISC, and PRICEMAT Functions
          5. The DISC Function
          6. The YIELD, YIELDDISC, and YIELDMAT Functions
          7. The TBILLEQ, TBILLPRICE, and TBILLYIELD Functions
          8. The COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD Functions
          9. The DURATION and MDURATION Functions
        4. Using the Euro Currency Tools Add-In
      6. 17. Functions for Analyzing Statistics
        1. Analyzing Distributions of Data
          1. Using Built-In Statistical Functions
            1. The AVERAGE Functions
            2. The MEDIAN, MODE.SNGL, MODE.MULT, MAX, MIN, and COUNT Functions
          2. Using Functions That Analyze Rank and Percentile
            1. The PERCENTRANK Functions
            2. The PERCENTILE and QUARTILE Functions
            3. The SMALL and LARGE Functions
            4. The RANK Functions
          3. Using Sample and Population Statistical Functions
            1. Calculating Sample Statistics: VAR.S and STDEV.S
            2. Calculating Total Population Statistics: VAR.P and STDEV.P
        2. Understanding Linear and Exponential Regression
          1. Calculating Linear Regression
            1. The LINEST Function
            2. The TREND Function
            3. The FORECAST Function
            4. The SLOPE Function
            5. The STEYX Function
          2. Calculating Exponential Regression
            1. The LOGEST Function
            2. The GROWTH Function
        3. Using the Analysis Toolpak Data Analysis Tools
          1. Installing the Analysis Toolpak
          2. Using the Descriptive Statistics Tool
          3. Creating Histograms
            1. Analyzing Distribution with the FREQUENCY Function
          4. Using the Rank And Percentile Tool
            1. Generating Random Numbers
            2. Distributing Random Numbers Uniformly
            3. Distributing Random Numbers Normally
            4. Generating Random Numbers Using Bernoulli Distribution
            5. Generating Random Numbers Using Binomial Distribution
            6. Generating Random Numbers Using Poisson Distribution
            7. Generating Random Numbers Using Discrete Distribution
            8. Generating Semi-Random Numbers Using Patterned Distribution
          5. Sampling a Population of Numbers
          6. Calculating Moving Averages
      7. 18. Performing What-If Analysis
        1. Using Data Tables
          1. Data Tables Based on One Input Variable
          2. Single-Variable Tables with More Than One Formula
          3. Data Tables Based on Two Input Variables
          4. Editing Tables
        2. Using the Scenario Manager
          1. Defining Scenarios
          2. Browsing Your Scenarios
          3. Adding, Editing, and Deleting Scenarios
            1. Tracking Changes
          4. Routing and Merging Scenarios
          5. Creating Scenario Reports
            1. The Scenario Summary Report
            2. The Scenario PivotTable Report
        3. Using the Goal Seek Command
          1. Precision and Multiple Solutions
        4. Using the Solver
          1. Stating the Objective
          2. Specifying Variable Cells
          3. Specifying Constraints
            1. Specifying Integer Constraints
          4. Other Solver Options
            1. Linear Models
            2. Viewing Iteration Results
          5. Saving and Reusing the Solver Parameters
          6. Assigning the Solver Results to Named Scenarios
          7. Generating Reports
            1. The Sensitivity Report
            2. The Answer Report
            3. The Limits Report
    12. 6. Creating Charts
      1. 19. Basic Charting Techniques
        1. Selecting Data for Your Chart
        2. Choosing a Chart Type
        3. Changing the Chart Type
        4. Switching Rows and Columns
        5. Choosing a Chart Layout
        6. Choosing a Chart Style
        7. Moving the Chart to a Separate Chart Sheet
        8. Adding, Editing, and Removing a Chart Title
        9. Adding, Editing, and Removing a Legend
        10. Adding and Positioning Data Labels
        11. Adding a Data Table
        12. Manipulating Axes
        13. Adding Axis Titles
        14. Changing the Rotation of Chart Text
        15. Displaying Gridlines
        16. Adding Text Annotations
        17. Changing the Font or Size of Chart Text
        18. Applying Shape Styles and WordArt Styles
        19. Adding Glow and Soft Edges to Chart Markers
        20. Saving Templates to Make Chart Formats Reusable
      2. 20. Using Sparklines
        1. Creating Sparklines
          1. Creating Groups of Sparklines
          2. Expanding a Set of Sparklines
        2. Customizing Sparklines
          1. Changing the Sparkline Color and Weight
          2. Emphasizing Particular Points
          3. Customizing Axes
            1. Setting Minimum and Maximum Values for the Vertical Axis
            2. Plotting a Group of Sparklines Against a Common Vertical Axis
            3. Using a Time-Scaled Horizontal Axis
          4. Adding Text to Sparklines
        3. Removing Sparklines
      3. 21. Advanced Charting Techniques
        1. Selecting Chart Elements
        2. Repositioning Chart Elements with the Mouse
        3. Formatting Lines and Borders
        4. Formatting Areas
          1. Using Transparency to Create a Minimal Chart Display on the Worksheet
          2. Filling an Area with a Color Gradient
          3. Filling an Area with a Texture or Picture
        5. Formatting Text
        6. Working with Axes
          1. Specifying the Line Style, Color, and Weight
          2. Specifying the Position of Tick Marks and Axis Labels
          3. Changing the Numeric Format Used by Axis Labels
          4. Changing the Scale of a Value Axis
            1. Changing the Positions of Tick Marks and Gridlines
            2. Changing the Point Where Axes Intersect
            3. Reversing the Value-Axis Scale
            4. Using Logarithmic Scaling
            5. Applying a Scaling Factor
          5. Changing the Scale of a Text Category Axis
            1. Adjusting the Spacing Between Gridlines
            2. Curing Label Overlap
            3. Adjusting the Position of Category Labels
            4. Changing the Intersection of the Value Axis
            5. Switching a Category Axis from Text to Date
          6. Changing the Scale of a Date Category Axis
            1. Changing the Minimum and Maximum
            2. Changing the Major and Minor Units
            3. Changing the Base Unit
          7. Formatting a Depth (Series) Axis
        7. Working with Data Labels
          1. Labeling Individual Data Points
          2. Labeling with Ad Hoc Text
        8. Formatting Data Series and Markers
          1. Assigning a Series to a Secondary Axis
          2. Using Two or More Chart Types in the Same Chart
        9. Modifying the Data Source for Your Chart
          1. Using the Mouse to Add Data Points
          2. Modifying Data with the Select Data Source Command
            1. Plotting Noncontiguous Source Ranges
            2. Changing the Way Excel Plots Empty and Hidden Cells
        10. Using Multilevel Categories
        11. Adding Moving Averages and Other Trendlines
        12. Adding Error Bars
        13. Adding High-Low Lines and Up and Down Bars
    13. 7. Managing Databases and Tables
      1. 22. Managing Information in Tables
        1. How to Organize a Table
        2. Creating a Table
          1. Overwriting Default Headers
          2. Turning a Table Back into an Ordinary Range
          3. Naming a Table
          4. Expanding a Table
        3. Adding Totals to a Table
        4. Sorting Tables and Other Ranges
          1. Sorting on a Single Column
          2. Sorting on More Than One Column
          3. Sorting Only Part of a List
          4. Sorting by Column
          5. Sorting Cells That Contain Formulas
          6. Sorting Months, Weekdays, or Custom Lists
          7. Performing a Case-Sensitive Sort
        5. Filtering a List or Table
          1. Using Filters
            1. Determining How Many Rows Pass the Filter
            2. Removing a Filter
            3. Using Filter Criteria in More Than One Column
            4. Using a Filter to Find the Top or Bottom n Items
            5. Using a Filter to Display Blank Entries
            6. Using Filters to Select Dates
            7. Using Filters to Specify More Complex Criteria
            8. Using Custom Filters to Specify Complex Relationships
          2. Using the Advanced Filter Command
            1. Specifying a Criteria Range
            2. An Example Using Two Columns Joined by OR
          3. An Example Using Three ORs on a Column
          4. An Example Using Both OR and AND
          5. Applying Multiple Criteria to the Same Column
          6. Using Computed Criteria
          7. Extracting Filtered Rows
          8. Removing Duplicate Records
        6. Using Formulas with Tables
          1. Referencing the Total Row
          2. Explicitly Referencing the Current Row
          3. Referencing Parts of a Table
        7. Formatting Tables
          1. Using Themes to Change Style Appearance
          2. Customizing Table Styles
      2. 23. Analyzing Data with PivotTable Reports
        1. Introducing PivotTables
        2. Creating a PivotTable
        3. Rearranging PivotTable Fields
        4. Refreshing a PivotTable
        5. Changing the Numeric Format of PivotTable Data
        6. Choosing Report Layout Options
        7. Formatting a PivotTable
          1. Customizing the Display of Empty or Error Cells
          2. Merging and Centering Field Labels
          3. Hiding Outline Controls
          4. Hiding Row Labels and Column Labels
        8. Displaying Totals and Subtotals
          1. Customizing Subtotals
        9. Sorting PivotTable Fields
        10. Filtering PivotTable Fields
          1. Filtering with the Report Filter Axis
          2. Filtering with Slicers
            1. Connecting a Slicer to Multiple PivotTables
          3. Formatting Slicers
          4. Filtering in the Field List Window
        11. Changing PivotTable Calculations
          1. Using a Different Summary Function
          2. Applying Multiple Summary Functions to the Same Field
          3. Using Custom Calculations
          4. Using Calculated Fields and Items
            1. Creating a Calculated Field
            2. Creating a Calculated Item
            3. Displaying a List of Calculated Fields and Items
        12. Grouping and Ungrouping Data
          1. Creating Ad Hoc Item Groupings
          2. Grouping Items in Date or Time Ranges
        13. Displaying the Details Behind a Data Value
        14. Creating PivotCharts
      3. 24. Working with External Data
        1. Using and Reusing Data Connections
          1. Setting Refresh Options
          2. Requiring or Not Requiring a Password to Refresh
          3. Refreshing on Demand
        2. Opening an Entire Access Table in Excel
        3. Working with Data in Text Files
          1. Using the Text Import Wizard
          2. Parsing Clipboard Text
        4. Working with XML Files
          1. Creating an Ad Hoc Mapping of XML Elements to Table Columns
          2. Importing XML Data Using an Existing XML Structure
        5. Using Microsoft Query to Import Data
          1. Choosing Tables and Fields (Columns)
          2. Filtering Records
          3. Sorting Records
            1. Saving the Query or Moving to Microsoft Query
          4. Working Directly with Microsoft Query
            1. Getting to Query
            2. Adding and Removing Tables
            3. Working with Joins
            4. Adding, Removing, and Moving Fields
            5. Renaming Fields
            6. Sorting the Result Set
            7. Filtering the Result Set
            8. Performing Aggregate Calculations
            9. Creating a Parameter-Based Query
            10. Saving a Query
            11. Returning the Result Set to Excel
        6. Using a Web Query to Return Internet Data
          1. Using an Existing Web Query
          2. Creating Your Own Web Query
            1. Using the From Web Command
            2. Copying and Pasting from the Web Browser
            3. Exporting from Internet Explorer to Excel
    14. 8. Collaborating
      1. 25. Collaborating on a Network or by E-Mail
        1. Saving and Retrieving Files on Remote Computers
        2. Sharing Workbooks on a Network
          1. Using Advanced Sharing Options
          2. Tracking Changes
          3. Protecting the Change History
          4. Reviewing Changes
          5. Canceling the Shared Workbook Session
        3. Combining Changes Made to Multiple Workbooks
        4. Distributing Workbooks and Worksheets by E-Mail
          1. Sending an Entire Workbook as an E-Mail Attachment
          2. Sending a Worksheet, Chart, or Range by E-Mail
          3. Sending a Workbook for Review
        5. Controlling Document Access with Information Rights Management
          1. Installing IRM
          2. Protecting a Document with IRM
            1. Setting an Expiration Date
            2. Allowing Users to Print
            3. Allowing Users to Click the Copy Command
            4. Allowing Programmatic Access
            5. Making the Current Settings the Default
            6. Adding New Users and Modifying Permissions Settings
          3. Using a Protected Document
      2. 26. Collaborating Using the Internet
        1. Using Windows Live SkyDrive
          1. Saving Files to SkyDrive
          2. Managing SkyDrive Folders
        2. Using Microsoft Office Web Apps
          1. Issues with Web Apps
    15. 9. Automating Excel
      1. 27. Recording Macros
        1. Configuring Macro Security
        2. Using the Macro Recorder
          1. Recording with Relative References
          2. What to Do When the Macro Recorder Does Not Give You What You Expect
        3. Introducing the Visual Basic Editor
        4. Learning the Basics of Visual Basic
          1. Objects, Methods, and Properties
          2. The Object Browser
          3. Collections of Objects
          4. Manipulating an Object’s Properties Without Selecting the Object
          5. Naming Arguments to Methods
        5. Adding Code to or Editing Recorded Macros
        6. Using Subroutines in Macros
        7. Using the Personal Macro Workbook
        8. Going On from Here
      2. 28. Creating Custom Functions
        1. Creating a Simple Custom Function
        2. Using Custom Functions
        3. Understanding Custom Function Rules
        4. Using VBA Keywords in Custom Functions
        5. Documenting Macros and Custom Functions
        6. Creating Custom Functions with Optional Arguments
        7. Making Your Custom Functions Available Anywhere
      3. 29. Debugging Macros and Custom Functions
        1. Using Design-Time Tools
          1. Catching Syntax Errors
          2. Catching Misspelled Variable Names
          3. Stepping Through Code
          4. Setting Breakpoints with the Toggle Breakpoint Command
          5. Setting Conditional Breakpoints Using Debug.Assert
          6. Using the Watch Window to Monitor Variable Values and Object Properties
            1. Setting Conditional Breakpoints with the Watch Window
            2. Using Quick Watch to Monitor a Variable or Add a Watch Item
          7. Using the Immediate Window
        2. Dealing with Run-Time Errors
    16. 10. Integrating Excel with Other App ication
      1. 30. Using Hyperlinks
        1. Creating a Hyperlink in a Cell
          1. Turning Ordinary Text into a Hyperlink
          2. Linking to a Web Site or Local File
          3. Linking to a Location in the Current Document
          4. Linking to a New File
          5. Linking to an E-Mail Message
        2. Assigning a Hyperlink to a Shape, Image, or Chart
        3. Selecting a Cell or an Object Without Triggering the Hyperlink
          1. Editing or Deleting a Hyperlink
        4. Using Formulas to Create Hyperlinks
      2. 31. Linking and Embedding
        1. Embedding vs. Linking
        2. Embedding vs. Static Pasting
        3. Embedding and Linking from the Clipboard
        4. Embedding and Linking with the Object Command
        5. Managing Links
          1. Choosing Automatic or Manual Update
          2. Fixing Broken Links
        6. Linking vs. Hyperlinking
      3. 32. Using Excel Data in Word Documents
        1. Using Excel Tables in Word Documents
          1. Pasting an Excel Table from the Clipboard
          2. Using Paste Special to Control the Format of Your Table
            1. Using the Microsoft Excel Worksheet Object Format
            2. Using RTF and HTML Formats
            3. Using Unformatted Text and Unformatted Unicode Text
            4. Using Picture (Windows Metafile), Bitmap, and Picture (Enhanced Metafile)
            5. Paste-Linking an Excel Table into Word
            6. Linking with Hyperlinks
            7. Using the Object Command
        2. Using Excel Charts in Word Documents
        3. Using Excel to Supply Mail-Merge Data to Word
    17. 11. Appendixes
      1. A. Menu to Ribbon Command Reference
        1. Excel 2003 File Menu
        2. Excel 2003 Edit Menu
        3. Excel 2003 View Menu
        4. Excel 2003 Insert Menu
        5. Excel 2003 Format Menu
        6. Excel 2003 Tools Menu
        7. Excel 2003 Data Menu
        8. Excel 2003 Chart Menu
        9. Excel 2003 Window Menu
        10. Excel 2003 Help Menu
        11. Excel 2003 Standard Toolbar
        12. Excel 2003 Formatting Toolbar
      2. B. Keyboard Shortcuts
        1. Keyboard Shortcuts by Key
        2. Keyboard Shortcuts by Task
      3. C. Function Reference
        1. Alphabetical List of Excel 2010 Functions
        2. Alphabetical List of Excel 2010 Functions (continued)
    18. Index
    19. About the Authors
    20. Copyright

    Product information

    • Title: Microsoft® Excel® 2010 Inside Out
    • Author(s): Mark Dodge and Craig Stinson
    • Release date: August 2010
    • Publisher(s): Microsoft Press
    • ISBN: 9780735647824