 |
 |
| About MrExcel |
| Consulting Services |
| Learn Excel Resources |
| Challenge of the Month |
| MrExcel Seminars |
| MrExcel Store |
| Podcast |
| Search |
| Media |
| Contact |
| Home |
 |
|
|||
Pivot Table Data Crunching
"Summarize 60,000 rows of data in seconds!"The functionality behind pivot tables was actually invented by folks at the Lotus Advanced Technology Group in the late 1980's. Originally available only for Steve Job's NeXT computer, the technology became widely available in 1992 with the release of Improv. During Microsoft's bitter battle with Lotus for spreadsheet supremacy, the concept was added to Excel's data menu. Not many people understand how to actually make use of pivot tables. As a data analyst in 1992, Bill Jelen quickly discovered the power behind pivot tables. "It was possible to create summary reports before Pivot Tables, but you had to build complicated DSUM formulas and weave them into an obscure feature called / Data Table 2. It would take an hour and make your head spin. If I ever tried to explain the process to a spreadsheet novice, their eyes would glaze over. It was something that was simply impossible to learn. Once Pivot Tables were added to Excel, anyone who can drag a mouse can create stunning summary reports in seconds. "If you can drag a mouse, you can create a basic pivot table"Imagine that you have 60,000 rows of transactional data like this: 
Your department VP asks for a summary table showing sales by product and region. Without a pivot table, you are going to be learning CSE formulas or using the conditional sum wizard in combination with Data - Filter - Advanced Filter. But, with a pivot table, you can convert the 60,000 rows of data to this summary report with four mouse clicks and three mouse drags.
Once you have a pivot table, then one additional mouse drag allows you to transform the report to show detail by customer or any other field.
Both of the above summary reports were created in seconds instead of hours.
Click here to order the book! "Includes features rarely seen in a pivot table!"As if Pivot Tables were not powerful enough, you can also use them to create Top 10 reports, or charts for each region, or tables showing year over year growth. Learn how to use Pivot Tables with Access or OLAP data. See how to use VBA to automate the creation of charts for each product or customer. This book will allow you to stop wasting time with obscure methods (I have to read Aladin's post every time that I try to use SUMPRODUCT...). This book will enable you to do analyses that were previously time-prohibitive. Become the Excel superstar in your office with just a few clicks of the mouse. Click here to order the book! About the Authors Mike Alexander is a Microsoft Certified Application Developer (MCAD) with over 14 years experience developing business solutions with Microsoft Office, VBA, and .Net. He currently lives in Frisco, Texas, where he works as a senior program manager for a top technology firm. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips with intermediate users. Bill Jelen is the principal behind MrExcel.com. He honed his pivot table wizardry during a 12 year tenure as a financial analyst for a fast-growing public computer firm. Armed with only a spreadsheet, he learned how to turn thousands of rows of transactional data into meaningful summaries in record time. He is a regular guest on Call for Help on TechTV Canada. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the world. Click here to order the book! "Learn how to do things you never dreamt were possible."Table of Contents Introduction. 1. Pivot Table Fundamentals. 2. Creating a Basic Pivot Table. 3. Customizing Fields in a Pivot Table. 4. Formatting Your Pivot Table Report. 5. Controlling the Way You View Your Pivot Data. 6. Performing Calculations Within Your Pivot Tables. 7. Creating and Using Pivot Charts. 8. Using Disparate Data Sources for Your Pivot Table. 9. Using External Data Sources for Your Pivot Table. 10. Leveraging the Power of OLAP Cubes. 11. Enhancing Your Pivot Table Reports with Macros. 12. Using VBA to Create Pivot Tables. A. Solutions to Common Questions and Issues with Pivot Tables. Click here to order the book! "If you want to save hours per day in Microsoft Excel --- You Need This Book!"Product Details Paperback: 274 Pages ; Dimensions (in inches): 7-3/8 x 9-1/8 Publisher: QUE; ISBN 0789734354 (June 2005) Price: $34.95 "Downloadable Sample Files"See the top of page 23 for a link to all of the sample files in the book. Order Click here to order the book!
|
