|
Brilliant Excel 2007 Tips & Tricks
This book provides the intermediate/experienced Excel user with everything they need to build on their existing Excel skills to more effectively use and develop all of the more advanced features and functions of Excel 2007. All over the world, Excel developers, power-users and gurus have been working overtime to uncover the hottest new tips, tricks,
and tweaks. Now all of these have been collected in one place: Brilliant Microsoft Excel 2007 Tips & Tricks. Here you will find tips and shortcuts from Excel professionals, instructors, and power users; the best of solutions scoured from the Web (so you won't have to). Only the most valuable tips techniques and tweaks made the cut to improve your efficiency, take total control of Excel 2007 and avoid, troubleshoot and fix problems; make Excel work better, faster, smarter and safer. This book is targeted specifically at users of previous versions of Excel who are ready to take their learning to a new level and to develop their skills to harness the power of the new features of Excel 2007. The book begins by introducing all of the new features and functions of Excel 2007.The reader will then go on to learn what's new to this version of Excel and will refine and further their existing Excel skills. Most readers of this book will be developing databases/spreadsheets, macros and applications for themselves and one or two other users, and most will have no previous development experience. The book covers all topics necessary to take advantage of Excel 2007's rich set of features. Much of the book will be devoted to topics such as tables, queries, forms, reports, macros, beginning VBA, beginning security, and introduction to Excel and the Internet and an introduction to Excel and SharePoint. The teaching style of the book is clear and succinct with no superfluous material. It straddles the market between end-user and developer books, meeting the needs of those who want more than an end-user level book delivers, but who don't want or need a developer book. Author Information: Greg Holden (Chicago, IL) has written more than 30 books on computers and the Internet, including Introducing MS Expression Studio; Starting an Online Business for Dummies, and How to Do Everything with Your eBay Business.He is founder and owner of Stylus Media, his own small Internet-based business. Brilliant Excel 2007 Tips & Tricks: *Provides expert information for the intermediate to advanced Excel user that is not available in any other book. *Provides the reader with all of the new tips and tricks that are available with Excel 2007 whilst reinforcing and improving their basic Excel skills.
Contents:
Table of Contents Part I: Tables, Charts, and Queries Chapter 1: Improving Tables (14 pp.) Introduction Tables in Excel 2007 BOXOUT: Formatting in Excel 2007y BOXOUT: Understanding Data Mining TASK: Preserving Leading Zeros When Importing Data TASK: Converting SSN and other Numbers to the Proper TASK: Changing Names to the Proper Format TASK: Converting Dates to the Proper Format TASK: Uniform Formatting for E-Mail Addresses TASK: Formatting Phone Numbers with a Formula TASK: Data Mining with Excel 2007 TASK: Watching Cells that are Not Currently on Screen TASK: Auditing a Worksheet Chapter 2: Queries (16 pp.) Introduction Working with Queries BOXOUT: Understanding Queries TASK: Run a query in the background TASK: Customize a parameter query TASK: Change the custom prompt for a query TASK: Use cell data as query parameters TASK: Use a constant parameter value for a query TASK: Building Your Own Query TASK: Using Microsoft Query to Retrieve Data Chapter 3: Forms (19 pp.) Introduction Data Forms and Excel 2007 TASK: Configuring Excel 2007 to Use Data Forms TASK: Creating a Blank User Form TASK: Accessing Forms Controls TASK: Adding a List Box TASK: Adding a Combo Box TASK: Inserting a Spinner TASK: Adding a Scroll Bar TASK: Using Offset and Scroll Link in Scroll Bars TASK: Creating a Custom Dialog Box Part II: Reports Chapter 4: PivotChart and PivotTable Reports (21pp.) Introduction How PivotCharts and PivotTables Present Data BOXOUT: Comparing PivotCharts and PivotTables PivotTables TASK: Uncovering Business Intelligence with PivotTables TASK: Filtering Data for a PivotTable Report TASK: Styling a PivotTable Report TASK: Choosing a Report Layout TASK: Changing the Summary Type of a PivotTable TASK: Refreshing PivotTable Report Data TASK: Shaping PivotTable Report Data PivotChart Reports TASK: Creating a Pivot Chart Report from a PivotTable Report TASK: Choosing the Right PivotChart Format TASK: Customizing a PivotChart Report TASK: Relocating a PivotChart Report to a Worksheet TASK: Refreshing a PivotChart Report with New Data Chapter 5: Formulas and Functions in Reports (19 pp.) Introduction Working with Formulas and Functions Formulas TASK: Adding a Formula Field to a Report TASK: Creating a Custom Calculation TASK: Formatting Data Using Custom Formulas TASK: Taking Advantage of Formula AutoComplete Functions TASK: Sorting Text TASK: Simple Iteration TASK: Modifying an Aggregate Function Chapter 6: Tables and Charts in Reports (18 pp.) Introduction Using Reports to Present Data in Table and Chart Form Tables TASK: Creating a Spreadsheet Report TASK: Sorting Spreadsheet Report Data TASK: Rearranging Report Columns TASK: Automatically Refreshing Report Data TASK: Using Conditional Formatting with Reports Charts TASK: Using Charts to Find Business Intelligence TASK: Applying Picture and Texture fills TASK: Combining Chart Types TASK: Modifying Pivot Charts Part III: Sharing Information Chapter 7: Beginning Security (20 pp.) Introduction Security Problems and Microsoft Excel BOXOUT: Security Concerns and Microsoft Excel TASK: Locking Elements within a Workbook TASK: Understanding Macro Security Settings in Detail TASK: Recovering Lost or Forgotten Excel 2007 Passwords TASK: Keeping Excel Services Secure Chapter 8: Excel and the Internet (22 pp.) Introduction Sharing with Excel Online TASK: Working with Excel Web Access TASK: Retrieving Data with the Web Data Add-In TASK: Adding an Excel Services Web Part to a Web Page TASK: Connecting a List View Part to Excel Web Access TASK: Saving an Excel file as a PDF TASK: Gathering Data from a Web Query TASK: Refreshing a Web Query Chapter 9: Excel and SharePoint (23 pp.) Introduction Getting Your Excel Data Online BOXOUT: What Is Microsoft SharePoint? TASK: Working with Excel Services TASK: Understanding Excel Services TASK: Enabling Excel Services on Your Share Point Site TASK: Working with User-Defined Functions TASK: Calling Excel Services from a SharePoint Web Part TASK: Handling Data Types with Excel Services TASK: Scaling Spreadsheets with User-Defined Functions for the Web TASK: Printing with Excel Services TASK: Refreshing Data TASK: Selecting which content to publish on a SharePoint Site TASK: Letting Others Enter Parameters in a Calculator Chapter 10: Working with Document Workspaces (22 pp.) Introduction All About Document Workspaces BOXOUT: Using the Document Management Server TASK: Create a Document Workspace from within Office TASK: Create a Document Workspace site from a library file TASK: Create a Document Workspace site by e-mail TASK: Create a Document Workspace site with a Web browser TASK: Add members to a Document Workspace site TASK: Connect to a Document Workspace site from within Excel TASK: Checking on a Document's Status TASK: Reviewing Current Tasks Part IV: Working Faster and Smarter Chapter 11: Customizing the Interface (24 pp.) Introduction Why Streamline the Interface? BOXOUT: Pros and Cons of the Ribbon TASK: Customizing Excel's Ribbon TASK: Selecting Cells Quickly TASK: Hiding User Interface Elements TASK: Steamlining Data Entry TASK: Making Times Exceed 24 Hours TASK: Adding a Frame to a Range TASK: Entering Special Characters Chapter 12: Boosting Performance (26 pp.) Introduction BOXOUT: Performance Issues in Excel 2007 TASK: Customizing Excel TASK: Exploring Quick Access Toolbar Customization Options TASK: Controlling Calculation Options TASK: Speeding Up Workbook Calculations TASK: Uncovering Calculation Slowdowns TASK: Reducing the Number of Calculation Threads TASK: Blocking Macro Attacks TASK: Exporting Data to Microsoft Word TASK: Sharing a Workbook Chapter 13: Macros and Shortcuts (19pp.) Introduction Pros and Cons of Working with Macros BOXOUT: Keyboard Shortcuts in Excel 2007 TASK: Creating a Macro TASK: Copying a Macro Module TASK: Adding a Digital Signature to a Macro Project TASK: Working with the Shortcut Menu TASK: Automatically Filling Worksheet Cells with Data TASK: Editing the Spelling or Grammar Checker Chapter 14: Working With Excel 2007 Add-Ins (20 pp.) Introduction Add-Ins and Excel 2007 BOXOUT: Using Excel's Built-In Add-Ins TASK: Loading the Solver TASK: Loading the Analysis ToolPak TASK: Working with the Conditional Sum Wizard BOXOUT: Installing Microsoft Add-Ins TASK: Adding a New Tab to the Ribbon TASK: Synchronizing Tables TASK: Retrieving Web Data BOXOUT: Installing Third-Party Add-Ins TASK: Viewing with a Fractal Map TASK: Merging Cells TASK: Editing with Extools TASK: Searching and Replacing Chapter 15: Beginning VBA (17 pp.) Introduction Getting Started with VBA BOXOUT: VBA and Microsoft Excel TASK: Opening the Visual Basic Editor TASK: Adding Code to a Macro TASK: Adding Comments to VBA Code TASK: Declaring Variables TASK: Performing Range Calculations TASK: Using the Date Picker Add-In Appendix A: Excel 2007 Web Sites Appendix B: Finding Online Excel Discussion Groups Appendix B: Glossary
|
