Cover

Table of Contents

Chapter 1: Working with Excel

Getting to Know Excel

Build a Spreadsheet

Manipulate Data

Start Excel

Tour the Excel Window

Work with Excel’s Ribbon

Customise the Quick Access Toolbar

Customise the Ribbon

Display the Customize Ribbon Tab

Add a New Tab or Group

Add a Command

Work with Smart Tags

Change the View

Switch to Page Layout View

Switch to Page Break Preview

Switch to Normal View

Configure Excel Options

Chapter 2: Entering and Editing Excel Data

Learning the Layout of a Worksheet

Understanding the Types of Data You Can Use

Enter Text into a Cell

Enter a Number into a Cell

Insert a Symbol

Edit Cell Data

Delete Data from a Cell

Delete Cell Data

Undo Cell Data Deletion

Chapter 3: Working with Excel Ranges

Select a Range

Select a Rectangular Range

Select a Range of Individual Cells

Select an Entire Row

Select an Entire Column

Fill a Range with the Same Data

Fill a Vertical or Horizontal Range

Fill a Selected Range

Fill a Range Without Copying Formatting

Fill a Range with a Series of Values

AutoFill a Series of Numeric, Date, or Alphanumeric Values

Fill a Custom Series of Values

Create an AutoFill Series

Move or Copy a Range

Move a Range

Copy a Range

Insert a Row or Column

Insert a Row

Insert a Column

Insert a Cell or Range

Delete Data from a Range

Delete Range Data

Undo Range Data Deletion

Delete a Range

Hide a Row or Column

Hide a Row

Hide a Column

Freeze Rows or Columns

Freeze Rows

Freeze Columns

Merge Two or More Cells

Transpose Rows and Columns

Define or Change a Range Name

Define a Range Name

Change a Range Name

Use Worksheet Text to Define a Range Name

Navigate a Workbook Using Range Names

Navigate Using the Name Box

Navigate Using the Go To Command

Navigate to a Range in Another Workbook

Chapter 4: Formatting Excel Ranges

Change the Font and Font Size

Apply Font Effects

Change the Font Colour

Select a Theme or Standard Colour

Select a Custom Colour

Align Text Within a Cell

Align Text Horizontally

Align Text Vertically

Centre Text Across Multiple Columns

Rotate Text Within a Cell

Add a Background Colour to a Range

Select a Theme or Standard Colour

Select a Custom Colour

Apply a Number Format

Apply a Basic Format

Use the Format Cells Dialog Box

Apply an AutoFormat to a Range

Apply a Conditional Format to a Range

Apply a Style to a Range

Change the Column Width

Change the Width of One Column

Change the Width of All Columns

Change the Row Height

Wrap Text Within a Cell

View Text Without Turning on Text Wrapping

Add Borders to a Range

Chapter 5: Building Formulas and Functions

Understanding Excel Formulas

Build a Formula

Understanding Excel Functions

Add a Function to a Formula

Add a Row or Column of Numbers

Build an AutoSum Formula

Add a Range Name to a Formula

Reference Another Worksheet Range in a Formula

Move or Copy a Formula

Move a Formula

Copy a Formula

Chapter 6: Manipulating Excel Worksheets

Create a New Worksheet

Insert a Blank Worksheet

Insert a Worksheet from a Template

Move a Worksheet

Move a Worksheet Between Workbooks

Move a Worksheet Within the Same Workbook

Copy and Rename a Worksheet

Copy a Worksheet

Rename a Worksheet

Delete a Worksheet

Change the Gridline Colour

Toggle Worksheet Gridlines On and Off

Toggle Worksheet Headings On and Off

Chapter 7: Dealing with Excel Workbooks

Create a New Blank Workbook

Create a New Workbook from a Template

Save a Workbook

Open a Workbook

Arrange Workbook Windows

Find and Replace Text in a Workbook

Find Text in a Workbook

Replace Text in a Workbook

Modify Workbook Colours

Apply a Colour Scheme

Create a Colour Scheme

Set Workbook Fonts

Apply Fonts to a Workbook

Create a Font Scheme

Choose Workbook Effects

Apply a Workbook Theme

Apply a Workbook Theme

Create a Workbook Theme

Chapter 8: Analysing Excel Data

Sort a Range

Filter a Range

Apply a Filter

Create More Sophisticated Filters

Set Data Validation Rules

Convert a Range to a Table

Create a Data Table

Summarise Data with Subtotals

Chapter 9: Visualising Data with Excel Charts

Examining Chart Elements

Understanding Chart Types

Create a Chart

Add Chart Titles

Add Data Labels

Position the Chart Legend

Display Chart Gridlines

Display a Data Table

Change the Chart Layout and Style

Format Chart Elements

Format Chart Elements Using the Format Dialog Box

Format Chart Elements Using the Ribbon

Customise a Chart Element Background

Apply a Colour Fill

Apply a Gradient Fill

Apply a Texture Fill

Apply a Picture Fill

Set a Chart Element’s Outline

Apply a Rounded Outline to a Chart

Add Effects to a Chart Element

Add a Shadow Effect

Add a Glow Effect

Add a Soft Edges Effect

Add a Bevel Effect

Apply a Style to a Chart Element

Chapter 10: Collaborating with Other People

Add a Comment to a Cell

Add a Comment

View a Comment

Protect a Workbook

Protect a Worksheet’s Data

Protect a Workbook’s Structure and Windows

Share a Workbook with Other Users

See Users with the Workbook Open

Track Workbook Changes

Accept or Reject Workbook Changes

Make a Workbook Compatible with Earlier Versions of Excel

Collaborate on a Workbook Online

Save a Workbook to Your Windows Live SkyDrive

Allow Another User Access to Your Windows Live SkyDrive

Simply Excel^® 2010

by Paul McFedries

First published under the title Excel 2010 Simplified,
ISBN 978-0-470-57763-9 by Wiley Publishing, Inc.,
10475 Crosspoint Boulevard, Indianapolis, IN 46256

This edition first published 2010.

Registered office

John Wiley & Sons Ltd, The Atrium, Southern Gate, Chichester, West Sussex, PO19 8SQ, United Kingdom

For details of our global editorial offices, for customer services and for information about how to apply for permission to reuse the copyright material in this book please see our website at www.wiley.com.

The right of the author to be identified as the author of this work has been asserted in accordance with the Copyright, Designs and Patents Act 1988.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, except as permitted by the UK Copyright, Designs and Patents Act 1988, without the prior permission of the publisher.

Microsoft product screenshots reprinted with permission from Microsoft Corporation.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Designations used by companies to distinguish their products are often claimed as trademarks. All brand names and product names used in this book are trade names, service marks, trademarks or registered trademarks of their respective owners. The publisher is not associated with any product or vendor mentioned in this book. This publication is designed to provide accurate and authoritative information in regard to the subject matter covered. It is sold on the understanding that the publisher is not engaged in rendering professional services. If professional advice or other expert assistance is required, the services of a competent professional should be sought.

All prices correct at time of going to press. Please check appropriate website for current details.

All website information was correct at the time of going to press. Websites do constantly update their privacy settings and policies. Please check the relevant website homepage to find their current policies.

ISBN 978-0-470-71131-6

A catalogue record for this book is available from the British Library.

Printed in Italy by Printer Trento

Permissions

Corbis Digital Stock
Digital Vision
PhotoDisc/Getty Images
Purestock

Publisher’s Acknowledgements

Some of the people who helped bring this book to market include the following:

Editorial and Production

VP Consumer and Technology Publishing Director: Michelle Leete

Associate Director – Book Content Management: Martin Tribe

Associate Publisher: Chris Webb

Executive Commissioning Editor: Birgit Gruber

Publishing Assistant: Ellie Scott

Production Manager: Amie Jackowski Tibble

Project Editor: Juliet Booker

Development Editor: Shena Deuchars

Marketing:

Senior Marketing Manager: Louise Breinholt

Marketing Executive: Chloe Tunnicliffe

Composition Services:

Layout: Beth Brooks, Andrea Hornberger

Series Designer: Patrick Cunningham

Indexer: Potomac Indexing, LLC

About the Author

Paul McFedries is a technical writer who has been authoring computer books since 1991. He has more than 60 books to his credit, which together have sold more than three million copies worldwide. These books include the Wiley titles Teach Yourself VISUALLY Excel 2010, Teach Yourself VISUALLY Microsoft Office 2008 for Mac, Excel 2010 Visual Quick Tips, and Excel 2010 PivotTable and PivotCharts Visual Blueprint. Paul also runs Word Spy, a Web site dedicated to tracking new words and phrases (see www.wordspy.com). Please visit Paul’s personal Web site at www.mcfedries.com.

Author’s Acknowledgments

The book you hold in your hands is not only an excellent learning tool, but it is truly beautiful, as well. I am happy to have supplied the text that you will read, but the gorgeous layout comes from Wiley’s crack team of designers and screen artists. The layout of the tasks, the accuracy of the spelling and grammar, and the veracity of the information are all the result of hard work performed by project editor Lynn Northrup and technical editor Namir Shammas. Thanks to both of you for your excellent work. My thanks, as well, to executive editor Jody Lefevere for asking me to write this book.

How to Use This Book

Who Needs This Book

This book is for the reader who has never used this particular technology or software application. It is also for readers who want to expand their knowledge.

The Conventions in This Book

Steps

This book uses a step-by-step format to guide you easily through each task. Numbered steps are actions you must do; bulleted steps clarify a point, step, or optional feature; and indented steps give you the result.

Notes

Notes give additional information – special conditions that may occur during an operation, a situation that you want to avoid, or a cross reference to a related area of the book.

Icons and Buttons

Icons and buttons show you exactly what you need to click to perform a step.

Tip and Warning Icons

Tips offer additional information, including tips, hints, and tricks. You can use the tip information to go beyond what you have learned in the steps. Warnings tell you about solutions to common problems and general pitfalls to avoid.

Bold

Bold type shows command names, options, and text or numbers you must type.

Italics

Italic type introduces and defines a new term.

Simply Excel® 2010

Simply Excel^® 2010