Microsoft Office Excel 2010 Foundation
Microsoft Office Excel 2010 Foundation Courseware
Written by John Laska
Published by Velsoft Training Materials Inc.
Courseware Release Version 3.0
© 2011 by Velsoft Training Materials, Inc.
Notice of Rights
No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language or computer language, in any form or by any means, electronic, mechanical, magnetic, optical, chemical, manual, or otherwise, without the prior written permission of Velsoft Training Materials, Inc. except under the terms of a courseware site license agreement.
Trademark Notice
PowerPoint, Windows, Word, Microsoft are trademarks of Microsoft, Inc. Throughout this courseware title, trademark names are used. Rather than just put a trademark symbol in each occurrence of a trademarked name, we state we are using the names only in an editorial fashion and to the benefit of the trademark owner with no intention of infringement of the trademark.
Notice of Liability
The information in this courseware title is distributed on an ‘as is’ basis, without warranty. While every precaution has been taken in the preparation of this course, neither the authors nor Velsoft Training Materials, Inc. shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the instructions contained in this book or by the computer software and hardware products described in it.
Disclaimer
We make a sincere effort to ensure the accuracy of the material described herein; however, Velsoft International makes no warranty, expressed or implied, with respect to the quality, correctness, reliability, accuracy, or freedom from error of this document or the products it describes. Data used in examples and sample data files are intended to be fictional. Any resemblance to real persons or companies is entirely coincidental.
All information in this manual was correct at the time of writing. Velsoft is not affiliated with nor has any control over changes made to the product described in this manual. These include, but are not limited to, changes in the application’s color scheme, icon appearance and locations, addition or removal of program features, online templates, and help content. Velsoft reserves the right to make corrections to the courseware at any time and without notification.
Terms and conditions
Sample versions: If the version of courseware that you are viewing is marked as NOT FOR TRAINING, SAMPLE, or similar, then it is made available for content and style review only and cannot be used in any part of a training course. Sample versions may be shared but cannot be re-sold to a third party. For licensed users: This document may only be used under the terms of the license agreement from Velsoft International, Inc. Velsoft reserves the right to alter the licensing conditions at any time, without prior notice.
Velsoft Training Materials, Inc.
185 Provost Street
New Glasgow, Nova Scotia
B2H 2P8
Canada
Table of Contents
Introduction 1
Prerequisites 1
Section 1: Getting Started 2
Lesson 1.1: Starting Out 3
What is Microsoft Office Excel 2010? 3
What’s New in Excel 2010? 4
Opening Excel 7
Interacting with Excel 9
Closing Excel 12
Lesson 1.2: About Workbooks 14
Creating a New Workbook 14
Opening a Workbook 16
Saving a Workbook 19
About Excel File Types 21
Closing a Workbook 26
Lesson 1.3: Exploring your Workbook 27
Using Worksheets 27
The Active Cell 31
Selecting Cells 33
Exploring a Worksheet 36
Using Zoom 36
Lesson 1.4: Getting Help with Excel 40
Opening Help 40
Using the Help Screen 41
The Help Toolbar 44
Searching for Help 45
Online Help vs. Offline Help 46
Using the Table of Contents 47
Getting Help in a Dialog Box 50
Section 1: Review Questions 51
Section 2: The Excel Interface 53
Lesson 2.1: The Quick Access Toolbar and File Menu 54
The Default QAT Commands 54
Adding Commands 56
Removing Commands 57
Customizing the Toolbar 58
Using the File (Backstage) Menu 62
Lesson 2.2: The Home Tab 68
Understanding Tabs and Groups 68
Clipboard Commands 70
Font Commands 70
Alignment Commands 71
Number Commands 71
Styles Commands 71
Cells Commands 72
Editing Commands 72
Lesson 2.3: The Insert Tab 73
Tables Commands 73
Illustrations Commands 73
Charts Commands 74
Sparklines Commands 74
Filter Commands 74
Links Commands 75
Text Commands 75
Symbol Commands 75
Lesson 2.4: The Page Layout Tab 76
Themes Commands 76
Page Setup Commands 76
Scale to Fit Commands 77
Sheet Options Commands 77
Arrange Commands 77
Lesson 2.5: The Formulas Tab 79
The Functions Library 79
Defined Names Commands 79
Formula Auditing Commands 80
Calculation Commands 80
Lesson 2.6: The Data Tab 81
Get External Data Commands 81
Connections Commands 81
Sort and Filter Commands 82
Data Tools Commands 82
Outline Commands 82
Lesson 2.7: The Review Tab 84
Proofing Commands 84
Language Commands 84
Comments Commands 84
Changes Commands 85
Section 2: Review Questions 86
Section 3: Excel Basics 88
Lesson 3.1: Working with Excel 89
Columns, Rows, Cells, and Ranges 89
Creating Worksheet Labels 92
Entering and Deleting Data 92
Printing your Worksheet 97
Lesson 3.2: Basic Excel Features 98
AutoFill 98
AutoSum 101
AutoComplete 102
Working with Basic Formulae 103
Lesson 3.3: Moving your Data 106
Dragging and Dropping Cells 106
How to Cut, Copy, and Paste Cells 107
How to Cut, Copy, and Paste Multiple Cells 108
Using the Clipboard 109
Using Paste Special 110
Inserting and Deleting Cells, Rows, and Columns 114
Using Undo, Redo, and Repeat 117
Lesson 3.4: Custom Actions and Options Buttons 119
What are Custom Actions? 119
Setting Custom Action Options 121
The Error Option Button 122
The AutoFill Option Button 123
The Paste Option Button 124
Lesson 3.5: Editing Tools 127
Using AutoCorrect 127
Using Spell Check 128
Using Find and Replace 131
Adding Comments 133
Section 3: Review Questions 137
Section 4: Editing your Workbook 139
Lesson 4.1: Modifying Cells and Data 140
Changing the Size of Rows or Columns 140
Adjusting Cell Alignment 142
Rotating Text 144
Creating Custom Number and Date Formats 147
Lesson 4.2: Cell Formatting 151
Conditional Formatting 151
The Format Painter 160
Cell Merging and AutoFit 161
Find and Replace Formatting 162
Lesson 4.3: Enhancing a Worksheet’s Appearance 165
Adding Patterns and Colors 165
Adding Borders 169
Working with Styles 170
Working with Themes 175
Lesson 4.4: Working with Charts, Part 1 179
Creating a Chart 179
Styling Charts with the Design Tab 181
Modifying Charts with the Layout Tab 188
Additional Styling with the Format Tab 195
Manipulating a Chart 197
Lesson 4.5: Working with Charts, Part 2 200
Changing the Type of Chart 200
Changing the Source Data 202
Working with the Chart Axes and Data Series 205
Saving a Chart as a Template 209
Absolute and Relative Cell References 212
Section 4: Review Questions 214
Section 5: Printing and Viewing your Workbook 216
Lesson 5.1: Using the View Tab 217
Using Normal View 217
Using Full Screen View 218
Using Page Layout View 220
Page Break Preview 222
Lesson 5.2: Managing a Single Window 224
Creating a New Window 225
Hiding a Window 226
Unhiding a Window 227
Freezing a Pane 227
Splitting a Worksheet 229
Lesson 5.3: Managing Multiple Windows 231
Switching Between Open Workbooks 231
Arranging Workbooks 232
Comparing Workbooks Side by Side 234
Synchronous Scrolling and Resetting a Window 235
Saving a Workspace 236
Lesson 5.4: Printing your Workbook 238
Print Commands 238
Print Preview 239
Using Basic Print Options 240
Other Print Options 243
Setting Printer Properties 244
Section 5: Review Questions 246
Index 248
Introduction
Welcome to Velsoft’s courseware for Microsoft Office Excel 2010, Microsoft’s powerful and easy-to-use spreadsheet program. This new version of Excel incorporates some new features and connectivity options in efforts to make collaboration and production as easy as possible.
This Foundation level is intended to help all novice computers get up to speed quickly. This manual will also help more experienced users who have little to no experience with Excel 2007 and the ribbon interface.
This manual will cover different features of the interface, give a brief overview of all the tabs in the ribbon, show users how to print, cover some simple scenarios, and cover the basics of formatting. By the end of this manual, users should be comfortable with creating a new spreadsheet, working with basic formulae, making their spreadsheet look professional and presentable, and then saving and printing the spreadsheet.
In general, the manual is geared towards the novice computer user. If you are an instructor, gauge the comfort level your students have with using a computer. You may be able to skip over some easy components. Although we don’t get into the details of working with Excel until Section 3, the Step-by-Step exercises in Sections 1 and 2 require the student to use a number of basic interface commands. Be sure to give plenty of time and support during these Step-by-Steps, as many of the basic commands are not fully covered until Section 3.
This manual was created using Microsoft Office 2010 Professional Plus. Our test machine was a 64-bit computer that used Windows 7 Ultimate. If you are an instructor, you can use any version of Windows that is accessible to your students. Any feature specific to Windows 7 in this manual will be marked as such.
Occasionally, this manual may reference where certain keys are on the keyboard (such as Insert, Home, or Page Up). The directions are given based on a standard desktop keyboard that contains a separate number pad. Laptop keyboards may be different or have combined keys.
Prerequisites
This manual assumes the user understands the basics of using a Windows-based computer. Students should be comfortable using the keyboard, mouse, and Start menu. Understanding and experience with printing and using a Web browser is an asset, but not required. No previous experience with other versions of Excel is necessary.
Section 1: Getting Started
In this section you will learn:
▪ What Microsoft Office Excel 2010 is
▪ What’s new in Excel 2010
You will also learn how to:
▪ Open and interact with Excel
▪ Close Excel
▪ Create new workbooks
▪ Open and close existing workbooks
▪ Save workbooks
▪ Recognize the different Excel file types
▪ Recognize and work with the active cell
▪ Select multiple cells
▪ Explore worksheets and workbooks
▪ Zoom in and out of a worksheet
▪ Open and use the Help interface
▪ Recognize the difference between online and offline Help
▪ Get help while in a dialog box
Lesson 1.1: Starting Out
Microsoft Office Excel is a powerful and easy-to-use spreadsheet application. Nearly everyone who works with numbers has likely used Excel or some other spreadsheet application (such as Lotus 1-2-3) in one form or another. In this lesson, we will look at what’s new in the 2010 version, how to open and close the program, and outline some of the things you will see in the program.
If you are new to Excel and spreadsheets in general, the vast array of features and controls can seem quite daunting. However, once we cover the workings of a spreadsheet and how to deal with the basics, you will be well on your way to becoming an expert in Excel!
What is Microsoft Office Excel 2010?
Microsoft Office Excel 2010 is the fourteenth version of Microsoft’s spreadsheet program. A spreadsheet is essentially a large flexible grid that is used to hold information, usually numerical.
The spreadsheet is made up of rows and columns. The intersection of a row and column is called a cell:
[pic]
Using Excel, you can analyze large amounts of data, move sets of data around to get a different picture of your figures, and generate a number of different charts and diagrams to help summarize the data.
What’s New in Excel 2010?
Excel 2010’s interface does not make use of menus like you may be familiar with. Instead, Excel uses a tab system that groups like commands at the top. This interface, called the ribbon, was first used in some programs with the Office 2007 suite:
[pic]
There is a lot to see and do in Excel. Before we get down to the basics, let’s take a few moments to go over some new features:
|Slicer |Slicers are visual controls that allow for faster filtering of data. They “float” above PivotTable and |
| |CUBE objects, allowing you to quickly filter the data shown in that object. |
|Spark Lines |Spark Lines are mini-charts that fit into a single cell. They offer quick information about the data |
| |specified in a row or column and are very useful when spotting trends in large groups of data. For |
| |example, it is easy to see without even looking at the numbers that sales of Product3 are declining: |
| | |
| |[pic] |
|Easier collaboration via |Excel 2010 is capable of interacting with a SharePoint server. This allows for easy sharing and storage |
|SharePoint |of documents, team communication, and simultaneous editing of documents. |
|Backstage View |The File menu offers an all-in-one location to view and manage the file as a whole. You can preview a |
| |file for printing, change document properties, and collaborate with SharePoint users in Backstage view: |
| | |
| |[pic] |
|Integrated Screen Capture |Excel (and many other Office 2010 programs) has an integrated screen clipping tool. This tool lets you |
| |insert a picture from anything you can see on your screen including Web information, data from another |
| |program, or a technical diagram: |
| | |
| |[pic] |
|Equation Editor |Excel offers a robust equation editor, allowing you to create equations of any size. Excel is also |
| |compatible with the Windows 7 Math Input Panel. |
|OneDrive and Expanded File Types |Using the Backstage view, you can save a document to the Microsoft OneDrive service, save the document |
| |in PDF/XPS format, send the file in an e-mail, or change the file type without closing the file. |
|PowerPivot |PowerPivot is a data analysis tool for Excel which is capable of pulling information from anywhere in |
| |the world with minimum effort. It is specifically designed to connect with large database sources and |
| |give you access to large amounts of data in an easy-to-use interface. |
|Pivot Table and Pivot Chart |PivotTables and Pivot Charts are a way to examine a group of data in a number of different ways. As the |
|Improvements |names imply, you can “pivot” names and values of data around the X and Y axes of a chart and look at the|
| |data in different ways. This makes it easier to explore trends of one item against others. |
|Block Certain File Types |Administrators have the ability to prevent Excel users from opening/viewing certain file types. This |
| |helps the overall health and safety of a network. |
|Better Print Interface |In the Backstage view, the Print Preview dialog and Print dialog have been combined into a single |
| |location. This makes viewing and printing a file much easier. |
Opening Excel
You can open Excel in a number of different ways. If the Excel icon is visible on the desktop, double-click the icon to open the program:
[pic]
You can also click Start ( All Programs ( Microsoft Office ( Microsoft Excel 2010:
[pic]
If you are used to using the keyboard more than the mouse, press the Windows key and type “excel.” As you type, Windows will search for files/programs/locations with “excel” as part of the name. The actual Excel program will be first in the list (and already highlighted in blue), so press Enter to open the program:
[pic]
Interacting with Excel
When you open Excel, you will see something like the following image. This is the user interface. Let’s go over the basics of what you will see and how to interact with the interface. As we progress through the manual, we will cover these items in more detail:
[pic]
|1 |Quick Access Toolbar |As the name implies, the Quick Access Toolbar gives you quick access to frequently-used |
| | |commands. This toolbar is completely customizable and can be positioned above or below the |
| | |Ribbon commands. |
|2 |Tabs |Groups of like commands are organized under tab names. Click a tab to view the commands in |
| | |the Ribbon. If you are familiar with older versions of Excel, these tabs roughly correspond |
| | |to the menus used in the older interface. |
|3 |Ribbon Commands |Displays tab commands. If you click the different tabs, you will see the commands change. |
| | |Notice that some of the commands might be grayed out. This is because those commands are only|
| | |usable under certain situations. |
| | | |
| | |Excel 2010 also features contextual tabs. These are special tabs that only appear when you |
| | |are working with a specific object or group of information. For example, if you were to |
| | |select a bar graph based on your data, three contextual tabs would appear that allow you to |
| | |change the aspects of the graph: |
| | | |
| | |[pic] |
| | | |
| | |Once you switch back to working with something else, these tabs would disappear. |
| | | |
| | |You can also hover your mouse pointer above a command to see the command name. Many commands |
| | |also include a short description: |
| | | |
| | |[pic] |
|4 |Name Box |Every cell has a name in the format . The name of the currently selected cell, |
| | |called the active cell, is shown in the Name Box. In the image on page 9, the active cell is |
| | |A1. |
|5 |Formula Bar |The Formula Bar allows you to enter data in a cell. Data can be alphanumeric, pictures, |
| | |symbols, or (as the name suggests) formulae. |
|6 |Working Area |The data contained in the file will be shown here. |
|7 |Workbook Tabs |Every Excel file is properly referred to as a workbook. A workbook can contain one or more |
| | |worksheets, just like an accounting ledger can contain one or more pages. Click these tabs to|
| | |switch between the different worksheets. |
|8 |Status Bar |This bar is used to display information about the workbook. Any running calculations will be |
| | |shown here. There are also some zoom and view commands here which we will explore later. |
|9 |Scroll Bars |As you grow more accustomed to working with Excel, you will no doubt begin to work on larger |
| | |files. Not all of the information in a worksheet will fit on the screen, so use these scroll |
| | |bars to scroll horizontally/vertically through the data. |
To move the active cell, use the arrow keys on your keyboard or click your mouse somewhere in the working area of the screen. The active cell will be referenced in the Name Box, and the row/column headers will highlight showing the active cell’s location.
For example, if you click cell D4, the Name Box and column/row headers will change to show the position of the active cell:
[pic]
Closing Excel
If you look in the upper right-hand corner of the Excel window, you will see two close buttons:
[pic]
The top button is used to close the program. The bottom button is used to close the currently open file, but leave Excel open. (We will explore more about file and window management later in this manual.)
To close Excel, click the top close button:
[pic]
You can right-click the Excel icon and click Close window in the Jump List:
[pic]
You can also close Excel by clicking File ( Exit:
[pic]
No matter which method you use, if you haven’t already done so, you will be asked to save any changes you have made to the file. We will cover saving files in the next lesson.
Lesson 1.2: About Workbooks
In the previous lesson, we learned how to open Excel and how to close it. We also received a brief introduction to Excel spreadsheets, cells, and the basics of the user interface.
Let’s move on and talk a bit more about workbooks. In the last lesson, we learned that a workbook is synonymous with an Excel file. The workbook can contain one or more worksheets; a large grid of cells that contains data. Many people use the word “spreadsheet” to describe either a worksheet or a workbook, but we will stick with the proper names in order to differentiate between the two.
Creating a New Workbook
If you open Excel using the methods described earlier (shortcut, Start Menu, etc.), a new blank workbook with three worksheets will appear:
[pic]
As you can see, this new file is given the default name of “Book1” and the three worksheets are highlighted at the bottom.
You can also create a new workbook while Excel is already open. Click File ( New. The “Blank workbook” template will already be selected, so click Create:
[pic]
This will create a new file named Book2, Book3, etc. A new icon will be added to the Windows taskbar:
[pic]
You can also press and hold Ctrl and then press N to create a new workbook. (This keyboard shortcut is denoted as Ctrl + N.)
Opening a Workbook
To open an existing workbook when Excel is not open, just double-click the file name:
[pic]
To open an existing workbook while Excel is open, click File ( Open:
[pic]
You will then be required to browse your computer to find the document. Select it and click Open:
[pic]
The file will open.
As you work with more files, Excel remembers the names and locations of those files. If you click File ( Recent, you will see a list of recently-used files and recent locations. Click any file to open it, or click any location to open its contents in the Open dialog:
[pic]
As you work with more and more files/locations, only the most recently-used items will remain in this list. If you want certain files/locations to always stay in the list, you can “pin” them by clicking the pushpin icon. Click the pushpin icon to “pin” the item; click it again to “unpin” the item:
[pic]
Note that Excel does not keep track of files that you move manually. For example, if you cut the SalesReport1 file from the Desktop and pasted it to the Documents folder, Excel would not record this change, even if the file was pinned.
Saving a Workbook
When working with files in Excel, there will be two save scenarios. You will either save a new file that was made from scratch or save changes to an existing file. There are two different save commands in Excel: Save and Save As. Consider the following chart which outlines the actions of each command on either a new file or an existing file:
| |Save |Save As |
|New File |You will be prompted to give the file a name and choose|You will be prompted to give the file a name and choose a|
| |a save location. You can also specify a file type. |save location. You can also specify a file type. |
|Existing File |Any changes you made will be applied to the existing |You have the option to give the file a new name and/or a |
| |file in its current location. |new save location. You can also specify a new file type. |
| | |If you do change something, the original existing file |
| | |will not be changed. |
Both save commands are found in the File menu:
[pic]
The Save command is also found in the Quick Access Toolbar:
[pic]
As shown in the chart on the previous page, the Save and Save As commands work the same for a new file. When the Save As dialog appears, give the file a name and choose a save location, and then click Save.
[pic]
If you are working with an existing file and click Save, any changes will simply be saved. If you click the Save As command with an existing file, you will see the Save As dialog appear, and allow you to save the file under a new name and/or in a different location.
For example, imagine you are working on a workbook named “budget.” You want to e-mail a copy of the budget to your supervisor, but give it a more meaningful name. Therefore, you would click File ( Save As and name the file something like “Q1 Budget Report J Smith.” Excel creates a new workbook for you to send, and each workbook can be worked on independently of each other.
About Excel File Types
Excel 2010 uses a file format known as Microsoft Excel XML format. XML (extensible markup language) is a very flexible type of computer language. XML is similar in nature to HTML (Hyper Text Markup Language, the language used to build Web pages), but is designed more for the communication of information rather than the presentation of information. XML was incorporated into the Office 2007 file formatting system to facilitate communication of data between Microsoft Office programs and other applications.
Despite this file format change, Excel 2010 is capable of using files creates from Excel 97 right on up to 2010, and is capable of using other file types as well, including plain text, documents, and data output files.
Whenever you save a new file in Excel, it is automatically saved in the Excel 2010 file format. One additional use of the Save As command is the ability to choose a file type in the dialog. This can be helpful if you are worried about compatibility with earlier versions of Microsoft Office. As you can see, Excel is capable of saving files in many formats!
[pic]
In most computer systems, a file is normally identified by a file name and a three or four letter file type extension. “Term paper.docx,” for example, is a Microsoft Word 2010 document named term paper. The four letter “.docx” extension signifies that this file is a Microsoft Word document.
The following table summarizes the file types that can be saved with Excel 2010:
|Excel Workbook |[pic] |Default format for Excel 2010 and Excel 2007. Add new authors or tag the file. You can also save a |
|(.xlsx) | |thumbnail, which will let you look at the beginning of the document if you use the Extra Large or |
| | |Large icon view in Windows Explorer: |
| | | |
| | |[pic] |
| | | |
| | |Although Excel 2007 and 2010 share the same file extension, there are some instances where items |
| | |created in Excel 2010 may not be compatible with Excel 2007. |
|Excel Macro-Enabled |[pic] |Excel workbooks with macros. Macros are short, specific pieces of code that allow the document to |
|Workbook | |perform some functionality, such as accessing data from a database file. |
|(.xlsm) | | |
| | |(Extra options same as above.) |
|Excel Binary Workbook |[pic] |This option is the same as the default Excel Workbook option, only the file is saved in binary form |
|(.xlsb) | |instead of XML form. This makes the file more efficient to open and use, though the Binary Workbook |
| | |is intended for very large files with columns and rows numbering in the tens of thousands. |
| | | |
| | |(Extra options same as above.) |
|Excel 97-2003 Workbook |[pic] |The format for Excel 97-2003. |
|(.xls) | | |
| | |(Extra options same as above.) |
|XML Data |[pic] |Saves the file in raw XML form. In order to use this format, the workbook must contain XML mappings.|
|(.xml) | | |
| | |(Extra options same as above except no thumbnail.) |
|Single File Web Page |[pic] |All information in the workbook is saved in a single Web page archive. You have the ability to save |
|(.mht) | |the entire workbook or just the selected data in a single worksheet. You can also give the Web page |
| | |a title; otherwise the file name will be the title: |
| | | |
| | |[pic] |
|Web Page |[pic] |Saves the workbook as an HTML file, along with a folder that contains any supporting files |
|(.htm) |[pic] |associated with the workbook like pictures or graphs. |
| | | |
| | |(Extra options same as above.) |
|Excel Template |[pic] |Template for Excel 2007 and 2010. A template is a pre-formatted file designed to be used over and |
|(.xltx) | |over, meaning you don’t have to keep re-creating the same formatting and file structure each time |
| | |you make a certain file. |
| | | |
| | |[pic] |
|Excel Macro-Enabled |[pic] |Template for Excel 2007 and 2010 that contains macros. |
|Template | | |
|(.xltm) | |(Extra options same as above.) |
|Excel 97-2003 Template |[pic] |Template for Excel 97-2003. |
|(.xlt) | | |
| | |(Extra options same as above.) |
|Text (Tab delimited) |[pic] |This option is only capable of saving one worksheet at a time. Text is entered with one row equaling|
|(.txt) | |one line of text. The text is also delimited (separated) by tab spaces. |
| | | |
| | |Delimited text is capable of being read by many different programs on nearly any computer platform. |
| | |This characteristic makes the data very portable, meaning the raw data can be used just about |
| | |anywhere. |
| | | |
| | |(Extra options same as above.) |
|Unicode Text |[pic] |Computers basically deal directly with numbers. The problem with so many different computer |
|(.txt) | |platforms is that there may be two completely different ways of saying the same thing between two |
| | |different computer systems. “Unicode provides a unique number for every character, no matter what |
| | |the platform, no matter what the program, no matter what the language.” () |
| | | |
| | |(Extra options same as above.) |
|XML Spreadsheet 2003 |[pic] |Saves the file in an XML format which is compatible with Excel 2003. |
|(.xml) | | |
| | |(Extra options same as above.) |
|Microsoft Excel 5.0/95 |[pic] |Contains XML information about the Word 2010 or Word 2007 document. |
|Workbook | | |
|(.xls) | |(Extra options same as above.) |
|CSV (Comma delimited) |[pic] |Stands for Comma Separated Values. This style of delimited file uses commas instead of tab spaces. |
|(.csv) | |Like tab delimited files, it is only capable of saving the data on a single worksheet. |
| | | |
| | |(Extra options same as above.) |
|Formatted Text (Space | |This format is another type of plain text usable in other computing environments. Only usable with a|
|delimited) | |single worksheet. |
|(.prn) | | |
| | |(Extra options same as above.) |
|Text (Macintosh and | |Plain-text file for use in Macintosh or MS-DOS environments. Only usable with a single worksheet. |
|MS-DOS) | | |
|(.txt) | |(Extra options same as above.) |
|CSV (Macintosh and MS-DOS)| |CSV file for use in Macintosh or MS-DOS environments. Only usable with a single worksheet. |
|(.csv) | | |
| | |(Extra options same as above.) |
|DIF (Data Interchange | |This file type is used to export single worksheets between different spreadsheet programs. Only |
|Format) | |usable with a single worksheet. |
|(.dif) | | |
| | |(Extra options same as above.) |
|SYLK (Symbolic Link) |[pic] |This file type is also used to exchange data between various applications including spreadsheet |
|(.slk) | |programs. Only usable with a single worksheet. |
| | | |
| | |(Extra options same as above.) |
|Excel Add-in |[pic] |This type of file would be used to add extra functionality and tools, mainly via the use of macros. |
|(.xlam) | |Used with Excel 2007 and 2010. |
| | | |
| | |(Extra options same as above.) |
|Excel 97-2003 Add-in |[pic] |This type of file is also used to add extra functionality and tools, mainly via the use of macros. |
|(.xla) | |Used with Excel 97-2003. |
| | | |
| | |(Extra options same as above.) |
|PDF |[pic] |Stands for Portable Document Format. PDF files work by creating a snapshot of a file, just as if you|
|(.pdf) | |printed a file and then scanned it to send an electronic copy. PDF files are widely used for things |
| | |like instruction manuals, government forms, etc., and are usable with nearly every computing |
| | |platform. |
| | | |
| | |Extra options include the ability to change the file detail and to open the file after saving to |
| | |make sure everything looks OK. Advanced options available via Options button. |
|XPS Document |[pic] |Stands for XML Paper Specification. XPS documents are Microsoft’s answer to PDF documents. |
|(.xps) | | |
| | |(Extra options same as above.) |
|OpenDocument Spreadsheet |[pic] | is an open-source productivity suite designed to be a free alternative to the |
|(.ods) | |Microsoft Office suite of applications (and other similar suites). Excel 2010 is capable of creating|
| | |spreadsheet files that are compatible with the spreadsheet application. Extra options|
| | |include the ability to change the author and add file tags. |
Whatever you decide to use for a file format, remember to give your files meaningful names and be careful where you save the file. If you choose to save a file as a template, Excel will automatically save the template to a default Microsoft folder on your computer unless you specifically tell Excel to save it where you want.
Closing a Workbook
We know that there are two close buttons at the top of the Excel window and that the topmost is used to close Excel completely:
[pic]
If you just want to close a workbook but leave Excel open (particularly if you are working on many workbooks at once), click the bottom x. Unless you have already done so, you will be asked to save any changes made since you opened the file:
[pic]
Lesson 1.3: Exploring your Workbook
Now that we are familiar with the basic concepts of workbooks, worksheets, cells, and file formats, it is time to learn how to explore and navigate your workbooks in greater detail.
In this lesson, you will learn how to switch between worksheets in a workbook, how to select cells in a worksheet, how to move around in a worksheet, how to use the active cell, and how to use Excel’s zoom feature.
Using Worksheets
A workbook is a collection of one or more worksheets. By default, new files created in Excel have three worksheet tabs:
[pic]
You can easily switch between worksheets by clicking the worksheet tab you want to view. The name of the worksheet that you are presently working with will be in bold type. In the image shown above, Sheet1 is the worksheet that is currently being used.
You can also use the worksheet navigation buttons just to the left of the worksheet tabs to switch between worksheets. These commands are useful if you have more worksheets than space on your screen:
[pic]
From left to right, these buttons will go to the first worksheet, go to the previous worksheet tab, go to the next worksheet tab, and go to the last worksheet.
Right-click any of the four commands to jump to a specific worksheet:
[pic]
To add more worksheets to your workbook, click the new tab command:
[pic]
A new worksheet will be added to the list of tabs.
If you right-click on any worksheet tab, you will see a menu with several worksheet management options:
[pic]
Let’s quickly go over these options:
|Insert |Inserts a new worksheet; the same as clicking Insert Worksheet command. |
|Delete |Deletes the current worksheet. You will be asked to confirm your choice: |
| | |
| |[pic] |
|Rename |Renames the current worksheet. |
|Move or Copy |This command lets you move the current worksheet to a currently open workbook or a new workbook. You can |
| |also copy the worksheet and paste it somewhere within the same workbook: |
| | |
| |[pic] |
|View Code |If any macros are assigned to this worksheet, click this command to view and edit the code in Microsoft |
| |Visual Basic for Applications. Macro code is beyond the scope of this manual. |
|Protect Sheet |If a workbook is going to be distributed to others, you might want to lock certain portions of the data |
| |to prevent accidental/intentional disruption of your work. You can also assign a password to allow others|
| |to edit your work: |
| | |
| |[pic] |
|Tab Color |You can color tabs in your workbook to help differentiate between the data that might be contained |
| |within: |
| | |
| |[pic] |
|Hide/Unhide |Right-click a tab and click Hide to remove it from view. The data is still available, just hidden from |
| |view. To show hidden worksheets, right-click any tab and click Show. A dialog will appear and allow you |
| |to choose which worksheet(s) to make visible again. |
|Select All Sheets |This will select all sheets and allow you to perform actions on all sheets at once. |
Using these options, you can clearly label each worksheet:
[pic]
The Active Cell
The active cell is a name given to whichever cell you are currently working with. When you click a cell in a worksheet, it becomes enhanced with a thicker border. As you can see in the image below, the row and column headers are shaded orange, and the cell reference is shown in the Name Box. In this image, cell D5 (the one with the thick border) is the active cell:
[pic]
If you type a cell reference into the Name Box and then press Enter, that cell will become highlighted as the active cell. For example, try typing “aa29” into the Name Box and then press Enter (capital letters for the column headings are not required):
[pic]
As you can see, the column heading AA (the 27th column) comes after Z.
You can enter text or a number directly into the active cell; simply click somewhere and type:
[pic]
As you can see, the text appears to be written over cells D4 and E4. However, those cells are still technically empty. We will explore cell sizes and boundaries later in this manual.
If you use some of the text formatting commands on the Home tab (such as bold, italics, or underline), the formatting will be applied to the active cell. If there is already data in the active cell, the formatting option you choose will be applied to this data. Here we have applied bold and italic text effects to cells B2 and B3 respectively, and are about to apply Underline formatting to B4:
[pic]
You can enter text or numerical data into the active cell by clicking inside the Formula Bar and typing. Notice again how all that we typed seems to be flowing behind cell A2, when in fact all the text is contained within B2. We will explore more about cell sizes later.
[pic]
Be careful when typing information into the active cell:
▪ If you click a cell that already contains information and start to type, you will erase all the data that was in that cell. Whatever you type will overwrite the old information.
▪ To edit or append data in a cell that already contains information, click the cell to make it active, and then make your changes in the Formula Bar.
Selecting Cells
Selecting a single cell is easy: just click it. That cell will become the active cell. You can also select groups of cells or multiple individual cells using the Shift and Ctrl keys, as well as the column/row headers.
To select a group of cells, place your mouse pointer over a cell and then click and hold the left mouse button. Drag the mouse in any direction to select rows, columns, or a combination of each. Notice that as you drag your mouse, the Name Box will show you how many rows/columns you are selecting:
[pic]
Here, the cells from one row and five columns are being selected. When you release the mouse button, all the cells will be selected but only the first cell in the click and drag operation will be marked as the active cell:
[pic]
Here, 10 rows and 5 columns worth of cells are being selected:
[pic]
If you click a cell, press and hold Shift, and then click some other cell, the cells in between will become selected, based on where you clicked. For example, if you clicked A1 to make it the active cell, held Shift, and then clicked C6, the following cells will be selected:
[pic]
To select multiple individual cells, select the first cell, press and hold Ctrl, and then click other cells. You can also click and drag to select multiple cells while Ctrl is being held down:
[pic]
To select an entire row/column of cells, move your mouse over a row/column header. The mouse pointer will turn into an arrow. Then click the header to select that row/column:
[pic]
Click and drag multiple row or column headings to select multiple rows/columns:
[pic]
Exploring a Worksheet
Now that we are familiar with the concept of worksheets, the active cell, and selecting multiple cells, let’s learn some alternate ways of moving around a worksheet that involve more than just the mouse and scroll bars.
|Arrow keys |Press Up, Down, Left, or Right to move the active cell selection box in that direction. |
|Page Up & |Press Page Up to move the active cell up one screen’s worth of cells. Press Page Down to move the active |
|Page Down |cell down the same amount. |
|Ctrl + arrow keys |Press Ctrl + Up, Down, Left, or Right to move to the respective outside edge of the worksheet. (Be warned,|
| |Excel worksheets are very large!) |
|Shift + arrow keys |Press and hold Shift while pressing Up, Down, Left, or Right to select multiple adjacent cells in that |
| |direction. |
|Ctrl + Home & End |Ctrl + Home will take you to cell A1, while Ctrl + End will take you to the bottom right-most cell that |
| |contains any data (i.e. the end of whatever data is in the worksheet). |
Using Zoom
A single Excel worksheet can contains more than 1000000 rows and 16000 columns, totaling more than 16 billion cells per worksheet. While it is unlikely you will ever deal with spreadsheets that are that large, you will very likely deal with spreadsheets that are larger than your screen. To help you view your data, you can use the Zoom feature to change the viewing scale of a worksheet. By default, Excel opens workbooks at 100% zoom. You can see this number in the status bar:
[pic]
Consider the following spreadsheet, which contains quite a lot of data. At 100% Zoom, we won’t be able to see all the data:
[pic]
You could use the scroll bars to view all of the data, or you could use the zoom slider in the lower right corner of the screen. Drag the slider left toward the negative (-) command to decrease the zoom level, or right toward the (+) command to increase the zoom level.
You can also click the (+) and (-) buttons to incrementally zoom in or out, 10% at a time:
[pic]
Here is the same set of data at 50% zoom, which is small enough for us to see everything:
[pic]
If you click the current zoom amount (for example, 50%), you can choose between common zoom levels or enter your own:
[pic]
Choose one of the radio buttons or enter a custom amount, and then click OK.
Lesson 1.4: Getting Help with Excel
Before we get into the nuts and bolts of working with Excel, it is probably a good idea to learn about Excel’s help features. Almost anyone who works with a program with many functions and options will find themselves needing help at some point. You may know exactly what you want to do, but not how to do it.
In this lesson, you will learn how to get help by using the Help file. You will also learn about online and offline Help. Knowing how to use these features can help you access the information and instructions you need to accomplish your Excel goals.
Opening Help
To open the Help file, click the blue question mark icon at the top of the window or press F1 on your keyboard:
[pic]
Using the Help Screen
The Help file will open in a separate window:
[pic]
Note that you may see a list of suggestions appear at the top of the Help file depending on what you were doing when the Help file was opened. For example, if you opened the Help file while viewing the File menu, you would see a number of items referring to the management of Excel files, things new users to Excel 2010 should know, etc.
In the top right-hand corner, you will see the Minimize, Maximize/Restore, and Close buttons. Also at the top is the title bar, toolbar, and search bar:
[pic]
The main part of the window shows the help content, which is laid out like a Web page. There are links at the top to the different sections of the Microsoft Office Support portal, and links to the main Help topics:
[pic]
Browse through the Help content by clicking the blue text. This text is linked to relevant information described by the hyperlink text. For example, the “Getting Started with Excel 2010” link will take you to a page that lists subcategories for the topic. Continue clicking the links to follow the information path. Notice too that at the very top of the Help file you will see a “breadcrumb trail” that shows which location in the Help file you are viewing:
[pic]
At the bottom there is a status bar which shows you where Help is searching. As you can see in the picture below, the Help file is Connected to . This means that the Help file is using your Internet connection to get the latest help right from Microsoft. We will explore the difference between online and offline help in a moment.
[pic]
The Help Toolbar
The Help toolbar contains commands similar to those you would find in a Web browser. In fact, the Help window behaves very much like a Web browser that only searches for information relevant to Excel.
|Back |[pic] |Move back one step at a time through the help topics you have previously read. |
|Forward |[pic] |If you click the Back button, the Forward button will become active. This lets you step forward one step|
| | |at a time through the topics you have visited. |
|Stop |[pic] |If you are searching for a help topic and the Help file is taking a long time to show results, you can |
| | |click the Stop button to stop Word from searching. You might then revise your search or search for |
| | |something else. |
|Refresh |[pic] |Use this button to reload the information on the current page. |
|Home |[pic] |Click this button to return to the main list of information you saw when you opened the Help file. |
|Print |[pic] |Prints the current topic. |
|Text Size |[pic] |Use this to make the text in the help file larger or smaller: |
| | | |
| | |[pic] [pic] |
|Table of Contents |[pic] |Use this button to browse the entire alphabetical list of Help topics. We will explore the table of |
| | |contents in a moment. |
|Keep on Top |[pic] |By default, the Help window will always display itself on top of the Excel window. Even if you are doing|
| | |something in Excel, the Help window will remain on top until you close it or click this icon to make the|
| | |Help window behave like any other window. |
|Toolbar Options |[pic] |Use this command to add or remove buttons from this toolbar. By default, all of the commands we have |
| | |listed are shown. |
Searching for Help
Searching for help is easy – just type something into the search bar you are looking for and press Enter. After a moment, any results Excel thinks are relevant will appear in a list. Click one of the topics in that list to view information on the topic.
For example, if you search for “formula” (while connected to ) you should get search results that look something like this:
[pic]
Click the pull-down arrow beside the Search command for more search options:
[pic]
Online Help vs. Offline Help
There are two versions of the Help file: online and offline.
Online Help requires an active Internet connection. When available, all searches for Help are directed to , the online portal for all Office programs. The Help topics retrieved via Online help are considered the most up-to-date should there be a program change to any of the Office programs.
Offline Help refers to help content on your computer (called “local” content). It may not be completely up to date, but it will always be available.
To switch between the two modes, click the connection icon in the status bar and make your choice:
[pic]
Using the Table of Contents
If you would prefer to navigate through the Help file using a more traditional method, click the Table of Contents (TOC) button ([pic]) on the Help toolbar. Your Help screen will then look like this:
[pic]
From here, you can do one of two things.
One thing you can do is to click items in the TOC to see the articles on the right side of the window:
[pic]
When browsing the Help file, click items on the right side to see their relation in the tree structure of the TOC:
[pic]
To navigate through the table itself, simply click on topics to expand them, and then click the link to view the topic.
[pic]
Here is what the different icons mean.
|Closed Book |[pic] |Click the book to expand the list of topics. |
|Open Book |[pic] |Click the book to collapse the list of topics. |
|Help Topic |[pic] |Click the title to view the help topic. |
Getting Help in a Dialog Box
Some of Excel’s features are accessed via dialog boxes, which we haven’t really discussed yet. However, you should know that in some dialog boxes, you will see a help icon in the top left hand corner. Click this question mark to see specific help on that topic.
[pic]
Section 1: Review Questions
Excel 2010 is designed for:
A. The organization and analysis of data
B. The formatting, presentation, and charting of data
C. The calculation of complex functions and formulas
D. All of the above
An Excel workbook is (usually) composed of:
A. One or more worksheets
B. Cells
C. Rows and columns
D. All of the above
To switch between worksheets you should:
A. Click the Switch Worksheets button on the Home tab
B. Use the worksheet tabs
C. Close one worksheet and open another
D. A and C only
Which of the following statements is false?
A. To enter data directly into a cell, it must be active
B. You can edit cell contents in the formula bar
C. A valid cell reference is 34X
D. A valid cell reference is AA3000
How many rows are in a typical Excel worksheet?
A. Over a million
B. Less than one thousand
C. Less than one hundred thousand
D. As many as you want
There are essentially two varieties of help in Excel. What are they?
A. Offline and Research
B. Offline and Online
C. Research and Reference
D. Onscreen and hard copy
Which of the following is a valid way to open Excel?
A. Double click the Excel desktop icon
B. Find Microsoft Office Excel 2010 in the Start menu
C. Type “excel” in the Search box and press Enter
D. All of the above
In Excel, rows are indexed by:
A. Numbers
B. Letters
C. Icons
D. Colors
In Excel, columns are indexed by:
A. Numbers
B. Letters
C. Icons
D. Colors
Excel is commonly referred to as a:
A. Spreadsheet program
B. Word processor
C. Integrated development environment
D. Graphical user interface
Section 2: The Excel Interface
In this section you will learn how to:
▪ Work with the Quick Access Toolbar
▪ Add and remove buttons on the Quick Access Toolbar
▪ Use the File (Backstage) menu
You will also learn about the:
▪ Home tab
▪ Insert tab
▪ Page Layout tab
▪ Formulas tab
▪ Data tab
▪ Review Tab
Lesson 2.1: The Quick Access Toolbar and File Menu
Excel’s user interface does not rely on multiple toolbars and menus as past versions have. Instead, it offers a cleaner, more intuitive, tab-based layout. An important component of the interface is the Quick Access Toolbar (or the QAT for short). The Quick Access Toolbar is fully customizable and gives you access to the features that you rely on the most.
In this lesson, you will learn all about the QAT. You will learn about the default buttons, how to add and remove buttons, how to change the icon size, and how to customize the toolbar.
We will also explore the File menu, which offers the Backstage view. With this view, you can manage settings that control the file itself, not the components within the file.
The Default QAT Commands
The Quick Access Toolbar is located in the upper left of the Excel screen, just to the right of the Excel icon.
[pic]
The QAT has three commands. From left to right, they are Save, Undo, and Redo.
|Save |If you click this command while editing a previously saved file, Excel will save your changes, just as if you|
| |were to click File ( Save. |
| | |
| |If you opened a new file, performed some actions, and then clicked Save, you would be prompted to give the |
| |file a name and save location. |
|Undo |If you did something you didn’t mean to, such as added the wrong formatting, formula, or deleted something, |
| |use the Undo command to revert one change at a time. |
| | |
| |Click the pull-down arrow and select an action to undo up to and including that change. For example, consider|
| |these actions. The most recent action is at the top of the list: |
| | |
| |[pic] |
| | |
| |If you clicked “Typing ‘300’ in A3,” then the two formatting commands would be undone and the typing in A3 |
| |would be erased. |
|Redo |Use the Redo command to “undo an Undo.” If you undid an action, use Redo to redo that action. As with the |
| |Undo command, you can click the pull-down arrow to redo many things at once: |
| | |
| |[pic] |
Adding Commands
If you want to customize the QAT, chances are you will add commands that are readily available on the ribbon, such as number formatting or text/data filtering. To add a command to the QAT, right-click the command and then click Add to Quick Access Toolbar.
For example, if you wanted to add currency formatting to the QAT, right-click the command and click Add to Quick Access Toolbar:
[pic]
The formatting command will be added to the right-hand side of the QAT:
[pic]
Many commonly-used commands can be easily added to the QAT by clicking the pull-down arrow to the right of the QAT. Select an option by clicking it; this will add a check mark beside that command and place it in the ribbon. Notice how Save, Undo, and Redo are already checked:
[pic]
Removing Commands
To remove commands from the QAT, right-click any command and click Remove from Quick Access Toolbar:
[pic]
You can also remove commonly-used commands by clicking the pull-down arrow beside the QAT. Click any checked item; this will remove the checkmark and the command.
Customizing the Toolbar
The QAT is 100% customizable. You can reposition it, add any command you want, or remove all commands.
To move the QAT, click the pull-down arrow to the right of the toolbar and click Show Below the Ribbon:
[pic]
This will add the QAT between the ribbon and the Name/Formula bars:
[pic]
To move the toolbar back to the top, click the pull-down arrow again and click Show Above the Ribbon. We recommend you keep the QAT at the top because it already fits into the title bar for Excel. Putting it below the ribbon actually takes up more space on your screen.
To customize the QAT with more commands, click the pull-down arrow beside the toolbar and click More Commands:
[pic]
You can also click File ( Options. When the Excel Options dialog opens, click the Quick Access Toolbar link on the left:
[pic]
The list of Popular Commands contains mostly those commands that are already found in the ribbon. You can change which group of commands to see by clicking the “Choose commands from” combo box and selecting an option:
[pic]
To add commands, select a command from the list on the left and click Add. The command will be added to the bottom of the list on the right. Note that you can also add to help organize whatever commands you will add to the QAT.
Once commands and/or separators are added, you can organize them using the small up and down buttons on the far right. Items at the top of the list are shown on the left of the QAT.
For example, here we are adding the Create Chart command:
[pic]
Once the command is added, we will move it up two positions using the Move Up button:
[pic]
Click OK to commit the changes. The QAT will be updated:
[pic]
As you become more experienced with Excel, you may find that you frequently use the same group of commands for a certain file, but not other files. Excel lets you save certain QAT customizations for use with a specific file. To do this, open the file you use frequently. Click File ( Options ( Quick Access Toolbar. Make your customizations to the QAT and then select the file name from the Customize Quick Access Toolbar:
[pic]
Click OK to confirm the change. Now each time you open this file, the QAT customizations will be applied.
To reset the QAT back to its default, click File ( Options ( Quick Access Toolbar ( Reset only Quick Access Toolbar:
[pic]
Using the File (Backstage) Menu
The File menu is a new addition to Excel 2010. This menu lets you modify your workbook as a single entity, rather than making changes to the data contained within the workbook. You can preview your file, print it, make changes to file properties, share the file with others, and more, all using the File menu.
Click the File tab. This will open the Backstage view:
[pic]
Let’s go over the main categories available on the left. As we progress further through this manual, we will expand more on some of these options:
|File Management |These are options we have already explored: |
| |Save will save changes to an open file. |
| |Save As lets you save an existing file under a new name, location, and/or file format. |
| |Open will open a worksheet. |
| |Close will close the current worksheet without closing Excel. |
|Info |Displays vital statistics about the current file. You are also able to modify read/write permissions, check |
| |for compatibility issues with other programs, and manage versions of this file. The document properties are |
| |listed on the far right. |
|Recent |Lists recently-opened files; click a workbook to open it and/or pin recent documents to this list. Note the |
| |checkbox at the very bottom of the list: |
| | |
| |[pic] |
| | |
| |If you click this checkbox, the first few workbooks in the Recent list (default 4) will be shown under the |
| |File Management area: |
| | |
| |[pic] |
|New |Create a new blank workbook or choose from a number of installed templates. More templates are available via |
| |download from . To create a new blank file or a file from a template, select an option and click |
| |Create: |
| | |
| |[pic] |
|Print |Excel 2010 features an all-in-one printing location. Select your printing options on the left, and see what |
| |the printed page will look like on the right: |
| | |
| |[pic] |
|Save & Send |Collaboration was a major design consideration when Office 2010 was in production. Although many of the |
| |sharing options here are beyond the scope of this manual, the major ones are explained below. We will discuss|
| |some of them later: |
| |Send Using E-mail: Send the workbook via e-mail or send a link, provided the recipient is part of the same |
| |network. |
| |Save to Web: Upload the file to Microsoft OneDrive, an online file-sharing service. |
| |Save to SharePoint: A combination of a central repository Web site and other collaboration tools. |
| |Publish to Excel Services: Make all or part of this workbook available to view/edit through a Web browser or |
| |SharePoint. |
| |Change File Type: Same as Save As command. |
| |Create PDF/XPS Document: These two file formats are widely used and easy to e-mail. |
|Help |Here you can open the Help file, learn about new Excel 2010 features, view Excel options (same as next item),|
| |check for program updates, and view program information: |
| | |
| |[pic] |
|Options |Opens the Excel Options dialog. Here you can view and modify the more specific and background options in |
| |Excel. Choose a category on the left and then modify options on the right: |
| | |
| |[pic] |
|Exit |Closes the program. You will be asked to save any changes to opened documents if you haven’t already done so.|
Lesson 2.2: The Home Tab
In this lesson, and the rest of the lessons in this section, we will cover the command groupings in each tab. Before we go into the details of the Home tab, let’s take a quick look at the structure of tabs.
Understanding Tabs and Groups
The current tab is outlined with a border to differentiate it from the others. The actual commands are found in the ribbon, and split into groups. Each group is identified with a name. Here, we can see that the Home tab contains seven groups, with the names outlined in red:
[pic]
Some groups feature an option button beside the group name. Click this button to open a dialog with more specific controls relating to this group and other commands in the tab:
[pic]
The Home tab contains the most commonly-used commands.
Clipboard Commands
The clipboard group offers commands for Cut, Copy, and Paste, three commands that you should be familiar with. Cut (Ctrl + X) will remove material from highlighted cells. Copy (Ctrl + C) will record the information from highlight cells. Paste (Ctrl + V) will place the Cut or Copied information to a new location.
The Format Painter works like the Copy command, but it only copies the formatting applied to data in a cell, not the cell data. Copy formatting from a single cell and “paint” the other cells with the same formatting.
[pic]
Click the option button to open the Office Clipboard. The clipboard saves up to 24 cut/copy operations and allows you to pick and choose which value/data you want to paste. We will cover the clipboard later in this manual.
Font Commands
The Font group lets you apply formatting to your worksheet. Choose the font, font size, text formatting, cell outlines, highlighting, and cell colors here.
[pic]
Click the option button to open the Format Cells dialog. This dialog offers more specific formatting for fonts and cells including number formatting, alignment within the cell, and borders.
Alignment Commands
The Alignment group lets you position data within a cell. You can also choose justification for the cell data, wrap the text to fit within the cell width, and merge two or more adjacent cells together.
[pic]
Click the option button to open the Alignment tab of the Format Cells dialog, which is also available by clicking the option button in the Font group.
Number Commands
Since most worksheets deal with numbers, Excel offers a range of number formatting commands that let you apply specific number formatting, add currency, percentage, and command formatting, and increase/decrease the number of decimal places.
[pic]
Click the option button to open the Number tab of the Format Cells dialog, which is also available by clicking the option button in the Font group.
Styles Commands
You can increase the readability of worksheets by formatting the cells to reflect their value or draw attention to information. Use the conditional formatting commands to change the way data looks based on its value, format a group of cells to look like a stand-alone table, and apply various coloring to indicate contained values.
[pic]
Cells Commands
The Cells group lets you modify cells within a worksheet. Insert/delete individual cells or rows/columns. You can also format cells to adjust their height and width, format the worksheet tabs, and lock down certain cells to make them un-editable.
[pic]
Editing Commands
The Editing group provides commands to work with bulk amounts of data. You can add various AutoSum commands to find the sum, average, maximum/minimum value, etc. The Fill command lets you continue a pattern of data in a certain direction, just like clicking and dragging the small black square of the active cell to continue filling data in a direction. You can also clear a cell or groups of cells, sort and filter a group of data, and search the worksheet/workbook for a certain value.
[pic]
Lesson 2.3: The Insert Tab
Next, let’s explore the Insert tab. The commands here are used for inserting a variety of different objects into your spreadsheets including charts, illustrations, hyperlinks, and symbols.
Tables Commands
The Tables group lets you add a PivotTable or an ordinary table. PivotTables are used to compare trends in data. They work by allowing you to literally change which axis is used to display a group of data (i.e. “pivot” the data). PivotTables are beyond the scope of this manual.
It might seem strange to see a Table command in Excel, since each worksheet is already essentially a giant table. However, this command creates a table based on data that has already been entered into a worksheet. The advantage of doing this is that you can more easily view and work with a large group of data rather than formatting everything by hand.
[pic]
Illustrations Commands
The Illustrations group lets you add a variety of different items to your worksheet. You can add a picture or image from your computer, a stock image from the Clip Art gallery, or a variety of different shapes (circles, rectangles, banners, starburst, etc.). SmartArt lets you create professional-looking items like flow charts, process diagrams, and hierarchy trees with just a few clicks. Finally, the Screenshot command lets you capture all or part of any window visible on your desktop.
[pic]
Charts Commands
The Charts group lets you insert a variety of different charts based on the data in a worksheet. Click a chart type to select a format and then specify the source data.
[pic]
The option button opens the Insert Chart dialog box, which offers full access to all chart types.
Sparklines Commands
Sparklines are a new addition to Excel 2010. They are essentially small charts or graphs that fit into a single cell and show trends in your data. Choose between a line, column, or win/loss chart style.
[pic]
Filter Commands
This group contains a single command to use the new Slicer feature, which is used when using PivotTables or data from an external source. Slicers offer a much faster method of sorting and filtering data than the commands in the Home tab.
[pic]
Links Commands
The Links group contains the Hyperlink command. A hyperlink lets you send an e-mail, visit a Web page, open another document, or open a location within the current document.
[pic]
Text Commands
Excel lets you add many different text features to enhance your worksheet, particularly if you are going to print and distribute your work. You can add free-floating text boxes, headers & footers, WordArt, a signature line (where someone can physically sign a printed page), or some other object (such as other Microsoft Office files, archives, images, etc.).
[pic]
Symbol Commands
Excel is capable of working with complex formulae in order to describe various mathematical functions or make sense of data. With the Symbol group, you can insert a variety of formulae or insert a specialized symbol, such as Greek letters and characters from other alphabets.
[pic]
Lesson 2.4: The Page Layout Tab
The Page Layout tab allows you to change the look and feel of your printed worksheet. Here you can enhance the style and presentation of everything in your worksheet, control how the worksheet is printed, and arrange the objects within the worksheet.
Themes Commands
The Themes group lets you control the color, font, and effect schemes used in your worksheet. By using themes, you can ensure your document remains consistently formatted. This means you don’t have to spend time changing the look and feel of each individual element.
[pic]
Page Setup Commands
The Page Setup group lets you control the physical properties of the printed page, including:
▪ Margin sizes
▪ Page orientation (portrait or landscape)
▪ Paper size (letter, A4, etc.)
▪ How much to print on the page
▪ Page breaks (important when deciding how much to print per page)
▪ Page background
▪ Titles printed on each page (file name, date, page #, etc.)
[pic]
Click the option button to open the Page Setup dialog. This dialog offers more specific page options than those provided on the Page Layout ribbon.
Scale to Fit Commands
The Scale to Fit group gives you finer control over the look of printed data. You can force Excel to make the data fit within the height/width of the page, or you can manually adjust the scale of the printed data.
[pic]
Click the option button to open the Page Setup dialog box. This dialog offers more specific page options than those provided on the Page Layout ribbon.
Sheet Options Commands
When you open a new file in Excel, the standard worksheet displays a grid (showing cell boundaries) and row/column headings. However, if you print a worksheet, Excel will not show the grid or the row/column headers. Use these commands to toggle the grid and headings on or off.
[pic]
Click the option button to display the Page Setup (Sheet tab) dialog box. This dialog offers more specific page options than those provided on the Page Layout ribbon.
Arrange Commands
The Arrange group allows you to control where various objects in your worksheet are placed. For example, if you had a company logo you wanted to add to a chart, you would insert the graphic (via the Insert tab) and then position it on top of the chart by using the Bring Forward command. You could also use the Align command to make sure various objects in your worksheet were neatly lined up along an imaginary margin.
Finally, you can group many objects together (enabling to perform an action on all of them at once) and rotate objects to suit your needs.
[pic]
Lesson 2.5: The Formulas Tab
The Formulas tab provides access to Excel’s preset functions and formula auditing tools. As you become more familiar with Excel, you will likely start to rely more on formulae to help process your data. To help with this, Excel features a wide variety of formulae, called functions. Chances are the one you need is already part of the library! And if you need to create your own formulae, Excel features a comprehensive set of auditing tools to make sure your formula is as correct as possible.
This lesson will show you where you can access some of these features. Many of the advanced controls described here are beyond the scope of this manual. However, we will explore more about functions later in this course.
The Functions Library
All of the various functions included with Excel can be found here. Construct your own function or choose one from the various categories. Each function you insert will open a dialog box specific to that function and let you define variable values or select data from your worksheet to use with the function.
[pic]
Defined Names Commands
We know that cells are referenced by the column and row headings. Groups of adjacent cells (called cell ranges) are referenced like this: A6:B10. This notation refers to 10 cells (2 columns x 5 rows) and is shown in the Name Box.
This reference is fine, but isn’t very descriptive. Therefore, you can use defined names for cell ranges, making the data much clearer and easier to reference, particularly when working with functions.
The Defined Names group lets you define, use, and manage defined cell ranges.
[pic]
Formula Auditing Commands
Modern computers are capable of amazing speeds and mathematical feats. But for all intents and purposes, they really only do one thing at a time. However, because they do only one thing at a time, even the most complex function or formula can be performed step by step. This is where the Formula Auditing group comes into play.
These commands let you view the data set(s) used to calculate a function so you can see exactly what was used to come up with a result. You can also make Excel show the formulas entered into cells rather than their computed value, check for bugs and errors in custom formulae, check a formula for correctness, and keep watch over various things as the formula calculates a result.
[pic]
Calculation Commands
The Calculation group gives you control over when and how Excel calculates formulae in a worksheet. You can also calculate the value of a specific function, or make the worksheet calculate everything at once. These commands are useful if your functions depend on random data or data from an external data source.
[pic]
Lesson 2.6: The Data Tab
Data in a worksheet comes from one of two places: internally (typed by you or generated by a function) or externally (collected from a database or other data source, including other files on your computer.) The Data tab provides you with all the necessary commands to use and manage internal/external data connections.
Get External Data Commands
The Get External Data group offers commands to access data from outside Excel. These sources include Microsoft Office Access databases, Web pages, a variety of text-based data files, and external databases. You can also manage existing external data sources.
[pic]
Connections Commands
When you use an external data source, Excel saves a snapshot of the data to use within your worksheet. However, external sources often change frequently. Therefore, Excel offers commands to use with an external data source in order to make sure you are working with the most up-to-date information.
The Connections group is used with external data sources. You have commands to refresh the worksheet information and commands to manage Excel’s connection to the data source.
[pic]
Sort and Filter Commands
The Sort and Filter group allows you to do just that – sort and filter your data in order to make it easier to read or focus upon. Sort a selection, range, group, or entire column/row of cells in ascending or descending order, and filter the data to view only the information you need.
[pic]
Data Tools Commands
The Data Tools group gives you a number of handy commands to better control the data in your worksheet. Using these commands, you can turn well formatted text into columns, remove duplicate entries, check the validity of data, and group like data together.
This group also features the extremely useful What-If Analysis command. This command is designed to work with a group or groups of data and various formulae in order to determine the required values needed to reach an outcome. For example, you can give Excel the numerical answer to a question (“We want sales of $XXXXX per quarter”) and Excel works backwards to determine the question for you (such as, “How much does each division need to sell?”).
[pic]
Outline Commands
Back in the Formulas tab, we saw that groups of adjacent cells can be given a defined name. The commands in the Outline group allow you to do something similar by allowing you to group adjacent cells together and then collapse (hide) or expand (show) that block of data. This allows you to temporarily hide unimportant data from view, making the worksheet easier to read and print.
For example, if you had a large sales worksheet showing the figures from several divisions, you could group and collapse all the sales figures and show only the subtotals. All of the individual figures would still be available in the worksheet, but they would be hidden from view.
[pic]
Click the option button to open the Settings dialog, which provides commands to create summary rows and/or columns (automatic subtotals) around the collapsed data. You can also apply styles to any subtotal rows/columns, making the important information stand out and easier to read.
Lesson 2.7: The Review Tab
The last tab we will explore in this section is the Review tab. This tab offers commands to make sure everything in your workbook is spelled correctly, translation services for international readers, the ability to add and manage comments, and the ability to prevent others from making unauthorized changes to your document.
(Excel features one more tab, the View tab, which we will explore in Section 5.)
Proofing Commands
The Proofing group offers commands for spell checking, the ability to do online research (search engine access, periodical searching, etc.), and use a thesaurus.
[pic]
Language Commands
The Language group lets you run your worksheet through an online translation service.
[pic]
Comments Commands
Excel lets you add comments to a worksheet just as you might add yellow sticky notes to a book or blueprint. These comments are useful to remind yourself of something you should add or to remind others to check consistency, accuracy, or formatting. Use the Comments group to create, browse, and manage comments.
[pic]
Changes Commands
The Microsoft Office suite of programs is designed with interconnectivity in mind. This means that you are able to share your work with others and make changes to a document as a team. However, although you may need to share your work with others, you might also want to lock down your document to prevent accidental (or intentional!) modification/deletion of your worksheet.
The Changes group allows you to protect a single worksheet or an entire workbook, as well as share the workbook with others. You can also allow others to edit only certain sections of your workbook, and track every change that is made.
[pic]
Section 2: Review Questions
The Quick Access Toolbar is designed for what type of Excel commands?
A. The commands you use most frequently
B. The commands you use least
C. Printing commands
D. Functions
Each Excel ______ contains numerous _______.
A. Interface, tabs
B. Tab, commands
C. Command, tools
D. Tab, dialog boxes
Most of Excel’s formatting commands are located on what tab?
A. The Home tab
B. The View tab
C. The Data tab
D. The Review tab
Most of Excel’s page enhancement commands are located on what tab?
A. The Home tab
B. The View tab
C. The Page Layout tab
D. The Data tab
The Formulas tab mostly contains…
A. Commands for editing data
B. Commands for logical and mathematical functions
C. Commands for creating charts
D. Commands for reviewing data
The Insert tab contains commands to insert which of the following objects?
A. Charts
B. Tables
C. Graphics and shapes
D. All of the above
To find commands that will help you use Excel with an Access database, you would…
A. Try the Formulas tab
B. Try the Insert tab
C. Try the Data tab
D. Try the Design tab
The Spelling button is located in which tab?
A. The Data tab
B. The Insert tab
C. The Review tab
D. The Page Layout tab
Which of the following is not a tab?
A. The Design tab
B. The Formulas tab
C. The Calculation tab
D. The Data tab
What are groups?
A. Collections of like commands that make up tabs
B. A dialog box containing specific commands
C. Customizable toolbars
D. Another name for the Quick Access Toolbar
Section 3: Excel Basics
In this section you will learn how to:
▪ Work with columns, rows, cells, and ranges
▪ Create worksheet labels
▪ Enter and delete data
▪ Print a worksheet
▪ Use AutoFill, AutoSum, and AutoComplete
▪ Work with basic formulae
▪ Drag and drop cells
▪ Cut, copy, and paste cells
▪ Use the Clipboard and Paste Special
▪ Insert and delete cells, rows, and columns
▪ Use undo, redo, and repeat
▪ Use Custom Actions
▪ Use the error option button
▪ Use the AutoFill option button
▪ Use the Paste Option button
▪ Use AutoCorrect
▪ Use spell check
▪ Use Find and Replace
▪ Document a worksheet with comments
Lesson 3.1: Working with Excel
Excel is used to organize and analyze data. In order to work effectively with Excel, you need to understand the building blocks that make up a worksheet. In this lesson we will review the basic elements of a worksheet: columns, rows, cells, and ranges. We will also learn about worksheet labels, how to enter and remove data, and how to print a worksheet.
Columns, Rows, Cells, and Ranges
Columns, rows, and cells make up the fundamental components of a worksheet. A column is a vertical series of adjacent cells from top to bottom. A row is a horizontal series of cells from left to right. A cell describes the intersection of a row and column:
[pic]
Each column had an index letter. Since there are only 26 letters, and far more than 26 columns on a spreadsheet, the next columns after column Z are indexed by the letters AA, AB, AC, and so on until the last column (XFD, over 16,000).
Each row has an index number. The rows are numbered from 1 through 1048576. The top left cell in the worksheet is indexed by the letter-number combination A1.
The active cell is a name given to the cell that is currently selected. The active cell is referenced in the Name Box:
[pic]
Excel is designed to have data organized down the sheet under column headings more so than across the sheet in rows. This is why there are over a million rows down and only about 16000 columns across.
A cell range (or simply “range”) is defined as a series or block of adjacent cells. A range can be a very useful tool because it allows you to make changes (such as applying formatting) to multiple cells at once. You can select cell ranges in one of three ways: with the mouse, the Name Box, and the keyboard.
To select a range with your mouse, move your pointer over the center of a cell. Your mouse pointer will turn into a thick cross. Hold the left mouse button down and drag your pointer to select a range. (A selected range will be highlighted in blue.)
[pic]
To select a range with the Name Box, type the range directly into the Name Box using the format “StartCell:EndCell”. For example, if you enter the range A1:A7 and pressed Enter, these cells will be selected:
[pic]
To select a range with the keyboard, you have two options. If you have a block of data in a worksheet, you can click on any data cell in the block, and press Ctrl + Shift + 8. This will select the block of data as a range. Excel will use the empty cells adjacent to the data as boundaries to the range:
[pic]
[pic]
Finally, you can select a range by clicking in the first cell of a block you want to select, press and hold Shift, and then click on the last cell in the block or use the arrow keys to select one row/column at a time.
When you select a group of cells, you should notice some numbers appear on the status bar. These numbers tell you the average, count (how many numbers), and the sum of the data in the selected cells:
[pic]
Creating Worksheet Labels
We know that each row and column has a header, and the combination of a column and row header identifies each cell. In order to make your worksheet much more readable, it’s a good idea to label your data. Anyone who reads the worksheet will be able to make more sense of the data because it would be extremely difficult to read multiple rows and columns of unidentified numbers!
A worksheet label is a simple text description of the data it represents. For example, if your worksheet contains information about the age, height, and weight of a group of people, label the data as such:
[pic]
As you can see, the stats for each person and each person’s name have been entered as labels. This not only makes the data easier to read, but also makes it easier to create charts and graphs based on the data. We will explore this concept later.
Entering and Deleting Data
Now that you are familiar with the basics of columns, rows, ranges, and labels, let’s start working with data. First, you need to enter the data. There are a number of ways to do this.
The most direct way is to click the cell you want to use (making it the active cell) and type. When you type something in the active cell, what you type will also be displayed into the Formula Bar:
[pic]
To the left of the Formula Bar are two commands: an X and a check mark. Click the X to remove the data in the active cell. Click the check mark to commit the data to this cell and move to the next row:
[pic]
If you are typing data in a spreadsheet, you can press Tab or Enter to move the active cell. Press Tab to advance one column (ex. D2 to E2); press Enter to advance one row (ex. D2 to D3):
[pic]
To delete data from a cell, right-click on the cell to display the drop down menu. Clicking the Clear Contents option will remove the data, but not the formatting:
[pic]
As a side note, when you right-click a cell, you will see a small formatting toolbar at the top of the pop-up menu. From this small toolbar, called the mini toolbar, you can apply a number of formatting commands available in the Font and Alignment groups of the Home tab. This toolbar really comes in handy when you have a tab other than the Home tab in view, but you need to format some cells:
[pic]
If you click the Delete option in the right-click menu, a Delete dialog box will be displayed. With the Delete option, you will physically remove rows/columns from the worksheet. Select your option and click OK:
[pic]
If you select the Shift cells left option, all data from the cell to the immediate right will be shifted left into the now vacant cell. If the Shift Cells Up option is selected, the data in the cell immediately below would be shifted up into the vacant cell.
You can also delete rows and columns by clicking the Delete command the Home tab.
[pic]
To delete rows, first select a row or rows. Then, click the Delete arrow to display the delete menu, and then click the Delete Sheet Rows option. This will delete the row, and shift the cells below up by however many rows were deleted
To delete columns, select the column or columns you want removed, and then choose the Delete Sheet Columns option. The columns to the right of the deleted data will be shifted left however many columns were deleted.
To delete a worksheet, switch to the worksheet you want to remove and click Delete Sheet.
Remember, clearing contents only removes the data, while deleting removes data and formatting.
It is important to keep in mind that Excel treats text and numbers differently. A number is seen as a value in Excel, something that can be used in mathematical operations. Text is often used as labels or identifiers.
Consider the following table of data:
[pic]
Cells B1:D1 have numbers for worksheet labels. Notice how these cells, like the table data(B2:D4), are right-aligned. The text in A2:A4 is left-aligned.
If you want to enter a number as text (use a number as a label), put an apostrophe (‘) in front of the number. Do this by clicking the cell you want to change, adding an apostrophe in front of the number in the Formula Bar, and press Enter. The number will now be “formatted” as text:
[pic]
Numbers that are formatted as text are denoted with a small green marker:
[pic]
If you click one of these cells, you will see a small error notice appear. If you hover your mouse pointer over this notice, you will see a description of the notice:
[pic]
There is a pull-down arrow beside the notice; click it for more options:
[pic]
As we proceed through this manual, we will cover more of these error notices.
Printing your Worksheet
Excel 2010 lets you perform all printing activities from a single location in the File (Backstage) menu. To open this view, click File ( Print:
[pic]
This view is split into two sections. On the left, there are commands to modify the printer and page properties. In fact, you might recognize some of the commands here from the Page Layout tab. On the right, you will see what the data will look like when printed, based on the settings in this view. You can use the arrow buttons on the bottom to browse through the pages, if your data won’t all fit on one page.
When you have made your changes, click Print. We will explore printing more in the final lesson of this manual.
Lesson 3.2: Basic Excel Features
When working with spreadsheets, it is often the case that you must repeat data in a large number of cells. Excel helps you do this efficiently by automating some basic and repetitive tasks for you. In this lesson we will discuss some of the useful Excel features concerning data entry: AutoFill, AutoSum, and AutoComplete.
In addition to covering these automated features, we will also cover a key concept: how to work with basic formulas.
AutoFill
If you use Excel to log information over a period of time, such as recording daily sales, you might be dreading having to type the days of the week over and over again. Those of you who have more experience with computers might think of using Copy and Paste (which we will cover in the next lesson), but there is another way to enter repeating text or numerical sequences. The AutoFill feature can help you enter repeated or incremental text and numbers quickly.
For example, imagine that you have to enter all of the years from 1990-2010 in a worksheet. Rather than typing each year manually, you can take advantage of the AutoFill feature to enter the data quickly and easily.
In order to use AutoFill, you need to establish a pattern. In this example, type 1990 in one cell, and 1991 immediately below. Then select both cells, like this:
[pic]
Next, click and drag the small square down the worksheet until the desired value is reached. Once “2010” appears beside the mouse pointer, release the mouse button and Excel will automatically fill in the numbers:
[pic] [pic]
The worksheet will now contain the years 1990-2010.
This feature doesn’t work just on single increments either. You can have Excel jump 2, 10, or 10,000 numbers at a time, forwards or backwards, depending on the two initial values you have entered.
Note that for numerical data, you have to select two adjacent data items, and they have to change incrementally for AutoFill to recognize and enter the correct consecutive values. If you selected only 1990 and dragged down the column, AutoFill would enter 1990 into every cell.
However, depending on what you want to do, this can work to your advantage.
[pic]
Excel comes pre-programmed with some other common AutoFill sequences, including days of the week and months of the year. Unlike numerical sequences, you don’t have to enter two initial values:
[pic] [pic]
AutoSum
Most worksheets are used to calculate numerical or financial data, so Excel includes an AutoSum feature. This command will find the sum of a row or column of data.
To use this command, click the cell immediately below (if summing a column of data) or to the immediate right (if summing a row of data) of the data you want to sum. Next, click Formulas ( AutoSum:
[pic]
Excel will scan the data in the column/row. The column or row of data to be summed will be highlighted by an animated border:
[pic]
Press Enter to complete the AutoSum command:
[pic]
AutoComplete
AutoComplete will help you enter data by automatically filling information in as you type, based on similar data in adjacent cells in the same column. This feature is enabled by default, and is very useful if you need to create a list of names or if you commonly enter the same types of data.
For example, if you typed “Alice” into a cell, pressed Enter, and then typed “a,” Excel would automatically fill in the remaining letters of “Alice”:
[pic]
Just press Enter to accept the completion.
If you then typed the name “Arnold” into the same column, Excel will now be set up to AutoComplete either “Alice” or “Arnold.” However, you will need to type the second letter in order for Excel to determine which name you are entering:
[pic]
AutoComplete has the potential to save you time when you type information, but sometimes it can get in the way. If you want to turn the AutoComplete feature off, click File ( Excel Options ( Advanced (tab on the left) ( and uncheck “Enable AutoComplete for cell values:”
[pic]
Click OK to accept the change. Excel will no longer use AutoComplete.
Working with Basic Formulae
Formulas are mathematical expressions that operate on cell contents. When cells contain numerical data, you can perform multiple mathematical operations on the cell content as your worksheet requires. The results of these operations will be shown in the cell that contains the formula. Formulas can be simple, like adding two cell values, or quite complex, involving multiple mathematical operations.
Formulae are always preceded by an equals sign (=). Formulae can contain cell references (like A1), numbers (like 23), or even other functions (like SUM(B2:B9)). Enter a formula by typing directly into a cell, or use the Formula Bar:
[pic]
=A1+23, = d2-c2, and =B10+b11/C6 are all valid formulae. Cell references are not case-sensitive.
If you include a cell reference in a formula (like =B3*6), and that cell reference itself contains a second formula (like =B1+B2, stored in B3), that second formula (=B1+B2) will be evaluated first, and the result will be used in =B3*6.
Consider the following worksheet. To calculate Sales, we must multiply Quantity by Price:
[pic]
The formula =A2*B2 will be entered into C2. Note the colors that outline the cell references:
[pic]
After the formula has been entered, press Enter to calculate the value:
[pic]
You can tell if a cell contains a formula by making it active. If there is a formula in the active cell, it will be shown in the formula bar:
[pic]
We know that Excel can use AutoFill in order to fill in a single value over and over or a sequential value. AutoFill also works with a formula. Select the cell that contains the formula you want to use, and click and drag the black square:
[pic]
Excel will change the column/row references as necessary:
[pic]
Formulas can contain multiple cell references from a single worksheet, or even references from different worksheets or workbooks. However, you can create a circular reference in Excel by referencing a cell that is dependent on the very cell that references it for a result.
For example, If A1 contains the formula =10+B2, and B2 contains the formula =A1-25, you have created a circular reference. Cell A1 cannot be resolved until Cell B2 is resolved, and vice-versa. You will be warned if Excel finds any such references.
[pic]
Lesson 3.3: Moving your Data
The ability to manipulate your data is crucial when building worksheets and workbooks. To work with your data efficiently, you should know how to manipulate the data in a worksheet. We will cover the Cut, Copy and Paste functions; how to insert and delete cells, rows, and columns; how to use Paste Special; how to drag and drop cells; and how to use the Undo, Redo, and Repeat features.
This lesson contains a number of important features available when working with spreadsheets, so take your time. After completing this lesson, you will have the necessary skills to take on the data in a wide variety of spreadsheets!
Dragging and Dropping Cells
Dragging and dropping cells is easy. First, select a cell by clicking on it, thereby making it the active cell:
[pic]
Now move your mouse pointer over one edge of the active cell border. The mouse pointer will turn into a four-headed arrow:
[pic]
Click and drag the cell contents to a new location. Release the mouse button to drop the cell in its new location:
[pic]
You can click and drag multiple cells in the same way: select a block of cells and then click and drag them around your worksheet by dragging the border.
You can drag and drop cells pretty much anywhere in the worksheet. If you drag a cell with a formula, the formula will move to the place you drop it. If you drag a cell that is referenced in a formula (a single cell, or a selection of cells) all formulas that reference the cell will be adjusted to reference the new location.
Be careful when dragging and dropping cells in a worksheet. It is easy to drag and drop cells by mistake when trying to perform other operations. Use the Undo command to move things back.
How to Cut, Copy, and Paste Cells
To be able to move your data effectively, you must know how to cut, copy, and paste cells. The Cut, Copy, and Paste commands are almost universal throughout the computing world, and are usually some of the first commands novice computer users will use on a regular basis. Because these three operations are so common, we end up with several ways of doing the same thing.
Our advice is to learn the keyboard shortcuts (below) as soon as you can, because these keyboard shortcuts are used in nearly every computer program.
▪ Cut (Ctrl + X) will remove the source data and store it in the computer’s memory.
▪ Copy (Ctrl + C) will make a copy of the source data and store it in the computer’s memory, leaving the source data untouched.
▪ Paste (Ctrl + V) will put the cut or copied data into a new location specified by the user.
Usually, cut items can be pasted only once and copied items can be pasted multiple times.
To cut a cell, right-click the cell and select Cut from the drop down menu or press Ctrl + X. To copy a cell, right-click the cell and select Copy or press Ctrl + C:
[pic]
Either method will surround the cell with a flashing border. You can then paste cut/copied data in one of two ways: by using the right-click menu, or choosing a destination cell and pressing Ctrl + V.
How to Cut, Copy, and Paste Multiple Cells
Cutting, copying, and pasting works the same way with single or multiple cells. The important difference is that you must select a range of cells first. When you have made your selection, use the keyboard shortcuts or right-click menu to Cut/Copy the cells. The cut/copied data will be surrounded by a flashing border:
[pic]
Next, select a destination cell and press Ctrl + V or right-click ( Paste. This will be the top leftmost cell for the pasted data:
[pic]
Notice the small notification that appears beside the pasted data. This outlines various paste options, which we will explore next.
Using the Clipboard
We know that Cut/Copied information is saved in the computer until it is pasted. The area of memory used to store these items is called the clipboard. Normally, only one item at a time is remembered. But Office 2010 lets you view and store up to 24 different items on the clipboard.
To view the clipboard and its contents, click Home ( Clipboard option button:
[pic]
This will open the Clipboard Task Pane on the left-hand side of your screen:
[pic]
Anything that has been cut or copied (including most items cut/copied in something other than the Office 2010 suite of programs) will be listed here, with the most recently cut/copied items on the top. To use items in the clipboard, click the pull-down arrow and select Paste:
[pic]
Using Paste Special
Paste Special is a very useful Excel feature. You can use this command to perform a lot of operations that might be tedious to perform using other Excel tools. Paste Special does indeed paste data, but it also allows you to perform operations on the destination cells using the pasted data.
Consider the following worksheet. It lists quantities in column A, prices in column B, and sales (A*B) in column C:
[pic]
Suppose that all prices are to be raised by 20%. You can manually enter the new prices, use a formula in a new column to calculate the prices, or you can use Paste Special.
To use Paste Special in this situation, enter the value 1.2 (the numerical equivalent of 120%) in cell D1, then right-click D1 and choose Copy:
[pic]
Next, select the prices in column B. Right-click on the selected area and choose Paste Special (ignore the Paste Options submenu that appears):
[pic]
This will display the Paste Special dialog box:
[pic]
There are a number of options In the Paste Special dialog box that you can choose from. To increase the prices in the selected range by 20%, we want to multiply each price in the selected range by 1.2.
Select the Values and Multiply radio buttons and then click OK:
[pic]
The prices have now been increased by 20%, and Sales have increased as well, taking the new prices into account. The currency formatting applied to column B also remains:
[pic]
The Paste Special dialog box defaults to paste all source information (1.2 in this example). If the All radio button (in the Paste Special dialog box) was left selected, the same operation would have occurred, but the currency formatting applied to column B would have been stripped away:
[pic]
The end result is still mathematically correct, but you would have to reapply the currency formatting.
Like the regular copy and paste operation, you can use single or multiple items with Paste Special. You can copy and Paste Special a single item to a single cell, a single item to multiple cells, and multiple items to multiple cells. With Paste Special you can choose to add the copied value, subtract it, multiply it, or divide by selecting the appropriate radio button. You can also choose to paste only values, so a formula will not be copied but its result will.
Depending on what you want to do using Paste Special, you might have to experiment with some of the various settings in the Paste Special dialog box. If you make a mistake, you can use the Undo command to revert the changes and try something else. We will explore the Undo command later in this lesson.
Inserting and Deleting Cells, Rows, and Columns
Suppose you are building a worksheet and realize you forgot to include a row or column of important data. It could also be the case that a row or column of data is unnecessary and should be removed. In either case, it’s easy to add or remove rows and columns from a worksheet.
To insert a column in a worksheet, right-click a column header and click Insert. This will add a new column in this location and push all existing information (including the column you right-clicked) one column to the right.
Here, we are going to add a new column between columns B and C:
[pic]
[pic]
You now have a new empty column to work with.
This procedure is the same for inserting rows. To insert a row, right-click a row header and click Insert. All of the data in the row you selected as your insertion point, and the data in the rows beneath it, will be shifted down one row. This will leave an empty row where you can enter new data:
[pic]
[pic]
Deleting rows and columns works in the same way. To delete a column, right-click the column header you want to remove and click Delete. All of the data to the right of the column will be shifted one row to the left, and the old information will be replaced with the data that was in the column to the immediate right. To delete a row, right-click the row header and click Delete. All of the data below the column will be shifted up one row, and the old information will be replaced with the data that was in the row directly beneath.
To insert a cell, right-click a cell and click Insert. The Insert dialog box will appear:
[pic]
When you insert a cell, the existing data must be relocated. Selecting Shift Cells Right will move the item in the active cell, and all the items to the right of it, one cell further to the right, leaving a blank cell at the original location. For example, if you added a new cell at A1 the data in cell A1 would move to B1, and the data in B1 would move to C1 and so on, leaving cell A1 empty. Selecting Shift Cells Down will perform a similar operation, but a downward direction. For example, if you inserted a cell at location B1, the data in B1 would shift to B2, B2 would shift to B3, and so on, leaving B1 empty.
The Entire Row or Entire Column options allow you to insert a row or a column in the same manner we just discussed.
Let’s look at an example. Here we are going to insert a cell at B5 and shift cells to the right:
[pic]
A new empty cell appears at B5:
[pic]
Notice that cell D5, the active cell, contains a formula. When you insert rows, columns, or cells, Excel is capable of adjusting most existing formulae in order to account for the new cell/row/column.
Using Undo, Redo, and Repeat
Anyone can make mistakes, especially when creating a complex worksheet. Excel provides a way of backtracking to revert unintended actions. To undo an action, click the Undo button on the Quick Access Toolbar or press Ctrl + Z:
[pic]
This will undo the very last action you performed. If you continue to click the Undo button, the next most recent action will be undone, and so on.
You can also click the pull-down to show a list of recent actions. You can click any selected item to undo it and any other actions above it in the list.
[pic]
To redo an action (or “undo the Undo”), you can click the Redo button on the Quick Access Toolbar:
[pic]
Here, you can also use the pull-down arrow to select multiple actions to Redo, just as you could with Undo.
In certain cases, the Redo function will change to a Repeat command. This allows you to do repeat the actions on one cell or group of cells again. For example, if you applied Bold formatting to some cells, you could select another group of cells and click the Repeat command or press Ctrl + Y to repeat the last action.
Lesson 3.4: Custom Actions and Options Buttons
Excel’s Custom Actions and Option Buttons can provide you with information and can perform actions based on the context of what you are currently doing with your worksheet.
In this lesson, we will learn what Custom Actions are and how to use them. You will also learn about the Error option button, the AutoFill option button, and the Paste option button, and how they can make tasks easier.
What are Custom Actions?
A Custom Action is a type of command button that appears in response to information you enter in a worksheet. Custom Actions are capable of recognizing certain types of data and will sometimes appear based on the context of your current actions. The Custom Action will then provide a menu of options related to the information you are entering or the action you are performing.
As an example, if you enter the letters “MSFT” in a cell with the appropriate Custom Action enabled, and then right-click that cell, Excel is capable of recognizing that text as the stock symbol for the Microsoft Corporation:
[pic]
Setting Custom Action Options
You can configure Custom Actions by clicking File ( Options. When the Excel Options window appears, select Proofing from the panel on the left, and then click the AutoCorrect Options button:
[pic]
When the AutoCorrect dialog box appears, click the Actions tab:
[pic]
To enable Custom Actions in your worksheets, check the Enable additional actions… checkbox. Here you can specify which Custom Actions to use. If you change the Custom Actions settings, click the OK button to activate the changes.
The More Actions button will open your Web browser to a page listing available actions for Microsoft Excel, PowerPoint, and Word 2010. You can also purchase additional Custom Action procedures from third-party vendors.
The Error Option Button
Excel keeps an eye on your work as you define and calculate formulae. If it detects an error in your work, you will be warned. For example, consider the following worksheet. A formula is being entered into C2. Do you see the error?
[pic]
The formula does not contain an error, but the data does. Because cell B2 has the value 0, the formula =A2/B2 will cause an error (division by zero is mathematically undefined). If you press Enter to move to the next cell, you will see the following warning:
[pic]
Notice that there is a small green triangle in the upper left of the cell. If you click this triangle, you will see the Error option button.
Click the pull-down arrow for information on resolving this error:
[pic]
This is a good example of how Option Buttons can provide context-sensitive help. If you made another type of error (other than division by zero), the choices on the option button’s menu would change to reflect this error.
The AutoFill Option Button
After using AutoFill to complete a row or column, the AutoFill option button will appear:
[pic]
Click the pull-down arrow to see additional options, depending on the AutoFill sequence you have created:
[pic]
For example, if you use AutoFill to complete dates, you will have more specific options available:
[pic]
The Paste Option Button
Whenever you paste something in Excel, you will see this notification appear beside the pasted data:
[pic]
Click this notification to use the different paste options:
[pic]
As you can see, there are a large number of options. Let’s quickly go over the different choices:
|Paste |[pic] |A 1:1 paste of the original data, whatever it may be. This is the default Paste option. |
|Formulas |[pic] |Pastes the formulas and recalculates upon pasting. |
|Formulas and Number |[pic] |Pastes the formulas and recalculates upon pasting, preserving whatever formatting was applied to the|
|Formatting | |formulas. |
|Keep Source Formatting |[pic] |Preserves all formatting from source data. |
|No Borders |[pic] |If the cut/copied information contained border formatting, the pasted data would not. |
|Keep Source Column Widths |[pic] |Excel allows you to adjust the width/height of columns/rows. If the source data used a different |
| | |column width than the destination cell, use this option to keep the same width. |
|Transpose |[pic] |If source data was 2 columns x 3 rows, transposed data would be 3 columns x 2 rows. |
|Values |[pic] |Copies only the values as calculated by any formula in the source data. The formulas themselves and |
| | |source formatting are not copied. |
|Values and Numbering |[pic] |Copies values and number formatting; formulas and text formatting are not copied. |
|Values and Source |[pic] |Copies values and all formatting; formulas are not copied. |
|Formatting | | |
|Formatting |[pic] |Pastes only the formatting from the source data. |
|Paste Link |[pic] |Pastes a link to the source data. |
|Picture |[pic] |Excel captures a screenshot of the source data and turns it into a picture. |
|Linked Picture |[pic] |Pastes a link to a screenshot of the source data. |
Lesson 3.5: Editing Tools
Excel offers a variety of editing tools to help ensure that your worksheets are accurate, free from spelling errors, and well documented. In this lesson, we will learn about AutoCorrect and Spell Check, two features that can help you fix typing and spelling errors. We will also learn how to use Find and Replace, a great tool for finding and fixing data in a large worksheet. Finally, we will learn how to add comments to a worksheet in order to clarify the data or post reminders.
Using AutoCorrect
AutoCorrect can help you avoid common spelling mistakes and typographical errors as you type. Excel keeps a list of common misspellings called AutoCorrect entries. If you type a misspelling that is in the AutoCorrect list, Excel will replace the mistake with the correct word when you press Enter or hit the space bar.
AutoCorrect also enforces rules such as capitalizing the first word in a sentence (such as “this worksheet describes…” ( “This worksheet describes…”), capitalizing the names of days (monday ( Monday), and correcting two successive capital letters (BUdget ( Budget).
To modify AutoCorrect options, click File ( Options ( Proofing tab ( AutoCorrect Options button:
[pic]
When the AutoCorrect dialog box appears, click the AutoCorrect tab. Here, you can configure a number of AutoCorrect behaviors by checking or clearing the various options. You can also browse to see what sort of text Excel will use for replacement as you type (such as (c) with ©):
[pic]
You can also add your own replacement combinations to this list by defining them using the Replace and With text boxes.
Using Spell Check
Spell check is an Excel editing feature that you can use to check your worksheets for spelling mistakes. It is much more comprehensive than the AutoCorrect feature, which is designed to quickly correct simple, common typos.
If you have used a word processing program such as Microsoft Office Word, you know that the program is capable of identifying words it thinks are misspelled. These words identified by a marker of some sort; in Microsoft Word they are defined with a red squiggle underline.
On the other hand, Excel does not track cell content for misspelled words. This is because spreadsheets are often filled with abbreviations, acronyms, people’s names, and other things that are not part of a standard spell checking library, so Excel simply doesn’t bother to check. However, you can still check spelling in your worksheet.
When you invoke spell check, Excel will compare the words in your worksheet against a library of correct spellings for your language/dialect. If a word is not found in the dictionary, you can choose a substitute to replace it or add the unknown word to the dictionary so it will no longer be a spelling error.
To spell check a worksheet, click cell A1 and then click Review (Spelling. You can also use the F7 keyboard shortcut:
[pic]
To spell check only a selection of cells, select a range of cells and then click the Spelling button.
In either case, if the spell checker finds a word that is not in its dictionary, you will see the Spelling dialog box:
[pic]
You can choose to ignore this word, replace it from a list of suggested words, or add it to the dictionary so it will no longer be interpreted as a mistake. You can also click the AutoCorrect button to enter the mistake and replacement word into the AutoCorrect list.
When the spell checker is finished, it will alert you:
[pic]
Using Find and Replace
You can search a selection of cells or a worksheet for a particular word or number by using the Find and Replace feature. Click Home (Find & Select or press Ctrl + F:
[pic]
Either action will display the Find and Replace dialog box:
[pic]
As you can see, there are two tabs, Find and Replace. We’ll discuss Find first.
To use the Find command, type what you want to find in the text field (the pull-down list contains recently used search terms) and click the Find All button or the Find Next button. If you click Find All, Excel will provide a list of cell references that contain a match for the search term. If you click Find Next, you can jump to the next cell that contains a match for the search term.
If you click the Options button, you have more options to refine your search:
[pic]
You can now choose to search via formatting, within a sheet or workbook, by rows or columns, or by matching the case, all by making selections in the combo boxes.
If you click the Replace tab, you have the option to search for one thing and replace it with something else. You also can jump directly to this tab by pressing Ctrl + H:
[pic]
If any instances of the search criteria entered in the “Find what” text field are found, they can be replaced with whatever is written in the “Replace with” text field. All you have to do is click the Replace button each time Excel finds something.
If you click the Replace All button, Excel will replace every instance of the word or number it finds with the replacement term, without waiting for you to click Replace for each one. This feature is useful when correcting errors with proper names or abbreviations.
Click the Options button to replace items in the same fashion as we discussed with the Find tab:
[pic]
Adding Comments
Sometimes it is necessary to include explanations for data or formulas, especially if your spreadsheet is very complex. Comments allow you to place explanations or definitions where ever you need them in your spreadsheet. Comments are also useful when editing or sharing files with others. You can leave these virtual “sticky notes” in the workbook to ask others for clarification or to check the validity of the workbook.
If a cell contains a comment, there will be a small red triangle in the upper right-hand corner:
[pic]
To view a comment, just point to it with your mouse:
[pic]
It’s easy to insert comments into your worksheet. Just choose a cell to comment and click Review ( New Comment or right-click the cell ( Insert Comment:
[pic]
A comment box will appear with your profile name and a flashing cursor. Type whatever you like into the comment:
[pic]
You can resize the comment box by moving clicking and dragging the round handles around the edge of the box:
[pic]
You can change the name to whatever you want by clicking on it and typing a new name. You can also click in the main body of the box and type the comment or explanation that you need. When you are finished, click outside the comment box. The cell will now contain a red triangle and be marked with a comment:
[pic]
If you ever need to edit a comment, right-click the commented cell and click Edit Comment or Delete Comment. Clicking the Edit Comment option will open the comment box for editing, while clicking the Delete option will remove the comment and the small red triangle from the cell:
[pic]
Section 3: Review Questions
What is a worksheet label?
A. A worksheet label is a kind of identifier that describes the data in your worksheet
B. A worksheet label is a custom column or row heading
C. A worksheet label is often composed of text, but numbers can be used as well
D. All of the above
Which of the following statements is false?
A. An Excel worksheet has thousands and thousands of cells
B. There are more rows than columns in a worksheet
C. There are more columns than rows in a worksheet
D. Excel 2010 worksheets can hold more data than the worksheets in earlier versions of Excel
When you enter a formula in Excel, what sign needs to be in front?
A. >
B. #
C. =
D. Does not matter
Which of the following statements is false?
A. You can copy and paste selections
B. You can drag individual or multiple cells
C. You cannot use Paste Special on multiple cells
D. In Excel, copied items are stored on the clipboard
AutoCorrect is useful because it…
A. Corrects most formula errors
B. Corrects most logical errors
C. Corrects poor formatting
D. Corrects spelling as you type
A range can be defined as…
A. A single cell
B. A collection of adjacent cells
C. Neither A nor B
D. Both A and B
Clearing cell contents will…
A. Remove data
B. Remove data and cell formatting
C. You cannot clear a cell
D. None of the above
To take advantage of Custom Actions, you need to:
A. Have an Internet connection and enable Custom Actions for your workbooks
B. Install the Custom Actions add-in
C. Learn how to develop XML
D. Excel has no Custom Actions functionality
In Excel, what is a comment?
A. A section of descriptive text that will be stored in your workbook
B. A tool for annotating your spreadsheets
C. Descriptions of obscure or complex spreadsheet components
D. All of the above
The Find and Replace feature can do which of the following?
A. Find a word or number in the spreadsheet
B. Find every occurrence of a word or number in a selection of cells
C. Find and replace every occurrence of a word, number, or text string in a selection of cells
D. All of the above
Section 4: Editing your Workbook
In this section you will learn how to:
▪ Change the size of rows or columns
▪ Adjust cell alignment and rotate text
▪ Create custom number and date formats
▪ Use conditional formatting
▪ Use the Format Painter
▪ Merge adjacent cells together
▪ Use AutoFit
▪ Find and replace formatting
▪ Add patterns, colors, and borders to a worksheet
▪ Work with styles and themes
▪ Create and format charts
▪ Enhance charts with drawing tools
▪ Change the chart type
▪ Change the source data for a chart
▪ Work with chart axes and data series
▪ Save a chart style/layout as a template
▪ Identify absolute and relative cell references
Lesson 4.1: Modifying Cells and Data
To make the most out of your worksheets, you need to understand the many ways that you can modify cells and data in Excel. In this lesson, we will learn about changing the size of rows and columns, adjusting cell alignment, creating custom formats, and rotating text.
[pic]
Changing the Size of Rows or Columns
Sometimes it will be necessary to change the size of a row or column in order to display all the data contained in the cell. You also might want to change the size of a row or column just for the change in appearance. In either case, changing the size of a row or column is quite easy.
To change the size of a column, place your mouse pointer on the line that divides the column headers. For example, if you wanted to change the size of column B, you would place your mouse pointer on the line separating B and C. Your mouse pointer will turn into a vertical line with a small arrow on either side:
[pic]
When you see this pointer, click and hold the left mouse button to drag the column edge to the left or right. As you drag you will see the size (default width is 64 pixels):
[pic]
Now the size of column B has been changed.
To change the size of a row, place your pointer on the line separating the row headers and then click and drag up or down to make the row larger or smaller (default height is 20 pixels):
[pic]
There are more cell formatting commands available by clicking Home ( Format. Here you can have Excel automatically adjust to the necessary dimensions or define a specific dimension:
[pic]
Adjusting Cell Alignment
To align data within a worksheet select the cell or cells you want to adjust and then use the commands in the Alignment group of the Home tab:
[pic]
Let’s go over the different groups of commands:
|Vertical Alignment |[pic] |If there is extra space above or below the data in a cell, use these commands|
| | |to vertically align the data. |
|Horizontal Alignment |[pic] |Use these commands to left, center, or right align items in a cell. |
|Orientation |[pic] |Use this command to change the way text is written in the cell: |
| | | |
| | |[pic] |
|Increase/Decrease Indent |[pic] |Increase or decrease the distance between cell data and the cell margins. |
|Wrap Text |[pic] |If you need to enter a lot of data into a cell, stretching the column width |
| | |to accommodate everything on one line may be impractical. Use this command to|
| | |wrap the text to the next line inside the cell. |
|Merge & Center |[pic] |This command is useful when making titles. Select two or more adjacent cells |
| | |and click this command. The adjacent cells will merge into a single cell and |
| | |the data contained inside will be center aligned: |
| | | |
| | |[pic] |
| | |[pic] |
| | | |
| | |We will discuss cell merges later in this manual. |
Rotating Text
Rotated text can make your worksheets look better, improve organization, and improve readability. Rotating text can also make viewing or printing a large worksheet easier because the column widths do not have to accommodate the length of your text descriptions.
To rotate text, first select the cell or range of cells you want to rotate.
[pic]
Once you have selected a cell or range, click the Orientation button in the Alignment group on the Home tab. Select the alignment of your choice:
[pic]
You can also right-click on any cell in the selected range and click on Format Cells in the drop down menu.
Right-clicking a cell ( Format Cells or clicking Home ( Orientation ( Format Cell Alignment will open the Format Cells dialog box to the Alignment tab. Here you have finer control over the rotation of your text:
[pic]
You can control how the data in the cells will be aligned, choose if you want to wrap the text or shrink the data to fit in the cell, control the text direction, or manually select a rotation angle.
To rotate the text in the selected cells, put your mouse pointer on the red diamond in the orientation field. Hold your left mouse button down and drag the diamond to the degree that you need.
For example, if you want the text to be 45 degrees from the horizontal position, drag the red diamond until you see 45 in the Degrees text box. You can also type a value or use the small up/down arrows to adjust the value. When you have made your selections, click OK:
[pic]
Here’s what the text will look like:
[pic]
Creating Custom Number and Date Formats
Excel provides a variety of number and date formats for you to work with, but it may be the case that you require something different. You can create your own custom number and date formats in order to present your data exactly as you wish. Excel’s default number formats are visible in the Number group of the Home tab:
[pic]
To create a custom number format, select the cell or cells that contain numbers you want to format and open the Format Cells dialog box. Right-click the cell and click Format Cells or click the option button in the Font, Alignment, or Number groups on the Home tab. When the dialog appears, click the Number tab:
[pic]
You can see the number you are formatting in the Sample area of the box.
To create a custom format, select Custom from the bottom of the Category list. You will see a text window containing a list of symbolic formatting codes in the Type list. If you click on a symbolic formatting code, you will see what your number will look like with this formatting. This will be the starting point for your custom format:
[pic]
For example, let’s say that you want to format your number so there are two places after the decimal, and a dollar sign at the right side of the number. To achieve this, click the “#, ##0.00” format code from the list, and look at your number in the sample field. Now add a $ directly to the right side of the format code beneath the Type heading.
The format code for your custom number format will be saved at the bottom of the format code list in the Format cells dialog box. Click OK to format the cell or range of cells with this new custom format:
[pic]
Here is the new custom number format applied to the cell. Notice the difference between the value in the Formula Bar and what is displayed in the active cell:
[pic]
Lesson 4.2: Cell Formatting
When it comes to the quality and clarity of a workbook, layout and appearance both play major roles. Excel features a number of formatting features at your disposal, letting you create practical worksheets with a professional look and feel. This lesson deals with some of the cell formatting tools that can help you present your data in a polished and meaningful way. In this lesson, we will cover conditional formatting and the Format Painter. We will also learn about cell merging, AutoFit, and Find and Replace Formatting.
Conditional Formatting
Excel allows you to format data based on the values the data assumes at any given time. This is called conditional formatting and makes certain types of worksheets easier to read. It is useful for worksheets that show financial figures (black = positive, red = negative), test results (results change color based on their value), or any time you want to highlight the data that falls within a certain value.
To use conditional formatting, select a range of data that you want to format with conditional formatting:
[pic]
Now click Home ( Conditional Formatting:
[pic]
Here, we have added some data bars. These bars color the background of the cell based on the value of one cell compared to the others in the range:
[pic]
As you can see, the largest number is 3500 and the smallest is 1. Each number in between those values will be shaded accordingly. The amount of shading is maintained at a constant ratio, no matter how the cells are modified:
[pic]
There are several options available via the Conditional Formatting command. Let’s go over the basics of each option:
[pic]
Conditional formatting works using logical rules. Notice the commands at the bottom of this menu where you can define your own rules for conditional formatting, clear the rules currently in use, and manage existing rules (edit/delete).
The formatting options are:
|Highlight Cells Rules |This will highlight cells that are greater than, less than, between, or equal to specified values: |
| | |
| |[pic] |
|Top/Bottom Rules |This option will allow you to highlight the top or bottom numbers or percentages in the selected cells: |
| | |
| |[pic] |
|Data Bars |Will display colored bars that are indicative of the value in the cell. The more the cell is filled in, the |
| |higher its value compared to the other cells in the range: |
| | |
| |[pic] |
|Color Scales |Will use different shades of color to represent different values, from low to high: |
| | |
| |[pic] |
|Icon Sets |Will use sets of similar icons that will visually indicate a cell’s value: |
| | |
| |[pic] |
Notice that at the bottom of each submenu there is a command to choose additional rules in this category.
You can apply multiple conditional formatting rules to a group of cells by simply re-selecting the group and applying another condition. Here, we have added an icon set to the data that already contains data bars:
[pic]
Red arrows are low values, descending yellow means low range, ascending yellow means high range, and green arrows indicate high values.
If you click the New Rule option near the bottom of the Conditional Formatting submenu, the New Formatting Rule dialog box will appear:
[pic]
In this dialog box you can configure more precise and specific conditional formatting rules. Select one of the six rule types at the top, and then modify the details of how the rule will work at the bottom. The details for each rule type are different.
Let’s briefly go over each rule option:
|Format all cells based on |Apply formatting based on numerical values. Click the [pic] icon to select a value or values directly from the |
|their values |spreadsheet. |
| | |
| |[pic] |
|Format only cells that |Apply formatting to values between a high and low value. Click the Format button to define the cell formatting. |
|contain | |
| |[pic] |
|Format only top or bottom |Apply formatting to the top or bottom portion of the selected range. Click the Format button to define the cell |
|ranked values |formatting. |
| | |
| |[pic] |
|Format only values that |Apply formatting to all values that are above or below the average for the selected range. Click the Format |
|are above or below average|button to define the cell formatting. |
| | |
| |[pic] |
|Format only unique or |Apply formatting to all duplicate or unique values in the selected range. Click the Format button to define the |
|duplicate values |cell formatting. |
| | |
| |[pic] |
|Use a formula to determine|Apply formatting to all cells in the selected range that satisfy the equation. Click the Format button to define |
|which cells to format |the cell formatting. |
| | |
| |[pic] |
If you want to remove conditional formatting rules, click Conditional Formatting ( Clear Rules and then select what to clear:.
[pic]
The Manage Rules option will allow you to delete, edit, or add new conditional formatting rules.
[pic]
The Format Painter
The Format Painter is a very useful feature that helps you save time formatting. With the Format Painter, you can copy just the formatting from one cell and apply it to others in your worksheet. This is useful if you didn’t create the worksheet and don’t have the time to duplicate the formatting.
To use the Format Painter, select a cell and then click Home ( Format Painter:
[pic]
The cell that you selected will be enhanced with a flashing dark and light border and your mouse pointer will turn into a thick cross with a paint brush beside it. When this happens, any cell or range you select with the cross and paint brush pointer will assume the formatting of the cell that you selected. In other words, you “paint” your selection with the formatting from the original cell.
Let’s say you have formatted the Month cell a certain way, and we now want to copy that format to the Sales cell:
[pic] [pic]
Click Format Painter once to apply the copied formatting once. Double-click Format Painter to apply the copied formatting multiple times. Once you have finished applying formatting multiple times, click Format Painter once more to deactivate the command.
Cell Merging and AutoFit
Cell Merging is a feature that helps you change the physical layout of a worksheet. Occasionally, you may need a long cell for a heading, or you may need to resize your columns or rows so that long items can be displayed. If you need to put some text or a value into a cell but don’t have the available space to resize the row or column, you can use the merge command. This command allows you to select a group of cells, either across a row or down a column, and combine them to form one big cell.
To do this, select a group of adjacent cells and click the Merge & Center command in the Home tab:
[pic]
Here, the title is entered into A1, but spills over into B1 and C1. If you select A1:D1 and click the Merge & Center command, you can turn this:
[pic]
Into this:
[pic]
A1:D1 has been merged into one big cell and is referenced as A1. Cells B1:D1 do technically still exist (you can reference them in a formula), but you can’t easily access the data because it is behind A1. To avoid confusion, we recommend you assume that cells behind a merged cell are inaccessible.
If numerical data in a cell is too large for the available space, you might see a string of number signs (########). This means that the data is still there and usable, but is too wide to be displayed properly. If you have the room to expand the row or column size, use AutoFit.
AutoFit is the easiest way to change row or column dimensions in order to fit the contents. Just double-click the divider between adjacent columns or rows. Excel will automatically size the columns to fit the widest thing in a column or the highest thing in a row. Here, the text in B1 is too wide to be seen at once. Double-click the line between the B and C column headers and Excel will automatically widen column B to fit the contents:
[pic][pic]
Find and Replace Formatting
You can use the Find and Replace command to search your workbook for a specific term and replace it with something else. Find and Replace also lets you search for and replace certain types of formatting. This feature is ideal for replacing a formatting style that is scattered throughout a spreadsheet. To use Find and Replace with formatting, click Home ( Find & Select. When the Find and Replace dialog box appears, click the Replace tab.
[pic]
Click the Options button to display the options for formatting. Then, click the Format button and click Format:
[pic]
Here you can select a Number format from the category list and then specify the details of the format using the other tabs across the top of the dialog. Click OK to proceed:
[pic]
You can also choose a format from a cell by clicking the Choose Format From Cell button.
If you click Choose Format From Cell, you will see a thick cross mouse pointer with an eye dropper beside it. Click a cell with formatting that contains formatting want to find, and the Find and Replace dialog box will be displayed again. Next, make sure that the Replace tab on the dialog box is selected to show the options for choosing a replacement format. You can choose a replacement format in the same way that you chose a format to find:
[pic]
In the second half of the dialog, there are options in the list boxes to search worksheets, workbooks, and formulas. Once your options are set, click Replace to replace the unwanted format one cell at a time, or click Replace All to perform all of the replacements at once.
Lesson 4.3: Enhancing a Worksheet’s Appearance
Up until now, we have been dealing with formatting individual values or ranges of cells. In this lesson, we will learn how to further enhance a worksheet’s appearance by applying colors, patterns, borders, and fill effects. We will also learn what styles are and how to use them, and how to roll all of these concepts together by using a theme.
Adding Patterns and Colors
A few colors here and there will really help enhance the look and feel of your worksheet. For example, adding a few bright colors to headings or subtotals/totals will make the worksheet easier to read and draw the reader’s eye to what is important.
To add colors to a worksheet, select the range of cells where you want to add color. Here is a worksheet that has had some text formatting. It isn’t too hard to read, since the headings and totals have been turned bold. Let’s add some color to the totals at the bottom of the sheet:
[pic]
Click Home ( Fill Color arrow and move your mouse over the different colors. You can choose shades of colors from the theme you are currently using or you can choose from a selection of standard colors. Click a color to apply it to your selection:
[pic]
You can add patterns as well as colors to a selection of cells. To do this, select a range of cells, then right-click ( Format Cells:
[pic]
When the Format Cells dialog box appears, click the Fill tab, select a color, and choose a pattern from the Pattern Style combo box. You can see what the pattern will look like in the sample bar at the bottom of the dialog:
[pic]
As you can see, just one color and a pattern is enough to tell you exactly what the worksheet is about (Budget) and which numbers are important (the totals at the bottom):
[pic]
Adding Borders
Borders can help to distinguish selected data within a worksheet, or give a worksheet a more polished overall appearance. Although the gridlines dividing rows and column are turned on so you can see them in your worksheet, they are not printed.
To add a border to your worksheet, select a range of data and click the pull-down arrow next to Home ( Borders. Select type of border you wish to apply to the range (including no border), and the borders will be added:
[pic]
Whichever option you choose will be available by clicking the Border command again. For example, by default, the Border command is set to display bottom borders: [pic]
If you clicked the pull-down arrow and selected All Borders, the All Borders option would replace the bottom border on the command itself: [pic]
If you click More Borders at the bottom of the drop down menu, this will display the Format Cells dialog box - Border tab. Here you can customize a border by choosing line thickness and the line position in and around the cell. When you are finished building a custom border, click OK to implement it on the selected cells:
[pic]
Working with Styles
A style is a set of formatting options that can be given a name, saved, and applied to a cell or a range of cells. You can create a style or modify an existing style to build the formatting options you need for your worksheets. Excel provides a large menu of preset styles that you can choose to apply to your cells.
To use a preset style, select a cell or range of cells and choose a style by clicking Home ( Cell Styles:
[pic]
Click a style to apply the formatting:
[pic]
To remove applied styles from a cell range, select the styles cells and click Cell Styles ( Normal. This will remove the formatting by re-applying basic black and white formatting:
[pic]
To create your own style, enter data in a cell and apply the various effects that you need (such as font color, fill color, font size, bold, italic, etc.). Here, we have entered the word “Hello,” and selected bold, a font size of 14, a white font color, and a shade of purple for the cell color:
[pic]
Next, select the cell and click Cell Styles ( New Cell Style:
[pic]
This will open the Style dialog box. If you give the style a name and then click OK, the formatting you added to the selected cell will be saved as a style. If you click the Format button, you will display the Format Cells dialog box. This will allow you to add pretty much any kind of formatting you want to the new style. You can also select or deselect elements to include in your style with the various checkboxes. When you have confirmed the style elements to use, give the style a name and click OK:
[pic]
The new custom style will be listed at the top of the Cell Styles command:
[pic]
Styles are saved with the workbook they are created in. If you want to use a style from another workbook, the Merge Styles command will allow you to import the styles you need. To do this, you must have two workbooks open: one that contains styles you want to use (source) and one where you want to use the styles (destination). Switch to the destination workbook and Cell Styles ( Merge Styles:
[pic]
(We will discuss more about working with multiple workbooks later in this manual.)
The Merge Styles dialog will open. Select the workbook with the style you want and click OK:
[pic]
Excel will copy all of the styles from the source workbook into the destination workbook. If there are styles in both workbooks that have matching names, you will be asked if you want to merge styles.
Working with Themes
As you know, Excel offers a wide range of customization options to change the look and feel of your worksheet. All of the effects we have dealt with in this lesson can be collected together into a single theme. Themes allow you to apply coordinating font, colors, effects, styles, and other types of formatting at once.
To make the best use of themes, you should first apply styles to your data. Consider the following chart of information:
[pic]
All information has been correctly labeled and the totals are in place.
Using a number of styles from the Cell Styles command, we have formatted the worksheet to look like this:
[pic]
These styles were applied using the standard Office theme used by all new workbooks. You can view the current theme by clicking Page Layout ( Themes:
[pic]
If you move your mouse over the different themes, you will see the current styles in your worksheet change depending on the theme. For example, here is the worksheet using the Equity theme:
[pic]
Themes are pre-packaged with Excel, but as with just about everything else, they are fully customizable. You can change the color scheme, fonts, and effects for a theme by clicking and browsing through the other commands in the Themes group. You can pick a color scheme from one of the other themes as well as the fonts:
[pic] [pic]
If you click the Effects command, you can change the way objects such as WordArt, SmartArt, and diagrams look, as based on another theme:
[pic]
Keep in mind that changes to themes are applied to all worksheets in a workbook. You can’t apply individual themes to each worksheet.
Lesson 4.4: Working with Charts, Part 1
We have certainly come a long way in our exploration of Excel! In the final two lessons of this section, we will explore the last major component to help you complete your workbook – how to create and modify charts. After completing these two lessons, you are well on your way to being able to create functional and attractive worksheets worthy of any quarterly report or boardroom meeting!
If you look at a large table of figures, it can be very hard to figure out what is happening with the data. Conditional formatting will help, but sometimes a picture really is worth a thousand words. Excel features powerful charting tools to help you create a more meaningful representation of your data. In this lesson, we will learn how to create, format, and manipulate a chart.
Creating a Chart
Office 2007 featured a number of interface improvements that were designed to help you get more things done quickly. Office 2010 continues with this new style of interface. The ribbon interface with bright, colorful icons was a step away from traditional menus and submenus. The interface changes weren’t necessarily loved by all, but they did make a lot of Office’s features more accessible to new users.
One of the major changes in Excel 2007 was the way that charts were created and handled. Excel 2010 uses these same changes. Instead of going through a chart wizard (a series of dialogs that let you choose options), a professional-looking chart can be created in just a few clicks. The main charting tools are found on the Insert tab:
[pic]
Before you create a chart, consider the type of chart that you require. Pie charts and bar charts are good for showing comparisons. Line graphs can be useful for showing trends and plotting relationships between variables. Excel can produce three dimensional charts as well which may not be best for an internal report, but would be great for a Web site or promotional literature.
To create a chart, select the data that you want to use in the chart. This data should include some identifiers such as the row headings shown here. This is so Excel knows how to identify the data.
[pic]
Now click Insert ( Pie to view a list of possible pie charts. For this example, we will choose the exploded 3D pie chart:
[pic]
This action creates an exploded 3-D chart in the spreadsheet, showing comparative slices for the sales per region. Note that the data that was used to create the chart has been highlighted in the worksheet:
[pic]
Styling Charts with the Design Tab
Once a chart has been created, it’s not set in stone – you can modify everything related to the chart, including size, color, layout, visual effects, 3D effects, chart type, and even the data that was used to make the chart in the first place. In order to work with a chart, click the border surrounding the chart. Doing so will open a feature of Excel we haven’t explored in detail yet called contextual tabs:
[pic]
Contextual tabs appear when you are working with certain objects (i.e., you are working in context with them). There are three Chart Tools tabs: Design, Layout, and Format. These three tabs are only available when you are working with a chart. If you were to click elsewhere in the worksheet (deselect the chart), these tabs would disappear. Click anywhere in the chart again to bring them back.
Let’s go over the groups in the Design tab:
|Type |Use these commands to change the type of chart or save the current chart design as a template for future charts: |
| | |
| |[pic] |
|Data |Use the commands in the Data group to reverse the items on the X and Y axes. (Note that this command might make |
| |the chart nonsensical.) You can also redefine the cell range that provides source data for the chart: |
| | |
| |[pic] |
|Chart Layouts |To quickly add a new layout style to your chart, including the addition/removal of titles, a legend, or other |
| |features, use these commands. Click the pull-down arrow in the lower right-hand corner to select a layout: |
| | |
| |[pic] |
|Chart Styles |Excel features a number of colorful, pre-formatted chart styles. Pick one from the list to add an instant splash|
| |of color to the chart. Click the pull-down arrow in the lower right-hand corner to choose from a number of chart|
| |styles: |
| | |
| |[pic] |
| | |
| |[pic] |
|Location |Click this command to open the Move Chart dialog box. Here, you can move the chart to either a new worksheet in |
| |the workbook, or move the chart as an embedded object: |
| | |
| |[pic] |
| | |
| |[pic] |
|Mode |This command allows you to switch a chart/all charts between Draft Mode and Normal Mode. By default, every chart|
| |is displayed in Normal Mode. This means that special effects in the chart (shading, antialiasing, some 3D |
| |effects) are turned on. Draft Mode turns these effects off, which makes complex charts and graphs easier to |
| |render on slower computers. |
| | |
| |It is unlikely you will ever have a problem with rendering performance in Excel. But if you do notice |
| |significant computer slowdown (meaning performance seems sluggish due to a very large worksheet), it could be |
| |due to Excel rendering a very complex chart or graph. Turn on Draft Mode to gain some performance, and turn |
| |Normal Mode on when printing. |
| | |
| |[pic] |
| | |
| |[pic][pic] |
By now you will have noticed that you can customize just about anything to make it look the way you want, and charts are no different. Over the next few pages, we’ll look at how to customize the different chart elements.
Each element of the chart can be formatted using the contextual tabs or by using the right-click menu. For example, right-click the chart legend and choose Format Legend.
[pic]
A box will appear around the legend and the Format Legend dialog box will appear:
[pic]
There are lots of options available to modify just the legend. In fact, each element you can right-click in a chart has formatting options that are very similar to the ones shown here, so let’s go over the main categories:
|Legend Options |Choose the legend position relative to the outside chart border: |
| | |
| |[pic] |
|Fill |Select from a number of coloring options for the legend. These options would help separate the legend|
| |from the rest of the chart: |
| | |
| |[pic] |
|Border Color |Help visually separate the legend from the rest of the chart with these options: |
| | |
| |[pic] |
|Border Styles |Give the legend a border with these options. If your organization uses a consistent border style with|
| |letterheads, business cards, etc., you could duplicate the same look here: |
| | |
| |[pic] |
|Shadow |Apply 3D shading effects with these options. This might help make a 3D chart look even more vibrant: |
| | |
| |[pic] |
|Glow and Soft Edges |Further enhance the look of your legend by giving it a glowing border. This would be useful if your |
| |chart had a dark background: |
| | |
| |[pic] |
Try right-clicking the different objects in the chart. For example, if you right-click one of the exploded pie slices, you can modify how this and the other slices will look. You can also right-click the area just beside the visual chart components (this is the plot area) and change a number of settings, including 3D formats and rotation.
Finally, if you right-click the blank white area of the chart (around the sales heading and legend) and then select Format Chart Area from the pop-up menu, you will display the Format Chart Area dialog box. This box offers the same sort of formatting commands available by right-clicking other objects.
Modifying Charts with the Layout Tab
We know that when a chart is created in Excel, three contextual tabs will appear. The Design Tab is useful for quickly changing the look of the chart. Let’s move on to look at the Layout tab. This tab lets you control the items that are included in the chart, including labels, axes, and backgrounds.
The most important sections of the Layout tab (in terms of charts) are the Labels group, the Axes group, the Background group, and the Analysis group.
First, let’s take a look at the commands available here:
[pic]
To see how these tools work, take the following Excel chart as an example.
[pic]
Click the chart to select it, and then click the Chart Tools - Layout tab. Let’s go over the groups in this tab:
|Current Selection |We saw earlier that you can click the various elements in a chart to select them. If you click the combo box |
| |in this group, you can be specific and pick from all the various chart elements. You can then click Format |
| |Selection to open the appropriate dialog box for the selection, or click Reset to Match Style in order to keep|
| |all chart elements consistent: |
| | |
| |[pic] |
|Insert |Use these commands to insert a picture (like a company logo), select from a wide range of shapes (to highlight|
| |important data), or add a text box (for extra chart information): |
| | |
| |[pic] |
|Labels |These commands let you label each element of your chart. Click a command and pick a type of label for that |
| |category: |
| | |
| |[pic] |
|Axes |With these commands, you can label the axes of your chart and add gridlines to better define your data: |
| | |
| |[pic] |
|Background |You can add different markings to the background of your chart, depending on the type of chart. These markings|
| |would make it easier for someone to read the chart data, particularly on a large chart: |
| | |
| |[pic] |
|Analysis |These commands are very useful for calculating trends and patterns in your data and providing statistical |
| |data. Trendlines show things like averages from the data. Lines show items such as Drop Lines or High/Low |
| |lines. Up/Down Bars show the growth/shrink of a series of data. Error Bars show items such as standard error |
| |and standard deviation. |
| | |
| |[pic] |
|Properties |Give the chart a name. This is useful if you are dealing with multiple charts in a single workbook: |
| | |
| |[pic] |
Let’s add some items to the sample chart, starting with a title:
[pic]
The title will appear above the data. Click in the title and give the chart a meaningful name:
[pic]
Now let’s add a shape to highlight the record sales in the South:
[pic]
Once the shape is selected, click and drag in the chart to place the shape:
[pic]
The new shape opens another contextual tab, Drawing Tools ( Format. This tab appears whenever you have inserted a shape or drawing. Click the Text Box command, click and drag an area inside the shape, and type “Record!” to celebrate the sales:
[pic]
Click on a chart item again to bring back the Chart Tools contextual tabs.
Finally, let’s add some error bars to see the standard error for this chart:
[pic]
Depending on the element you add from the Layout tab, the chart will automatically adjust itself in order to make everything fit within the boundaries of the chart:
[pic]
As you can see, it is very easy to add additional information and labeling to your charts. However, there is such a thing as too much information! Don’t load up your chart with so much detail that people won’t be able to read it. If you do need to add a lot of extra detail, we suggest you make additional charts based on the same data, and then add groups of like chart enhancements together. Lastly, don’t forget about the Undo command! If the chart element you have added is wrong or doesn’t make sense, just undo the action and try something else.
Additional Styling with the Format Tab
There are a few more styling commands available in the Chart Tools – Format tab:
|Current Selection |Use this group to select individual elements of your chart and perform actions on them: |
| | |
| |[pic] |
|Shape Styles |Depending on the object that is selected, you can modify the border color and style, the fill (background) |
| |color, and add 3D/special effects to the element. Click the pull-down arrow to see a number of different |
| |styles: |
| | |
| |[pic] |
| | |
| |Click the option button to open the Format Chart Area dialog box. This gives you finer control over the chart |
| |area fill color, border color and style, 3D and special effects, size, etc. |
|WordArt Styles |WordArt is a type of stylized text that is great for titles or headings. Choose from a number of stylish and |
| |textured text effects using the pull-down arrow beside the styles. You can also select the font and outline |
| |color and add additional text effects: |
| | |
| |[pic] |
| | |
| |Click the option button to open the Format Text Effects dialog box. As you might have guessed, this dialog |
| |gives you finer control over the color, fill, style, etc. relating to the WordArt. |
|Arrange |The Arrange group lets you work with the physical objects in the chart area. Use these commands to decide how |
| |objects in the chart will be positioned. |
| | |
| |If objects overlap, you can decide which is on top of the other using the Bring Forward/Send Backward |
| |commands. The Selection Pane lets you select which object to work with in a worksheet (like multiple charts). |
| |Line items in the chart up with the Align command. You can Group several objects together as one, making them |
| |easier to work. Finally, you can Rotate objects in the chart if you need to. |
| | |
| |[pic] |
|Size |Adjust the width/height of the current chart or chart object using these commands: |
| | |
| |[pic] |
| | |
| |Click the option button to open the Format Chart Area dialog box to the Size tab. As you have probably |
| |guessed, there are more detailed size commands available here including scaling and reset tools. |
Manipulating a Chart
The chart is surrounded by a border. If you move to the edge of a chart border, your cursor will turn into a four-headed arrow. Click and drag an edge to physically move the chart around the worksheet. Click and drag a corner to make the chart larger or smaller:
[pic]
You can also click on individual elements within the chart, such as individual elements of the chart itself or the legend. For example, you can click and drag individual elements of an exploded pie chart and drag them towards or away from the collective center of the chart:
[pic]
If you want to make a chart an object in another worksheet or move the chart to a sheet of its own, click Chart Tools - Design ( Move Chart. You can also right-click the chart ( Move Chart:
[pic]
This will display the Move Chart dialog box.
[pic]
If you click New sheet, give the chart another name if you like and click OK. This will move the chart to a sheet all by itself, identified by the worksheet tabs:
[pic]
If you click Object in, select a worksheet and click OK. A copy of the chart will be embedded as an object into the worksheet.
Finally, to remove a chart from your worksheet, click in the chart area, and press the Backspace or Delete key on your keyboard.
Lesson 4.5: Working with Charts, Part 2
In the last lesson, we explored how to create and modify the visual look of a chart. In this lesson, we will explore the background manipulation of charts including how to apply a new chart type and change the source data. We will also learn how to create chart templates and how to work with the chart axes and data series.
Changing the Type of Chart
To change the chart type, click the chart and then click Chart Tools – Design ( Change Chart Type.
[pic]
This will display the Change Chart Type dialog box and highlight the current chart type:
[pic]
With this box, you can select a new chart type from the list on the left and then choose a chart style. Click OK to continue:
[pic]
Additional commands in this dialog include the ability to manage chart templates (which we will discuss in a moment) and choosing to make a certain chart type the default.
Note that not all chart types will do justice to your data. In fact, some chart types just won’t make any sense at all! You may need to experiment with some different chart formats in order to find one that makes sense. Remember that changing the chart type does not affect the source data, so feel free to switch chart types until you get it right.
Changing the Source Data
As we have just seen, Excel makes it easy to change the chart type for your data set. Excel also makes it easy to change the source data for your chart while retaining the original chart type.
In the example below, the pie chart is based on sales data per region:
[pic]
If you wanted to depict expenses per region, you would have to change the source data of the chart from the sales data (B2:B5), to the expenses data (D2:D5). To do this, right-click the chart and click Select Data:
[pic]
This will display the Select Data Source dialog box:
[pic]
At the top of the dialog box, the Chart data range field shows the range of cells that serve as the current chart data: cells A1:D5. (As you can see, the range includes dollar signs. We will explore what these dollar signs mean later; for now, just ignore them.)
To change the data source, use your mouse to select the new data range from the spreadsheet (D2:D5). You will see the new range entered into the Chart data range field. The chart itself will also change:
[pic]
If you added custom elements such as a chart title, you may have to change the title if you changed the source data. Otherwise, Excel takes care of all other updates and your chart is ready to go.
Working with the Chart Axes and Data Series
In a typical chart, the axes are the horizontal and vertical scales. Basically, data is charted with respect to its numerical position along the X or Y axis. A series is a group of data (normally a selection of cells) that is to be charted against an axis. You can have more than one series represented in a chart to show how the different series (selections of data) compare to each other.
To add more than one series to a chart, right-click on the chart and click Select Data from the menu that appears. When the Select Data Source dialog box appears, you will see buttons for adding and removing a series of data:
[pic]
To add a new series to the chart, click the Add button. This will display an Edit Series box where you can enter a name for the series in the name field that is provided. You can also edit an existing series (for example, by selecting more or less data) using the Edit button:
[pic]
If you click the [pic] button, you will be switched back the worksheet where you can select a range by clicking and dragging your mouse pointer. (You can also enter a data series by typing a range directly into the text fields, but selecting with the mouse is usually simpler.)
In the following stacked line chart, the Sales data and Profit data are represented against a Y axis consisting of dollar amounts.
[pic]
To control the chart axes, right-click on one of the dollar amounts on the Y axis and click Format Axis:
[pic]
This will display a Format Axis dialog box with the Axis Options heading highlighted on the left:
[pic]
The formatting categories on the left should look familiar by now! Here you have controls to specify the units and adjust the scale, tick mark, and position of the axis labels. Use any of the other headings on the left to modify the look and feel of the axis’ components.
Usually, the more information Excel has to create your chart, the better. If all of your source data is appropriately labeled, and you select those data labels before creating a chart, chances are the chart will be correctly labeled. However, if you want to change the labels on an axis, do the following.
First, type the new labels somewhere in your worksheet in the same order as the original labels (you’ll need them in a moment). This means that if the current headings (North, South, East, West) were written horizontally across four columns, type the new headings (A, B, C, D) horizontally across four columns somewhere else in the worksheet.
Next open the Select Data Source dialog box and click the Edit button under the Horizontal (Category) Axis Labels heading:
[pic]
The Axis Labels dialog box will appear and let you select the labels you want from the worksheet. Click [pic] and drag your mouse to select the appropriate cells, or manually type the cell range into the box provided:
[pic]
Saving a Chart as a Template
If you have spent a lot of time getting your chart just right, you might want to save the chart settings as a template. This will allow you to create another chart with the same formatting in just a few clicks, rather than spending a lot of time doing the same formatting over and over again. Templates allow you to save the chart type, colors, and formatting.
Consider the following chart style and layout that we want to save as a template:
[pic]
To save this chart as a template, click the chart and click Chart Tools – Design ( Save As Template:
[pic]
When the Save Chart Template dialog appears, choose a save location (the default template location is highlighted), give the template a name, and then click Save:
[pic]
When you want to use the template, select some data, choose a chart type (it doesn’t matter which), and click All Chart Types:
[pic]
When the Create Chart dialog appears, click the Templates option at the top of the list on the left and select your template. You can hover over each template for a moment to see which is which:
[pic]
Experiment with different charts and see what works best for you. However, keep in mind that the pre-set chart styles in Excel were very thoughtfully designed and implemented. It may be difficult to surpass this new, quick, and easy method of formatting charts.
Absolute and Relative Cell References
Earlier in this lesson we saw a cell range that contained some dollar signs. Throughout this manual, we have written the references without dollar signs, which indicate relative cell references. These dollar signs indicate absolute cell references. Absolute cell references are a way of making sure your charts and formulae are always referencing the right thing. Relative cell references can move around based on what is happening in the worksheet.
Let’s take a moment to discuss how the dollar signs work by looking at an example. Consider the following worksheet. It contains two values and a simple equation in C1:
[pic]
If we add a column between A and B, watch what happens to the formula:
[pic]
As you can see, Excel changed the formula from =A1+B1 to =A1+C1. Excel was able to change the relative cell references to make the equation work. Let’s remove the new column and add dollar signs in front of the column/row identifiers, making the cell references absolute:
[pic]
If we were to add a column between A and B, the formula would still adjust to its surroundings. That is, the formula would become =$A$1+$C$1, meaning if you had to add more data to your current worksheet, you could insert rows/columns easily.
But if we delete column A, watch what happens:
[pic]
As you can see, there is now a cell reference error because what was column A has disappeared, and column C is now column B, erasing the cell reference.
We will discuss cell references further in the more advanced level of Velsoft’s Microsoft Office Excel 2010 courseware.
Section 4: Review Questions
The easiest way to adjust a column is to:
A. Click the line separating the column letter from the letter to its immediate left
B. Double-click the line separating the column letter from the letter to its immediate left
C. Double-click the line separating the column letter from the letter to its immediate right
D. Right-click a cell ( AutoFit
Why might you rotate text in a cell?
A. To improve the appearance of your spreadsheet
B. To conserve space in your spreadsheet
C. To preserve a specified column width
D. All of the above
To add borders to a selection of cells:
A. You must draw them manually
B. You cannot add borders to a worksheet; a worksheet already contains borders
C. Click the Borders command on the Home tab
D. None of the above
The commands to create charts are found on the:
A. Insert tab
B. View tab
C. Formulas tab
D. Data tab
You can create a three dimensional bar chart by:
A. Choosing one from the different chart commands
B. Creating a chart and then clicking the 3D button
C. You can only create a 3D chart by customizing a 2D chart
D. You cannot create 3D charts in Excel
Which of the following statements is true?
A. You cannot reformat an existing chart
B. The AutoFit command enlarges a chart to fill your entire screen
C. You can use a maximum of two data sources in an existing chart
D. You can change the chart type that in an existing chart
What does the Merge & Center command do?
A. Merges all charts in a selection together
B. Merges selected cells together into one larger cell
C. Combines all formulas in a worksheet together
D. None of the above
What does Conditional Formatting do?
A. Formats cells based on a criteria that you create
B. Applies icons to data if they are above or below an average value
C. Colors cells differently depending on cell values
D. All of the above
Which of the following statements are true?
A. You can customize a chart and save it as a chart template
B. Once a chart type has been selected, you can change the chart type
C. One chart type does not suit all types of data
D. All of the above
Which of the following is not a default chart?
A. Scatter
B. Pie
C. Gantt
D. Area
Section 5: Printing and Viewing your Workbook
In this section you will learn how to:
▪ Use Normal view, Page Break Preview, Page Layout view, and Full Screen view
▪ Manage a single window
▪ Create, hide, or unhide a window
▪ Freeze a pane
▪ Split a worksheet to view multiple areas at once
▪ Manage multiple windows
▪ Switch between open workbooks
▪ Arrange windows
▪ Compare workbooks side by side
▪ Reset windows
▪ Use synchronous scrolling
▪ Save a workspace
▪ Print your workbook
▪ Use Print Preview
▪ Set printer options
Lesson 5.1: Using the View Tab
Excel has a number of different view options that make viewing your workbook easier in certain situations. For example, if you are ready to print, you probably don’t want to view your workbook as a normal spreadsheet because it can be hard to tell exactly where your printed pages will begin and end. Sometimes, you may want to view a larger section of the grid area, or you may want to view several spreadsheet pages at once. In this lesson, we will explore the different commands on the View tab.
Using Normal View
The first of the different views is Normal view, the default view used by Excel. This view displays the ribbon, Quick Access Toolbar, status bar, and as much of the worksheet that will fit using the current zoom level. Chances are you will use this view most often:
[pic]
The Workbook Views group in the View tab lets you switch between all of the different views. When you start Excel, Normal view will be highlighted:
[pic]
You can switch between views using commands on the status bar as well. From left to right, you can switch between Normal, Page Layout, and Page Break views:
[pic]
You can also adjust which elements of the worksheet will be visible by checking/unchecking the commands in the Show group of the View tab:
[pic]
Using Full Screen View
Normal view gives you access to all of the commands and tools needed to modify your worksheet. However, sometimes you won’t be able to see all of your data at once, meaning you might have to zoom out a bit or use the scroll bars. You might also need to give an impromptu presentation about your work in Excel.
To help with these issues, Excel features a Full Screen view. To switch to this view, click View ( Full Screen:
[pic]
This view will expand the working area of your worksheet to fill your entire screen, regardless of if the Excel window was maximized or not. Full Screen view is designed to show as much data as possible. Only the working area, title bar, worksheet tabs, and scroll bars will be visible:
[pic]
Although the ribbon commands are not visible, you still have access to a number of commands using the right-click menu. Full Screen view could therefore be used for some editing tasks; however, editing is not recommended because the formula bar is not visible.
To exit full-screen mode, press the Esc key on your keyboard. You will return to Normal view.
Using Page Layout View
Page Layout view lets you view your worksheet as if it were printed on paper. This view is similar to the Print commands we look at in Lesson 3.1. To see this view, click View ( Page Layout or click the middle view button on the status bar:
[pic] [pic]
Excel outlines which column/row headings will print on which page, effectively splitting up your worksheet into page-sized pieces:
[pic]
The amount of data that can fit on a page is relative to the paper size. You can define the paper size by clicking File ( Print and choosing a paper size:
[pic]
Note that if you switch back to Normal view, you will see dotted lines appear between some of the columns or rows. These lines indicate the page breaks where Excel will split the data during printing:
[pic]
Page Break Preview
Excel worksheets can get very large. In fact, most real worksheets contain too much data to fit on one printed page. To print a large worksheet, you have to break up the data into manageable sections. The point where one continuous sheet of data is broken into separate pages is called a page break.
If you print an Excel worksheet that is too big for a single page, Excel will define page breaks for you based on the size of the cells, the size of the paper, and the print scale you choose. However, Excel doesn’t care about the meaning or interpretation of your data when it sets up page breaks, meaning the data can be broken into pages in awkward, illogical ways. This is why it is a good idea to learn how to manage page breaks on your own.
Click View ( Page Break Preview to display page breaks. You will see a notification appear stating you can manually adjust the page breaks in order for your printed data to make sense:
[pic]
Page breaks appear as dotted blue lines. The solid blue lines indicate the boundaries of the information that will be printed. You can also see a faint outline of the printing order (Page 1, Page 2, etc.):
[pic]
Page Break Preview still provides full functionality, but is not that great for actually working with your data. This view is designed to help you organize your spreadsheets for printing.
Lesson 5.2: Managing a Single Window
When you open a workbook in Excel, the actual working area (the cells) is defined as its own region. This working area is bounded by a border and can be minimized, closed, or resized independently of the Excel program itself. This self-contained working area is a window inside Excel.
Here, you can see a workbook represented as a window within the Excel screen window:
[pic]
You can have multiple windows for the same workbook open at the same time, or multiple windows representing different workbooks open at the same time. In this lesson, we will learn how to create a new window, hide a window, unhide a window, and how to freeze panes in a window.
Creating a New Window
It is easy to create a new window. If you have a workbook open, click View ( New Window:
[pic]
This will create a new window based on the same workbook. Each version of the workbook will be numbered as filename:2, filename:3, etc. Here you can see the two file names, of which budget:1 is the original:
[pic]
Each window has its own minimize, maximize, and close buttons:
[pic]
This allows you to minimize, restore, and close each window individually.
Minimized windows inside Excel appear as tabs along the bottom of the Excel window:
[pic]
In addition to the standard window controls, the windows can be individually resized by dragging their respective borders. If you have two or more windows open for the same workbook and you change or modify data in one window, the data will be changed in all of the other windows as well.
Hiding a Window
To hide a window from view, click the Hide button on the View tab.
[pic]
All traces of the currently active window will disappear from your screen, but it is not really gone. The hide feature is great if you have multiple windows open, and you want to put a window (or a few windows) aside for a moment for less clutter, or perhaps to prevent accidental modifications or closure.
Unhiding a Window
Click the Unhide button (right below Hide) to show a list of any hidden windows. Select a window from the list and click OK:
[pic]
Freezing a Pane
It is sometimes convenient to be able to keep an eye on one part of a spreadsheet while simultaneously viewing other parts of the same spreadsheet. (For example, you could keep cells with headings in place while scrolling through long lists of data.) If you want to see multiple parts of your worksheet at the same time, you can segment the workbook into more than one viewing area by using the Freeze feature.
To use Freeze, open a workbook window, and click the Freeze Panes button on the View tab:
[pic]
|Freeze Panes |If you select some cells before clicking this option, everything to the left and above the selection |
| |will remain frozen. This is useful if you want to keep any row and/or column headings in place while |
| |you scroll through the data. |
|Freeze Top Row |Many top rows in a worksheet contain column headings. Use this option to keep column headings visible |
| |while scrolling vertically through the data. |
|Freeze First Column |This will freeze the first column. Use this option to keep row headings visible while scrolling |
| |horizontally through the data. |
When you are finished using the Freeze Panes command, click Freeze Panes ( Unfreeze Panes:
[pic]
This will unfreeze any of the Freeze Panes options.
Splitting a Worksheet
The Split command allows you to view four different areas of a worksheet at the same time. It is similar to the Freeze command, except that you have the option to scroll through the parts of the window that are frozen. This command is useful if you have several tables of data that are used to create a chart; you can view the chart as you modify values in the source data.
Before adding a split, click somewhere in your source data. The split will be added around this cell. Next, click View ( Split. This command acts like a toggle. Click to activate/deactivate:
[pic]
Here is some data with a selected cell:
[pic]
After clicking Split, you will see these borders appear around the selected cell, dividing the worksheet into four parts:
[pic]
To remove the split, click the Split command again. This will remove the split. To move the split, click and drag the center of the divider wherever you like:
[pic]
Each split does not operate independently:
▪ The top left/top right sides share the same vertical scroll bar.
▪ The bottom left/bottom right sides share the same vertical scroll bar.
▪ The top left/bottom left sides share the same horizontal scroll bar.
▪ The top right/bottom right sides share the same horizontal scroll bar.
[pic]
Lesson 5.3: Managing Multiple Windows
If you find that you frequently open a number of workbooks at the same time, Excel has some useful features that may help you manage your windows and workbooks. In this lesson, you will learn how to switch between open workbooks, how to arrange workbooks, how to compare workbooks side by side, and how to reset a window.
Switching Between Open Workbooks
If you have multiple workbooks open at the same time, they are all accessible from the same Excel window. To switch between open workbooks, use the icons on the taskbar to switch between the different files:
[pic]
You can also click View ( Switch Windows. Whichever file name has a check mark beside it is the one that is currently visible; click an unchecked name to view that file:
[pic]
Arranging Workbooks
We learned earlier that you can freeze panes or use a split to examine two or more parts of the same worksheet at the same time. If you want to compare two or more workbooks at the same time, click View ( Arrange All:
[pic]
This will open a dialog that lets you choose how to arrange the windows:
[pic]
Imagine that you had three open workbooks. Here is a sample of what each one of the arrangements would look like:
|Tiled |[pic] |
|Horizontal |[pic] |
|Vertical |[pic] |
|Cascade |[pic] |
Comparing Workbooks Side by Side
Chances are if you are going to compare documents, you are going to look at two of them side by side. The Horizontal/Vertical alignment options shown earlier are useful but only if you have two workbooks open at once. Excel therefore features a command just for side by side comparison.
With two or more files open, click View ( View Side by Side. A dialog will appear that allows you to select one other file to compare side by side with whatever file you are viewing at the time. For example, if you were viewing a file named “budget” but also had “sales” and “source workbook” open as well, clicking View Side by Side would show the following:
[pic]
Pick a file to view side by side with “budget” and click OK. The two files will be shown together:
[pic]
Synchronous Scrolling and Resetting a Window
If you have clicked View Side by Side, Excel will automatically use Synchronous Scrolling. This means that if you scroll up and down in one workbook, the other workbook will also scroll. To enable or disable this feature while comparing workbooks, click this command:
[pic]
When you use the side by side view, you can also use the Arrange All button to arrange the windows you are comparing. This allows you to switch between vertical or horizontal side by side comparison. You can also resize and reposition the two windows you are comparing manually, while maintaining View Side by Side functionality. If you do resize the windows manually and you want to return to the original side by side window configuration, click the Reset Window Position button.
[pic]
Clicking this button will return your two windows to the original (View Side by Side) position that you started with.
Saving a Workspace
If you often work with multiple workbooks and windows, it may be a good idea to use a workspace. When you save a workspace, all of the workbooks that you have open at the same time will be linked together.
To save a workspace, click View ( Save Workspace:
[pic]
The workspace will be saved with an .xlw extension and can later be opened just like a regular workbook. When you open a workspace, Excel will open all of the workbooks you were using when you created the workspace.
If you make changes to a workbook as part of a workspace, and then save the workspace, you will see a prompt to save the changes you made. If you do save the changes, the workbook file will be changed on an individual basis. This means that even if you opened the workbook individually (not as part of the workspace you saved), the workbook will now contain the changes that you made to it.
Lesson 5.4: Printing your Workbook
It is often the case that printouts of your workbook (or parts of your workbook) will be required for one purpose or another. This is especially true for the workplace, where hard copies of data are often requested. Needless to say, knowing how to print data from your workbook is essential. In this final lesson, we will explore how to use the printing functionality in Excel.
Print Commands
There are a few ways you can print your document. First, you can add the Quick Print icon to the Quick Access toolbar. The Quick Print icon will send the document directly to the default printer:
[pic]
The more conventional method is to use the Print section of the File menu, which is accessible by clicking File ( Print or pressing Ctrl + P.
Print Preview
When you open the Print options by clicking File ( Print, you will see a preview of what your printed document will look like on the right-hand side of the screen:
[pic]
This view provides a scaled-down version of what your document would look like if it were printed as is. You can flip through the pages by using the arrows at the bottom of the screen. There are also two commands in the bottom right-hand corner. Click the left command to show margins, and click the right one to zoom in to 100% zoom:
[pic]
Using Basic Print Options
Open the Print options by clicking File ( Print:
[pic]
If you have a printer installed, turned on, and paper ready to go, just click the big Print button near the top of the window.
To select a different printer, click the pull-down arrow under the Printer heading and make a selection:
[pic]
You can adjust printer settings by clicking the link under the Printer command:
[pic]
These commands will depend on the capabilities of your printer. We will explore printer options later in this lesson.
Under the Setting heading, there are a few more basic commands. If you click Print Active Sheets, you will have a choice over which pages in the document will be printed:
[pic]
Let’s go over these three options:
|Print Active Sheets |Prints the entire worksheet. |
|Print Entire Workbook |Prints all worksheets in the current workbook. |
|Print Selection |If you have only selected part of a table of data or a chart, you can specify to only print that |
| |information. Use this when you only need to print a specific thing. |
Other Print Options
Let’s go over the other print options that are available. The commands listed below are not the proper command names; rather, they are the default options when you first click File ( Print. Many of these commands are the same as those in the Page Setup group of the Page Layout tab:
[pic]
|Print Active Sheets |Choose what to print (current worksheet, all worksheets, or the current selection). |
|Pages |Choose which pages to print. |
|Collated |If you have a 3 page document and wanted to print 2 copies, the pages would come out 1, 2, 3, 1, 2, |
| |3. If you printed uncollated, the pages would print 1, 1, 2, 2, 3, 3. |
|Portrait Orientation |Change the page orientation from portrait to landscape. |
|Letter |Change the paper size. |
|Normal Margins |Change the page margins. |
|No Scaling |Choose how large the print should be. You can print at 100%, fit the worksheet to fit on one page, |
| |fit all columns on one page, or fit all rows to fit on one page. |
Setting Printer Properties
To set options specific to your printer, click the Printer Properties command under the Printer heading:
[pic]
A properties window specific to your printer will appear:
[pic]
Typically, you will have options for color settings, page size, and paper type, though every printer is a little different. Make any changes you like and click OK to apply them.
Note that a printer doesn’t always necessarily have to be a physical thing sitting on your desk or in a room down the hall. Computers can make use of “soft printers,” or software that can print a file into a different file format. Excel is also capable of “printing” to other Office 2010 programs, such as OneNote, a program used like a big notebook to record information for at-a-glance use.
Section 5: Review Questions
What is generally the most commonly used view in Excel?
A. Normal view
B. Page Layout view
C. Page Break preview
D. Print Preview
Which of the following is not a view in Excel?
A. View Side by Side
B. Full Screen
C. Outline
D. Custom
Which view should you use if you want to see more of the Excel grid area?
A. The full screen view
B. The Normal view
C. The Page Layout view
D. None of the above
The Switch Windows button allows you to…
A. Switch between open windows
B. Switch between worksheets
C. Switch between workbooks
D. All of the above
What is the purpose of hiding windows?
A. To reduce clutter in your workspace
B. To prevent accidental modification of your data
C. To prevent formula errors
D. A and B
What is the Quick Print menu item for?
A. To quickly print data without special preparation or formatting
B. To make your printer print faster
C. To add headers and footers to your pages
D. None of the above
Which of the following items is Excel capable of printing?
A. The current worksheet
B. All worksheets in a workbook
C. A selection from a worksheet
D. All of the above
What extension do Excel workspaces use?
A. .xlt
B. .xlsx
C. .xlw
D. .wks
Which of the following is true concerning the Split command?
A. The top right and bottom left areas of the split use the same horizontal scroll bar
B. You can move the split by clicking and dragging the split center point
C. The top left and top right areas of the split use the same horizontal scroll bar
D. A split does the same thing as freezing panes
Most of the controls for window management are…
A. On the Insert tab
B. On the Design tab
C. On the Office menu
D. On the View tab
Index
Active Cell 2, 10, 11, 27, 31, 32, 33, 34, 36, 72, 89, 92, 93, 105, 106, 116, 117, 150
Adding a Border 169
Adding a Cell Pattern 168
Adding a Fill Color 166
Aligning Data 142
AutoFit 162
Automated Features
AutoComplete 98, 102, 103
AutoCorrect 88, 121, 127, 128, 130
AutoFill 88, 98, 100, 119, 123, 124
AutoSum 72, 98, 101, 102
Backstage 5, 6, 53, 54, 62, 63, 97
Cell Merging 161
Cell Range 89
Cell References
Relative vs. Absolute References 212
Charts 179
As an object in another worksheet 198
Axes 188
Changing axis labels in 207
Changing the source data 202
Changing the type 200
Choosing the right chart type 202
Creating a chart 180
Design Tab 200, 210
Formatting 195
Formatting the chart area 188
Formatting the legend 185
Layout Tab 188
Normal vs. Draft Mode 184
Saving as a template 210
Series in 205
Types of charts 179
Clipboard 109
Closing Excel 12
Column 3
Comments 84, 133
Adding comments 133
Editing a comment 136
Conditional Formatting 139, 151, 152, 156, 159
Creating custom rules 156
Icons 156
Contextual Tabs 10, 181, 185, 188, 193
Creating a Custom Number Format 148
Custom Actions 88, 119, 121
Custom Action Options 121
Data Tab
Commands 81
Deleting
Columns 95
Rows 94
Worksheets 95
Deleting Data 93
Dialog Boxes
Help in 50
Editing Commands
Changing the size of a row or column 140
Cut, copy, and paste 107
Deleting rows 115
Drag and drop cells 106
Format Painter 139, 160
Inserting cells 116
Inserting columns 114
Inserting rows 115
Paste Special 110, 111, 112, 113
Redo 118
Repeat 118
Undo 117
Entering Data 92
Error Bars 191
Error Option Button 122
File Format Change 21
File Types Associated With Excel 2010 22
Find and Replace 131
Formatting 162
Formula
Circular references 105
Formula Bar 11, 33, 92, 93, 95, 103, 150
Formula Errors 123
Formulas
Entering a formula 103
Valid formulae 103
What are formulas? 103
Formulas Tab
Commands 79
Help
Browsing 42
In a dialog box 50
Online vs. Offline Help 46
Opening 40
Searching 45
Table of Contents 47
Toolbar 44
Home Tab 32, 94, 144, 148, 161
Commands 68
Hyperlinks 75
Insert Tab 179
Commands 73
Merge & Center 143, 161, 215
Microsoft Excel XML Format 21
Mini Toolbar 94
Name Box 10, 11, 31, 33, 79, 89, 90
Number formats 147
Opening Excel 7
Opening Workbooks 16
Outline 82
Page Layout Tab 86
Commands 76
Paste Options 125
Paste Special 88, 106, 110, 111, 113, 137
Pivot Chart 6
PivotTable 6
PowerPivot 6
Printing 97
Commands 238
Dialog 238
Print options 243
Print Preview 239
Quick Print 238
Quick Access Toolbar 9, 19, 53, 54, 56, 57, 62, 86, 87, 117, 118, 217, 238
Customizing 59
Moving 58
Recent Files
Opening 64
Review Tab 86, 87
Commands 84
Ribbon 4
Row 3
Save As 19, 20, 63, 65, 210
Saving Workbooks
Save commands 19
Screen Capture 6
Selecting Cells 33
SharePoint 5, 65
Slicer 5, 74
Spark Lines 5
Spreadsheet 3
Storing Numbers as Text 95
Styles 170
User Interface 9
Command description 10
Contextual tabs 10
Ribbon 10
Status bar 11
Tabs 9
Worksheet tabs 11
Using Save As 21
Using Spell Check 128
View Tab 217, 223, 226
Views
Arranging windows 232
Full Screen view 216, 218
Normal view 217
Page Break preview 216, 223
Page Layout view 216, 220
Resetting a window 236
Side by Side 234
Switch Windows 231
Synchronous Scrolling 235
Using zoom 27, 36, 37, 39
View tab 86, 214, 228, 247
Zoom slider 37
Web browser 1, 44, 65
What-If Analysis 82
Windows
Creating 225
Freezing 227
Hiding 226
Showing 227
Splitting 229
Workbooks
What are workbooks? 14
Worksheets
Components of 89
Creating labels for 92
Inserting, deleting, renaming 28
Tabs 27
What are 27
Workspaces 216, 236, 237
XPS 6, 26, 65
-----------------------
Fully Customizable
Print on Demand
Unlimited Number of Users
No Annual Renewal Fees
Student Manual
Microsoft Office 2010 Training Material
Excel 2010 Foundation
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.