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

 

...

<>Power Excel 2003 with MrExcel LiveLessons

 

Mastering the Smartest, Fastest Ways to Use Excel

 

Bill Jelen

 

DVD includes:

• 6+ hours of video instruction   
• More than 80 tips and techniques
• Features that can be learned in less than Five minutes

Bill Jelen (“MrExcel”) presents focused, practical videos that teach you powerful new ways to chart, calculate, build PivotTables, automate workbooks, wrangle your data, and more! Click play to follow along as Jelen walks you through using Excel’s most sophisticated features… all you need to do is watch!

 

You’ve got Microsoft Excel…now get the most out of it, faster and more easily than ever before! In 85 quick video lessons, Bill Jelen (“MrExcel”) shows you all you need to maximize your productivity with Excel 2003, 2002 (XP), or 2000!

 

Building on the live course he’s taught to thousands of financial professionals, Jelen walks you through Excel’s most powerful features and techniques. Just watch: You’ll master these powerful techniques at your own pace—and get specific answers and solutions immediately, whenever you need them!

 

These practical, easy-to-use videos cover charting, PivotTables, advanced calculations, worksheet management, macros, and a whole lot more—including tips and tricks that could save you hours every single week!

 

Looking for a better way to master today’s rapidly changing technologies? Want expert help but don’t have the time or energy to read a book? Can’t find classroom training worth the money? Discover LiveLessons: self-paced, personal video instruction from the world’s leading experts.

 

For anyone who wants to get more done in less time with Excel 2003, 2002 (XP), or 2000—from entrepreneurs to financial analysts, marketers to office managers!

 

• LiveLessons is an eight-hour video course organized into bite-sized, self-contained sessions—you’ll learn key skills in as little as five minutes!
• Each session begins with well-defined learning objectives and ends with comprehensive summaries, which help you track your progress.
• Follow along as your instructor shows exactly how to get great results in your real-world environment.

Bill Jelen is known worldwide as MrExcel. He presents live Excel seminars to accountants throughout the U.S. and has logged more than 60 appearances on The Lab with Leo Laporte. Honored as a Microsoft MVP for Excel, he has produced 500 episodes of his “Learn Excel from MrExcel” video podcast. He writes the monthly Excel column for Strategic Finance magazine.

 

Microsoft Office Spreadsheets/Desktop Applications

 

mylivelessons.com

informit.com/que

 

Contents:

 

Part 1 Charting

Lesson 1 Creating a Chart Using the Wizard

Lesson 2 Creating a Chart with One Keystroke

Lesson 3 Formatting a Chart

Lesson 4 Adding Chart Data

Lesson 5 Noncontiguous Data

Lesson 6 Interactive Chart

Lesson 7 When to Choose Which Chart Type

Lesson 8 Charting Differing Orders of Magnitude

Lesson 9 Pie Chart Tricks

Lesson 10 XY & Bubble Charts

Lesson 11 Histograms & Overlap Charts

Lesson 12 Saving Favorite Chart Settings

Lesson 13 Chart Lies

Part 2 Text

Lesson 14 Drag the Fill Handle to Extend a Series

Lesson 15 Create Your Own Series

Lesson 16 Joining Text

Lesson 17 Rapid Formula Copy

Lesson 18 Convert to Proper Case

Lesson 19 Joining Dates

Lesson 20 Converting Formulas to Values

Lesson 21 Breaking Apart Text

Lesson 22 Parsing Fixed Width

Part 3 Excel Customizations

Lesson 23 Full Menus

Lesson 24 Toolbar Customization

Lesson 25 Resetting Toolbars

Lesson 26 Steve-O Toolbar

Lesson 27 Close All Workbooks

Lesson 28 Recently Used File List

Lesson 29 Data Entry Tricks

Lesson 30 Customize All Future Workbooks

Lesson 31 Copy Worksheets

Lesson 32 Change All Worksheets

Lesson 33 Arranging Windows

Part 4 Sorting & Subtotals

Lesson 34 Data Preparation

Lesson 35 Sorting Basics

Lesson 36 Random Sort

Lesson 37 Sorting by Weekday

Lesson 38 Rearranging Columns

Lesson 39 Subtotals

Lesson 40 Collapsing Subtotals

Lesson 41 Copy Subtotals

Lesson 42 Formatting Subtotals

Lesson 43 Multiple Subtotals

Lesson 44 Filling in Data on Subtotal Rows

Lesson 45 Subtotal Tricks

Part 5 Pivot Tables

Lesson 46 Creating a Pivot Table

Lesson 47 Changing a Pivot Table

Lesson 48 Pivot Gotchas

Lesson 49 Pivot Sorting

Lesson 50 Drill Down

Lesson 51 Summarizing Dates

Lesson 52 Top 10 Report

Lesson 53 Pivot Calculations

Lesson 54 Pivot Formatting

Lesson 55 Unique List

Lesson 56 Ad-Hoc Queries

Lesson 57 50 Reports in 5 Seconds

Lesson 58 Making Reports Pivotable

Part 6 Formulas

Lesson 59 AutoSum Tricks

Lesson 60 Formula Auditing

Lesson 61 Three Ways of Entering Formulas

Lesson 62 Relative Versus Absolute Formulas

Lesson 63 Discovering Functions

Lesson 64 Goal Seek

Lesson 65 Unusual Functions

Lesson 66 IF Formulas

Lesson 67 VLOOKUP

Lesson 68 Super Formulas

Lesson 69 Date & Time

Part 7 Macros

Lesson 70 Recording a Macro

Lesson 71 Recording Navigation

Lesson 72 How Many Rows?

Lesson 73 Event Macros

Part 8 Tips & Tricks

Lesson 74 Finding Records

Lesson 75 Web Queries

Lesson 76 Copying Column Widths

Lesson 77Floating Palettes

Lesson 78 Page Numbering

Lesson 79 Turn Data

Lesson 80 & in Header

Lesson 81 Choose from a List

Lesson 82 Tool Tips for a Cell

Lesson 83 Word Processing in Excel

Lesson 84 Mapping Data

Lesson 85 Have a Talk with Excel

 

...

DVD includes:

• 7+ HOURS OF VIDEO INSTRUCTION
• 50 TIPS & TECHNIQUES
• SKILLS YOU CAN LEARN IN Fifteen MINUTES OR LESS

 

In Excel VBA and Macros with MrExcel, renowned Excel instructor and author Bill Jelen (MrExcel) teaches all the skills you’ll need to automate virtually any routine task with Excel and build powerful Excel macros! This package brings together nearly eight hours hours of personalized, expert video training: 50 quick, practical video lessons that demonstrate all the skills you’ll need to successfully use both the Excel macro recorder and the Visual Basic for Applications development environment. You’ll learn one step at a time, at your own pace–using hands-on examples that reflect realistic challenges and showcase Excel’s remarkable capabilities. Along the way, Jelen will take you from the absolute basics through PivotTables and data filtering.

 

Excel VBA and Macros with MrExcel delivers the power of the best classroom training at a small fraction of the cost. If you don’t have time to read a huge book on Excel macros and scripting, this is exactly what you’ve been searching for!

 

For all serious Excel users: managers, financial pros, entrepreneurs, marketers, analysts, and more.

 

Looking for a better way to master today’s rapidly changing technologies? Want expert help, but don’t have the time or energy to read a book? Can’t find classroom training worth the money? Discover LiveLessons: self-paced, personal video instruction from the world’s leading technology experts.

• LiveLessons are video courses, on DVD with a book supplement, that are organized into bite-sized, self-contained sessions–you’ll learn key skills in as little as fifteen minutes!
• Each lesson begins with well-defined learning objectives and ends with comprehensive summaries, which help you track your progress.
• Follow along as your trainer shows you how to make the most of Excel’s macro recorder and its powerful VBA development environment!

 

Bill Jelen is called MrExcel for a reason! Nobody knows more about Excel macros and scripting–and nobody knows more about teaching these skills to working professionals! Thought you’d never write your own Excel macros and programs? Think again! You will…and you can start in just minutes!

 

Bill Jelen, known worldwide as MrExcel, presents live Excel seminars across the United States and appears in over 800 podcast episodes. His 20 Excel books include Special Edition Using Excel 2007 and VBA and Macros for Microsoft Excel. His website, MrExcel.com, answers 30,000 Excel questions per year.

 

System Requirements

• OS: Windows 98, 2000, XP, and Windows Vista; Mac OS X; versions of Linux with the Flash 8 Player or later.
• Multimedia: DVD drive, an 1024x768 or higher display, and a sound card with speakers.
• Computer: 500MHz or higher, 128MB RAM or more

 

Microsoft Office Spreadsheets/Desktop Applications

 

$49.99 USA / $59.99 CAN / £31.99 U.K.

 

mylivelessons.com

informit.com/que

 

...

<>It is easy to create a bad looking chart in Excel. This book teaches you how to unlock the beautiful formatting options available to make incredible looking charts. The first section will talk about how to decide which chart type to use. Subsequent chapters will walk through each chart type, how to create them, how to utilize them, and special options available for each chart. The book discusses themes, colors, creating metallic charts, shadows, transparency, etc. The book also handles anything graphical in Excel. It will show the new In-Cell Data Bar charts available in Excel 2007. A section will talk about creating business flowcharts with IGX Graphics and how to display product pictures in Excel, and a section on VBA will cover creating 100's of charts using the macro language.

 

“More than a how-to and reference, this book also provides the why-tos and when-tos, with serious consideration given to layout best practices and design possibilities–a very well-rounded resource.”

–Kathy Villella, CEO, PowerFrameworks.com

• Implementing 1-Click Charting
• Incorporating Drag & Drop and Dynamic Charts
• Creating Amazing Effects
• Using Charting Templates and Macros
• Mastering Glow, Shadow, Sparklines, Dashboards, and More
• Eliminating Chart Junk
• Structuring Spreadsheets with Business Diagrams, SmartArt Graphics, and Pivot Charts
• Develop your Charting expertise instantly with proven techniques

After 15 years with no updates to the Excel charting engine, Microsoft has provided a complete rewrite of the chart rendering engine in Excel 2007. However, no amount of soft glow or glass bevel effects will help you communicate your point if you use the wrong chart type. This book helps you choose the right charting type and shows you how to make it look great.

 

This book shows you how to coax Excel to create many charts you might not have believed were possible. You’ll learn techniques that allow you to ditch the Microsoft defaults and actually create charts that communicate your point. You’ll learn why the Excel stock charts are so restrictive and how you can easily turn any line chart into a stock chart–without any limitations. You’ll also learn how to add invisible series to make columns float in midair. Learn how to create charts right in Excel cells using the new Excel 2007 data bars–or even the decades-old REPT function!

 

In no time, this book will have you creating charts that wow your audience and effectively communicate your message.

• Master effective visual display of data
• Choose the right chart type to convey your message
• Learn time-saving workarounds
• Create charts that most people think you can’t create with Excel
• Understand what a Radar chart is and when you might use it
• Summarize a million rows of data in a single pivot table chart
• Present data graphically without charts
• Employ SmartArt graphics to show process or relationship charts
• Utilize VBA to create charts
• Put your data on a map
• Export your charts to the web or PowerPoint
• Detect chart lies

ABOUT THE AUTHOR

Bill Jelen is MrExcel! He is principal behind the leading Excel website, MrExcel.com. He honed his Excel wizardry during his 12-year tenure as a financial analyst for a fastgrowing 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 The Lab on TechTV Canada. You can find Bill at your local accounting group chapter meeting entertaining audiences with his humorous and informative Power Excel seminar. His website hosts more than 12 million page views annually.

 

Introduction

1 Introducing Charts in Excel 2007

2 Customizing Charts

3 Creating Charts That Show Trends

4 Creating Charts That Show Differences

5 Creating Charts That Show Relationships

6 Creating Stock Analysis Charts

7 Advanced Chart Techniques

8 Creating and Using Pivot Charts

9 Presenting Data Visually Without Charts

10 Presenting Your Excel Data on a Map Using Microsoft MapPoint

11 Using SmartArt Graphics and Shapes

12 Exporting Your Charts for Use Outside of Excel

13 Using Excel VBA to Create Charts

14 Knowing When Someone Is Lying to You with a Chart

Appendix A: Charting References

Index

...