Ebook Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen
By reviewing this e-book Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen, you will obtain the ideal point to obtain. The new point that you don't should invest over cash to get to is by doing it on your own. So, exactly what should you do now? See the link web page and download guide Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen You could get this Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen by on the internet. It's so simple, isn't really it? Nowadays, modern technology truly sustains you tasks, this online publication Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen, is too.
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen
Ebook Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen. Join with us to be participant right here. This is the site that will certainly give you ease of searching book Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen to check out. This is not as the other site; the books will certainly be in the types of soft documents. What benefits of you to be member of this site? Get hundred compilations of book connect to download and install and also obtain constantly updated book every day. As one of the books we will certainly provide to you currently is the Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen that has an extremely pleased idea.
If you desire truly obtain the book Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen to refer currently, you need to follow this page consistently. Why? Keep in mind that you need the Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen source that will offer you best assumption, don't you? By seeing this site, you have started to make new deal to always be updated. It is the first thing you can begin to get all gain from being in an internet site with this Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen and various other compilations.
From now, finding the finished site that offers the completed publications will be many, however we are the relied on website to see. Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen with easy web link, easy download, and also finished book collections become our better services to get. You can find and also make use of the benefits of picking this Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen as everything you do. Life is consistently establishing and also you need some new book Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen to be recommendation constantly.
If you still require a lot more publications Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen as recommendations, going to browse the title as well as motif in this site is offered. You will locate even more lots books Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen in numerous self-controls. You could additionally as quickly as possible to check out guide that is currently downloaded and install. Open it and save Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen in your disk or gizmo. It will certainly reduce you any place you require guide soft file to read. This Pivot Table Data Crunching (Adobe Reader) (Business Solutions), By Michael Alexander, Bill Jelen soft data to review can be referral for every person to boost the skill as well as capacity.
Become a savvy Microsoft Excel user. Pivot tables are a great feature in Excel that help you organize and analyze data, but not many Excel users know how to use pivot tables. Pivot Table Data Crunching offers a comprehensive review of all the functionalities of Pivot Tables from author Bill Jelen, otherwise known as Mr. Excel from www.mrexcel.com, and Michael Alexander, a Microsoft Certified Application Developer. The authors' practical scenarios and real-world advice demonstrate the benefits of Pivot Tables and how to avoid the common pitfalls of every day data crunching. Each solution presented in the book can be accomplished with resources available in the Excel interface, making Pivot Table Data Crunching a beneficial resource for all levels of Excel users.
- Sales Rank: #1391906 in eBooks
- Published on: 2001-06-20
- Released on: 2001-06-20
- Format: Kindle eBook
Review
Excel users may already be aware of the power of pivot tables, which allow summarization of 50,000 rows of data with one click of the mouse – but many user's guides don't begin to touch upon its powerful features. PIVOT TABLE DATA CRUNCHING provides a tutorial which promises users and prior Excel fans the ability to get up to speed quickly, using real-world examples such as how to highlight productivity channels and produce meaningful reports. An excellent business guide.
--California Bookwatch, 10/1/06
About the Author
Bill Jelen is the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts more than 12 million page views annually. Prior to founding MrExcel.com, Bill spent 12 years "in the trenches," working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. His duties included turning large amounts of mainframe data into meaningful reports. Working initially with Lotus 1-2-3 and then Excel, Bill honed techniques to take massive amounts of data and produce meaningful reports in record time. Bill is the author of seven books on Microsoft Excel.
Michael Alexander is a Microsoft Certified Application Developer (MCAD) with more than 13 years' experience consulting and developing office solutions. Michael started his career in consulting and development at the White House Communications Agency in Washington D.C., later parleying his experience with VBA and VB into a successful career in the private sector, developing middleware and reporting solutions for a wide variety of industries. He currently lives in Plano, Texas, where he heads an analytical services group for a $700 million company. In his spare time he runs a free tutorial site, http://www.datapigtechnologies.com, where he shares basic Access and Excel tips with intermediate users.
© Copyright Pearson Education. All rights reserved.
Excerpt. © Reprinted by permission. All rights reserved.
INTRODUCTIONINTRODUCTIONIn this introduction
What You Will Learn from This Book
Skills Required to Use This Book
The Invention of the Pivot Table
Conventions Used in This Book
Pivot tables are the single most powerful feature in all of Excel. They came along during the 90s when Microsoft and Lotus were locked in a bitter battle for dominance of the spreadsheet market. The race to continually add enhanced features to their respective products during the mid-90s led to many incredible features, but none as powerful as the pivot table.
With a pivot table, you can take 65,000 rows of transactional data and transform it into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables allow you to change your analysis on the fly by simply dragging fields from one area of a report to another.
There is simply no other tool in Excel that gives you the flexibility and analytical power that pivot tables can give you.
What You Will Learn from This Book
It is widely agreed upon that close to 50% of Excel users leave 80% of Excel untouched. That is to say that most users don't tap into the full potential of Excel's built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for more than 10 years now, they remain one of the most underutilized tools in the entire Microsoft Office Suite. If you have picked up this book, you are savvy enough to have heard of pivot tables or even have used them on occasion. You have a sense that there is power in pivot tables that you are not using and you want to learn how to leverage that power to quickly increase your productivity.
Within the first two chapters, you will be able to create basic pivot tables, increase your productivity, and produce reports in minutes instead of hours. Within the first seven chapters, you will be able to output complex pivot reports with drill-down capabilities accompanying charts. By the end of the book, you will be able to build a dynamic pivot table reporting system.
Skills Required to Use This Book
We have created a reference that is comprehensive enough for hardcore analysts, yet relevant to casual users of Excel. The bulk of the book will cover how to use pivot tables in the Excel user interface. The final chapter includes information on how to create pivot tables in Excel's powerful VBA macro language. This means that any user who has a firm grasp of the basics (preparing data, copying, pasting, entering simple formulas) should have no problem understanding the concepts in this book.
Life Before Pivot Tables
Imagine that it is 1992. You are using Lotus 1-2-3 or Excel 4. You have 40,000 rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and model.
Figure I.1
As a financial analyst in 1992, your job is to produce a summary from this 40,000 row dataset.
In 1992, this was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you would need to take:
You need to get a list of the unique regions in the dataset. Use the Advanced Filter command with Unique Records Only (see Figure I.2) to extract a list of the unique regions.
Figure I.2
Even today, the Advanced Filter command is not a lot of fun to use.
You need to get a list of the unique models in the dataset. Do a second Advanced Filter with Unique Records Only to extract a list of the unique models.
You need to turn the list of models sideways so that it runs across the columns. Copy the list of unique models. Then, do a Edit, Paste Special, Transpose to arrange the models as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.
Figure I.3
After a second Advanced Filter command and Edit, Paste Special, Transpose, you have this skeleton of the final report. There is still a long way to go.
The DSUM function could be used to total a column based on one criteria, but not based on two criteria. Therefore, you need to abandon typical functions and instead rely on an array formula. Before entering the array formula, set up two fields above the report to hold a sample region and a sample model.
In the corner cell of the report, build an array formula to test whether the region column is North and the Model column is 4055T, and if so, add up the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter or else the formula will not work. The formula is shown in the formula bar in Figure I.4.
Tip - For a complete explanation of two-condition sums using array formulas, see http://www.MrExcel.com/tip031.shtml.
Figure I.4
With the array formula in the corner of the report, you are ready to use the not-so-intuitive Data Table 2 command.
You know you're a hardcore data analyst if you can still imagine hitting the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. The Table command on the Data menu will allow you to set up a table.
Figure I.5
The Data Table command replicates the formula in the top-left corner of the table, but replaces two references in the formula with the headings at the top and left of the report.
Finally, after using two advanced filters, a Paste Special, writing the hardest formula in the world, and then using the Data Table command, you have the result your manager is looking for, as shown in Figure I.6. If you could pull this analysis off in 10 minutes, you were doing an amazing job.
Figure I.6
After 10 minutes displaying knowledge of obscure spreadsheet commands, you have produced the needed report.
Now, if your manager takes a look at the report and asks you to add Market to the analysis, you are nearly back at square one and are looking at an additional 15 minutes to produce the new report.
The Invention of the Pivot Table
The concept that led to today's pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if one could build a tool that could recognize these patterns, then one could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.
Throughout 1987, Lotus demoed its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993.
Pito Salas, inventor of the pivot table concept, is always working on cutting-edge products at http://www.salas.com.
The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv, rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries.
Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the ability to add calculated fields. Excel 97 also opened up the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.
Life After Pivot Tables
You have 40,000 rows of transactional data, as discussed in the previous case study. Your manager asks you to prepare a summary report showing revenue by Region and Model. Luckily, you have pivot tables at your disposal. Here are the steps you would follow today:
Select a single cell in your dataset. Choose PivotTable Report from the Data menu. Click Finish. You are given a blank pivot table, as shown in Figure I.7.
Figure I.7
After three mouse clicks, you have a blank pivot table report. Three more mouse clicks to go.
From the pivot table field list, drag Region and drop it where it reads "Drop Row Fields Here." Drag the Model field where it reads "Drop Column Fields Here." Drag the Revenue field where it reads "Drop Data Items Here." After a total of six mouse clicks, you have the required report, as shown in Figure I.8.
Figure I.8
Drag three headings to the report, and Excel calculates your report.
If you are racing, you can actually create the report shown in Figure I.8 in exactly 10 seconds. This is an amazing accomplishment. Realistically, it would take you about 50 seconds at normal speed to create the report. If you are a spreadsheet wizard and are instead following the steps in the previous case study, the non–pivot table solution would take you at least 12 times longer.
Further, when your manager comes back with the request to add Market to the analysis, it takes just seconds to drag the Market field from the field list and drop it on the report, as shown in Figure I.9.
Figure I.9
Creating a new report with the Market field is as simple as dragging the field to the report.
Conventions Used in This Book
This book contains the following special elements.
Note - Notes provide additional information outside the main thread of the chapter discussion that might still be useful for you to know.
Tip - Tips provide you with quick workarounds and timesaving techniques to help you do your work more efficiently.
Caution - Cautions warn you about potential pitfalls you might encounter. Pay attention to these, because they alert you to problems that otherwise could cause you hours of frustration.
Case Study - Case studies provide a real-world look at topics previously introduced in the chapter.
© Copyright Pearson Education. All rights reserved.
Most helpful customer reviews
43 of 45 people found the following review helpful.
Not really for advanced users
By BlazerHorn
I was encouraged by other reviewer's comments that this book was useful to beginners AND experts, but I disagree. I consider myself a fairly advanced user/macro developer, and was hoping this book would help me take my Pivot Table skills to another level. The book is laid out well - and DOES cover its topics thoroughly - but it is really more of a book for intermediate excel users who may have little to no experience with Pivots.
2 of 2 people found the following review helpful.
Thorough, comprehensive, and well done
By Jonathan Boyce
Weighing in a just under a pound, this book is worth it's weight in gold (currently trading at around $800 an ounce). Those new to pivot tables and those wanting a comprehensive understanding of the power of PTs (and the logic behind them) would do well to buy this book. Laid out in 11 chapters (the 12th is devoted to writing VB code, which is really an ancillary topic) it takes you from scratch: from an introduction to the origin of PTs to creating charts from your PTs to customizing PTs with macros.
Each chapter has a case study (all downloadable with ease from the website; no CD with this book), which nicely gives you a hands-on application of what you've just read. I found them all very useful and easy to use.
Those who want a basic mastery of PTs can read just the first seven chapers (about 125 pages), which took me 30 hours (including downloading the necessary work files from the book's website). Finishing to chapter eleven takes another 12 hours. Chapter 12 is another matter in itself: it really requires some understanding of code writing to get through it and my only quibble with the book is this: it plunges into VB code and, if you have no background in code, you'll get lost immediately and never find your way. It took me 12 hours to get through the chapter (I have a background in SQL) and the downloadable files don't help much here as they're all driven by pre-written macros.
If you want to advance your way up the career ladder with more sophisticated use of Excel, this is the place to start.
42 of 43 people found the following review helpful.
An excellent book for both all the newbie and expert alike.
By Darryl Collins
On the Excel Developer Mailing list that I subscribe to there are many talented and generous people whom freely share their Excel knowledge and experience with others less skilled. Numerous of them are MVP's.
Now when folks like Bob Umlas and Jon Peltier (whom are MVP Excel experts in their own rights) say a book is worth looking at, well, I definitely am keen to check it out.
Bill "Mr. Excel" Jelen and Michael Alexander's book on getting the best out of Pivot tables was recommended to me by Jon Peltier ([...] ) and what a great piece of advice it turned out to be.
Pivot table are one of Excel's most powerful features, and yet many users never harness the potential and power of this wonderful tool. I use pivot tables a lot and thought I had a good understanding of them, but this book has taken my productivity to a whole new level. Reading this book and understanding the concepts within has definitely been a "wow" experience for me.
The style of writing is excellent and extensive screenshots and notes make it easy to follow the necessary processes to quickly and painless achieve the desired outcome.
This book covers everything from the history of the Pivot table and data basics, right through to complex tasks involving calculated pivot fields, pivot charts and extracting data from external data sources such as an SQL database or OLAP cube.
Importantly the authors also explain in great detail the potential for risk and data errors and how to minimise the chances of errors being generated. A worthwhile process for anyone using excel.
Actually I loved the way the book was interesting and useful for all skill levels. Even if you had never created a pivot table before this book is very accessable and easy to follow. And for people like myself who are advanced Excel users, there is still an amazing amount of clever techniques to maximise the power of pivot tables.
As a professional excel developer this book has definitely added value to work and (more importantly) to the end products I produce for my clients. A "wow! That is great!" client is a happy client.
See all 36 customer reviews...
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen PDF
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen EPub
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen Doc
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen iBooks
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen rtf
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen Mobipocket
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen Kindle
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen PDF
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen PDF
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen PDF
Pivot Table Data Crunching (Adobe Reader) (Business Solutions), by Michael Alexander, Bill Jelen PDF
