Special Edition Using Microsoft(R) Office Excel 2007 [Deluxe Edition] [Paperback]

Bill Jelen (Author)

Book Description

Publication Date: December 16, 2006 | ISBN-10: 078973611X | ISBN-13: 978-0789736116 | Edition: 1

Special Edition Using Microsoft Office Excel 2007 will ease the upgrade path to the lastest version of Microsoft best-selling spreadsheet program. The author, "Mr. Excel "  introduces you to the new interface, allowing  you to quickly get back up-to-speed in performing your job, and will then introduce the powerful new features available in Excel 2007. Among other skills, you will learn how to create amazing data visualizations using conditional formatting and in-cell data bars. This is the only book you need on Microsoft Office Excel 2007!

 

“Excel 2007 is the biggest, most exciting release of Excel ever. This book’s straightforward approach explains the most important features of Excel 2007 in a thorough, easy-to-understand format. Further, it clearly compares older versions of Excel with Excel 2007, which makes for a seamless transition to this newest version of the program. It is a must-have desk reference for today’s business professional.”

–David Gainer,

Group Program Manager,

Microsoft Excel

 

THE ONLY  EXCEL BOOK YOU NEED

We crafted this book to grow with you, providing the reference material you need as you move toward Excel proficiency and use of more advanced features. If you buy only one book on Excel, Special Edition Using Microsoft® Office Excel® 2007 is the book you need.

 

Does your life play out in a spreadsheet? Do numbers in columns and rows make or break you in the work world? Tired of having numbers kicked in your face by other Excel power users who make your modest spreadsheets look paltry compared to their fancy charts and pivot tables?

 

If you answered yes to any of these questions, Special Edition Using Microsoft® Office Excel® 2007 is the bookthat will make it all better. Learn quickly and efficientlyfrom a true Excel master using the tried and true SpecialEdition Using formula for success. Here, you’ll findinformation that’s undocumented elsewhere–even inMicrosoft’s own Help systems. You’ll learn from finelycrafted, real-life examples built by an author who livesand dies by the integrity of his spreadsheets.

 

Excel’s backbone is its formulas and functions. Master those and you will master your spreadsheets. Special Edition Using Microsoft Office® Excel® 2007 provides more down and dirty help with your formulas and functions than you’ll find in any other book! See how it’s done in real life! Don’t settle for lame pivot table and chart examples found in other books… This book provides beautifully detailed examples that not only show you how it should be done, but how to be the local worksheet hero!

 

CONTENTS

Introduction

I Mastering the New User Interface

1 Introducing the Ribbon User Interface . . . . . . . . . . . . . 21

2 The Quick Access Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

3 The Mini Toolbar and Other U.I. Improvements . . . 53

4 Keyboard Shortcuts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

5 Galleries, Live Preview, and Themes . . . . . . . . . . . . . . . . 87

6 The Excel Options Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . 103

II A Tour of What’s New

7 The Big Grid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

8 Fabulous Table Intelligence . . . . . . . . . . . . . . . . . . . . . . . . 131

9 Visualizing Data in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153

10 Using Pivot Tables to Analyze Data . . . . . . . . . . . . . . . . 191

11 Formatting Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215

12 Pivot Table Data Crunching for Excel 2007 . . . . . . . 237

13 Removing Duplicates and Filtering . . . . . . . . . . . . . . . . 271

14 Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287

15 Using Excel Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299

16 Using SmartArt, Shapes, WordArt, and Text Boxes . . 327

17 Using Pictures and Clip Art . . . . . . . . . . . . . . . . . . . . . . . . 351

III Working in a Legacy Environment

18 File Format Differences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365

19 Working with Prior Versions of Excel . . . . . . . . . . . . . . 371

IV Calculating with Excel

20 Understanding Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . 385

21 Controlling Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409

22 Understanding Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

23 Using Everyday Functions: Math, Date and Time,

and Text Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451

24 Using Powerful Functions: Logical, Lookup, and

Database Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525

25 Using Financial Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 587

26 Using Statistical Functions . . . . . . . . . . . . . . . . . . . . . . . . . 631

27 Using Trig, Matrix, and Engineering Functions . . . . 735

28 Connecting Worksheets, Workbooks, and

External Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 797

29 Using Super Formulas in Excel . . . . . . . . . . . . . . . . . . . . 823

30 Using Names in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841

31 Using What If, Scenario Manager, Goal Seek,

and Solver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 863

V Formatting and Sharing Information

32 Formatting Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 891

33 Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 925

34 Sharing Workbooks with Others . . . . . . . . . . . . . . . . . . . 941

35 More Tips and Tricks for Excel 2007 . . . . . . . . . . . . . . 949

VI More Power

36 Automating Repetitive Functions Using

VBA Macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 969

37 Interacting with Other Office Applications . . . . . . 1005

38 A Tour of the Best Add-Ins for Excel . . . . . . . . . . . . . 1019

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1025

 

Editorial Reviews

About the Author

Bill Jelen, Excel MVP and MrExcel, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. His team provides custom Excel applications to clients around the world. You can see Bill as a regular guest on Call for Help with Leo Laporte in Australia, in Canada, and on Google Video. Bill produces a daily video podcast about Excel. He also enjoys taking his show on the road, doing a one- to four-hour power Excel seminar anywhere that a room full of accountants or Excellers will show up.

Product Details

• Paperback: 1080 pages
• Publisher: Que; 1 edition (December 16, 2006)
• Language: English
• ISBN-10: 078973611X
• ISBN-13: 978-0789736116
• Product Dimensions: 9.1 x 7.1 x 2.2 inches
• Shipping Weight: 3.6 pounds (View shipping rates and policies)
• Average Customer Review: 3.2 out of 5 stars  See all reviews (12 customer reviews)
• Amazon Best Sellers Rank: #403,659 in Books (See Top 100 in Books)

More About the Author

I spent twelve years 'in the trenches', working as a financial analyst for finance, accounting, marketing and operations departments of a $500 million company. Armed with a half-rate reporting tool and a spreadsheet, I was responsible for turning large amounts of data into analyses for the board room quickly and accurately. Working originally in Lotus 1-2-3 and then Microsoft Excel, I honed techniques to take massive amounts of data and produce meaningful reports in record time. These techniques are spelled out in Guerilla Data Analysis Using Microsoft Excel.

In 1998, I launched MrExcel.com, which is now one of the three most popular Excel websites with 21 million annual page views. Being MrExcel is now my full-time gig. My team has developed custom applications for hundreds of clients around the world. I enjoy entertaining a room full of accountants with my 'Power Excel' seminar.

When I am not Excelling, you can find me and the family under the stars enjoying a movie at one of the many drive-in theaters that still dot northeast Ohio or kayaking on the Mogadore Resevoir.

Customer Reviews

Most Helpful Customer Reviews

34 of 36 people found the following review helpful:

3.0 out of 5 stars Inadequate for many features, May 7, 2007

By 

Brews (Tucson, AZ) - See all my reviews

This review is from: Special Edition Using Microsoft(R) Office Excel 2007 (Paperback)

This book is only so-so as a guide to features, and definitely isn't aimed at engineers or technical types.

A simple example: suppose you want to change the cursor motion after Enter so the cursor stays put, instead of moving down to the next cell. Look under "Cursor" in the index -"Cursor" is not an entry. Look under the Chapter on Excel Options Dialog (the menu where this is adjusted). Cursor adjustment neither is mentioned nor is the menu to do it shown in any of the screen shots. Another example: suppose you want to find the Document Properties, like who is the author. Help tells you "Click the Office Button, point to Prepare, click on Properties". Jelen tells you nothing explicit, although he has a table p. 32 suggesting erroneously that you use menu View, Show/Hide, Properties. So, use Help, I guess.

There's more: suppose you want to change the label of a single data point on an xy-chart. The procedure, found by trial and error, is to select the chart, select the FORMAT tab under "Chart Tools" on the "ribbon", select the curve on the chart (click it), select the data point (click it), select "Format Selection" in the "Current Selection" window of the "ribbon". That extended process produces the same-old menu choices you used to get by selecting the point and making a right-click. Jelen doesn't tell you anything at all, and the Excel help is useless too.

More examples? If you want to use Goal Seek, the book tells you how to find it, but it does not tell you how to set Maximum Iterations and Maximum Change, two items often changed. To find that info, use Excel Help, a hit-and-miss thing at best. The book tells you how to install Solver, but fails to point out how to call it once installed. As typical of Excel books, there is no assessment of accuracy of Excel functions, nor the hazards of round-off errors.

By and large, guidance is erratic, and there's much blather about "jaw-dropping new Excel features". The Special Edition volume on Word 2007 is better organized, has a comprehensive index, and uses cross-references to advantage. Author Jelen should look at that book to see how much better things are done.

As a postscript, Jelen's five-page discussion of Assigning a Formula to a Name (p. 857-862) has several examples, while the "Inside Out" book has only a paragraph (p. 448). So there are topics that are better in Jelen.

22 of 27 people found the following review helpful:

5.0 out of 5 stars Everything there is to know about Excel 2007, December 23, 2006

By 

John Matlock "Gunny" (Winnemucca, NV) - See all my reviews
(REAL NAME)   

This review is from: Special Edition Using Microsoft(R) Office Excel 2007 (Paperback)

Excel 2007 is a fairly major rewrite of the old Excel that we all came to know and love. The old Excel, whether it was Excel 97, 2000, XP, or 2003 was pretty much the same program. It had just about the same look and feel. To be sure Microsoft marketing made lots of smoke about the new features in each edition, but in reality there wasn't much fire behind that smoke. With Excel 2007 there are some significant changes - I mean, it's still a spread sheet, but but it's changed and improved in a number of ways. ==There are really two reasons for buying this book. One is that you're a newcommer to Excel. There must be a few people out there who are just starting out on Excel. This book has a fair amount of material aimed at you. It's probably not as basic as some books. It at lease presumes that you have some indication as to what a spreadsheet is nad does. ==The other reason is to be able to come up to date with the 2007 features, perhaps to use as part of your decision making process about upgrading or not. Here the book is excellent. In particular, its description of the new 'ribbon' feature is as good as or better than any I've seen. ==In general the book is organized with each thapter taking on a new subject. The beginning of each chapter is spent talking about the new 2007 features as they pertain to that particular chapter. ==All in all, here's everything there is to know in Excel 2007

5 of 5 people found the following review helpful:

3.0 out of 5 stars Good for transition, but not for learning, September 4, 2008

By 

Ernest Pergrem (Elgin, IL USA) - See all my reviews
(REAL NAME)   

This review is from: Special Edition Using Microsoft(R) Office Excel 2007 (Paperback)

In many ways this is a good book. I just don't feel that it's a good "learning" book.

For a person wanting to learn Excel 2007, this book's biggest flaw is the lack of hands-on exercises.

It does include some step-by-step examples, but they assume that the data already exists. This would have been okay had a CD been included, or the files were available on the Que site, but neither seemed to be the case. Therefore, I didn't find them terribly useful.

I would recommend that new Excel 2007 users look elsewhere. However, those making the transition from Excel 2003 might find this a fairly decent text.

No half stars so I'm stuck at 3 instead of 3 1/2...