by Bill Jelen

Containing 277 business case studies that illustrate nearly every aspect of Excel, this book presents real-life business problems and works them through to their solutions. In addition to exemplary solutions, each case analysis considers alternate approaches and gotchas, and includes a summary of the necessary commands and functions. Excel files that can be downloaded and worked through step-by-step are included for each case.

Read more >>

by Bill Jelen

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

Read more >>

by Bill Jelen

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.

Read more >>

by Bill Jelen

“In this day and age of ‘too much information and not enough time,’ the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.” —Jerry Kohl, president of Brighton Collectibles Develop your Excel macro programming skills using VBA instantly with proven techniques Automate Reports Handle Errors Master Pivot Tables Produce Charts Build User-Defined Functions Migrate to Excel 2007 Query Web Data Build Dialog Boxes Use Data Visualizations Automate Word You are an expert in Excel, but the macro recorder doesn’t work and you can’t make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 records but not one with 12 or 16 records. These are all common problems with the macro recorder that unfortunately cause too many Excel gurus to turn away from writing macros. This book shows you why the macro recorder fails and the steps needed to convert recorded code into code that will work every day with every dataset. The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you. Learn VBA syntax as easy-to-understand English Automate Excel’s power tools: Pivot Tables, Charts, Advanced Filters Save hours per week by automating redundant tasks Create applications built on top of Excel with custom dialog boxes Automatically produce hundreds of Excel reports in seconds Understand how changes in Excel 2007 impact your VBA macros Introduction 1 1 Unleash the Power of Excel with VBA 7 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? 29 3 Referring to Ranges 61 4 User-Defined Functions 75 5 Looping and Flow Control 101 6 R1C1-Style Formulas 121 7 What’s New in Excel 2007 and What's Changed 135 8 Create and Manipulate Names in VBA 143 9 Event Programming 155 10 UserForms--An Introduction 177 11 Creating Charts 197 12 Data Mining with Advanced Filter 249 13 Using VBA to Create Pivot Tables 281 14 Excel Power 337 15 Data Visualizations and Conditional Formatting 373 16 Reading from and Writing to the Web 393 17 XML in Excel 2007 413 18 Automating Word 421 19 Arrays 441 20 Text File Processing 449 21 Using Access as a Back End to Enhance Multi-User Access to Data 461 22 Creating Classes, Records, and Collections 477 23 Advanced UserForm Techniques 493 24 Windows Application Programming Interface (API) 517 25 Handling Errors 529 26 Customizing the Ribbon to Run Macros 543 27 Creating Add-Ins 569 Index 577

Read more >>

by Bill Jelen

Providing the tools and techniques necessary for finding errors and fraud in audits, this guide for auditors looking to better validate their Microsoft Excel spreadsheets provides techniques for performing a risk assessment and gathering spreadsheet and other data from company systems. Performing audit data analysis using data and analytical management functions and pinpointing the common errors in spreadsheets with focused Excel tests is discussed, as are the best practices for error and fraud prevention when developing spreadsheets. This reference is fully updated to reflect Excel 12.

Read more >>

by Ted Gerard Jelen

Religion is resurgent across the globe. In many countries it is a powerful source of political mobilization, and in some, potent social cleavage. In some, religion reinforces the state, while in others, it provides the space for resistance. This book contains a series of detailed studies examining religion and politics in specific countries or regions. The studies include countries with one dominant religious tradition, and others with two or more competing traditions. They encompass Catholicism, Protestantism, Islam, Hinduism, Shinto, and Buddhism. They involve states where religion and politics are closely linked, and others with at least a basic separation between church and state.

Read more >>

by Bill Jelen

This book consolidates all the best functionality of pivot tables into one guide that provides you with a meaningful tutorial, offering practical solutions to day-to-day problems. Within just the first 2 chapters, you will be creating basic pivot tables, increasing productivity, and producing reports in minutes instead of hours. Within the first 6 chapters, you will learn how to use pivot tables to quickly highlight your top 10 customers or bottom 5 products in profitability; quickly create analysis comparing sales this period to last period by product or region or both; easily summarize daily transactional data by month or quarter or year in a few mouse clicks–all without knowing any formulas! By the end of the book, you will truly be a pivot table guru, automating pivot tables using VBA, creating pivot tables with external data in OLAP cubes, and even creating dynamic reporting systems so that your managers can answer their own queries with a few mouse clicks. • Take advantage of the numerous pivot table recipes • Create powerful summary reports in seconds • Build ad-hoc query tools with ease • Enhance your Executive Reporting • Filter reports to top 10 customers or products • Add dynamic charts to reports • Quickly summarize daily data by month, quarter, or year Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1 Pivot Table Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 2 Creating a Basic Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . .21 3 Customizing a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 4 Controlling the Way You View Your Pivot Data . . . . . . . . . . . .83 5 Performing Calculations Within Your Pivot Tables . . . . . . . . 117 6 Using Pivot Charts and Other Visualizations . . . . . . . . . . . . .141 7 Analyzing Disparate Data Sources with Pivot Tables . . . . . . 167 8 Sharing Pivot Tables with Others . . . . . . . . . . . . . . . . . . . . . .189 9 Working with and Analyzing OLAP Data . . . . . . . . . . . . . . . . 201 10 Enhancing Your Pivot Table Reports with Macros . . . . . . . . .215 11 Using VBA to Create Pivot Tables . . . . . . . . . . . . . . . . . . . . . 231 12 Common Pivot Table Issues and Questions . . . . . . . . . . . . . .291 A Finding Pivot Table Commands on the Ribbon . . . . . . . . . . . .315

Read more >>

by Bill Jelen

<>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 liesABOUT 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

Read more >>

by Bill Jelen

Everyone is looking for ways to save money these days. That can be hard to do for businesses that have complex needs, such as custom software applications. However, VBA and Macros for Microsoft Excel can teach you ways to customize pre-existing software to meet your specific needs. A variety of topics are covered that are sure to give you a solid knowledge of the VBA language. Event programming, user forms, carts, pivot tables, multi-dimensional arrays and Web queries are just a few of the areas you will learn about in this book. Written by the principal behind the leading Excel Web site, www.mrexcel.com, this book is sure to save you time and money!

Read more >>

by Bill Jelen

Drawn from actual excel conundrums posted on the author's website, www.mrexcel.com, this high-level resource is designed for people who want to stretch Excel to its limits. Tips for solving 100 incredibly difficult problems are covered in depth and include extracting the first letter of each word in a paragraph, validating URL's, generating random numbers without repeating, and hiding rows if cells are empty. The answers to these and other questions have produced results that have even surprised the Excel development team.

Details >>

by Bill Jelen

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

Details >>

by Bill Jelen

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 ExcelTHE ONLY- EXCEL BOOK YOU NEEDWe 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!

Details >>

by Bill Jelen

“In this day and age of ‘too much information and not enough time,’ the ability to get to the bottom line quickly and in a concise method is what excels companies to the top of their industry. The techniques in this book will allow you to do things you only dreamt of.” —Jerry Kohl, president of Brighton Collectibles Develop your Excel macro programming skills using VBA instantly with proven techniques Automate Reports Handle Errors Master Pivot Tables Produce Charts Build User-Defined Functions Migrate to Excel 2007 Query Web Data Build Dialog Boxes Use Data Visualizations Automate Word You are an expert in Excel, but the macro recorder doesn’t work and you can’t make heads or tails out of the recorded code. If this is you, buy this book. Macros that you record today might work today but not tomorrow. Recorded macros might handle a dataset with 14 records but not one with 12 or 16 records. These are all common problems with the macro recorder that unfortunately cause too many Excel gurus to turn away from writing macros. This book shows you why the macro recorder fails and the steps needed to convert recorded code into code that will work every day with every dataset. The book assumes that you know Excel well, but there is no need for prior programming experience. This book describes everything you could conceivably need to know to automate reports and design applications in Excel VBA. Whether you want to automate reports for your office or design full-blown applications for others, this book is for you. Learn VBA syntax as easy-to-understand English Automate Excel’s power tools: Pivot Tables, Charts, Advanced Filters Save hours per week by automating redundant tasks Create applications built on top of Excel with custom dialog boxes Automatically produce hundreds of Excel reports in seconds Understand how changes in Excel 2007 impact your VBA macros Introduction 1 1 Unleash the Power of Excel with VBA 7 2 This Sounds Like BASIC, So Why Doesn’t It Look Familiar? 29 3 Referring to Ranges 61 4 User-Defined Functions 75 5 Looping and Flow Control 101 6 R1C1-Style Formulas 121 7 What’s New in Excel 2007 and What's Changed 135 8 Create and Manipulate Names in VBA 143 9 Event Programming 155 10 UserForms--An Introduction 177 11 Creating Charts 197 12 Data Mining with Advanced Filter 249 13 Using VBA to Create Pivot Tables 281 14 Excel Power 337 15 Data Visualizations and Conditional Formatting 373 16 Reading from and Writing to the Web 393 17 XML in Excel 2007 413 18 Automating Word 421 19 Arrays 441 20 Text File Processing 449 21 Using Access as a Back End to Enhance Multi-User Access to Data 461 22 Creating Classes, Records, and Collections 477 23 Advanced UserForm Techniques 493 24 Windows Application Programming Interface (API) 517 25 Handling Errors 529 26 Customizing the Ribbon to Run Macros 543 27 Creating Add-Ins 569 Index 577

Details >>

by Bill Jelen

Updating the previous edition's tips to make them compatible with Excel 2007, and featuring new tips that are only available in Excel 2007, this new edition of Mr. Excel's popular software guide even incorporates suggestions sent in by readers. Each featured topic has a problem statement and description, followed by a broad strategy for solving the problem. Mr. Excel then walks readers through through the specific steps to solve the issue. Alternate strategies are also provided, along with common "gotchas" that trip users up, leaving readers with not only answers to their specific dilemmas, but also with new and quicker ways to use formulas and spreadsheets.

Details >>

by Bill Jelen

<>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 liesABOUT 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

Details >>

by Bill Jelen

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

Details >>

by Bill Jelen

Everyone is looking for ways to save money these days. That can be hard to do for businesses that have complex needs, such as custom software applications. However, VBA and Macros for Microsoft Excel can teach you ways to customize pre-existing software to meet your specific needs. A variety of topics are covered that are sure to give you a solid knowledge of the VBA language. Event programming, user forms, carts, pivot tables, multi-dimensional arrays and Web queries are just a few of the areas you will learn about in this book. Written by the principal behind the leading Excel Web site, www.mrexcel.com, this book is sure to save you time and money!

Details >>

by Bill Jelen

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.

Details >>

by Bill Jelen

Power Excel 2007 with MrExcel Mastering the Smartest, Fastest Ways to Use Excel 2007 Bill Jelen For anyone who wants to get more done in less time with Excel 2007—from entrepreneurs to financial analysts, marketers to office managers! Bill Jelen (“MrExcel”) presents focused, practical videos that teach you Excel 2007’s best new techniques: supercharged solutions for charting, calculating, formatting, data analysis, automation, and a whole lot more! Click play to follow along as Jelen walks you through using Excel 2007’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 more than 90 quick video lessons, Bill Jelen (“MrExcel”) shows you all you need to maximize your productivity with Excel 2007. 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 Excel 2007’s new Ribbon interface and most powerful techniques at your own pace—and get specific answers and solutions immediately, whenever you need them! These practical, easy-to-use videos cover Excel 2007’s hottest new features: everything from charting improvements to conditional formatting, filtering to SmartArt. You’ll find up-to-the-minute tips and tricks for using PivotTables, managing and automating worksheets, and a whole lot more—great new solutions 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. 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 Contents: Part 1 Excel 2007 Interface Lesson 1 Introducing the Ribbon Lesson 2 Quick Access Toolbar Lesson 3 Finding Icons Lesson 4 Keyboard Shortcuts Lesson 5 Mini Toolbar and Live Preview Lesson 6 Zoom Slider and Page Layout Lesson 7 Recent Documents Lesson 8 Save as PDF Lesson 9 Excel Options Lesson 10 Opening Templates, Closing Excel Part 2 Excel 2007 New Features Lesson 11 Unlocking the Big Grid, File Formats Lesson 12 Cell Styles and Themes Lesson 13 Data Visualizations Lesson 14 Tables and Table Formulas Lesson 15 Sort and Filter by Color Lesson 16 Duplicates Lesson 17 Picture Tools and WordArt Lesson 18 New Features Part 3 Text Lesson 19 Drag the Fill Handle to Extend a Series Lesson 20 Create Your Own Series Lesson 21 Joining Text Lesson 22 Rapid Formula Copy Lesson 23 Convert to Proper Case Lesson 24 Joining Dates Lesson 25 Converting Formulas to Values Lesson 26 Breaking Apart Text Lesson 27 Parsing Fixed Width Part 4 Excel Customizations Lesson 28 Data Entry Tricks Lesson 29 Customize All Future Workbooks Lesson 30 Copy Worksheets Lesson 31 Change All Worksheets Lesson 32 Arranging Windows Part 5 Sorting and Subtotals Lesson 33 Data Preparation Lesson 34 Sorting Basics Lesson 35 Random Sort Lesson 36 Sorting by Weekday Lesson 37 Rearranging Columns Lesson 38 Subtotals Lesson 39 Collapsing Subtotals Lesson 40 Copy Subtotals Lesson 41 Formatting Subtotals Lesson 42 Multiple Subtotals Lesson 43 Filling In Data on Subtotal Rows Lesson 44 Subtotal Tricks Part 6 Pivot Tables Lesson 45 Creating a Pivot Table Lesson 46 Changing a Pivot Table Lesson 47 Pivot Gotchas Lesson 48 Pivot Sorting Lesson 49 Drill Down Lesson 50 Summarizing Dates Lesson 51 Top 10 Report Lesson 52 Pivot Calculations Lesson 53 Pivot Formatting Lesson 54 Unique List Lesson 55 Ad-Hoc Queries Lesson 56 New Filtering Options Lesson 57 50 Reports in 5 Seconds Lesson 58 Pivot Compatibility Lesson 59 Making Reports Pivotable Part 7 Charting and SmartArt Lesson 60 Creating and Formatting a Chart Lesson 61 Adding or Removing Chart Data Lesson 62 Interactive Chart Lesson 63 Charting Differing Orders of Magnitude Lesson 64 Pie Chart Tricks, Histograms Lesson 65 XY and Bubble Charts Lesson 66 Saving Favorite Chart Settings Lesson 67 Creating Smart Art Lesson 68 Formulas in Smart Art Part 8 Formulas Lesson 69 AutoSum Tricks Lesson 70 Formula Auditing Lesson 71 Three Ways of Entering Formulas Lesson 72 Relative Versus Absolute Formulas Lesson 73 Discovering Functions Lesson 74 Goal Seek Lesson 75 Unusual Functions Lesson 76 IF Formulas Lesson 77 VLOOKUP Lesson 78 IFERROR Lesson 79 SUMIFS Lesson 80 Super Formulas Lesson 81 Date and Time Part 9 Macros Lesson 82 Recording a Macro Lesson 83 Recording Navigation Lesson 84 How Many Rows? Lesson 85 Event Macros Part 10 Tips and Tricks Lesson 86 Finding Records Lesson 87 Web Queries Lesson 88 Copying Column Widths Lesson 89 Page Numbering Lesson 90 Turn Data Lesson 91 & in Header Lesson 92 Choose from a List Lesson 93 Tool Tips for a Cell Lesson 94 Word Processing in Excel Lesson 95 Have a Talk with Excel

Details >>