Special Edition Using Microsoft Office Excel 2007 (Adobe Reader)

This book unfortunately leaves a lot to be desired in order to leave up to its proclaimed sub-title as "the only Excel book you'll ever need". For an Excel book, this one has a few key shortcomings: - no data to practice the examples with: any serious Excel book should make available to the reader the data that the examples in the book are based on - many readers would be interested in actually practicing the examples - way too many errors, even for a first edition - one of the most annoying type of errors are the ones that point to figures that don't exist or the wrong figures altogether - too many pages in a row filled with endless lists of functions or other attributes - instead of focusing on the most popular ones in the narrative and provide the whole list in an appendix This book has some positives as well, such as a comprehensive treatment of topics in general and perhaps the most comprehensive treatment of Excel functions I have come across. However, these positive aspects are far outweighed by the negative aspects I mentioned above. Contents......Page 4 Introduction......Page 30 The Massive Grid......Page 31 Fantastic Data Visualizations......Page 32 Where Are My Menus and Toolbars?......Page 35 Why Couldn’t They Leave Well Enough Alone?......Page 36 The Most Requested New Features......Page 37 A Scientific Approach to Popular Features......Page 38 Introducing the Ribbon......Page 40 Ribbon Limitations......Page 41 Ribbon Tricks......Page 42 Live Preview......Page 43 The Journey......Page 44 Special Elements......Page 45 I: Mastering the New User Interface......Page 48 1 Introducing the Ribbon User Interface......Page 50 The Excel 2007 Interface......Page 51 Using the Office Icon Menu......Page 52 Understanding the Ribbon User Interface......Page 54 Finding Old Menu Items in the New Interface......Page 60 2 The Quick Access Toolbar......Page 70 Changing the Location of the Quick Access Toolbar......Page 71 Adding Your Favorite Commands to the Quick Access Toolbar......Page 72 Customizing the Quick Access Toolbar......Page 74 3 The Mini Toolbar and Other U.I. Improvements......Page 82 Using the Mini Toolbar to Format Selected Text......Page 83 Expanding the Formula Bar......Page 87 Zooming In and Out on a Worksheet......Page 88 Using the Status Bar to Add Numbers......Page 89 Switching Between Normal View, Page Break Preview, and Page Layout View Modes......Page 90 Using the New Sheet Icon to Add Worksheets......Page 92 4 Keyboard Shortcuts......Page 94 Using the New Keyboard Accelerators......Page 95 Using the Shortcut Keys......Page 99 Using Excel 2003 Keyboard Accelerators......Page 105 5 Galleries, Live Preview, and Themes......Page 116 Using Galleries......Page 117 Using Live Preview......Page 119 Understanding Themes......Page 120 6 The Excel Options Dialog......Page 132 Introducing the Excel Options Dialog......Page 133 Toggling the New Excel 2007 Features......Page 136 Controlling the Look and Feel of the Worksheet Window......Page 137 Guide to Excel Options......Page 141 II: A Tour of What’s New......Page 148 7 The Big Grid......Page 150 Excel 2007 Grid Limits......Page 151 Other Limits in Excel 2007......Page 154 Tips for Navigating the Big Grid......Page 156 8 Fabulous Table Intelligence......Page 160 Defining Suitable Data for Excel Tables......Page 161 Keeping Headers in View......Page 162 Adding a Total Row to a Table......Page 166 Expanding a Table......Page 167 Adding New Formulas to Tables......Page 168 Selecting Only the Data in the Column......Page 171 Using Table Data for Charts to Ensure Stickiness......Page 174 Replacing Named Ranges with Table References......Page 175 Creating Banded Rows and Columns with Table Styles......Page 178 Dealing with the AutoFilter Drop-Downs......Page 181 9 Visualizing Data in Excel......Page 182 Creating In-Cell Bar Charts with Data Bars......Page 184 Creating Heat Maps with Color Scales......Page 187 Using Stop if True to Improve All Conditional Formatting......Page 193 Using the Top/Bottom Rules......Page 195 Using the Highlight Cells Rules......Page 197 Tweaking Rules with Advanced Formatting......Page 203 Combining Rules......Page 214 Clearing Conditional Formats......Page 215 Special Considerations for Tables......Page 216 10 Using Pivot Tables to Analyze Data......Page 217 Preparing Your Data for Pivot Table Analysis......Page 221 Creating Your First Pivot Table......Page 229 Changing the Default Layout of a Pivot Table......Page 231 Adding New Fields to a Pivot Table......Page 237 Eliminating Blank Cells from a Pivot Table......Page 238 Using Pivot Table Legacy Mode......Page 239 Pivot Table Limitations......Page 240 11 Formatting Pivot Tables......Page 244 The New Compact View......Page 245 Report Layout Options......Page 249 Special Considerations When Adding Data Fields to the Value Section......Page 253 Formatting a Pivot Table......Page 256 12 Pivot Table Data Crunching for Excel 2007......Page 266 Grouping Daily Dates to Months and Years......Page 267 Sorting and Filtering......Page 268 Exploring Even More Filtering Options......Page 275 Adding New Items Along a Dimension......Page 284 Showing Percentages Instead of Values......Page 287 Exploring Interesting Uses for Pivot Tables......Page 292 Finding More Information on Pivot Tables......Page 298 13 Removing Duplicates and Filtering......Page 300 Using Remove Duplicates to Find Unique Values......Page 301 Filtering Records......Page 304 Using the Advanced Filter Command......Page 313 14 Sorting Data......Page 316 Introducing the New Sort Dialog......Page 317 Using Specialized Sorting......Page 318 One-Click Sorting......Page 323 Sorting Randomly......Page 324 15 Using Excel Charts......Page 328 Understanding the Components of a Chart......Page 329 Setting Up Your Data for Charting......Page 330 Inserting a Chart by Choosing a Chart Type......Page 331 Choosing a Chart Layout to Further Customize the Chart Type......Page 336 Customizing a Chart by Using a Chart Style or Theme......Page 337 Charting Tricks and Techniques......Page 341 Using Pivot Charts......Page 352 16 Using SmartArt, Shapes, WordArt, and Text Boxes......Page 356 Using SmartArt......Page 357 Using Shapes to Display Cell Contents......Page 371 Using WordArt for Interesting Titles and Headlines......Page 374 Using Text Boxes to Flow Long Text Passages......Page 376 17 Using Pictures and Clip Art......Page 380 Using Pictures on Worksheets......Page 381 Using Clip Art......Page 389 III: Working in a Legacy Environment......Page 392 18 File Format Differences......Page 394 A Brief History of File Formats......Page 395 Using the New XML File Formats: XLSX and XLSM......Page 397 The Future of File Formats......Page 399 19 Working with Prior Versions of Excel......Page 400 Opening Excel 2007 Files in Previous Versions of Excel......Page 401 Creating Excel 2007 File Formats in Excel 2003......Page 405 Opening Excel 2003 Files in Excel 2007......Page 406 Preparing to Save Excel 2007 Files for Excel 97 Through Excel 2003......Page 407 The Digital Divide......Page 410 IV: Calculating with Excel......Page 412 20 Understanding Formulas......Page 414 Getting the Most from This Chapter......Page 415 Introduction to Formulas......Page 416 Entering Your First Formula......Page 417 Three Methods of Entering Formulas......Page 424 Entering the Same Formula in Many Cells......Page 427 Copying a Formula by Using the Table Tool......Page 431 21 Controlling Formulas......Page 438 Formula Operators......Page 439 Understanding Error Messages in Formulas......Page 443 Using Formulas to Join Text......Page 444 Copying Versus Cutting a Formula......Page 447 Automatically Formatting Formula Cells......Page 448 Using Date Math......Page 450 Troubleshooting Formulas......Page 452 22 Understanding Functions......Page 460 Working with Functions......Page 461 Getting Help with Excel Functions......Page 465 Using AutoSum......Page 467 Using the New General-Purpose Functions in Excel 2007......Page 472 Using the New CUBE Functions in Excel 2007......Page 476 Using the Former ATP Functions......Page 477 The Function Reference Chapters......Page 478 23 Using Everyday Functions: Math, Date and Time, and Text Functions......Page 480 Examples of Math Functions......Page 487 Dates and Times in Excel......Page 513 Examples of Date and Time Functions......Page 519 Examples of Text Functions......Page 534 24 Using Powerful Functions: Logical, Lookup, and Database Functions......Page 554 Examples of Logical Functions......Page 560 Examples of Information Functions......Page 568 Examples of Lookup and Reference Functions......Page 579 Examples of Database Functions......Page 608 25 Using Financial Functions......Page 616 Examples of Common Household Loan and Investment Functions......Page 621 Examples of Functions for Financial Professionals......Page 627 Examples of Depreciation Functions......Page 632 Functions for Investment Analysis......Page 638 Examples of Functions for Bond Investors......Page 644 Examples of Miscellaneous Financial Functions......Page 656 26 Using Statistical Functions......Page 660 Examples of Functions for Descriptive Statistics......Page 671 Examples of Functions for Regression and Forecasting......Page 687 Examples of Functions for Inferential Statistics......Page 711 Using the Analysis Toolpak to Perform Statistical Analysis......Page 740 27 Using Trig, Matrix, and Engineering Functions......Page 764 A Brief Review of Trigonometry Basics......Page 769 Examples of Logarithm Functions......Page 779 Working with Imaginary Numbers......Page 785 Solving Simultaneous Linear Equations with Matrix Functions......Page 792 Examples of Engineering Functions......Page 799 Using the Analysis Toolpack to Perform Fast Fourier Transforms (FFTs)......Page 823 28 Connecting Worksheets, Workbooks, and External Data......Page 826 Connecting Two Worksheets......Page 827 Connecting to a Worksheet in Another Workbook......Page 829 Connecting to Data on a Web Page......Page 834 Setting Up a Connection to a Text File......Page 838 Setting Up a Connection to an Access Database......Page 841 Setting Up SQL Server, XML, OLE DB, and ODBC Connections......Page 842 Managing Connections......Page 847 29 Using Super Formulas in Excel......Page 852 Using 3D Formulas to Spear Through Many Worksheets......Page 853 Combining Multiple Formulas into One Formula......Page 857 Calculating a Cell Reference in the Formula by Using the INDIRECT Function......Page 859 Assigning a Formula to a Name......Page 862 Replacing Multiple Formulas with One Array Formula......Page 867 30 Using Names in Excel......Page 870 Defining a Name for a Cell by Using the Name Box......Page 871 Naming a Cell by Using the New Name Dialog......Page 872 Using the Name Box for Quick Navigation......Page 873 Using Scope to Allow Duplicate Names in a Workbook......Page 874 Using Named Ranges to Simplify Formulas......Page 875 Managing Names......Page 883 Using a Name to Hold a Value......Page 885 Assigning a Formula to a Name......Page 886 31 Using What If, Scenario Manager, Goal Seek, and Solver......Page 892 Using What-If......Page 893 Using Scenario Manager......Page 896 Using Goal Seek......Page 903 Using Solver......Page 908 V: Formatting and Sharing Information......Page 918 32 Formatting Worksheets......Page 920 Why Format Worksheets?......Page 921 Using Traditional Formatting......Page 922 Formatting with Styles......Page 943 Other Formatting Techniques......Page 944 Copying Formats......Page 950 33 Printing......Page 954 Using Page Layout View......Page 955 Using the Improved Headers and Footers......Page 956 Using the Page Setup and Sheet Options......Page 960 Working with Page Breaks......Page 966 Printing......Page 968 34 Sharing Workbooks with Others......Page 970 Sending a Workbook via Email......Page 971 Creating a PDF from a Worksheet......Page 972 Publishing a Worksheet to a Webpage......Page 973 Publishing to a Server......Page 975 35 More Tips and Tricks for Excel 2007......Page 978 Speeding Up Calculation by Using Multithreaded Calculation......Page 979 Watching the Results of a Far-Off Cell......Page 980 Looking for Hidden Data in a Workbook......Page 981 Opening the Same Files Everyday......Page 982 Downloading Cool Spreadsheets from Office Online......Page 983 Adding Visual Interest by Using a Background Picture......Page 984 Comparing Documents Side-by-Side with Synchronous Scrolling......Page 985 Calculating a Formula in Slow Motion......Page 986 Adding a Digital Signature Line to a Workbook......Page 987 Protecting a Worksheet......Page 988 Sharing a Workbook......Page 990 Adding Automatic Subtotals......Page 991 Translating Text......Page 994 VI: More Power......Page 996 36 Automating Repetitive Functions Using VBA Macros......Page 998 Checking Security Settings Before Using Macros......Page 999 Recording a Macro......Page 1000 Case Study: Macro for Formatting for a Mail Merge......Page 1001 Everyday-Use Macro Example: Formatting an Invoice Register......Page 1008 Understanding VBA Code–An Analogy......Page 1011 Using Simple Variables and Object Variables......Page 1015 Using R1C1-Style Formulas......Page 1016 Customizing the Everyday-Use Macro Example: GetOpenFileName and GetSaveAsFileName......Page 1019 From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges......Page 1020 Combination Macro Example: Creating a Report for Each Customer......Page 1027 Conclusion......Page 1033 37 Interacting with Other Office Applications......Page 1034 Pasting Excel Data to Microsoft OneNote......Page 1035 Pasting Excel Data to PowerPoint......Page 1038 Creating Tables in Excel and Pasting to Word......Page 1040 Pasting Word Data to an Excel Text Box......Page 1041 Using Excel Data in a Word Mail Merge......Page 1042 Building a Pivot Table from Access Queries......Page 1045 38 A Tour of the Best Add-Ins for Excel......Page 1048 Customizing the Ribbon by Using CustomizeRibbon......Page 1049 Doing Access with Excel by Using DigDB......Page 1050 Accessing More Functions by Using MoreFunc.dll......Page 1051 Using Add-ins from AddIns.com......Page 1052 A......Page 1054 C......Page 1055 D......Page 1059 E......Page 1060 F......Page 1061 G......Page 1063 I......Page 1064 K......Page 1065 M......Page 1066 N......Page 1068 P......Page 1069 R......Page 1071 S......Page 1072 T......Page 1074 V......Page 1076 Z......Page 1077 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! 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! 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 .. Assisting experienced users in upgrading to the latest version of the popular spreadsheet program, a comprehensive manual offers an introduction to the new user interface, as well as an overview of all the latest features and functions, integrating graphics into the spreadsheet, PivotTable, calculations, customization, sharing workbooks, macros, and interaction with other Office 2007 applications, accompanied by a host of special tips and tricks. Original. (Intermediate) Easing the upgrade path to the latest version of Microsoft's bestselling spreadsheet program, the author introduces the new interface and details the powerful new features available in Excel 2007. Jelen shows how to create amazing data visualizations using conditional formatting and in-cell data bars