Brilliant Excel 2007 Advanced Tips & Tricks

Description

When you're working on your computer and come up against a problem that you're unsure how to solve or want to accomplish something in an application that you aren't sure how to do, where do you look? Manuals and traditional training guides are too unwieldy and make it hard to get to the info you need right away, and help-lines are rarely that helpful. Brilliant guides allow you to find the info you need easily and without fuss and guide you through the task using a highly visual, step-by-step approach - providing exactly what you need to know, when you need it.

This book will provide any Excel user with everything that they need to build on their existing Excel skills to more effectively use and develop all of the more advanced features and functions of the Excel 2007 application.

 All over the world, Excel developers, power‑users and gurus have been working overtime to uncover the hottest new 2007 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, safer, and have more fun too!

Brilliant Excel 2007 Tips & Tricks will teach you all of the tips and tricks that will help you use Microsoft Excel 2007 better and more efficiently, but which you don't have time to develop or work out for yourself ‑ learn to do it better and faster from those who know how !

Brilliant Excel 2007 Tips & Tricks will show you how to...

* Create and customize your own Queries
* Improve your Data Mining and Formatting skills
* Improve your use of PivotTables & PivotCharts to present data
* Produce better reports with Formulas and Functions
* Create more useful and informative Charts & Tables
* Share information securely with SharePoint
* Customize your Excel interface
* Boost your Excel performance
* Create new and exciting Macros and Shortcuts
* Get started with VBA

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