Docstoc

PRACTICAL GUIDE ON EXCEL 2003 WITH SCREEN SHOTS!

Document Sample
PRACTICAL GUIDE ON EXCEL 2003 WITH SCREEN SHOTS! Powered By Docstoc
					   EXCEL 2003 BASICS                                TOMBARI N. BARIMA




THIS EXCEL 2003 BASICS TUTORIAL IS BROUGHT TO YOU BY:




   BARIMA TOMBARI N.
This is a practical tutorial with step by step explanation on how to use the
Microsoft office EXCEL 2003 application for basic practical purposes. It is
written in clear language that is easy to understand. There are screen shots
included all through the tutorial to show you the item under discussion. It is my
honest desire that this tutorial will help you get a good start in the excel 2003
systems.

I will welcome constructive comments and criticisms as well as thank you notes.
Kindly visit: http://successsecrets01.blogspot.com to DOWNLOAD MORE
INTERESTING STUFF AND SUCCESS SECRETS THAT WILL TAKE YOUR
LIFE FORWARD. REFER YOUR FRIENDS AND RELATIONS TO THIS
BLOG BY SHARING AND RECOMMENDING IT. FEEL FREE TO LEAVE
YOUR COMMENTS.

This guide is the original work of the author. All rights are rights reserved. No
part of this publication may be reproduced, stored in any form mechanical or
electronic for sale without the express written permission of the author BARIMA
TOMBARI N. except brief excerpts for reference purposes. You are free to
distribute this material to your friends and relatives on the condition that YOU
MUST NOT ALTER ANY PART OF THE PUBLICATION.

                   GOOD LUCK!


         BARIMA TOMBARI N.
   http://successsecrets01.blogspot.com

   Page 1 of 18                        BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                      TOMBARI N. BARIMA
EXCEL

Microsoft excel is a spreadsheet application.
A spreadsheet refers to a number manipulator; it is used in calculation such as
creating payroll. An interesting feature of Microsoft excel is its ability to
automatically recalculate.

ORGANISATION
Microsoft excel is made up of worksheets – contained in a work book, each
worksheet is made up of rows and columns, the intersection of a row and a column
is called a cell. The vertical stacks are the column and they are represented with
alphabets [A, B, C…] while the Horizontal stacks are the rows and they are
represented by numbers [1, 2, 3…].

OPENING EXCEL
Microsoft excel can be launched as in any other application by double clicking on

its icon            on the desktop or clicking on start and clicking on its icon on the




start menu.
Alternatively, you can click            on   start→all    programmes        Microsoft
office→Microsoft office excel.




Lastly you can type in ‘excel’ after clicking on start        run. Then press enter on
the keyboard or click on ok.
Page 2 of 18                             BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                   TOMBARI N. BARIMA




THE EXCEL IINTERFACE




The excel interface opens with the rows and column visible. It also has the other
familiar toolbars such as,
1. The title bar
2. Menu bar
3. Standard tools bar
4. Formatting tools bar
5. The drawing tools bar

The name box displays the address of the active cell while the formula bar
displays the current formula or the content in the active cell.

Name box           formula bar
The vertical & horizontal scroll bars enable you to scroll through the rows & the
columns respectively.
The active cell is that into which you can type values. It is the presently selected
cell & is indicated with a bold border & has its address in the name box.

INSERTING A WORKSHEET
To insert a worksheet,
  Click on insert menu & click on work sheet you can also insert a new
  worksheet by right clicking on the tab of another worksheet.
Page 3 of 18                          BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                TOMBARI N. BARIMA
   Choose insert
   select worksheet
   And click ok.

RENAMING A WORKSHEET
To rename a worksheet
  Right click on the worksheet you want to rename
  Choose rename
  Type in the name you want
  Click outside to finish

Moving a worksheet
You can move a worksheet to the position of your choice by clicking & dragging
it to the position of your choice.

CHANGING TAB COLOR
To change tab color of a worksheet, right click on the worksheet & choose tab
color then click on the color of your choice.

INSERTING A COLUMN
To insert a column, select the column behind which you want to insert the new
column the column, click on insert on the menu bar & choose column.
   You can also right click and choose insert column.

ADJUSTING COLUMN WIDTH
Move your mouse pointer over the right boundary line of the column until it
changes to a     sign & click & drag to the desired location & width.
Alternatively, select the column you want to adjust the width,
Click on format and type in the size of the column width you want and click ok.
You can also select the column & right click and choose column width. Type in
the size you want & click ok.

HIDING A COLUMN
To hide a column, select the column right click & choose hide column. You can
as well click on format =column = hide column.

TO UNHIDE COLUMN
Select the column before & after the column you want to unhide. Right click &
choose unhide.
DELETING A COLUMN
To delete a column,
  select the column you want to delete
Page 4 of 18                        BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                   TOMBARI N. BARIMA
   right click and choose delete

INSERT A ROW
-To insert a row, select the row above which you can to insert the new row, right
click & choose insert.
-You can also click on insert on the menu bar & choose row.

ADJUSTING ROW HEIGHT
Select the row you want to adjust the height, right click on it & choose row height,
type in the value you want for it and click on ok. Alternatively, you can select the
row, click on format row height & type in your desired value then click on ok.

HIDING A ROW
To hide a row, select the row that you want hide, right click on it & choose hide.

UNHIDING A ROW
To unhide a row, select the row before & after it, right click choose unhide.

DELETING A ROW
To delete a row,
   right click on the row you want to delete
   choose delete
DRAG AND DROP
Drag and drop is used to move the contents of the selected cell to the new location
of your choice. To use drag and drop,
   click and move your mouse pointer over the border of the cell(s) you want to
   drag and drop until it changes to a
   click and drag the cell(s) to the position of your choice

FORMATING
There are many formatting options you can apply to your cell in the format cell
dialogue box e.g. text direction, number, font style, border, etc.

TO FORMAT A CELL (Ctrl + 1)
Click on format on the menu bar & choose cell.
Click on the tab corresponding to the option you want to format e.g. number
alignment font, border etc choose the particular option you want click ok.
You can also select the cell and use the shortcut of Ctrl+1




Page 5 of 18                           BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                   TOMBARI N. BARIMA




TEXT DIRECTION
Text direction is one of the format cell options you can choose
To set the text direction, select the necessary cell(s).
Click on format on the menu bar & choose cells (or right click & choose format
cells.
Click on alignment tab and specify the alignment you want in degrees or click &
drag the text line to the direction you want then click ok.




MERGE AND CENTRE
The merge and centre           button on the formatting toolbar is used to merge the
selected cells and centralize the contents.
The dollar symbol          is for currency, the percentage symbol            for
percentage,(note that percent implies a number divided by 100 and excel
recognizes this fact so when you select 5, 10, 15 and click on   they change to
500%, 1000% and 1,500% respectively. Additionally, 5%*5,000=5/100*5,000)
the increase decimal     button increases the number of decimal places while the
decrease decimal       button decreases the number of decimal places to which
numbers are displayed. The border button       is for setting the border style of
the selected cell(s).
The ascending order      button is used to arrange the numbers in the selected
cells in ascending order(lowest to highest), while the descending order     button
is used to arrange the numbers in the selected cell in descending order (highest to
lowest).
Page 6 of 18                          BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                    TOMBARI N. BARIMA


CALCULATIONS IN EXCEL
The main function of excel as a spreadsheet program is for calculations, therefore,
it is of utmost importance that you understand Excel’s calculations patterns!
Calculations can be based on user defined formulas or on formulas that are preset
by the computer system(functions).

OPERATORS
Operators are the signs used in excel calculations and they include:
Arithmetic:
+ addition
- subtraction
/ division
* multiplication
^ exponent
Logical:
= equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
< > not equal to
:is used to specify a range of cells. For example, G10:G14 means from cell G10 to
cell G14.

!
To tell excel that you want to perform a calculation, you must begin your formula
with an equal sign (=). This is what tells excel that the content of the cell is a
formula.
An important fact to remember is that the address of the cell is used in calculations
therefore, a change in the contents of the cell will cause excel to recalculate so as
to reflect the change. This is a great advantage in using excel for calculations. For
example, type 12 in A10 and 5 in A11 then click on A12 and type in =A10*A11.
Change the value in A10 to 50 and click outside the cell. What happens to your
answer in A12?
CREATE AND COMPLETE THIS WORKSHEET




Page 7 of 18                           BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                    TOMBARI N. BARIMA




THE FILL HANDLE
The fill handle is used to copy a formula down or across a range of cells. It can
also be used in generating sequences and series. The fill handle has a sign at the
bottom right corner of a cell.
To use the fill handle to copy a formula down or across a range of cells,
   click on the cell that contains the formula
   move your mouse pointer over the lower right corner of the cell until the sign
   for the fill handle appears ( )
   click and drag down or across to copy the formula to the required cells

To use the fill handle to generate a sequence or series,
   input the first member of the sequence or series into the first cell and input the
   second member into the next cell
   select the two cells containing the first two members of the sequence or series
   move your mouse pointer over the lower right corner of the selection until the
   sign for the fill handle appears
   click and drag to the required cells
Try these: type JAN in A3 and FEB in A4 select A3 and A4 and use your fill
handle to drag down. What do you notice. Repeat this with 3 and 6, 2 and 4, 6 and
12, Mon and Tue, etc.
ABSOLUTE AND REFERENCE ADDRESSING
Reference addressing is the normal addressing pattern in which a cell address is
represented by the column alphabet and the row number only and the address will
change when the formula in the cell is copied down or across a range of cells. For
example A12; this becomes B12 when copied across and A13 when copied down.
Absolute addressing is used to make a value or an address in a formula to remain
constant as the formula is copied down or across. To use absolute addressing, a
dollar sign is, $, placed before the column alphabet and before the row number.
Example $A$13

FUNCTIONS


Page 8 of 18                           BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                           TOMBARI N. BARIMA
There are various functions in excel. Functions are predefined formulas that perform
calculation by using specific values called arguments in a particular order or structure
(syntax). Functions can be used to perform both simple and complex calculations.
To insert a function,
   Click on the insert function button on the formula bar

   In the insert function dialog box that appears, click on the function you want and click on
   ok




   Type in the arguments for the function and then click on ok or press enter to see the result.




Note: Before you click on the insert function button, you should click on the cell where you
want the result to be displayed.
Create the worksheet below and use it in calculating the functions we will discuss below




Page 9 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                          TOMBARI N. BARIMA




THE SUM FUNCTION
The sum function is used to calculate the sum of the values in the specified range of cells.
To use the sum function,
   Click on the insert function button
   Choose the sum function and click on ok
   Specify the range of cells you want to sum up or use your mouse to select the range by
   clicking and dragging
For example, in our RESULT SHEET, to see the total score,
   Click on the insert function button
   Click on sum and click ok
   In the function argument window that appears, type in B7:G7 or use your mouse pointer to
   select the range and
   Click on ok or press enter to see the result
Note: to calculate the total result for others, use your fill handle to copy down the formula in
H7




Page 10 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                         TOMBARI N. BARIMA




before releasing the mouse                             after releasing the mouse
This process is applied when we are calculating all the other functions: we calculate for just
one person and then use the fill handle to calculate for the rest
AVERAGE FUNCTION
It is used to find the average value of the numbers in a specified range of cells.
To use the average function,
    Click on the cell where you want the result to be displayed
    Click on the insert function button and choose the average function then click on ok
    Type in the range of cells you want to calculate the average or use your mouse pointer to
    select them
    Click on ok to display the result
In our RESULT SHEET, to calculate the average for the first student
    Click on I7
    Click on the insert function button , click on average and click on ok
    Type in B7:G7 or use your mouse pointer to select the range
    Click on ok or press enter to see the result.
You can use the fill handle to calculate the result for others

MAX AND MIN FUNCTIONS
The MAX and MIN functions are used to calculate the maximum and minimum values
respectively in a specified range of cells. Try to use the Min and the Max functions to
calculate the maximum and the minimum score of each student

TH IF FUNCTION
Page 11 of 18                               BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                             TOMBARI N. BARIMA
The IF function is one of the many logical functions in Excel. It is used to analyze the
specified argument and return one value if true and another if false.
A simple analogy to make us understand the IF function is this:
Remember when you were going to school and then your mother tells you: “IF YOU GO TO
SCHOOL, USE THIS N200 FOR LUNCH, IF YOU DON’T GO, EAT THE FOOD IN THE
POT.” Here the Logical Test is “IF YOU GO TO SCHOOL”. The Value If True is use this
N200 for lunch. The Value If False is “EAT THE FOOD IN THE POT” A more modern
example is this: A shop can decide to give 20% discount to all shoppers who buy goods worth
N20,000. Analyzing this, the Logical Test is ‘IF YOU BUY GOODS WORTH N20,000’. the
‘Value If True’ is that you are given a 20% discount while the ‘Value If False’ is that you do
not have any discount.
To use the IF function,
   Click on the insert function button and choose IF then click on ok
   In the logical test box type in the condition which you want to be evaluated
   In the value if true box, type in what you want if the condition is met, e.g. PASS
   In the value if false box, type in what you want if the condition is not met, e.g. FAIL
   Click on ok to display the result

In our result sheet, to determine those that fail and those that pass using the IF function and a
pass mark of 50,

   Click on I7
   Click on the insert function button
   Click on IF and click on OK
   In the logical test box, type in I7>=50
   In the value if true box type in PASS
   In the value if false box type in FAIL
   Click on OK to display the result .
   Use the fill handle to calculate the result (pass/fail) for other students

NOTE: In our exercise, we have used a pass mark of 50 and instructed that all that score 50
and above should pass while those that score below 50 fail. The IF function has other useful
applications such as giving discount if the purchase is up to a certain amount.

NESTED FUNCTION
Nested function refers to the use of one function within another function. For example,
=IF (AVERAGE(F2:F5)>50,SUM(G2:G5),0)
I.E. LOGICAL TEST: AVERAGE(F2:F5)>50
VALUE IF TRUE: SUM(G2:G5)
VALUE IF FALSE:0

LEARNING FUNCTIONS
There are so many functions in excel that we cannot learn all of them. To learn how to use a
function,
   Click on the insert function button
   Click on the function you want to learn
   Click on help on this function

Page 12 of 18                                  BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                          TOMBARI N. BARIMA




   You will now see a detailed information about that function, how to use it and examples.
For example, try to learn the product function and use it to calculate the product of this
numbers: 12, 18, 20
Multiply the previous answer by 50.
CHARTS
Microsoft excel enables you to represent your data graphically with various types of charts.
This is of much importance as it enables someone to easily spot the trend in the data making
for easy reading and interpretation of the data. The chart wizard enables you to easily create
charts
To Create a Chart,
   Click on the icon for the chart wizard      on the standard tool bar (the chart wizard now
   starts)
   Step 1 of 4 of the chart wizard enables you to choose the type of chart you want to create.
   Click on the chart type of your choice and click on next to continue




   Step 2 of 4 of the chart wizard enables you to choose the source of the data to be
   represented on the chart. Click on the button next to data range and use your mouse to
   select the data range you want (by a click drag action) and click on the button again to
   return to the chart wizard. Click on either series in rows or series in columns and watch the
   preview of your graph




Page 13 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                          TOMBARI N. BARIMA




NOTE: The graph is interactive and any changes made to it can be seen as you are creating it.
You can easily use a click –drag action to select the area of the chart you want to use for the
data range.
Step 3 of 4 of the chart wizard enables you to input chart options such as titles, data labels,
etc. Input the necessary ones and click on next to continue. It will be important to use titles
that enable people to easily read the data in your chart and understand clearly what you are
trying to convey to them. NOTE when creating a pie chart, click on data labels in step 3 of
the chart wizard and tick the box for value and percentage if you want the value and the
percentage for each sector to be displayed.




   Step 4 of 4 of the chart wizard enables you to specify the location of the chart either as a
   new worksheet or as an object in any of the existing worksheets in the workbook
Click on finish to end the wizard and see your finished chart.


Page 14 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                          TOMBARI N. BARIMA




MODIFYING CHART
Charts can be modified in so many ways. However, I recommend selecting the chart and
going back to the chart wizard as this (I believe) will be much simpler especially to the
beginner.
Select the chart and click on the icon for the chart wizard on the standard tools bar. This
enables you to change whatever you want to change about the chart

You can also select the chart and click on chart on the menu bar then use the available options
to modify the chart as necessary. These options are also available when you right Click on the
chart.




ADDING DATA TO CHART
To add new data to your chart,
   Select the data you want to add
   Click and drag it into the embedded chart.
Alternatively,
   You can select the chart
   Click on chart on the menu bar
Page 15 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                           TOMBARI N. BARIMA
   Choose add data
   Use your mouse to select the data range you want to add and click on ok.

PRINTING WORKSHEET
Like other documents, worksheet can be printed. To print a worksheet:-
   Click on the file on the menu bar & click on print preview to see the appearance of your
   print                                                                                job.




   Click on page break preview to see how your work sheet has been divided into pages, you
   can use your mouse pointer to click & drag the lines to your choice location.




   Click on the set up tab to open the page setup window & then click the page tab to select
   the orientation i.e., portrait or landscape.
   Click on header & footer tab your to type in the head & footer of your choice if necessary.
   Click on the sheet tab & tick the radio, button for gridlines under print what if you want the
   gridlines to appear.




Page 16 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS                                            TOMBARI N. BARIMA




Setting to make grids appear                 result of setting(grids shown)
  Click on ok when you’re done.
  All these are to ensure that you print the correct data & in the correct format.
  Click on print when you’re done.
SETTING PRINT AREA
  You can select the area you want to print & click on the file →print area→ set print area.
  To clear print area:
     Click on file→ print area → clear print areas.
  The print dialogue box – It allows you to select what to print. To access it, click on file &
  choose print – (Ctrl +P)




SPREAD SHEET SOLUTIONS
Spread sheet solutions are automatic spreadsheets that came with the system which can be
used to automate a specific task or proffer a solution to a task. Examples are time card,
invoice, expense statement balance sheet, etc. To insert a spreadsheet solution,
      Right click on any worksheet and click on insert
      Click on spread sheet solutions and click on the solution of your choice
      Click on ok.




Page 17 of 18                                BAMATRONIC COMPUTER SOLUTIONS
EXCEL 2003 BASICS             TOMBARI N. BARIMA




Page 18 of 18       BAMATRONIC COMPUTER SOLUTIONS

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:44
posted:11/17/2011
language:English
pages:18