Brilliant Microsoft Excel 2007 Advanced Tips & Tricks

DESCRIPTION

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.

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.

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 Documents 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 Excels 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 Excels 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