- Series
- Que
- Author
- Bill Jelen
- Publisher
- QUE Publishing
- Cover
- Softcover
- Edition
- 1
- Language
- English
- Total pages
- 1152
- Pub.-date
- January 2013
- ISBN13
- 9780789748577
- ISBN
- 0789748576
- Related Titles

ISBN | Product | Product | Price CHF | Available | |
---|---|---|---|---|---|

Excel 2013 In Depth |
9780789748577 Excel 2013 In Depth |
55.90 |

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!*

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

**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.