Enhanced descriptions from Syndetics:

ANALYZE HUGE AMOUNTS OF BUSINESS DATA FASTER, MORE EASILY, AND MORE ACCURATELY!

Use Microsoft's free PowerPivot add-in for Excel 2010 to analyze immense amounts of data from any source, perform state-of-the-art business analysis far more easily, and make better decisions in less time!

Simple, step-by-step instructions walk you through installing PowerPivot, importing data, using PivotTables with PowerPivot, using super-powerful DAX functions and measures, reporting to print or SharePoint, and a whole lot more.

Whatever your Excel data analysis experience, this book will help you use PowerPivot to get the right answers, right now--without IT's help!

* Import and integrate data from spreadsheets, SQL Server, Access, Oracle, text files, Atom data feeds, and other sources

* Analyze multiple tables together, without complicated VLOOKUPs

* Format, sort, and filter data in the PowerPivot window

* Add calculated columns using new DAX functions

* Create powerful reports from PowerPivot data-and format them so executives instantly get your point

* Discover PivotTable tricks that work even better in PowerPivot

* Control multiple PivotTable elements on one worksheet, with one set of Excel 2010 Slicers

* Use DAX Measures to quickly perform tasks that were difficult or impossible with Calculated Fields

* Compare today's sales to yesterday's-or to sales from the parallel period last fiscal year.

* Use Named Sets to prepare asymmetric reports, show actuals for past months and plan for future months.

About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will

* Dramatically increase your productivity-saving you 50 hours a year, or more

* Present proven, creative strategies for solving real-world problems

* Show you how to get great results, no matter how much data you have

* Help you avoid critical mistakes that even experienced users make

Table of contents provided by Syndetics

• Introduction (p. 1)
• "There's Row 20 Million" (p. 2)
• "The PivotTable Field List Has Fields from Both Tables" (p. 2)
• Now, I Love VLOOKUPs (p. 3)
• "How Much Will This Cost? Well, the Client Is Free" (p. 4)
• This Book Was Pitched Eight Hours Later (p. 4)
• There Could Be Five Titles for This Book (p. 4)
• WhoAreThose Other People in the Room? (p. 5)
• This Book Is For The Excel People (p. 6)
• How This Book Is Organized (p. 7)
• Conventions Used in This Book (p. 7)
• Text Conventions (p. 7)
• Special Elements (p. 8)
• Cross References (p. 8)
• 1 Downloading and Installing PowerPivot
• System Requirements (p. 9)
• 32 Bit or 64 Bit? (p. 9)
• Not Excel Starter Edition (p. 11)
• Not Excel Web Apps (p. 11)
• Installing PowerPivot (p. 11)
• The Many PowerPivot Tabs (p. 11)
• Ribbon Tabs in the PowerPivot Application (p. 12)
• Uninstalling PowerPivot (p. 16)
• Next Steps (p. 16)
• 2 The Promise of PowerPivot (p. 17)
• Preparing Your Data for PowerPivot (p. 17)
• Getting Your Data into PowerPivot (p. 18)
• Decide on a Sequence for Importing (p. 19)
• Import a Text File (p. 19)
• Add Excel Data by Copying and Pasting (p. 22)
• Define Relationships (p. 23)
• Add Calculated Columns Using DAX (p. 25)
• Build a Pivot Table (p. 26)
• World-Class Data Compression (p. 31)
• Asymmetric Reporting with PowerPivot (p. 33)
• Next Steps (p. 34)
• 3 Why Wouldn't I Build Every Future Pivot Table in PowerPivot? (p. 37)
• Great Reasons to Use PowerPivot (p. 38)
• Create One Pivot Table from Multiple Tables (p. 38)
• Use Massive Data Volumes (p. 39)
• Fit More Data into Memory (p. 39)
• Use Named Sets to Build Asymmetric Pivot Tables (p. 39)
• Join Four Pivot Tables Together Using a Single Set of Slicers (p. 40)
• PowerPivot Slicer AutoLayout Runs Circles Around Regular Excel Slicers (p. 41)
• PowerPivot Allows for Standalone Pivot Charts (p. 42)
• Convert Your PowerPivot Pivot Table to Formulas (p. 43)
• Measures Created by DAX Run Circles Around Calculated Fields (p. 43)
• The Downside of PowerPivot (p. 44)
• You Lose Undo (p. 44)
• PowerPivot Is Not Smart Enough to Sort Jan, Feb, Mar, Apr (p. 44)
• It's Hard to Change the Calculation in the Pivot Table (p. 46)
• You Cannot Create PowerPivot Pivot Tables with VBA (p. 49)
• You Cannot Edit a Single Cell in the PowerPivot Window (p. 49)
• GetPivotData Is Harder to Use with PowerPivot (p. 49)
• Show Items with No Data Is Grayed Out (p. 51)
• Calculated Fields and Calculated Items Are Grayed Out (p. 52)
• You Cannot Double-Click to Drill Through (p. 52)
• Grouping Does Not Work with PowerPivot (p. 53)
• Certain On-Worksheet Typing Adjustments Do Not Work in PowerPivot (p. 55)
• Greatest Pivot Table Trick of All Time: Show Pages Does Not Work (p. 57)
• Other Minor Annoyances (p. 58)
• Bottom Line (p. 58)
• Next Steps (p. 60)
• 4 Getting Your Data into PowerPivot (p. 61)
• Getting Excel Data into PowerPivot (p. 61)
• Converting Your Data to a Table and Linking (p. 62)
• Add Excel Data by Copying and Pasting (p. 67)
• Adding Excel Data by Importing (p. 76)
• Importing Data from SQL Server (p. 79)
• Importing a Text File (p. 80)
• Importing from Atom Data Feeds (p. 83)
• Importing from Other Sources (p. 83)
• Next Steps (p. 84)
• 5 Creating and Managing Relationships (p. 85)
• Trying to Autodetect Relationships (p. 85)
• Manually Defining a Relationship (p. 88)
• Calculating Between Tables (p. 89)
• Defining a Difficult Relationship (p. 91)
• Unwinding a Lookup Table (p. 91)
• Building a Concatenated Key Relationship (p. 95)
• Is This Harder Than a VLOOKUP? (p. 98)
• Questions About Relationships (p. 98)
• Next Steps (p. 100)
• 6 Using Data Sheet View (p. 101)
• Working with Data in the PowerPivot Window (p. 101)
• Applying Numeric Formatting (p. 102)
• Sorting Data in the PowerPivot Window (p. 104)
• Filtering in the PowerPivot Window (p. 104)
• Rearranging Columns (p. 106)
• Hiding Columns at Two Levels (p. 108)
• Using PowerPivot Undo and Redo (p. 109)
• Deleting Columns (p. 109)
• Using the Context Menu (p. 109)
• Adding New Columns Using DAX Formulas (p. 110)
• Operators in the DAX Language (p. 110)
• Building Formulas in the PowerPivot Grid (p. 111)
• DAX Function Reference (p. 112)
• Date and Time Functions (p. 112)
• Using YEARFRAC to Calculate Elapsed Time (p. 119)
• Using TIMEVALUE to Convert Text Times to Real Times (p. 123)
• Examples of Math and Trigonometry Functions (p. 123)
• Examples of Text Functions (p. 130)
• Examples of Text Functions (p. 132)
• Examples of Logical Functions (p. 139)
• Examples of Information Functions (p. 142)
• Grabbing Values from a Related Table (p. 142)
• Using One Value from a Related Table (p. 143)
• Getting Multiple Values from a Related Table (p. 143)
• Filtering Multiple Values from a Related Table (p. 144)
• Using the Recursive Functions (p. 145)
• Using Other Functions (p. 146)
• Next Steps (p. 147)
• 7 Building Pivot Tables (p. 149)
• Elements of a Pivot Table (p. 149)
• Arranging Field Headings to Build a Report (p. 150)
• Using the PowerPivot Field List to Create Reports (p. 150)
• Building a Pivot Table (p. 152)
• A Look at the Underlying Data (p. 152)
• Defining the Pivot Table (p. 153)
• Using the Report Filter (p. 159)
• Report Filters Versus Slicers (p. 161)
• Explanation of Column B (p. 162)
• Returning the Column Labels to Sanity (p. 164)
• New Trick with Column Labels (p. 164)
• Is There a Way to Permanently Sack the Compact Layout? (p. 165)
• Two Important Rules with Pivot Tables (p. 166)
• Pivot Tables Do Not Recalculate When Underlying Data Changes (p. 166)
• You Cannot Move or Change Part of a Pivot Table (p. 167)
• Working with Pivot Charts (p. 169)
• Behind the Scenes with PowerPivot Field List and Add-In (p. 172)
• Next Steps (p. 173)
• 8 Cool Tricks Native to Pivot Tables (p. 175)
• Applying Sorting Rules to Pivot Tables (p. 175)
• Presenting Customers with the Largest Sales at the Top (p. 175)
• Adding a Custom List to Control Sort Order (p. 179)
• Showing the Top Five Customers (p. 182)
• Notes About the Top 10 Filter (p. 183)
• Changing the Calculation in the Pivot Table (p. 184)
• Easiest Way to Force a Count (p. 184)
• Using Sum, Count, Min, Max, or Average (p. 185)
• Changing the Show Values as Drop-Down (p. 187)
• Base Fields and Base Items (p. 188)
• Pivot Table Formatting (p. 190)
• Change the Numeric Formatting for a Field (p. 190)
• Formatting Changes on the Design Tab (p. 192)
• Not Enough Styles? Multiply by 20 (p. 194)
• Applying Data Visualizations and Sparklines (p. 195)
• Next Steps (p. 197)
• 9 Cool Tricks New with PowerPivot (p. 199)
• Building a Report with Two Pivot Charts (p. 200)
• Chart Formatting Changes (p. 202)
• Adding Slicers and Understanding Slicer AutoLayout (p. 203)
• Cannot Directly Change the Size of Slicers (p. 204)
• Controlling the Size of the Bounding Rectangle (p. 206)
• Strategy for Dealing with AutoLayout of Slicers (p. 208)
• Adding a Pivot Chart to an Existing Layout (p. 208)
• Hooking the New Pivot Chart Up to the Existing Slicers (p. 210)
• Moving the Pivot Table to a Back Worksheet (p. 211)
• Adding a Pivot Table to an Existing Layout (p. 212)
• Can the PowerPivot Layout Be Skipped Entirely? (p. 213)
• Next Steps (p. 215)
• 10 Using DAX for Aggregate Functions (p. 217)
• DAX Measures Are Calculated Fields for the Values Area of a Pivot Table (p. 218)
• Five of the Six Pivot Table Drop Zones Are Filter Fields! (p. 219)
• DAX Measures Respect the Home Table Filters (p. 220)
• Generate a Count Distinct (p. 220)
• Using the DISTINCT Function (p. 220)
• Using COUNTROWS as a Wrapper Function (p. 221)
• Entering a DAX Measure (p. 222)
• DAX Measures Are Calculated Only on Demand (p. 225)
• DAX Measures Can Reference Other DAX Measures (p. 225)
• Using Other DAX Functions That Respect Filters (p. 226)
• Denominators Frequently Need to Ignore the Filters (p. 228)
• DAX Calculate Function Is Like the Excel SUMIFS Function (p. 228)
• In DAX, a Filter Might Give You More Rows Than You Started With! (p. 230)
• ALL Function Says to Ignore All Existing Filters (p. 231)
• CALCULATE Is So Powerful, There Is a Shortcut (p. 234)
• Using the FILTER Function (p. 234)
• The Double Negative of AllExcept (p. 237)
• Other DAX Functions (p. 238)
• Next Steps (p. 240)
• 11 Using DAX for Date Magic (p. 241)
• Using Time Intelligence Functions (p. 242)
• Fiscal Quarters and Calendar Quarters (p. 244)
• Using Period-to-Date Calculations (p. 245)
• Comparing Today's Sales to Yesterday (p. 245)
• Comparing Today's Sales to One Year Ago (p. 245)
• Reporting Sales for the Full Month (p. 246)
• Calculating Sales for the Previous or Next Month, Quarter, Year (p. 247)
• Sales for the Last 30 Days (p. 247)
• Using Date Functions for Data Reported at a Monthly Level (p. 248)
• Use Care with ParallelPeriod (p. 248)
• Opening and Closing Balances (p. 249)
• Skip the CALCULATE Function in Three Cases (p. 249)
• Next Steps (p. 250)
• 12 Named Sets, GetPivotData, and Cube Formulas (p. 251)
• Defining Territories with Named Sets (p. 251)
• Correcting the Grand Total Row in Named Sets (p. 254)
• Using Named Sets for Asymmetric Reporting (p. 256)
• Preserving Report Formatting Using GetPivotData (p. 258)
• Producing a Perfectly Formatted Shell Report (p. 259)
• Evaluating the Formula Built by Excel (p. 261)
• Converting Live Pivot Table to Cube Formulas (p. 263)
• Customizing the Formatted Report (p. 265)
• Next Steps (p. 266)
• 13 Final Formatting: Making the Report Not Look Like Excel (p. 267)
• Charts Should Have Less Ink, More Information (p. 267)
• Component Charts Make Great Pie Charts (p. 268)
• Time Series Charts Should Be Columns or Lines (p. 268)
• Category Charts Make Great Bar Charts (p. 269)
• Use Descriptive Titles (p. 269)
• Single-Series Column and Bar Charts Do Not Need Legends! (p. 271)
• Reduce the Number of Zeros on the Values Axis (p. 271)
• Slicers Make the On-Chart Controls Obsolete (p. 272)
• Replace Pie Chart Legends with Labels (p. 273)
• Gridlines, Tick Marks, Axis, and Column Widths (p. 273)
• Trying to Tame the Slicers (p. 276)
• Change the Slicer Color (p. 278)
• Remove Excel Interface Elements (p. 279)
• Hide the Gridlines (p. 279)
• Hide Other Interface Elements (p. 279)
• Hide the PowerPivot Field List (p. 280)
• Making a Report Look Like a Dashboard (p. 280)
• Change the Background Color (p. 280)
• Contrast Color and Title in Row 1 (p. 280)
• Minimize the Ribbon (p. 281)
• Micro-Adjust the Zoom Slider (p. 281)
• Add a Row of Color at the Bottom of the Dashboard (p. 281)
• Hide the Cell Pointer Behind a Slicer (p. 281)
• Adding a Picture as a Top Banner (p. 282)
• Next Steps (p. 284)
• 14 Upgrading to PowerPivot Server (p. 285)
• Requirements to Run the Server Version of PowerPivot (p. 285)
• Benefits of the PowerPivot Server (p. 286)
• How the Report Looks in the Server (p. 287)
• The Report Gallery Is Slick (p. 288)
• Why the IT Department Will Embrace PowerPivot (p. 291)
• Mistakes to Avoid When Publishing Reports to SharePoint (p. 292)
• Always Add Interactivity (p. 293)
• Always Select Cell A1 Before Saving a Workbook (p. 293)
• Pictures Will Not Render on the Server (p. 293)
• Hide All but the Main Worksheet (p. 293)
• Whatever Is Not Hidden in Excel Shows Up in SharePoint (p. 294)
• Next Steps (p. 294)
• Appendix A More Resources (p. 295)
• TOC, 9780789743152, 5/14/10

Author notes provided by Syndetics

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,200 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 30 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine. You will most frequently find Bill taking his show on the road, doing half-day Power Excel seminars wherever he can find a room full of accountants or Excellers. Before founding MrExcel.com, 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 and his sons, Josh and Zeke.