Pivot Table Data Crunching for Microsoft Office Excel 2007 and over one million other books are available for Amazon Kindle. Learn more
Qty:1
  • List Price: $34.99
  • Save: $11.85 (34%)
FREE Shipping on orders over $35.
Only 12 left in stock (more on the way).
Ships from and sold by Amazon.com.
Gift-wrap available.
Pivot Table Data Crunchin... has been added to your Cart
FREE Shipping on orders over $35.
Condition: Used: Like New
Comment: Eligible for FREE Super Saving Shipping! Fast Amazon shipping plus a hassle free return policy mean your satisfaction is guaranteed! Tracking number provided in your Amazon account with every order. Crisp, clean pages; like new.
Access codes and supplements are not guaranteed with used items.
Have one to sell? Sell on Amazon
Flip to back Flip to front
Listen Playing... Paused   You're listening to a sample of the Audible audio edition.
Learn more
See this image

Pivot Table Data Crunching for Microsoft Office Excel 2007 Paperback – January 5, 2007

ISBN-13: 978-0789736017 ISBN-10: 0789736012 Edition: 1st

Buy New
Price: $23.14
35 New from $4.84 46 Used from $0.01
Amazon Price New from Used from
Kindle
"Please retry"
Paperback
"Please retry"
$23.14
$4.84 $0.01
Free%20Two-Day%20Shipping%20for%20College%20Students%20with%20Amazon%20Student


Frequently Bought Together

Pivot Table Data Crunching for Microsoft Office Excel 2007 + Learn Excel 2007 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques
Price for both: $44.98

Buy the selected items together

NO_CONTENT_IN_FEATURE

Shop the new tech.book(store)
New! Introducing the tech.book(store), a hub for Software Developers and Architects, Networking Administrators, TPMs, and other technology professionals to find highly-rated and highly-relevant career resources. Shop books on programming and big data, or read this week's blog posts by authors and thought-leaders in the tech industry. > Shop now

Product Details

  • Paperback: 360 pages
  • Publisher: Que Publishing; 1 edition (January 5, 2007)
  • Language: English
  • ISBN-10: 0789736012
  • ISBN-13: 978-0789736017
  • Product Dimensions: 9.1 x 7.1 x 0.8 inches
  • Shipping Weight: 1.2 pounds (View shipping rates and policies)
  • Average Customer Review: 4.2 out of 5 stars  See all reviews (23 customer reviews)
  • Amazon Best Sellers Rank: #415,052 in Books (See Top 100 in Books)

Editorial Reviews

About the Author

Bill Jelen is Mr. Excel! He is principal behind the leading Excel website, 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 an accomplished author of books on Excel and 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 English-speaking world. His website hosts over 12 million page views annually.

 

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

Excerpt. © Reprinted by permission. All rights reserved.

Introduction

Introduction

Pivot tables are the single most powerful feature in all of Excel. They came along during the 1990s 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 1 million 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 moving 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 that close to 50% of Excel users leave 80% of Excel untouched. That is, 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 12 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 pivot tables have a power 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.

What Is New in Excel 2007's Pivot Tables

Microsoft streamlined the pivot table interface to make it easier to use. In the last six versions of Excel, you generally created and modified a pivot table by dragging field names around the worksheet. Excel provided subtle visual clues about where a dropped field would appear, but these clues were too subtle for most. If you accidentally dropped a text field in the data area instead of the row area, disaster would result. Now, in Excel 2007, you can build a pivot table by checking a few boxes. Excel's IntelliSense figures out the best location for the field. To modify the default, you can drag field names around the PivotTable Field List.

Also new in Excel 2007 is the easier interface for sorting and filtering fields in a pivot table. Whereas sorting was formerly hidden three levels deep in the menu system, it is now just one click away from the PivotTable Field List.

Formatting such as heat maps, data bars, banded rows, and columns are now available as icons on the Excel 2007 Ribbon.

Finally, Microsoft is pushing a high-end server product that allows many people to access data stored in pivot tables. It is no surprise that pivot tables play a key role in the interactivity of Excel Services for SharePoint.

Skills Required to Use This Book

We have created a reference that is comprehensive enough for hard-core analysts yet relevant to casual users of Excel. The bulk of the book covers how to use pivot tables in the Excel user interface. The final chapter describes 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 thousands of rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and product.

In 1992, preparing this report was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you needed to take:

  1. 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.
  2. You need to get a list of the unique products in the dataset. Use the Advanced Filter command with Unique Records Only a second time to extract a list of the unique products.
  3. You need to turn the list of products sideways so that it runs across the columns. Copy the list of unique models. Then choose Edit, Paste Special, Transpose to arrange the products as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.
  4. You could use the DSUM function to total a column based on one criterion, 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 sample model.
  5. 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 the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter; otherwise, 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.


  1. You know you're a hard-core data analyst if you can still imagine pressing the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. In Excel 2007, this command is found in Data, Data Tools, What If Analysis, Data Table.
  2. Finally, after using two advanced filters and a Paste Special command, 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 off this analysis in 10 minutes, you were doing an amazing job.

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 a user could build a tool that could recognize these patterns, then he could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demonstrated 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.

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 capability to add calculated fields. Excel 97 also opened 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 600,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:

  1. Select a single cell in your dataset. Choose PivotTable from the Insert tab. Click OK. You are given a blank pivot table, as shown in Figure I.7.
  2. From the Pivot Table Field List, click the Region check box. Excel adds it to the left side of the pivot table. Click the check box next to Product_Number. In the lower portion of the dialog box, drag Product_Description from the Row Labels section to the Column Labels section. Click the Sales_Amount field in the top of the Pivot Table Field List. After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

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.

...


More About the Authors

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Very easy to follow and understand.
Jim Hardy
It is simple to use, just start at the beginning and in a few hours or a couple of days you will wonder how you ever did without pivot tables.
Charles Crizer
It is a good supplement to learning more about the pivot table role in data crunching.
Swony

Most Helpful Customer Reviews

50 of 53 people found the following review helpful By John Matlock on January 30, 2007
Format: Paperback
Pivot Tables are one of the most powerful yet least used features of Excel. The author's MrExcel web site conducted a survey that showed that less than 42% of Excell users make use of pivot tables. I believe his estimate is high. Only experts are likely to visit his web site. I don't believe that 42% of the people who use Excel have even heard of pivot tables.

I further think that part of the problem is the name Pivot Table. This is primarily a Microsoft term (Microsoft has trademarked the word PivotTable - with no space), but the original functionality came from Lotus Improv.

It's foolish for me to try to explain pivot tables here when the book exists. Let me just say that if your task involves taking a lot of data that would make a lot more sense to you or your boss if it were summarized in various ways, Pivot Tables are for you, and the authors of this book are recognized experts in the field.

Note that the book has been brought up to date to cover Excel 2007. There are a number of changes in 2007 that are distinct improvements over previous versions but that require you to do things that are a bit different while the overall functionality remains basically the same.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
21 of 22 people found the following review helpful By Bob V on August 13, 2009
Format: Paperback
There are no references in the book to the applicaplbe practice files (downloadable leaveing the user to open the various files and determine which is applicable to a given exercises. I'm only in Ch 3 and have found references within the text to tables within the text that do not exist. Example, Ch 3, pg 51 references Cell B7 in figure 3.7.
Figure 3.7 is a dialog box displaying how to change a field name. The book itself seems to have excellent content; however, I've found nomenclature to be inconsistent, practice files not identified, and references not accurate. Very informative book be very frustrating to use.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
20 of 21 people found the following review helpful By Alex B on July 2, 2007
Format: Paperback
This book helped tremendously during my early stages using PT, without a doubt I would recommend this book to someone who just started to incorporate Pivot Table into their work. One recommendation, It would be nice if a CD with all the work examples came with the book, unfortunately you have to download the example files from a website.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
22 of 25 people found the following review helpful By WAMozart on May 10, 2007
Format: Paperback Verified Purchase
I have no previous experience of either Pivot Table or Office 2007. However, by following the instructions of this book and keep practicing, I don't feel pivot table is so difficult as I did before. Highly recommend this book.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
9 of 9 people found the following review helpful By P. Edwards on May 3, 2008
Format: Paperback
I have thoroughly enjoyed what I have learnt so far. Good and easy to understand. However, very difficult to follow which of the downloadable exercise files is being used. Need to state the file name in the book. I am wasting time trying to find the correct file in order to follow the instructions.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
7 of 7 people found the following review helpful By J. Peterson on January 14, 2009
Format: Paperback
I liked this book. Even though I had a basic idea of what pivot tables are and how to use them, and actually HAVE used them, this book was exactly right to make things much more clear to me.

Specific things I liked:
- It's concise. Only 274 pages in all, but all of them useful. No wading through long-winded introductory material. This gets right to the point.
- It gives specific information about how you should structure your data so it's amenable to pivot table processing. I had been struggling to make a pivot table work with data that I had already collected into a typical 2-dimensional table but was having trouble making it come out as I wanted, and now I know why. That alone was worth the price of the book.

This book would have been perfect if it had been more specific to Excel 2007 and had not tried to be applicable to users of many previous versions of Excel. The 2007 version is enough different that I was left feeling a bit hungry for the specific 2007 view at times.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
5 of 5 people found the following review helpful By S. WIlson on July 15, 2009
Format: Paperback
I use pivot tables all of the time so I bought this book to see if there are things I can learn. I did and have learned a lot and it is a great reference. The first two chapters cover the basics but it advances very quickly after that. Writing is very bullet-ed and you can easily look up what you are looking for. A must have for all beginner, intermediate, and even advanced Pivot Table users!

Note of caution is that if you do not have Excel 2007 this book is not that useful as a lot of the features for Pivot Tables have changed from the 2003 to the 2007 version.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again
7 of 8 people found the following review helpful By J Boland on March 26, 2007
Format: Paperback
Simple, straight forward and clear writing make this book a very useful addition to any library. It explains clearly the concepts and more importantly how to apply them.
Comment Was this review helpful to you? Yes No Sending feedback...
Thank you for your feedback. If this review is inappropriate, please let us know.
Sorry, we failed to record your vote. Please try again

Most Recent Customer Reviews