Microsoft Excel 2003 (Zambak) (Low Quality)

152 Pages·2005·2.455 MB·English

by  Mükremin ÖZKUL

M O D U L A R S Y S T E M

MICROSOFT
EXCEL 2003
İbrahim MESECAN
Kenan ÇETİNKAYA
Orhan KARSLI
www.zambak.com
Copyright © 2005 Zambak
Basým Yayýn Eðitim ve Turizm
Ýþletmeleri Sanayi Ticaret A.Þ.
All rights reserved.
No part of this book may be
reproduced, stored in a retrieval
system, or transmitted in any
form of recording without the
prior written permission of the
publisher.
Digital Assembly
Zambak Typesetting & Design
Page Design
Suat KESKİNKILINÇ
Proofreader
Patrick CASEY
Osman AY
Publisher
Zambak Basým Yayýn Eðitim ve
Turizm Ýþletmeleri Sanayi Ticaret
A.Þ.
Printed in
Ýzmir - TURKEY
ISBN
975-266-179-3
DISTRIBUTION
ZAMBAK YAYINLARI
Bulgurlu Mah. Haminne Çeşme Sok.
Nu:20 34696 Üsküdar/ İstanbul

Tel : [phone] (pbx)
Fax : [phone]
www.zambak.com
SPREADSHEET BASICS 3.6 USING STYLE ..............................................................29
1.1 OVERVIEW ...................................................................2 SUMMARY .......................................................................31
1.2 SCREEN ELEMENTS AND DEFINITIONS .....................3 QUESTIONS ......................................................................32
The Icons of the Standard Toolbar .............................................4 PRACTICE ........................................................................34
1.3 STANDARD TOOLBAR .................................................4 ENTERTAINTMENT ..........................................................37
1.4 CREATING, SAVING, AND OPENING A WORKBOOK ..5 PROJECT ..........................................................................38
1.4.1 CREATING A WORKBOOK ........................................5
1.4.2 SAVING A WORKBOOK ............................................5 PAGE SETUP AND PRINTING
1.4.3 OPENING A WORKBOOK ..........................................5 4.1 PAGE SETUP ...............................................................40
1.4.4 CLOSING A WORKBOOK ..........................................5 4.1.1 PAGE TAB ...............................................................40
SUMMARY ........................................................................6 4.1.2 MARGINS TAB ........................................................40
QUESTIONS .......................................................................7 Margin of the Header and footer .............................................41
4.1.3 HEADER/FOOTER TAB .............................................41
WORKSHEET AND CELL OPERATIONS 4.1.4 SHEET TAB .............................................................42
2.1 MOUSE POINTERS ......................................................10 4.1.5 CHART TAB ............................................................42
2.2 CELL OPERATIONS ....................................................10 Printed chart size: .....................................................................42
2.2.1 SELECTING A CELL OR AN AREA ...........................10 Printing Quality: ........................................................................42
2.2.2 MOVING THROUGH A SELECTED AREA .................11 4.2 PRINT PREVIEW .........................................................43
2.2.3 SELECTING MULTIPLE CELLS .................................11 4.2.1 PRINT PREVIEW TOOLBAR .....................................43
2.2.4 ENTERING DATA INTO A CELL ...............................11 4.3 PRINT ............................................................................43
2.2.5 ENTERING DATA INTO AN AREA ...........................11 4.3.1 PRINT RANGE .........................................................44
2.2.6 CLEARING DATA .....................................................11 SUMMARY .......................................................................45
2.2.7 RESIZING AND AUTO SIZING .................................12 QUESTIONS ......................................................................46
2.2.8 HIDING AND UNHIDING ..........................................12 ENTERTAINTMENT ..........................................................47
2.2.9 CUT COPY PASTE OPERATIONS .............................12 PROJECT ..........................................................................48
2.2.10 PASTE SPECIAL ....................................................12
2.2.11 DELETING – INSERTING ........................................13 FUNCTIONS AND FORMULAS
a. A cell or a group of cells .......................................................13 5.1 UNDERSTANDING FUNCTIONS AND FORMULAS ....50
b. Rows or columns .................................................................13 Referring to another worksheet .............................................50
2.3 WORKSHEET OPERATIONS .......................................14 5.1.1 WRITING YOUR FIRST FORMULA ...........................50
2.3.1 DELETING A WORKSHEET: ....................................14 5.1.2 OPERATORS IN EXCEL FORMULAS ........................51
2.3.2 RENAMING A WORKSHEET: ..................................14 5.1.3 ABSOLUTE AND RELATIVE REFERENCE .................52
2.3.3 MOVING OR COPYING A WORKSHEET ..................14 5.2 SIMPLE FUNCTIONS ...................................................53
2.3.4 INSERTING AN EMPTY WORKSHEET ...................14 5.2.1 SUM FUNCTION ......................................................53
2.3.5 SELECTING MULTIPLE WORKSHEETS ....................15 5.2.2 AVERAGE FUNCTION ..............................................54
2.3.6 CHANGING AN ACTIVE WORKSHEET .....................15 5.2.3 MAX FUNCTION .....................................................55
SUMMARY .......................................................................16 5.2.3 MIN FUNCTION .......................................................55
QUESTIONS ......................................................................17 5.2.4 COUNT FUNCTION ..................................................55
ENTERTAINTMENT ..........................................................19 5.3 USING FUNCTIONS AND FORMULAS ........................56
PRACTICE ........................................................................20 5.3.1 DATE AND TIME FUNCTIONS .................................57
PROJECT ..........................................................................22 Now: .......................................................................................57
Today: .....................................................................................57
FORMATTING CELLS Abs: .........................................................................................57
3.1 THE FORMATTING TOOLBAR ....................................24 Pi: .............................................................................................58
3.2 USING THE FORMAT CELLS DIALOG BOX .................25 Radians: ..................................................................................58
3.2.1 NUMBER .................................................................25 Degrees: ..................................................................................58
3.2.2 ALIGNMENT ............................................................25 Sin: ..........................................................................................58
3.2.3 FONT .......................................................................26 Fact: ........................................................................................58
3.2.4 BORDERS ................................................................27 Int: ...........................................................................................58
3.2.5 PATTERNS ..............................................................27 Mod: ........................................................................................58
3.2.6 PROTECTION ...........................................................27 Power: .....................................................................................58
3.3 USING THE FORMAT PAINTER ..................................28 Product: ...................................................................................58
3.4 USING AUTO FORMAT ...............................................28 Round: .....................................................................................59
3.5 CONDITIONAL FORMATTING .....................................28 Trunc: ......................................................................................59
SumIf: .......................................................................................59 8.2.1 SPLITTING PANES .................................................102
Sqrt: ..........................................................................................60 8.2.2 FREEZING PANES ..................................................102
Rand: .......................................................................................60 8.3 GROUP AND OUTLINE .................................................103
5.3.3 STATISTICAL FUNCTIONS ......................................60 8.4 COMMENT .................................................................104
Count: ......................................................................................60 To Insert a Comment: ..............................................................104
CountBlank: .............................................................................60 To Edit a Comment: .................................................................104
Counta: ....................................................................................60 To Delete a Comment: .............................................................104
CountIf: ....................................................................................60 To Format a Comment: ...........................................................105
5.3.4 TEXT FUNCTIONS ...................................................61 8.5 TRACKING ..................................................................105
Mid: .........................................................................................61 8.5.1 HOW TO USE CHANGE TRACKING ........................105
Find: ........................................................................................61 8.6 SHARING A WORKBOOK ...........................................106
Len: .........................................................................................61 Untracked changes ..................................................................107
Left: .........................................................................................61 8.7 OPTIONS WINDOW ...................................................107
Right: ........................................................................................61 8.7.1 VIEW TAB .............................................................107
5.3.5 LOOKUP & REFERENCE ..........................................62 8.7.2 GENERAL TAB ......................................................108
VLookUp: .................................................................................62 8.7.3 CUSTOM LISTS .....................................................109
HLookUp: .................................................................................62 8.7.4. INTERNATIONAL TAB ...........................................110
5.3.6 DATABASE FUNCTIONS .........................................63 8.7.5. SECURITY TAB ......................................................111
SUMMARY .......................................................................65 8.7.6. EDIT TAB .............................................................111
QUESTIONS ......................................................................67 8.7.7. COLOR TAB ...........................................................112
PRACTICE ........................................................................69 8.7.8 SPELLING OPTIONS ...............................................112
ENTERTAINTMENT ..........................................................73 SUMMARY ......................................................................113
PROJECT ..........................................................................74 QUESTIONS .....................................................................114
PRACTICE .......................................................................115
SORTING AND FILTERING PUT THE WORDS IN GROUPS ........................................116
6.1 PREPARING LISTS ......................................................76 PROJECT .........................................................................117
6.2.1 DATA FORMS HOW TO ACCESS ............................76
6.2.2 USING A FORM TO FIND SPECIFIC LIST MACROS
INFORMATION .................................................................77 9.1 RECORDING MACROS ...............................................120
6.3 SORTING .....................................................................77 9.1.1 OBJECT BROWSER ..............................................121
6.4.1 FILTERING ...............................................................78 9.1.2 ASSIGNING VALUE TO CELLS ................................122
6.4.2 AUTOFILTER ............................................................79 9.1.3 CONCATENATING TWO STRINGS .........................122
6.4.3 ADVANCED FILTER .................................................79 Before running a macro ...........................................................123
SUMMARY .......................................................................81 Record your macro ..................................................................123
QUESTIONS ......................................................................82 9.2 FORMS TOOLBAR .....................................................128
PRACTICE ........................................................................84 9.2.1 BUTTON .................................................................128
9.2.2 COMBOBOX ...........................................................128
CHARTS 9.2.3 CHECKBOX .............................................................129
7.1 CHART WIZARD ..........................................................86 9.2.4 OPTIONBUTTON .....................................................129
Step 1 ......................................................................................86 9.3 USER FORMS .............................................................132
Step 2 ......................................................................................87 SUMMARY ......................................................................135
Step 3 ......................................................................................88 QUESTIONS .....................................................................136
Step 4 ......................................................................................90 PRACTICE .......................................................................137
7.2 CHART TOOLBAR ........................................................90 PROJECT .........................................................................138
SUMMARY .......................................................................94 ENTERTAINTMENT .........................................................139
QUESTIONS ......................................................................95
PRACTICE ........................................................................96
ANSWERS
ENTERTAINTMENT ..........................................................97
PROJECT ..........................................................................98 INDEX
EXTRA OPTIONS
8.1 DATA VALIDATION ....................................................100
Analyses and Solution .............................................................100
Input Message: ........................................................................101
Error Alert: ...............................................................................101
8.2 FREEZE AND SPLIT PANES .......................................101

SPREADSHEET BASICS
1.1 OVERVIEW
Microsoft Excel is a spreadsheet program enabling the user to make
calculations, prepare charts and manage data easily. When you have
large amount of data, numbers and calculations (accountancy documents,
personal info, marks and other info in a school, etc), it’s very easy to process
and get outputs from your data with a spreadsheet program like Microsoft
Excel.
Title Menu Formula Standard Formatting
bar bar bar toolbar toolbar
Name
box
Column
headings
Row
headings
Active
cell
Worksheet
window
Navigation
buttons
Status Worksheet Horizontal Vertical Task
bar tabs scroll bar scroll bar pane
FIGURE 1.1 Excel Interface
MICROSOFT EXCEL 2003
2
Spreadsheet Basics Chapter 1
1.2 SCREEN ELEMENTS AND DEFINITIONS
Cell: Microsoft Excel is made up of small boxes that are called cells. Each cell
You can change the name
can have different properties and store different data. All cells have different of a cell or a range from
addresses or names like “E5”, which means “the cell in column E and row Name Box
5”.
Row: A row is the adjacent cells horizontally adjacent to one another. Each
row is named with a number and contains 256 (28) cells.
Column: A column is the cells vertically adjacent to one another. Each column
is named with letter(s) A, B, C, ... IV and contains 65536 (216) cells.
Worksheet: A worksheet contains both rows and columns (28 x 216 = 224 or
16.777.216 cells.)
Workbook: A workbook is made up of worksheets. Each worksheet is like a
paper in a file (Workbook) that contains all formulas, links, and data in tabular
format. Theoretically, a workbook can have as many worksheets as you want,
but it’s not suggested that you store too much information in a workbook. It’s
better to store it on different but related files.
Navigation buttons: They are placed at the bottom left corner of the active
workbook. You can change the current worksheet using navigation buttons.
The name box: It shows the name or address information of the active cell.
Using this box, you can change the reference name of the selected area. On
the right side of the box, there is an arrow which is used to show the user
defined area names. When you select one of these names Excel automatically
selects and shows that range.
The formula bar: It is used to define formulas in the active cell.
To modify the formula in
The status bar: It shows messages or brief information about the current
the active cell, press F2
situation.
MICROSOFT EXCEL 2003
3
Chapter 1 Spreadsheet Basics
1.3 STANDARD TOOLBAR
The Standard Toolbar provides quick access to commonly used actions. Each action is represented by an
icon. When you move your mouse pointer over an icon, it is highlighted and a descriptive tool tip appears.
Locate the icon for the action you want to perform click on the icon.
The Icons of the Standard Toolbar
Format painter: Copies the format of the
New: Creates a new workbook.
selected range cells onto another range.
Open: Opens an existing workbook created
Undo: Cancels the last action. You can undo
before.
upto 16 actions using the arrow from the drop
down menu.
Save: Saves the current workbook. If you’re
saving the file for the first time, you’ll be
Redo: Repeats the last action. You can also
required to give a name.
redo the last 16 undo actions using the arrow
from the drop down menu.
Send: Sends an entire workbook as an
attachment to an e-mail or sends the current
sheet as the body of an e-mail message. Hyperlink: Adds a hyperlink to the selected
items.
Research: Displays a basic search panel to search
Insert Function: Applies the sum function
anything that you want in the workbook.
or any other function from the drop down
menu
Print: Prints a worksheet, chart or the entire
workbook. Sorts Ascending: Sorts the selected
range of cells in ascending order
Print Preview: Displays print preview window.
Sort Descending: Sorts the selected range
of cells in descending order
Spelling: Checks a workbook for spelling
errors.
Chart Wizard: Starts Chart Wizard for the
Cut: Cuts the data in the selected range of selected range of cells
cells.
Drawing Toolbar: Displays or hides the
Copy: Copies the data and/or formats in drawing toolbar
the selected range of the cells to MS Office
clipboard.
Zoom: Zooms the worksheet to a
Paste Special: Pastes the copied data and/or percentage you specify.
formats in the copied items from the clipboard
to a selected range of cell(s). See Section
Help: Displays the Office Assistant for help.
2.2.10 for details.
TABLE 1.1 The Icons of the Standard Toolbar
MICROSOFT EXCEL 2003
4
Spreadsheet Basics Chapter 1
1.4 CREATING, SAVING, OPENING
AND CLOSING A WORKBOOK
1.4.1 CREATING A WORKBOOK
A workbook may be created by in of these ways:
(cid:1) S elect File > New. This displays the New Workbook task pane. Then select
Blank Workbook from the task pane.
(cid:1) Click New button on the Standard Toolbar.
1.4.2 SAVING A WORKBOOK
Save As: Use the following steps when saving a workbook for the first time, or
when you want to save it with a different name.
(cid:1) Select Save As ... command from the File menu. FIGURE 1.2 File menu
(cid:1) W hen the Save As dialog box appears, select a location
for the file.
(cid:1) Type a name in the File name textbox.
(cid:1) Click Save button.
Save: Use to save an existing file.
(cid:1) Click Save button on the standard toolbar
(cid:1) or select S ave command from the File Menu
(cid:1) or press <Ctrl+S>.
1.4.3 OPENING A WORKBOOK FIGURE 1.3 Save as... dialog box
A workbook can be opened using one of these ways:
(cid:1) S elect O pen command from the File menu which
displays the Open dialog box. Then find and select the
file to open.
(cid:1) Click Open button on the Standard toolbar.
(cid:1) Or Press <Ctrl+O> key combination.
1.4.4 CLOSING A WORKBOOK
(cid:1) S elect C lose command from the File menu to close the
current workbook.
FIGURE 1.4 Open dialog box
(cid:1) Or Press <Ctrl+F4> key combination.
MICROSOFT EXCEL 2003
5
Chapter 1 Spreadsheet Basics
CHAPTER 1
IN BRIEF
In this chapter you have learnt what a spreadsheet is. Spreadsheet programs
creates and manages the workbooks. A workbook is made up of worksheets.
Each worksheet is like a paper in a file (workbook) that contains data and/or links
in tabular format. A worksheet is composed of cells. A cell is represented by a
reference like “A1”, where “A” refers to column index and “1” refers to row index.
You also studied main file operations in Microsoft Excel. Some useful key
combinations for Worksheet operations are:

Create a new workbook <Ctrl+N>
Open an existing workbook <Ctrl+O>
Save the current workbook <Ctrl+S>
Close the current workbook <Ctrl+W>
Print the workbook <Ctrl+P>
yy
rr
aa
mm
mm
uu
SS

MICROSOFT EXCEL 2003
6
Speadsheed Basics Chapter 1