Microsoft Office Excel 2007: Top 100 Simplified Tips & Tricks (Top 100 Simplified Tips & Tricks)

259 Pages·2007·29.54 MB·English

by  Denise Etheridge

01_126745 ffirs.qxp 6/5/07 6:58 PM Page i
by Denise Etheridge
02_126745 ftoc.qxp 6/5/07 6:59 PM Page 1
01_126745 ffirs.qxp 6/5/07 6:58 PM Page i
by Denise Etheridge
01_126745 ffirs.qxp 6/5/07 6:58 PM Page ii
Excel® 2007: Top 100
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE
Simplified® Tips & Tricks PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS
OR WARRANTIES WITH RESPECT TO THE ACCURACY OR
COMPLETENESS OF THE CONTENTS OF THIS WORK AND
Published by
SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING
Wiley Publishing, Inc. WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A
111 River Street PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED
Hoboken, NJ 07030-5774 OREXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE
ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE
Published simultaneously in Canada
SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, THE UNDERSTANDING THAT THE PUBLISHER IS NOT
Indiana ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER
PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS
Library of Congress Control Number: 2007926010
REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL
ISBN: 978-0-470-12674-5 PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR
Manufactured in the United States of America THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING
HEREFROM. THE FACT THAT AN ORGANIZATION OR
10 9 8 7 6 5 4 3 2 1
WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION
No part of this publication may be reproduced, stored in a AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION
retrieval system or transmitted in any form or by any means, DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER
electronic, mechanical, photocopying, recording, scanning or ENDORSES THE INFORMATION THE ORGANIZATION OR
otherwise, except as permitted under Sections 107 or 108 of WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY
the 1976 United States Copyright Act, without either the prior MAKE. FURTHER, READERS SHOULD BE AWARE THAT
INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE
written permission of the Publisher, or authorization through
CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK
payment of the appropriate per-copy fee to the Copyright
WAS WRITTEN AND WHEN IT IS READ.
Clearance Center, 222 Rosewood Drive, Danvers, MA 01923,
978-750-8400, fax [phone]. Requests to the Publisher
forpermission should be addressed to the Legal Department, FOR PURPOSES OF ILLUSTRATING THE CONCEPTS AND
Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, TECHNIQUES DESCRIBED IN THIS BOOK, THE AUTHOR HAS
CREATED VARIOUS NAMES, COMPANY NAMES, MAILING,
IN46256, 317-572-3447, fax [phone], online:
E-MAIL AND INTERNET ADDRESSES, PHONE AND FAX
www.wiley.com/go/permissions.
NUMBERS AND SIMILAR INFORMATION, ALL OF WHICH
Trademark Acknowledgments AREFICTITIOUS. ANY RESEMBLANCE OF THESE FICTITIOUS
NAMES, ADDRESSES, PHONE AND FAX NUMBERS AND
Wiley, the Wiley Publishing logo, Visual, the Visual logo, SIMILAR INFORMATION TO ANY ACTUAL PERSON, COMPANY
Simplified, Read Less - Learn More, and related trade dress are AND/OR ORGANIZATION IS UNINTENTIONAL AND PURELY
trademarks or registered trademarks of John Wiley & Sons, Inc. COINCIDENTAL.
and/or its affiliates. Microsoft and Excel are registered trademarks
of Microsoft Corporation in the U.S. and/or other countries. All
other trademarks are the property of their respective owners.
Wiley Publishing, Inc. is not associated with any product or
vendor mentioned in this book.
Contact Us
For general information on our other products and services
contact our Customer Care Department within the U.S. at
800-762-2974, outside the U.S. at 317-572-3993, or fax
317-572-4002.
For technical support please visit www.wiley.com/techsupport.
U.S. Sales
Contact Wiley at
[phone] or
fax [phone].
01_126745 ffirs.qxp 6/5/07 6:58 PM Page iii
P V B
RAISE FOR ISUAL OOKS
“I have to praise you and your company on the “Like a lot of other people, I understand things
fine products you turn out. I have twelve Visual best when I see them visually. Your books really
books in my house. They were instrumental in make learning easy and life more fun.”
helping me pass a difficult computer course. John T. Frey (Cadillac, MI)
Thank you for creating books that are easy to
follow. Keep turning out those quality books.” “I have quite a few of your Visual books and have
Gordon Justin (Brielle, NJ) been very pleased with all of them. I love the way
the lessons are presented!”
“What fantastic teaching books you have Mary Jane Newman (Yorba Linda, CA)
produced! Congratulations to you and your staff.
You deserve the Nobel prize in Education. Thanks “Thank you, thank you, thank you...for making it
for helping me understand computers.” so easy for me to break into this high-tech world.”
Bruno Tonon (Melbourne, Australia) Gay O’Donnell (Calgary, Alberta,Canada)
“A Picture Is Worth A Thousand Words! If your “I write to extend my thanks and appreciation for
learning method is by observing or hands-on your books. They are clear, easy to follow, and
training, this is the book for you!” straight to the point. Keep up the good work! I
Lorri Pegan-Durastante (Wickliffe, OH) bought several of your books and they are just
right! No regrets! I will always buy your books
“Over time, I have bought a number of your because they are the best.”
‘Read Less - Learn More’ books. For me, they are Seward Kollie (Dakar, Senegal)
THE way to learn anything easily. I learn easiest
using your method of teaching.” “I would like to take this time to thank you and
José A. Mazón (Cuba, NY) your company for producing great and easy-to-
learn products. I bought two of your books from a
“You’ve got a fan for life!! Thanks so much!!” local bookstore, and it was the best investment
Kevin P. Quinn (Oakland, CA) I’ve ever made! Thank you for thinking of us
ordinary people.”
“I have several books from the Visual series and Jeff Eastman (West Des Moines, IA)
have always found them to be valuable
resources.” “Compliments to the chef!! Your books are
Stephen P. Miller (Ballston Spa, NY) extraordinary! Or, simply put, extra-ordinary,
meaning way above the rest! THANKYOU
“I have several of your Visual books and they are THANKYOU THANKYOU! I buy them for friends,
the best I have ever used.” family, and colleagues.”
Stanley Clark (Crawfordville, FL) Christine J. Manfrin (Castle Rock, CO)
01_126745 ffirs.qxp 6/5/07 6:58 PM Page iv
C
REDITS
Project Editor Layout
Sarah Hellert Carrie A. Foster
Jennifer Mayberry
Acquisitions Editor Heather Pope
Jody Lefevere Amanda Spagnuolo
Copy Editor Screen Artist
Kim Heusel Jill A. Proll
Technical Editor Proofreader
Suzanne Borys, PhD Broccoli Information Management
James Floyd Kelly
Quality Control
Editorial Manager Cynthia Fields
Robyn Siesky Jessica Kramer
Charles Spencer
Business Manager
Amy Knies Indexer
Infodex Indexing Services, Inc.
Editorial Assistant
Laura Sinise Wiley Bicentennial Logo
Richard J. Pacifico
Manufacturing
Allan Conley Special Help
Linda Cook Malinda McCain
Paul Gilchrist Barbara Moore
Jennifer Guynn Christine Williams
Book Design Vice President and Executive
Kathie Rickard Group Publisher
Richard Swadley
Production Coordinator
Erin Smith Vice President and Publisher
Barry Pruett
Composition Director
Debbie Stailey
A A
BOUT THE UTHOR
Denise Etheridge is a certified public accountant as well as
the president and founder of Baycon Group, Inc. She publishes
Web sites, provides consulting services on accounting-related
software, and authors computer-related books. You can visit
www.baycongroup.com to view her online tutorials.
This book is dedicated to my mother, Catherine Austin Etheridge
01_126745 ffirs.qxp 6/5/07 6:58 PM Page v
H T U T B
OW O SE HIS OOK
Excel 2007: Top 100 Simplified®Tips & Tricks includes 100 tasks that reveal cool secrets, teach timesaving tricks, and
explain great tips guaranteed to make you more productive with Excel. The easy-to-use layout lets you work
through all the tasks from beginning to end or jump in at random.
Who is this book for?
You already know Excel basics. Now you’d like to go beyond, with shortcuts, tricks, and tips that let you work
smarter and faster. And because you learn more easily when someone shows you how, this is the book for you.
Conventions Used In This Book
1
Steps 33
This book uses step-by-step
44
instructions to guide you easily
through each task. Numbered
callouts on every screen shot show
you exactly how to perform each
task, step by step.
2
Tips 11
Practical tips provide insights to
save you time and trouble, caution 22
you about hazards to avoid, and
reveal how to do things in Excel
2007 that you never thought
possible!
3
Task Numbers
Task numbers from 1 to 100
indicate which lesson you are
working on.
4 Difficulty Levels Demonstrates a new spin on a
common task
For quick reference, the symbols
Introduces a new skill or a new
below mark the difficulty level of
task
each task.
Combines multiple skills requiring
in-depth knowledge
Requires extensive skill and may
involve other technologies
02_126745 ftoc.qxp 6/5/07 6:59 PM Page vi
Table of Contents
1
Boost Your Efficiency
#1 Validate with a Validation List 4
#2 Validate with Data Entry Rules 6
#3 Extend a Series with AutoFill 8
#4 Insert Symbols or Special Characters 10
#5 Hide Rows by Grouping and Outlining 12
#6 Find and Replace Formats 14
#7 Add Comments to Your Worksheet 16
#8 Let Excel Read Back Your Data 18
#9 Create Your Own Sort or AutoFill 20
2
Work with Formulas and Functions
#10 Enter Formulas Using a Variety of Methods 24
#11 Name Cells and Ranges 26
#12 Define a Constant 28
#13 Create Formulas That Include Names 30
#14 Calculate with the Function Wizard 32
#15 Figure Out Loan Terms 34
#16 Determine the Internal Rate of Return 36
#17 Determine the Nth Largest Value 38
#18 Create a Conditional Formula 40
#19 Calculate a Conditional Sum 42
#20 Add a Calculator 44
#21 Find Products and Square Roots 46
#22 Perform Time Calculations 48
#23 Perform Date Calculations 50
vi
02_126745 ftoc.qxp 6/5/07 6:59 PM Page vii
3
Copy, Format, and More
#24 Check Your Formulas for Errors 54
#25 Trace Precedents and Dependents 56
#26 Change Text to Numbers 58
#27 Convert a Row to a Column 60
#28 Copy with the Office Clipboard 62
#29 Adjust Column Widths with Paste Special 64
#30 Specify How to Paste with Paste Special 66
#31 Create Your Own Style 68
#32 Copy Styles to Another Workbook 70
#33 Conditionally Format Your Worksheet 72
#34 Track Changes While Editing 76
#35 Consolidate Worksheets 78
4
Manipulate Records
#36 Enter Data with a Form 82
#37 Filter Duplicate Records 84
#38 Perform Simple Sorts and Filters 86
#39 Perform Complex Sorts 88
#40 Sort by Cell Color, Font Color, or Icon 90
#41 Perform Complex Filters 92
#42 Filter by Multiple Criteria 94
#43 Subtotal Sorted Data 96
#44 Chart Filtered Data Easily 98
#45 Count Filtered Records 100
#46 Look Up Information in Your Worksheet 102
#47 Define Data as a Table 104
#48 Modify a Table Style 106
vii