Excel 2013 In Depth

Description

• Copyright 2013
• Dimensions: 7" x 9-1/8"
• Pages: 1248
• Edition: 1st

• eBook (Watermarked)
• ISBN-10: 0-13-325927-7
• ISBN-13: 978-0-13-325927-8

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!

 



Sample Content

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 Worksheets

    Jumping to the Bottom of Data with Ctrl+Arrow

    Selecting the Current Region with Ctrl+*

    Jumping to the Next Corner of a Selection

    Pop Open the Right-Click Menu Using Shift+F10

    Crossing Tasks Off Your List with Ctrl+5

    Date-Stamp or Time-Stamp Using Ctrl+; or Ctrl+:

    Repeating the Last Task with F4

    Adding Dollar Signs to a Reference with F4

    Finding the One Thing That Takes You Too Much Time

Using Excel 2003 Keyboard Accelerators

    Invoking an Excel 2003 Alt Shortcut

    Determining Which Commands Work in Legacy Mode

Chapter 7 The Big Grid and File Formats

Excel Grid Limits

Why Are There Only 65,536 Rows in My Excel 2013 Spreadsheet?

Other Limits in Excel 2013

Tips for Navigating the Big Grid

    Using Shortcut Keys to Move Around

    Using the End Key to Navigate

    Using the Current Range to Navigate

    Using Go To for Navigation

Understanding the File Formats

    A Brief History of File Formats

    Using the New Binary File Format: BIFF12

    Using the New XML File Formats: XLSX and XLSM

Version Compatibility

Opening Excel 2013 Files in Excel 2002 or 2003

    Minor Loss of Fidelity

    Significant Loss of Functionality

Creating Excel 2013 File Formats in Excel 2003

Opening Excel 2013 Files in Excel 2007

Part II Calculating with Excel

Chapter 8 Understanding Formulas

Getting the Most from This Chapter

Introduction to Formulas

    Formulas Versus Values

Entering Your First Formula

    Building a Formula

    The Relative Nature of Formulas

    Overriding Relative Behavior: Absolute Cell References

    Using Mixed References to Combine Features of Relative and Absolute References

    Using the F4 Key to Simplify Dollar Sign Entry

Three Methods of Entering Formulas

    Enter Formulas Using the Mouse Method

    Entering Formulas Using the Arrow Key Method

Entering the Same Formula in Many Cells

    Copying a Formula by Using Ctrl+Enter

    Copying a Formula by Dragging the Fill Handle

    Double-Click the Fill Handle to Copy a Formula

Use the Table Tool to Copy a Formula

Chapter 9 Controlling Formulas

Formula Operators

    Order of Operations

    Stacking Multiple Parentheses

Understanding Error Messages in Formulas

Using Formulas to Join Text

    Joining Text and a Number

Copying Versus Cutting a Formula

Automatically Formatting Formula Cells

Using Date Math

Troubleshooting Formulas

    Highlighting All Formula Cells

    Seeing All Formulas

    Editing a Single Formula to Show Direct Precedents

    Using Formula Auditing Arrows

    Tracing Dependents

    Using the Watch Window

    Evaluate a Formula in Slow Motion

    Evaluating Part of a Formula

Chapter 10 Understanding Functions

Working with Functions

    The Formulas Tab in Excel 2013

    Finding the Function You Need

    Using AutoComplete to Find Functions

    Using the Insert Function Dialog to Find Functions

Getting Help with Excel Functions

    Using On-Grid Tooltips

    Using the Function Arguments Dialog

    Using Excel Help

Using AutoSum

    Potential Problems with AutoSum

    Special Tricks with AutoSum

    Using AutoAverage or AutoCount

    Function Reference Chapters

Chapter 11 Using Everyday Functions: Math, Date and Time, and Text Functions  

Examples of Math Functions

    Using SUM to Add Numbers

    Using AGGREGATE to Ignore Error Cells or Filtered Rows

    Using COUNT or COUNTA to Count Numbers or Nonblank Cells

    Using ROUND, ROUNDDOWN, ROUNDUP, INT, TRUNC, FLOOR, FLOOR.MATH, CEILING, CEILING.MATH, EVEN, ODD, or MROUND to Remove Decimals or Round Numbers

    Using SUBTOTAL Instead of SUM with Multiple Levels of Totals

    Using SUBTOTAL Instead of SUM to Ignore Rows Hidden by a Filter

    Using RAND and RANDBETWEEN to Generate Random Numbers and Data

    Using =ROMAN() to Finish Movie Credits and =ARABIC() to Convert Back to Digits

    Using ABS() to Figure Out the Magnitude of ERROR  

    Using PI to Calculate Cake or Pizza Pricing

    Using =COMBIN to Figure Out Lottery Probability

    Using COMBINA to Calculate Triple-Dip Ice Cream Bowls

    Using FACT to Calculate the Permutation of a Number

    Using GCD and LCM to Perform SeventhGrade Math

    Using MULTINOMIAL to Solve a Coin Problem

    Using MOD to Find the Remainder Portion of a Division Problem

    Using QUOTIENT to Isolate the Integer Portion in a Division Problem

    Using PRODUCT to Multiply Numbers

    Using SQRT and POWER to Calculate Square Roots and Exponents

    Using SIGN to Determine the Sign of a Number

    Using COUNTIF, AVERAGEIF, and SUMIF to Conditionally Count, Average, or Sum Data

    Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()

Dates and Times in Excel

    Understanding Excel Date and Time Formats

Examples of Date and Time Functions

    Using NOW and TODAY to Calculate the Current Date and Time or Current Date

    Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to Break a Date/Time Apart

    Using DATE to Calculate a Date from Year, Month, and Day

    Using TIME to Calculate a Time

    Using DATEVALUE to Convert Text Dates to Real Dates

    Using TIMEVALUE to Convert Text Times to Real Times

    Using WEEKDAY to Group Dates by Day of the Week

    Using WEEKNUM or ISOWEEKNUM to Group Dates into Weeks

    Alternative Calendar Systems and DAYS360

    Using YEARFRAC, DATEDIF, or DAYS to Calculate Elapsed Time

    Using EDATE to Calculate Loan or Investment Maturity Dates

    Using EOMONTH to Calculate the End of the Month

    Using WORKDAY or NETWORKDAYS or Their International Equivalents to Calculate Workdays

    Using International Versions of WORKDAY or NETWORKDAYS

Examples of Text Functions

    Joining Text with the Ampersand (&) Operator

    Using LOWER, UPPER, or PROPER to Convert Text Case

    Using TRIM to Remove Trailing Spaces

    Using CLEAN to Remove Nonprintable Characters from Text

    Using the CHAR or UNICHAR Function to Generate Any Character

    Using the CODE or UNICODE Function to Learn the Character Number for Any Character

    Using LEFT, MID, or RIGHT to Split Text

    Using LEN to Find the Number of Characters in a Text Cell

    Using SEARCH or FIND to Locate Characters in a Particular Cell

    Using SUBSTITUTE and REPLACE to Replace Characters

    Using REPT to Repeat Text Multiple Times

    Using EXACT to Test Case

    Using TEXT, DOLLAR, and FIXED to Format a Number as Text

    Converting Number Punctuation Using the NUMBERVALUE Functions

Using the T and VALUE Functions

Using Functions for Non-English Character Sets

Chapter 12 Using Powerful Functions: Logical, Lookup, Web, and Database Functions

Examples of Logical Functions

    Using the IF Function to Make a Decision

    Using the AND Function to Check for Two or More Conditions

    Using the AND Function to Compare Two Lists

    Using OR or XOR to Check Whether One or More Conditions Are Met

    Using the TRUE and FALSE Functions

    Using the NOT Function to Simplify the Use of AND and OR

    Using the IFERROR or IFNA Function to Simplify Error Checking

Examples of Information Functions

    Using the IS Functions to Test for Errors

    Using the ISFORMULA Function with Conditional Formatting to Mark Formula Cells

    Using IS Functions to Test for Types of Values

    Using the SHEETS and SHEET Functions to Dynamically Build a 3-D Reference

    Using the ISREF Function to Check a Reference

    Using the N Function to Add a Comment to a Formula

    Using the NA Function to Force Charts to Not Plot Missing Data

    Using the INFO Function to Print Information About a Computer

    Using the CELL Function

    Using TYPE to Determine the Type of Cell Value

Examples of Lookup and Reference Functions

    Using the CHOOSE Function for Simple Lookups

    Using VLOOKUP with TRUE to Find a Value Based on a Range

    Using COLUMN to Assist with VLOOKUP When Filling a Wide Table

    Using HLOOKUP for Horizontal Lookup Tables

    Using the MATCH Function to Locate the Position of a Matching Value

    Using INDEX and MATCH for a Left Lookup

    Using MATCH and INDEX to Fill a Wide Table

    Performing Many Lookups with LOOKUP

    Using FORMULATEXT to Document a Worksheet

    Using Functions to Describe the Shape of a Contiguous Reference

    Using AREAS and INDEX to Describe a Range with More Than One Area

    Using Numbers with OFFSET to Describe a Range

    Using ADDRESS to Find the Address for Any Cell

    Using INDIRECT to Build and Evaluate Cell References on the Fly

    Using the HYPERLINK Function to Quickly Add Hyperlinks

    Using the TRANSPOSE Function to Formulaically Turn Data

    Using the RTD Function and COM Addins to Retrieve Real-time Data

    Using GETPIVOTDATA to Retrieve One Cell from a Pivot Table

Cube Functions Introduced in Excel 2007

Examples of Web Functions

    Using ENCODEURL to Replace Reserved Characters

    Returning XML or JSON Using the WEBSERVICE Function

    Parsing XML from the WEBSERVICE Function Using the FILTERXML Function

Examples of Database Functions

    Using DSUM to Conditionally Sum Records from a Database

    Using the DGET Function

Chapter 13 Using Financial Functions

Examples of Common Household Loan and Investment Functions

    Using RRI to Calculate the Investment Return After Many Years

    Using PDURATION to Calculate How Long It Will Take Before You Are a Millionaire

    Using PMT to Calculate the Monthly Payment on an Automobile Loan

    Using RATE to Determine an Interest Rate

    Using PV to Figure Out How Much House You Can Afford

    Using NPER to Estimate How Long a Nest Egg Will Last

    Using FV to Estimate the Future Value of a Regular Savings Plan

Examples of Functions for Financial Professionals

    Using PPMT to Calculate the Principal Payment for Any Month

    Using IPMT to Calculate the Interest Portion of a Loan Payment for Any Month

    Using CUMIPMT to Calculate Total Interest Payments During a Time Frame

    Using CUMPRINC to Calculate Total Principal Paid in Any Range of Periods

    Using EFFECT to Calculate the Effect of Compounding Period on Interest Rates

    Using NOMINAL to Convert the Effective Interest Rate to a Nominal Rate

Examples of Depreciation Functions

    Using SLN to Calculate Straight-Line Depreciation

    Using DB to Calculate Declining-Balance Depreciation

    Using DDB to Calculate Double-Declining- Balance Depreciation

    Using SYD to Calculate Sum-of-Years’- Digits Depreciation

    Using VDB to Calculate Depreciation for Any Period

Functions for Investment Analysis

    Using the NPV Function to Determine Net Present Value

    Using IRR to Calculate the Return of a Series of Cash Flows

    Using MIRR to Calculate Internal Rate of Return, Including Interest Rates

    Using XNPV to Calculate the Net Present Value When the Payments Are Not Periodic

    Using XIRR to Calculate a Return Rate When Cash Flow Dates Are Not Periodic

Examples of Functions for Bond Investors

    Using YIELD to Calculate a Bond’s Yield

    Using PRICE to Back into a Bond Price

    Using RECEIVED to Calculate Total Cash Generated from a Bond Investment

    Using INTRATE to Back into the Coupon Interest Rate

    Using DISC to Back into the Discount Rate

    Handling Bonds with an Odd Number of Days in the First or Last Period

    Using PRICEMAT and YIELDMAT to Calculate Price and Yield for ZeroCoupon Bonds

    Using PRICEDISC and YIELDDISC to Calculate Discount Bonds

    Calculating T-Bills

    Using ACCRINT or ACCINTM to Calculate Accrued Interest

    Using DURATION to Understand Price Volatility

Examples of Miscellaneous Financial Functions

    Using DOLLARDE and DOLLARFR to Convert Between Decimals and Fractions

    Using FVSCHEDULE to Calculate the Future Value for a Variable Scheduled Interest Rate

Chapter 14 Using Statistical Functions

Functions That Have Been Renamed

    Using Worksheets with Legacy Function Names

Examples of Functions for Descriptive Statistics

    Using MIN or MAX to Find the Smallest or Largest Numeric Value

    Using LARGE to Find the Top N Values in a List of Values

    Using SMALL to Sequence a List in Date Sequence

    Using MEDIAN, MODE.SNGL, MODE.MULT, and AVERAGE to Find the Central Tendency of a Data Set

    Using TRIMMEAN to Exclude Outliers from the Mean

    Using GEOMEAN to Calculate Average Growth Rate

    Using HARMEAN to Find Average Speeds

    Using AVERAGEIF or AVERAGEIFS

    Using RANK to Calculate the Position Within a List

    Using QUARTILE.INC to Break a Data Set into Quarters

    Using PERCENTILE.INC to Calculate Percentile

    Using PERCENTRANK.INC to Assign a Percentile to Every Record

    Using AVEDEV, DEVSQ, VAR.S, and STDEV.S to Calculate Dispersion

Examples of Functions for Regression and Forecasting

    Considerations When Using Regression Analysis

    Regression Function Arguments

    Functions for Simple Straight-Line Regression: SLOPE and INTERCEPT

    Using LINEST to Calculate Straight-Line Regression with Complete Statistics

    Using FORECAST to Calculate Prediction for Any One Data Point

    Using TREND to Calculate Many Future Data Points at Once

    Using LOGEST to Perform Exponential Regression

    Using GROWTH to Predict Many Data Points from an Exponential Regression

    Exponential Regression Used to Predict Future Generations

    Using PEARSON to Determine Whether a Linear Relationship Exists

    Using RSQ to Determine the Strength of a Linear Relationship

    Using STEYX to Calculate Standard Regression Error

    Using COVARIANCE.P to Determine Whether Two Variables Vary Together

    Using CORREL to Calculate Positive or Negative Correlation

    Using FISHER to Perform Hypothesis Testing on Correlations

    Using SKEW, SKEW.P, and KURTOSIS

Examples of Functions for Inferential Statistics

    Understanding the Language of Inferential Statistics

    Using BINOM.DIST to Determine Probability

    Using BINOM.DIST.RANGE to Calculate the Probability of N to N Binomial Events

    Using BINOM.INV to Cover Most of the Possible Binomial Events

    Using NEGBINOM.DIST to Calculate Probability

    Using POISSON.DIST to Predict a Number of Discrete Events Over Time

    Using FREQUENCY to Categorize Continuous Data

    Using NORM.DIST to Calculate the Probability in a Normal Distribution

    Using NORM.INV to Calculate the Value for a Certain Probability

    Functions for the Standard Normal Distribution

    Using PHI to Plot a Standard Normal Curve

    Using NORM.S.INV to Calculate a z Score for a Given Probability

    Using STANDARDIZE to Calculate the Distance from the Mean

    Using Student’s t-Distribution for Small Sample Sizes

    Using CHISQ.TEST to Perform Goodness-of-Fit Testing

    The Sum of Squares Functions

    Testing Probability on Logarithmic Distributions

    Using GAMMA.DIST and GAMMA.INV to Analyze Queuing Times

    Calculating Probability of Beta Distributions

    Using F.TEST to Measure Differences in Variability

    Other Distributions: Exponential, Hypergeometric, and Weibull

    Using Z.TEST, CONFIDENCE.NORM, and CONFIDENCE.T to Calculate Confidence Intervals

    Using Z.TEST to Accept or Reject an Hypothesis

    Using PERMUT to Calculate the Number of Possible Arrangements

    Using PERMUTATIONA to Calculate the Number of Possible Arrangements When Repeats Are Allowed

Using the Analysis ToolPak to Perform Statistical Analysis

    Installing the Analysis ToolPak in Excel 2013

    Generating Random Numbers Based on Various Distributions

    Generating a Histogram

    Generating Descriptive Statistics of a Population

    Ranking Results

    Using Regression to Predict Future Results

    Using a Moving Average to Forecast Sales

    Using Exponential Smoothing to Forecast Sales

    Using Correlation or Covariance to Calculate the Relationship Between Many Variables

    Using Sampling to Create Random Samples

    Using ANOVA to Perform Analysis of Variance Testing

    Using the F-Test to Measure Variability Between Methods

    Performing a z-Test to Determine Whether Two Samples Have Equal Means

    Performing Student’s t-Testing to Test Population Means

    Using Functions Versus the Analysis ToolPak Tools

Chapter 15 Using Trig, Matrix, and Engineering Functions

A Brief Review of Trigonometry Basics

    Radians Versus Degrees

    Pythagoras and Right Triangles

    One Side + One Angle = Trigonometry

    Using TAN to Find the Height of a Tall Building from the Ground

    Using SIN to Find the Height of a Kite in a Tree

    Using COS to Figure Out a Ladder’s Length

    Using the “Arc” Functions to Find the Measure of an Angle

    Using ATAN2 to Calculate Angles in a Circle

    Emulating Gravity Using Hyperbolic Trigonometry Functions

Examples of Logarithm Functions

    Common Logarithms on a Base-10 Scale

    Using LOG to Calculate Logarithms for Any Base

Working with Imaginary Numbers

    Using COMPLEX to Convert a and b into a Complex Number

    Using IMREAL and IMAGINARY to Break Apart Complex Numbers

    Using IMSUM to Add Complex Numbers

    Using IMSUB, IMPRODUCT, and IMDIV to Perform Basic Math on Complex Numbers

    Using IMABS to Find the Distance from the Origin to a Complex Number

    Using IMARGUMENT to Calculate the Angle to a Complex Number

    Using IMCONJUGATE to Reverse the Sign of an Imaginary Component

    Calculating Powers, Logarithms, and Trigonometry Functions with Complex Numbers

Solving Simultaneous Linear Equations with Matrix Functions

    Using MDETERM to Determine Whether a Simultaneous Equation Has a Solution

    Using SERIESSUM to Approximate a Function with a Power Series

    Using SQRTPI to Find the Square Root of a Number Multiplied by p

    Using SUMPRODUCT to Sum Based on Multiple Conditions

Examples of Engineering Functions

    Using CONVERT to Convert English to Metric

    Performing Bitwise Operations for Electrical Engineering

    Converting to Other Number Systems

    Converting from Other Number Systems to Decimal

    Converting from Binary to Hexadecimal

    Converting Using the Legacy Functions

    Explaining the Two’s Complement for Negative Numbers

    Using DELTA or GESTEP to Filter a Set of Values

    Using ERF and ERFC to Calculate the Error Function and Its Complement

    Calculating the BESSEL Functions

Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)

Chapter 16 Connecting Worksheets, Workbooks, and External Data

Connecting Two Worksheets

    Creating Links Using Paste Options Menu

    Creating Links Using the Right-Drag Menu

    Building a Link by Using the Mouse

    Links to External Workbooks Default to Absolute References

    Building a Formula by Typing

    Creating Links to Unsaved Workbooks

    Using the Links Tab on the Trust Center

    Opening Workbooks with Links to Closed Workbooks

    Dealing with Missing Linked Workbooks

    Preventing the Update Links Dialog from Appearing

Connecting to Data on a Web Page

    Setting Up a Connection to a Web Page

    Managing Properties for Web Queries

Setting Up a Connection to a Text File

Setting Up a Connection to an Access Database

Setting Up SQL Server, XML, OLE DB, and ODBC Connections

    Connecting Using Microsoft Query

Managing Connections

Chapter 17 Using Super Formulas in Excel

Using 3D Formulas to Spear Through Many Worksheets

    Referring to the Previous Worksheet

Combining Multiple Formulas into One Formula

Calculating a Cell Reference in the Formula by Using the INDIRECT Function

    Using OFFSET to Refer to a Range That Dynamically Resizes

Assigning a Formula to a Name

    Turning a Range of Formulas on Its Side

Replacing Multiple Formulas with One Array Formula

    Setting Up an Array Formula

    Understanding an Array Formula

    Coercing a Range of Dates Using an Array Formula

Chapter 18 Using Names in Excel

Advantages of Using Names

Naming a Cell by Using the Name Dialog

Using the Name Box for Quick Navigation

Avoiding Problems by Using Worksheet- Level Scope

    Defining a Worksheet-Level Name

    Referring to Worksheet-Level Names

    Understanding Phantom Names in Excel 2010 and Earlier

Using Named Ranges to Simplify Formulas

    Retroactively Applying Names to Formulas

    Using Names to Refer to Ranges

    Dealing with Invalid Legacy Naming

    Adding Many Names at Once from Existing Labels and Headings

    Using Intersection to Do a Two-Way Lookup

    Using Implicit Intersection

Managing Names

    Filtering the Name Manager Dialog

Using a Name to Simplify an Absolute Reference

Using a Name to Hold a Value

Assigning a Formula to a Name

    Using Basic Named Formulas

    Using Dynamic Named Formulas

    Using a Named Formula to Point to the Cell Above

Chapter 19 Fabulous Table Intelligence

Defining Suitable Data for Excel Tables

    Defining a Table

    Keeping Headers in View

    Freezing Worksheet Panes

    Clearing Freeze Panes

    Using the Old Version of Freeze Panes for Absolute Control

Adding a Total Row to a Table

Toggling Totals

Expanding a Table

    Adding Rows to a Table Automatically

    Manually Resizing a Table

    Adding New Columns to a Table

Adding New Formulas to Tables

    Stopping the Automatic Copying of Formulas

    Formatting the Results of a New Formula

Selecting Only the Data in the Column

    Selecting by Right-Clicking

    Selecting by Using Shortcuts

    Selecting by Using the Arrow Mouse Pointers

Using Table Data for Charts to Ensure Stickiness

Replacing Named Ranges with Table References

    Referencing an Entire Table from Outside the Table

    Referencing Table Columns from Outside a Table

    Using Structured References to Refer to Tables in Formulas

Creating Banded Rows and Columns with Table Styles

    Customizing a Table Style: Creating Double-Height Banded Rows

    Creating Banded Rows Outside a Table

Dealing with the Filter Drop-Downs

Part III Business Intelligence

Chapter 20 Sorting Data

Introducing the Sort Dialog

Using Specialized Sorting

    Sorting by Color or Icon

    Factoring Case into a Sort

    Reordering Columns with a Left-to-Right Sort

    Sorting into a Unique Sequence by Using Custom Lists

One-Click Sorting

    Sorting by Several Columns Using One- Click Sorting

Sorting Randomly

Chapter 21 Removing Duplicates and Filtering

Filtering Records

    Using a Filter

    Selecting One or Multiple Items from the Filter Drop-Down

    Identifying Which Columns Have Filters Applied

    Combining Filters

    Clearing Filters

    Refreshing Filters

    Resizing the Filter Drop-Down

    Filtering by Selection–Hard Way

    Filtering by Selection–Easy Way

    Filtering by Color or Icon

    Handling Date Filters

    Using Special Filters for Dates, Text, and Numbers

Sorting Filtered Results

    Totaling Filtered Results

    Formatting and Copying Filtered Results

Using the Advanced Filter Command

Using Remove Duplicates to Find Unique Values

    Removing Duplicates Based on Several Columns

    Handling Duplicates Other Ways

    Combining Duplicates and Adding Values

Chapter 22 Using Automatic Subtotals

Adding Automatic Subtotals

Working with the Subtotals

    Showing a One-Page Summary with Only the Subtotals

    Sorting the Collapsed Subtotal View So the Largest Customers Are on Top

    Copying Only the Subtotal Rows

    Formatting the Subtotal Rows

    Removing Subtotals

Using Specialty Subtotal Techniques

    Summing Some Columns While Counting Another Column

    Add Subtotals by Two Fields

Chapter 23 Using Pivot Tables to Analyze Data

Creating Your First Pivot Table

    Browsing Four “Recommended” Pivot Tables Using the Quick Analysis Icon

    Browsing Ten “Recommended” Pivot Tables

    Starting with a Blank Pivot Table

    Adding Fields to Your Pivot Table Using the Field List

    Changing the Pivot Table Report by Using the Field List

Dealing with the Compact Layout

Rearranging a Pivot Table

Finishing Touches: Numeric Formatting and Removing Blanks

Four Things You Have to Know When Using Pivot Tables

    Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!

    One Blank Cell in a Value Column Causes Excel to Count Instead of Sum

    If You Click Outside the Pivot Table, All the Pivot Table Tools Disappear

    You Cannot Change, Move a Part of, or Insert Cells in a Pivot Table

Calculating and Roll-ups with Pivot Tables

    Grouping Daily Dates to Months and Years

    Adding Calculations Outside the Pivot Table

    Changing the Calculation of a Field

    Showing Percentage of Total Using Show Value As Settings

    Showing Running Totals and Rank

    Using a Formula to Add a Field to a Pivot Table

Formatting a Pivot Table

Finding More Information on Pivot Tables

Chapter 24 Using Slicers and Filtering a Pivot Table

Filtering Using the Row Label Filter

    Filtering Using the Search Box

    Clearing a Filter

    Filtering Using the Check Boxes

    Filtering Using the Label Filter Fly-Out

    Filtering Using the Date Filters

    Filtering Using Value Filters

    Filtering to the Top 10

Filtering Using Filter Fields

    Arranging the Filters

    Selecting Multiple Items

Filtering Using Slicers

    Adding Slicers

    Arranging the Slicers

    Formatting the Slicers

    Using the Slicers

Filtering Using Timelines

    Adding a Timeline

    Choosing Between Timelines or Grouped Slicers

Filtering Oddities

    AutoFiltering a Pivot Table

    Applying Row Label Filters to Fields Not in the Pivot Table Report

    Replicating a Pivot Table for Every Customer

Sorting a Pivot Table

Chapter 25 Mashing Up Data with PowerPivot

Joining Multiple Tables Using the Data Model in Regular Excel 2013

    Preparing Data for Use in the Data Model

    Adding the First Table to the Data Model

    Adding the Second Table and Defining a Relationship

    Understanding the Limitations of the Data Model

Benefits of Moving to PowerPivot

    Benefits of the Server Version of PowerPivot

Enabling PowerPivot

Case Study: Building a PowerPivot Report

    Import a Text File

    Add Excel Data by Copying and Pasting

    Add Excel Data by Linking

    Define Relationships

    Add Calculated Columns Using DAX

    Build a Pivot Table

Some Things Are Different

Two Kinds of DAX Calculations

    DAX Calculations for Calculated Columns

    Using RELATED() to Base a Column Calculation on Another Table

Using DAX to Create a Calculated Field in the Pivot Table

    Count Distinct Using DAX

Defining KPIs with PowerPivot

Using QuickExplore

Other Notes

    Combination Layouts

    Report Formatting

    Refreshing the Pivot Table Refreshes PowerPivot

    Getting Your Data into PowerPivot with SQL Server

    Other Issues

Chapter 26 Creating Interactive Dashboards with Power View or GeoFlow

Preparing Your Data for Power View

Creating a Power View Worksheet

    Every New Dashboard Element Starts as a Table

    Convert the Table to a Chart

    Creating a New Element by Dragging

    Every Chart Point Is a Slicer for Every Other Element

    Adding a Real Slicer

    The Filter Pane Can Be Confusing

    Use Tile Boxes to Filter One or a Group of Charts

Replicating Charts Using Multiples

Showing Data on a Map

Using Table or Card View with Images

Animating a Scatter Chart Over Time

Using Drill-Down

Some Closing Tips on Power View

Creating a Map in GeoFlow

Chapter 27 Using What-If, Scenario Manager, Goal Seek, and Solver

Using What-If

    Creating a Two-Variable What-If Table

Using Scenario Manager

    Creating a Scenario Summary Report

    Adding Multiple Scenarios

Using Goal Seek

Using Solver

    Installing Solver

    Solving a Model Using Solver

Chapter 28 Automating Repetitive Functions Using VBA Macros

Checking Security Settings Before Using Macros

    Enabling VBA Security

Recording a Macro

Case Study: Macro for Formatting for a Mail Merge

    How Not to Record a Macro: The Default State of the Macro Recorder

    Relative References in Macro Recording

    Starting the Macro Recorder

    Running a Macro

Everyday-Use Macro Example: Formatting an Invoice Register

    Using the Ctrl+Down Arrow Key to Handle a Variable Number of Rows

    Making Sure You Find the Last Record

    Recording the Macro in a Blank Workbook

    Editing a Macro

Understanding VBA Code–An Analogy

    Comparing Object.Method to Nouns and Verbs

    Comparing Collections to Plural Nouns

    Comparing Parameters to Adverbs

    Comparing Adjectives to Properties

    Using the Analogy While Examining Recorded Code

Using Simple Variables and Object Variables

    Using R1C1-Style Formulas

    Fixing Calculation Errors in Macros

Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName

From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges

    Finding the Last Row with Data

    Looping Through All Rows

    Referring to Ranges

    Combining a Loop with FinalRow

    Making Decisions by Using Flow Control

    Putting Together the From-Scratch Example: Testing Each Record in a Loop

    A Special Case: Deleting Some Records

Combination Macro Example: Creating a Report for Each Customer

    Using the Advanced Filter for Unique Records

    Using AutoFilter

    Selecting Visible Cells Only

    Combination Macro Example: Putting It All Together

Chapter 29 More Tips and Tricks for Excel 2013

Speeding Up Calculation

Watching the Results of a Distant Cell

Opening the Same Files Every Day

Comparing Documents Side by Side with Synchronous Scrolling

Calculating a Formula in Slow Motion

Inserting a Symbol in a Cell

Edit an Equation

Adding a Digital Signature Line to a Workbook

Protecting a Worksheet

Sharing a Workbook

Separating Text Based on a Delimiter

Translating Text

Auditing Worksheets Using Inquire

    Analyzing a Workbook

    Highlighting Potential Problems Using Interactive Diagnostics

    Building Relationship Diagrams

    Comparing Two Versions of a Workbook

Part IV Visual Presentation

Chapter 30 Formatting Worksheets

Why Format Worksheets?

Using Traditional Formatting

    Changing Numeric Formats by Using the Home Tab

    Changing Numeric Formats by Using Built-in Formats in the Format Cells Dialog

    Changing Numeric Formats Using Custom Formats

    Aligning Cells

    Changing Font Size

    Changing Font Typeface

    Applying Bold, Italic, and Underline

    Using Borders

    Coloring Cells

    Adjusting Column Widths and Row Heights

    Using Merge and Center

    Rotating Text

Formatting with Styles

Understanding Themes

    Choosing a New Theme

    Creating a New Theme

Other Formatting Techniques

    Formatting Individual Characters

Changing the Default Font

    Wrapping Text in a Cell

    Justifying Text in a Range

    Adding Cell Comments

Copying Formats

    Pasting Formats

    Pasting Conditional Formats

    Using the Format Painter

    Copying Formats to a New Worksheet

Chapter 31 Using Data Visualizations and Conditional Formatting

Using Data Bars to Create In-Cell Bar Charts

    Creating Data Bars

    Customizing Data Bars

    Showing Data Bars for a Subset of Cells

Using Color Scales to Highlight Extremes

    Customizing Color Scales

Using Icon Sets to Segregate Data

    Setting Up an Icon Set

    Moving Numbers Closer to Icons

    Mixing Icons or Hiding Icons

Using the Top/Bottom Rules

    Setting Up Conditional Formatting Rules

Using the Highlight Cells Rules

    Highlighting Cells by Using Greater Than and Similar Rules

    Comparing Dates by Using Conditional Formatting

    Identifying Duplicate or Unique Values by Using Conditional Formatting

    Using Conditional Formatting for Text Containing a Value

Tweaking Rules with Advanced Formatting

Using a Formula for Rules

    Finding Cells Within Three Days of Today

    Finding Cells Containing Data from the Past 30 Days

    Highlighting Data from Specific Days of the Week

    Highlighting an Entire Row

    Highlighting Every Other Row Without Using a Table

Combining Rules

Clearing Conditional Formats

Extending the Reach of Conditional Formats

Special Considerations for Pivot Tables

Chapter 32 Graphing Data Using Excel Charts

Choosing from Recommended Charts

Using Paintbrush Icon for Styles

Deleting Extraneous Data Using the Funnel

Changing Chart Options Using the Plus Icon

Showing Two Orders of Magnitude Using a Combo Chart

    Using Ctrl+1 to Format Any Chart Element

Labeling Charts

Controlling the Axis Range

Seeing the Relationship Between Two Variables in a Scatter Chart

    Adding a Third Variable with a Bubble Chart

Plotting Two Populations on One Scatter Chart

Summarizing Data Using the Quick Analysis Icon

Saving Time with Charting Tricks

    Adding New Data to a Chart by Pasting

    Adding New Data to a Chart by Using a Table

    Adding Drop Lines to a Surface Chart

    Predicting the Future by Using a Trendline

    Creating Stock Charts

    Dealing with Small Pie Slices

    Saving a Favorite Chart Style As a Template

Chapter 33 Using Sparklines

Fitting a Chart into the Size of a Cell with Sparklines

Understanding How Excel Maps Data to Sparklines

    Creating a Group of Sparklines

    Built-in Choices for Customizing Sparklines

    Controlling Axis Values for Sparklines

    Setting Up Win/Loss Sparklines

    Showing Detail by Enlarging the Sparkline and Adding Labels

    Other Sparkline Options

Chapter 34 Using SmartArt, Shapes, WordArt, and Text Boxes

Using SmartArt

    Elements Common in Most SmartArt

    Tour of the SmartArt Categories

    Inserting SmartArt

    Changing Existing SmartArt to a New Style

Micromanaging SmartArt Elements

    Controlling SmartArt Shapes from the Text Pane

    Adding Images to SmartArt

    Special Considerations for Organizational Charts and Hierarchical SmartArt

    Using Limited SmartArt

    Deciphering the Labeled Hierarchy Layouts

Using Shapes to Display Cell Contents

    Working with Shapes

    Using the Freeform Shape to Create a Custom Shape

Using WordArt for Interesting Titles and Headlines

    Using Text Boxes to Flow Long Text Passages

Chapter 35 Using Pictures and Clip Art

Getting Your Picture into Excel

    Inserting a Picture from Your Computer

    Inserting Multiple Pictures at Once

    Inserting a Picture or Clip Art from Online

Adjusting the Picture Using the Ribbon Tab

    Resizing the Picture to Fit

    Adjusting the Brightness and Contrast

    Adding Interesting Effects Using the Picture Styles Gallery

    Applying Artistic Effects

    Removing the Background

    Reducing a Picture’s File Size

    Adding Captions to Images

Using the Format Picture Task Pane

    Rotating a Shape over a Picture

Inserting Screen Clippings

Selecting and Arranging Pictures

Part V Sharing Information

Chapter 36 Printing

Printing in One Click

Finding Print Settings

Previewing the Printed Report

    Using the Print Preview on the Print Panel

    Using Full Screen Print Preview

    Making the Report Fit On the Page

    Setting Worksheet Paper Size

    Adjusting Worksheet Orientation

    Adjusting Worksheet Margins

    Adding Print Titles

    Excluding Part of Your Worksheet from the Print Range

    Forcing More Data to Fit on a Page

Working with Page Breaks

    Manually Adding Page Breaks

    Manual Versus Automatic Page Breaks

    Using Page Break Preview to Make Changes

    Removing Manual Page Breaks

Adding Headers or Footers to the Printed Report

    Adding an Automatic Header

    Adding a Custom Header

    Inserting a Picture or a Watermark in a Header

    Using Different Headers and Footers in the Same Document

    Scaling Headers and Footers

Printing from the File Menu

    Choosing a Printer

Choosing What to Print

    Changing Printer Properties

Changing Some of the Page Setup Settings

Using Page Layout View

Exploring Other Page Setup Options

    Printing Gridlines and Headings

    Centering a Small Report on a Page

    Replacing Error Values When Printing

    Printing Comments

    Controlling the First Page Number

Chapter 37 Excel Web App and Other Ways to Share Workbooks

Viewing Your SkyDrive Workbooks from Anywhere

    Editing Excel on the iPad

    Editing Excel on the Surface RT Tablet

    Group-Editing Using the Excel Web App

    Understanding the Limitations of the Excel Web App

    Using the Excel Web App Instead of Excel Starter

Designing a Workbook as an Interactive Web Page

    Sharing a Link to Your Web Workbook

    Embedding Your Workbook in a Blog Post or Your Web Page

Collecting Survey Data in the Excel Web App

Make Any Web Table Interactive with Excel Everywhere

Creating a PDF from a Worksheet

Interacting with Other Office Applications

    Sending a Workbook via Outlook

Pasting Excel Data to Microsoft OneNote

    Using Excel Charts in PowerPoint

    Creating Tables in Excel and Pasting to Word

    Pasting Word Data to an Excel Text Box

    Creating Labels in Word from Excel Data

Chapter 38 Saving Time Using the Easy-XL Program

Downloading and Installing Easy-XL

Easy-XL Works Best with Tabular Data

Doing Away with VLOOKUP

Using a Fuzzy Match

Text to Columns on Steroids

Sorting Columns Left to Right

Summarizing Data

    Adding Statistics to the Report

    Getting Quick Statistics

Cleansing Data Without Using TRIM(), PROPER(), or CLEAN()

    Adding Text to Cells

    Filling in the Annoying Outline View

There’s More

    Deal with Fiscal Years

    Record Easy-XL Commands into VBA Macros

9780789748577   TOC   12/18/2013



Updates

Submit Errata

