Pay Thanks to ALLAH, not to Me.

Waqar Aamir Katiar

Contact No. 031-22-22-99-69 & 0300-2831681

Microsoft Excel

Prepared by
Waqar Aamir Katiar
Gulshan-e-Hadeed, Karachi
Contact No. 031-22-22-99-69
0300-2831681
Email: waqaraamir@msn.com
waqaraamirkatiar@facebook.com
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681

Course Outline
Basic Excel for Beginners
 Concept of Excel
 Importance of Excel
 Spread Sheets
 Column
 Rows
 Cells
 File menu
 Edit
 View
 Insert
 Format
 Tools
 Data
 Windows
 Help
 Data Entry
 Data Editing
 Data Formatting
 Simple Math operations
 Applying formulae
 Formulae of different fields
 Print
 Protect
 Formatting Sheets
 Charts & Graph
 Data Formatting
 Data Presentation

Advanced Excel for Professionals

 Data Formatting
 Data Presentation
 Financial Ratios & Formulae
 Financial Reports
 Accounting Ratios and Formulae
 Accounting Statements
 Statistical Formulae
 Managerial Formulae
 Mathematical Formulae
 Banking formulae and Statements
 Geometric Formulae (Optional)
 Trigonometric Formulae (Optional)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Concept of Excel:
Excel and its Concept
It is an application designed by worlds leading Software Company named
Micro Soft. It is included in Microsoft Office package. Excel is look like a very
light and sleek application but it is like a sky which covers whole land. It is just
not a spread sheet but it is a time and recourses saving application. Excel is
applicable in every organization across the globe. Command on excel give you
an additional benefit in your career. Nowadays accountants, bankers,
statitians and other professionals who are related to data cant work without
excel.

Importance of Excel:
Excel is so important for any organization and individual. It saves time. In
addition excel have too many solutions according to different fields. Like
finance, statistics and e-t-c. Now excel is necessary for every office, home and
institution. Professionals enhance their abilities through it. We can use excel
in decision making too. It has a vast and broad perspective.

Now we are going to learn Excel. Are you ready?

Spread Sheets
When we starts excel, we have a sheet with many boxes, it is an spread sheet.
We can say it work sheet.

Rows
Excel has 65536 rows on every spread sheet

Column
Excel had 256 columns. They are in ABC series
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Cells:
Small boxes on sheet called Cells. They have names like that A6. it means this
cell is located in A column and 6th row.

File Menu

This is File menu. It has several

options

New for opening a new sheet

Open for opening of existing file
Close for closing excel
Save for save
Save as for saving a file with
different name or extension
Save as Web Page for saving a
sheet as an html document.
Save Workspace for creating a
separate space for files in disk.
File Search for finding files
Permission for restriction
Web page Preview for viewing
current sheet as a web page
Page setup for setting page
margins, size e-t-c.
Print Area is for setting an area
for print
Print Preview for viewing
document, how it will be on
paper
Print for printing
Send to for mailing sheet
Properties for sheet properties
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Edit Menu
Undo for cancellation of any
activity
Redo for re- doing the same
activity
Cut for cutting a part from
sheet
Copy for coping a selecting
part
Paste for pasting that selected
part of sheet which is cut or
copy before
Paste special for inserting a
special selected area
Paste a Hyperlink for
pasting a link of other file
Fill for different operations
Clear for various operations
Delete for various operations
Delete Sheet for Deleting
existing sheet from file
Move or copy for moving
data to other sheet
Find for finding certain
number or word
Replace for replacing a
certain number of word
Go To for going on a defined
location

View Menu
Normal for viewing page
normal
Page Break Preview for
viewing print area of page
Task Pane for getting help
Toolbars for displaying tool
menus
Formula bar for showing it
Status bar for showing it
Header and footer for a
showing specific matter on top
and bottom of every page.
Comments for writing
comments on a cell
Custom view for viewing a
predefined area on sheet
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Full Screen for viewing sheet on full screen of computer
Zoom for viewing and adjusting sheet size according to your preference

Insert Menu
Cell for inserting cells
Rows for inserting rows
Column for inserting column
Worksheet for inserting worksheet
Chart for insertingCharts
Symbol for inserting different signs
Page Break for breaking page
Function for inserting formulae
Name for various options about cell
Comment for writing something about cell
Picture for inserting picture and various
Options
Diagram for inserting Orzanization Chart
Objects for inserting from other files
Hyperlink for inserting link of other sheet

Format Menu
Cell for various options of cell
Rows for various options of rows
Column for various options of
column
Sheet for various options of sheet
Auto Format for various options
Conditional formatting for
different
conditions applied on cells
Style for font, comma and other
styles
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Tools Menu
Spelling for checking spells of words
Research for online references
Error checking for finding errors
Speech for listen data in voice
Shared Workspace and
Shared workbook for various options of
sheet
Track changes for various options of sheet
Protection for securing document
Online Collaboration for online data
sharing
Goal seeking for achieving desired
amount or number
Scenarios for defining and allocating a
selected area
Formula auditing for verify formulae
Macro for recording a process
Add-ins for various options related to data
and Information
Auto Correct options a formatting tool
Customize a formatting tool for cell
Options a formatting tool

Data Menu
Sort for arranging data in an order

Filter for analyzing data

Form for viewing data in a form

Subtotal for sum of desired data

Validation for formatting cell

Table for Table formatting

Text to Column for formatting cells

Group and Outline for grouping of

cells

Pivot for Pivot table options

Import external data for data sharing

Lists for creating list and formatting
XML for XML files
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Windows Menu
New Window for opening new excel file

Arrange for formatting style

Hide for hide sheets

Split for break sheet into parts

Freeze panes for dividing sheet areas

according to data

Help Menu
For getting any type of help related to excel

Data Entry in Excel

Type your desired data in cells

Data Editing
First of all you have to highlight area
For highlighting
If it is only one cell the click on it or go through keyboard or write its location in cell
address.
You can see A2 in cell address

If it is more then one cell then press mouse left buttons on cell where your desired
range in start and hold button, drag mouse till your range ends.

You can highlight are by keyboard. Go to cell from your

Range starts press Shift button of keyboard hold it
And by direction arrow keys go till end then stop
Holding shift button
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Cut
Cut means cut your highlighted cell or area from sheet.
Highlight your cell or area on sheet. Press Ctrl + X (press hold Ctrl and press
x)
Or press a button (scissors) named Cut

When you cut a text then you can paste it

On anywhere in sheet but highlighted text
Will be removed from highlighted area

Copy
It is also like cut but main deference in cut and copy is the highlighted text still
remains there after pasting it on other area
Command from Keyboard is Ctrl + C and button for it
Is a button with image of (two papers)

Paste
After cutting or copying highlighted area. We press Paste button with image of
(A Bag and Envelope).
Keyboard command is Ctrl + V

Change Font Color

Highlight Cell or Area then press Button (A with Red Underline). You will see a
small window with many Colours select any one
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Highlight Text or Data with Colours
Highlight cell or area then press a button with image of (Paint Bucket). A new
small window will open. It shows you many colours. Select anyone.

Font Style and Size

Highlight cell or area.
Go in Format Menu
Press Cell. You will found this
Window. Press font button

You can change font style, size

Colour. And
Strike a line through text
Like that
Super script (like exponential in
Mathematics or write powers on
Numbers like squares and cube)
X2 - 23
Sub script for writing numbers
Below text or number, In
Chemistry we write molecules
Under element like H2O
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Format Numbers

For formatting numbers

And Language

Alignment
Degrees for rotate font

Wrap text for adjusting

Text in one cell, size of
Will be same, size of cell
Will increase

Shrink to fit for adjust

Font in a line, size of font
Will reduced

Merge cell for making one

Big cell by joining cells

Context for font direction

 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681

Patterns for cell

Patterns fill colours
Or giving patterns
In cells

Borders and lines for

cells
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Font Alignment
Highlight and press these buttons for centre, Left or right
Text alignment

Increase Decimals
for increasing decimals press

For Decreasing decimals

Print Preview for viewing document before print. How it will be on paper
Press button with image of (a paper and a magnifying glass)

Email Sheet press button with image of (an envelope)

Arranging data in ascending or descending order

It is drawing menu. We can draw a single line to a flowchart from it

For making a Chart or a graph

For getting help type in bar then press Enter

For Cell Borders

 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Simple Mathematics Operations

Adding
For adding we have many methods
First one is Auto Sum (This Function is used for sum data when it is in a row
or column). When you press this button then it will show range of data adjust
it according to your requirement and show you range with cell names then
press Enter button.

Other method is go to formula bar and write

=sum()
Then define range through mouse or keyboard.
If you want to write range then it write in brackets like =sum (E1:E3)
It means in E column from row 1 to row 3

An other method which is for scatter data.

In this method we will write cells names in formula bar and by pressing + sign
with them like =C1+C5+D2+E4 then we press Enter button
We can select desired cells through mouse and keyboard

An other method for scatter data is

=sum(cell,cell,cell)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Subtracting
Go to formula bar write
= then cell name like C2 press – sign then second cell name like D3,
you can add more and more cells in the formula according to you need.

If you have data in a row or in a table and you have to subtract it from another
cell. Like you have a task to subtract sum of a column from a single cell.

Go to formula bar write

= (then cell names from you are subtracting
Press – sign
Write Sum(Define Range)
Close )

Like that
=(C2-Sum(D1:D4))
=25-(5+6+4+3)
=25-18
=7

If you have another condition

You have to subtract sum of amount of a column, but it is not in a series. We
have one cell is C2 and we have to subtract a sum of cell which are not in same
column
Like we have 5 different cells

Write formula
=(cell – Sum (cell name press ,
Cell name, cell name))
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Product (Multiplication)
It is also like subtracting but we use * sign instead of x
Or in formula bar we write =cell*cell*cell

Other Method is write =Product (define range)

Divide
Go to formula bar
Press = Cell name / sign cell name
Like =D2/D3

If we have to divide sum of cells from a cell then we have to define range of
sum
Go to formula bar write = cell / Sum (range)
Like =D1/Sum (E1:E3)

If data is scattered then, it will be like

=Cell / Sum (Cell, Cell, Cell)

=D1/Sum(E1,F2,E3)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Percentage
Ali Got 314 numbers out of 325. What is his Percentage?
In Mathematics, We have learned

Percentage = Marks Obtained x 100

Total Marks

If cell D1 = Marks Obtained

D2 = Total Marks
Solution in excel is same as in mathematics

=D1/D2*100

If you want to add % sign then don’t write *100.

Write =D1/D2 press Enter. Then press % Button

When Ali comes to home his Papa asked what your result is. Ali said to him,
I got 96.62% marks from 325 marks. How Ali’s Papa find his obtained marks.

In Mathematics

Total Marks x Percentage Obtained

100

If D1 is Percentage Obtained
D2 is Total Marks
Then first we Divide D2 by 100 then multiply it with D1

Like that =D2/100*D1

After solving it Papa gave 1000 rupees to Ali for shopping. Ali bought a watch
of 1000 rupees. When he came to home papa asked price of watch. Ali told
price.
Papa said this is not an actual price of it. Sales Tax (Value Added) is included
in it. Tell me its actual price without tax. Tax is 16%.
Ali used a mathematical formula

Total Amount_________
(100 + Additional) %
If D1 is Watch price
D2 is Percentage

=D1/D2

(Note: if you are not using %age sign then multiply

It with 100)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Some Important Formula

Average
Write =Average(Range) or press Average Button

If Data is scattered

=Average (Cell, Cell, Cell)

If one cell is sum of 3 cells

= Average (Cell, Range)

= Average (Cell, Cell:Cell)

Count
Count how much cells Contain Data in a list

=Count (cell:cell)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Count cells of Value Equals to 6

=Countif(Range, “=6”)

Count cells of Value is Greater then 6

=Countif(Range, “>6”)

Count Cells of value is Less

Then 6

=Countif(Range, “<6”)

Cells Values are equal to or greater then 6

=Countif(Range, “>=6”)

Maximum Value
For finding the maximum value cell
in an area

=Max (Range)

Minimum Value
For Minimum Value in
an
Area

=Min(Range)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Conditional Formatting
Go to Format Menu, click on Conditional format

Type your criteria, like cell value is between 50 and 60

Now press format button, you will find a window

In this window go in colour select blue colour
it means all value between 50& 60
will be shown in blue colour.

Result is
Here
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Square Root of a Number
For finding an square root of a number write formula in formula bar

=SQRT(Cell)

Square of a Number
Square means a number has a power of 2
Like (5)2. formula for square is

=Power(Cell, Power)
=Power(C1, 2) here we write 2 because square means a number with power is
2

For Cube we Write =Power(Cell, 3)

=Power(C1, 3)

If a number has Power 6 then similarly

=Power(C1, 6)

Sum Product
If you have to find sum of different products
Like 25 x 15
12 x 20
18 x 25

For solution we have a Common idea is multiply 25 with 15, 12 with 20 and 18
with 25, add all answer. This method is right but it is a time consuming when
you have a large data.

For solving it easily and quickly, go to formula bar and write

=Sumproduct(Range,Range)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Sum If
If we have to sum only desired numbers from a list. We use a formula

=Sumif(Range,Criteria,Range)

If you have three products red, blue and

Green. If you want to sum all blue products

Just write blue in formula, on the place of

Criteria

=Sumif(C1:D9,”Blue”,C1:D9)

Add all Products except B

Sum all data – Blue Products

 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Factorial
If you want to calculate factorial of 15. you need a time because

10! =10*9*8*7*6*5*4*3*2*1

In Excel you just write in formula bar

=Fact(Cell)

Or =Fact(10)

Least Common Multiplication

=LCM(range)

Logarithms
=Log(Cell)

Natural Log

=Ln(Cell)

Converting Numbers into Romans

=Roman(Cell)
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Date and Time
For Inserting Date

=Date(Year,Month,Date)

For Insert Current Date

Ctrl + ;

Insert data which updates

=Today()

Time
For inserting time
=Time(Hour,Minute,Second)

For Current Time

Ctrl + Shift + ;

For time which updates

=Now()
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
Accounting
Depreciation
Depreciation is an expense incurred by utilizing an asset according to time or
usage. We can calculate depreciation by following methods

 Straight Line / Equal Instalment Method

The graph of this depreciation expense is a straight line. In this method we
calculate a constant amount of depreciation expense.

Annual Depreciation = Cost – Salvage Value

Life

=SLN(Cost,Salvage Value,Life,)

 Sum of Years Digit Method

In this method we assume that the depreciation charge should be more in the
early years of the life of asset

Depreciation = Number of years Life Remaining x (Cost – Salvage)

Sum of years’ Digits

=SYD(Cost,Salvage Value,Life,Year)

Write 1 in year for 1st year

For 1st year Depreciation expense is 1633

For 2nd Year

Write 2 in year because it is 2nd year
For 2nd year Depreciation expense is 1307
 Pay Thanks to ALLAH, not to Me.
Waqar Aamir Katiar
Contact No. 031-22-22-99-69 & 0300-2831681
 Declining Method
The fixed-declining balance method computes depreciation at a fixed rate.
After charging depreciation 1st time, the expense will be deducted from asset
value, next year depreciation will be calculated on remaining value of asset.

=DB(Cost,Salvage,Life,Year,Month in that year)