Excel® 2013 In Depth

Excel® 2013 In Depth

Released January 2013
Publisher(s): Que
ISBN: 9780133259322

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

Get more out of Microsoft Excel® 2013: more productivity and better answers for greater success! Drawing on his unsurpassed Excel experience, Bill Jelen (“Mr Excel”) brings together all the intensely useful knowledge you need: insights, techniques, tips, and shortcuts you just won’t find anywhere else. Excel 2013 In Depth is the fastest, best way to master Excel 2013’s full power; get comfortable with its updated interface; and leverage its new tools for everything from formulas, charts, and functions to dashboards, data visualization, and social media integration. Start by taking a quick “tour” of Excel 2013’s most valuable new features. Then, learn how to

  • Build more trustworthy, error-resistant, flexible, extensible, intelligent, and understandable spreadsheets

  • Get more productive with Excel 2013’s new Start Screen and Timelines

  • Create formulas, charts, subtotals, and pivot tables faster with new Flash Fill and Analysis Lens

  • Quickly apply attractive, consistent formats

  • Master every function you’ll ever need,- including powerful new web services functions

  • Solve real-world business intelligence analysis problems

  • Create amazing PowerPivot data mashups that integrate information from anywhere

  • Use Power View to generate stunningly intuitive maps, dashboards, and data visualizations

  • Share workbooks on the Web and social networks

  • Leverage the improved Excel Web App to create highly interactive web pages and online surveys

  • Automate repetitive functions using Excel macros

  • Supercharge your workbooks with new apps from the Excel App Store

Like all In Depth books, Excel 2013 In Depth delivers complete coverage with detailed solutions, and troubleshooting help for tough problems you can’t fix on your own. Whatever you intend to do with Excel 2013, this is the only book you’ll need!

Table of contents

  1. Title Page
  2. Copyright Page
  3. Contents at a Glance
  4. Table of Contents
  5. About the Author
  6. Dedication
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
    1. How This Book Is Organized
    2. Conventions Used in This Book
  11. I: Mastering the New User Interface
    1. 1. Staying Connected Using Excel 2013
      1. Displaying Two Workbooks on Two Monitors
      2. Signing In to Excel 2013
      3. Introducing the Excel 2013 Start Screen
      4. Using the Cloud for Storage and More
    2. 2. Introducing Flash Fill and Quick Analysis
      1. Cleaning Data with Flash Fill
      2. Discovering Interesting Things in Your Data Using the Quick Analysis
    3. 3. Using the Excel Interface
      1. Using the Ribbon
      2. Using the Quick Access Toolbar
      3. Using the Full-Screen File Menu
      4. Using Other Excel Interface Improvements
      5. Using the New Sheet Icon to Add Worksheets
      6. Navigating Through Many Worksheets Using the Controls in the Lower Left
      7. Using the Mini Toolbar to Format Selected Text
      8. Expanding the Formula Bar
      9. Zooming In and Out on a Worksheet
      10. Using the Status Bar to Add Numbers
      11. Switching Between Normal View, Page Break Preview, and Page Layout View Modes
    4. 4. Customizing Excel
      1. Performing a Simple Ribbon Modification
      2. Adding a New Ribbon Tab
      3. Sharing Customizations with Others
      4. Questions About Ribbon Customization
      5. Introducing the Excel Options Dialog
      6. Ten Options to Consider
      7. Five Excel Oddities
    5. 5. Extending Excel with Excel Apps and Add-Ins
      1. Using Apps for Office
      2. Using Traditional Add-Ins
      3. General-Purpose Utility Suites
      4. Utilities for Data Analysis Tasks
    6. 6. Keyboard Shortcuts
      1. Using New Keyboard Accelerators
      2. Using the Shortcut Keys
      3. Using My Favorite Shortcut Keys
      4. Using Excel 2003 Keyboard Accelerators
    7. 7. The Big Grid and File Formats
      1. Excel Grid Limits
      2. Why Are There Only 65,536 Rows in My Excel 2013 Spreadsheet?
      3. Other Limits in Excel 2013
      4. Tips for Navigating the Big Grid
      5. Understanding the File Formats
      6. Version Compatibility
      7. Opening Excel 2013 Files in Excel 2002 or 2003
      8. Creating Excel 2013 File Formats in Excel 2003
      9. Opening Excel 2013 Files in Excel 2007
  12. II: Calculating with Excel
    1. 8. Understanding Formulas
      1. Getting the Most from This Chapter
      2. Introduction to Formulas
      3. Entering Your First Formula
      4. Three Methods of Entering Formulas
      5. Entering the Same Formula in Many Cells
      6. Use the Table Tool to Copy a Formula
    2. 9. Controlling Formulas
      1. Formula Operators
      2. Understanding Error Messages in Formulas
      3. Using Formulas to Join Text
      4. Copying Versus Cutting a Formula
      5. Automatically Formatting Formula Cells
      6. Using Date Math
      7. Troubleshooting Formulas
    3. 10. Understanding Functions
      1. Working with Functions
      2. Getting Help with Excel Functions
      3. Using AutoSum
    4. 11. Using Everyday Functions: Math, Date and Time, and Text Functions
      1. Examples of Math Functions
      2. Dates and Times in Excel
      3. Examples of Date and Time Functions
      4. Examples of Text Functions
    5. 12. Using Powerful Functions: Logical, Lookup, Web, and Database Functions
      1. Examples of Logical Functions
      2. Examples of Information Functions
      3. Examples of Lookup and Reference Functions
      4. Cube Functions Introduced in Excel 2007
      5. Examples of Web Functions
      6. Examples of Database Functions
    6. 13. Using Financial Functions
      1. Examples of Common Household Loan and Investment Functions
      2. Examples of Functions for Financial Professionals
      3. Examples of Depreciation Functions
      4. Functions for Investment Analysis
      5. Examples of Functions for Bond Investors
      6. Examples of Miscellaneous Financial Functions
    7. 14. Using Statistical Functions
      1. Functions That Have Been Renamed
      2. Examples of Functions for Descriptive Statistics
      3. Examples of Functions for Regression and Forecasting
      4. Examples of Functions for Inferential Statistics
      5. Using the Analysis ToolPak to Perform Statistical Analysis
    8. 15. Using Trig, Matrix, and Engineering Functions
      1. A Brief Review of Trigonometry Basics
      2. Examples of Logarithm Functions
      3. Working with Imaginary Numbers
      4. Solving Simultaneous Linear Equations with Matrix Functions
      5. Examples of Engineering Functions
      6. Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)
    9. 16. Connecting Worksheets, Workbooks, and External Data
      1. Connecting Two Worksheets
      2. Connecting to Data on a Web Page
      3. Setting Up a Connection to a Text File
      4. Setting Up a Connection to an Access Database
      5. Setting Up SQL Server, XML, OLE DB, and ODBC Connections
      6. Managing Connections
    10. 17. Using Super Formulas in Excel
      1. Using 3D Formulas to Spear Through Many Worksheets
      2. Combining Multiple Formulas into One Formula
      3. Calculating a Cell Reference in the Formula by Using the INDIRECT Function
      4. Assigning a Formula to a Name
      5. Replacing Multiple Formulas with One Array Formula
    11. 18. Using Names in Excel
      1. Advantages of Using Names
      2. Naming a Cell by Using the Name Dialog
      3. Using the Name Box for Quick Navigation
      4. Avoiding Problems by Using Worksheet-Level Scope
      5. Using Named Ranges to Simplify Formulas
      6. Managing Names
      7. Using a Name to Simplify an Absolute Reference
      8. Using a Name to Hold a Value
      9. Assigning a Formula to a Name
    12. 19. Fabulous Table Intelligence
      1. Defining Suitable Data for Excel Tables
      2. Adding a Total Row to a Table
      3. Toggling Totals
      4. Expanding a Table
      5. Adding New Formulas to Tables
      6. Selecting Only the Data in the Column
      7. Using Table Data for Charts to Ensure Stickiness
      8. Replacing Named Ranges with Table References
      9. Creating Banded Rows and Columns with Table Styles
      10. Dealing with the Filter Drop-Downs
  13. III: Business Intelligence
    1. 20. Sorting Data
      1. Introducing the Sort Dialog
      2. Using Specialized Sorting
      3. One-Click Sorting
      4. Sorting Randomly
    2. 21. Removing Duplicates and Filtering
      1. Filtering Records
      2. Sorting Filtered Results
      3. Using the Advanced Filter Command
      4. Using Remove Duplicates to Find Unique Values
    3. 22. Using Automatic Subtotals
      1. Adding Automatic Subtotals
      2. Working with the Subtotals
      3. Using Specialty Subtotal Techniques
    4. 23. Using Pivot Tables to Analyze Data
      1. Creating Your First Pivot Table
      2. Dealing with the Compact Layout
      3. Rearranging a Pivot Table
      4. Finishing Touches: Numeric Formatting and Removing Blanks
      5. Four Things You Have to Know When Using Pivot Tables
      6. Calculating and Roll-ups with Pivot Tables
      7. Formatting a Pivot Table
      8. Finding More Information on Pivot Tables
    5. 24. Using Slicers and Filtering a Pivot Table
      1. Filtering Using the Row Label Filter
      2. Filtering Using Filter Fields
      3. Filtering Using Slicers
      4. Filtering Using Timelines
      5. Filtering Oddities
      6. Sorting a Pivot Table
    6. 25. Mashing Up Data with PowerPivot
      1. Joining Multiple Tables Using the Data Model in Regular Excel 2013
      2. Benefits of Moving to PowerPivot
      3. Enabling PowerPivot
      4. Case Study: Building a PowerPivot Report
      5. Some Things Are Different
      6. Two Kinds of DAX Calculations
      7. Using DAX to Create a Calculated Field in the Pivot Table
      8. Defining KPIs with PowerPivot
      9. Using QuickExplore
      10. Other Notes
    7. 26. Creating Interactive Dashboards with Power View or GeoFlow
      1. Preparing Your Data for Power View
      2. Creating a Power View Worksheet
      3. Replicating Charts Using Multiples
      4. Showing Data on a Map
      5. Using Table or Card View with Images
      6. Animating a Scatter Chart Over Time
      7. Using Drill-Down
      8. Some Closing Tips on Power View
      9. Creating a Map in GeoFlow
    8. 27. Using What-If, Scenario Manager, Goal Seek, and Solver
      1. Using What-If
      2. Using Scenario Manager
      3. Using Goal Seek
      4. Using Solver
    9. 28. Automating Repetitive Functions Using VBA Macros
      1. Checking Security Settings Before Using Macros
      2. Recording a Macro
      3. Case Study: Macro for Formatting for a Mail Merge
      4. Everyday-Use Macro Example: Formatting an Invoice Register
      5. Understanding VBA Code—An Analogy
      6. Using Simple Variables and Object Variables
      7. Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName
      8. From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges
      9. Combination Macro Example: Creating a Report for Each Customer
    10. 29. More Tips and Tricks for Excel 2013
      1. Speeding Up Calculation
      2. Watching the Results of a Distant Cell
      3. Opening the Same Files Every Day
      4. Comparing Documents Side by Side with Synchronous Scrolling
      5. Calculating a Formula in Slow Motion
      6. Inserting a Symbol in a Cell
      7. Edit an Equation
      8. Adding a Digital Signature Line to a Workbook
      9. Protecting a Worksheet
      10. Sharing a Workbook
      11. Separating Text Based on a Delimiter
      12. Translating Text
      13. Auditing Worksheets Using Inquire
  14. IV: Visual Presentation
    1. 30. Formatting Worksheets
      1. Why Format Worksheets?
      2. Using Traditional Formatting
      3. Formatting with Styles
      4. Understanding Themes
      5. Other Formatting Techniques
      6. Copying Formats
    2. 31. Using Data Visualizations and Conditional Formatting
      1. Using Data Bars to Create In-Cell Bar Charts
      2. Using Color Scales to Highlight Extremes
      3. Using Icon Sets to Segregate Data
      4. Using the Top/Bottom Rules
      5. Using the Highlight Cells Rules
      6. Tweaking Rules with Advanced Formatting
      7. Combining Rules
      8. Clearing Conditional Formats
      9. Extending the Reach of Conditional Formats
      10. Special Considerations for Pivot Tables
    3. 32. Graphing Data Using Excel Charts
      1. Choosing from Recommended Charts
      2. Using Paintbrush Icon for Styles
      3. Deleting Extraneous Data Using the Funnel
      4. Changing Chart Options Using the Plus Icon
      5. Showing Two Orders of Magnitude Using a Combo Chart
      6. Labeling Charts
      7. Controlling the Axis Range
      8. Seeing the Relationship Between Two Variables in a Scatter Chart
      9. Summarizing Data Using the Quick Analysis Icon
      10. Saving Time with Charting Tricks
    4. 33. Using Sparklines
      1. Fitting a Chart into the Size of a Cell with Sparklines
      2. Understanding How Excel Maps Data to Sparklines
    5. 34. Using SmartArt, Shapes, WordArt, and Text Boxes
      1. Using SmartArt
      2. Using Shapes to Display Cell Contents
      3. Using WordArt for Interesting Titles and Headlines
    6. 35. Using Pictures and Clip Art
      1. Getting Your Picture into Excel
      2. Adjusting the Picture Using the Ribbon Tab
      3. Using the Format Picture Task Pane
      4. Inserting Screen Clippings
      5. Selecting and Arranging Pictures
  15. V: Sharing Information
    1. 36. Printing
      1. Printing in One Click
      2. Finding Print Settings
      3. Previewing the Printed Report
      4. Working with Page Breaks
      5. Adding Headers or Footers to the Printed Report
      6. Printing from the File Menu
      7. Choosing What to Print
      8. Using Page Layout View
      9. Exploring Other Page Setup Options
    2. 37. Excel Web App and Other Ways to Share Workbooks
      1. Viewing Your SkyDrive Workbooks from Anywhere
      2. Designing a Workbook as an Interactive Web Page
      3. Collecting Survey Data in the Excel Web App
      4. Make Any Web Table Interactive with Excel Everywhere
      5. Creating a PDF from a Worksheet
      6. Interacting with Other Office Applications
    3. 38. Saving Time Using the Easy-XL Program
      1. Downloading and Installing Easy-XL
      2. Easy-XL Works Best with Tabular Data
      3. Doing Away with VLOOKUP
      4. Using a Fuzzy Match
      5. Text to Columns on Steroids
      6. Sorting Columns Left to Right
      7. Summarizing Data
      8. Cleansing Data Without Using TRIM(), PROPER(), or CLEAN()
      9. There’s More
  16. Index

Product information

  • Title: Excel® 2013 In Depth
  • Author(s):
  • Release date: January 2013
  • Publisher(s): Que
  • ISBN: 9780133259322