Contents

Introduction

I Changes in User Interface

1. The File Menu Becomes the Backstage View

Understanding “In” Versus “Out” Commands

Using Backstage View

Pressing the Esc Key to Close Backstage View

Using the Four Quick Commands in the Left Navigation

Opening Recent Files

One-Click Access to Recent Files

Recovering Unsaved Workbooks

Clearing the Recent Workbooks List

Getting Information About the Current Workbook

Correcting Special States Such as Disabled Macros and Links

Excel’s Automatic Trusting of a Document

Opening a File in the Protected View Sandbox

Marking a Workbook as Final to Prevent Editing

Finding Hidden Content Using the Document Inspector

Creating a New Workbook from a Template

Printing and Print Preview

Sharing Your Workbook Using Save & Send

Getting Updates and Help

2. The Ribbon Interface and Quick Access Toolbar

Using the Ribbon

Using Dialog Launchers and the 80/20 Rule

Using Flyout Menus and Galleries

The Ribbon Is Constantly Changing

Harnessing Contextual Ribbon Tabs

Resizing Excel Changes the Ribbon

Solving Common Ribbon Problems

You Cannot Find a Particular Command on the Ribbon

You Still Cannot Find the Command on the Ribbon

The Ribbon Takes Up Too Many Rows

You Do Not Like Where Something Is Located on the Ribbon

You Cannot See All Your Favorite Commands at Once

Using the Quick Access Toolbar

Changing the Location of the Quick Access Toolbar

Adding Favorite Commands to the Quick Access Toolbar

Knowing Which Commands Can Be on the Quick Access Toolbar

Removing Commands from the Quick Access Toolbar

Customizing the Quick Access Toolbar

Using the Excel Options to Customize the Quick Access Toolbar for All Workbooks

Customizing Icons for the Current Workbook Only

Filling Up the Quick Access Toolbar

Rearranging Icons on the Quick Access Toolbar

Resetting the Quick Access Toolbar

Assigning VBA Macros to Quick Access Toolbar Buttons

3. Using Other Excel Interface Improvements

Using Live Preview

Previewing Paste Using the Paste Options Gallery

Accessing the Gallery After Doing a Paste

Accessing the Paste Options Gallery from the Right-Click Menu

Accessing the Paste Options Gallery from the Paste Drop-Down

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

Using the New Sheet Icon to Add Worksheets

Dragging a Worksheet to a New Location

Inserting a Worksheet in the Middle of a Workbook

4. Customizing the Ribbon

Performing a Simple Ribbon Modification

Using a More Complex Ribbon Modification

Hiding/Showing Ribbon Tabs

Adding a New Ribbon Tab

Sharing Customizations with Others

Resetting Customizations

Questions About Ribbon Customization

5. 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 Excel 2003 Keyboard Accelerators

Invoking an Excel 2003 Alt Shortcut

Determining Which Commands Work in Legacy Mode

6. The Excel Options Dialog

Introducing the Excel Options Dialog

Getting Help with a Setting

Using AutoRecover Options

New Excel 2010 Options for Internationalization

New Excel 2010 Options for Performance

New Excel 2010 Options for Security

Ten Options to Consider

Five Excel Oddities

Guide to Excel Options

7. The Big Grid and File Formats

Excel Grid Limits

Why Are There Only 65,536 Rows in My Excel 2007 Spreadsheet?

Other Limits in Excel 2010

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 to Navigate

Understanding the New 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 2010 Files in Excel 2002 or 2003

Minor Loss of Fidelity

Significant Loss of Functionality

Creating Excel 2010 File Formats in Excel 2003

Opening Excel 2010 Files in Excel 2007

II Calculating with Excel

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

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

Excel in Practice: Moving the Formula ToolTip

10. Understanding Functions

Working with Functions

The Formulas Tab in Excel 2010

Finding the Function You Need

Using AutoComplete to Find Functions

Using the Function Wizard to Find Functions

Getting Help with Excel Functions

Using In-Cell ToolTips

Using the Function Arguments Dialog

Using Excel Help

Using AutoSum

Potential Problems with AutoSum

Special Tricks with AutoSum

Using the AutoSum Drop-Down

Using the New General-Purpose Functions in Excel

Like SUBTOTAL, but Better: AGGREGATE()

Added in Excel 2007: IFERROR()

Using Conditional Formulas with Multiple Conditions: SUMIFS(), AVERAGEIFS(), and COUNTIFS()

Functions with New Variations in Excel 2010

Calculating Multiple MODE Values

Calculating Workdays

Handling Ties in the RANK Function

Calculating Percentiles and Quartiles

Calculating CEILING and FLOOR for Negative Values

Functions That Have Been Renamed

Using Worksheets with Legacy Function Names

Cube Functions Introduced in Excel 2007

Using the Former ATP Functions

Function Reference Chapters

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.PRECISE, CEILING, CEILING.PRECISE, EVEN, ODD, or MROUND to Remove Decimals or Round Numbers

Using SUBTOTAL Instead of SUM with Multiple Levels of Totals

Using RAND and RANDBETWEEN to Generate Random Numbers and Data

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 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 Data 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 to Group Dates into Weeks

Alternative Calendar Systems and DAYS360

Using YEARFRAC or DATEDIF 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 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 Function to Generate Any Character

Using the CODE 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 the T and VALUE Functions

Using Functions for Non-English Character Sets

12. Using Powerful Functions: Logical, Lookup, 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 OR to Check Whether Any Conditions Are Met

Using the TRUE and FALSE Functions

Using the NOT Function to Simplify the Use of AND and OR

Using the IFERROR Function to Simplify Error Checking

Examples of Information Functions

Using the IS Functions to Test for Errors

Using the ISREF Function

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

Examples of Database Functions

Using DSUM to Conditionally Sum Records from a Database

Using the DGET Function

13. Using Financial Functions

Examples of Common Household Loan and Investment Functions

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

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 to Convert to Decimals

Using FVSCHEDULE to Calculate the Future Value for a Variable Scheduled Interest Rate

14. Using Statistical Functions

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

Case Study: Application of Regression Analysis

Using FORECAST to Calculate Prediction for Any One Data Point

Using TREND to Calculate Many Future Data Points at Once

Case Study: Forecasting Using Regression Analysis

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 CORREL to Calculate Positive or Negative Correlation

Using FISHER to Perform Hypothesis Testing on Correlations

Using SKEW and KURTOSIS

Examples of Functions for Inferential Statistics

Understanding the Language of Inferential Statistics

Using BINOM.DIST to Determine Probability

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

Using NORM.S.DIST to Calculate Probability

Using NORM.S.INV to Calculate a z Score for a Given Probability

Using STANDARDIZE to Calculate the Distance from the Mean

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 a Hypothesis

Using PERMUT to Calculate the Number of Possible Arrangements

Using the Analysis ToolPak to Perform Statistical Analysis

Installing the Analysis ToolPak in Excel 2010

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

15. Using Trig, Matrix, and Engineering Functions

A Brief Review of Trigonometry Basics

Radians Versus Degrees

Pythagoras and Right Triangles

One Side Plus 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

Using LN and EXP to Calculate Natural Logarithms

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 Pi

Using SUMPRODUCT to Sum Based on Multiple Conditions

Examples of Engineering Functions

Converting from Decimal to Hexadecimal and Back

Converting from Decimal to Octal and Back

Converting from Decimal to Binary and Back

Explaining the Two’s Complement for Negative Numbers

Converting from Binary to Hex to Octal and Back

Using CONVERT to Convert English to Metric

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)

16. Connecting Worksheets, Workbooks, and External Data

Connecting Two Worksheets

Creating Links Using Paste Options Menu

Creating Links Using 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 to XML Data

Connecting Using Microsoft Query

Managing Connections

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

18. Using Names in Excel

Use the Name Box to Define a Name for a Cell

Naming a Cell by Using the Name Dialog

Using the Name Box for Quick Navigation

Using Scope to Allow Duplicate Names in a Workbook

Using Named Ranges to Simplify Formulas

Retroactively Applying Names to Formulas

Using Names to Refer to Multiple-Cell Ranges

Dealing with Invalid Legacy Naming

Adding Many Names at Once from Existing Labels and Headings

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

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 AutoFilter Drop-Downs

III Business Intelligence

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

21. Removing Duplicates and Filtering

Filtering Records

Using a Filter

Selecting One or Multiple Items from the Filter Drop-Down

Identifying Columns With Filters

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

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

Adding a Blank Row After Each Subtotal

Add Subtotals by Two Fields

23. Using Pivot Tables to Analyze Data

Creating Your First Pivot Table

When you have your data in the correct format, creating and changing a pivot table is easy.

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 of 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

Showing Percentage of Total

Showing Running Totals and Rank

Using a Formula to Add a Field to a Pivot Table

Formatting a Pivot Table

Using the PivotTable Styles

Finding More Information on Pivot Tables

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 Flyout

Filtering Using the Date Filters

Filtering Using Value Filters

Filtering to the Top 10

Filtering Using Report Filter Fields

Arranging the Filters

Selecting Multiple Items

Filtering Using Slicers

Adding Slicers

Arranging the Slicers

Formatting the Slicers

Using the 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

Why Not Sort Using the Data Tab?

25. Mashing Up Data with PowerPivot

Benefits and Drawbacks to PowerPivot

Mega-Benefits of PowerPivot

Moderate Benefits of PowerPivot

Why Is This Free?

Benefits of the Server Version of PowerPivot

Drawbacks to Using PowerPivot

Installing 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

Slicers in PowerPivot

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 New Measures

Count Distinct Using DAX

When “Filter, Then Calculate” Doesn’t Work in DAX Measures

Mix In Those Amazing Time Intelligence Functions

Other Notes

Combination Layouts

Report Formatting

Refreshing PowerPivot Versus Refreshing Pivot Table

Getting Your Data into PowerPivot with SQL Server

Other Issues

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

27. 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 End Key to Handle a Variable Number of Rows

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

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

28. More Tips and Tricks for Excel 2010

Speeding Up Calculation by Using Multithreaded 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

29. Tour of the Best Add-Ins for Excel

Charting Utilities from Jon Peltier

Creating Dashboards by Using Speedometer Chart Creator

Add Labels to XY Charts

Loading PDF Data to Excel by Using Able2Extract

Customizing the Ribbon Using CustomizeRibbon

Accessing More Functions by Using MOREFUNC.DLL

General Purpose Utility Suites

Utilities for Data Analysis Tasks

IV Visual Presentation of Data

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

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

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

32. Using Excel Charts

Understanding the Components of a Chart

Setting Up Data for Charting

Inserting a Chart by Choosing a Chart Type

Using the Create Chart Dialog

Changing a Chart’s Type

Moving or Resizing a Chart

Choosing a Chart Layout to Further Customize the Chart Type

Customizing a Chart Using the Chart Tools Tabs

Customizing a Chart by Using the Design Tab

Changing Chart Settings Using the Layout Tab

Micromanaging Using the Format Tab

Charting Tips and Tricks

Showing Numbers of Different Scale on a Chart

Creating a Chart with One Keystroke

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

Displaying Three Variables by Using a Bubble Chart

Changing the Location of a Chart

Saving a Favorite Chart Style As a Template

Using Pivot Charts

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

34. Using SmartArt, Shapes, WordArt, and Text Boxes

Using SmartArt

Elements Common in Most SmartArt

Tour of the SmartArt Categories

Inserting SmartArt

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

35. Using Pictures and Clip Art

Using Pictures on Worksheets

Formatting with Picture Styles

Resizing and Cropping Pictures

Reducing a Picture’s File Size

Adjusting a Picture

Adding Borders

Removing the Background

Arranging Pictures

Displaying the Selection Pane

Adding Captions to Images

Inserting Screen Clippings

Using Clip Art

V Sharing

36. Printing

Printing from Backstage View

Choosing a Printer

Choosing What to Print

Changing Printer Properties

Changing Some of the Page Setup Settings

Using Print Preview Controls

Closing Backstage View

Printing Using Quick Print

Using Page Layout View

Using the Improved Headers and Footers

Adding an Automatic Header

Adding a Custom Header

Inserting a Picture in a Header

Using Different Headers and Footers in the Same Document

Scaling Headers and Footers

Using the Page Setup and Sheet Options

Adjusting Worksheet Margins

Adjusting Worksheet Orientation

Setting Worksheet Paper Size

Setting the Print Area

Adding Print Titles

Scaling Options

Printing Gridlines and Headings

Working with Page Breaks

Manually Adding Page Breaks

Manual Versus Automatic Page Breaks

Using Page Break Preview to Make Changes

Removing Manual Page Breaks

37. Excel Web App and Other Ways to Share Workbooks

Sharing Workbooks with Others

Using the Excel Web Application

Advantages of Creating a Client Version of Your Workbook

Sending a Workbook via Email

Creating a PDF from a Worksheet

Publishing to Excel Services on SharePoint

Interacting with Other Office Applications

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

Using Excel Data in a Word Mail Merge

Building a Pivot Table from Access Queries

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

Transforming Data Instead of TRIM(), PROPER(), 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

Index