Excel 2013 In Depth

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.

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