01 excel session 01-1

7/27/2019 01 Excel Session 01-1

1/40

Slide 1 of 40Ver. 1.0

Swift Foundation - Excel

In this session, you will learn to:

Explore the User Interface

Explore the Ribbon

Navigate in Excel

Select and Enter DataSave a Workbook

Manipulate Data

Insert and Delete Cells, Columns, and Rows

Search for Data in a Worksheet

Objectives

7/27/2019 01 Excel Session 01-1

2/40



Explore the User Interface

The User Interface of Excel consists of the following

components:

Spreadsheets

The Excel Application Window

WorksheetsWorkbooks

7/27/2019 01 Excel Session 01-1

3/40



Explore the User Interface (Contd.)

Spreadsheet:

Is a paper or an electronic document that stores various types

of data, such as numbers, text, and non-alphanumeric

symbols, in a tabular format.

Consists of vertical columns and horizontal rows that intersect

to form cells.

7/27/2019 01 Excel Session 01-1

4/40




The Excel Application Window:

Fills the entire screen and provides a place for you to interact

with Excel.

Microsoft Office Button

Ribbon

Task

Pane

Formula Bar

Microsoft Office Window Frame

Workbook

Window

Name

Box

7/27/2019 01 Excel Session 01-1

5/40




Worksheet:

Is a spreadsheet used to store data in the Excel application.

Is a collection of 16,384 columns and 1,048,576 rows that

intersect to form grids.

Is comprised of gridlines that form rectangles called cells.

Column Headings

Row

Headings

16384th

Column

1048576th

RowCell XFD1048576

7/27/2019 01 Excel Session 01-1

6/40




Workbook:

Is an Excel file that acts as a repository for related Excel

worksheets.

Contains, by default, three worksheets named Sheet1, Sheet2,

and Sheet. Title Bar

Tab

Scrolling

Buttons

Worksheet

TabsInsert Worksheet

Button

Home Tab bar

7/27/2019 01 Excel Session 01-1

7/40Slide 7 of 40Ver. 1.0


Explore the Ribbon

Some of the important tools and functions that enable you to

complete your tasks quickly and efficiently are:

The Page Layout Tab

The Formulas Tab

The Data TabThe Review Tab

7/27/2019 01 Excel Session 01-1



Explore the Ribbon (Contd.)

The Page Layout Tab:

Is a command tab located on the Ribbon and is used for

specifying page settings, layout, orientation, margins, and

other related options.

Groups and Commands on the Home tab

7/27/2019 01 Excel Session 01-1




The Formulas Tab:

Is a command tab located on the Ribbon and has several

options that allow you to work easily with formulas and

functions.

7/27/2019 01 Excel Session 01-1

10/40Slide 10 of 40Ver. 1.0



The Data Tab:

Is a command tab located on the Ribbon that allow you to

establish connections with external data sources and import

data for use within Excel worksheets.

Groups and Commands on the Page Layout tab

7/27/2019 01 Excel Session 01-1

11/40Slide 11 of 40Ver. 1.0



The Review Tab:

Is a command tab located on the Ribbon that allows you to

ensure correctness of the language and insert comments,

among other tasks.

Groups and Commands on the Review tab

7/27/2019 01 Excel Session 01-1

12/40Slide 12 of 40Ver. 1.0


Navigate in Excel

The Open Dialog Box:

Has options to search for existing files to be opened.

Has the Files Of Type drop-down list that provides options to

select the format in which the file needs to be opened.

7/27/2019 01 Excel Session 01-1

13/40Slide 13 of 40Ver. 1.0


Navigate in Excel (Contd.)

Mouse Navigation Options:

Allow you to navigate to a specific cell, range of cells, or to the

end of ranges by using the mouse.

Vertical

Scroll

Arrow

Scroll Box

Vertical

Scroll

Arrow

Vertical

Scroll Bar

Horizontal

Scroll Arrow

Horizontal

Scroll Arrow

Horizontal Scroll Bar

7/27/2019 01 Excel Session 01-1

14/40Slide 14 of 40Ver. 1.0


Navigate in Excel (Contd.)

Keyboard Navigation Options:

Enable you to navigate to a specific cell, range of cells, or to

the end of ranges in a worksheet by using the keyboard.

7/27/2019 01 Excel Session 01-1

15/40Slide 15 of 40Ver. 1.0


Select and Enter Data

The New Workbook Dialog Box:

Allows you to create either a blank workbook or a workbook

based on a template.

Online

Template

Categories

Offline

Template

Categories

7/27/2019 01 Excel Session 01-1

16/40Slide 16 of 40Ver. 1.0


Problem Statement:

You need to document the companys sales and expenses

information for the first quarter in a new Excel spreadsheet.

Before that, you would like to practice the different ways of

selecting data in Excel.

Demo: Selecting and Entering Data

7/27/2019 01 Excel Session 01-1

17/40Slide 17 of 40Ver. 1.0


Solution:

To solve the preceding problem, you need to perform the

following task:

Select a single cell and its contents.

Select a range of cells.

Select an entire worksheet.

Create a new, blank workbook.

Enter the column headings Months, Sales, Expenses, and Totals.

Enter the names of the months.

Enter the sales values.

Enter the expense values.

Demo: Selecting and Entering Data (Contd.)

7/27/2019 01 Excel Session 01-1

18/40Slide 18 of 40Ver. 1.0


Save a Workbook

The Save Command:

Allows you to save a new workbook or save the changes made

to an existing workbook without changing its name.

The Save As Command:

Allows you to save an existing file with a new name.File Format:

Excel 2007 offers an extensive list of formats in which to save

your workbook, such as:

Excel workbook (XLSX)

Excel macro-enabled workbook (XLSM)

Excel template (XLTX)

Excel macro-enabled template (XLTM)

Excel binary workbook (XLSB)

Excel add-in (XLAM)

7/27/2019 01 Excel Session 01-1

19/40Slide 19 of 40Ver. 1.0


Problem Statement:

You are working on the companys employee information

spreadsheet, and you need to send it to your manager. Before

sending the workbook for review, you find that there are a few

empty columns and you would like to utilize the empty space.

You have Excel 2007 installed on your system, but yourmanager is still using Excel 2003.

Demo: Saving a Workbook in XLS Format

7/27/2019 01 Excel Session 01-1

20/40Slide 20 of 40Ver. 1.0


Solution:


following task:

Paste the Total Hours column in column J.

Save the file in XLS format.

Demo: Saving a Workbook in XLS Format (Contd.)

7/27/2019 01 Excel Session 01-1

21/40

7/27/2019 01 Excel Session 01-1

22/40

7/27/2019 01 Excel Session 01-1

23/40



Manipulate Data (Contd.)

Auto Fill Feature:

Fills a cell data or a series of data in a worksheet into a

selected range of cells.

The Fill Handle

7/27/2019 01 Excel Session 01-1

24/40



Problem Statement:

You presented a draft copy of the worksheet that tracks sales

data to your manager for her review, and she has a few

suggested changes. She wants you to list all the months of the

year for both Northeastern and Mideastern regions, and she

wants you to align the dates in the same column for bothlocations and rename them with Fiscal 2006. She also wants

you to take a closer look at the values and validate them, if

necessary.

Demo: Manipulating Data

7/27/2019 01 Excel Session 01-1

25/40



Solution:


following task:

Move the Totals column.

Move the date text for the Northeastern Region one cell to the left.

Copy the column headings, April and Totals, to the correspondingcolumns in the Mideastern Region section.

Add the remaining months of the year to the Northeastern and

Mideastern Region sections.

Edit a sales value for the month of April in the Midwestern region.

Change the date to Fiscal 2006.

Save and close the workbook.

Demo: Manipulating Data (Contd.)

7/27/2019 01 Excel Session 01-1

26/40



Problem Statement:

You are updating your companys salary information worksheet

for the Northeastern and the Midwestern regions. You want to

categorize employee information by adding the project code of

each employee against his/her ID number. Most of the

employees belong to the Microsoft project that has projectcode MS15, and few of them belong to a project with project

code AR30.

Demo: Copying Cells Using Auto Fill Options

7/27/2019 01 Excel Session 01-1

27/40



Solution:


following task:

Insert a new column after the employee ID column.

Add the column headings for the new column.

In the Northeastern Region section, add the project codeinformation.

In the Midwestern Region section, add the project code

information.

Demo: Copying Cells Using Auto Fill Options (Contd.)

7/27/2019 01 Excel Session 01-1

28/40

7/27/2019 01 Excel Session 01-1

29/40



Problem Statement:

You are the sales executive of a company and have tracked

the sales data for the first quarter. You want to add a title to the

spreadsheet and require an empty column to add employee

names after the employee ID data. By mistake, you entered

last Decembers data in the January column, and the actualdata for January is entered in the February column, the

February data is entered in March column, and so on. You

would like to correct this mistake and, for aesthetic appeal, you

would like to eliminate unwanted rows add new rows, wherever

required, and include a new column at the beginning of the

spreadsheet.

Demo: Inserting and Deleting Cells, Columns, and Rows

7/27/2019 01 Excel Session 01-1

30/40



Solution:


following task:

Insert two rows above the current row 1.

Insert a new column before column A.

Insert a block of cells after the employee ID column.

Delete rows 4 and 14, that contain the text, Fiscal 2006.

Delete the empty column between the December and Totals

headings.

Delete the sales data in the January column and shift the

remaining monthly sales data one cell to the left.

In the Northeastern and Midwestern Region sections, insert an

empty row between the months and the first employee

information.

Demo: Inserting and Deleting Cells, Columns, and Rows (Contd.)

7/27/2019 01 Excel Session 01-1

31/40



Search for Data in a Worksheet

The Find Command:

Helps you locate specific data within a worksheet.

Is present in the Find & Select menu, in the Editing group, on

the Home tab, and displays the Find And Replace dialog box.

Specify text to

be located

Locate every instance

of search criteria

Locate next instance

of the search criteria

Displays advanced

find options

7/27/2019 01 Excel Session 01-1

32/40



Search for Data in a Worksheet (Contd.)

The Replace Command:

Helps you replace the existing data within a worksheet with

new data.

Is present in the Find & Select menu, in the Editing group, on

the Home tab, and displays the Find And Replace dialog box.

Replace every instance

of search criteria

Specify the text with

which to replaceDisplays advanced

replace options

Replace selected instance

of search criteria

7/27/2019 01 Excel Session 01-1

33/40

7/27/2019 01 Excel Session 01-1

34/40




Cell Name:

Can be used to search for data in a worksheet and to create

formulas.

Can be specified in the Name box to locate the data.

Cell Name

7/27/2019 01 Excel Session 01-1

35/40




The Go To Command:

Takes you to a specific cell either by a cell reference or by a

cell name you have specified.

Is listed along with the Find and Replace options in the Find &

Select menu.

7/27/2019 01 Excel Session 01-1

36/40

if d i l

7/27/2019 01 Excel Session 01-1

37/40



Solution:


following task:

Locate the Totals column in the Northeastern Region section.

In the Northeastern Region section, alter the total salary of the

fourth employee.Locate the Totals column in the Midwestern Region section.

In the Midwestern Region section, alter the total salary of the third

employee.

Demo: Locating a Cell in a Worksheet (Contd.)

S if d i l

7/27/2019 01 Excel Session 01-1

38/40



Test Your Understanding

Which window appears within the application window and is

used for entering data?

How many rows and columns are there in an Excel

worksheet?

Which tab on the Ribbon of the Excel User Interface is usedfor specifying page settings, layout, orientation, margins,

and other related options?

Which tab on the Ribbon of the Excel User Interface

provides options that allow you to establish connections with

external data sources and import data for use within Excel

worksheets?

Which feature of Excel fills a cell data or a series of data in a

worksheet into a selected range of cells?

S ift F d ti E l

7/27/2019 01 Excel Session 01-1

39/40



Summary

In this session, you leaned that:

Microsoft Office Excel 2007 helps you store and manage

alphanumeric data.

A spreadsheet is a paper or an electronic document that stores

various types of data, such as numbers, text, and

non-alphanumeric symbols, in a tabular format.A worksheet is a spreadsheet used to store data in the Excel

application.

A workbook is an Excel file that acts as a repository for related

Excel worksheets.

The Page Layout tab is used for specifying page settings,layout, orientation, margins, and other related options.

The Formulas tab contains options to allow you to work easily

with formulas and functions.

7/27/2019 01 Excel Session 01-1

40/40

01 excel session 01-1

Documents