Excel

(1)

2 Warning All gtslearning products are supplied on the basis of a single copy of a course per student. Additional resources that may be made available from gtslearning may only be used in conjunction with courses sold by gtslearning. No material changes to these resources are permitted without express written permission by a director of gtslearning. These resources may not be used in conjunction with content from any other supplier. If you suspect that this course has been copied or distributed illegally, please telephone or email gtslearning.

Tel: +44 (0)20 7887 7999 Fax: +44 (0)20 7887 7988 e-mail:

[email protected]

Acknowledgements

www.gtslearning.com

Course Developer ... John Laska

COPYRIGHT

This courseware is copyrighted © 2010 Velsoft Courseware & gtslearning. No part of this courseware or any training material supplied by gtslearning International Limited to accompany the courseware may be copied, photocopied, reproduced, or re-used in any form or by any means without permission in writing from a director of gtslearning International Limited. Violation of these laws will lead to prosecution.

All trademarks, service marks, products, or services are trademarks or registered trademarks of their respective holders and are acknowledged by the publisher.

LIMITATION OF LIABILITY

Every effort has been made to ensure complete and accurate information concerning the material presented in this course. Neither gtslearning International Limited nor its agents can be held legally responsible for any mistakes in printing or for faulty instructions contained within this course. The publisher appreciates receiving notice of any errors or misprints.

Information in this manual is subject to change without notice. Companies, names, and data used in examples herein are fictitious unless otherwise noted.

Where the courseware and all materials supplied for training are designed to familiarise the user with the operation of software programs and computer devices, the publisher urges the user to review the manuals provided by the product vendor regarding specific questions as to operation.

There are no warranties, expressed or implied, including warranties of merchantability or fitness for a particular purpose, made with respect to the materials or any information provided to the user herein. Neither the author nor publisher shall be liable for any direct, indirect, special, incidental, or consequential damages arising out of the use or the inability to use the contents of this course.

DISTRIBUTOR

This courseware is distributed by gtslearning, the market-leading worldwide provider of blended learning solutions. A global network of commercial and academic education centres benefits from quality learning materials and support resources, optimised for instructor-led, self-paced, and e-learning delivery.

[email protected]

+44 (0)20 7887 7999 +44 (0)20 7887 7988

Three Elysium Gate, 126-128 New Kings Road

London, SW6 4LZ, United Kingdom

(2)

3

Introduction

Welcome to 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 Courseware 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 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.

(7)

8

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

(8)

9

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:

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:

(9)

10 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:

Easier

collaboration via SharePoint

Excel 2010 is capable of interacting with a SharePoint server. This allows for easy sharing and storage 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:

(10)

11

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:

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.

SkyDrive and Expanded File Types

Using the Backstage view, you can save a document to the Windows Live SkyDrive 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.

(11)

12

Pivot Table and Pivot Chart Improvements

PivotTables and Pivot Charts are a way to examine a group of data in a number of different ways. As the 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:

You can also click Start All Programs Microsoft Office Microsoft Excel 2010:

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

(12)

13

“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:

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:

(13)

14

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:

(14)

15

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:

4 Name Box Every cell has a name in the format <ColumnRow>.

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.

(15)

16

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:

Closing Excel

If you look in the upper right-hand corner of the Excel window, you will see two close buttons:

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:

(16)

17

You can also close Excel by clicking File Exit:

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.

(17)

18

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:

As you can see, this new file is given the default name of “Book1” and the three worksheets are highlighted at the bottom.

(18)

19

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:

This will create a new file named Book2, Book3, etc. A new icon will be added to the Windows taskbar:

You can also press and hold Ctrl and then press N to create a new workbook. (This keyboard shortcut is denoted as Ctrl + N.)

(19)

20

Opening a Workbook

To open an existing workbook when Excel is not open, just double-click the file name:

To open an existing workbook while Excel is open, click File Open:

You will then be required to browse your computer to find the document. Select it and click Open:

(20)

21

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:

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:

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:

(21)

22

Save Save As

New File You will be prompted to give the

file a name and choose a save location. You can also specify a file type.

You will be prompted to give the file a name and choose a save location. You can also specify a file type.

Existing File Any changes you made will be

applied to the existing file in its current location.

You have the option to give the file a new name and/or a 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:

The Save command is also found in the Quick Access Toolbar:

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.

(22)

23

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, OpenOffice.org documents, and data output files.

(23)

24 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!

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 (.xlsx)

Default format for Excel 2010 and Excel 2007. Add new authors or tag the file. You can also save a 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:

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 Workbook (.xlsm)

Excel workbooks with macros. Macros are short, specific pieces of code that allow the document to perform some functionality, such as accessing data from a database file.

(24)

25

Excel Binary Workbook (.xlsb)

This option is the same as the default Excel Workbook option, only the file is saved in binary form 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 (.xls)

The format for Excel 97-2003. (Extra options same as above.)

XML Data (.xml)

Saves the file in raw XML form. In order to use this format, the workbook must contain XML mappings. (Extra options same as above except no thumbnail.)

Single File Web Page (.mht)

All information in the workbook is saved in a single Web page archive. You have the ability to save 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:

Web Page (.htm)

Saves the workbook as an HTML file, along with a folder that contains any supporting files associated with the workbook like pictures or graphs.

Excel Template (.xltx)

Template for Excel 2007 and 2010. A template is a pre-formatted file designed to be used over and over, meaning you don’t have to keep re-creating the same formatting and file structure each time you make a certain file.

(25)

26

Excel Macro-Enabled Template (.xltm)

Template for Excel 2007 and 2010 that contains macros.

Excel 97-2003 Template (.xlt)

Template for Excel 97-2003. (Extra options same as above.)

Text (Tab delimited) (.txt)

This option is only capable of saving one

worksheet at a time. Text is entered with one row equaling 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.

Unicode Text (.txt)

Computers basically deal directly with numbers. The problem with so many different computer 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.” (http://unicode.org) (Extra options same as above.)

XML

Spreadsheet 2003

(.xml)

Saves the file in an XML format which is compatible with Excel 2003.

Microsoft Excel 5.0/95 Workbook (.xls)

Contains XML information about the Word 2010 or Word 2007 document.

CSV (Comma delimited) (.csv)

Stands for Comma Separated Values. This style of delimited file uses commas instead of tab spaces. Like tab delimited files, it is only capable of saving the data on a single worksheet.

(26)

27

Formatted Text (Space delimited) (.prn)

This format is another type of plain text usable in other computing environments. Only usable with a single worksheet.

Text

(Macintosh and MS-DOS)

(.txt)

Plain-text file for use in Macintosh or MS-DOS environments. Only usable with a single worksheet. (Extra options same as above.)

CSV

(Macintosh and MS-DOS)

(.csv)

CSV file for use in Macintosh or MS-DOS

environments. Only usable with a single worksheet. (Extra options same as above.)

DIF (Data Interchange Format) (.dif)

This file type is used to export single worksheets between different spreadsheet programs. Only usable with a single worksheet.

SYLK

(Symbolic Link) (.slk)

This file type is also used to exchange data

between various applications including spreadsheet programs. Only usable with a single worksheet. (Extra options same as above.)

Excel Add-in (.xlam)

This type of file would be used to add extra functionality and tools, mainly via the use of macros. Used with Excel 2007 and 2010. (Extra options same as above.)

Excel 97-2003 Add-in

(.xla)

This type of file is also used to add extra functionality and tools, mainly via the use of macros. Used with Excel 97-2003.

PDF (.pdf)

Stands for Portable Document Format. PDF files work by creating a snapshot of a file, just as if you 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.

(27)

28 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 (.xps)

Stands for XML Paper Specification. XPS documents are Microsoft’s answer to PDF documents.

OpenDocument Spreadsheet (.ods)

OpenOffice.org is an open-source productivity suite designed to be a free alternative to the Microsoft Office suite of applications (and other similar suites). Excel 2010 is capable of creating spreadsheet files that are compatible with the OpenOffice.org 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:

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:

(28)

29

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:

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:

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:

(29)

30

To add more worksheets to your workbook, click the new tab command:

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:

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:

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

(30)

31

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:

Tab Color You can color tabs in your workbook to help differentiate

(31)

32

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:

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:

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):

(32)

33

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:

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:

(33)

34 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.

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:

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:

(34)

35

Here, 10 rows and 5 columns worth of cells are being selected:

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:

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:

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:

(35)

36 Click and drag multiple row or column headings to select multiple rows/columns:

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 & Page Down

Press Page Up to move the active cell up one screen’s worth of cells. Press Page Down to move the active cell down the same amount.

(36)

37

keys 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:

Consider the following spreadsheet, which contains quite a lot of data. At 100% Zoom, we won’t be able to see all the data:

(37)

38 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:

Here is the same set of data at 50% zoom, which is small enough for us to see everything:

If you click the current zoom amount (for example, 50%), you can choose between common zoom levels or enter your own:

(38)

39

Choose one of the radio buttons or enter a custom amount, and then click OK.

(39)

40

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:

Using the Help Screen

(40)

41

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:

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:

(41)

42 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:

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 Office.com. 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.

(42)

43

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 Move back one step at a time through the help topics you

have previously read.

Forward 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 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 Use this button to reload the information on the current page.

Home Click this button to return to the main list of information you

saw when you opened the Help file.

Print Prints the current topic.

Text Size Use this to make the text in the help file larger or smaller:

(43)

44

Table of Contents

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

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

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 Office.com) you should get search results that look something like this:

(44)

45

Click the pull-down arrow beside the Search command for more search options:

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

(45)

46 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:

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 ( ) on the Help toolbar. Your Help screen will then look like this:

(46)

47

One thing you can do is to click items in the TOC to see the articles on the right side of the window:

When browsing the Help file, click items on the right side to see their relation in the tree structure of the TOC:

(47)

48 To navigate through the table itself, simply click on topics to expand them, and then click the link to view the topic.

Here is what the different icons mean.

Closed Book _{Click the book to expand the list of topics.}

Open Book Click the book to collapse the list of topics.

Help Topic _{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.

(48)

49

Section 1: Review Questions

1. _{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

2. An Excel workbook is (usually) composed of:

A. One or more worksheets B. Cells

C. Rows and columns D. All of the above

3. _{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

4. _{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

5. _{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

6. _{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

(49)

50 7. _{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

8. _{In Excel, rows are indexed by:}

A. Numbers B. Letters C. Icons D. Colors

9. _{In Excel, columns are indexed by:}

A. Numbers B. Letters C. Icons D. Colors

10. _{Excel is commonly referred to as a:} A. Spreadsheet program

B. Word processor

C. Integrated development environment D. Graphical user interface

(50)

51

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

(51)

52

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 Office icon.

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.

(52)

53

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:

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:

(53)

54

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:

The formatting command will be added to the right-hand side of the QAT:

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:

(54)

55

Removing Commands

To remove commands from the QAT, right-click any command and click Remove from Quick Access Toolbar:

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:

This will add the QAT between the ribbon and the Name/Formula bars:

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

(55)

56 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:

You can also click File Options. When the Excel Options dialog opens, click the Quick Access Toolbar link on the left:

(56)

57

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:

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 <Separators> 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.

(57)

58 Once the command is added, we will move it up two positions using up button:

Click OK to commit the changes. The QAT will be updated:

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:

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:

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

(58)

59

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:

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.

(59)

60

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:

If you click this checkbox, the first few workbooks in the Recent list (default 4) will be shown under the File Management area:

New Create a new blank workbook or choose from a number of installed templates. More templates are available via download from Office.com. To create a new blank file or a file from a template, select an option and click Create:

(60)

61

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:

Share 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 a workbook in PDF/XPS format: These two file

formats are widely used and easy to e-mail.

Save to Windows Live SkyDrive: 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.

(61)

62

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:

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:

Exit Closes the program. You will be asked to save any changes to

opened documents if you haven’t already done so.

(62)

63

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:

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:

(63)

64

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.

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.

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.

(64)

65

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.

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.

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.

(65)

66

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.