Copyright Element K Corporation

Microsoft

Office Excel

2010 - Level 1
(Second Edition)
Copyright Element K Corporation
Microsoft

Office Excel

2010
Level 1 (SecondEdition)
Copyright Element K Corporation
Microsoft

OfficeExcel

2010 - Level 1
(SecondEdition)
Part Number: EBEE
Course Edition: 1.0
ACKNOWLEDGMENTS
Project Team
Content Developer: Srimathi Padmanabhan Content Manager: Shalmraj J Graphic Designer: Ganeshkumar B Project
Manager: Gopalakrishnan A Media Instructional Designer: Srimathi Padmanabhan Content Editor: Nirmala Rangamani
Materials Editor: Mohamed Anas Business Matter Expert: Technical Reviewer: Shalmraj J Project Technical Support:
Mike Toscano
NOTICES
DISCLAIMER: While Element K Corporation takes care to ensure the accuracy and quality of these materials, we cannot guarantee their accuracy, and all materials are provided without any warranty
whatsoever, including, but not limited to, the implied warranties of merchantability or tness for a particular purpose. The name used in the data les for this course is that of a ctitious company. Any
resemblance to current or future companies is purely coincidental. We do not believe we have used anyones name in creating this course, but if we have, please notify us and we will change the name in
the next revision of the course. Element K is an independent provider of integrated training solutions for individuals, businesses, educational institutions, and government agencies. Use of screenshots,
photographs of another entitys products, or another entitys product name or service in this book is for editorial purposes only. No such use should be construed to imply sponsorship or endorsement of
the book by, nor any afliation of such entity with Element K. This courseware may contain links to sites on the Internet that are owned and operated by third parties (the External Sites). Element K is
not responsible for the availability of, or the content located on or through, any External Site. Please contact Element K if you have any concerns regarding such links or External Sites.
TRADEMARK NOTICES: Element K and the Element K logo are trademarks of Element K Corporation and its afliates.
Excel

2010 is a registered trademark of Microsoft Corporation in the U.S. and other countries; the Microsoft products and services discussed or described may be trademarks of Microsoft Corporation.
All other product names and services used throughout this course may be common law or registered trademarks of their respective proprietors.
Copyright 2011 2011 Element K Corporation. All rights reserved. Screenshots used for illustrative purposes are the property of the software proprietor. This publication, or any part thereof, may not
be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, storage in an information retrieval system, or otherwise, without express written
permission of Element K, 500 Canal View Boulevard, Rochester, NY 14623, (585) 240-7500, (800) 478-7788. Element K Coursewares World Wide Web site is located at
www.elementkcourseware.com.
This book conveys no rights in the software or other products about which it was written; all use or licensing of such software or other products is the responsibility of the user according to terms and
conditions of the owner. Do not make illegal copies of books or software. If you believe that this book, related materials, or any other Element K materials are being reproduced or transmitted without
permission, please call (800) 478-7788.
Element K is independent from Microsoft Corporation, and is not afliated with Microsoft in any manner. While this publication and CD-ROM may be used in assisting individuals to prepare for a
Microsoft Ofce Specialist (MOS) certication exam, Microsoft, its designated program administrator, and Element K do not warrant that use of this publication and CD-ROM will ensure passing a MOS
certication exam.
What is the Microsoft Ofce Specialist Certication Program?
The Microsoft Ofce Specialist (MOS) Certication Program enables candidates to show that they have something exceptional to offer - proven expertise in Microsoft

Ofce applications. The MOS

Certication Program is the only Microsoft-approved certication program of its kind. The MOS Certication exams focus on validating specic skill sets within each of the Microsoft

Ofce system
programs. The candidate can choose which exam(s) they want to take according to which skills they want to validate. The available MOS exams include:
MOS: Microsoft

Ofce Word 2010

MOS: Microsoft

Ofce Excel 2010

MOS: Microsoft

Ofce PowerPoint 2010

MOS: Microsoft

Ofce Outlook 2010

MOS: Microsoft

Ofce Access 2010

MOS: Microsoft

SharePoint 2010
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) ii

4336
Copyright Element K Corporation
For more information:
To learn more about MOS exams, visit www.microsoft.com/learning/en/us/certication/mos.aspx.
iii
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) iv

NOTES
Copyright Element K Corporation
MICROSOFT

OFFICE EXCEL

2010-
LEVEL 1(SECOND EDITION)
LESSON 1- GETTING STARTED WITH EXCEL
A. Identify theElements of theExcel Interface . . . . . . . . . . . . . . . . . . . . . . . . . 2
Microsoft Excel 2010. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
TheExcel Applicationwindow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
TheRibbon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
TheBackstageView . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
TheQuick Access Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
TheStatus Bar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
TheFormulaBar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Contextual Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
TheExcel HelpWindow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
B. NavigateandSelect Cells in Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Spreadsheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Worksheet ReferencingElements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
MouseNavigationOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
KeyboardNavigationOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Cell SelectionOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
C. CustomizetheExcel Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
TheExcel Options DialogBox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
CONTENTS
Contents v
Copyright Element K Corporation
D. CreateaBasicWorksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
DataTypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Excel 2010 FileFormats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
TheSaveandSaveAs Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
TheCompatibility Checker Feature. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
LESSON 2- PERFORMING CALCULATIONS IN AN EXCEL WORKSHEET
A. CreateFormulas in aWorksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Excel Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Mathematical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
B. Insert Functions in aWorksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
FunctionCategories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
TheFormulaAutoCompleteFeature. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Commonly UsedFunctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
TheAutoSumFeature . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
C. ReuseFormulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
TheCut, Copy, andPasteCommands . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
PasteSpecial Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
LivePreview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
RelativeReferences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
AbsoluteReferences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
LESSON 3- MODIFYING AN EXCEL WORKSHEET
A. Edit Worksheet Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
TheUndoandRedoCommands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
TheAutoFill Feature. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
AutoFill Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
TheTransposeOption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Clear Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
CONTENTS
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) vi

Copyright Element K Corporation
B. FindandReplaceData . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Cell Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
TheFindCommand. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
TheReplaceCommand . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
TheSpellingDialogBox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
C. ManipulateWorksheet Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
TheInsert andDeleteOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
ColumnWidthandRowHeight AlterationMethods . . . . . . . . . . . . . . . . 82
TheHideandUnhideOptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
LESSON 4- MODIFYING THE APPEARANCE OF A WORKSHEET
A. Apply Font Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Fonts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
TheFont Group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
TheFormat Cells DialogBox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
TheFormat Painter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Galleries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
TheMini Toolbar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
B. AddBorders andColors toCells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Borders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Fills . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
C. Align Content in aCell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Alignment Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
TheIndent Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
TheWrapText Option. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
OrientationOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
TheMergeFeature. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
D. Apply Number Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
DataFormats inExcel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
E. Apply Cell Styles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Cell Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
TheStyleDialogBox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
CONTENTS
Contents vii
Copyright Element K Corporation
LESSON 5- MANAGING AN EXCEL WORKBOOK
A. ManageWorksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
InsertionandDeletionOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Worksheet RepositioningMethods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
TabFormattingOptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
HideandUnhideOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
B. ViewWorksheets andWorkbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
FreezePanes Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
TheSplit Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
TheArrangeAll Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
TheViewSideby SideCommand . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
TheSwitchWindows Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
TheNewWindowCommand. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
LESSON 6- PRINTING EXCEL WORKBOOKS
A. DenethePageLayout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Headers andFooters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Header andFooter Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
PageMargins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Margins TabOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
PageOrientation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
ThePrint Titles Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
PageBreaks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
PageBreak Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
B. Print aWorkbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
ThePrint Options intheBackstageView . . . . . . . . . . . . . . . . . . . . . . . . . . 160
ThePrint Area . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
ScalingOptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
CONTENTS
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) viii

Copyright Element K Corporation
APPENDIX A- MICROSOFT OFFICE EXCEL 2010EXAM 77882
APPENDIX B- MICROSOFT OFFICE EXCEL 2010EXPERT EXAM
77888
LESSON LABS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
179
SOLUTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
185
INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
187
CONTENTS
Contents ix
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) x

NOTES
Copyright Element K Corporation
ABOUT THIS COURSE
You possess some basic computer skills and are comfortable with using a computer to perform
basic tasks. You may have used a calculator and paper to record data and perform calculations,
and now want to migrate to using a computer application to store and process data in an elec-
tronic format. In this course, you will use Microsoft

Office Excel

2010 to create
spreadsheets that you can use to perform these tasks.
When manually calculating and recording data on paper, you need to recalculate every time
you add new data. When working with large volumes of data, by the time the new set of data
is recalculated manually, the paper-based sheets are a complete mess. Updating spreadsheets in
Excel is easy and time saving. You can simply insert the new data and set the sheet to be
updated whenever the data changes. Storing data electronically is more efficient because it
allows you to quickly update existing data, run reports on the data, perform calculations, and
much more.
This course can also benet you if you are preparing to take the Microsoft Office Specialist
(MOS) Certication exams for Microsoft

Excel

2010. Please refer to the CD-ROM that

came with this course for documents that map exam objectives to the content in the Microsoft
Office Excel courseware series. To access the mapping documents, insert the CD-ROM into
your CD-ROM drive and at the root of the CD, double-click ExamMappingCore.doc or
ExamMappingExpert.doc to open a mapping document. In addition to the mapping documents,
two assessment les per course can be found on the CD-ROM to check your knowledge. To
access the assessments, at the root of the course part number folder, double-click 084676s3.doc
to view the assessments without the answers marked, or double-click 084676ie.doc to view the
assessments with the answers marked.
If your course manual did not come with a CD-ROM, please go to http://www.elementk.com/
courseware-le-downloads to download the les.
CourseDescription
Target Student
This course is designed for students who desire to gain the necessary skills to create, edit, for-
mat, and print basic Microsoft Office Excel 2010 worksheets.
INTRODUCTION
Introduction xi
Copyright Element K Corporation
CoursePrerequisites
To be successful in this course, you should be familiar with using personal computers and you
should have used the mouse and keyboard. You should be comfortable in the Windows envi-
ronment and be able to use Windows to manage information on the computer. Specically, you
should be able to launch and close programs; navigate to information stored on the computer;
and manage les and folders. To ensure your success, we recommend you rst take one of
New Horizons s introductory Windows courses, such as either of the following, or have
equivalent skills and knowledge:
Windows XP Professional - Level 1
Windows XP - Introduction
HowtoUseThis Book
As aLearningGuide
This book is divided into lessons and topics, covering a subject or a set of related subjects. In
most cases, lessons are arranged in order of increasing prociency.
The results-oriented topics include relevant and supporting information you need to master the
content. Each topic has various types of activities designed to enable you to practice the guide-
lines and procedures as well as to solidify your understanding of the informational material
presented in the course.
At the back of the book, you will nd a glossary of the denitions of the terms and concepts
used throughout the course. You will also nd an index to assist in locating information within
the instructional components of the book.
As aReviewTool
Any method of instruction is only as effective as the time and effort you, the student, are will-
ing to invest in it. In addition, some of the information that you learn in class may not be
important to you immediately, but it may become important later. For this reason, we encour-
age you to spend some time reviewing the content of the course after your time in the
classroom.
As aReference
The organization and layout of this book make it an easy-to-use resource for future reference.
Taking advantage of the glossary, index, and table of contents, you can use this book as a rst
source of denitions, background information, and summaries.
This course is one of a series of New Horizons courseware titles that addresses Microsoft
Office Specialist (MOS) certication skill sets. The MOS and certication program is for indi-
viduals who use Microsofts business desktop software and who seek recognition for their
expertise with specic Microsoft products.
INTRODUCTION
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) xii

Copyright Element K Corporation
CourseObjectives
In this course, you will create and edit basic Microsoft Office Excel 2010 worksheets and
workbooks.
You will:
create a basic worksheet by using Microsoft Excel 2010.
perform calculations in an Excel worksheet.
modify an Excel worksheet.
modify the appearance of data within a worksheet.
manage Excel workbooks.
print the content of an Excel worksheet.
INTRODUCTION
Introduction xiii
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) xiv

NOTES
Copyright Element K Corporation
Getting Started with Excel
In this lesson, you will create a basic worksheet by using Microsoft Excel 2010.
You will:
Identify elements of the Excel interface.
Navigate and select cells in worksheets.
Customize the Excel interface.
Create a basic worksheet.
Lesson Time
1 hour(s), 15 minutes LESSON 1
LESSON 1
Lesson 1: Getting Started with Excel 1
Copyright Element K Corporation
Introduction
You often work with data, but may not be aware that Microsoft Excel 2010 enables users to
store and manage data better. Knowing that using Excel 2010 has several advantages, you are
ready to learn the details. In this lesson, you will familiarize yourself with the Excel 2010
environment, customize the interface, and create a basic worksheet.
Using an instrument without the basic knowledge of its components and operating procedures
can be a complicated task. Similarly, it would be difficult to use a software application such as
Excel without understanding its interface elements and tools. Excel 2010 provides an interac-
tive interface with enhanced features that help you create professional workbooks to store and
analyze data easily.
TOPIC A
Identify the Elements of the Excel
Interface
You are interested in the efficiency that can be realized by using the Excel application for stor-
ing and manipulating data. Before you gain this efficiency, you need to be familiar with its
interface. In this topic, you will identify the elements of the Excel interface.
Wouldnt it be easy to work at a new job if you were fully acquainted with the tasks involved?
Similarly, exploring Excels interface will help you familiarize yourself with the options avail-
able in the application, which in turn, will help you use the application effectively.
Microsoft Excel 2010
Excel is an application in the Microsoft Office suite that you can use to create, revise, and save
data in a spreadsheet format. You can also add formulas and functions to perform calculations,
and analyze, share, and manage information using charts and tables. Excel also has options for
adding pictures, shapes, and screenshots to a spreadsheet.
The Excel Application window
When you launch Excel, two windows are displayed, one within the other. The outer window
is the main application window that usually lls the entire screen, and provides various inter-
active tools and commands. The inner window is the workbook window where you will work
with data.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 2

Copyright Element K Corporation
Figure 1-1: The components of an Excel application window.
Component Description
The Quick Access
toolbar
A toolbar that provides you with an easy access to frequently used applica-
tion commands.
The Ribbon A panel that displays relevant commands to a particular set of tasks. These
commands are organized into different tabs and groups.
The Formula Bar A bar that displays the contents of the selected cell in a spreadsheet and can
be used to type a formula or function. It also displays a reference to the
active cell or the range of the current selection of cells.
The task pane A pane that appears on an as-needed basis and provides you with several
options for a particular command selected on the Ribbon. You can move and
resize the task pane.
The status bar A window element that is displayed at the bottom, and contains features
such as dynamic zoom slider and a customizable status display.
The Ribbon
The Ribbon is an interface component that comprises several task-specic commands, which
are grouped together under various tabs. It is designed to be the central location for accessing
commands in the Microsoft Office suite for performing both simple and advanced operations
without having to navigate extensively.
LESSON 1
Lesson 1: Getting Started with Excel 3
Copyright Element K Corporation
Figure 1-2: The Ribbon displaying the commands of the Home tab.
Ribbon Tab Used To
File Display the Backstage view that contains commands to
print, save, and share workbooks.
Home Format spreadsheet data, add basic data and cell format-
ting, and add styles.
Insert Insert text, tables, charts, symbols, illustrations, and links.
Page Layout Specify page settings, layout, orientation, margins, and
other options related to printing a workbook.
Formulas Create formulas with built-in functions to calculate values
automatically. The built-in functions are categorized by the
type of calculations they can perform.
Data Connect with external data sources and import data for use
within Excel worksheets.
Review Review Excel worksheets. It also provides tools such as
spell checker, thesaurus, and translator.
View Control the display of the worksheet and the workbook
window. You can also hide or display the gridlines in a
worksheet.
ScreenTips
A ScreenTip is descriptive text that is displayed when you position the mouse pointer
over a component in the interface. An enhanced ScreenTip describes the component in
detail and often provides a link to a help topic. Most of the components in Excel have
associated enhanced ScreenTips. Excel provides you with options to select the level of
detail you want ScreenTips to display. Additionally, there are options in the Excel
Options dialog box to turn off the ScreenTips.
The Backstage View
The Backstage view is an interface element with options that group similar commands. This
view is designed to simplify access to Excel features and can be used to save, send, print,
open workbooks, display document information, and customize the application. You can access
the Backstage view from the File tab.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 4

Copyright Element K Corporation
Figure 1-3: The Backstage view in Excel.
Option Description
Save, Save As, Open, and
Close
Allows you to save the changes made to a workbook, save a workbook
with a new le name in the desired location, open an existing work-
book, and close a workbook.
Info Displays options to protect workbooks by using a password, check for
accessibility and compatibility issues, manage versions of workbooks,
and set workbook properties.
Recent Lists workbooks that were recently accessed. It also allows you to cus-
tomize the recently opened workbooks list by adding, removing, or
reordering the items in the list.
New Displays options to create a blank workbook, or a workbook based on
a predened or custom designed templates, or an existing workbook.
You can also access additional templates from the Office.com website.
Print Displays options to preview and print workbooks.
Save & Send Provides options to save a workbook in a previous version of Excel,
share a workbook through email or SharePoint, and publish a work-
book.
Help Allows you to access the online and offline help resources. It also pro-
vides access to the Excel Options dialog box.
Options Displays the Excel Options dialog box, which allows you to customize
the Excel interface.
Exit Allows you to exit the application.
LESSON 1
Lesson 1: Getting Started with Excel 5
Copyright Element K Corporation
The Quick Access Toolbar
The Quick Access toolbar is usually located above the Ribbon and is displayed as an inte-
grated component of the title bar. It provides easy access to commonly used commands such
as Save, Undo, and Redo. The Customize Quick Access Toolbar menu options not only
allow you to customize the display of buttons, but also reposition the Quick Access toolbar
below the Ribbon. You can also add frequently used commands either from the Ribbon or by
selecting options from the Excel Options dialog box.
Figure 1-4: The default commands on the Quick Access toolbar.
The Status Bar
The status bar, located at the bottom of the Excel application window, displays the current
mode and cell information, such as average, count, and sum. It also has elements for adjusting
the zoom level of the displayed worksheet and for selecting the desired page layout option.
Element Function
The Mode indicator Displays current mode of Excel along with the information of any special
key, which is engaged.
The Auto Calculate indi-
cator
Displays the average and sum of current selection along with the count of
selected cells.
View buttons Provides options to display a worksheet in any of the three default views:
Normal, Page Layout, and Page Break Preview.
The Zoom Out button Allows you to view the content in a worksheet in a smaller size.
The Zoom slider Allows you to magnify or diminish a worksheet view to any desired size.
The Zoom In button Allows you to enlarge the view of worksheet contents.
The Zoom level button Allows you to set the zoom percentage of a worksheet.
The View Buttons on the Status Bar
The view buttons on the status bar enable you to view a worksheet in different views.
View Button Allows You To
Normal View a worksheet in the normal view. By default, Excel opens in this
view. This view displays the row and column headers, and also
facilitates easy entry of data.
Page Layout View a worksheet as it will appear on a printed page.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 6

Copyright Element K Corporation
View Button Allows You To
Page Break Preview Preview where the pages will break in a worksheet when it is
printed.
The Formula Bar
The Formula Bar, located below the Ribbon, contains the Name Box, the Insert Function
button, and the Formula Bar text box. The Name Box displays the name or reference of the
selected cells. The Insert Function button enables you to insert a function in the selected cell.
The Formula Bar text box displays the contents of the selected cell and allows you to edit the
contents. You can expand, collapse, resize, or hide the Formula Bar to suit your preferences.
Figure 1-5: The Formula Bar on a worksheet.
Contextual Tabs
Contextual tabs are additional tabs that appear on the Ribbon when you select a specic object
such as a chart, table, drawing, or text box. Because these tabs are context based, the com-
mands displayed on the Ribbon depend upon the object that you select. These contextual tabs
keep the number of tabs displayed on the Ribbon to a minimum and disappear once the rel-
evant object is deselected.
Figure 1-6: A contextual tab displayed on the Ribbon.
LESSON 1
Lesson 1: Getting Started with Excel 7
Copyright Element K Corporation
Templates
Denition:
A template is a predesigned layout that is used to create a document or a design pat-
tern. A template can be created and stored in either an electronic or a print medium.
Like most templates, an Excel template is a xed layout, and you can make changes to
the layout and customize it to create a new design pattern. Essentially, templates are
created to simplify repetitive actions of creating similar looking documents.
Example:
Figure 1-7: A template in Excel.
The Excel Help Window
The Excel Help window enables you to search for information for your Excel-related questions
and can be accessed by clicking the Microsoft Excel Help button at the top-right corner of the
application window. You can also search Office.com for Excel-related information.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 8

Copyright Element K Corporation
Figure 1-8: The Excel Help window.
Help Window Element Description
The toolbar Provides access to the navigation, printing, and formatting help contents.
The Type words to
search for text box
Allows you to enter text to search for.
The Search drop-down
list
Allows you to specify whether to search for the query term online or offline.
The Browse Excel Help
pane
Displays links to various topics available in Excel Help. You can click a link
to navigate to the desired topic.
Search Options
The Search drop-down list helps you narrow the search to a specic content reposi-
tory. You can select an option from the list to restrict the search to your computer or
include content from the Office.com website.
How to Identify Elements of the User Interface
Procedure Reference: Open a Workbook Using the Open Dialog Box
To open a workbook using the Open dialog box:
1. Select the File tab and choose Open to display the Open dialog box.
2. Navigate to the desired le and click Open.
LESSON 1
Lesson 1: Getting Started with Excel 9
Copyright Element K Corporation
New Workbooks
You can select the New option on the File tab to open a new workbook. When you
select New, the Backstage view displays the available templates organized in various
categories from which to choose. You can choose to create a blank workbook, a work-
book based on a template, or an existing workbook.
ACTIVITY 1-1
Identifying Elements of the User Interface
Data Files:
C:\084676Data\Getting Started with Excel\Sales Revenue.xlsx
Scenario:
You have joined Our Global Company (OGC) Stores as a sales manager. In your job role, you
need to use the Excel application to analyze information. You have not worked with the Excel
application earlier, but you want to familiarize yourself with the interface elements of the
application.
What You Do How You Do It
1. Explore the Backstage view and open
a workbook.
a. Choose StartAll ProgramsMicrosoft
OfficeMicrosoft Excel 2010 to launch
the Microsoft Excel 2010 application.
b. If necessary, in the User Name dialog box,
click OK.
c. In the Welcome to Microsoft Office 2010
dialog box, select the Dont make
changes option and click OK.
d. On the Ribbon, select the File tab to dis-
play the Backstage view.
e. Observe that the File tab displays options
to save, open, print, and close a work-
book. Choose New.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 10

Copyright Element K Corporation
f. In the Available Templates section, view
the available templates, and on the File
tab, choose Open to display the Open dia-
log box.
g. In the Open dialog box, navigate to the
C:\084676Data\Getting Started with Excel
folder.
h. Select the Sales Revenue.xlsx file and
click Open.
2. Explore the Ribbon tabs. a. On the Ribbon, select the Page Layout
tab to view its commands.
b. Observe that the Themes, Page Setup,
Scale to Fit, Sheet Options, and Arrange
groups are displayed along with relevant
commands.
LESSON 1
Lesson 1: Getting Started with Excel 11
Copyright Element K Corporation
c. In the Page Setup group, place the mouse
pointer over any button to view its
ScreenTip.
d. Select the other tabs on the Ribbon to
view the commands and groups.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 12

Copyright Element K Corporation
3. Explore the Quick Access toolbar. a. On the Quick Access toolbar, place the
mouse pointer over each button to view
its description.
b. At the right end of the Quick Access
toolbar, click the Customize Quick
Access Toolbar drop-down arrow to dis-
play the Customize Quick Access Toolbar
menu.
c. View the options available on the Custom-
ize Quick Access Toolbar menu and click
the Customize Quick Access Toolbar
drop-down arrow to close the menu.
4. Explore the status bar. a. At the bottom-right corner of the
worksheet, on the status bar, to the left
of the Zoom slider, place the mouse
pointer over each of the buttons to view
their descriptions.
b. At the right end of the status bar, on the
Zoom slider, click the Zoom In button.
c. Observe that the zoom percentage has
increased to 110%.
d. Click the Zoom Out button to revert the
zoom percentage to 100%.
LESSON 1
Lesson 1: Getting Started with Excel 13
Copyright Element K Corporation
TOPIC B
Navigate and Select Cells in
Worksheets
You familiarized yourself with the interface components of the Excel application. To view or
modify data in Excel, you need to know how to work with worksheets. In this topic, you will
navigate through an Excel worksheet.
Imagine that you have just moved to a new city to start a new job. To reach your office, you
want to try the various modes of transport available in the city and decide on the quickest and
the easiest option. Learning the basics of navigating in Excel is very similar to this; you know
how to commute, but you need to familiarize yourself with the commuting options available.
By navigating through Excel, you will familiarize yourself with the interface, thus making it
easier for you to work with Excel.
Spreadsheets
Denition:
A spreadsheet is a paper or an electronic document that is used to store and manipu-
late data. It consists of rows and columns that intersect to form cells, where the data
you enter is stored. Data can be in the form of numbers, text, and non-alphanumeric
symbols in a tabular format. You can customize spreadsheets based on your business
needs and data requirements.
Example:
Figure 1-9: A spreadsheet with data.
Worksheets
A worksheet is an electronic spreadsheet that is used for entering and storing data in Excel. An
Excel worksheet contains columns and rows, which intersect like a grid to form cells. Excel
designates columns with alphabetical headers running across the top of the worksheet, and
rows with numerical headers running down the left of the worksheet. An Excel worksheet can
contain various types of data such as text, numbers, pictures, formulas, charts, or tables. You
can insert or delete rows, columns, and cells from an Excel worksheet.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 14

Copyright Element K Corporation
Figure 1-10: An Excel worksheet.
Worksheet Referencing Elements
Column headings in Excel worksheets begin with the letter A and continue through the letter
Z. After the 26th column (column Z), headings become double letters, from AA to AZ. After
AZ, the letter pairs start again with columns BA through BZ, and so on, until all 16,384 col-
umns have alphabetical headings, ending with three-letter headers at XFD. Row headings begin
with 1 and continue through 1,048,576. In Excel, cells are referenced based on the intersection
of the respective column and row. For example, a cell in column B and row 3 is referenced as
B3. The cell that is selected is called an active cell and the reference of the active cell appears
in the Name Box on the Formula Bar. The contents of the active cell is displayed on the For-
mula Bar.
Figure 1-11: The reference of the active cell in the Name Box.
Workbooks
A workbook is an Excel le that acts as a repository for related Excel worksheets. The name
of the workbook is displayed on the title bar of the Excel application window. By default, a
new blank Excel workbook contains three worksheets: Sheet1, Sheet2, and Sheet3. The
worksheet names are displayed on tabs at the bottom left of the workbook. You can rename,
add, or delete worksheets in an Excel workbook.
LESSON 1
Lesson 1: Getting Started with Excel 15
Copyright Element K Corporation
Workbook Element Description
The sheet tab bar A bar at the bottom of a workbook that contains worksheet scroll buttons,
worksheet tabs, and the Insert Worksheet button. It also contains the hori-
zontal scroll bar.
The worksheet tabs The tabs at the bottom left of the workbook that allow you to navigate
across worksheets.
The tab scroll buttons The buttons on the sheet tab bar, at the left of the worksheet tabs that pro-
vide you with options to scroll through the display of the worksheet tabs one
at a time, or display the rst or last grouping of the worksheet tabs in a
workbook.
The Insert Worksheet
button
The buttons on the sheet tab bar, at the right of the worksheet tabs that allow
you to add a new worksheet to the workbook.
Mouse Navigation Options
You can use the mouse pointer to navigate through worksheets. You can also navigate to a spe-
cic cell within a worksheet, or to a different worksheet in a workbook.
Mouse Navigation Option Action
Move the worksheet display up or down,
one row
Click the top or bottom vertical scroll arrow.
Move the worksheet display left or right,
one column
Click the left or right horizontal scroll arrow.
Move the worksheet display either hori-
zontally or vertically
Click and hold the horizontal or vertical scroll arrow.
Move the worksheet display one screen at
a time
Click the space between the scroll bar and the scroll arrow
of either the vertical or the horizontal scroll bar.
Move rapidly through a worksheet Drag either the vertical or the horizontal scroll bar.
Move to a specic cell in a worksheet Click the Name Box, type the cell reference, and press
Enter.
Display a different worksheet Click the name of the desired worksheet on the sheet tab
bar.
Keyboard Navigation Options
You can use a keyboard to navigate within and across worksheets in a workbook to enter,
view, or modify data.
Keyboard Navigation Option Action
Move one cell to the left, right, up, or
down
Press the Up, Down, Left, or Right arrow key.
Move to column A of the current row Press Home.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 16

Copyright Element K Corporation
Keyboard Navigation Option Action
Scroll down or up by one screen Press Page Down or Page Up.
Scroll one screen to the left or right Press Alt+Page Down or Alt+Page Up.
Move to the cell on the right Press Tab.
Move to the cell on the left Press Shift+Tab.
Move to cell A1 Press Ctrl+Home.
Navigate across worksheets Press Ctrl+Page Up or Ctrl+Page Down.
Move to the last column of the current
row
Press End.
Move to the rst or last column or row of
data
Press Ctrl along with the Up, Down, Left, or Right arrow
key.
Cell Selection Options
Excel provides you with multiple options to select a cell or a group of cells in a worksheet.
You can select either a contiguous range consisting of cells that are adjacent to each other, or
a noncontiguous range consisting of cells that are not adjacent to each other.
Selection Option Action
A cell Click the cell.
A contiguous range Select the rst cell in the range, hold down Shift, and select the last cell of
the range. Alternatively, you can click and drag from the rst cell of the
range to the last cell of the range.
A noncontiguous range Select the rst cell in the rst range, hold down Ctrl, and click the next cell
in the range. To select multiple cells, hold down Ctrl and click multiple
cells. You can also select multiple contiguous range of cells that are
noncontinuous by using the Shift+click and Ctrl+click methods.
An entire row or column Click the alphabetical header of a column or the numerical header of a row.
Alternatively, you can hold down Shift and press the Spacebar to select the
entire row, or hold down Ctrl and press the Spacebar to select the entire
column.
An entire worksheet Click the Select All button below the Name Box.
Selecting Cell Data
Excel allows you to select a cell as well as its data. At times, you may need to change
only a part of the data rather than the entire data. In such cases, you can double-click
the cell, then select the part of the data which needs to be altered. Alternatively, you
can click the cell, and then select the data that needs to be altered in the Formula Bar.
LESSON 1
Lesson 1: Getting Started with Excel 17
Copyright Element K Corporation
How to Navigate and Select Cells in Worksheets
Procedure Reference: Navigate and Select Cells in a Worksheet
To navigate and select cells in a worksheet:
1. Open a workbook.
2. Use the appropriate navigation techniques to move to the desired location.
Click in the Name Box, type the cell reference, and press Enter to move to
a specic cell in the worksheet.
Click a scroll bar to move the worksheet display up or down.
Press the Up, Down, Left, or Right arrow key to move one cell up, down,
left, or right, respectively.
3. Use the appropriate selection methods to select cells.
Click a cell to select it.
Select the rst cell of the range, hold down Shift, and select the last cell of
the range.
Click and drag from the rst cell of the range to the last cell of the range to
select a continuous range of cells.
ACTIVITY 1-2
Working with Cells
Before You Begin:
The Sales Revenue.xlsx le is open.
Scenario:
You have a workbook with sales trends, which you want to browse through so that you can
prepare a report on your teams performance in the past year. You need to view the relevant
information of each sales person in the workbook.
What You Do How You Do It
1. View specific cells in the worksheet. a. Scroll down to view the data in row 83.
b. Hold down Ctrl and press Home to navi-
gate to the beginning of the worksheet.
c. Hold down Ctrl and press Page Down to
move to the next worksheet.
d. Observe that Sheet2 is selected.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 18

Copyright Element K Corporation
e. On the sheet tab bar, click Sheet1 to
return to the first worksheet.
2. Select a continuous range of cells. a. Scroll down and select cell A83.
b. Hold down Shift and click cell M84 to
select a range of cells from A83 to M84.
c. At the top-left corner of the worksheet,
below the Name Box, click the Select All
button to select the entire worksheet.
3. Select a noncontinuous range of
cells.
a. Scroll up and to the left, and then select
cell A19.
b. Hold down Shift and click cell E19.
c. Observe that the cells A19 to E19 are
selected.
d. Hold down Ctrl and click cell H19.
e. Hold down Shift and click cell J19.
f. Observe that a noncontinuous range of
cells are selected.
g. Click on any empty cell to deselect the
selection.
LESSON 1
Lesson 1: Getting Started with Excel 19
Copyright Element K Corporation
TOPIC C
Customize the Excel Interface
You navigated through and selected content in an Excel worksheet. There may be instances
when the default display and arrangement of the interface elements do not suit your prefer-
ence. Excel provides you with options to personalize the interface according to your
requirements. In this topic, you will customize the Excel interface.
When you start working with a new software application, the interface may not provide you
with convenient access to all the options that you require, or the interface may be cluttered
with options that you may not require at all. A cluttered interface can compromise your work
efficiency. By customizing the applications interface, you will be able to access the options
that you need, easily and quickly.
The Excel Options Dialog Box
The Excel Options dialog box provides you with various options to customize the Excel user
interface. These options are classied into 10 categories.
Figure 1-12: The customizing options available in the Excel Options dialog box.
Category Description
General Provides you with options to make general settings such as viewing the Mini
toolbar, changing the color scheme of the Excel interface, and changing the
ScreenTip style.
Formulas Allows you to specify how to calculate formulas, manage performance, and
control errors.
Proong Enables you to set the way Excel corrects and formats text.
Save Provides you with options to customize the way workbooks are saved.
Language Allows you to set the Office language preferences.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 20

Copyright Element K Corporation
Category Description
Advanced Provides you with advanced options under various sections such as Editing
options, Image Size and Quality, Print, and Display.
Customize Ribbon Provides you with options to customize the Ribbon.
Quick Access Toolbar Provides you with options to customize the Quick Access toolbar and
include additional commands.
Add-Ins Provides you with options to view and manage Office add-ins.
Trust Center Provides you with access to information about protecting your privacy and
securing your computer. It also allows you to specify the security and pri-
vacy settings.
How to Customize the Excel Interface
Procedure Reference: Customize the Quick Access Toolbar Using the Excel
Options Dialog Box
To customize the Quick Access toolbar using the Excel Options dialog box:
1. Display the Quick Access Toolbar tab of the Excel Options dialog box.
Select the File tab and choose Options, and in the Excel Options dialog
box, click Quick Access Toolbar or;
From the Customize Quick Access Toolbar menu, choose More Com-
mands.
2. From the Choose commands from drop-down list, select the category from
which you want to add a command.
3. In the Choose commands from list box, select the desired command and click
Add to add the command to the Quick Access toolbar.
4. If necessary, click the Move Up or Move Down arrow button located on the right
of the Customize Quick Access Toolbar list box to move the options up or down
the list.
You can also reposition the Quick Access toolbar below the Ribbon by selecting the Show Below
the Ribbon option from the Customize Quick Access Toolbar menu.
5. Click OK to close the Excel Options dialog box.
Procedure Reference: Customize the ScreenTip Style
To customize the ScreenTip style:
1. Select the File tab and choose Options.
2. If necessary, in the Excel Options dialog box, select the General tab.
3. Under the User Interface options section, from the ScreenTip style drop-down
list, select an option.
Select Show feature descriptions in ScreenTips to display the name of an
element along with a brief description.
LESSON 1
Lesson 1: Getting Started with Excel 21
Copyright Element K Corporation
Select Dont show feature descriptions in ScreenTips to display only the
name of an element.
Select Dont show ScreenTips to disable ScreenTips.
4. Click OK to apply the new ScreenTip style.
Procedure Reference: Add a Command Button from the Ribbon to the Quick
Access Toolbar
To add a command button or group from the Ribbon to the Quick Access toolbar:
1. On the Ribbon, select the tab that has the desired command button or group.
2. Add the desired command button or group.
Right-click the command button and choose Add to Quick Access Toolbar.
Within the desired group, right-click the text region below the buttons and
choose Add to Quick Access Toolbar.
Procedure Reference: Customize the Status Bar
To customize the status bar:
1. At the bottom of the application window, right-click the status bar to display the
Customize Status Bar menu.
2. From the displayed menu, choose the required options.
On the menu, when you choose an option, a check mark is displayed to its left to indicate that
the selected option is displayed on the status bar. Selecting an option that has a check mark will
hide the information on the status bar.
3. Click away from the Customize Status Bar menu to close it.
Procedure Reference: Customize the Save Options
To customize the save options:
1. Select the File tab and choose Options.
2. In the Excel Options dialog box, select the Save tab.
3. In the Save workbooks section, set the save options.
From the Save les in this format drop-down list, select a format.
In the Save AutoRecover information every text box, type the number of
minutes to specify the duration after which AutoRecover information is
saved.
In the Default le location text box, type the path of the default location in
which workbooks are saved.
4. Click OK to apply the customized options.
Procedure Reference: Customize the Ribbon
To customize the Ribbon:
1. Select the File tab and choose Options.
2. In the Excel Options dialog box, select the Customize Ribbon tab.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 22

Copyright Element K Corporation
3. In the Customize the Ribbon list box, use the check boxes to show or hide tabs
on the Ribbon.
4. Create a new tab and group.
a. Below the Customize the Ribbon list box, click New Tab to create a tab.
b. If necessary, below the Customize the Ribbon list box, click New Group to
create a group.
5. Rename a tab or a group.
a. In the Customize the Ribbon list box, select the tab or group that you want
to rename.
b. Display the Rename dialog box.
Below the Customize the Ribbon list box, click Rename or;
Right-click the tab or group and choose Rename.
c. In the Rename dialog box, type a new name and click OK.
6. Add commands to a group.
a. From the Choose commands from drop-down list, select the desired cat-
egory from which you want to choose commands.
b. In the Choose commands from list box, select the desired command that
you want to add.
c. In the Customize the Ribbon list box, select the tab and group to which you
want to add the command.
d. Click Add to add the selected command.
7. If necessary, in the Customize the Ribbon list box, select a command and click
Remove to remove the command from the group.
8. If necessary, create more tabs and groups and add commands to them.
9. Click OK to close the Excel Options dialog box.
LESSON 1
Lesson 1: Getting Started with Excel 23
Copyright Element K Corporation
ACTIVITY 1-3
Customizing the Microsoft Excel User Interface
Before You Begin:
The Sales Revenue.xlsx le is open.
Scenario:
As you will be using the Excel application extensively in your role, you identied certain fre-
quently used commands and wanted a quicker option to access them. You also wanted to
display the required statistical information for selected cells on the status bar. In addition, you
wanted to maintain all official les in a specic folder.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 24

Copyright Element K Corporation
What You Do How You Do It
1. Add the Open, New, and Close com-
mands to the Quick Access toolbar.
a. At the right end of the Quick Access
toolbar, click the Customize Quick
Access Toolbar drop-down arrow, and
from the displayed menu, choose More
Commands.
b. In the Excel Options dialog box, in the
left pane, observe that the Quick Access
Toolbar tab is selected, and in the right
pane, in the Choose commands from list
box, scroll down and select Open.
c. Click Add to add the Open command to
the Customize Quick Access Toolbar list
box.
d. In the Choose commands from list box,
select New and click Add to add the New
command to the Customize Quick Access
Toolbar list box.
LESSON 1
Lesson 1: Getting Started with Excel 25
Copyright Element K Corporation
e. From the Choose commands from drop-
down list, select File Tab.
f. In the Choose commands from list box,
select Close and click Add to add the
Close command to the Customize Quick
Access Toolbar list box.
g. Click OK to close the Excel Options dialog
box.
h. Observe that the newly added commands
are displayed on the Quick Access toolbar.
2. Add the Alignment group to the
Quick Access toolbar.
a. On the Ribbon, select the Home tab.
b. In the Alignment group, right-click on the
text Alignment and choose Add to Quick
Access Toolbar.
c. On the Quick Access toolbar, click the
Alignment button to display the options in
the Alignment group.
d. Observe that all the Alignment group
options are now accessible from the Quick
Access toolbar; click the Alignment but-
ton to close the Alignment group.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 26

Copyright Element K Corporation
3. Customize the status bar. a. On the status bar, right-click to view the
Customize Status Bar menu.
b. From the displayed menu, choose Numeri-
cal Count to display on the status bar, a
count of numeric values in a selection.
c. Observe that a check mark is displayed
beside the Numerical Count option denot-
ing, that it is added to the status bar.
d. Click away from the Customize Status Bar
menu to close it.
e. Select cell A20.
f. Hold down Shift and click cell F20 to
select a range of cells from A20 to F20.
g. On the status bar, observe that the count
of the number of cells containing numeri-
cal values in the selected range is
displayed as 5.
h. Observe that the zoom percentage is dis-
played to the left of the Zoom slider.
i. On the status bar, right-click to view the
Customize Status Bar menu.
j. From the displayed menu, choose Zoom.
k. Observe that the check mark is removed
from beside the Zoom option and the
zoom percentage that was displayed on
the status bar is hidden.
l. Click away from the Customize Status Bar
menu to close it.
LESSON 1
Lesson 1: Getting Started with Excel 27
Copyright Element K Corporation
4. Set the default location and color
scheme.
a. Select the File tab and choose Options.
b. In the Excel Options dialog box, verify
that the General tab is selected.
c. In the User Interface options section,
from the Color scheme drop-down list,
select Blue.
d. In the Excel Options dialog box, select
the Save tab.
e. In the Save workbooks section, in the
Default file location text box, triple-click
and type C:\084676Data and click OK.
f. Observe that the blue theme is applied.
g. On the Quick Access toolbar, click the
Close button to close the file.
h. If necessary, in the Microsoft Excel mes-
sage box, click Dont Save to close the
workbook without saving it.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 28

Copyright Element K Corporation
TOPIC D
Create a Basic Worksheet
You customized the Excel interface to enable easy access to frequently used commands in
Excel. To start using the Excel application, you need to add data to a worksheet and save it. In
this topic, you will enter data in an Excel worksheet.
Bricks are put together to construct a building, and complex structures are achieved by com-
bining the bricks in different forms. Similarly, basic data are put together to create complex
worksheets. Before you begin to create complex worksheets, you should know how to enter
basic data.
Data Types
Excel allows you to enter various types of data. These data types can be generally categorized
as labels, values, and dates and time. Labels are text that can be represented using letters,
numbers, and symbols. Values are numbers that you may use to perform mathematical or sta-
tistical analysis. Date and time are used to represent date, time, or both in various formats.
Depending on the data you enter in a cell, Excel automatically chooses the appropriate data
type.
Excel 2010 File Formats
The default le type in Excel 2010 is (.xlsx) an XML-based le format. Files saved in this
format are suffixed with the letter x. Using this XML-based le format allows les to be auto-
matically compressed upon saving and decompressed upon opening. Saving spreadsheets in the
default Excel 2010 le format not only allows you to secure data, but also recover data if the
le is corrupt. Excel provides you with an extensive list of formats to save spreadsheets that
can be shared with other users.
File Type Description
Excel Workbook (.xlsx) The default le type in Excel 2010.
Excel Macro-Enabled
Workbook (.xlsm)
A basic XML le type that can store VBA macrocode.
Excel 972003 Work-
book (.xls)
The le type that is used to save a le in a format that is compatible with
the previous versions of Excel.
Excel Template (.xltx) The default le type for an Excel template. It is used to save a workbook as
a template so that new workbooks can be created using its content, layout,
and format.
Excel Macro-Enabled
Template (.xltm)
The default le type for an Excel macro-enabled template.
Excel Binary Workbook
(.xlsb)
The binary le format in Excel 2010.
Excel 972003 Template
(.xlt)
The le type that enables you to save an Excel template that is compatible
with the previous versions of Excel.
PDF (.pdf) The le type that enables you to save an Excel document as an Adobe Por-
table Document Format (PDF) le.
LESSON 1
Lesson 1: Getting Started with Excel 29
Copyright Element K Corporation
The Save and Save As Commands
The Save command is used to save a new workbook or the changes made to an existing work-
book, without altering its name, le type, or location. The Save As command is used to save
an existing le with a new name, le type, or location. These commands can be accessed from
the File tab.
If you use the Save command when a workbook was not previously saved, the Save As dialog box will be dis-
played automatically.
The Save As Dialog Box
The Save As dialog box can be used to select a different folder, in which to save the
le. The default name of the le is displayed in the File name text box. You can also
specify a different le name for the le. From the Save as type drop-down list, you
can select a different format in which to save the le.
The Compatibility Checker Feature
The Compatibility Checker feature in Excel 2010 allows you to identify the compatibility of
objects and data in an Excel 2010 workbook, when you intend to save it in an earlier version
of Excel. In the Microsoft Excel - Compatibility Checker dialog box, you can view a list of
features in your Excel 2010 le that are not supported in earlier versions of Excel. The dialog
box also provides you with an option to convert these objects so that they are visible in earlier
versions of Excel. However, you will not be able to modify the objects once you convert them.
Figure 1-13: The Microsoft Excel Compatibility Checker dialog box.
How to Create a Basic Worksheet
Procedure Reference: Create a Workbook and Enter Data
To create a workbook and enter data:
1. Select the File tab and choose New.
2. In the Backstage view, in the Available Templates pane, below the Home button,
select Blank workbook.
3. In the right pane, in the Blank workbook pane, click Create.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 30

Copyright Element K Corporation
4. In the new worksheet, enter the desired data.
5. Use the appropriate navigation technique to select the next cell where you want to
enter data.
6. On the File tab, choose Save.
Procedure Reference: Perform a Compatibility Check on a Workbook
To perform a compatibility check on a workbook:
1. Select the File tab and choose Info.
2. In the Backstage view, in the Prepare for Sharing section, from the Check for
Issues drop-down list, select Check Compatibility.
3. In the Microsoft Excel Compatibility Checker dialog box, observe the features
that are not supported in the earlier version of Excel and click Cancel.
Procedure Reference: Save a Workbook Using the Save As Option
To save a workbook using the Save As option:
1. Select the File tab and choose Save As to display the Save As dialog box.
2. In the Save As dialog box, navigate to the desired folder.
3. In the File name text box, type a name for the le.
4. From the Save as type drop-down list, select the desired le format and click
Save.
If you select a format of an earlier version of Excel, the Compatibility Checker feature will auto-
matically check the le for any compatibility issues.
5. If necessary, in the Microsoft Excel - Compatibility Checker dialog box, click
Continue to convert the features that are not supported in the earlier version of
Excel.
Procedure Reference: Recover Unsaved Workbooks
To recover unsaved workbooks:
1. On the File tab, choose Recent.
2. In the Backstage view, in the right pane, select the Recover Unsaved Workbooks
option.
3. In the Open dialog box, select the unsaved workbook and click Open.
LESSON 1
Lesson 1: Getting Started with Excel 31
Copyright Element K Corporation
ACTIVITY 1-4
Entering Data in an Excel Workbook
Before You Begin:
The Excel 2010 application is open.
Scenario:
After gaining familiarity with the Excel interface you want to try entering data in a workbook.
You have the sales data for two new products on a printed paper. You want to enter this data
in an Excel worksheet so that you can add more related information as you receive it. You also
need to send a copy of this workbook to your coworker who is using Excel 2003.
What You Do How You Do It
1. Enter the column headings. a. On the Quick Access toolbar, click the
New button to open a blank workbook.
b. In cell A1, click and type Product and
then press Tab to move to cell B1.
c. In cell B1, type Quantity and press Tab.
d. In cell C1, type Price and press Tab.
2. Enter the name of the products. a. Select cell A2, type Pen and press Enter.
b. In cell A3, type Chart and press Enter.
3. Enter the data for quantity. a. Select cell B2, type 410 and press Enter.
b. In cell B3, type 385 and press Enter.
4. Enter the price of the products. a. Select cell C2, type 0.5 and press Enter.
b. In cell C3, type 2 and press Enter.
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 32

Copyright Element K Corporation
5. Save the workbook in different file
formats.
a. Select the File tab and choose Save As.
b. In the Save As dialog box, navigate to the
C:\084676Data\Getting Started with Excel
folder.
c. In the File name text box, type My
Invoice and click Save.
d. In the title bar, observe that the filename
is displayed along with the .xlsx exten-
sion.
e. Select the File tab and choose Save As.
f. In the Save As dialog box, from the Save
as type drop-down list, select Excel
972003 Workbook (*.xls) and click Save
to save the file in the XLS format.
g. In the title bar, observe that the filename
is displayed along with the .xls extension,
indicating that the file is saved in the
Excel 972003 format.
h. On the Quick Access toolbar, click the
Close button to close the file.
LESSON 1
Lesson 1: Getting Started with Excel 33
Copyright Element K Corporation
Lesson1Follow-up
In this lesson, you identied the elements of the Excel environment and created a basic
worksheet. This knowledge will enable you to work with the various options available in the
Excel interface.
1. What advantages do you see for using Excel to store data?
2. In your estimation, which options in the Backstage view will allow you to perform tasks
with ease?
LESSON 1
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 34

Copyright Element K Corporation
Performing Calculations in
an Excel Worksheet
In this lesson, you will perform calculations in an Excel worksheet.
You will:
Create formulas in a worksheet.
Insert functions in a worksheet.
Reuse formulas.
Lesson Time
45 minutes LESSON 2
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 35
Copyright Element K Corporation
Introduction
You created a basic worksheet and entered data in it. Now, you are ready to work with data
that contains trends and patterns to be analyzed. To identify these trends and patterns, you
need to perform some calculations based on the data. In this lesson, you will perform calcula-
tions in an Excel worksheet.
Performing calculations manually can be tedious and prone to errors. However, you can use
Excels formulas and built-in functions to perform data calculations quickly and free of errors.
TOPIC A
Create Formulas in a Worksheet
You entered data in an Excel worksheet. You may need to perform calculations based on for-
mulas to analyze the data further. In this topic, you will create formulas in Excel.
Calculating the values of data in worksheets manually can be time consuming and may even
lead to inaccurate or erroneous results. Excel provides you with an easier and quicker way to
calculate accurate values by creating formulas that help you automate calculations.
Formulas
Denition:
A formula is a symbolic representation that denes the standard procedure for a calcu-
lation. A formula comprises an expression to the right and a resultant to the left of an
equal sign. The expression in a formula usually consists of a combination of variables,
constants, and operators.
Example:
Figure 2-1: A mathematical formula to compute Simple Interest.
Excel Formulas
An Excel formula is a type of formula that can be used to perform calculations on data that is
entered in Excel worksheets. All formulas in Excel begin with an equal sign and contain vari-
ous components such as arguments and operators. The result of an Excel formula is stored in
the cell where the formula is entered. When the data of the arguments in an Excel formula
changes, the formula automatically recalculates the result. You can revise existing formulas by
pressing F2 and changing the arguments in the formula.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 36

Copyright Element K Corporation
An Excel formula has various elements.
Formula Element Description
References Addresses of cells or ranges of cells on a worksheet that refer to the location
of the values or data on which you need to apply a formula for calculation.
Operators Symbols that specify the kind of calculation that needs to be performed on
the components of a formula.
Constants Numbers or text that do not change in a formula.
Functions Predened formulas in Excel that are used to simplify complex calculations.
Mathematical Operators
Mathematical operators are symbols or signs that are used to represent an arithmetic operation
in Excel.
Mathematical Operator Function
Plus sign ( + ) Add
Minus sign ( - ) Subtract
Asterisk ( * ) Multiply
Forward slash ( / ) Divide
Caret symbol ( ^ ) Exponent
Open and closed parentheses Group computation instructions
Order of Evaluation
Excel allows you to create formulas that contain multiple mathematical operators.
These mathematical operators are computed in a specic order. When you use a com-
bination of operators, the order of evaluation can affect the result of the formula. Excel
evaluates the mathematical operators in the following order.
1. Computations enclosed in parentheses, wherever they appear in the formula.
2. Computations involving exponents.
3. Computations involving multiplication and division. Because they are equal with
regard to the order in which Excel performs them, the operation is performed in
the order in which it encounters them, which is from the left to the right.
4. Computations involving addition and subtraction. Excel also performs them in the
order in which it encounters them.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 37
Copyright Element K Corporation
Figure 2-2: Operators computed in a specific order.
Enforcing Precedence with Parentheses
You can use parentheses to group expressions within another expression. When an
expression is within parentheses, it means that it is evaluated rst and the result is
used to evaluate the remainder of the expression. If there are nested sets of parenthe-
ses, then the innermost set is evaluated rst.
Figure 2-3: A formula displaying precedence within parentheses.
Precedence of Operators for Percent vs. Exponentiation
In a formula with percent operators and exponentiation operators, Excel performs cal-
culations for the percent operators rst and then calculates the exponentiation
operators.
How to Create Formulas in a Worksheet
Procedure Reference: Create a Formula
To create a formula:
1. Select the cell in which you want to enter a formula.
2. Type the formula.
a. Type the equal sign.
b. Specify the arguments and operators for the formula.
Enter a number or cell reference, or select a cell.
Enter the operator.
If necessary, enter another argument.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 38

Copyright Element K Corporation
c. If necessary, enter additional arguments and operators to complete the for-
mula.
3. Press Enter to apply the formula and populate the cell with the calculated value.
Procedure Reference: Revise Formulas
To revise a formula:
1. Select the cell containing the formula to be revised.
2. Revise the formula
Double-click the cell and select the desired part of the formula that needs to
be revised, and then make the desired changes.
On the Formula Bar, select the desired part of the formula that needs to be
revised, and then make the desired changes.
ACTIVITY 2-1
Creating Formulas
Data Files:
C:\084676Data\Performing Calculations in an Excel Worksheet\New Product Sales.xlsx
Scenario:
The management of OGC Stores has planned to introduce four new products. You need to
determine the income from these products by analyzing the estimated sales data, expenses, tax,
and the prot after tax.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 39
Copyright Element K Corporation
What You Do How You Do It
1. Calculate the total income for the
products.
a. Select the File tab, choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Performing Calculations in
an Excel Worksheet folder, and open the
New Product Sales.xlsx file.
c. Select cell B6.
d. Type =B2+B3+B4+B5 and press Enter to
display the total income of the products.
e. Observe that the sum of the values in the
cell range B2:B5 is displayed in cell B6.
2. Calculate the net income for the
products.
a. Select cell B8.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 40

Copyright Element K Corporation
b. Type = and select cell B6.
c. Type - and select cell B7.
d. Observe the formula displayed in the For-
mula Bar and press Enter.
3. Calculate the tax and profit after tax
for the products introduced in the
market.
a. Verify that cell B9 is selected.
b. Click and type =B8*E1 and press Enter.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 41
Copyright Element K Corporation
c. Observe that the tax calculated by multi-
plying the net income with the tax rate is
displayed in cell B9.
d. In cell B10, calculate the profit after tax
by deducting the tax in cell B9 from the
net income in cell B8.
e. Observe that a green triangle appears at
the top-left corner of the cell B9 because
Excel has found this formula to be differ-
ent from other formulas in the column.
f. Select cell B9 and click the Error Check-
ing button.
g. From the displayed menu, choose Ignore
Error.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 42

Copyright Element K Corporation
h. Observe the green triangle that was in cell
B9 has disappeared.
i. Select the File tab and choose Save As.
j. In the Save As dialog box, in the File
name text box, type My New Product
Sales and click Save.
k. Close the workbook.
TOPIC B
Insert Functions in a Worksheet
You created formulas to perform simple calculations on the data in a worksheet. To perform
complex calculations to analyze data, you need to use more complex formulas. In this topic,
you will use Excels built-in functions to perform complex calculations.
You can create formulas to perform complex calculations on the data in a worksheet. However,
using functions for routine calculations is even more user friendly because you do not have to
type the entire formula each time. Excel provides you with various built-in functions that you
can use to analyze data.
Functions
Denition:
A function is a built-in Excel formula that can be used to perform calculations. Func-
tions contain a function name, followed by arguments within parentheses. The function
name is usually an abbreviated name of the function. Function arguments can be cell
references, constants, formulas, other functions, or logical values.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 43
Copyright Element K Corporation
Example:
Figure 2-4: A function comprising arguments.
Function Categories
Functions in Excel are categorized by their purposes. There are 12 categories of functions.
Category Used To
Financial Perform common business calculations, such as determining the repayment
for a loan, the future value or net present value of an investment, or a
schedule of cash ow.
Date & Time Work with date and time values in functions.
Math & Trig Perform mathematical calculations.
Statistical Perform statistical analysis on data. It includes the average, highest and
lowest values, median, standard deviation, and other statistics functions.
Look up & Reference Find values in a list or table, or used when you need to nd a reference to
a cell.
Database Query lists of data held in a worksheet. The calculations will be done on
records that meet the specied criteria.
Text Manipulate textual values.
Logical Check whether a condition is true or false.
Information Return the formatting, location, or contents of a cell.
Engineering Perform engineering analysis.
Cube Analyze the contents of a database to learn more about your business. It
represents sets of data derived from raw information stored in a standard
database.
Compatibility Develop spreadsheet applications that are compatible with earlier versions
of Excel.
The Formula AutoComplete Feature
The Formula AutoComplete feature is a dynamic feature in Excel that allows you to conve-
niently choose and enter functions without having to remember lengthy function names or
risking a spelling error. When you type the equal sign, followed by the rst few characters of a
function, a drop-down list with all the available function names beginning with the same char-
acters will be displayed. You can select the required function from the list and enter the
necessary arguments.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 44

Copyright Element K Corporation
Figure 2-5: The Formula AutoComplete feature listing functions beginning with su.
Commonly Used Functions
Excel provides you with various built-in functions that you can frequently use to work with
data.
Function Enables You To
SUM Add all the values specied in the argument.
AVERAGE Calculate the average of the values specied in the argument.
MIN Find the lowest of the values specied in the argument.
MAX Find the highest of the values specied in the argument.
COUNT Find the number of cells that contain numerical values in the specied range
in the argument.
COUNTA Find the number of cells that contain data within a specied range. This
function does not count the empty cells.
The AutoSum Feature
The AutoSum feature allows you to quickly insert commonly used functions in a worksheet to
perform basic mathematical and statistical analysis. You can also use this feature to build for-
mulas that compute the average value, count the number of values, and return the highest or
lowest values specied in a range.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 45
Copyright Element K Corporation
Figure 2-6: The AutoSum feature used to calculate a total.
How to Insert Functions in a Worksheet
Procedure Reference: Apply a Formula by Using the Formula AutoComplete
Feature
To apply a formula by using the Formula AutoComplete feature:
1. Select the cell in which you want to enter a formula.
2. In the worksheet, type the equal sign and the rst few letters of the function
name.
3. In the AutoComplete list, double-click a function to select it and enter the for-
mula.
4. Specify the arguments for the function.
5. Press Enter to apply the formula.
Procedure Reference: Insert Functions
To insert functions:
1. Select the cell in which you want to enter a formula.
2. On the Ribbon, select the Formulas tab.
3. Display the Insert Function dialog box.
In the Function Library group, click Insert Function or;
On the Formula Bar, click the Insert Function button.
4. Select a desired function.
In the Search for a function text box, type a brief introduction of what you
want to do and click Go.
From the Or select a category drop-down list, select a category.
5. In the Select a function list box, select the desired function and click OK.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 46

Copyright Element K Corporation
6. In the Function Arguments dialog box, enter the desired arguments.
7. At the bottom left of the Function Arguments dialog box, preview the displayed
formula result and click OK.
Procedure Reference: Calculate Values by Using the AutoSum Feature
To calculate values by using the AutoSum feature:
1. Enter the values that you want to calculate in the cells.
2. Click on the cell where you want the result to appear.
3. Display the result.
Apply the sum function.
On the Home tab, in the Editing group, click the AutoSum button or;
On the Formulas tab, in the Function Library group, click the
AutoSum button.
Apply other functions.
On the Home tab, in the Editing group, click the AutoSum drop-down
arrow, and from the displayed list, select a function or;
On the Formulas tab, in the Function Library group, click the
AutoSum drop-down arrow, and from the displayed list, select a func-
tion.
4. Ensure that the selected range of cells is correct.
If it is correct, press Enter.
If it is incorrect, drag the selection to the range of cells you want to include
in the calculation and press Enter.
ACTIVITY 2-2
Calculating Values Using Functions
Data Files:
C:\084676Data\Performing Calculations in an Excel Worksheet\Sales Ledger.xlsx
Scenario:
You want to analyze the sales performance of two employees, Simon and Barbara, for three
quarters. So you decide to calculate the total and average sales made by them. You also want
to nd the highest and lowest sales for each sales person for the three quarters, to recognize
the best performer.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 47
Copyright Element K Corporation
What You Do How You Do It
1. Calculate the total sales made by
Simon and Barbara for Q1 through
Q3.
a. On the File tab, choose Open.
b. In the Open dialog box, verify that the
C:\084676Data\Performing Calculations in
an Excel Worksheet folder is displayed and
open the Sales Ledger.xlsx file.
c. Select cell E3.
d. On the Home tab, in the Editing group,
click the AutoSum button.
e. Observe that the cell range B3:D3 is
selected.
f. Press Enter to display the total sales
made by Simon for Q1 through Q3.
g. Similarly, calculate the total sales made
by Barbara for Q1 through Q3.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 48

Copyright Element K Corporation
2. Calculate the average sales made by
Simon and Barbara.
a. Select cell F3 and type =av
b. In the AutoComplete list, double-click
AVERAGE.
c. In the worksheet, select the cell range
B3:D3 and press Enter to display the sales
average.
d. Similarly, calculate the average sales
made by Barbara for Q1 through Q3.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 49
Copyright Element K Corporation
3. Calculate the highest sales made by
Simon and Barbara.
a. Select cell G3.
b. Select the Formulas tab, and in the Func-
tion Library group, click Insert Function.
c. In the Insert Function dialog box, in the
Search for a function text box, type Max
and click Go.
d. In the Select a function list box, verify
that MAX is selected and click OK.
e. In the Function Arguments dialog box, to
the right of the Number1 text box, click
the Collapse Dialog button, and select
the cell range B3:D3.
f. In the Function Arguments dialog box, to
the right of the Number1 text box, click
the Expand Dialog button, and then
click OK.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 50

Copyright Element K Corporation
g. Similarly, calculate the highest sales for
Barbara from Q1 through Q3.
4. Calculate the lowest sales made by
Simon and Barbara.
a. Select cell H3.
b. On the Formulas tab, in the Function
Library group, click the AutoSum drop-
down arrow, and from the displayed list,
select Min.
c. In the worksheet, select the cell range
B3:D3 and press Enter to display the low-
est sales made by Simon.
d. Similarly, calculate the lowest sales for
Barbara from Q1 through Q3.
5. Save the worksheet. a. Select the File tab and choose Save As.
b. In the Save As dialog box, in the File
name text box, type My Sales Ledger and
click Save.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 51
Copyright Element K Corporation
TOPIC C
Reuse Formulas
You calculated data by using Excel formulas and functions. Sometimes, you may need to use
the same calculation to analyze data in a different cell of a worksheet. In this topic, you will
copy formulas and functions to reuse them.
When working with data in an Excel worksheet, you may want to reuse the formulas and func-
tions to apply in similar cells across the worksheet. Typing the formulas and functions every
time you need to use them is very tedious. Excel allows you to copy formulas and functions to
the required cells.
The Cut, Copy, and Paste Commands
Excel allows you to move or copy cells and their contents in a worksheet. You can select a
cell or its content and move it using the cut and paste options. To copy the content of a cell,
you can use the copy and paste options. Excel provides you with the paste preview feature that
enables you to view how the content will appear on the spreadsheet before you actually paste
it.
Figure 2-7: The Cut, Copy, and Paste commands used to reuse data.
Paste Special Options
You can copy and paste specic cell contents or attributes such as formats, formulas, and val-
ues by using the Paste Special option in Excel. By selecting the appropriate Paste Special
option, you can reuse a specic property of the selected cell.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 52

Copyright Element K Corporation
Paste Special Option Enables You To
Paste Paste the content with the formula and formatting.
Formulas Paste all the text, numbers, and formulas in the current selection
without the format of the source cell.
Formulas and Number Format-
ting
Paste the content with the number formats and formulas.
Keep Source Formatting Paste the content with the formatting that was applied to the source
cell.
No Border Paste the content in the cell without any borders if the source cell
had any borders.
Keep Source Column Width Paste the content and keep the column width the same as the
source cell.
Transpose Paste the content by transposing rows into columns or columns into
rows.
Values Paste the calculated value of the formula used in the source cell.
Values and Number Formatting Paste the calculated value of the formula used in the source cell
along with the number formatting.
Values and Source Formatting Paste the calculated value of the formula used in the source cell
along with the formatting.
Formatting Paste only the formatting applied to the source cell.
Operations Paste the results of the mathematical calculations based on the
value of the source cell.
The Add option will add the value of the source cell to the des-
tination cell.
The Subtract option will subtract the value of the source cell
from the destination cell.
The Divide option will divide the value of the source cell with
the destination cell.
The Multiply option will multiply the value of the source cell
with the destination cell.
Comments Paste only the comments that are attached to the source cell.
Validation Paste only the data validation rules applied to the source cell.
Paste as a Link Paste a reference to the source cell so that the value of the destina-
tion cell is linked to the value of the source cell.
Preview Icons
The Paste drop-down arrow provides a number of different paste special options, each
of which is represented by an icon. When you place the mouse pointer over an icon,
you can see a preview of the pasted content on the worksheet.
Option Icon
Paste
Formulas
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 53
Copyright Element K Corporation
Option Icon
Formulas & Number Format-
ting
Keep Source Formatting
No Borders
Keep Source Column Widths
Transpose
Values
Values & Number Formatting
Values & Source Formatting
Formatting
Paste Link
Picture
Linked Picture
Live Preview
Live Preview is a dynamic feature of Excel that allows you to preview how a formatting will
appear on a worksheet before you actually apply it. For example, when you move the mouse
pointer over various options in the Font drop-down list, you can see a live preview of how
each font option will affect the appearance of the selected cell without actually applying the
font to the object. However, this temporary formatting is removed when the mouse pointer is
moved away from the selected option.
Figure 2-8: Preview of how a formatting will appear on a worksheet.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 54

Copyright Element K Corporation
Relative References
Denition:
A relative reference is a cell reference in a formula that changes when a formula is
copied from one cell to another, based on the new position of the formula. Relative
references are used to create formulas that use values, which are stored in a cell that is
relative to the cell in which the result is to be displayed.
Example:
Figure 2-9: A formula using a relative reference.
Absolute References
Denition:
An absolute reference is a cell reference in a formula that does not change when the
formula is copied from one cell to another. Absolute references are used in formulas to
refer to the values in cells whose reference does not change in relation to the cells
where the result is to be stored. Absolute references contain a dollar sign before the
column and row headings in the cell reference.
Example:
Figure 2-10: A formula using an absolute reference.
Mixed References
A mixed reference is a cell reference that contains a mix of absolute and relative refer-
ences. When a formula with a mixed reference is copied from one cell to another, the
relative reference changes, while the absolute reference does not change. Mixed refer-
ences contain a dollar sign either before the column or the row reference.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 55
Copyright Element K Corporation
How to Reuse Formulas
Procedure Reference: Copy a Formula or Function
To copy a formula or function:
1. Select the cell that contains the formula you want to copy.
2. On the Home tab, in the Clipboard group, click Copy.
3. Select the destination cell where you want to paste the formula.
4. In the Clipboard group, click Paste.
The Cut, Copy, and Paste Shortcut Keys
The following table identies the shortcut keys for the Cut, Copy, and Paste options.
Action Shortcut Key
Cut Ctrl+X
Copy Ctrl+C
Paste Ctrl+V
Procedure Reference: Create an Absolute Reference
To create an absolute reference:
1. Select the cell with the formula that needs to refer to constant cell values.
2. On the Formula Bar, in the formula box, click and type the dollar sign in front of
the column and row references to make the cell reference constant in the formula.
You can press F4 on the keyboard to add the $ sign in front of the column and row references.
3. Press Enter to apply the change made to the formula.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 56

Copyright Element K Corporation
ACTIVITY 2-3
Copying Formulas and Functions
Data Files:
C:\084676Data\Performing Calculations in an Excel Worksheet\My Sales Ledger.xlsx
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
Your manager has asked you to analyze the sales performance of the remaining employees on
your team and also asked for information on the commission earned by them. For each
employee, the formula for calculating the commission should refer to the commission rate
value specied in the worksheet.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 57
Copyright Element K Corporation
What You Do How You Do It
1. Calculate the total, average, highest
and lowest sales for the remaining
employees.
a. Select cell E4.
b. Select the Home tab, and in the Clip-
board group, click the Copy button.
c. Observe that the cell E4 has a dotted
rectangle around it, indicating that the
formula in the cell has been copied.
d. Select the cell range E5:E26.
e. On the Home tab, in the Clipboard group,
click Paste.
f. Observe that the total sales for the
remaining employees is calculated.
g. Select cell F4, and on the Home tab, in
the Clipboard group, click the Copy but-
ton.
h. Select the cell range F5:F26.
i. On the Home tab, in the Clipboard group,
click Paste.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 58

Copyright Element K Corporation
j. Similarly, calculate the highest and lowest
sales for the remaining employees.
2. Calculate the commission for employ-
ees based on the commission rate.
a. Select cell I3, type =E3*L2 and press
Enter.
b. Select cell I3, and on the Home tab, in
the Clipboard group, click the Copy but-
ton.
c. Select cell I4, and on the Home tab, in
the Clipboard group, click Paste.
d. Observe that cell I4 displays the value 0
because the formula used in the cell I4
now refers to cell L3 instead of cell L2 for
the commission rate.
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 59
Copyright Element K Corporation
3. Modify the formula to use an abso-
lute reference to the cell containing
the commission rate.
a. Select cell I3, and in the Formula Bar
text box, click before L and press F4 to
convert the reference to cell L2 as an
absolute reference.
b. Observe that the $ sign is added before
the column and row references and press
Enter.
c. Select cell I3, and on the Home tab, in
the Clipboard group, click the Copy but-
ton.
d. Select cell I4, and in the Clipboard group,
click Paste.
e. Observe that the cell value in cell I4 has
changed and the cell reference for the
commission rate remains as L2.
f. Copy the formula in cell I4, and select the
range I5:I26.
g. Click Paste to calculate the commission
rate in the cells I5 to I26.
h. On the Quick Access toolbar, click the
Save button.
i. Close the workbook.
LESSON 2
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 60

Copyright Element K Corporation
Lesson2Follow-up
In this lesson, you performed calculations in an Excel worksheet. By using the formulas and
built-in functions in Excel, you can perform calculations easily and quickly, and without any
errors.
1. Which built-in functions in Excel do you expect to use the most often?
2. Do you think the Formula AutoComplete feature is beneficial to you? Why?
LESSON 2
Lesson 2: Performing Calculations in an Excel Worksheet 61
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 62

NOTES
Copyright Element K Corporation
Modifying an Excel
Worksheet
In this lesson, you will modify an Excel worksheet.
You will:
Edit worksheet data.
Find and replace data.
Manipulate worksheet elements.
Lesson Time
45 minutes LESSON 3
LESSON 3
Lesson 3: Modifying an Excel Worksheet 63
Copyright Element K Corporation
Introduction
You entered formulas and functions in Excel worksheets to perform calculations. When you
work with worksheets, you will often need to update data in them. In this lesson, you will
modify the content of an Excel worksheet.
To modify a paper-based spreadsheet, you may have to re-create the entire spreadsheet to
include new additions or other changes. Excel minimizes the effort required to revise and
update spreadsheets. By using Excel, you can easily modify the data in an existing worksheet,
rather than creating a new worksheet every time you need to change data.
TOPIC A
Edit Worksheet Data
You performed calculations in an Excel workbook, which enables you to interpret data better.
There may be instances when you have to work with existing worksheets, and you need to
alter the data presented in them. In this topic, you will edit the data in an Excel worksheet.
Suppose you join a company, and you are asked to create an expense report for the year by
using Excel. You will not set out to create a report from scratch, rather you may use an exist-
ing report as a base and edit its various data to create a new report. You can use various Excel
options to edit data, which will enable you to complete the tasks quickly and efficiently.
The Undo and Redo Commands
The Undo command allows you to reverse the most recent actions that you performed on a
workbook. The Redo command allows you to cancel the most recent Undo actions. You can
access these commands from the Quick Access toolbar or by using the shortcut keys, Ctrl+Z
for Undo and Ctrl+Y for Redo. The Undo drop-down list displays all the recently performed
actions that can be reversed, and the Redo drop-down list displays all the recently undone
actions. You can undo or redo several actions at once by selecting the actions from these lists.
However, some actions such as saving a workbook cannot be undone.
Figure 3-1: The Undo drop-down list listing actions that can be undone.
The Auto Fill Feature
The Auto Fill feature allows you to ll cells with a series of data. You can enter data in one or
more cells to establish a pattern for the series, select the cells, and drag the ll handle to ll
the cells with the series. The ll handle is the box at the bottom-right corner of the selected
cell or cell range. You can specify how the data should be lled in the target cells by using the
options on the Auto Fill Options menu.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 64

Copyright Element K Corporation
Figure 3-2: Data entered using the Auto Fill feature.
Auto Fill Options
The Auto Fill options in Excel 2010, help you specify how a selected range of cells should be
lled in with data.
Auto Fill Option Allows You To
Copy Cells Fill the selected range with the selected data in a cell or cells.
Fill Series Fill the selected range with the series of data specied by the pattern in the
selected cells.
Fill Formatting Only Fill the selected range with only the formatting used in the selected cell, but
not the data.
Fill Without Formatting Fill the selected range with the data in the selected cell, but without the for-
matting that is applied to that data.
Other Auto Fill Options
The Auto Fill options vary according to the data in the rst cell of the selected range
of cells. For instance, when a day is typed in the rst cell, the Auto Fill Options
menu will display the Fill Days and Fill Weekdays options. You can then select an
option to include or exclude the weekends from the series, as required.
The Copy Cells Option
The Copy Cells option on the Auto Fill Options menu allows you to copy data to the
destination cell or range of cells. The Copy Cells option simplies the procedure to
copy content by allowing the paste action to be repeated for the selected range.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 65
Copyright Element K Corporation
The Transpose Option
The Transpose option in the Paste command shifts the vertical and horizontal orientation of
the columns and rows in a worksheet. For example, if data is set up with months as rows and
department names as columns, the Transpose option will reverse the rows and columns when
the data is pasted so that the months become columns and the department names become rows.
Clear Options
The Clear button on the Home tab in the Editing group provides you with options to clear
formatting, data, or comments from the cells in a worksheet. You can also choose Clear All to
remove all the data at once.
Figure 3-3: The Clear drop-down list displaying clear options.
Clear Option Used to Clear
Clear All Everything from the selected cells including contents, formatting, data, and
comments.
Clear Formats Only the formatting applied to the selected cells.
Clear Contents Only the contents in the selected cells.
Clear Comments Comments attached to the selected cells.
Clear Hyperlinks Hyperlinks from the selected cells.
How to Edit Worksheet Data
Procedure Reference: Edit Cell Data
To edit cell data:
1. Select the cell that contains the data you want to edit.
2. Edit the cell data.
Type the new data in the cell and press Enter to replace the cell data or;
Click the contents displayed on the Formula Bar and edit the data to update
cell data.
3. Type the new data in the cell and press Enter.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 66

Copyright Element K Corporation
Procedure Reference: Fill Cells with a Series of Data Using the Auto Fill Feature
To ll cells with a series of data by using the Auto Fill feature:
1. Enter the required data to establish a pattern for the series of data.
2. Select cells with values that have the established pattern.
3. Drag the ll handle to the ending cell of the series.
4. If necessary, click the Auto Fill Options drop-down arrow and select an option to
ll cells.
Procedure Reference: Clear Cell Data
To clear cell data:
1. Click in a cell and type the data.
2. Edit the data in the cell.
a. Double-click the cell and type the new data or;
b. Select the cell that contains the data to be edited and then type the new data.
3. Clear the cell data.
Select the cell that contains the data to be edited and press Delete.
Select a range of cells whose data has to be cleared, and on the Home tab, in
the Editing group, click the Clear button and select the appropriate clear
option.
Procedure Reference: Move Data Between Cells
To move data between cells:
1. Select the cells that contain the data you want to move.
2. Move the selected cells.
Position the mouse pointer on the selection border until the mouse pointer
changes to a copy/move pointer and drag the selection to the destination or;
On the Home tab, in the Clipboard group, click the Cut button, select the
cell to which you want to move the data, and click Paste.
Procedure Reference: Transpose Data
To transpose data:
1. Select the data that you want to transpose.
2. Copy the data to the clipboard.
Cut the selected cells to transpose the data.
Copy the selected cells to transpose a copy of the data.
3. Select the destination cell in which you want the transposed data to appear.
4. On the Home tab, in the Clipboard group, from the Paste drop-down list, select
the Transpose option.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 67
Copyright Element K Corporation
Procedure Reference: Use the Undo and Redo Commands
To use the Undo and Redo commands:
1. Perform an action in a worksheet.
2. Undo the action.
On the Quick Access toolbar, click the Undo button to undo one action.
On the Quick Access toolbar, click the Undo drop-down arrow, and from the
displayed list, select the desired actions to be undone.
3. If necessary, redo undone the action.
On the Quick Access toolbar, click the Redo button to redo one action.
On the Quick Access toolbar, click the Redo drop-down arrow, and from the
displayed list or undone actions, select the desired actions to be redone.
ACTIVITY 3-1
Manipulating Data
Data Files:
C:\084676Data\Modifying an Excel Worksheet\Sales Ledger.xlsx
Scenario:
You presented the worksheet that includes a summary of sales data of your team to the man-
agement. The management has suggested the following changes, which you want to implement
in the worksheet.
1. Add the IDs of all employees.
2. Change the region of an employee who has been recently transferred.
3. Delete the ID of an employee who has left the company.
4. Move the information below the list of employees along with other information to
the right of the list of employees.
5. Display the names of employees in a single row in a new worksheet.
What You Do How You Do It
1. Enter the IDs of all employees. a. From the File tab, choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Modifying an Excel
Worksheet folder, and open the Sales
Ledger.xlsx file.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 68

Copyright Element K Corporation
c. Select cell L3, type S1001 and press
Enter to enter the ID of the first
employee.
d. Select cell L3, click and drag the fill
handle to cell L20 to fill in the corre-
sponding cells with the employee IDs of
other employees.
e. Observe that the IDs of other employees
are filled in the respective cells by auto-
matically increasing the numeric value of
the first employees ID by 1.
2. Edit the employee details. a. Select cell B8, type West and press
Enter.
b. Select cell L9.
c. Press Delete.
3. Move the contents of the cells. a. Select the cell range A22:B22.
b. On the Home tab, in the Clipboard group,
click Cut.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 69
Copyright Element K Corporation
c. Select cell O5, and in the Clipboard
group, click Paste.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 70

Copyright Element K Corporation
4. Transpose the contents from a col-
umn to a row in a different
worksheet.
a. Select the cell range A3:A20.
b. On the Home tab, in the Clipboard group,
click the Copy button.
c. Click Sheet2.
d. Verify that cell A1 is selected. In the Clip-
board group, from the Paste drop-down
list, in the Paste section, select Trans-
pose.
e. Observe that the names of the employees,
which were displayed in rows in Sheet1,
are copied and transposed to columns in
Sheet2.
f. Save the worksheet as My Sales Ledger
LESSON 3
Lesson 3: Modifying an Excel Worksheet 71
Copyright Element K Corporation
TOPIC B
Find and Replace Data
You edited the data in a worksheet. When you have a worksheet with a large volume of data,
it would be difficult for you to identify and update only specic values. In this topic, you will
search and replace specic data in a worksheet.
When you need to update specic data in a worksheet, you can manually search for that data
by visually examining each cell in the worksheet. This is a time-consuming and tiresome pro-
cess, and you may also end up not locating the data altogether. Excel provides you with
options to quickly and accurately locate and change the required data and ensure that the
worksheet is free of any spelling mistakes.
Cell Names
You can name a cell or a range of cells to identify it by the name you specify, rather than
using the cell reference. Cell names are not case sensitive and can be up to 255 characters
long, but they cannot contain spaces or begin with a number. When you want to move to a
named cell or range of cells, enter the name of the cell in the Name Box and press Enter or
display the Name Box drop-down list and select the name.
Figure 3-4: A named cell range.
The Find Command
The Find command allows you to locate specic data within a workbook. The Find tab of the
Find and Replace dialog box contains various options that allow you to perform the search.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 72

Copyright Element K Corporation
Figure 3-5: The Find tab of the Find and Replace dialog box.
Option Description
The Find what drop-
down arrow
Displays all the recently searched terms.
The Options button Provides advanced search options such as specifying the search location or
matching the text casing.
The Find All button Allows you to locate all the instances of the search terms occurrence.
The Find Next button Allows you to search for the next occurrence of the search term.
The Close button Allows you to close the Find and Replace dialog box.
Access the Find Command
You can access the Find command from the Find & Select drop-down list in the Edit-
ing group on the Home tab, or by using the Ctrl+F shortcut key.
Advanced Search Options
Excel provides you with advanced options that allow you to search for specic infor-
mation.
Option Used To
The Format button Search for formats. You can also click the drop-down arrow on this
button to access the Find Format option.
The Within drop-down
list
Restrict the search to either the active worksheet or the entire work-
book.
The Search drop-down
list
Specify whether the search should be performed by row or by col-
umn.
The Look In drop-down
list
Specify whether the search target should include formulas, values,
or comments.
The Match Case check
box
Specify whether the search has to be for the text characters with the
exact casing as specied in the search criteria.
The Match Entire Cell
Contents check box
Specify whether the search has to be for the exact and complete
characters that are specied in the search criteria.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 73
Copyright Element K Corporation
The Replace Command
The Replace command allows you to replace any existing data in a workbook with new data.
You can access this command from the Find & Select drop-down list in the Editing group on
the Home tab or by using the Ctrl+H shortcut key. The Replace tab in the Find and Replace
dialog box contains various options that allow you to select specic data and replace it.
Figure 3-6: The Find and Replace dialog box with the Replace tab displayed.
The Spelling Dialog Box
The Spelling dialog box allows you to check for spelling and grammar errors in a workbook.
The spell checker ags the text, such as proper words, numbers, dates, or time, that Excel does
not recognize, as errors. The Spelling dialog box provides you with various options to use and
customize the spell checker according to your requirements.
Figure 3-7: Word suggestions displayed in the Spelling dialog box.
Option Description
Not in Dictionary Displays the misspelled word that the spell checker identies as an error.
Ignore Once Ignores the current occurrence of the misspelled word.
Ignore All Ignores all the occurrences of the misspelled word.
Add to Dictionary Adds the misspelled word to the dictionary so that the spell checker recog-
nizes the word as correct.
Suggestions Lists all the possible correct spellings for the word that is displayed in the
Not in Dictionary text box.
Change Replaces the current occurrence of the misspelled word with the word
selected in the Suggestions list box.
Change All Replaces all the occurrences of the misspelled word with the word selected
in the Suggestionslist box.
Undo Last Reverses the most recent action performed in the Spelling dialog box.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 74

Copyright Element K Corporation
Option Description
Options Displays the Excel Options dialog box.
Dictionary Language Provides options to select language for checking the text.
AutoCorrect Corrects all the occurrences of the misspelled word that is displayed in the
Not in Dictionary text box. This will automatically correct the misspelled
word across worksheets.
How to Find and Replace Data
Procedure Reference: Find and Replace Data in a Workbook
To nd and replace cell data:
1. On the Home tab, in the Editing group, click Find & Select, and from the drop-
down list, select Replace.
2. In the Find and Replace dialog box, on the Replace tab, in the Find what text
box, type the search criteria.
3. In the Replace with text box, type the replacement data.
4. Click Find Next to nd the next instance matching the search criteria.
Click Find All to locate all instances that match the search criteria and display a list of hyperlinks
at the bottom of the Find and Replace dialog box. Click each hyperlink to make that cell active.
5. Click Replace to replace the selected instance that matches the search criteria
with the replacement data.
Click Replace All to replace all instances that match the search criteria with the replacement
data.
6. Repeat steps 4 and 5 until you have replaced the required data in the workbook.
7. Click Close to return to the worksheet.
Procedure Reference: Check a Worksheet for Spelling Errors
To check a worksheet for spelling errors:
1. On the Review tab, in the Proong group, click Spelling.
2. In the Spelling dialog box, address the words that are agged as misspelled words
in the worksheet.
Click Ignore Once to ignore the current instance of the misspelled word, or
click Ignore All to overlook all instances of the misspelled word.
Click Add to Dictionary to add the misspelled word to the dictionary so that
the spell checker recognizes the word as correct.
In the Suggestions list box, select the correct word and click Change to
replace the current occurrence of the misspelled word, or click Change All
to replace all the occurrences of the misspelled word.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 75
Copyright Element K Corporation
3. In the Microsoft Excel message box, click OK to acknowledge the completion of
the spell-check process.
Procedure Reference: Enter a Cell Range Denition in the Formula Bar
To enter a cell range denition in the Formula Bar:
1. Select a cell in the worksheet.
2. In the Formula Bar, type = and start typing the formula.
3. Enter a cell range.
a. Type an absolute or relative cell reference to specify the starting cell of the
range.
b. Type colon.
c. Type an absolute or relative cell reference to specify the ending cell of the
range.
4. If necessary, type other values, operators, and cell references in the formula.
5. Press Enter
ACTIVITY 3-2
Searching for Data in a Worksheet
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
The Human Resources department has informed you about the change in name for an
employee and also a change in region for another employee of your team. You want to quickly
locate the details of these employees in your worksheet and update the necessary information
without having to scroll through the entire data.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 76

Copyright Element K Corporation
What You Do How You Do It
1. Add cell names to specific cells. a. Select Sheet1, and then select cell P4.
b. In the Name Box, click and type Employ-
ees and press Enter to name the cell.
c. Select the cell range A5:L5.
d. In the Name Box, click and type Alice and
press Enter to name the cells.
2. Go to a named cell directly without
scrolling.
a. In the Name Box, click and type Employ-
ees and press Enter to go to cell P4 to
view the cell that contains the employee
total.
b. Click the Name Box drop-down arrow, and
from the displayed list, select Alice.
c. Observe that the entire data of the
employee, Alice, is selected.
3. Update the region for the employee
Maureen.
a. Select any cell to deselect the range
A5:L5.
b. On the Home tab, in the Editing group,
from the Find & Select drop-down list,
select Find.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 77
Copyright Element K Corporation
c. In the Find and Replace dialog box, in the
Find what text box, type Maureen and
click Find Next to observe that the cell
containing the name Maureen is selected.
d. Click Close to close the Find and Replace
dialog box.
e. Select cell B17, type Mideast and press
Enter.
4. Replace the employee name Simon
with Simone.
a. On the Home tab, in the Editing group,
from the Find & Select drop-down list,
select Replace.
b. In the Find and Replace dialog box, in the
Find what text box, double-click Maureen
and type Simon
c. In the Replace with text box, click and
type Simone
d. In the Find and Replace dialog box, click
Find Next.
e. Click Replace to replace the text.
f. Click Close to close the Find and Replace
dialog box.
g. Save the worksheet.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 78

Copyright Element K Corporation
ACTIVITY 3-3
Checking a Worksheet for Spelling Errors
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
Before you submit your sales report to the management, you want to ensure that the report is
free of any spelling errors.
What You Do How You Do It
1. Display the Spelling dialog box. a. Select cell A1, and on the Ribbon, select
the Review tab.
Select cell A1 to check for spelling errors from the
beginning of the worksheet.
b. In the Proofing group, click Spelling to
display the Spelling: English (U.S.) dialog
box.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 79
Copyright Element K Corporation
2. Correct the misspelled words across
the worksheet.
a. In the Spelling: English (U.S.) dialog box,
in the Not in Dictionary text box, observe
that the misspelled word is displayed.
Also, observe that in the Suggestions list
box, suggestions for the misspelled word
are displayed.
b. Click Change to change to the recom-
mended word Commission.
c. Observe that the next misspelled word is
displayed in the Not in Dictionary text
box, with a suggestion for the word in the
Suggestions list box.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 80

Copyright Element K Corporation
d. Click Change to replace Southweet with
Southwest.
e. In the Microsoft Excel message box, click
OK to acknowledge the completion of the
spell-checking process.
f. Save the worksheet.
TOPIC C
Manipulate Worksheet Elements
You proofed the worksheet data for any spelling errors. The data in some of the cells may
exceed the default width of the cell resulting in poor readability of contents in the worksheet.
You may need to alter the width of the cell to view such data easily. In this topic, you will
adjust the width of columns and the height of rows.
It will be difficult to work with a worksheet where data you need to view is obscured within
rows that are too short or columns that are too narrow. It also takes away the visual appeal of
the worksheet. By changing the column width and row height, you can ensure that the data
stored in the rows and columns will t correctly within their respective cells.
The Insert and Delete Options
The Insert and Delete options in Excel allow you to insert or delete cells, columns, and rows.
When you access the Insert command, the Insert dialog box is displayed providing you with
options to insert a cell, row, or column, and shifting the consecutive cell to the right or down.
When you access the Delete command, the Delete dialog box is displayed providing you with
options to delete a cell, row, or column, and shifting the consecutive cell to the left or up.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 81
Copyright Element K Corporation
Figure 3-8: The Insert drop-down list.
Column Width and Row Height Alteration
Methods
Excel provides you with several methods that you can use to adjust the column width and row
height in a worksheet. These options include:
Automatically adjusting the column width and row height using the AutoFit option.
Manually adjusting the column width and row height to t the content by dragging or by
double-clicking the borders between two columns or rows.
Setting a specic column width and row height using the options in the Column Width
or Row Height dialog box.
Excel displays the hash (####) sign in cells when the numeric or date and time data is too wide to be displayed
within the current column width. When the column width is adjusted, the hash (####) sign disappears and the
numeric or date and time data is displayed.
The Hide and Unhide Options
The Hide option allows you to hide columns or rows in a worksheet, while the Unhide option
allows you to unhide the hidden columns or rows. When rows and columns are hidden, they
exist in the worksheet, but are not visible, until they are unhidden. The references to cells, col-
umns, or rows that are hidden do not change.
Figure 3-9: A worksheet with hidden columns.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 82

Copyright Element K Corporation
How to Manipulate Worksheet Elements
Procedure Reference: Change the Column Width and Row Height
To change the column width and row height:
1. Change the column width.
Change the column width to a specic value.
a. Select the column that you want to change.
Right-click the selection and choose Column Width.
On the Home tab, in the Cells group, from the Format drop-down
list, under the Cell Size section, click Column Width.
b. In the Column Width dialog box, in the Column width text box, type
a new value for the column width.
c. Click OK to change the column width.
In the column header, drag the columns right border to the left or right to
adjust the column width manually.
In the column header, double-click the right border to automatically t the
overowing text.
2. Change the row height.
Change the row height to a specic value.
a. Select the row that you want to change.
Right-click the selection and choose Row Height.
On the Home tab, in the Cells group, from the Format drop-down
list, under the Cell Size section, click Row Height.
b. In the Row Height dialog box, in the Row height text box, type a new
value for the row height.
c. Click OK to change the row height.
In the row header, drag the upper border above or below to adjust the row
width manually.
In the row header, double-click the above border to automatically t the
overowing text.
Procedure Reference: Insert or Delete Rows or Columns
To insert or delete rows or columns:
1. Select a row or column.
2. On the Home tab, in the Cells group, click Insert or Delete.
Procedure Reference: Insert or Delete a Range of Cells
To insert or delete a range of cells:
1. Select a range of cells.
2. On the Home tab, in the Cells group, from the Insert or Delete drop-down list,
select Insert Cells or Delete Cells.
3. In the Insert or Delete dialog box, select the preferred option to shift cells up,
down, left, or right and click OK.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 83
Copyright Element K Corporation
Procedure Reference: Hide or Unhide Columns or Rows
To hide or unhide columns or rows:
1. Select the columns or rows that you want to hide or unhide.
Click a column header to select a single column.
Click a column header, hold down Ctrl and click other column headers to
select the desired columns.
Click a column header, hold down Shift and click another column to select a
series of columns.
Click a row header to select a single row.
Click a row header, hold down Ctrland click other row headers to select the
desired rows.
Click a row header, hold down Shiftand click another row to select a series
of rows.
To select hidden rows or columns, select a range that includes visible rows or columns on either
side or type the desired range in the Name Box. You can also select multiple rows and columns
to hide or unhide.
2. Hide the columns or rows.
On the Home tab, in the Cells group, from the Format drop-down list, from
the Hide & Unhide submenu, select an option to hide the selected rows or
columns.
Select Hide Rows to hide the selected rows or;
Select Hide Columns to hide the selected columns.
Right-click anywhere in the selected column or row and choose Hide.
3. If necessary, unhide the column or row.
On the Home tab, in the Cells group, from the Format drop-down list, from
the Hide & Unhide submenu, select an option to unhide the selected rows or
columns.
Select Unhide Rows to unhide the selected rows or;
Select Unhide Columns to unhide the selected columns.
Right-click anywhere in the selected column or row and choose Unhide.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 84

Copyright Element K Corporation
ACTIVITY 3-4
Adjusting Columns
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
While reviewing the sales report, you realize that the column containing details of employees
name and region occupy more space than needed. Also, you want to hide the columns contain-
ing details of quarterly results because you need only the employees total and average sales
gures to make a judgement.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 85
Copyright Element K Corporation
What You Do How You Do It
1. Adjust the width of columns A and B. a. Place the mouse pointer over the column
header for column A, and when the
pointer turns to an arrow, click to select
the entire column A.
b. Select the Home tab, and in the Cells
group, click the Format drop-down arrow,
and from the displayed list, select AutoFit
Column Width.
c. In the column header, double-click the
border between columns B and C to adjust
the contents in column B.
d. Observe that the column width of columns
A and B is adjusted to fit the contents in
the cell.
2. Hide columns C, D, E, and F. a. Select column C.
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 86

Copyright Element K Corporation
b. Hold down Shift and select column F.
c. On the Home tab, in the Cells group, click
the Format drop-down arrow, and from
the displayed list, select Hide & Unhide,
and then select Hide Columns.
d. Observe the column headings. Because
columns C through F are hidden, the
worksheet displays column G after column
B.
e. Save and close the worksheet.
LESSON 3
Lesson 3: Modifying an Excel Worksheet 87
Copyright Element K Corporation
Lesson3Follow-up
In this lesson, you modied an Excel worksheet. Modifying worksheets lets you update the
existing data based on your requirements, rather than creating a new worksheet every time the
data changes.
1. What are the advantages of using the Find and Select options?
2. When will you use various data manipulation options such as Auto Fill, Undo, and
Redo?
LESSON 3
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 88

Copyright Element K Corporation
Modifying the Appearance
of a Worksheet
In this lesson, you will modify the appearance of data within a worksheet.
You will:
Apply font properties.
Add borders and colors to cells.
Align content in a cell.
Apply number formatting.
Apply cell styles.
Lesson Time
1 hour(s) LESSON 4
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 89
Copyright Element K Corporation
Introduction
You modied data in a worksheet. You may now want to enhance the appearance of the
worksheet by using certain formatting options, which will help you quickly differentiate the
data in the worksheet. In this lesson, you will modify the appearance of the worksheet.
A worksheet that is not formatted properly will look cluttered and it will be difficult to analyze
its data. By highlighting specic cells, rows, or columns and using other formatting methods,
you can not only ensure that the required data stands apart, but also classify sets of data visu-
ally. With Excel, your formatted worksheets can help you analyze data and locate the desired
information efficiently.
TOPIC A
Apply Font Properties
You modied the data in a worksheet. You now want to emphasize important data in the
worksheet so that it is easy to locate necessary information and enhance the appearance of the
worksheet. In this topic, you will apply font properties to data in an Excel worksheet.
Newspaper headlines are always larger and much more prominent from the text of the news
stories. This is done to attract readers to a particular news item, and enables a reader to easily
and quickly scan and locate important news stories. In a similar way, altering the font proper-
ties of the data in a worksheet helps you highlight key points from the rest of the data. By
modifying the font properties, you can also change the appearance of the worksheet to make it
look visually appealing.
Fonts
Denition:
A font is a predened typeface that can be used for formatting characters. Each font
has a unique style and character spacing. Fonts can be either built in or user dened.
Typefaces can be letters, numbers, punctuation marks, symbols, and other graphical
characters called ideograms.
Example:
Figure 4-1: Various font faces.
The Font Group
The Font group on the Home tab contains options to set font properties such as font face,
size, and color. You can also access these options from the Format Cells dialog box.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 90

Copyright Element K Corporation
Figure 4-2: The Font group on the Home tab.
The Format Cells Dialog Box
The Format Cells dialog box contains various options for formatting the appearance of data in
an Excel worksheet. You can preview the format result in the preview section, before you
apply it to the data. The Format Cells dialog box consists of six tabs containing formatting
options.
Tab Enables You To
Number Specify the options for changing the number formatting such as
Currency, Short Date, Long Date, and Time.
Alignment Specify the options for changing the alignment and orientation of
data in a cell range. It also contains options to wrap text and merge
cells.
Font Specify the font properties such as font, style, size, and color.
Border Specify the border formatting options such as Line and Color.
Fill Specify the ll color options such as Background Color, Pattern
Color, and Pattern Style.
Protection Specify the protection options such as locking cells or hiding for-
mulas in a worksheet.
The Format Painter
The format painter provides you with an easy option to copy only the formatting that is
applied to a cell to another cell or cell range, without copying the data. The format painter
works similar to a paintbrush and can be used to apply format changes to data, or to copy a
color scheme from one cell to another. When you select the format painter, the cursor includes
a paint brush graphic. You can double-click the Format Painter button to apply the same for-
matting to multiple cells in a worksheet.
Galleries
A gallery is a repository for elements that belong to the same category. In Excel, a gallery acts
as a central location for accessing various preset styles and appearance settings for an object.
Excel provides galleries for various options such as cell styles, tables, shapes, and charts. Gal-
leries enable you to choose from any of the preset formats and styles to quickly alter
worksheet objects.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 91
Copyright Element K Corporation
Figure 4-3: A gallery providing various options for shapes.
The Mini Toolbar
The Mini toolbar is a oating toolbar that is displayed when you right-click a cell or select
data within a cell. It combines some of the options available in the various groups on the
Home tab, and provides easy and quick access to some of the frequently used formatting
options. This toolbar is automatically displayed when data is selected within a cell and remains
semi-transparent until the mouse pointer is hovered over it. Although the Mini toolbar is not
customizable, you can turn it off.
Figure 4-4: The Mini toolbar displayed when text in a cell is selected.
How to Apply Font Properties
Procedure Reference: Change the Font Face, Size, Color, and Style Using the
Font Group
To change the font face, size, color, and style using the Font group:
1. Select the cell that contains the data you want to format.
2. On the Home tab, in the Font group, change the font properties.
From the Font drop-down list, select the desired font face.
Change the font size.
From the Font Size drop-down list, select the desired font size.
Click the Increase Font Size or Decrease Font Size button.
From the Font Color drop-down list, select the desired color.
If necessary, click the Bold, Italic, and Underline buttons to apply the
respective formatting.
You can also right-click the cell that contains the data you want to format, and on the Mini
toolbar, select the desired font face, size, color, and style.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 92

Copyright Element K Corporation
Procedure Reference: Change the Font Properties Using the Format Cells Dialog
Box
To change the font properties using the Format Cells dialog box:
1. Display the Format Cells dialog box.
On the Home tab, in the Font group, click the Font dialog box launcher or;
On the Home tab, in the Cells group, click the Format drop-down arrow
and select Format Cells.
2. In the Format Cells dialog box, on the Font tab, change the font properties.
In the Font list box, select the desired font.
In the Font Style list box, select the desired font style.
In the Size list box, select the desired font size, or enter a value in the Size
text box.
Click the Underline drop-down arrow, and from the displayed list, select an
underline style.
From the Font Color drop-down list, select the desired color.
Change the font effects.
Check the Strikethrough check box to strike through the text.
Check the Superscript check box to make the text superscript.
Check the Subscript check box to make the text subscript.
3. In the Preview section, preview the font properties and click OK to apply the
changes.
Procedure Reference: Format Cells Using the Format Painter
To format cells using the format painter:
1. Click a cell with the formatting that you want to copy.
2. Apply the formatting to a different cell.
a. On the Home tab, in the Clipboard group, click Format Painter.
Observe that the mouse pointer includes a paint brush graphic.
b. Move to the cell where you want to apply the formatting and click on it.
3. Apply the formatting to multiple cells.
Double-click Format Painter and then click multiple individual cells to
apply the format or;
Click and drag across a group of cells.
4. Click Format Painter again, or press Esc to turn off the format painter.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 93
Copyright Element K Corporation
ACTIVITY 4-1
Modifying Fonts
Data Files:
C:\084676Data\Modifying the Appearance of a Worksheet\Sales Ledger.xlsx
Scenario:
You are preparing a sales report for the period Q1 to Q3. You feel that you need to make the
worksheet visually more appealing. Therefore, you decide to modify the font properties of the
data in the worksheet.
What You Do How You Do It
1. Change the font for the data in the
entire worksheet.
a. From the File tab, choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Modifying the Appearance
of a Worksheet folder, and open the Sales
Ledger.xlsx file.
c. Click the Select All button, to select
the entire worksheet.
d. On the Home tab, in the Font group, click
the Font drop-down arrow, and in the dis-
played list, scroll down and select Times
New Roman.
e. Observe that the font for the entire
worksheet has changed to Times New
Roman.
2. Format the worksheet title. a. Select cell F1.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 94

Copyright Element K Corporation
b. On the Home tab, in the Font group, click
the Font Size drop-down arrow, and from
the displayed list, select 18.
c. In the Font group, click the Bold button.
d. Observe that the worksheet title is for-
matted.
3. Format the other headings and names
in the worksheet.
a. Select the cell range A2:I2.
b. From the Font Size drop-down list, select
12 to increase the size of the selected
headings.
c. In the Font group, click the Bold button.
d. Select the cell range A3:A25.
e. On the Home tab, in the Font group, click
the Increase Font Size button which is
the first button to the right of the Font
Size drop-down list.
f. In the column header, double-click the
border between columns I and J to adjust
the width of column I and display the con-
tents in the column entirely.
g. Save the worksheet as My Sales Ledger
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 95
Copyright Element K Corporation
TOPIC B
Add Borders and Colors to Cells
You modied the fonts of data in a worksheet to emphasize specic data. After emphasizing
specic data, you may want to highlight key information in the worksheet by using various
borders and colors. In this topic, you will add borders and colors to cells.
Billboard advertisements are designed to catch a persons attention. Successful advertisements
highlight the key information to attract the attention of the passerby. Similarly, adding borders
and colors to specic cells in a worksheet helps you highlight key information. It also allows
the grouping of similar data and makes worksheets that have loads of data look uncluttered.
Excel provides you with various options for adding borders and colors to cells.
Borders
Borders enable you to emphasize and dene sections in a worksheet. In Excel, you can apply
borders to the sides of a cell or a range of cells in a worksheet. You can specify a border style
or color that you want to apply to the selected cells in a worksheet. You can also remove any
borders that are applied to cells if you do not require them.
Figure 4-5: Various borders applied to define sections in a worksheet and options
available in the Border drop-down list.
Fills
Fills are worksheet enhancements that allow you to add background colors and highlight spe-
cic cells to draw attention to important data. You can choose from standard colors, dene a
custom color, or set a desired background effect. You can also select from a list of patterns and
pattern colors to apply to the selected cells in a worksheet.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 96

Copyright Element K Corporation
How to Add Borders and Colors to Cells
Procedure Reference: Add Borders to Cells
To add borders to cells:
1. Select the cells to which you need to add borders.
2. Apply a border to the selected cells.
On the Home tab, in the Font group, click the Border drop-down arrow, and
from the displayed list, select the desired border or;
Right-click the selected cells, and on the displayed Mini toolbar, click the
Border drop-down arrow, and from the displayed list, select the desired bor-
der or;
Select the desired option from the Format Cells dialog box.
a. Display the Format Cells dialog box.
From the Border drop-down list, select More Borders or;
On the Home tab, in the Cells group, click the Format button, and
from the displayed list, select Format Cells.
b. On the Border tab, set the desired border.
In the Line section, select the desired border style.
In the Color section, click the Color drop-down arrow, and from
the displayed gallery, select the desired border color.
In the Presets and Border sections, set the borders for the selected
cells.
c. Preview the border and click OK.
Procedure Reference: Modify or Remove Cell Borders
To modify or remove cell borders:
1. Modify the cell borders.
From the Border drop-down list, select a border.
If necessary, change the line color of a border.
a. From the Border drop-down list, select Line Color, and from the dis-
played gallery, select a desired color.
The mouse pointer will be displayed as a pencil graphic.
b. Select the lines to which you want to apply the color.
c. Click the Border button, or press Esc to change to the normal mouse
pointer.
If necessary, change the line style of a border.
a. From the Border drop-down list, select Line Style, and from the dis-
played gallery, select a desired style.
Observe that the mouse pointer has changed to a pencil graphic.
b. Select the lines to which you want to apply the style.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 97
Copyright Element K Corporation
c. Click the Border button, or press Esc to change to the normal mouse
pointer.
2. In the worksheet, select the cells from which you need to remove the borders.
3. Remove the borders.
On the Home tab, in the Font group, click the Border drop-down arrow, and
from the displayed list, select No Border or;
Display the Format Cells dialog box, select the Border tab, and in the Pre-
sets section, select None and click OK or;
Right-click the selected cells, and on the displayed Mini toolbar, click the
Border drop-down arrow, and from the displayed list, select No Border.
Procedure Reference: Add a Color to Cells
To add a color to cells:
1. Select the desired cells to which you want to add a color.
2. Apply the desired color.
On the Home tab, in the Font group, click the Fill Color drop-down arrow,
and from the displayed gallery, select the desired color or;
Right-click the selected cells, and on the displayed Mini toolbar, click the
Fill Color drop-down arrow, and from the displayed gallery, select the
desired color or;
In the Format Cells dialog box, select the Fill tab, select a color, and click
OK.
In the Background Color section, select the desired color.
Click the Pattern Color drop-down arrow, and from the displayed gal-
lery, select the desired color.
Click the Pattern Style drop-down arrow, and from the displayed gal-
lery, select the desired pattern.
Selecting the No Color option will remove the color applied to the selected cell or cell
range.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 98

Copyright Element K Corporation
ACTIVITY 4-2
Adding Borders and Colors to Cells
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
You are still in the process of formatting the sales analysis report that you need to present to
the management. You want to highlight the title and heading of the data in the worksheet. You
also want to add an outline to the data cells in the worksheet.
What You Do How You Do It
1. Add a background color to the
worksheet title and headings.
a. Select the cell range A1:I1.
b. On the Home tab, in the Font group, click
the Fill Color drop-down arrow, and from
the displayed gallery, select the Blue
color.
c. Select the cell range A2:I2.
d. On the Home tab, in the Font group, click
the Fill Color drop-down arrow, and from
the displayed gallery, select the Light
Blue color.
2. Add formatting to a cell range. a. Verify that the cell range A2:I2 is
selected.
b. On the Home tab, in the Clipboard group,
click the Format Painter button, and
select K11:L15.
c. Select the cell range A2:I25.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 99
Copyright Element K Corporation
d. On the Home tab, in the Font group, click
the Border drop-down arrow, and from
the displayed list, select All Borders.
e. Save the worksheet.
TOPIC C
Align Content in a Cell
You added borders and colors to the cells in a worksheet to enhance the look of the worksheet.
By positioning the data in the cells, you can enhance the readability and clarity of the content
presented in a worksheet. In this topic, you will align the content within a cell.
When you enter data in a cell, Excel may not align the content within the cell as you wanted.
Sometimes, when the data exceeds the column width, some part of the data will not be visible
within the cells. Aligning the content within a cell improves the appearance of the worksheet
content. Excel provides you with various options that enable you to align content within a cell.
Alignment Options
The Alignment options in Excel enable you to align content within a cell either horizontally or
vertically. These options can be accessed from the Alignment group on the Home tab.
Alignment Option Used To
Top Align Align text to the top of a cell.
Middle Align Align text to the vertical center of a cell.
Bottom Align Align text to the bottom of a cell.
Align Text Left Align text to the left of a cell.
Center Align text to the horizontal center of a cell.
Align Text Right Align text to the right of a cell.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 100

Copyright Element K Corporation
The Indent Command
The Indent command is used to reposition data in cells for better spacing and readability. The
Decrease Indent command decreases the space between the left border and the text within a
cell. The Increase Indent command increases the space between the left border and the text
within a cell.
Figure 4-6: The two Indent commands used to reposition the data in a cell.
The Wrap Text Option
Excel enables you to automatically wrap the text within a cell by using the Wrap Text option.
If the text within a cell extends beyond the space available, this option moves the content to
the next line within the cell and automatically modies the row height to ensure that all of the
text is visible within the cell.
Figure 4-7: Text exceeding the space within a cell.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 101
Copyright Element K Corporation
Figure 4-8: The Wrap Text option used to fit the content in a cell.
Manual Line Breaks
The content in a cell can be wrapped within a cell by entering a manual line break.
After locating the point at which the line needs to be broken, a manual line break can
be inserted by holding down Alt and pressing Enter.
Orientation Options
To display data in a worksheet legibly and with some symmetry, you may have to change the
orientation of data in cells. Often, when the column header text is longer than the data con-
tained below it, you may want to change the angle of the header text diagonally or vertically
to minimize the column width. This allows you to t more columns on a page. The orientation
options are usually used for labeling narrow columns. Excel allows you to orient data clock-
wise, counterclockwise, vertically, or by a specic degree.
Figure 4-9: The Orientation option used to change the angle of the text.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 102

Copyright Element K Corporation
The Merge Feature
Excel provides you with options to not only merge contiguous cells across columns and rows,
but also split cells that are merged.
Merge Option Description
Merge & Center Combines the selected cells across columns and rows to a larger, single cell
and centers the data. Only the data in the top-left cell that is merged will be
retained.
Merge Across Combines the selected cells across columns to a larger, single cell. When
multiple rows of data are selected, the cells are merged across columns for
each row separately. Only the data in the rst cell from the left of each row
that is merged will be retained.
Merge Cells Combines the selected cells across columns and rows to a larger, single
cell. Only the data in the top-left cell will be retained.
Unmerge Cells Splits the merged cell into separate cells. The data of the merged cell is
copied only to the top-left cell that was separated.
How to Align Content in a Cell
Procedure Reference: Align Content in a Cell
To align content in a cell:
1. Select the cell in which you want to align the content.
2. Align the content of the selected cell.
On the Home tab, in the Alignment group, select the desired option to align
the text within the cell or;
Display the Format Cells dialog box, and on the Alignment tab, in the Text
Alignment section, click either the Horizontal or the Vertical drop-down
arrow, and from the displayed list, select an option or;
Right-click the selected cell, and on the displayed Mini toolbar, click the
Center button to align the content to the center.
3. Indent the content in a cell.
a. Select the cell in which you want to indent the content.
b. Indent the content of the selected cell.
On the Home tab, in the Alignment group, click the Increase Indent or
Decrease Indent button to increase or decrease the indent between the
border and text within a cell or;
In the Format Cells dialog box, on the Alignment tab, in the Indent
section, enter a value, or click the up or down arrow to specify an
indent value in the spin box.
4. If necessary, orient the content in a cell.
a. Select the cell in which you want to orient the content.
b. Orient the content in the selected cell.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 103
Copyright Element K Corporation
Click the Orientation button, and from the displayed list, select the
desired option.
Select Angle Counterclockwise to rotate the text in a cell selection
up 45 degrees from the baseline.
Select Angle Clockwise to rotate the text in a cell selection down
45 degrees from the baseline.
Select Vertical Text to align the text in a column where one letter
appears over the other.
Select Rotate Text Up to rotate the text in a cell selection up 90
degrees from the baseline.
Select Rotate Text Down to rotate the text in a cell selection down
90 degrees from the baseline.
Orient the content in a cell using the Format Cells dialog box.
A. In the Format Cells dialog box, on the Alignment tab, in the Ori-
entation section, enter a value or click the up or down arrow to
specify a degree in the spin box.
B. Preview the text in the place provided above the spin box.
Procedure Reference: Wrap Text in a Cell
To wrap text in a cell:
1. Select the cells in which the text needs to be wrapped.
2. Wrap the text in the selected cells.
On the Home tab, in the Alignment group, click Wrap Text or;
Display the Format Cells dialog box, select the Alignment tab, check the
Wrap text check box, and click OK or;
If necessary, in the Format Cells dialog box, on the Alignment tab, in the
Text Control section, check the Shrink to t check box to shrink the text to
be visible within the cell.
Procedure Reference: Merge or Split Cells
To merge or split cells:
1. Select the range of contiguous cells that you want to merge or a merged cell that
you want to split.
2. Merge or split the selected cells.
Display the Format Cells dialog box, select the Alignment tab, and in the
Text control section, check or uncheck the Merge cells check box and click
OK or;
Right-click the selected range of cells, and on the displayed Mini toolbar,
click the Merge & Center button or;
On the Home tab, in the Alignment group, click the Merge & Center drop-
down arrow, and from the displayed list, select the desired option.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 104

Copyright Element K Corporation
ACTIVITY 4-3
Positioning the Content in a Cell
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
You reviewed the sales report and noticed that the headings in the worksheet are not aligned
with the data. You also observed that the text in some of the cells exceeds the available space
and is not displayed completely. You want to align the cell content to enhance the readability
of the worksheets contents.
What You Do How You Do It
1. Align the text in the cell range B2:I2
to the right.
a. Select the cell range B2:I2.
b. On the Home tab, in the Alignment
group, click the Align Text Right button,
to right align the content in the cells.
2. Merge and center the title SALES
LEDGER.
a. Select the cell range A1:I1.
b. On the Home tab, in the Alignment
group, click the Merge & Center button.
c. Double-click the border between the rows
1 and 2 to automatically adjust the height
of row 1.
d. Observe that the title SALES LEDGER is
displayed at the center of a single large
cell.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 105
Copyright Element K Corporation
3. Wrap the text Date last modified
and Commission rate to fit in their
respective cells.
a. Select cell K1.
b. Observe that the content displayed does
not fit in a cell because the space avail-
able in the cell is not sufficient to display
the full content.
c. On the Home tab, in the Alignment
group, click the Wrap Text button.
d. Observe that the text Date last modi-
fied now fits in a single cell.
e. Select cell K2.
f. Observe that some of the content dis-
played on the Formula Bar does not
appear in the cell because the space
available in the cell is not sufficient to
display the full content.
g. On the Home tab, in the Alignment
group, click the Wrap Text button.
h. Observe that the text Commission rate
now fits in a single cell.
i. Save the worksheet.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 106

Copyright Element K Corporation
TOPIC D
Apply Number Formatting
You aligned content in cells to improve the presentation of data in a worksheet. When you
have numerical data in a worksheet, you may want this data to appear in specic formats so
that you can interpret it easily. In this topic, you will apply number formats.
When creating spreadsheets, you may have specic requirements to present data in a particular
cell, row, or column. If you are working on an inventory worksheet that contains a column for
the stocks value, you may need to manually enter the currency symbol for each cell. Excel
provides you with various number formats that can be applied to such cells so that the value
displayed in the cells are formatted automatically. Applying number formats changes the
appearance of the numerical data and makes it easier to understand the data present in the
cells.
Data Formats in Excel
A data format changes the appearance of the data in a cell by applying specialized formatting
to the data. Applying data format changes only the appearance of the data, but not the actual
data. You can apply the desired format to a cell or a range of cells before or after you enter
data. Excel provides you with a variety of data format categories.
Category Used To
General Display data using the default formatting that Excel applies when you enter data in
a cell.
Number Display numbers. You can specify the number of decimal places and the format in
which the negative numbers will be displayed.
Currency Display monetary values. The currency symbol is displayed in a cell along with
the value. By default, the dollar ( $ ) sign is displayed. You can also change the
sign to the currency sign that you need.
Accounting Display monetary values with decimals. This will align the currency symbols and
decimal points of the numbers in a column.
Date Display date or a combination of date and time values according to the type and
location that you specify.
Time Display time or a combination of time and date values according to the type and
location that you specify.
Percentage Display percentages by multiplying the cell value by 100 and displaying the result
with a percent symbol.
Fraction Display a fraction according to the type of fraction that you specify.
Scientic Display an exponential notation.
Text Display text. Even when a cell contains numbers, you can use this option to treat
the content as text so that the numbers appear exactly the same as you type them.
You can use this option when leading zeros are necessary, as in an employee num-
ber.
Special Display data with special formatting such as ZIP code, phone number, or Social
Security Number.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 107
Copyright Element K Corporation
Category Used To
Custom Create a custom format. You can also customize an existing format.
Custom Data Formats
Excel allows you to create custom data formats to suit your needs. Custom data for-
mats can be created when Excels predened format categories do not provide the
format that is required for a particular type of data. When creating a custom data for-
mat, the # symbol is used to indicate that only signicant digits will be displayed,
while the insignicant zeros will not be displayed. Quotation marks at the beginning
and end of the text are used to indicate how the text should be displayed with the cus-
tom data format. You can add up to 250 custom data formats to a list.
How to Apply Number Formatting
Procedure Reference: Apply Number Formats
To apply number formats:
1. Select the cell to which you want to apply a number format.
2. Select the number format.
On the Home tab, in the Number group, click the Number Format drop-
down arrow, and from the displayed list, select the desired number format or;
Display the Format Cells dialog box, select the Number tab, and in the
Category list box, select the desired number format.
Procedure Reference: Create a Custom Number Format
To create a custom number format:
1. Select the cell to which you want to apply a custom number format.
2. Display the Format Cells dialog box and select the Number tab.
3. In the Category list box, select Custom.
4. Dene the new custom format.
In the Type text box, type the desired format using one of the existing for-
mats as the starting point to dene a new custom format or;
In the Type list box, select one of the existing models and modify it in the
Type text box.
5. Click OK to apply the format.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 108

Copyright Element K Corporation
ACTIVITY 4-4
Applying a Number Format
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
When checking the sales report for accuracy, you notice that it contains a lot of numerical
information. You nd it difficult to interpret this data because it is not formatted properly and
decide to apply appropriate formatting.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 109
Copyright Element K Corporation
What You Do How You Do It
1. Apply a currency format to the sales
data and the top three employees
summary.
a. Select the cell range B3:I25.
b. On the Home tab, in the Number group,
from the Number Format drop-down list,
select Currency to apply the currency for-
mat to the selected range with the dollar
sign.
The ## sign will appear if the column is not wide
enough to display the numbers with the currency
format.
c. Click the Decrease Decimal button
twice to display the values without
decimal points.
d. Select the cell range L13:L15.
e. On the Home tab, in the Number group,
from the Number Format drop-down list,
select Currency to apply the currency for-
mat to the selected range with the dollar
sign.
f. In the Number group, click the Decrease
Decimal button twice to display the val-
ues without decimal points.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 110

Copyright Element K Corporation
2. Enter the date and change its for-
mat.
a. In the Name Box, click and type L1 and
press Enter to navigate to cell L1.
b. Type the current date and press Enter.
c. Select cell L1.
d. On the Home tab, in the Number group,
click the Format Cells: Number dialog
box launcher.
e. In the Format Cells dialog box, on the
Number tab, in the Category list box,
select Date.
f. In the Type list box, scroll down and
select 14-Mar-2001 and click OK to apply
this date format to the selected cell.
g. Observe that the date is displayed in the
dd-mmm-yyyy format.
h. Save the worksheet.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 111
Copyright Element K Corporation
TOPIC E
Apply Cell Styles
You applied number formats to display data in a worksheet in the preferred format. Applying a
cell style gives you a way to create consistent-looking documents and also gives the ability to
change the formatting of all the cells that use a particular style. In this topic, you will apply
cell styles in a worksheet.
While creating a worksheet, you notice that some of the similar cells are formatted differently.
This results in an inconsistent look of the spreadsheet. Ensuring that the different formatting
options such as the font style, font size, borders, and shading are consistently applied through-
out the workbook can be a time-consuming task. Excel provides you with cell styles to apply
several formatting options to cells at the same time for a neat and consistent look.
Cell Styles
Denition:
A cell style is a predened set of appearance options that can be applied to a cell in a
worksheet. Each style includes a unique combination of number formatting, alignment,
font, border style, pattern, and protection options. You can select either a predened or
a customized cell style to suit your requirements. Predened cell styles are sets of
styles that are based on the theme of the workbook template. You can lock a cell style
to prevent others from modifying it. You can also delete cell styles that you no longer
require.
Example:
Figure 4-10: The Total cell style used to differentiate the total values in a
worksheet.
The Style Dialog Box
The Style dialog box contains various options that allow you to modify an existing cell style
or create a new cell style. This dialog box can be accessed from the gallery that is displayed
on clicking the Cell Styles button in the Styles group on the Home tab. You can modify an
existing cell style by selecting the Modify option in the list that is displayed when you right-
click a cell style in the gallery. You can create a new cell style by selecting the New Cell
Style option from the gallery. The modied or newly created cell style will also be listed in
the gallery.
The Style dialog box contains various options to customize cell styles.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 112

Copyright Element K Corporation
Option Used To
The Style Name text
box
Specify the name of the style that you want to modify.
The Format button Display the Format Cells dialog box. You can use this dialog box to modify
the formatting used in the selected style.
The Style Includes sec-
tion
Specify the formatting options that need to be included in the style. You can
check the Number, Alignment, Font, Border Fill, and Protection check
boxes to specify whether these formatting options are to be included in the
style.
How to Apply Cell Styles
Procedure Reference: Apply a Cell Style
To apply a cell style:
1. Select the range of cells to which you want to apply a cell style.
2. On the Home tab, in the Styles group, click Cell Styles.
3. From the displayed gallery, select the desired cell style.
Procedure Reference: Modify a Cell Style
To modify a cell style:
1. On the Home tab, in the Styles group, click Cell Styles, and in the displayed gal-
lery, right-click the desired cell style and choose Modify.
2. In the Style dialog box, click Format.
3. In the Format Cells dialog box, specify the modications that need to be made to
the cell style and click OK.
4. In the Style dialog box, in the Style includes section, specify the changes that
need to be made to the cell style.
5. Click OK to apply the changes to the cell style.
When you apply a cell style to the cells in a worksheet and then modify that cell style, the
worksheet automatically reects the modied cell style without having to apply it again.
Procedure Reference: Construct a New Cell Style
To construct a new cell style:
1. On the Home tab, in the Styles group, click the Cell Styles drop-down arrow, and
from the displayed gallery, select New Cell Style.
2. In the Style dialog box, in the Style name text box, specify a name for the style.
3. Click Format.
4. In the Format Cells dialog box, select the desired tabs, and set the formatting
options and then click OK.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 113
Copyright Element K Corporation
5. In the Style dialog box, in the Style Includes (By Example) section, check or
uncheck the desired check boxes to include or exclude formatting settings from
the style.
Check or uncheck the Number check box to include or exclude the number
formatting settings.
Check or uncheck the Alignment check box to include or exclude the text
alignment settings.
Check or uncheck the Font check box to include or exclude the font proper-
ties.
Check or uncheck the Border check box to include or exclude the border
formatting settings.
Check or uncheck the Fill check box to include or exclude the ll color set-
tings.
Check or uncheck the Protection check box to include or exclude the protec-
tion settings.
6. Click OK to create the new custom cell style.
ACTIVITY 4-5
Applying Cell Styles
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
When viewing the worksheet, you see that some cells containing similar data are not formatted
uniformly. You decide to use the cell styles feature to apply the formatting that looks consis-
tent across the worksheet. You also want to create a custom style that can be used for
formatting titles on all official worksheets.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 114

Copyright Element K Corporation
What You Do How You Do It
1. Apply a cell style to the worksheet
title.
a. Select the cell containing the title SALES
LEDGER.
b. On the Home tab, in the Styles group,
click Cell Styles, and from the displayed
gallery, in the Titles and Headings sec-
tion, select Title.
c. Observe the cell style that is applied to
the title.
2. Modify the font color of the applied
cell style.
a. In the Styles group, click Cell Styles, and
in the displayed gallery, in the Titles and
Headings section, right-click Title and
choose Modify.
b. In the Style dialog box, click Format.
c. In the Format Cells dialog box, select the
Font tab.
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 115
Copyright Element K Corporation
d. On the Font tab, click the Color drop-
down arrow, and from the displayed
gallery, in the Standard Colors section,
select Yellow, which is the fourth color
from left.
e. Click OK to close the Format Cells dialog
box.
f. In the Style dialog box, observe that the
Font check box is checked with the font
properties displayed.
LESSON 4
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 116

Copyright Element K Corporation
g. Click OK to apply the Title cell style.
3. Apply the Accent5 theme style to the
Name column.
a. Select the cell range A3:A25.
b. In the Styles group, click Cell Styles, and
in the Themed Cell Styles section, select
Accent5.
4. Apply the Input style to the cells that
contain the data for the sales ledger.
a. Select the cell range B3:D25.
b. In the Styles group, click Cell Styles, and
in the Data and Model section, select
Input.
5. Apply the Calculation style to the
cells that contain calculations.
a. Select the cell range E3:I25.
b. In the Styles group, click Cell Styles, and
in the Data and Model section, select Cal-
culation.
c. Click any cell to deselect the selected
range.
d. Save and close the workbook.
Lesson4Follow-up
In this lesson, you modied the appearance of data in a worksheet. By applying the formatting
options available in Excel, you can ensure that the worksheet looks professional and the data is
easy to interpret.
1. Why do you want to format a worksheet?
2. Which formatting options do you think you will use the most often?
LESSON 4
Lesson 4: Modifying the Appearance of a Worksheet 117
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 118

NOTES
Copyright Element K Corporation
Managing an Excel
Workbook
In this lesson, you will manage Excel workbooks.
You will:
Manage worksheets.
View worksheets and workbooks.
Lesson Time
1 hour(s) LESSON 5
LESSON 5
Lesson 5: Managing an Excel Workbook 119
Copyright Element K Corporation
Introduction
You modied the appearance of a worksheet. When working with multiple worksheets, you
may need to organize them in an easily accessible way. In this lesson, you will manage work-
books.
Large workbooks have many unique issues that not only come into play, but also need to be
managed. These workbooks may contain many worksheets with different sets of data. Also,
individual worksheets may need to be rearranged, or you may need to compare and work with
data located in distant sections of a worksheet. With relevant knowledge of Excels tools, you
can efficiently manage and work with large workbooks.
TOPIC A
Manage Worksheets
You modied the appearance of a worksheet in a workbook. When a workbook contains mul-
tiple worksheets, you may spend a considerable amount of time in identifying the required
worksheet. In this topic, you will manage the worksheets within a workbook.
Assume that you have entered data in different worksheets of a workbook based on regional
sales information. Later, you realize that these worksheets are not sequenced properly, and you
do not require some of the worksheets. Excel provides you with options to reposition, hide, or
delete worksheets easily in a workbook.
Insertion and Deletion Options
When you open a new Excel workbook, three worksheets will be displayed on the sheet tab
bar by default. Excel provides you with various options to modify the number of worksheets in
a workbook based on your requirements. You can insert new worksheets by clicking the Insert
Worksheet button, or by selecting the Insert Sheet option from the Insert drop-down list in
the Cells group. You can also use the Insert dialog box to insert a blank worksheet, a
worksheet based on a local template, or a worksheet based on a template from Office Online.
To delete worksheets with unwanted or obsolete data, you can select the Delete Sheet option
from the Delete drop-down list in the Cells group, or choose Delete from the worksheet tabs
shortcut menu.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 120

Copyright Element K Corporation
Figure 5-1: The Insert dialog box and a newly inserted worksheet.
Worksheet Repositioning Methods
Worksheet repositioning methods in Excel allow you to move or copy worksheets within a
workbook or between workbooks. You can reposition worksheets by using the Move or Copy
dialog box, or by dragging a worksheet tab to its new location.
Tab Formatting Options
Excel allows you to rename and change the color of worksheet tabs. You can change the
default sheet names to something more meaningful so that you can locate appropriate data
quickly. You can also change the color of a worksheet tab to enable easy identication of the
worksheet.
Figure 5-2: Formatting options available in the Format drop-down list.
Hide and Unhide Options
A workbook window may look cluttered when it contains multiple worksheets. In Excel, you
can hide worksheets that are not required for a task in progress. The hidden worksheets are not
deleted from the workbook and the references used in formulas to cells in a hidden worksheet
are valid. The Hide & Unhide submenu of the Format menu in the Cells group provides you
with the option to hide or display a worksheet. You can also hide or unhide a sheet by choos-
ing an option from the worksheet tabs shortcut menu.
LESSON 5
Lesson 5: Managing an Excel Workbook 121
Copyright Element K Corporation
Figure 5-3: Hidden worksheets.
How to Manage Worksheets
Procedure Reference: Insert or Delete Worksheets
To insert or delete worksheets:
1. Insert new worksheets.
Insert a single worksheet.
Right-click the worksheet tab to the left of which the new worksheet
needs to be added, choose Insert, and in the Insert dialog box, select
the Worksheet option, and click OK or;
On the sheet tab bar, to the right of the worksheet tabs, click the Insert
Worksheet button to insert a new worksheet or;
On the Home tab, in the Cells group, click the Insert drop-down arrow
and select Insert Sheet.
Insert multiple worksheets.
On the sheet tab bar, select multiple worksheet tabs, and right-click the
selection to the left of which the new worksheet needs to be added,
choose Insert, and in the Insert dialog box, click OK or;
On the sheet tab bar, select multiple worksheet tabs, and on the Home
tab, in the Cells group, click the Insert drop-down arrow and select
Insert Sheet to insert multiple worksheets.
2. If necessary, delete a worksheet.
Right-click a worksheet or a selection of multiple worksheets and choose
Delete to delete worksheets.
Select a sheet or multiple sheets, and on the Home tab in the Cells group,
from the Delete drop-down list, select Delete Sheet.
Procedure Reference: Move or Copy Worksheets
To move or copy worksheets within a workbook or between workbooks:
1. Right-click a worksheet tab and choose Move or Copy to display the Move or
Copy dialog box.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 122

Copyright Element K Corporation
2. From the Move selected sheets to book drop-down list, select the destination
workbook.
3. In the Before sheet list box, select the worksheet before which the selected
worksheet needs to be inserted.
4. Check the Create a copy check box if you want to copy the sheet, rather than
move it, and click OK.
You can also reposition a worksheet by clicking and dragging the worksheet tab to before or after
another worksheet tab.
Procedure Reference: Group Worksheets
To group worksheet.
1. Open the desired workbook.
2. Select the worksheets to be grouped.
Hold down Ctrl or Shift, and on the sheet tab bar, select the desired
worksheet tabs that you want to group.
Right-click a worksheet tab, and choose Select All Sheets.
3. If necessary, make changes in a worksheet so that the change is applied to all the
grouped sheets.
4. If necessary, ungroup the worksheets.
Right-click a grouped worksheet tab, and choose Ungroup Sheets.
Click a worksheet tab that is not in the group.
Modifying Grouped Worksheets
Excel allows you to group worksheets to make changes in all the grouped sheets
simultaneously. Any formatting applied to a worksheet in the group is applied to the
same cell range in all the grouped worksheets. When worksheets are grouped, their tab
colors will change from gray to white and the title bar includes the word [Group] at
the end of the le name.
Procedure Reference: Rename a Worksheet Tab.
To rename a worksheet tab.
1. On the sheet tab bar, select the desired worksheet tab.
Right-click the worksheet tab and choose Rename or;
Double-click the worksheet tab.
2. Type the new name for the worksheet and press Enter.
Procedure Reference: Color Worksheet Tabs.
To color a worksheet tabs.
1. Select worksheet tab.
Right-click the worksheet tab that you want to color, choose Tab Color, and
select a color or;
Select a worksheet tab, hold Shift and click another worksheet tab to select
multiple worksheet tabs.
LESSON 5
Lesson 5: Managing an Excel Workbook 123
Copyright Element K Corporation
Press Ctrl and click on the required worksheet tabs to select a non continu-
ous range of worksheet tabs.
2. Color the worksheet tabs.
Right-click the selected worksheet tabs that you want to color, choose Tab
Color, and select a color or;
On the Home tab, in the Cells group, from the Format drop-down list,
select Tab Color and then select a color.
Procedure Reference: Hide or Unhide Worksheets
To hide or unhide worksheets:
1. Select the worksheets that you want to hide.
2. Right-click the selected tabs and choose Hide to hide the worksheets or in Home
tab in the Cells group, from the Format drop-down list, from the Hide & Unhide
submenu select Hide Sheet.
3. If necessary, unhide the sheets.
a. On the sheet tab bar, right-click any worksheet tab and choose Unhide, or in
Home tab, in the Cells group, from the Format drop-down list, from the
Hide & Unhide submenu, select Unhide Sheet.
b. In the Unhide dialog box, in the Unhide sheet list box, select the hidden
worksheet that you want to display and click OK.
ACTIVITY 5-1
Formatting Worksheet Tabs
Data Files:
C:\084676Data\Managing an Excel Workbook\Sales Summary.xlsx
Scenario:
The sales summary workbook is comprised of three worksheets, one for each region. Identify-
ing the worksheet for the required region is difficult because the sheets have default names,
instead of a descriptive label. You want to be able to readily recognize each regions
worksheet.
What You Do How You Do It
1. Examine the three worksheets in the
workbook.
a. Select the File tab, choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Managing an Excel
Workbook folder, and open the Sales
Summary.xlsx file.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 124

Copyright Element K Corporation
c. Verify that the Sheet1 tab contains data
that is specific to the US division and
select Sheet2.
d. View the data that is specific to the Cana-
dian division and select Sheet3.
e. View the data that is specific to the Mexi-
can division.
2. Rename the first worksheet. a. Select the Sheet1 tab to display the US
Sales Summary worksheet.
b. On the sheet tab bar, right-click the
Sheet1 tab and choose Rename.
c. Type US and press Enter to rename the
tab.
3. Rename the second and third
worksheets.
a. Double-click the Sheet2 tab to rename
Sheet2.
b. Type Canada and press Enter.
LESSON 5
Lesson 5: Managing an Excel Workbook 125
Copyright Element K Corporation
c. Similarly, rename the third worksheet as
Mexico
4. Change the color of the worksheet
tabs.
a. Select the US tab.
b. On the Home tab, in the Cells group, from
the Format drop-down list, select Tab
Color, and from the displayed gallery, in
the Standard Colors section, select Blue.
c. Right click the Canada tab, choose Tab
Color, and from the displayed gallery, in
the Standard Colors section, select Yel-
low.
d. Similarly, change the color of the Mexico
tab to green.
e. Save the workbook as My Sales Summary
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 126

Copyright Element K Corporation
ACTIVITY 5-2
Organizing a Workbook
Before You Begin:
The My Sales Summary.xlsx le is open.
Scenario:
You have tracked the sales summary for each division in individual worksheets of a workbook.
You now need to add new worksheets to accommodate more data. Also, you want to organize
and display these worksheets based on the sequence in which you want to access information.
Moreover, you need to ensure that the worksheets meet the conditions mentioned below.
The Mexico worksheet should appear before the Canada worksheet.
A new sheet named Europe is needed for the new European division, which will
have content similar to the Canada worksheet.
Another new sheet is needed, which will contain the employee listing for all divi-
sions.
Finally, the workbook is to be used at a couple of presentations, and at each pre-
sentation, you want only specic sheets to be displayed.
What You Do How You Do It
1. Move the Mexico worksheet to place
it before the Canada worksheet.
a. On the sheet tab bar, drag the Mexico tab
to the left of the Canada tab.
b. Observe that the Mexico tab appears
before the Canada tab.
LESSON 5
Lesson 5: Managing an Excel Workbook 127
Copyright Element K Corporation
2. Copy the Canada worksheet. a. Right-click the Canada tab and choose
Move or Copy.
b. In the Move or Copy dialog box, in the To
book drop-down list, verify that the desti-
nation displayed is My Sales
Summary.xlsx, and in the Before sheet
list box, select Canada.
c. Check the Create a copy check box and
click OK.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 128

Copyright Element K Corporation
d. Observe that a copy of the worksheet,
named Canada (2), is created.
3. Modify the duplicated worksheet. a. Double-click the Canada (2) tab, type
Europe and press Enter to rename the
tab.
b. Right-click the Europe tab, choose Tab
Color, and in the Standard Colors section,
select Red.
c. Select cell D2, press Delete, type Euro-
pean Sales Summary and press Enter.
d. Select the cell range A5:H11.
e. On the Home tab, in the Editing group,
from the Clear drop-down list, select
Clear Contents.
4. Add a new worksheet to enter the
employee listing for all divisions.
a. On the sheet tab bar, click the Insert
Worksheet button to insert a new
worksheet.
b. Double-click the Sheet2 tab, type
Employee Summary and press Enter.
c. Apply orange color to the Employee Sum-
mary tab.
LESSON 5
Lesson 5: Managing an Excel Workbook 129
Copyright Element K Corporation
5. Hide all worksheets, except the US
worksheet.
a. Right-click the Employee Summary tab
and choose Hide.
b. Select the Mexico tab, hold down Shift,
and select the Canada tab.
c. Right-click the selected tabs and choose
Hide.
d. Observe that US is the only worksheet
that is displayed and that the other
worksheets are hidden.
Data in the hidden worksheets is not deleted,
and the references to cells in the hidden
worksheets are valid.
6. Unhide the Canada and Mexico
worksheets for use in another presen-
tation.
a. Right-click the US tab and choose Unhide.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 130

Copyright Element K Corporation
b. In the Unhide dialog box, in the Unhide
sheet list box, verify that Mexico is
selected.
c. Click OK to display the worksheet.
d. Similarly, unhide the Canada worksheet.
e. Save the workbook.
TOPIC B
View Worksheets and Workbooks
You managed the worksheets in a workbook. You may want to view multiple worksheets of a
workbook simultaneously or view different sections of a worksheet together. In this topic, you
will view worksheets and workbooks.
When you are working on data that is spread over numerous pages, it is difficult to keep track
of what information is on a particular page, or where new information is added. You may also
need to check the column or row headings multiple times to know whether you are looking at
the correct cell. Excel provides you with various views to work with worksheets and to ensure
that the column and row headings are always visible.
Freeze Panes Options
By freezing panes, you can keep a particular portion of a worksheet static, while you scroll
through the other areas. You can access the Freeze Panes options from the Freeze Panes
drop-down list in the Window group on the View tab. When you select the Freeze Panes
option, the option toggles to display the Unfreeze Panes option that allows you to unfreeze
panes. Excel allows you to freeze the top row, rst column, or panes in a worksheet.
LESSON 5
Lesson 5: Managing an Excel Workbook 131
Copyright Element K Corporation
Figure 5-4: A frozen header row in a worksheet.
Option Allows You To
Freeze Panes Keep the portion of the worksheet above the selected row and to the left of the
selected column static, while you scroll through the other rows and columns.
Freeze Top Row Keep the top row static, while you scroll through the other rows of the
worksheet.
Freeze First Col-
umn
Keep the rst column static, while you scroll through the other columns of the
worksheet.
Unfreeze Panes Unfreeze panes and enable you to scroll through the entire worksheet.
The Split Command
When you work with large worksheets, you may need to view different portions of a
worksheet simultaneously. Using the Split command, you can split the worksheet into multiple
resizable panes to view its various parts by scrolling through different panes. You can resize
these panes by dragging the split bars that separate them.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 132

Copyright Element K Corporation
Figure 5-5: A window displaying split panes of a worksheet.
The Arrange All Command
The Arrange All command enables you to arrange all open windows side by side on the
screen. The Arrange Windows dialog box contains options to specify how the workbook win-
dows are arranged within the Excel window. You can check the Windows of active workbook
check box to display only the windows of the current workbook. Excel provides you with
options to arrange windows in a tiled, horizontal, vertical, or cascade arrangement.
Figure 5-6: The Arrange Windows dialog box and cascaded worksheets.
LESSON 5
Lesson 5: Managing an Excel Workbook 133
Copyright Element K Corporation
Option Used To
Tiled View all the open windows as rectangles, covering the entire Excel window.
Horizontal View all the open windows, one below the other.
Vertical View all the open windows, one next to the other.
Cascade View all the open windows, displayed one behind the other.
The View Side by Side Command
You can view and compare two different Excel workbooks or different parts of the same
worksheet simultaneously by using the View Side by Side command. When you tile two
workbook windows using this command, both windows scroll simultaneously because the Syn-
chronous Scrolling command is enabled by default. You can disable synchronous scrolling by
clicking the Synchronous Scrolling button. You can use the Reset Window Position com-
mand to reset a workbook window to its original position.
Figure 5-7: Two views of a workbooks arranged side by side.
The Switch Windows Command
The Switch Windows command allows you to switch views between the workbook windows
that are open. You can switch to and view different workbooks that are open without maximiz-
ing or minimizing each workbook window. When you click the Switch Windows drop-down
arrow, a list of open workbooks is displayed. You can select the desired workbook from this
list to display it.
The New Window Command
The New Window command allows you to view the same worksheet in another workbook
window. This will enable you to use the View Side by Side command to compare and edit
different sections of the same worksheet.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 134

Copyright Element K Corporation
How to View Worksheets and Workbooks
Procedure Reference: Freeze or Unfreeze Panes
To freeze or unfreeze panes:
1. Select the worksheet in which you want to freeze or unfreeze panes.
2. Freeze the panes.
On the View tab, in the Window group, click the Freeze Panes drop-down
arrow and select Freeze Top Row or Freeze First Column to freeze only
the rst row or column.
Select the cell that is below the row and to the right of the column that you
want to freeze, and on the View tab, in the Window group, click the Freeze
Panes drop-down arrow and select Freeze Panes to freeze both rows and
columns.
3. If necessary, on the View tab, in the Window group, click the Freeze Panes
drop-down arrow and select Unfreeze Panes to unfreeze the panes.
Procedure Reference: Split a Worksheet Window
To split a worksheet window:
1. Select the cell in a worksheet where you want to split the worksheet window.
2. On the Ribbon, on the View tab, in the Window group, click Split.
3. If necessary, click the Split button again to undo the split action.
Procedure Reference: Arrange the Worksheets in Separate Windows Within an
Excel Window
To arrange the worksheets in separate windows within a workbook:
1. With a workbook open, on the View tab, in the Window group, click New Win-
dow to open the entire workbook in a new window.
2. Repeat step 1 until you have a new window for every worksheet that you want to
view in a new window.
3. In the Window group, click Arrange All to display the Arrange Windows dialog
box and select the desired display option.
4. If you have multiple workbooks open, check the Windows of active workbook
check box to avoid arranging the windows of other open workbooks and click
OK.
Procedure Reference: Open a New Window with Contents from the Current
Worksheet
To open a new window with contents from the current worksheet:
1. Open the desired workbook.
2. On the View tab, in the Window group, click New Window to open another win-
dow with the same workbook.
LESSON 5
Lesson 5: Managing an Excel Workbook 135
Copyright Element K Corporation
Procedure Reference: View Different Sections of a Worksheet Side by Side
To view different sections of a worksheet side by side:
1. With a workbook open, on the View tab, in the Window group, click New Win-
dow to open the entire workbook in another window .
2. In the Window group, click the View Side by Side button.
3. If necessary, click the View Side by Side button again to undo the action.
ACTIVITY 5-3
Viewing a Large Worksheet
Data Files:
C:\084676Data\Managing an Excel Workbook\Sales Ledger.xlsx
Before You Begin:
The My Sales Summary.xlsx le is open.
Scenario:
You are reviewing the sales report. Due to the large amount of data in the sheet, you nd it
difficult to identify and focus on specic data. You also want to compare the sales values in
the US division, Canadian division, and Mexican division worksheets. You need to be able to
view the content simultaneously in all the three worksheets.
What You Do How You Do It
1. Split the worksheet window. a. Select the File tab, and choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Managing an Excel
Workbook folder, and open the Sales
Ledger.xlsx file.
c. Scroll down the worksheet that is dis-
played to view its data.
d. Observe that once you scroll down to see
the information, the column headings are
no longer visible.
e. Scroll up and select cell E16.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 136

Copyright Element K Corporation
f. Select the View tab, and in the Window
group, click Split to display the vertical
and horizontal split bars.
g. In the top-right pane, scroll down.
h. Observe that the panes below are station-
ary, while both the panes at the top scroll
simultaneously.
i. In the bottom-right pane, scroll down to
compare different segments of the
worksheet.
j. On the View tab, in the Window group,
click the Split button again to remove the
split window.
2. Freeze panes and view data. a. Select cell A3.
b. On the View tab, in the Window group,
from the Freeze Panes drop-down list,
select Freeze Panes.
c. In the worksheet, scroll down to row 33.
d. Observe that the column headings are vis-
ible, while the rest of the worksheet can
be scrolled through.
LESSON 5
Lesson 5: Managing an Excel Workbook 137
Copyright Element K Corporation
e. In the Window group, from the Freeze
Panes drop-down list, select Unfreeze
Panes.
3. View the My Sales Summary and Sales
Ledger workbooks side by side.
a. On the View tab, in the Window group,
click the View Side by Side button.
b. Observe that the windows are displayed
one after another and in the Sales
Ledger.xlsx file, click on the scroll bar to
scroll down the worksheet.
c. Observe that both the windows scroll
simultaneously.
d. Close the Sales Ledger.xlsx file without
saving changes.
4. Create two new windows to view
three copies of the workbook.
a. In the My Sales Summary workbook, select
the US tab.
b. On the View tab, in the Window group,
click New Window.
c. Observe that the My Sales Summary work-
book opens in a new window with the title
My Sales Summary.xlsx:2 - Microsoft Excel.
d. In the Window group, click New Window
to create a another new window with the
title My Sales Summary.xlsx:3 - Microsoft
Excel.
e. Select cell E6, press Delete, type 15000
and press Enter to change the data in the
cell.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 138

Copyright Element K Corporation
f. On the View tab, in the Window group,
click the Switch Windows drop-down
arrow.
g. From the Switch Windows drop-down list,
select My Sales Summary.xlsx:2.
h. Observe that the content in cell E6 is
modified in this copy of the workbook too.
i. On the Quick Access toolbar, click the
Undo button to undo the modification.
LESSON 5
Lesson 5: Managing an Excel Workbook 139
Copyright Element K Corporation
5. View the windows arranged in differ-
ent styles.
a. In the Window group, click Arrange All to
display the Arrange Windows dialog box.
b. In the Arrange Windows dialog box,
select the Vertical option and click OK.
c. Observe that the three workbook windows
are displayed vertically one beside the
other.
d. In the My Sales Summary.xlsx:3 workbook
window, select the Mexico tab.
e. Click the My Sales Summary.xlsx:2 work-
book window, and select the Canada tab.
f. Observe that the three workbook windows
displays data in different tabs.
g. Close the My Sales Summary.xlsx:2 and My
Sales Summary.xlsx:3 windows.
h. Maximize the My Sales Summary.xlsx file.
i. Save and close the My Sales Summary.xlsx
file.
LESSON 5
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 140

Copyright Element K Corporation
Lesson5Follow-up
In this lesson, you managed an Excel workbook. This skill will enable you to efficiently access
and view specic areas of a worksheet.
1. How do you think Excels tools to manage multiple worksheets will be useful to you?
2. In what ways can you customize the display of worksheets in Excel?
LESSON 5
Lesson 5: Managing an Excel Workbook 141
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 142

NOTES
Copyright Element K Corporation
Printing Excel Workbooks
In this lesson, you will print the content of an Excel worksheet.
You will:
Dene the page layout.
Print a workbook.
Lesson Time
45 minutes LESSON 6
LESSON 6
Lesson 6: Printing Excel Workbooks 143
Copyright Element K Corporation
Introduction
You worked with a large workbook. You may now want to share a worksheet with others. One
way to share the content of a worksheet is by printing it. In this lesson, you will print the con-
tent of an Excel workbook.
You may be faced with situations where you want to share the content of a worksheet and
need a printout of it. Excel provides you with various options to print a workbook. The Print
option in Excel is integrated with the Backstage view, and provides you with the ability to
print worksheets with just a few mouse clicks.
TOPIC A
Dene the Page Layout
You used various methods to manage a workbook. The workbook is nalized and now ready
to be printed. Before printing, you want to adjust the print options that help you customize the
output to meet your needs. In this topic, you will dene the page layout for a worksheet.
Before printing a workbook, you need to check and adjust the page layout so that the printed
output looks professional. To achieve the desired print output, you may need to adjust the lay-
out of the worksheets, add headers and footers, or change the page orientation. Excel provides
you with various page layout options for customizing workbooks and improving their appear-
ance on the printed material.
Headers and Footers
Denition:
A header or footer is a data block that comprises text and graphics displayed at the top
or bottom of a printed page. By default, Excel headers and footers contain three
placeholders at the left, center, and right edges of a page, respectively. You can include
information in any of the three placeholders in a header or footer. Each placeholder
can contain text or graphics that will remain the same for all pages, or text that
changes based on some criteria, such as the page number.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 144

Copyright Element K Corporation
Example:
Figure 6-1: A worksheet with a header and footer.
Header and Footer Settings
Excel provides you with options to either use predened or create custom headers and footers.
Headers and footers are displayed only in the Page Layout view and on printed pages. Pre-
dened settings allow you to insert page numbers, page count, sheet numbers, predened text,
and current date in a predened layout. Alternatively, you can create custom headers and
footers that enable you to enter content in the left, center, and right header or footer text box.
You can also dene different headers and footers for odd and even pages if you want a
worksheet to be printed in a book style. In addition, you can remove the header and footer
from the rst printed page that usually displays the title.
Figure 6-2: Header and footer settings displayed on the Design contextual tab.
Page Margins
A page margin is a boundary line that determines the amount of space between the worksheet
data and the edge of the paper. Page margins dene a region within which the content of a
page should t in. Excel provides you with options to select from a list of predened margins
or specify a custom value for page margins. You can set the top, bottom, left, and right mar-
gins. You can also adjust the distance of the header from the top of the page and the distance
of the footer from the bottom of the page. You can set the option to print the data at the center
of the page horizontally and vertically.
LESSON 6
Lesson 6: Printing Excel Workbooks 145
Copyright Element K Corporation
Figure 6-3: A page with margins displayed.
Margins Tab Options
The options on the Margins tab allow you to set margin sizes for either an entire worksheet or
only the current section.
Option Enables You To
The Left, Top, Header,
Right, Bottom, and
Footer spin boxes
Set the respective margin values by using the up or down arrow or by enter-
ing a value for the margin.
The Center on page sec-
tion
Specify whether the content should be centered horizontally or vertically on
a printed page.
The Print button Set the print options in the Backstage view.
The Print Preview but-
ton
Preview a worksheet.
The Options button Set the document properties for the print output.
Page Orientation
Page orientation is a page layout setting that determines the layout of the content on a printed
page. It species whether a page is to be printed vertically or horizontally. In the Portrait ori-
entation, the height of the page will be greater than the width; this enables you to print more
rows of data, but fewer columns. In the Landscape orientation, the width will be greater than
the height; this enables you to print more columns of data, but fewer rows.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 146

Copyright Element K Corporation
Figure 6-4: A page displayed in the Portrait layout.
Figure 6-5: A page displayed in the Landscape layout.
The Print Titles Command
When a worksheet containing data that runs across multiple pages is printed, you can use the
Print Titles command to repeat the row and column titles on each printed page of a
worksheet. Repeating the row and column titles enables you to identify and interpret the con-
tent in a worksheet easily. You can set print titles by using the Print Titles section on the
Sheet tab in the Page Setup dialog box.
LESSON 6
Lesson 6: Printing Excel Workbooks 147
Copyright Element K Corporation
Figure 6-6: The Print Titles section displaying the rows and columns to be repeated.
Print Row and Column Headings
The Page Setup dialog box provides you with an option to print the numerical row
headings that are at the left of the worksheet rows and the alphabetical column head-
ings that appear at the top of the columns on a worksheet.
Page Breaks
Denition:
Page breaks are lines that split content across pages for print purposes. They are deter-
mined based on the paper size, page orientation, and any existing page breaks. Page
breaks generated by the application are referred to as automatic page breaks, and those
that are manually inserted are referred to as manual page breaks. You can convert an
automatic page break to a manual page break by repositioning it at a preferred loca-
tion.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 148

Copyright Element K Corporation
Example:
Figure 6-7: Page breaks marking where the content will be split when print-
ing.
Page Break Options
You can insert or remove manual page breaks in a worksheet by using the options available in
Excel.
Option Allows You To
Insert Page Break Insert a manual page break either above or to the left of a selected row or
column.
Remove Page Break Remove an existing manual page break from either above or the left of a
selected row or column.
Reset All Page Breaks Remove all the manual page breaks from a worksheet.
Gridlines
Gridlines are thin lines that divide a worksheet into rows and columns. Printing
gridlines enables you to read and reference information presented on a printed
worksheet easily. You can also change the color of the gridlines based on your prefer-
ence.
How to Dene the Page Layout
Procedure Reference: Insert a Header and Footer in an Excel Spreadsheet
To insert a header and footer in an Excel spreadsheet:
1. Open an Excel workbook.
2. Select the worksheet to which you want to add the header and footer.
3. Select the Page Layout view.
On the View tab, in the Workbook Views group, click Page Layout or;
LESSON 6
Lesson 6: Printing Excel Workbooks 149
Copyright Element K Corporation
On the Insert tab, in the Text group, click Header & Footer or;
On the status bar, next to the Zoom Level button, click the Page Layout
button.
4. Insert the header and footer.
On the Header & Footer Tools tool tab, on the Design contextual tab, in the
Header & Footer group, click Header or Footer, and from the displayed
list, select an option to insert a predened header and footer or;
On the header or footer, click the left, center, or right text box and enter the
desired text to create a custom header and footer.
5. Click any cell outside the text boxes to deselect the header or footer text box.
6. If necessary, return to the Normal view.
On the View tab, in the Workbook Views group, click Normal or;
On the status bar, next to the Zoom slider, click the Normal button.
7. If necessary, in the Page Setup dialog box, on the Header/Footer tab, specify the
desired header and footer option.
Check the Different odd and even pages check box to specify that the head-
ers and footers on the odd-numbered pages should be different from those on
the even-numbered pages.
Check the Different First Page check box to remove headers and footers
from the rst printed page.
Procedure Reference: Create or Modify a Header or Footer by Using the Page
Setup Dialog Box
To create or modify a header or footer by using the Page Setup dialog box:
1. Display the Page Setup dialog box.
Select the File tab, choose Print, and in the Backstage view, click the Page
Setup link.
Select the Page Layout tab, and in the Page Setup group, click the Page
Setup dialog box launcher.
You can also display the Page Setup dialog box by using the customization options from
the Margins or Size drop-down list.
2. In the Page Setup dialog box, select the Header/Footer tab.
3. From the Header or Footer drop-down list, select a predened header or footer.
4. If necessary, click Custom Header or Custom Footer and enter data in the left,
center, or right header text box.
5. Click OK to apply the header or footer settings.
Procedure Reference: Change the Header and Footer Size
To change the header and footer size:
1. Open an Excel workbook.
2. Select a worksheet with a header and footer.
3. Resize the header and footer.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 150

Copyright Element K Corporation
Change the size in the Page Layout view.
a. Switch to Page Layout view.
b. On the worksheet, click and drag the vertical ruler to increase or
decrease the size of the header or footer.
Click the ruler above the rst row header and drag upward to
decrease the header size or drag downward to increase the header
size.
Click the ruler below the last row header of the page and drag
upward to increase the footer size or drag downward to decrease
the footer size.
Change the size using the Page Setup dialog box.
a. Display the Page Setup dialog box and select the Margins tab.
b. Modify the header or footer size.
In the Header spin box, specify the header size.
In the Footer spin box, specify the footer size.
c. Click OK to apply the changes.
Procedure Reference: Set the Page Margins
To set the page margins:
1. On the Page Layout tab, in the Page Setup group, click Margins, and from the
displayed list, select the desired option.
Select Normal to set top and bottom margins at 0.75 inches and left and the
right margin at 0.7 inches.
Select Wide to set 1 inch for the top, bottom, left, and right margins and 0.5
inches for separating the header and footer from the top and bottom margins.
Select Narrow to set top and bottom margins at 0.75 inches, left and right
margins at 0.25 inches, and 0.3 inches for separating the header and footer
from the top and bottom margins.
2. Select Custom Margins, and in the displayed dialog box, specify the desired
option to customize the margins.
You can also set the page margins in the Backstage view. In the preview pane, click the Show
Margins button to display the margin guides that you can use to reposition the margins.
Procedure Reference: Change the Page Orientation
To change the page orientation:
1. Change the orientation of a worksheet.
In the Page Setup dialog box, on the Page tab, in the Orientation section,
select an option.
Select Portrait to print the worksheet vertically.
Select Landscape to print the worksheet horizontally.
On the Page Layout tab, in the Page Setup group, click Orientation and
select an option.
LESSON 6
Lesson 6: Printing Excel Workbooks 151
Copyright Element K Corporation
On the File tab, select Print, and in the Print pane, from the Portrait Ori-
entation drop-down list, select an option.
2. Save the worksheet with the new orientation.
Procedure Reference: Set or Remove a Print Title
To set or remove a print title:
1. On the Page Layout tab, in the Page Setup group, click Print Titles.
2. In the Page Setup dialog box, on the Sheet tab, specify a print title or remove an
existing print title.
In the Print titles section, click the Rows to repeat at top or Columns to
repeat at left text box and select the rows or columns that need to be used
as the print title.
You can click the Collapse Dialog button to the right of the Print Title text box to mini-
mize the Page Setup dialog box and make it easier to select data in a worksheet. Press
Enter or click the Expand Dialog button to maximize the dialog box when you are done.
Clicking anywhere in a row or column will select the entire row or column.
In the Rows to repeat at top and Columns to repeat at left text boxes,
delete any text that is already set and click OK to remove an existing print
title.
3. In the Page Setup dialog box, click OK to apply the changes.
Procedure Reference: Insert Page Breaks
To insert page breaks:
1. Select the row below or the column to the right of the location where you want to
insert a page break.
2. Insert a page break.
On the Page Layout tab, in the Page Setup group, click Breaks, and from
the displayed list, select Insert Page Break or;
Insert page breaks using Page Break Preview.
a. Enable Page Break Preview.
Click the Page Break Preview button or;
On the View tab, in the Workbook Views group, click Page Break
Preview.
b. Set the page breaks.
In the Page Break Preview window, drag the automatic page break
to the desired position or;
On the worksheet, right-click a cell adjacent to the page break and
choose Insert Page Break or;
In the worksheet, select the desired row or column, right-click the
selected row or column, and choose Insert Page Break.
c. Return to the Normal view.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 152

Copyright Element K Corporation
Page Break Preview
The Page Break Preview displays a worksheet as separate pages for printing. Page
breaks are based on the paper size, scale options and margin settings. The benet of
the Page Break Preview is that it shows the order in which pages will be printed in
addition to where page breaks will occur. You can adjust page breaks by clicking and
dragging the dotted blue lines, which represent page breaks.
The Page Layout View
The Page Layout view displays the worksheet as individual pages as they would
appear when printed, but also allows you to edit worksheet content. This view allows
you to insert the headers or footers at the top or bottom of a worksheet and also dis-
plays the horizontal and vertical rulers along with the column and row headers. It also
displays the margins for each page. The benet of the Page Layout view is that it
shows a preview of how the pages will be printed with headers and footers.
Procedure Reference: Remove Page Breaks
To remove page breaks:
1. Select the row below or the column to the right of a manual page break that you
want to remove.
2. Remove the page break.
On the Page Layout tab, in the Page Setup group, click Breaks, and from
the displayed list, select Remove Page Break or;
You can reset all the existing manual page breaks in a worksheet by using the Reset All
Page Break option in the drop-down list.
Remove page breaks using Page Break Preview.
a. Enable Page Break Preview.
Click the Page Break Preview button or;
On the View tab, in the Workbook Views group, click Page Break
Preview.
b. Remove the page breaks.
In the Page Break Preview window, drag the automatic page break
off the worksheet.
In the worksheet, select the desired row or column, right-click the
selected row or column, and choose Remove Page Break.
c. Return to the Normal view.
Procedure Reference: Create a Custom View
To create a custom view:
1. On the Excel worksheet, change the display and print settings as per your work
needs.
2. Select the View tab, and in the Workbook Views group, click Custom Views.
3. In the Custom Views dialog box, click Add.
4. In the Add View dialog box, in the Name text box, specify a name for the cus-
tom view.
LESSON 6
Lesson 6: Printing Excel Workbooks 153
Copyright Element K Corporation
5. In the Include in view section, check the check boxes to include the desired set-
tings in the view.
6. Click OK to create the custom view.
Custom Views
Excel provides options to create a custom view that will allow you to save specic
display settings such as column widths, row heights, lter settings and also the print
settings for a worksheet. You can create many custom views for a worksheet.
Procedure Reference: Apply a Custom View
To apply a custom view:
1. Select the View tab, and in the Workbook Views group, click Custom Views.
2. In the Custom Views dialog box, in the Views list box, select a custom view
that you want to apply, and then click Show.
ACTIVITY 6-1
Adding Headers and Footers
Data Files:
C:\084676Data\Printing Excel Workbooks\Sales Ledger.xlsx
Scenario:
You will be presenting a report on the sales revenue of your team. You want to distribute the
printed copies of the sales report to other managers. You decide to add the header and footer
information to the printed copies and preview the worksheet.
What You Do How You Do It
1. Create the header. a. Select the File tab, choose Open.
b. In the Open dialog box, navigate to the
C:\084676Data\Printing Excel Workbooks
folder, and open the Sales Ledger.xlsx
file.
c. Select the Insert tab, and in the Text
group, click Header & Footer.
Excel displays the worksheet in the Page Lay-
out view.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 154

Copyright Element K Corporation
d. In the left header text box, click and type
OGC Stores
e. In the center header text box, click and
type US Sales
f. Click the right header text box, and on
the Header & Footer Tools tool tab, on
the Design contextual tab, in the Header
& Footer Elements group, click Current
Time.
2. Create the footer. a. On the Header & Footer Tools tool tab,
on the Design contextual tab, in the Navi-
gation group, click Go to Footer.
b. Click the center footer text box, and on
the Design contextual tab, in the Header
& Footer group, click the Footer drop-
down arrow, and from the displayed list,
select Page 1 of ?.
LESSON 6
Lesson 6: Printing Excel Workbooks 155
Copyright Element K Corporation
3. Preview the header and footer infor-
mation.
a. Select the File tab, and choose Print.
b. In the right pane, observe the preview of
the worksheet.
c. In the Backstage view, at the bottom-right
corner of the right pane, click the Zoom
to Page button, to increase the mag-
nification and view the header
information.
d. Observe that the text OGC Stores and
US Sales are included in the header and
scroll to the right.
e. Observe that the current time is included
in the right section of the header and that
all of the columns do not fit within the
width of the page.
f. Scroll down to observe that the footer
information displays the page number.
g. Click the Zoom to Page button to
decrease the magnification level.
h. Save the worksheet as My Sales Ledger
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 156

Copyright Element K Corporation
ACTIVITY 6-2
Setting the Page Layout and Previewing a Worksheet
Before you Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
While previewing the sales report worksheet, you notice that the page orientation does not
accommodate all the columns in one page. You also want the Name and ID columns to be dis-
played on all the printed pages. You observe that the worksheet is not split logically, with
some of the data on one page and the rest of the data on the next page. You decide to modify
the page breaks to print the worksheet correctly.
What You Do How You Do It
1. Set the page orientation to Land-
scape.
a. Select the View tab, and in the Workbook
Views group, click Page Break Preview.
b. In the Welcome to Page Break Preview
dialog box, click OK.
c. Select the Page Layout tab, and in the
Page Setup group, click Orientation, and
from the displayed list, select Landscape.
LESSON 6
Lesson 6: Printing Excel Workbooks 157
Copyright Element K Corporation
2. Preview the worksheet. a. Select the File tab and choose Print.
b. In the right pane, observe that the page is
displayed horizontally, the Name and ID
columns are displayed, and the columns
are displayed only till the Lowest column.
c. In the right pane, click the Next Page but-
ton.
d. Observe that the column names are not
displayed on page 2, making it difficult to
know what information is presented in
each column.
e. Click the Next Page button.
f. Observe that the third page displays data
which cannot be related to the data on
the previous pages.
3. Insert a manual page break between
columns J and K.
a. Select the File tab to close the Backstage
view.
b. In the worksheet, click the page break
between the columns I and J and drag it
between the columns J and K.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 158

Copyright Element K Corporation
4. Set the column headings in rows one
and two as print titles.
a. On the Page Layout tab, in the Page
Setup group, click Print Titles.
b. In the Page Setup dialog box, on the
Sheet tab, in the Print titles section, in
the Rows to repeat at top text box, click
and type 1:2
c. Click OK to close the Page Setup dialog
box.
5. Preview the entire Sales Ledger
worksheet.
a. Select the File tab and choose Print.
b. Observe that the preview of the first page
of the worksheet contains information up
to the Commission column, and then click
the Next Page button.
c. Observe that the second page of the
worksheet displays the column titles.
d. Save the worksheet.
LESSON 6
Lesson 6: Printing Excel Workbooks 159
Copyright Element K Corporation
TOPIC B
Print a Workbook
You set the page layout options to control how the worksheet data will be displayed in print.
You are now ready to print a worksheet to share it with other people. In this topic, you will
specify the print options and print a workbook.
You have presented key information in a worksheet. You now want to make it accessible to
those who do not have access to a computer. Moreover, in a worksheet containing a large
amount of data, you may want to present the readers with only the necessary information. By
printing selective data, you can present the readers with only the relevant information. Excel
provides you with various options to print a worksheet.
The Print Options in the Backstage View
The Print command allows you to preview and print a worksheet. The print options are dis-
played in the Backstage view. The left pane of the Backstage view consists of the options to
print a document, specify the printer, and set the printer settings. The right pane displays the
preview of a worksheet and includes options to scroll through pages and display page margins.
The right pane also provides you with the option to zoom in or zoom out of a previewed page.
Print Section Enables You To
Print Set the number of copies that need to be printed and print a
worksheet.
Printer Select a printer from a list of available printers. It also allows you
to set the printer properties.
Settings Select the range of pages, specify the page orientation, select a
paper size, and set the margins.
Printing Selected Worksheets
The Settings section allows you to print selected worksheets by rst selecting the
worksheets that you want to print and then selecting the Print Active Sheets option
from the Settings drop-down list in the Backstage view.
Printing an Entire Workbook
The Settings section allows you to print an entire workbook by rst selecting any of
the worksheets and then selecting the Print Entire Workbook option from the Set-
tings drop-down list in the Backstage view.
The Print Area
The print area is a particular portion of a worksheet that you need to print often by selecting
the desired area in the worksheet. By default, when you print a worksheet, Excel prints the
dened print area. The options in the Print Area drop-down list allow you to set or clear the
print area. You can also add another selection to the print area by using the Add to Print Area
option.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 160

Copyright Element K Corporation
Figure 6-8: A marquee displaying the print area.
Scaling Options
The scaling options in the Scale to Fit group on the Page Layout tab enable you to restrict
the printed output of a worksheet to a desired number of pages. You can scale to shrink or
increase the width or height of a worksheet to t within a desired number of pages, or you can
shrink or increase the worksheet to a specic percentage of the actual size. You can also use
the Adjust to and Fit to options on the Page tab in the Page Setup dialog box to set the scal-
ing for a worksheet.
Figure 6-9: Various scaling options in the Scale to Fit group.
How to Print a Workbook
Procedure Reference: Set or Clear the Print Area
To set or clear the print area:
1. In a worksheet, select the cells that you want to set as the print area, or select the
cells that are currently set as the print area.
2. On the Page Layout tab, in the Page Setup group, click Print Area and select
Set Print Area or Clear Print Area.
3. If necessary, select specic cells, and from the Print Area drop-down list, select
Add to Print Area to add additional ranges of cells to the print area.
4. Save the worksheet with the new print area settings.
LESSON 6
Lesson 6: Printing Excel Workbooks 161
Copyright Element K Corporation
Procedure Reference: Scale the Printable Range
To scale the printable range:
1. Scale the printable range.
On the Page Layout tab, in the Scale to Fit group, select the Height,
Width, or Scale option to scale the range or;
Open the Page Setup dialog box, and on the Page tab, in the Scaling sec-
tion, scale the range to adjust the scaling to a specic percentage, or t the
whole spreadsheet within a specied number of pages.
2. If necessary, preview and print the workbook.
3. Save the workbook with the scaled settings.
Procedure Reference: Print an Excel Worksheet
To print an Excel worksheet:
1. Select the File tab and choose Print.
2. In the Backstage view, in the left pane, in the Print section, in the Copies spin
box, specify the number of copies that you need.
3. In the Printer section, from the Printer drop-down list, select a printer.
4. If necessary, click the Printer Properties link to set the properties of the printer.
5. In the Settings section, in the Pages and to spin boxes, specify the page range.
6. From the Collation drop-down list, select Collated to completely print the rst
set of the selected pages and then the next set of copies when you are printing
multiple copies.
7. In the right pane, preview the worksheet and click Print.
ACTIVITY 6-3
Setting the Print Area
Before You Begin:
The My Sales Ledger.xlsx le is open.
Scenario:
You need to present the sales report to your manager in a single page format, with only the
quarterly sales and total sale made by each sales person. You decide to dene a print area and
set the options to scale the content to be printed.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 162

Copyright Element K Corporation
What You Do How You Do It
1. Preview the range of cells that needs
to be printed.
a. Select the View tab, and in the Workbook
Views group, click Normal to view the
workbook in the normal view.
b. Select the cell range A2:F58.
If you forget to select the range, or forget to
select Selection in the Print dialog box, the
entire worksheet will be displayed in the pre-
view.
c. Select the File tab and choose Print.
d. In the Backstage view, in the Settings sec-
tion, from the Print Active Sheets drop-
down list, select Print Selection.
e. Observe that the selected range is dis-
played in two pages in the right pane of
the Backstage view.
f. Select the File tab to return to the
worksheet.
LESSON 6
Lesson 6: Printing Excel Workbooks 163
Copyright Element K Corporation
2. Set the selected range of cells as the
print area.
a. Verify that the cell range A2:F58 is
selected. Select the Page Layout tab, and
in the Page Setup group, from the Print
Area drop-down list, select Set Print
Area.
b. Click any cell to deselect the selected
range.
c. Observe the marquee around the cell
range A2:F58 that indicates the set print
area.
d. Select the File tab and choose Print.
e. In the Backstage view, in the Settings sec-
tion, from the Print Selection drop-down
list, select Print Active Sheets.
f. Observe that the print area that is set is
displayed in two pages in the print pre-
view.
g. Select the File tab to close the Backstage
view.
3. Change the color of the gridlines. a. On the Page Layout tab, in the Sheet
Options group, in the Gridlines section,
check the Print check box.
b. Select the File tab and choose Options.
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 164

Copyright Element K Corporation
c. In the Excel Options dialog box, select
the Advanced tab, scroll down, and in the
Display options for this worksheet sec-
tion, click the Gridline color drop-down
arrow, and select Dark Red.
d. Click OK to close the Excel Options dialog
box.
e. Observe that the gridlines in the
worksheet are displayed in red.
LESSON 6
Lesson 6: Printing Excel Workbooks 165
Copyright Element K Corporation
4. Scale and print the data in the entire
worksheet.
a. On the Page Layout tab, in the Scale to
Fit group, from the Height drop-down list,
select 1 page to fit all the rows of data in
the worksheet within a single page.
b. Select the File tab and choose Print.
c. Observe that the print area of the
worksheet fits within a single page but the
page has excessive white space at the
right side.
d. In the Settings section, from the Land-
scape Orientation drop-down list, select
Portrait Orientation to change the page
orientation.
e. In the Print section, click Print to print
the worksheet with the new settings.
f. Save the worksheet and close it.
g. Close the application.
Lesson6Follow-up
In this lesson, you printed a worksheet. Printing selective data saves paper when only small
subsets of a worksheet are to be printed and it also helps readers view only the necessary data.
1. Which page layout option will you use frequently? Why?
2. How do you think the Print Area option will be beneficial to you?
LESSON 6
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 166

Copyright Element K Corporation
Follow-up
In this course, you used Excel to manage, edit, and print data. Storing data electronically is
more efficient than storing it in a paper-based system because it allows you to quickly update
existing data, run reports on the data, calculate totals, and sort and lter data.
1. Consider your current work environment. What are the advantages if the entire
project is controlled and manipulated within Excel?
2. What specific techniques in this course did you find the most interesting and useful?
3. How does the Backstage view help you in printing workbooks?
Whats Next?
Microsoft

Offce Excel

2010 - Level 2 is the next course in this series. In this course, you
will use advanced formulas and work with various tools to analyze data in spreadsheets. You
will also organize table data, present data as charts, and enhance the look and appeal of work-
books by adding graphical objects.
FOLLOW-UP
167
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 168

NOTES
Copyright Element K Corporation
Microsoft OfficeExcel 2010
Exam77882
Selected Element K courseware addresses Microsoft Office Specialist certication skills for
Microsoft Office 2010. The following table indicates where Excel 2010 skills are covered. For
example, 3-A indicates the lesson and topic number applicable to that skill, and 3-1 indicates
the lesson and activity number.
Objective Domain Level Topic Activity
1. Managing the Worksheet Environment
1.1 Navigate through a worksheet
1.1.1 Use hot keys 1 1-B 1-2
1.1.2 Use the name box 1 1-A, 3-B 3-2
1.2 Print a worksheet or workbook
1.2.1 Print only selected worksheets 1 6-B
1.2.2 Print an entire workbook 1 6-B
1.2.3 Construct headers and footers 1 6-A 6-1
1.2.4 Apply printing options
1.2.4.1 Scale 1 6-B 6-3
1.2.4.2 Print titles 1 6-A 6-2
1.2.4.3 Page setup 1 6-A 6-2, 6-3
1.2.4.4 Print area 1 6-B 6-3
1.2.4.5 Gridlines 1 6-A 6-3
1.3 Personalize the environment by using Backstage
1.3.1 Manipulate the Quick Access Toolbar 1 1-C 13
1.3.2 Customize the ribbon
1.3.2.1 Tabs 1 1-C
1.3.2.2 Groups 1 1-C
1.3.3 Manipulate Excel default settings (Excel
Options)
1
2
1-C
6-A
1-3
6-1
APPENDIX A
APPENDIX A
Appendix A: Microsoft Ofce Excel 2010 Exam77882 169
Copyright Element K Corporation
Objective Domain Level Topic Activity
1.3.4 Manipulate workbook properties (document
panel)
3 1-A
1.3.5 Manipulate workbook les and folders
1.3.5.1 Manage versions 2 6-A
1.3.5.2 AutoSave 2 6-A
2. Creating Cell Data
2.1 Construct cell data
2.1.1 Use paste special
2.1.1.1 Formats 1 2-C
2.1.1.2 Formulas 1 2-C
2.1.1.3 Values 1 2-C
2.1.1.4 Preview icons 1 2-C
2.1.1.5 Transpose rows 1 3-A 3-1
2.1.1.6 Transpose columns 1 3-A
2.1.1.7 Operations
2.1.1.7.1 Add 1 2-C
2.1.1.7.2 Divide 1 2-C
2.1.1.8 Comments 1 2-C
2.1.1.9 Validation 1 2-C
2.1.1.10 Paste as a link 1 2-C
2.1.2 Cut 1 2-C
2.1.3 Move 1 2-C,3-A 3-1
2.1.4 Select cell data 1 1-B 1-2
2.2 Apply AutoFill
2.2.1 Copy data 1 3-A
2.2.2 Fill a series 1 3-A 3-1
2.2.3 Preserve cell format 1 3-A
2.3 Apply and manipulate hyperlinks
2.3.1 Create a hyperlink in a cell 2 6-B 6-2
2.3.2 Modify hyperlinks 2 6-B
2.3.3 Modify hyperlinked cell attributes 2 6-B
2.3.4 Remove a hyperlink 2 6-B
3. Formatting Cells and Worksheets
3.1 Apply and modify cell formats
3.1.1 Align cell content 1 4-C 4-3
3.1.2 Apply a number format 1 4-D 4-4
3.1.3 Wrapping text in a cell 1 4-C 4-3
3.1.4 Use Format Painter 1 4-A 4-2
3.2 Merge or split cells
3.2.1 Use Merge & Center 1 4-C 4-3
3.2.2 Merge Across 1 4-C
APPENDIX A
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 170

Copyright Element K Corporation
Objective Domain Level Topic Activity
3.2.3 Merge cells 1 4-C
3.2.4 Unmerge Cells 1 4-C
3.3 Create row and column titles
3.3.1 Print row and column headings 1 6-A
3.3.2 Print rows to repeat with titles 1 6-A 6-2
3.3.3 Print columns to repeat with titles 1 6-A
3.3.4 Congure titles to print only on odd or even
pages
1 6-A
3.3.5 Congure titles to skip the rst worksheet
page
1 6-A
3.4 Hide or unhide rows and columns
3.4.1 Hide or unhide a column 1 3-C
3.4.2 Hide or unhide a row 1 3-C
3.4.3 Hide a series of columns 1 3-C 3-4
3.4.4 Hide a series of rows 1 3-C
3.5 Manipulate Page Setup options for worksheets
3.5.1 Congure page orientation 1 6-A 6-2
3.5.2 Manage page scaling 1 6-B 6-3
3.5.3 Congure page margins 1 6-A
3.5.4 Change header and footer size 1 6-A
3.6 Create and apply cell styles
3.6.1 Apply cell styles 1 4-E 4-5
3.6.2 Construct new cell styles 1 4-E
4. Managing Worksheets and Workbooks
4.1 Create and format worksheets
4.1.1 Insert worksheets
4.1.1.1 Single 1 5-A 5-2
4.1.1.2 Multiple 1 5-A
4.1.2 Delete worksheets
4.1.2.1 Single 1 5-A
4.1.2.2 Multiple 1 5-A
4.1.3 Reposition worksheets 1 5-A
4.1.4 Copy worksheets 1 5-A 5-2
4.1.5 Move worksheets 1 5-A 5-2
4.1.6 Rename worksheets 1 5-A 5-1
4.1.7 Group worksheets 1 5-A
4.1.8 Apply color to worksheet tabs 1 5-A 5-1
4.1.9 Hide worksheet tabs 1 5-A 5-2
4.1.10 Unhide worksheet tabs 1 5-A 5-2
4.2 Manipulate window views
4.2.1 Split window views 1 5-B 5-3
APPENDIX A
Appendix A: Microsoft Ofce Excel 2010 Exam77882 171
Copyright Element K Corporation
Objective Domain Level Topic Activity
4.2.2 Arrange window views 1 5-B 5-3
4.2.3 Open a new window with contents from the
current worksheet
1 5-B 5-3
4.3 Manipulate workbook views
4.3.1 Use Normal workbook view 1 1-A 1-1
4.3.2 Use Page Layout workbook view 1 6-A 6-1
4.3.3 Use Page Break workbook view 1 6-A 6-1
4.3.4 Create custom views 1 6-A
5. Applying Formulas and Functions
5.1 Create formulas
5.1.1 Use basic operators 1 2-A 21
5.1.2 Revise formulas 2 2-A
5.2 Enforce precedence
5.2.1 Order of evaluation 1 2-A
5.2.2 Precedence using parentheses 1 2-A
5.2.3 Precedence of operators for percent vs. expo-
nentiation
1 2-A
5.3 Apply cell references in formulas
5.3.1 Relative and absolute references 1 2-C 2-3
5.4 Apply conditional logic in a formula
5.4.1 Create a formula with values that match con-
ditions
2 1-D
5.4.2 Edit dened conditions in a formula 2 1-D
5.4.3 Use a series of conditional logic values in a
formula
2 1-D
5.5 Apply named ranges in formulas
5.5.1 Dene ranges in formulas 2 1-A 1-1
5.5.2 Edit ranges in formulas 2 1-A
5.5.3 Rename a named range 2 1-A 1-1
5.6 Apply cell ranges in formulas
5.6.1 Enter a cell range denition in the formula bar 1 3-B
5.6.2 Dene a cell range 1 3-B 3-2
6. Presenting Data Visually
6.1 Create charts based on worksheet data 2 3-A 3-1
6.2 Apply and manipulate illustrations
6.2.1 Insert 2 5-A 5-1
6.2.2 Position 2 5-B
6.2.3 Size 2 5-A, 5-B 5-1, 5-2
6.2.4 Rotate 2 5-B
6.2.5 Modify Clip Art SmartArt 2 5-C 5-3
6.2.6 Modify Shape 2 5-B 5-2
APPENDIX A
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 172

Copyright Element K Corporation
Objective Domain Level Topic Activity
6.2.7 Modify Screenshots 2 5-A
6.3 Create and modify images by using the Image
Editor
6.3.1 Make corrections to an image
6.3.1.1 Sharpen or soften an image 2 5-A
6.3.1.2 Change brightness 2 5-A
6.3.1.3 Change contrast 2 5-A
6.3.2 Use picture color tools 2 5-A
6.3.3 Change artistic effects on an image 2 5-A
6.4 Apply Sparklines
6.4.1 Use Line chart types 3 4-B 4-2
6.4.2 Use Column chart types 3 4-B
6.4.3 Use Win/Loss chart types 3 4-B
6.4.4 Create a Sparkline chart 3 4-B
6.4.5 Customize a Sparkline 3 4-B
6.4.6 Format a Sparkline 3 4-B 4-2
6.4.7 Show or hiding data markers 3 4-B 4-2
7. Sharing Worksheet Data with other users
7.1 Share spreadsheets by using Backstage
7.1.1 Send a worksheet via Email or Skydrive 3 2-B
7.1.2 Change the le type to a different version of
Excel
1 1-D 1-4
7.1.3 Save as PDF or XPS 3 2-B
7.2 Manage comments
7.2.1 Insert 2 6-B 6-2
7.2.2 View 2 6-B
7.2.3 Edit 2 6-B 6-2
7.2.4 Delete comments 2 6-B
8. Analyzing and Organizing Data
8.1 Filter data
8.1.1 Dene a lter 2 2-C 2-4
8.1.2 Apply a lter 2 2-C 2-4
8.1.3 Remove a lter 2 2-C
8.1.4 Filter lists using AutoFilter 2, 2-C
8.2 Sort data
8.2.1 Use sort options
8.2.1.1 Values 2 2-C 2-4
8.2.1.2 Font color 2 2-C
8.2.1.3 Cell color 2 2-C
8.3 Apply conditional formatting
8.3.1 Apply conditional formatting to cells 3 1-D 1-4
APPENDIX A
Appendix A: Microsoft Ofce Excel 2010 Exam77882 173
Copyright Element K Corporation
Objective Domain Level Topic Activity
8.3.2 Use the Rule Manager to apply conditional
formats
3 1-D 1-5
8.3.3 Use the IF function to apply conditional for-
matting
3 1-D
8.3.4 Clear rules 3 1-D
8.3.5 Use icon sets 3 1-D
8.3.6 Use data bars 3 1-D
APPENDIX A
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 174

Copyright Element K Corporation
Microsoft OfficeExcel 2010
Expert Exam77888
Selected Element K courseware addresses Microsoft Office Specialist certication skills for
Microsoft Office 2010. The following table indicates where Excel 2010 Expert skills are cov-
ered. For example, 3-A indicates the lesson and topic number applicable to that skill, and 3-1
indicates the lesson and activity number.
Objective Domain Level Topic Activity
1.1. Apply workbook settings, properties, and data
options
1.1.1. Set advanced properties 3 1-A 1-1
1.1.2. Save a workbook as a template 2 6-D 6-4
1.1.3. Import and export XML data 3 6-C 6-3, 6-4
1.2. Apply protection and sharing properties to
workbooks and worksheets
1.2.1. Protect the current sheet 3 2-A 2-1, 2-2
1.2.2. Protect the workbook structure 3 2-A 2-3
1.2.3. Restricting permissions 3 2-G 2-9, 2-10
1.2.4. Require a password to open a workbook 3 2-A 2-3
1.3. Maintain shared workbooks
1.3.1. Merge workbooks 3 2-E 2-7
1.3.2. Set Track Changes options 3 2-C, 2-D 2-5, 2-6
2. Applying Formulas and Functions
2.1. Audit formulas
2.1.1. Trace formula precedents 3 3-A 3-1
2.1.2. Trace dependents 3 3-A 3-1
2.1.3. Trace errors 3 3-B 3-3
2.1.4. Locate invalid data 3 3-B 3-2
2.1.5. Locate invalid formulas 3 3-B 3-2, 3-3
APPENDIX B
APPENDIX B
Appendix B: Microsoft Ofce Excel 2010 Expert Exam77888 175
Copyright Element K Corporation
Objective Domain Level Topic Activity
2.1.6. Correct errors in formulas 3 3-B 3-2, 3-3
2.2. Manipulate formula options
2.2.1. Set iterative calculation options 4 3-B 3-3
2.2.2. Enable or disabling automatic workbook cal-
culation
2 6-A
2.3. Perform data summary tasks
2.3.1. Use an array formula 4 1-A 1-1
2.3.2. Use a SUMIFS function 4 4-C 4-3
2.4. Apply functions in formulas
2.4.1. Find and correct errors in functions 3 3-B 3-3
2.4.2. Applying arrays to functions 4 1-A 1-1
2.4.3. Use Statistical functions 4 4-A, 4-B, 4-C 1-1, 1-2, 1-3
2.4.4. Use Date functions 4 Appendix A
2.4.5. Use Time functions 4 Appendix A
2.4.6. Use Financial functions 4 3-A, 3-B 3-1, 3-3
2.4.7. Use Text functions 4 5-A 5-1
2.4.8. Cube functions 4 Appendix A
3. Presenting Data Visually
3.1. Apply advanced chart features
3.1.1. Use Trend lines 3 4-A 4-1
3.1.2. Use Dual axes 4 6-B 6-2
3.1.3. Use chart templates 4 6-B
3.1.4. Use Sparklines 3 4-B 4-2
3.2. Apply data analysis
3.2.1. Use automated analysis tools 3
4
4-E
2-A, 2-B, 2-C
2-E, 2-F
4-6
2-1, 2-2, 2-3,
2-5, 2-6
3.2.2. Perform What-If analysis 3 4-C, 4-D 4-3, 4-4
3.3. Apply and manipulate PivotTables
3.3.1. Manipulate PivotTable data 2 4-A 4-1
3.3.2. Use the slicer to lter and segment your
PivotTable data in multiple layers
2 4-B 4-2
3.4. Apply and manipulate PivotCharts
3.4.1. Create PivotChart 2 4-C 4-3
3.4.2. Manipulate PivotChart data 2 4-C 4-3
3.4.3. Analyzing PivotChart data 2 4-C 4-3
3.5. Demonstrate how to use the slicer
3.5.1. Choose data sets from external data connec-
tions
2 4-B
4. Working with Macros and Forms
4.1. Create and manipulate macros
4.1.1. Run a macro 3 1-B 1-2
APPENDIX B
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 176

Copyright Element K Corporation
Objective Domain Level Topic Activity
4.1.2. Run a macro when a workbook is opened 3 1-B
4.1.3. Run a macro when a button is clicked 3 1-B
4.1.4. Record an action macro 3 1-B
4.1.5. Assign a macro to a command button 3 1-B
4.1.6. Create a custom macro button on the Quick
Access Toolbar
3 1-B
4.1.7. Apply modications to a macro 3 1-C 1-3
4.2. Insert and manipulate form controls
4.2.1. Insert form controls 3 Appendix A
4.2.2. Set form properties 3 Appendix A
APPENDIX B
Appendix B: Microsoft Ofce Excel 2010 Expert Exam77888 177
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 178

NOTES
Copyright Element K Corporation
Due to classroom setup constraints, some labs cannot be keyed in sequence immediately fol-
lowing their associated lesson. Your instructor will tell you whether your labs can be practiced
immediately following the lesson or whether they require separate setup from the main lesson
content. Lesson-level lab setup information is listed in the front of this manual in the course
setup section.
LESSON 1 LAB 1
Creating a Basic Worksheet Using Excel 2010
Activity Time:
10 minutes
Scenario:
As the Human Resources manager at Everything for Coffee, you are charged with the task of
analyzing employee information using an Excel worksheet. You rst want to enter the data in
the worksheet.
1.
Launch the Microsoft Excel 2010 application.
2.
Create headers named Name, Emp ID, Location, and Hourly Wage in row 1 of
columns A, B, C, and D, respectively.
3.
In cells A2, A3, and A4, enter the labels Miller, Doyle, and Johnson in the Name
column.
LESSON LABS
LESSON
LABS
Lesson Labs 179
Copyright Element K Corporation
4.
Enter the information for each employee in the corresponding cells of the
worksheet, according to the details provided in the scenario.
5.
Perform a compatibility check on the worksheet.
6.
Save the workbook as My Employee Information.xlsx and also save a copy in the
XLS format.
LESSON 2 LAB 1
Executing Formulas and Functions
Activity Time:
15 minutes
Data Files:
C:\084676Data\Performing Calculations in an Excel Worksheet\Sales Summary.xlsx
enus_084676_02_1_datales.zip
Scenario:
As a sales executive, you have to track Everything for Coffees sales values for the four quar-
ters in an Excel worksheet. You also need to analyze the sales performance of your team and
hand it over to your manager.
1.
Open the Sales Summary.xlsx file from the C:\084676Data\Performing Calculations
in an Excel Worksheet folder.
2.
In cell E21, use the AutoSum feature to calculate the total sales for the first quar-
ter.
3.
Copy the formula to calculate the total sales of the cell range F21:H21.
4.
In row 22, calculate the maximum sales for all four quarters.
5.
In row 23, calculate the commission amount paid for each quarter at the rate
specified in cell L2.
6.
Save the workbook as My Sales Summary.xlsx and close it.
LESSON
LABS
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 180

Copyright Element K Corporation
LESSON 3 LAB 1
Reorganizing a Worksheet
Activity Time:
10 minutes
Data Files:
C:\084676Data\Modifying an Excel Worksheet\Everything for Coffee.xlsx
enus_084676_03_1_datales.zip
Scenario:
As a sales executive, you have tracked Everything for Coffees projected sales values for the
rst half of the year on an Excel worksheet. You want to make the following modications to
the worksheet before sending it to your manager.
Display the cost price value next to the relevant label.
Create additional columns for February through June and ll in the sales values.
Include two other items: Percolator and Chocolate.
Change the specialty item to a more detailed name.
Correct an incorrect subtitle in the spreadsheet.
Spell check the entire spreadsheet and save a copy of the workbook with the
changes made.
1.
Open the Everything for Coffee.xlsx file from the C:\084676Data\Modifying an
Excel Worksheet folder.
2.
Move the cost price value from cell D13 to cell D14.
3.
Add columns after January, for February through June, and enter their sales val-
ues as 100, 85, 150, 175, and 210, respectively.
4.
Insert two rows below the existing row 10 to include two more items, namely Per-
colator and Chocolate.
5.
Include prices for Percolator and Chocolate as 162 and 32, respectively, in the
Price column.
6.
Edit cell A8 so that it reads Specialty Coffee.
7.
Replace the word protected with projected.
8.
Spell check the worksheet and correct the misspelled words.
LESSON
LABS
Lesson Labs 181
Copyright Element K Corporation
9.
Save the file as My Everything for Coffee.xlsx and close it.
LESSON 4 LAB 1
Enhancing a Worksheet
Activity Time:
10 minutes
Data Files:
C:\084676Data\Modifying the Appearance of a Worksheet \Sales Summary.xlsx
enus_084676_04_1_datales.zip
Scenario:
You have recorded the sales revenue generated by the employees of the Everything for Coffee
company. You want to distinguish the column headings and the Total row from the rest of the
content in the worksheet. You also want to emphasize the title of the worksheet to enhance the
presentation.
Column heading format:
Font face, size, and style: Arial, 12 pt, Bold.
Align them with the respective cells.
Use the Thick Box Border to distinguish the column headings and Total row
from the other worksheet information.
Format currency values using the Accounting format so that the decimal points
and the dollar signs are aligned in one column.
1.
Open the Sales Summary.xlsx file from the C:\084676Data\Modifying the
Appearance of a Worksheet folder.
2.
Modify the font face, size, and style of the column headings according to the com-
pany specification.
3.
Align all the column headings to match the position in the respective cells.
4.
Add the specified border to the column headings and Total row.
5.
Apply the Heading 1 cell style to the title of the worksheet.
6.
Apply the Accounting number format to the cell range that contains financial val-
ues.
LESSON
LABS
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 182

Copyright Element K Corporation
7.
Save the workbook as My Sales Summary.xlsx and close it.
LESSON 5 LAB 1
Managing Excel Workbooks
Activity Time:
10 minutes
Data Files:
C:\084676Data\Managing an Excel Workbook\Company Info.xlsx
enus_084676_05_1_datales.zip
Scenario:
You have consolidated your companys sales information into a single workbook. The work-
book contains ve worksheets, and you decide to apply formatting to the worksheet tabs so
that you can identify them easily. You also want to delete the blank worksheets from the work-
book and organize the remaining worksheets in a particular order. In addition, you want to
create a workbook out of the programmers information provided in one of the worksheets. In
the new workbook, you want to freeze the column headings of the Programmers worksheet so
that you will always be able to see the headings, even when you scroll through the worksheet.
1.
Open the Company Info.xlsx file from the C:\084676Data\Managing an Excel Work-
book folder.
2.
Delete Sheet3 and Sheet5.
3.
Name the worksheets, starting from the left, as Programmers, Holidays Info, and
Schedule.
4.
Reposition the worksheet tabs in the order specified below.
a. Holidays Info
b. Programmers
c. Schedule
5.
Color the worksheet tabs.
6.
Open a new window with a view of the current workbook.
7.
In the new workbook, freeze the column headings on the Programmers worksheet.
LESSON
LABS
Lesson Labs 183
Copyright Element K Corporation
8.
Save the workbook as My Company Info.xlsx and close it.
LESSON 6 LAB 1
Printing a Workbook
Activity Time:
10 minutes
Data Files:
C:\084676Data\Printing Excel Workbooks\Sales Summary.xlsx
enus_084676_06_1_datales.zip
Scenario:
You have prepared a sales report for the Everything For Coffee company and wanted to dis-
tribute a printout of the report to your sales team. You want the rst four columns and second
row of the worksheet to be displayed on each page of the printout and the page number to be
displayed in the footer. You need to format the worksheet split so that the data for each quarter
appears on separate pages in print.
1.
Open the Sales Summary.xlsx file from the C:\084676Data\Printing Excel
Workbooks folder.
2.
Change the page orientation to Landscape.
3.
Set row 2 and columns A to D as the print title.
4.
Set cells A3:H27 as the print area.
5.
Add the page number to the footer.
6.
Add page breaks after the data for every quarter.
7.
Save the workbook as My Sales Summary.xlsx.
8.
Preview and print the worksheet.
LESSON
LABS
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 184

Copyright Element K Corporation
SOLUTIONS
SOLUTIONS
Solutions 185
Copyright Element K Corporation
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 186

NOTES
Copyright Element K Corporation
A
absolute references, 55
creating, 56
active cells, 15
arguments, 43
B
Backstage view, 4
borders, 96
adding, 97
C
cell names, 72
cell styles, 112
applying, 113
cells, 14
adding a color, 98
aligning content, 103
applying number formats, 108
wrapping text, 104
commands
Arrange All, 133
Find, 72
Indent, 101
New Window, 134
Print, 160
Print Titles, 147
Replace, 74
Split, 132
Switch Windows, 134
Undo, 64
Redo
using the Undo and Redo commands, 68
View Side by Side, 134
contextual tabs, 7
D
data formats, 107
data types, 29
dialog boxes
Excel Options, 20
Format Cells, 91
Save As, 30
Spelling, 74
Style, 112
E
Excel, 2
F
features
Auto Fill, 64
AutoSum, 45
calculating values by using the AutoSum
feature, 47
Compatibility Checker, 30
Formula AutoComplete, 44
ll handle, 64
lls, 96
fonts, 90
changing the font properties, 93
footers, 144
format painter, 91
formatting cells, 93
Formula Bar, 7
formulas, 36
applying, 46
creating, 38
function names, 43
functions, 43
inserting, 46
G
galleries, 91
gridlines, 149
H
headers, 144
INDEX
INDEX
Index 187
Copyright Element K Corporation
K
keyboard navigation options, 16
L
live preview, 54
M
mixed references, 55
mouse navigation options, 16
O
orientation options, 102
P
page breaks, 148
inserting, 152
page margins, 145
setting, 151
page orientation, 146
changing, 151
print area, 160
setting or clearing, 161
R
ranges
contiguous, 17
noncontiguous, 17
relative references, 55
Ribbon, 3
customizing, 22
S
scaling options, 161
ScreenTips, 4
customizing the style, 21
spreadsheets, 14
inserting a header and footer, 149
status bar, 6
customizing, 22
T
templates, 8
toolbars
customizing the Quick Access toolbar, 21
Mini, 92
Quick Access, 6
Transpose option, 66
transposing data, 67
typeface, 90
W
windows
application, 2
Excel Help, 8
workbook, 2
workbooks, 15
creating and entering data, 30
nding and replacing cell data, 75
saving, 31
worksheets, 14
changing the column width and row height, 83
checking for spelling errors, 75
editing cell data, 66
freezing or unfreezing panes, 135
hiding or unhiding, 124
hiding or unhiding columns or rows, 84
inserting or deleting, 122
navigating and selecting cells, 18
INDEX
Microsoft

Ofce Excel

2010 - Level 1 (Second Edition) 188

Copyright Element K Corporation