l12123456678909008876543, Exercises for Computer science

20 pages
117Number of visits
Description
exceldfghjklklllhgghhjkkkkkkkkkkkkkkkiuhjhggggfgbbnj
20 points
this document
Preview3 pages / 20
Untitled-12

12

BASICS OF MS-EXCEL

12.1 INTRODUCTION

MS-Excel 2000 is a Windows based application package. It is

quite useful in entering, editing, analysis and storing of data.

Arithmetic operations with numerical data such as addition,

subtraction, multiplication and division can also be done with

Excel. You can sort the numbers/characters according to some

given criteria (like ascending, descending etc.)and solve simple

financial, mathematical and statistical formulas.

12.2 OBJECTIVES

After going through this lesson you would be in a position to

l explain the basic features of MS Excel

l set pages and their printing

l modify a worksheet

l enter and edit data in a worksheet

l work on keyboard shortcuts

Basics of MS Excel :: 227

12.3 EXCEL FEATURES

There are a number of features that are available in Excel to

AutoFormat - lets you to choose many preset table formatting

options.

1 AutoSum - helps you to add the contents of a cluster of

2 List AutoFill - automatically extends cell formatting when a

new item is added to the end of a list.

3 AutoFill - feature allows you to quickly fill cells with repetitive

or sequential data such as chronological dates or numbers,

and repeated text. AutoFill can also be used to copy functions.

You can also alter text and numbers with this feature.

4 AutoShapes toolbar will allow you to draw a number of

geometrical shapes, arrows, flowchart elements, stars and

more. With these shapes you can draw your own graphs.

5 Wizard - guides you to work effectively while you work by

displaying various helpful tips and techniques based on what

you are doing.

Drag and Drop - feature will help you to reposition the data

and text by simply dragging the data with the help of mouse.

representation of your data in the form of Pie, Bar, Line charts

and more.

7 PivotTable - flips and sums data in seconds and allows you

to perform data analysis and generating reports like periodic

financial statements, statistical reports, etc. You can also

analyse complex data relationships graphically.

8 Shortcut Menus - commands that are appropriate to the

task that you are doing appear by clicking the right mouse

button.

12.4 STARTING EXCEL

1. Click on (with the help of mouse) the Start button on the

Windows 98 Taskbar at the bottom of the Screen

2. Highlight the Programs item. The program menu will open.

228 :: Basic Computing Skills

3. Select Microsoft Excel from the list of programs. (these steps

are shown Figure 12.1)

4. Click on Microsoft Excel

Symbolically these actions are shown below.

Select Start→Programs→Microsoft Excel commands from your

Fig. 12.1

Throughout the text of your lessons on MS Excel we will be

showing the symbol → to indicate the direction (steps) you have

to follow.

You can also use the Microsoft Office Shortcut Bar (MSOB) as

shown in figure 12.1 to start your work on Excel.

12.5 EXCEL WORKSHEET

Excel allows you to create worksheets much like paper ledgers

that can perform automatic calculations. Each Excel file is a

workbook that can hold many worksheets. The worksheet is a

grid of columns (designated by letters) and rows (designated by

Microsoft Excel

MSOB

Basics of MS Excel :: 229

numbers). The letters and numbers of the columns and rows

(called labels) are displayed in gray buttons across the top and

left side of the worksheet. The intersection of a column and a

row is called a cell. Each cell on the spreadsheet has a cell address

that is the column letter and the row number. Cells can contain

either text, numbers, or mathematical formulas.

Fig. 12.2

12.5.1 Selecting, Adding and Renaming Worksheets

The worksheets in a workbook are accessible by clicking the

worksheet tabs just above the status bar. By default, three

worksheets are included in each workbook. To add a sheet, select

Insert→Worksheet from the menu bar. To rename the worksheet

tab, move the cursor to sheet tab, right-click on the tab with the

mouse and select Rename from the shortcut menu. Type the

new name and press the ENTER key.

Sheet Tab Status Bar

Cell Number Box

Active Cell

Standard Tool Bar

Tool Bar

Formula

Bar

Worksheet

Tab Scroll

Buttons

Scroll Bar

Vertical

Scroll Bar

230 :: Basic Computing Skills

12.5.2 Standard Toolbar

This toolbar is located just below the menu bar at the top of the

screen and allows you to quickly access basic Excel commands.

Fig. 12.3

1. New - Select File→New from the menu bar, or press CTRL+N,

or click the New button to create a new workbook.

2. Open - Click File→Open from the menu bar, or press

CTRL+O, or click the Open folder button to open an existing

workbook.

3. Save - The first time you save a workbook, select File→Save

As and name the file. After the file is named click File→Save,

or CTRL+S, or the Save button on the standard toolbar.

4. Print - Click the Print button to print the worksheet.

5. Print Preview - This feature will allow you to preview the

worksheet before it prints.

6. Spell Check - Use the spell checker to correct spelling errors

on the worksheet.

7. Cut, Copy, Paste, and Format Painter - These actions will

be explained to you later in this lesson.

8. Undo and Redo - Click the backward Undo arrow to cancel

the last action you performed like entering data into a cell,

formatting a cell, entering a function, etc. Click the forward

Redo arrow to cancel the undo action.

9. Insert Hyperlink - To insert a hyperlink to a web site on the

Internet, type the text into a cell you want to be the link that

can be clicked with the mouse. Then, click the Insert

text to link to and click OK.

10. AutoSum, Function Wizard, and Sorting - These features

are discussed in lesson 15.

Basics of MS Excel :: 231

11. Chart and Drawing - These feature are discussed in

lesson 16.

12. Zoom - To change the size that the worksheet appears on

the screen, choose a different percentage from the Zoom

12.6 SELECTING CELLS AND RANGES

To enter data into your worksheet you must first have a cell or

range selected. When you open an Excel worksheet, cell A1 is

already active. An active cell will appear to have a darker border

around it than other cells on the worksheet. The simplest way

to select a cell is with your mouse pointer. Move your mouse to

the desired cell and click on it with right button. Whatever you

type goes into the cell. To select a range of cells, click on one

cell, hold down the left mouse button and drag the mouse pointer

to the last cell of the range you want to select. You can also use

keyboard shortcuts given at the end of this lesson for selecting

cells.

12.7 NAVIGATING THE WORKSHEET

scrollbar or by columns with the horizontal scrollbar (see Figure

13.2). when you click and drag the thumb tab on the scrollbar,

a Screen Tip will appear alongside the bar identifying the row or

keyboard shortcuts given at the end of this lesson for navigating

the worksheet.

12.8 DATA ENTRY

You can enter various kinds of data in a cell.

1. Numbers: Your numbers can be from the entire range of

numeric values: whole numbers (example, 25), decimals

(example, 25.67) and scientific notation (example,

0.2567E+2). Excel displays scientific notation automatically

if you enter a number that is too long to be viewed in its

entirety in a cell. You may also see number signs (# # # # #

#) when a cell entry is too long. Widening the column that

contains the cell with the above signs will allow you to read

the number.

2. Text: First select the cell in which data has to be entered

and type the text. Press ENTER key to finish your text entry.

232 :: Basic Computing Skills

The text will be displayed in the active cell as well as in the

Formula bar. If you have numbers to be treated as text use

an apostrophe (‘) as the first character. You cannot do

calculations with these kind of data entry.

3. Date and Time: When you enter dates and times, Excel

converts these entries into serial numbers and kept as

background information. However, the dates and times will

be displayed to you on the worksheet in a format opted by

you. You will learn about date and time formats later in

lesson 10.

4. Data in Series: You can fill a range of cells either with the

same value or with a series of values with the help of AutoFill.

12.9 EDITING DATA

Editing your Excel worksheet data is very easy. You can edit

your data by any of the following ways:

1. Select the cell containing data to be edited. Press F2. Use

Backspace key and erase the wrong entry. Retype the correct

entry.

2. Select the cell and simply retype the correct entry.

3. If you want only to clear the contents of the cell, select the

cell and press Delete key.

4. To bring back the previous entry, either click on Undo button

on standard Toolbar or select Edit→Undo command or use

keyboard shortcuts CTRL+Z.

12.10 CELL REFERENCES

Each worksheet contains a number of columns and rows. Each

cell of the worksheet has a unique reference. For example, D5,

refers to the cell containing column number D and row number

5.

12.11 FIND AND REPLACE DATA IN A WORKSHEET

You may want to locate a number or text that is already typed in

the worksheet. This is done through Edit→Find. You can also

locate your data and replace with new data with

Edit→Find→Replace.

12.12 MODIFYING A WORKSHEET

12.12.1 Adding Worksheets, Rows, and Columns

1. Worksheets - Add a worksheet to a workbook by selecting

Basics of MS Excel :: 233

2. Row - To add a row to a worksheet, select Insert→Rows

from the menu bar, or highlight the row by clicking on the

row label, right-click with the mouse, and choose Insert.

3. Column - Add a column by selecting Insert→Columns from

the menu bar, or highlight the column by click on the col-

umn label, right-click with the mouse, and choose Insert.

12.12.2 Resizing Rows and Columns

There are two ways to resize rows and columns.

1. Resize a row by dragging the line below the label of the row

you would like to resize. Resize a column in a similar man-

ner by dragging the line to the right of the label correspond-

ing to the column you want to resize.

2. Click the row or column label and select

Format→Row→Height or Format→Column→Width from

the menu bar to enter a numerical value for the height of

the row or width of the column.

12.12.3 Selecting Cells

Before a cell can be modified or formatted, it must first be selected

(highlighted). Refer to the table below for selecting groups of

cells.

Cells to select Mouse action:

One cell click once in the cell

Entire row click the row label

Entire column click the column label

Entire worksheet click the whole sheet button (at

the intersection of rows and

columns)

Cluster of cells drag mouse over the cells or hold

down the SHIFT key while using

the arrow keys

To activate the contents of a cell, double-click on the cell or click

once and press F2.

234 :: Basic Computing Skills

12.12.4 Moving and Copying Cells

1. Moving Cells

To cut cell contents that will be moved to another cell select

Edit→Cut from the menu bar or click the Cut button on the

standard toolbar.

2. Copying Cells

To copy the cell contents, select Edit→Copy from the menu

bar or click the Copy button on the standard toolbar.

3. Pasting Cut and Copied Cells

Highlight the cell you want to paste the cut or copied content

into and select Edit→Paste from the menu bar or click the

Paste button on the standard toolbar.

4. Drag and Drop

If you are moving the cell contents only a short distance, the

drag-and-drop method may be easier. Simply drag the

highlighted border of the selected cell to the destination cell

with the mouse.

5. Freeze Panes

If you have a large worksheet with column and row headings,

those headings will disappear as the worksheet is scrolled.

By using the Freeze Panes feature, the headings can be visible

at all times.

1. Click the label of the row below the row that should re-

main frozen at the top of the worksheet.

2. Select Window→Freeze Panes from the menu bar.

3. To remove the frozen panes, select Window→Unfreeze

Panes.

Basics of MS Excel :: 235

Fig. 12.4

Freeze panes has been added to row 2 in the image above. No-

tice that the row numbers skip from 3 to 8. As the worksheet is

scrolled, rows 1 and 2 will remain stationary while the remain-

ing rows will move. Following similar steps you can Freeze or

Unfreeze selected columns.

12.13 PAGE BREAKS

To set page breaks within the worksheet, select the row you

want to appear just below the page break by clicking the row’s

label. Then choose Insert→Page Break from the menu bar. You

may need to click the double down arrow at the bottom of the

menu list to view this option.

12.14 PAGE SETUP

Select File→Page Setup from the menu bar to format the page,

1. Page: The page option allows you to set the paper size,

orientation of the data, scaling of the area, print quality, etc.

Select the Orientation under the Page tab in the Page Setup

236 :: Basic Computing Skills

window to make the page Landscape or Portrait. The size of

the worksheet on the page can also be formatted using

Scaling. To force a worksheet to print only one page wide so

that all the columns appear on the same page, select Fit to

1 page(s) wide.

Fig. 12.5

2. Margins Change the top, bottom, left, and right margins

under the Margins tab. Enter values in the header and footer

fields to indicate how far from the edge of the page this text

should appear. Check the boxes for centering horizontally

or vertically on the page.

Fig. 12.6

Basics of MS Excel :: 237

(which will be displayed on the top of every page) and the

Footer (which will be displayed on the bottom of every page).

Fig. 12.7

To modify a preset header or footer, or to make your own,

click the Custom Header and Custom Footer buttons. A

new window will open allowing you to enter text in the left,

center, or right on the page.

Fig. 12.8

Format Text - Click this button after highlighting the text

to change the font, size, and style.

Page Number - Insert the page number of each page.

238 :: Basic Computing Skills

Total Number of Pages - Use this feature along with the

page number to create strings such as “page 5 of 10”.

Date - Add the current date.

Time - Add the current time.

File Name - Add the name of the workbook file.

Tab Name - Add the name of the worksheet’s tab.

4. Sheet tab has the option to select the area to be printed

(that is, range of cells). Check Gridlines if you want the

gridlines dividing the cells to be printed on the page. If the

worksheet is several pages long and only the first page in-

cludes titles for the columns, select Rows to repeat at top to

choose a title row that will be printed at the top of each page.

Fig. 12.9

INTEXT QUESTIONS

1. Write True or False for the following statements

(a) To modify a preset header or footer click the custom

(b) Autofill helps you to add the contents of a cluster of

Basics of MS Excel :: 239

representation of data.

(d) Click the edit button to print the worksheet.

(e) Pivot table allows you to perform data analysis.

12.15 PRINT PREVIEW

Select File→Print Preview from the menu bar to view how the

worksheet will print. Click the Next and Previous buttons at

the top of the window to display the pages and click the Zoom

button to view the pages closer. Make page layout modifications

needed by clicking the Page Setup button. Click Close to return

to the worksheet or Print to continue printing.

12.16 PRINT

To print the worksheet, select File→Print from the menu bar.

Fig. 12.10

1. Print Range - Select either all pages or a range of pages to

print.

2. Print What - Select selection of cells highlighted on the

worksheet, the active worksheet, or all the worksheets in

the entire workbook.

240 :: Basic Computing Skills

3. Copies - Choose the number of copies that should be printed.

Check the Collate box if the pages should remain in order.

4. Click OK to print.

12.17 FILE CLOSE AND EXIT EXCEL

When your work is finished and it has been saved properly:

1. Select File→Close command and then click mouse to close

2. Select File→Exit command and then click mouse to close

12.18 WORKBOOK PROTECTION

You have learned how to save your workbook as a file. Some

times your data can be very confidential which you would like to

protect from unauthorised people. Protection prevents changes

to all or part of a document. You can also assign a password so

that other users can be limited in accessing protected informa-

tion. A password is case sensitive, can be up to 255 characters

long, and can contain any combination of letters, numbers, and

symbols. When a document is protected, this command changes

to Unprotect Document.

1. Protect Sheet: Select Tools→Protect Sheet commands and

click. This feature prevents changes to cells on worksheets,

items in a chart, graphic objects on a worksheet or chart

sheet. When the active document is protected, the command

name changes to Unprotect Sheet. If you want to unprotect

a sheet that has been already protected. Select

Tools→Unprotect Sheet commands and click.

2. Protect Workbook: Select Tools→Protect Workbook

commands and click. This feature protects a workbook’s

structure and windows. You can prevent changes to the

structure of a workbook so that sheets can’t be deleted, moved,

hidden, unhidden, or renamed, and new sheets can’t be

inserted. You can also protect windows from being moved or

resized. When the active document is protected, the

command name changes to Unprotect Workbook. If you

want to unprotect a workbook that has been already

protected. Select Tools→Unprotect Workbook commands

and click.

Basics of MS Excel :: 241

3. Protect for Sharing: Select Tools→Protect and Share

Workbook commands and click. This feature protects the

sharing and change history tracking in a shared workbook

so the features can’t be turned off. If you select this check

box and click OK when the workbook isn’t a shared workbook,

you are asked if you want to save it as a shared workbook. In

a workbook that is already shared, you can turn on protection

for sharing and the change history, but you can’t assign a

first remove the workbook from shared use. When the active

shared workbook is protected, the command name changes

to Unprotect for Sharing. If you want to unprotect a share

workbook that has been already protected. Select

Tools→Unprotect Workbook commands and click.

12.19 KEYBOARD SHORTCUTS

Keyboard shortcuts can save time and the effort of switching

from the keyboard to the mouse to execute simple commands.

Print this list of Excel keyboard shortcuts and keep it at your

computer desk for a quick reference.

Action Keystroke

Document Actions

Open a file CTRL+O

New file CTRL+N

Save As F12

Save CTRL+S

Print CTRL+P

Find CTRL+F

Replace CTRL+H

Go to F5

Cursor Movement

One cell up up arrow

One cell down down arrow

242 :: Basic Computing Skills

One cell right Tab

One cell left SHIFT+Tab

Top of worksheet (cell A1) CTRL+Home

End of worksheet

(last cell with data) CTRL+End

End of row Home

End of column CTRL+left arrow

Move to next worksheet CTRL+PageDown

Formulas

Apply AutoSum ALT+=

Current date CTRL+;

Current time CTRL+:

Spelling F7

Help F1

Macros ALT+F8

Selecting Cells

All cells left of current cell SHIFT+left arrow

All cells right of current cell SHIFT+right arrow

Entire column CTRL+Spacebar

Entire row SHIFT+Spacebar

Entire worksheet CTRL+A

Text Style

Bold CTRL+B

Italics CTRL+I

Underline CTRL+U

Strikethrough CTRL+5

Formatting

Edit active cell F2

Basics of MS Excel :: 243

Format as currency with SHIFT+CTRL+\$ 2 decimal places

Format as percent with no SHIFT+CTRL+% decimal places

CTRL+X Cut

CTRL+C Copy

CTRL+V Paste

CTRL+Z Undo

CTRL+Y Redo

Format cells dialog box CTRL+1

Note: A plus sign in the above list indicates that the keys need to

be pressed at the same time.

INTEXT QUESTIONS

Fill in the blanks

(a) When the active document is protected the command name

changes to ____________ workbook.

(b) Select __________ from the menu bar to view how the

worksheet will print.

(c) A pasword can be upto ____________ characters long.

(e) Check ___________ if you want the gridlines dividing the cells

to be printed on the page.

12.20 WHAT YOU HAVE LEARNT

In this lesson you learnt about starting Excel and working on a

worksheet. You can select a cell or a range of cells. Also you can

enter data in a worksheet. You can define the size of a page by

going to page set up and insert a page break. You have learnt

about page-preview which gives an idea on how the print out will

look like.

244 :: Basic Computing Skills

12.21 TERMINAL QUESTIONS

1. List any four applications of MS Excel.

2. What are the main features of MS Excel?

3. Differentiate between a worksheet and a workbook?

4. What are the different types of data that can be entered into

worksheet cells?

5. Explain three different ways you protect your workbook.

6. What are the three different ways you save your workbook?

7. How do you find a single number or name you want in a

large worksheet containing thousands of numbers and

names? Is it possible to replace a name or number with

some other name or number? How?

8. How do you select a single cell, a single column, a single

row, a cluster of cells, and a entire worksheet?

9. Difference between Move cells and Copy cells

10. Explain the use of Freeze Panes

11. What are the different features available in Page setting

command?

12. Describe different features available in Print command

12.22 KEY TO INTEXT QUESTIONS

1. (a) True

(b) False

(c) True

(d) False

(e) True

Basics of MS Excel :: 245

2. (a) Unprotect

(b) Print preview

(c) 255

(d) three

(e) gridlines