Download Powerpivot Add In For Excel 2010

Return to site

Download Powerpivot Add In For Excel 2010

Powerpivot For Excel 2010
Download Powerpivot Add In For Excel 2010 Full
Download Powerpivot Add In For Excel 2010 Free
Download Powerpivot Add In For Excel 2010 By Microsoft
May 31, 2011 - In Excel 2010, the power of pivot tables skyrocketed because of the new PowerPivot add-in from Microsoft. In this article, Bill. You need to download the version that matches your version of Excel 2010. Open a blank.
 From Microsoft: Microsoft PowerPivot for Microsoft Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the software users already know and love -- Microsoft Excel. You can transform mass quantities of data with incredible speed into meaningful information to get the answers you need in seconds. You can effortlessly share your findings with others. You can process large data sets with about the same performance as processing a few hundred rows by leveraging the PowerPivot in-memory engine and its efficient compression algorithms, use PowerPivot to directly access trusted premium and public domain data from DataMarket section of Windows Azure Marketplace, integrate data from a multitude of sources, including corporate databases, spreadsheets, reports, text files, and Internet data feeds, go beyond standard Excel expressions and use PowerPivot's Data Analysis Expressions (DAX) language to perform powerful data manipulations, and follow relationships between tables as in a database and define sophisticated calculations using familiar and intuitive expressions.
read more +
What do you need to know about free software?Home > Store
Share|
Register your product to gain access to bonus material or receive a coupon. 
By Bill Jelen
 Published Jun 3, 2010 by Que. Part of the MrExcel Library series. 
 Book Sorry, this book is no longer in print.
 eBook (Watermarked) Your Price: $22.39
List Price: $27.99
Includes EPUB, MOBI, and PDF
 This eBook includes the following formats, accessible from your Account page after purchase: 
EPUBThe open industry format known for its reflowable content and usability on supported mobile devices. 
MOBIThe eBook format compatible with the Amazon Kindle and Amazon Kindle applications. 
PDFThe popular standard, used most often with the free Adobe® Reader® software. 
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
DescriptionCopyright 2011
Dimensions: 7' x 9-1/8'
Pages: 336
Edition: 1st
 Book 
ISBN-10: 0-7897-4315-9
ISBN-13: 978-0-7897-4315-2
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
Extras Related Articles Sample Content Online Sample Chapter Sample PagesDownload the sample pages (includes Chapter 2 and Index)
Table of ContentsIntroduction .................................................................................................... 1
 'There's Row 20 Million' ................................................................................................................ 2
 'The PivotTable Field List Has Fields from Both Tables' ................................................................. 2
 Now, I Love VLOOKUPs ............................................................................................................. 3
 'How Much Will This Cost? Well, the Client Is Free' ................................................................. 4
 This Book Was Pitched Eight Hours Later ................................................................................. 4
 There Could Be Five Titles for This Book .......................................................................................... 4
 Who Are Those Other People in the Room? .................................................................................... 5
 This Book Is For The Excel People.............................................................................................. 6
 How This Book Is Organized ............................................................................................................ 7
 Conventions Used in This Book ....................................................................................................... 7
 Text Conventions ...................................................................................................................... 7
 Special Elements ....................................................................................................................... 8
 Cross References ....................................................................................................................... 8
1 Downloading and Installing PowerPivot .................................................... 9
 System Requirements ..................................................................................................................... 9
 32 Bit or 64 Bit? ........................................................................................................................ 9
 Not Excel Starter Edition ......................................................................................................... 11
 Not Excel Web Apps ................................................................................................................ 11
 Installing PowerPivot.................................................................................................................... 11
 The Many PowerPivot Tabs .................................................................................................... 11
 Ribbon Tabs in the PowerPivot Application ............................................................................ 12
 Uninstalling PowerPivot ............................................................................................................... 16
 Next Steps .............................................................................................................................. 16
2 The Promise of PowerPivot .........................................................................17
 Preparing Your Data for PowerPivot ............................................................................................. 17
 Getting Your Data into PowerPivot ............................................................................................... 18
 Decide on a Sequence for Importing ....................................................................................... 19
 Import a Text File ................................................................................................................... 19
 Add Excel Data by Copying and Pasting .................................................................................. 22
 Define Relationships ..................................................................................................................... 23
 Add Calculated Columns Using DAX .............................................................................................. 25
 Build a Pivot Table ........................................................................................................................ 26
 World-Class Data Compression ..................................................................................................... 31
 Asymmetric Reporting with PowerPivot ....................................................................................... 33
 Next Steps .............................................................................................................................. 34
3 Why Wouldn't I Build Every Future Pivot Table in PowerPivot? .....................37
 Great Reasons to Use PowerPivot ................................................................................................. 38
 Create One Pivot Table from Multiple Tables .......................................................................... 38
 Use Massive Data Volumes ..................................................................................................... 39
 Fit More Data into Memory .................................................................................................... 39
 Use Named Sets to Build Asymmetric Pivot Tables ................................................................. 39
 Join Four Pivot Tables Together Using a Single Set of Slicers .................................................. 40
 PowerPivot Slicer AutoLayout Runs Circles Around Regular Excel Slicers................................ 41
 PowerPivot Allows for Standalone Pivot Charts ...................................................................... 42
 Convert Your PowerPivot Pivot Table to Formulas.................................................................. 43
 Measures Created by DAX Run Circles Around Calculated Fields ............................................. 43
 The Downside of PowerPivot ........................................................................................................ 44
 You Lose Undo ........................................................................................................................ 44
 PowerPivot Is Not Smart Enough to Sort Jan, Feb, Mar, Apr ................................................... 44
 It's Hard to Change the Calculation in the Pivot Table ............................................................ 46
 You Cannot Create PowerPivot Pivot Tables with VBA ........................................................... 49
 You Cannot Edit a Single Cell in the PowerPivot Window....................................................... 49
 GetPivotData Is Harder to Use with PowerPivot .............................................................. 49
 Show Items with No Data Is Grayed Out ................................................................................. 51
 Calculated Fields and Calculated Items Are Grayed Out .......................................................... 52
 You Cannot Double-Click to Drill Through............................................................................... 52
 Grouping Does Not Work with PowerPivot ............................................................................. 53
 Certain On-Worksheet Typing Adjustments Do Not Work in PowerPivot ............................... 55
 Greatest Pivot Table Trick of All Time: Show Pages Does Not Work ........................................ 57
 Other Minor Annoyances ........................................................................................................ 58
 Bottom Line ................................................................................................................................. 58
 Next Steps .............................................................................................................................. 60
4 Getting Your Data into PowerPivot .............................................................61
 Getting Excel Data into PowerPivot .............................................................................................. 61
 Converting Your Data to a Table and Linking ......................................................................... 62
 Add Excel Data by Copying and Pasting .................................................................................. 67
 Adding Excel Data by Importing ............................................................................................. 76
 Importing Data from SQL Server ................................................................................................... 79
 Importing a Text File ..................................................................................................................... 80
 Importing from Atom Data Feeds ................................................................................................. 83
 Importing from Other Sources ...................................................................................................... 83
 Next Steps .............................................................................................................................. 84
5 Creating and Managing Relationships .........................................................85
 Trying to Autodetect Relationships ............................................................................................... 85
 Manually Defining a Relationship ................................................................................................. 88
 Calculating Between Tables .......................................................................................................... 89
 Defining a Difficult Relationship ................................................................................................... 91
 Unwinding a Lookup Table ..................................................................................................... 91
 Building a Concatenated Key Relationship ................................................................................... 95
 Is This Harder Than a VLOOKUP? .................................................................................................. 98
 Questions About Relationships ..................................................................................................... 98
 Next Steps ............................................................................................................................ 100
6 Using Data Sheet View .............................................................................101
 Working with Data in the PowerPivot Window .......................................................................... 101
 Applying Numeric Formatting .............................................................................................. 102
 Sorting Data in the PowerPivot Window .............................................................................. 104
 Filtering in the PowerPivot Window..................................................................................... 104
 Rearranging Columns ........................................................................................................... 106
 Hiding Columns at Two Levels .............................................................................................. 108
 Using PowerPivot Undo and Redo ........................................................................................ 109
 Deleting Columns ................................................................................................................. 109
 Using the Context Menu ....................................................................................................... 109
 Adding New Columns Using DAX Formulas................................................................................. 110
 Operators in the DAX Language ............................................................................................ 110
 Building Formulas in the PowerPivot Grid ............................................................................ 111
 DAX Function Reference.............................................................................................................. 112
 Date and Time Functions ...................................................................................................... 112
 Using YEARFRAC to Calculate Elapsed Time........................................................................ 119
 Using TIMEVALUE to Convert Text Times to Real Times ..................................................... 123
 Examples of Math and Trigonometry Functions ................................................................... 123
 Examples of Text Functions .................................................................................................. 130
 Examples of Text Functions .................................................................................................. 132
 Examples of Logical Functions .............................................................................................. 139
 Examples of Information Functions ...................................................................................... 142
 Grabbing Values from a Related Table ........................................................................................ 142
 Using One Value from a Related Table .................................................................................. 143
 Getting Multiple Values from a Related Table ...................................................................... 143
 Filtering Multiple Values from a Related Table ..................................................................... 144
 Using the Recursive Functions .................................................................................................... 145
 Using Other Functions................................................................................................................. 146
 Next Steps ............................................................................................................................ 147
7 Building Pivot Tables ...............................................................................149
 Elements of a Pivot Table............................................................................................................ 149
 Arranging Field Headings to Build a Report .......................................................................... 150
 Using the PowerPivot Field List to Create Reports ................................................................ 150
 Building a Pivot Table ................................................................................................................. 152
 A Look at the Underlying Data .............................................................................................. 152
Powerpivot For Excel 2010 Defining the Pivot Table ....................................................................................................... 153
 Using the Report Filter .......................................................................................................... 159
 Report Filters Versus Slicers .................................................................................................. 161
 Explanation of Column B ............................................................................................................ 162
 Returning the Column Labels to Sanity ................................................................................ 164
 New Trick with Column Labels.............................................................................................. 164
 Is There a Way to Permanently Sack the Compact Layout? .................................................. 165
 Two Important Rules with Pivot Tables ...................................................................................... 166
 Pivot Tables Do Not Recalculate When Underlying Data Changes ........................................ 166
 You Cannot Move or Change Part of a Pivot Table ................................................................ 167
 Working with Pivot Charts .......................................................................................................... 169
 Behind the Scenes with PowerPivot Field List and Add-In.......................................................... 172
 Next Steps ............................................................................................................................ 173
8 Cool Tricks Native to Pivot Tables ..............................................................175
 Applying Sorting Rules to Pivot Tables ....................................................................................... 175
 Presenting Customers with the Largest Sales at the Top ...................................................... 175
 Adding a Custom List to Control Sort Order .......................................................................... 179
 Showing the Top Five Customers ................................................................................................ 182
 Notes About the Top 10 Filter ............................................................................................... 183
 Changing the Calculation in the Pivot Table ............................................................................... 184
 Easiest Way to Force a Count ................................................................................................ 184
 Using Sum, Count, Min, Max, or Average ............................................................................. 185
 Changing the Show Values as Drop-Down ........................................................................... 187
 Base Fields and Base Items ................................................................................................... 188
 Pivot Table Formatting ............................................................................................................... 190
 Change the Numeric Formatting for a Field .......................................................................... 190
 Formatting Changes on the Design Tab ................................................................................ 192
 Not Enough Styles? Multiply by 20 ....................................................................................... 194
 Applying Data Visualizations and Sparklines .............................................................................. 195
Download Powerpivot Add In For Excel 2010 Full Next Steps ............................................................................................................................ 197
9 Cool Tricks New with PowerPivot ..............................................................199
 Building a Report with Two Pivot Charts .................................................................................... 200
 Chart Formatting Changes .................................................................................................... 202
 Adding Slicers and Understanding Slicer AutoLayout ................................................................. 203
 Cannot Directly Change the Size of Slicers ............................................................................ 204
 Controlling the Size of the Bounding Rectangle ................................................................... 206
 Strategy for Dealing with AutoLayout of Slicers ................................................................... 208
 Adding a Pivot Chart to an Existing Layout ................................................................................. 208
 Hooking the New Pivot Chart Up to the Existing Slicers........................................................ 210
 Moving the Pivot Table to a Back Worksheet ....................................................................... 211
 Adding a Pivot Table to an Existing Layout ................................................................................. 212
 Can the PowerPivot Layout Be Skipped Entirely? ................................................................. 213

 Next Steps ............................................................................................................................ 215
10 Using DAX for Aggregate Functions ...........................................................217
 DAX Measures Are Calculated Fields for the Values Area of a Pivot Table ................................... 218
 Five of the Six Pivot Table Drop Zones Are Filter Fields! ........................................................ 219
 DAX Measures Respect the Home Table Filters ..................................................................... 220
 Generate a Count Distinct ........................................................................................................... 220
 Using the DISTINCT Function ............................................................................................ 220
 Using COUNTROWS as a Wrapper Function .......................................................................... 221
 Entering a DAX Measure ....................................................................................................... 222
 DAX Measures Are Calculated Only on Demand .................................................................... 225
 DAX Measures Can Reference Other DAX Measures .............................................................. 225
 Using Other DAX Functions That Respect Filters ......................................................................... 226
 Denominators Frequently Need to Ignore the Filters .................................................................. 228
 DAX Calculate Function Is Like the Excel SUMIFS Function ................................................... 228
 In DAX, a Filter Might Give You More Rows Than You Started With! .................................... 230
 ALL Function Says to Ignore All Existing Filters ................................................................... 231
 CALCULATE Is So Powerful, There Is a Shortcut .................................................................. 234
 Using the FILTER Function ....................................................................................................... 234
 The Double Negative of AllExcept ......................................................................................... 237
 Other DAX Functions ................................................................................................................... 238
 Next Steps ............................................................................................................................ 240
11 Using DAX for Date Magic .........................................................................241
 Using Time Intelligence Functions .............................................................................................. 242
 Fiscal Quarters and Calendar Quarters .................................................................................. 244
 Using Period-to-Date Calculations ........................................................................................ 245
 Comparing Today's Sales to Yesterday ................................................................................. 245
 Comparing Today's Sales to One Year Ago ............................................................................ 245
 Reporting Sales for the Full Month ....................................................................................... 246
 Calculating Sales for the Previous or Next Month, Quarter, Year .......................................... 247
 Sales for the Last 30 Days ..................................................................................................... 247
 Using Date Functions for Data Reported at a Monthly Level ....................................................... 248
 Use Care with ParallelPeriod ...................................................................................... 248
 Opening and Closing Balances .............................................................................................. 249
 Skip the CALCULATE Function in Three Cases ......................................................................... 249
 Next Steps ............................................................................................................................ 250
12 Named Sets, GetPivotData, and Cube Formulas .........................................251
 Defining Territories with Named Sets ......................................................................................... 251
 Correcting the Grand Total Row in Named Sets .................................................................... 254
 Using Named Sets for Asymmetric Reporting ............................................................................. 256
 Preserving Report Formatting Using GetPivotData .............................................................. 258
 Producing a Perfectly Formatted Shell Report ...................................................................... 259
 Evaluating the Formula Built by Excel .................................................................................. 261
 Converting Live Pivot Table to Cube Formulas ............................................................................ 263
 Customizing the Formatted Report ...................................................................................... 265
 Next Steps ............................................................................................................................ 266
13 Final Formatting: Making the Report Not Look Like Excel ...........................267
 Charts Should Have Less Ink, More Information ......................................................................... 267
 Component Charts Make Great Pie Charts ............................................................................ 268
 Time Series Charts Should Be Columns or Lines .................................................................... 268
 Category Charts Make Great Bar Charts ................................................................................ 269
 Use Descriptive Titles ............................................................................................................ 269
 Single-Series Column and Bar Charts Do Not Need Legends! ................................................ 271
 Reduce the Number of Zeros on the Values Axis ................................................................... 271
 Slicers Make the On-Chart Controls Obsolete ........................................................................ 272
 Replace Pie Chart Legends with Labels ................................................................................. 273
 Gridlines, Tick Marks, Axis, and Column Widths ................................................................... 273
 Trying to Tame the Slicers........................................................................................................... 276
 Change the Slicer Color ......................................................................................................... 278
 Remove Excel Interface Elements ............................................................................................... 279
 Hide the Gridlines ................................................................................................................. 279
 Hide Other Interface Elements .............................................................................................. 279
 Hide the PowerPivot Field List .............................................................................................. 280
 Making a Report Look Like a Dashboard ..................................................................................... 280
 Change the Background Color .............................................................................................. 280
 Contrast Color and Title in Row 1.......................................................................................... 280
 Minimize the Ribbon ............................................................................................................ 281
 Micro-Adjust the Zoom Slider ............................................................................................... 281
 Add a Row of Color at the Bottom of the Dashboard ............................................................ 281
 Hide the Cell Pointer Behind a Slicer..................................................................................... 281
 Adding a Picture as a Top Banner ............................................................................................... 282
 Next Steps ............................................................................................................................ 284
14 Upgrading to PowerPivot Server ...............................................................285
 Requirements to Run the Server Version of PowerPivot ............................................................. 285
 Benefits of the PowerPivot Server .............................................................................................. 286
 How the Report Looks in the Server ............................................................................................ 287
 The Report Gallery Is Slick ........................................................................................................... 288
 Why the IT Department Will Embrace PowerPivot ..................................................................... 291
 Mistakes to Avoid When Publishing Reports to SharePoint ........................................................ 292
 Always Add Interactivity ....................................................................................................... 293
 Always Select Cell A1 Before Saving a Workbook ................................................................. 293
 Pictures Will Not Render on the Server ................................................................................. 293
 Hide All but the Main Worksheet.......................................................................................... 293
 Whatever Is Not Hidden in Excel Shows Up in SharePoint .................................................... 294
 Next Steps ............................................................................................................................ 294
Appendix A: More Resources .........................................................................295
TOC, 9780789743152, 5/14/10
More InformationDownload Powerpivot Add In For Excel 2010 FreeOther Things You Might Like Book $39.99 
 eBook (Watermarked) $31.99 
Download Powerpivot Add In For Excel 2010 By Microsoft Book $31.99 