Always Learning

Advanced Search

Brilliant Microsoft Excel 2007 Tips & Tricks

Brilliant Microsoft Excel 2007 Tips & Tricks

Greg Holden

Dec 2008, Paperback, 304 pages
ISBN13: 9780273719342
ISBN10: 0273719343
Special online offer - Save 30%
Was 19.99, Now 13.99Save: 6.00
  • Print pagePrint page
  • Email this pageEmail page
  • Share

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.

Table of Contents

Part I: Tables, Charts, and Queries

Chapter 1: Improving Tables (14 pp.)


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


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


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


How PivotCharts and PivotTables Present Data

BOXOUT: Comparing PivotCharts and 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.)


Working with Formulas and Functions


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


TASK: Sorting Text

TASK: Simple Iteration

TASK: Modifying an Aggregate Function

Chapter 6: Tables and Charts in Reports (18 pp.)


Using Reports to Present Data in Table and Chart Form


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


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


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


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


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


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


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


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


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


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


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

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.