Get more out of Microsoft Excel® 2013: more productivity and better answers for greater success! Drawing on his unsurpassed Excel experience, Bill Jelen (“Mr Excel”) brings together all the intensely useful knowledge you need: insights, techniques, tips, and shortcuts you just won’t find anywhere else. Excel 2013 In Depth is the fastest, best way to master Excel 2013’s full power; get comfortable with its updated interface; and leverage its new tools for everything from formulas, charts, and functions to dashboards, data visualization, and social media integration. Start by taking a quick “tour” of Excel 2013’s most valuable new features. Then, learn how to   Build more trustworthy, error-resistant, flexible, extensible, intelligent, and understandable spreadsheets Get more productive with Excel 2013’s new Start Screen and Timelines Create formulas, charts, subtotals, and pivot tables faster with new Flash Fill and Analysis Lens Quickly apply attractive, consistent formats Master every function you’ll ever need,- including powerful new web services functions Solve real-world business intelligence analysis problems Create amazing PowerPivot data mashups that integrate information from anywhere Use Power View to generate stunningly intuitive maps, dashboards, and data visualizations Share workbooks on the Web and social networks Leverage the improved Excel Web App to create highly interactive web pages and online surveys Automate repetitive functions using Excel macros Supercharge your workbooks with new apps from the Excel App Store   Like all In Depth books, Excel 2013 In Depth delivers complete coverage with detailed solutions, and troubleshooting help for tough problems you can’t fix on your own. Whatever you intend to do with Excel 2013, this is the only book you’ll need!  

Table of Contents:
Introduction Part I Mastering the New User Interface Chapter 1 Staying Connected Using Excel 2013 Displaying Two Workbooks on Two Monitors     Understanding the Dark Side of SDI     Forcing Excel to Open in a New Instance Signing In to Excel 2013 Introducing the Excel 2013 Start Screen     Revealing the Fatal Flaw of the Start Screen     Dismissing the Start Screen with the Escape Key     Dismissing the Start Screen Permanently Using the Cloud for Storage and More     Relying On the Cloud     Linking Excel to Various Accounts     Using the Open and Save As Panels Chapter 2 Introducing Flash Fill and Analysis Lens Cleaning Data with Flash Fill     Coaching Flash Fill with a Second Example     Inserting New Characters in a Telephone Number     Using Commas Helps Flash Fill     Flash Fill Will Not Automatically Fill in Numbers     Using Formatting with Dates     Using Filter to Flash Fill a Subset of Records     Troubleshooting Flash Fill Discovering Interesting Things in Your Data Using the Analysis Lens     Color Coding Cells in the Data     Charting Your Data     Adding Statistics to the Bottom or Right Edge of Your Data     Creating a Summary Report from Your Data     Adding Tiny Charts to Each Row     I’ve Used the Analysis Lens! Now What? Chapter 3 Using the Excel Interface Using the Ribbon     Using Fly-out Menus and Galleries     Rolling Through the Ribbon Tabs     Revealing More Commands Using Dialog Launchers, Task Panes, and “More” Commands     Resizing Excel Changes the Ribbon     Activating the Developer Tab     Activating Contextual Ribbon Tabs     Finding Lost Commands on the Ribbon     Shrinking the Ribbon Using the Quick Access Toolbar     Adding Icons to the QAT     Removing Commands from the QAT     Customizing the QAT     Assigning VBA Macros to Quick Access Toolbar Buttons Using the Full-Screen File Menu     Pressing the Esc Key to Close Backstage View     Recovering Unsaved Workbooks     Clearing the Recent Workbooks List     Getting Information About the Current Workbook     Marking a Workbook as Final to Prevent Editing     Finding Hidden Content Using the Document Inspector Using Other Excel Interface Improvements     Adding White Space Around Icons Using Touch Mode     Previewing Paste Using the Paste Options Gallery     Accessing the Gallery After Performing a Paste Operation     Accessing the Paste Options Gallery from the Right-Click Menu     Accessing the Paste Options Gallery from the Paste Drop-Down Using the New Sheet Icon to Add Worksheets Navigating Through Many Worksheets Using the Controls in the Lower Left Using the Mini Toolbar to Format Selected Text     Getting the Mini Toolbar Back     Disabling the Mini Toolbar Expanding the Formula Bar Zooming In and Out on a Worksheet Using the Status Bar to Add Numbers Switching Between Normal View, Page Break Preview, and Page Layout View Modes Chapter 4 Customizing Excel Performing a Simple Ribbon Modification Adding a New Ribbon Tab Sharing Customizations with Others Questions About Ribbon Customization Introducing the Excel Options Dialog     Getting Help with a Setting     New Options in Excel 2013     Using AutoRecover Options     Controlling Image Sizes     Working with Protected View for Files Originating from the Internet     Working with Trusted Document Settings Ten Options to Consider Five Excel Oddities Chapter 5 Extending Excel with Excel Apps and Add-Ins Using Apps for Office Using Traditional Add-Ins     Charting Utilities from Jon Peltier     Loading PDF Data to Excel by Using Able2Extract       Accessing More Functions by Using MoreFunc.dll   General-Purpose Utility Suites   Utilities for Data Analysis Tasks   Chapter 6 Keyboard Shortcuts Using New Keyboard Accelerators     Selecting Icons on the Ribbon     Selecting Options from a Gallery     Navigating Within Drop-Down Lists     Backing Up One Level Through a Menu     Dealing with Keyboard Accelerator Confusion     Selecting from Legacy Dialog Boxes Using the Shortcut Keys Using My Favorite Shortcut Keys     Quickly Move Between 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