microsoft excel 2007 · microsoft excel 2007 kasunkosala@yahoo.com. introduction to ms excel...

Microsoft Excel 2007

KasunKosala@yahoo.com

Introduction to Ms Excel

• What is a spreadsheet?– A spreadsheet is a grid that organizes data into

columns and rows.

– It makes easier to organization, analysis and understand data in tabular.

• What is an electronic spreadsheet?– Automate calculations.

– Automatic recalculation.

2KasunKosala@yahoo.com

Getting Started

• Opening Ms Excel

• New Workbook

• Save Workbook

• Open Workbook

• Using Ms Excel Help

3KasunKosala@yahoo.com

Excel Interface

4KasunKosala@yahoo.com

Excel Interface

5KasunKosala@yahoo.com

Excel Interface

• Microsoft Office Button

• Ribbon

• Quick Access Toolbar & customizing it

• Mini Toolbar & customizing it

• Sheets tabs

6KasunKosala@yahoo.com

The Office Button

• It replaces the File menu from previous versions of Excel.

• Common tools such as printing and saving are performed from here.

KasunKosala@yahoo.com 7

Quick Access Toolbar

• Most frequent commands for fast access

KasunKosala@yahoo.com 8

Excel 2007 Ribbon

• The ribbon replaces the toolbars from previous versions of Excel. It allows fast access to the majority of Excel commands via a tabular system.

KasunKosala@yahoo.com 9

Formula bar

Worksheet

KasunKosala@yahoo.com 10

Status bar & View Panel

KasunKosala@yahoo.com 11

Basics

• Work Book

• Work Sheet

• Row & Row headers

• Column & Column headers

• Cell

• Cell Address - A2

• Cell Range - A2:D5

KasunKosala@yahoo.com 12

Excel Mouse Pointers

Cross Pointer Used for selecting ranges of cells

Fill Handle Used to copy cell contents to adjacent cells

Arrow Pointer Used to move cell contents

Resizing Arrows Used to change column widths

Resizing Arrows Used to change row heights

Cursor Used to edit cell contents in Formula Bar

KasunKosala@yahoo.com 13

Working with Worksheets & Workbooks

• Create a Workbook

• Naming & Save a Workbook

• Open a Workbook

• Entering Data

• Working with Multiple Worksheets

• Closing Workbook & Exiting excel

14KasunKosala@yahoo.com

CELLS IN A WORKSHEET

Selecting cells in a worksheet

The “active” cell

The range concept

ENTERING DATA IN A WORKSHEET

In any cell, you can enter a constant or a formula.

A constant is data that you enter (i.e., type at the keyboard) directly into a cell.

A formula produces a value that is based on data, cell references, and arithmetic operators included in the formula.

Excel displays the data you enter or edit at the keyboard on the formula bar.

ENTERING OR EDITING DATA AT THE KEYBOARD

Excel also displays the Cancel, Enter, and Insert Function buttons between the Name box and the data.

Use one of the ways to place or store the data in the cell.

You can also enter a constant in several cells at the same time

you can also cancel the data entry action

ENTERING TEXT WITHIN A CELL

When you enter text in a cell, Excel automatically aligns the characters at the left of the cell.

You can also change the alignment of data in cells by selecting one of the options in the Horizontal list box in the Alignment tab of the Format Cells dialog box.

Indenting text within a cell

Entering Multiple-Lines of Text in a Cell

Returning to a single line of text in a cell

Entering Text with AutoComplete and Pick From a Drop-down List

Entering Numbers in a Cell

When you enter a number in a cell, the number can include any of the numeric characters (0 through 9) plus any of the following special characters:

+ - () / $ % . , E e

When you enter a numeric value in a cell, Excel automatically aligns the value at the right border of the cell.

Changing the Number Format

Manipulating Data

• Selecting Cells, Data, Rows & Columns• Editing or Replacing Content in a Cell• Copy and Paste • Cut and Paste• Copy & Paste Special• Undo and Redo• Clearing a Cell• Auto Fill & Custom list• Merging & Splitting Cells• Finding and Replacing Data• Adjusting Row, Column Height & Width• Inserting and Deleting Rows, Columns & Cells

KasunKosala@yahoo.com 20

ENTERING A RANGE OR POINTING TO IT

Entering a range

Pointing to a range

Selecting multiple ranges of cells – Ctrl key

Selecting an entire row or column of cells

Selecting cells in entire multiple adjacent rows

Selecting cells in entire multiple nonadjacent rows

Selecting cells in entire columns (single column, multiple adjacent columns, or multiple nonadjacent columns)

Entering Formulas

The rules for entering a formula in a cell

Order of Precedence for Arithmetic Operators

Use of Parentheses

Cell References (see next slide)

Use of the Point Mode

Cell Reference

• Relative reference

• Absolute reference- $

• Mixed reference (Column & Row)

• ! reference

KasunKosala@yahoo.com 23

Naming Cells and Ranges

• Creating Names with Name Box:

Data_Input

• Creating and Deleting Names with the Name Manager box in the Defined Names group of the Formulas tab’s Ribbon.

Report

Total_Sales

Using Named Ranges

• Selecting a Range by Choosing its Name in the Name Box.

• Selecting a Range by Choosing its Name with the F5 key.

• Using Names with Functions.

• Using Names with the Page Setup dialog box.

Functions

Syntax of Functions

Entering a Function with the Insert Function Dialog Box:

Point to a cell or a range rather then entering it

View the Formula result = at the bottom of the dialog box

After entering the function, click on OK

Functions

The SUM function.

The following are examples of the SUM function:

Example Description

=SUM(2,3) Adds the constants 2 and 3.

=SUM(B3:F3) Adds the values in cells B3, C3, D3, E3, and F3.

=SUM(B8:B10) Adds the values in cells B8, B9, and B10.

=SUM(B8:B10, B12, B15:B20) Adds the values in cells B8 through B10, B12, and B15 through B20.

Entering Functions

These functions are grouped into the following 11 categories: Cube Database Date & Time Engineering*

Financial Information Logical Lookup & Reference Math & Trig Statistical Text

COPYING AND MOVING

Using the Windows Clipboard.

Using Drag and Drop

DOCUMENTING A WORKSHEETBY ATTACHING COMMENTS TO CELLS

Add a Comment to a Cell

Edit a Comment

Delete a Comment

CLEAR, UNDUE, and REDO CELLS

Clear cells

Undo cells

Redo cells

Customize Excel

• Popular

• Formulas

• Proofing

• Save

• Advanced

• Customize

KasunKosala@yahoo.com 32

Customizing Excel

– Freezing and Locking Cells

– Hiding and Showing Rows / Columns / Sheets

– Using Different Views

– Displaying Grid lines and Headings

– Using Excel Options

33KasunKosala@yahoo.com