Excel 2013 In Depth

Product Author Bios

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 36 books about Microsoft Excel, writes the monthly Excel column for Strategic Finance magazine, and his Excel tips appear regularly in the CFO Excel Pro Newsletter and CFO magazine. Before founding MrExcel.com, Bill Jelen spent 12 years in the trenches–working as a financial analyst for finance, marketing, accounting, and operations departments of a $500 million public company. He lives near Akron, Ohio with his wife, Mary Ellen.

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

Introduction

Part I Mastering the New User Interface

Chapter 1 Staying Connected Using Excel 2013

Displaying Two Workbooks on Two Monitors

    Understanding the Dark Side of SDI

    Forcing Excel to Open in a New Instance

Signing In to Excel 2013

Introducing the Excel 2013 Start Screen

    Revealing the Fatal Flaw of the Start Screen

    Dismissing the Start Screen with the Escape Key

    Dismissing the Start Screen Permanently

Using the Cloud for Storage and More

    Relying On the Cloud

    Linking Excel to Various Accounts

    Using the Open and Save As Panels

Chapter 2 Introducing Flash Fill and Analysis Lens

Cleaning Data with Flash Fill

    Coaching Flash Fill with a Second Example

    Inserting New Characters in a Telephone Number

    Using Commas Helps Flash Fill

    Flash Fill Will Not Automatically Fill in Numbers

    Using Formatting with Dates

    Using Filter to Flash Fill a Subset of Records

    Troubleshooting Flash Fill

Discovering Interesting Things in Your Data Using the Analysis Lens

    Color Coding Cells in the Data

    Charting Your Data

    Adding Statistics to the Bottom or Right Edge of Your Data

    Creating a Summary Report from Your Data

    Adding Tiny Charts to Each Row

    I’ve Used the Analysis Lens! Now What?

Chapter 3 Using the Excel Interface

Using the Ribbon

    Using Fly-out Menus and Galleries

    Rolling Through the Ribbon Tabs

    Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands

    Resizing Excel Changes the Ribbon

    Activating the Developer Tab

    Activating Contextual Ribbon Tabs

    Finding Lost Commands on the Ribbon

    Shrinking the Ribbon

Using the Quick Access Toolbar

    Adding Icons to the QAT

    Removing Commands from the QAT

    Customizing the QAT

    Assigning VBA Macros to Quick Access Toolbar Buttons

Using the Full-Screen File Menu

    Pressing the Esc Key to Close Backstage View

    Recovering Unsaved Workbooks

    Clearing the Recent Workbooks List

    Getting Information About the Current Workbook

    Marking a Workbook as Final to Prevent Editing

    Finding Hidden Content Using the Document Inspector

Using Other Excel Interface Improvements

    Adding White Space Around Icons Using Touch Mode

    Previewing Paste Using the Paste Options Gallery

    Accessing the Gallery After Performing a Paste Operation

    Accessing the Paste Options Gallery from the Right-Click Menu

    Accessing the Paste Options Gallery from the Paste Drop-Down

Using the New Sheet Icon to Add Worksheets

Navigating Through Many Worksheets Using the Controls in the Lower Left

Using the Mini Toolbar to Format Selected Text

    Getting the Mini Toolbar Back

    Disabling the Mini Toolbar

Expanding the Formula Bar

Zooming In and Out on a Worksheet

Using the Status Bar to Add Numbers

Switching Between Normal View, Page Break Preview, and Page Layout View Modes

Chapter 4 Customizing Excel

Performing a Simple Ribbon Modification

Adding a New Ribbon Tab

Sharing Customizations with Others

Questions About Ribbon Customization

Introducing the Excel Options Dialog

    Getting Help with a Setting

    New Options in Excel 2013

    Using AutoRecover Options

    Controlling Image Sizes

    Working with Protected View for Files Originating from the Internet

    Working with Trusted Document Settings

Ten Options to Consider

Five Excel Oddities

Chapter 5 Extending Excel with Excel Apps and Add-Ins

Using Apps for Office

Using Traditional Add-Ins

    Charting Utilities from Jon Peltier

    Loading PDF Data to Excel by Using Able2Extract  

    Accessing More Functions by Using MoreFunc.dll  

General-Purpose Utility Suites  

Utilities for Data Analysis Tasks  

Chapter 6 Keyboard Shortcuts

Using New Keyboard Accelerators

    Selecting Icons on the Ribbon

    Selecting Options from a Gallery

    Navigating Within Drop-Down Lists

    Backing Up One Level Through a Menu

    Dealing with Keyboard Accelerator Confusion

    Selecting from Legacy Dialog Boxes

Using the Shortcut Keys

Using My Favorite Shortcut Keys

    Quickly Move Between Workshe