Excel 2013 in Depth: An Unofficial Guide (In Depth)De (autor) Bill Jelen
en Limba Engleză Carte Paperback – 24 Jan 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.
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 Seventh Grade 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 Add ins 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 Zero Coupon 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 Apply