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
