Excel 2010 Just the Steps For Dummies E-Book

Diane Koers

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 285

Veröffentlichungsjahr: 2010

Beschreibung

Pick your Excel task, find it fast, and get it done with Just the Steps! If you want to see how to do a particular Excel task, this is the perfect book. Each page includes step-by-step instruction in one column and illustrations and screenshots in the other column, so you have all the information you need in one place--no flipping pages! Improve your Excel skills with just the steps for entering spreadsheet data, building formulas, protecting excel data, formatting cells, designing spreadsheets with graphics, managing workbooks, changing worksheet values, sorting and filtering data, creating charts, creating PivotTables, building macros, integrating Excel into other Office programs, and--whew!--still more. * Explains essentials tasks for Excel 2010, the spreadsheet application that is part of the Microsoft Office 2010 suite * Shows you just the steps for numerous Excel tasks using an easy-to-follow, two-column page layout, with step-by-step instruction on one side and illustrations and screenshots on the other * Covers entering spreadsheet data, building formulas, protecting excel data, formatting cells, designing spreadsheets with graphics, managing workbooks, changing worksheet values, sorting and filtering data, creating charts, creating PivotTables, and building macros * Walks you step by step through collaborating in Excel and integrating or using Excel with Word, PowerPoint, and Access Make your tasks easier, improve your Excel skills, and get better results with this step-by-step guide.

Leseprobe

Excel® 2010 Just the Steps™ For Dummies®

Table of Contents

Introduction

About This Book

Why You Need This Book

How This Book Is Organized

Part I: Putting Excel to Work

Part II: Sprucing Up Your Spreadsheets

Part III: Viewing Data in Different Ways

Part IV: Analyzing Data with Excel

Part V: Utilizing Excel with Other People and Applications

Part VI: Practical Applications for Excel

Back Cover: Using Excel Shortcut Keys

Get Ready To . . .

Part I: Putting Excel to Work

Chapter 1: Working with Excel Files

Open and Explore Excel

Explore Backstage View

Close Excel

Select Commands with the Keyboard

Change Status Bar Indicators

Create a New Excel File

Save a Workbook

Save a Workbook in a Different Format

Open an Existing Excel File

Convert a Prior Version File to Excel 2010

Delete a File

Rename a File

Specify Workbook Properties

Chapter 2: Entering Spreadsheet Data

Change the Active Cell

Select Multiple Cells

Enter Cell Data

Undo Data Entry

Edit or Delete Cell Data

Copy and Paste Data

Move Data

Drag and Drop Data

Transpose Data

Extend a Series with AutoFill

Add Rows and Columns

Insert Cells

Remove Rows and Columns

Delete Cells

Name a Range of Cells

Use Named Ranges

Manage Range Names

Validate Data Entry

Enter Data in Validated Cells

Locate Cells with Data Validation

Chapter 3: Building Formulas

Create Simple Formulas with Operators

Create Compound Formulas

Add Numbers with AutoSum

Find an Average Value

Copy Formulas with AutoFill

Edit a Formula

Define an Absolute Reference

Copy Values with Paste Special

Summarize an Array

Troubleshoot Formula Errors

Display Formulas in the Cell

Identify Formula Precedents and Dependents

Chapter 4: Using Excel Functions

Build a Formula with the Function Wizard

Manually Type an IF Function

Create Text Functions

Change the Cell Text Case

Use Functions to Calculate Time

Convert a Text Date to a Date Value

Count the Number of Cells Containing Data

Round Values with Math Functions

Locate Data with Lookup Functions

Use the MODE Function

Remove Unwanted Spaces with the TRIM Function

Estimate the Future Value of an Investment

Calculate a Loan Payment

Chapter 5: Protecting Excel Data

Quickly Hide an Open Workbook

Make a File Read-Only

Open a File as Read-Only

Mark a Workbook as Final

Hide Rows and Columns

Unlock Cells

Protect Worksheets

Restrict User Data Entry

Enter Data in a Restricted Area

Inspect for Private Information

Hide Cell Formulas

Assign a File Password

Part II: Sprucing Up Your Spreadsheets

Chapter 6: Formatting Cells

Format Values

Adjust Column Width

Change Row Height

Align Data Horizontally and Vertically

Create a Title by Merging Cells

Change Font Color

Select Font Attributes

Apply Cell Background Colors

Wrap Text in a Cell

Rotate Text

Chapter 7: Applying Additional Formatting Options

Use the Mini Toolbar

Change the Default Font

Place Borders around Cells

Work with Date Formats

Use Dates in a Calculation

Format Telephone, Zip Code, and Social Security Numbers

Copy Formatting

Indent Data in Cells

Use Cell Styles

Format Cells as a Table

Specify Conditional Formatting

Add Data Visualizations

Chapter 8: Designing with Graphics

Illustrate with Arrows

Draw with Shapes

Insert Saved Images

Add Artistic Effects

Use Picture Styles

Select a Transparent Color

Change a Picture

Annotate with Text Boxes

Add Clip Art

Create WordArt

Manipulate Graphics

Crop an Image

Flip an Object

Align Multiple Graphics

Control Object Order

Group Objects

Chapter 9: Managing Workbooks

Insert Additional Worksheets

Delete Worksheets

Rename Worksheets

Copy Worksheets

Move or Copy Worksheets to a Different Workbook

Hide and Unhide Worksheets

Change Worksheet Tab Colors

Generate References to Other Worksheets

Cross-Reference Other Workbooks

Insert a Hyperlink

Part III: Viewing Data in Different Ways

Chapter 10: Changing Worksheet Views

Zoom In or Out

Use the Zoom Controls

Change Worksheet Views

Freeze Worksheet Titles

Split the Excel Screen

Arrange Windows

Compare Spreadsheets

Save as a Template

Open a Template

Create a Workbook from an Existing File

Monitor Cells in the Watch Window

Chapter 11: Sorting Data

Use the Ribbon to Sort

Work with the Sort Command

Sort by Multiple Criteria

Create a Customized List

Sort by Day, Month, or Custom List

Remove Duplicate Records

Sort by Cell Format

Search for Data

Find All Data Occurrences

Locate Cells Based on Format

Use the Replace Command

Chapter 12: Creating Charts

Add Sparklines

Add Sparkline Markers

Change Sparkline Styles

Edit Sparkline Data

Create a Basic Chart

Insert a Chart

Change the Chart Type

Adjust the Chart Location

Display a Chart Title

Customize the Chart Legend

Add a Data Table

Show Data Labels

Select a Chart Color Style

Change Axis Options

Enhance a 3-D Chart

Place a Picture in a Data Series

Adjust Chart Data

Chapter 13: Printing Workbooks

Add a Manual Page Break

Set a Specific Area to Print

Adjust the Paper Orientation and Size

Make Worksheets Fit Better on a Page

Set Page Margins

Specify Repeating Rows and Columns

Print Gridlines and Row and Column Headings

Preview before Printing

Add a Standard Header or Footer

View Other Header and Footer Options

Create a Custom Header or Footer

Print Worksheets and Charts

E-Mail a Workbook

Chapter 14: Saving Time with Excel Tools

Set Excel General Options

Set the Default File Location

Manage AutoCorrect

Examine Advanced Editing Options

Customize the Quick Access Toolbar

Customize the Ribbon

Add Special Characters

Find the Right Word with the Thesaurus

Check Your Spelling

Work with Assistance Tags

Split Data into Multiple Columns

Merge Columns

Part IV: Analyzing Data with Excel

Chapter 15: Working with Outlines

Add the Form Button

Create a Data Entry Screen

Manage Records through the Form Screen

Generate a Subtotal

Collapse Subtotal Headings

Control Individual Subtotals

Create Multiple Subtotals

Copy Subtotals

Remove Subtotals

Use AutoOutline

Form an Outline Group

Remove Items from a Group

Consolidate Data by Category

Chapter 16: Filtering Data

Create an AutoFilter

Remove Filtering

Search for Blank or Non-Blank Cells

Perform a Secondary Filter Selection

Use a Comparison Filter for Text

Choose Additional Comparison Criteria

Use a Comparison Filter for Numbers

Filter for the Top or Bottom Numbers

Filter for the Above or Below Average Values

Filter by Date or Time

Filter by Color

Chapter 17: Creating PivotTables

Create a PivotTable

Filter PivotTable Data

Refresh PivotTable Data

Sort PivotTable Data

Change the Calculation Type

Rename a PivotTable Field

Format PivotTable Values

Generate Multilevel Totals

AutoFormat PivotTables

Group Data

Calculate a Percent of Totals

Add Your Own Calculations

Change PivotTable Display Options

Rename a PivotTable

Create a PivotTable Formula List

Reorder PivotTable Fields

Add a PivotTable Slicer

Create a PivotChart

Chapter 18: Building Simple Macros

Record a Macro

Assign a Macro Keystroke

Open a Macro-Enabled Workbook

Save a Macro-Enabled Workbook

Run a Macro

Check Macro Security Level

Add a Macro Button to the Quick Access Toolbar

Delete a Macro

Part V: Utilizing Excel with Other People and Applications

Chapter 19: Collaborating in Excel

Add Comments

Edit Comments

View Multiple Comments

Share a Workbook

Track Changes

Accept or Reject Changes

Merge Workbooks

Chapter 20: Integrating Excel into Word

Copy Excel Cells into Word

Insert a Saved Excel Worksheet into Word

Edit an Inserted Worksheet

Link an Excel Worksheet into Word

Copy a Word Table to Excel

Create a Word Mail Merge Form Letter Using an Excel List

Make Mailing Labels with Word and Excel

Chapter 21: Blending Excel and PowerPoint

Copy Excel Cells into a PowerPoint Slide

Drag an Excel Table into a PowerPoint Slide

Insert a Saved Excel Worksheet or Chart onto a PowerPoint Slide

Link an Excel Worksheet into a PowerPoint Slide

Add an Excel Chart to a PowerPoint Slide

Chapter 22: Using Excel with Access

Import Data from Excel to an Access Table

Link an Excel Worksheet to an Access Database

Update an Access-to-Excel Linked Table

Part VI: Practical Applications for Excel

Chapter 23: Creating a Commission Calculator

Enter Headings

Create the Commission Table

Define the Sales Data Input Area

Total Sales with the SUMIF Function

Use the COUNTIF Function to Count Sales

Calculate Commission with a Nested IF Statement

Create Totals

Make It Look Nicer

Protect Your Work

Chapter 24: Tracking Medical Expenses

Enter Text Headings

Create Totaling Formulas

Calculate the Amount Due

Specify Data Validation

Format the Worksheet

Determine Print Settings

Add Protection from Accidental Changes

Duplicate the Worksheet for Other Family Members

Create a Totals Worksheet

Excel® 2010 Just the Steps™ For Dummies®

by Diane Koers

Excel® 2010 Just the Steps™ For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

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, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Just the Steps, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States 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.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

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

Library of Congress Control Number: 2010925243

ISBN: 978-0-470-50164-1

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Author

Diane Koers owns and operates All Business Service, a software training and consulting business formed in 1988, that services the central Indiana area. Her area of expertise has long been in the word-processing, spreadsheet, and graphics areas of computing. She also provides training and support for Peachtree Accounting Software. Diane’s authoring experience includes over 40 books on topics, such as PC security, Microsoft Windows, Microsoft Office, Microsoft Works, WordPerfect, Paint Shop Pro, Lotus SmartSuite, Quicken, Microsoft Money, and Peachtree Accounting. Many of these titles have been translated into other languages, such as French, Dutch, Bulgarian, Spanish, and Greek. She has also developed and written numerous training manuals for her clients.

Diane and her husband enjoy spending their free time fishing, traveling, and playing with their four grandsons and their Yorkshire Terrier.

Dedication

To Sunshine: You are pup-a-licious!

Author’s Acknowledgments

I am deeply thankful to the many people at Wiley Publishing who worked on this book. Thank you for all the time and assistance you have given me.

To Bob Woerner: Thanks for the opportunity to write this book and for your confidence in me. A very special thank you to Jean Nelson for her assistance (and patience) in the book’s development; to Jen Riggs for keeping me grammatically correct, and to Joyce Nielsen for checking all the technical angles. And, last but certainly not least, a BIG thank you to all those behind the scenes who helped to make this book a reality. It’s been an interesting experience.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

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

Acquisitions, Editorial, and Media Development

Project Editor: Jean Nelson

Executive Editor: Bob Woerner

Copy Editor: Jen Riggs

Technical Editor: Joyce Nielsen

Editorial Manager: Kevin Kirschner

Media Development Project Manager: Laura Moss-Hollister

Media Development Assistant Project Manager: Jenny Swisher

Media Development Assistant Producers: Josh Frank, Marilyn Hummel, Douglas Kuhn, Shawn Patrick

Editorial Assistant: Amanda Graham

Sr. Editorial Assistant: Cherie Case

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Katherine Crocker

Layout and Graphics: Claudia Bell, Joyce Haughey

Proofreader: Jacqui Brownstein

Indexer: Infodex Indexing Services, Inc.

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Introduction

Conventions used in this book

When you need to type something, I put it in bold typeface.

For Ribbon commands, I use the ⇒ symbol to separate menu options. For example, “Choose Insert⇒Picture in the Illustrations group” says, “Click the Insert tab and then from the Illustrations group, click the Picture button.”

In some figures, you see circled items. This is done to help you locate items mentioned or referred to in the text.

This icon points out tips and helpful suggestions related to the current task.

Welcome to the world of Microsoft Excel, the most popular and powerful spreadsheet program in the world. You may ask: “What is a spreadsheet program?” A spreadsheet program is a computer program that features a huge grid designed to display data in rows and columns. You can use it to perform mathematical, logical, and other types of operations on the data you enter. You can sort the data, enhance it, and manipulate it in a plethora of ways — including creating powerful charts and graphs from it. Whether you need a list of names and addresses or a document to calculate next year’s sales projections based on prior year’s performance, Excel is the application you want to use.

About This Book

This book provides the tools you need to successfully tackle the potentially overwhelming challenge of figuring out how to use Microsoft Excel. In this book, you discover how to create spreadsheets; however, what you do with them is totally up to you. Your imagination is the only limit!

Why You Need This Book

Time is of the essence, and you probably don’t have the time to read a lot. You just need to complete a task effectively and efficiently. This book is full of concise, easy-to-understand steps designed to get you quickly up and running with Excel. I take you directly to the steps for a desired task without all the jibber-jabber that’s often included in other books.

Even if you’ve used Excel in the past, Excel 2010 brings many new features and major changes to existing features. This book helps ease the transition from earlier Excel versions.

How This Book Is Organized

This book is divided into 24 chapters broken into 6 convenient parts:

Part I: Putting Excel to Work

In Chapter 1, you uncover the basics of working with Excel files, such as opening, closing, and saving files. In Chapter 2, you work with entering the different types of data into Excel worksheets, and in Chapters 3 and 4, you create various types of formulas and functions to perform worksheet calculations. Chapter 5 shows you how to protect your work with Excel’s security features.

Part II: Sprucing Up Your Spreadsheets

Chapters 6 and 7 show you how to dress up the data you enter into a worksheet using data alignment, formatting values, changing fonts or colors, and adding cell borders. In Chapter 8, you work with graphics, such as arrows and Clip Art. In Chapter 9, you use workbooks consisting of multiple worksheets, hyperlinks, and cross-references.

Part III: Viewing Data in Different Ways

This part shows how you can modify the way Excel displays certain workbook options on your screen. Chapter 10 illustrates changing the worksheet views. In Chapter 11, you sort your data to make it easier to locate particular pieces of information. Chapter 12 enables you to create charts to display your data in a superb graphic manner. In Chapter 13, you work with the different output methods, including printing and e-mailing your worksheets. The last chapter in this part, Chapter 14, shows you several timesaving tools included with Excel.

Part IV: Analyzing Data with Excel

Use these chapters to effectively analyze all the data you input into a worksheet. In Chapters 15, 16, and 17, you work with Excel outlines, filters, and PivotTables, respectively. Chapter 18 shows you how you can create macros to save data entry and formatting time.

Part V: Utilizing Excel with Other People and Applications

Chapters 19–22 are all about sharing: sharing Excel with others by using Excel’s collaboration features or sharing Excel with Microsoft Office applications such as Word, PowerPoint, and Access.

Part VI: Practical Applications for Excel

Go to these chapters to save yourself time with a Commission Calculator worksheet (Chapter 23), or a medical-expense tracking worksheet (Chapter 24).

Back Cover: Using Excel Shortcut Keys

This helpful list shows you many shortcut keys that make access to Excel functions faster and easier.

Get Ready To . . .

To get started creating formulas, sorting data, adding a chart, or building macros, just flip through this book, pick a task, and dive in. The tasks in this book help you quickly master Excel.

Part I

Putting Excel to Work

Chapter 1: Working with Excel Files

Open and Explore Excel

Explore Backstage View

Close Excel

Select Commands with the Keyboard

Change Status Bar Indicators

Create a New Excel File

Save a Workbook

Save a Workbook in a Different Format

Open an Existing Excel File

Convert a Prior Version File to Excel 2010