Excel

Introduction: - Excel Is An Office Package Used For Calculation And Data Analysis. M.S Excel
Provide A Workbook. A Work Book Also Called Spreadsheet All An Electronic Sheet Made Of All Row And
Column Used For Planning A Project For Making Financial Report Of An Organization.

Extension Name of Excel: - XLSX

Element of Electronic Spreadsheet:-

1. Workbook: - A Workbook Is The Collection Of Worksheet Where The User Work. A Workbook
Can Contain Maximum 255 Sheets To Work In. Wherever A Workbook Open It Displayed 3
Worksheet By Default.
2. Worksheet: - A Worksheet Is The Place Where The User Work And Perform Arithmetical And
Function Calculation By Default It Is Named As Sheet1, Sheet2, and Sheet3. A Sheet Can Be
Renamed Too.
3. Row: - A Row Is The Horizontal Block Of The Worksheet Moves From Left To Right. A Row Is
Named As Numbers 1, 2, 3, ---------------------Etc. There Are 1048576 Rows Comes Under Excel
Application.

Bottom. It Is Named As A, B, C, ------------------Etc. There Are 16384 Columns In An Excel Worksheet.

4. Cell: - The Intersection Between Row And Column Produce A Cell And Every Cell Having An
Address With Column X Row.

Example: - A1, H5, E11 ---------------- Etc.

5. Formula: - Formulas Are The Tricks With Specific Producers Field The Desired Result. A Formulas
Is Initiate With (=) Sign.
6. Function: - Function Is Built In Commands To Perform A Specific Task. A Function Is System
Define Reserved Words Specific Argument To Performed A Certain Task.
7. Formula Bar: - It Is The Bar Where Write & Edit Formula & Function According To Our
Requirement.
8. Name Box: - It Is The Box On The Formula Bar That Gives The Address Of Each Cell.
9. Active Worksheet: - The Worksheet On Which Presently We Are Working.
10. Sheet Tab: - Wherever We Open Workbook We Fined 3 Worksheet By Default By Using Sheet
Tab We Can Open Any Worksheet Tab Is Identified By Level Given As Sheet 1, Sheet 2, Sheet 3,
Etc. You Can Rename The Sheet Tabs.
 Points in Excel
- Normal Selection Pointer (By Default)

- - Cell Pointer

- Apply Pointer

- Hanging Pointer

- Row Selection Pointer

- Column Selection Pointer

- Column Adjust Pointer

- Row Adjust Pointer

Shortcut Keys
1. Merge Cell – (Alt+H+M)
2. Center Alignment- (Alt+H+Am)
 3. Column Auto Fit – (Alt+O+Ca)
4. Bold- (Ctrl+B)
5. Auto File Up- First Selection Then Press (Ctrl +D)
6. Font Size Increase- (Alt+H+Fs)
7. Font Color – (Alt+H+Fc)

Quick Access Toolbar: - A Toolbar Provided To Access Quickly

Used Command In The Application.

Ribbon: - A Place That Contain Item Commands Bottom and

Organized Into Tabs

Tabs: - The Ribbon Is Divided Into 7 Tabs In Excel. Home, Insert,

Page Layout, Formulas, Data, Review, View.

Office Button: - The Office Button Replace The Old File Menu
Clicking The M.S.Office Button Will Display A List Of Command
Click New, Open, Save, Save As, Print, Public, Prepare, Close.

Contextual Tabs: - This Tab Only Appear When You Selected

Particular Option. They Making Further Appropriate Commands
Available Only When They Are Required.

Groups: - Each Tab Is Divided Into Groups And It Is Those That

Contains The Commands Button.
Home Tab (Groups)

Clipboard
Undo- This Option Is Used To Move the Position One Step Back. (Ctrl + Z)
Redo- This Option Is Used To Move the Position One Step Forward. (Ctrl + Y)

Cut- This Option Is Used To Cut The Selected Text Or Picture. (Ctrl + X)
Copy- This Option Is Used To Makes Copy Of Selected Text Or Picture. (Ctrl + C)

Paste- This Option Is Used To Insert The Item That You Cut Or Copied. (Ctrl + V)

Paste Special- You Can Paste the Copied Text as Per Your Requirement like Unformatted
Text, Formatted Text, and Picture Etc.

Format Painter- You Can Use The Format Painter On The Write Tab To Apply Text Formatting And
Some Basic Graphics Formatting, Such As Borders And Fills.

 Copy Format-(Ctrl + Shift + C)

 Paste Format-(Ctrl + Shift +V)

Office Button: - The M.S. Office Clipboard Allows You To Copy Multiple
Text And Graphics From Office Document Or Other Programmed And
Paste Them Into An Other Document. (You Can Maximum 24 Items Cut
or Copied In Office Clipboard)

Font
Font- This Option Is Used To Change The Font Face. (Alt + H + Ff)

Font Size- This Option Is Used To Change the Font Size. (Ctrl + Shift + >, <)

Grow Font- This Option Is Used To Grow Font Size. (Ctrl+Shift+ >)

Shrink Font Size- This Option Is Used To Shrink Font Size. (Ctrl +Shift+ <)

Clear Formatting- This Option Is Used To Clear All The Formatting From The Selection, Living
Only The Plain Text. (Alt + H + E)

Bold- This Option Is Used To Make The Selected Text Bold. (Ctrl + B)

Italic- This Option Is Used To Italicize the Selected Text. (Ctrl + I)

Underline- This Option Is Used Underline the Selected Text. (Ctrl + U)

Border – This Option Is Used To Set the Border on the Selected Cell (Alt+H+B)

Fill Colour- This Option Is Used To Fill Colour In Selected Cell.

Font Colour- This Option Is Used To Change the Font Colour. (Alt + H + Fc)

Alignment Group

Top Alignment: - Top Align Test To The Top Of The Cell. Central Align Test To
The Cell.

Bottom Align: - Align Test To The Bottom Of The Cell.

Left Alignment: - Align Text To The Left Of The Cell Horizontally.

Right Alignment: - Align Text To The Right Of The Cell Horizontally.

Orientation: - Rotate Text A Diagonal Or Vertically Orientation. This Option Is

Used For Labeling Narrow Column.

Increase Indent: - Increase The Margine Between The Border And Text In The Cell.

Decrease Indent: - Decrease The Margine Between The Border And Text In The
Cell.

Merge & Centre: - Joins the Selected Cell into One Large Cell and Centered and
Contain In the New Cell.

Wrap text: - All The Contains Visible with in a Cell by Displaying in Multiple Lines.

Number Groups

General: - The General Formate of No. Will Be Applied. It Is Mainly Used To

Remove Currency Symbol Or Any No. From Selected Cell.
Number;-The Number Format Allowed You To Set No. Of Decimal Places.

Currency: - Number Of Preceded With A Default Currency Sign Such As $ Rs. Etc.

Accounting: - Currency And The Decimal Places Set With The Number But The
Setting Of Currency Symbol Will Different From Currency Formate Currency
Symbol Are Coming With Left Alignment. Wherever As Number Value Are Right
Alignment N The Cell.

Date: - Date Format Display Various Types Of The Formatting.

Percentage: - Percentage Format Multiple The Cell Value Of 100 And Display The
Result With Percentage Sign.

Fraction: - Use This Option Display Number as Fraction.

Scientific: - Number Are Display Scientific Notation.

Text: - This Option Is Used To Change Number To The Text. Without Formatting.
This Is Useful For Numeric Labels May Include Text.

Special: -Special Format Are Use To Formatting Text And Data Base Value. This Is
Mainly Used For Telephone No., Zip Code…. Etc.

Custom: - Produce A List Of Customer For Number Data And Time That You Can
Selected From All Add Too.

Shortcut Keys
Date: - Ctrl+;

Time: - Ctrl+ Shift+;

Top Alignment: - Alt+ H+ At

Middle Alignment: - Alt+ H+Am

Bottom: - Alt+ H+Ab

 Align Text Left: - Alt+ H+Al

Align Text Right: - Alt+ H+Ac

Align Text Right: - Alt+ H+Ar

Orientation: - Alt+ H+Ao

Decrease Indent: - Alt+ H+5

Increase Indent : - Alt+ H+6

Wrap Text: - Alt+ H+W

Merge Cell: - Alt+ H+M

Numbers Groups
General Alt+H+N+↓

Accounting Alt+H+An

Comma Alt+H+K

Increase Decimal Alt+H+10

Decrease Decimal Alt+H+9

Format Cell Ctrl+1

Conditional Formatting (Alt+H+L) - You Can Use Something Called Condition

Formatting In Your Spreadsheet. Conditional Formatting Allow To Change The
Appreance Of A Cell Depending On Certain Condition What We Will Do Is To
Colour The Overall Average On Our Student Exam Spreadsheet Depending On The
Grade. Here The Spreadsheet We Shall Be Working On.

Format As A Table(Alt+H+T): - Quickly Format A Range Of Cell & Convert It To A

Table By Choosing The Predefined Table Style.
Cell Style (Alt+H+J): - Quickly Format A Cell By Choosing From Predefined Cell
Style. You Can Also Define Your Own Cell Style.

Cell Group
Format: - This Option Is Used To The Row Height, Column Width Organize Sheet
Or Protection Sheet & Hide Cells.

Insert Cell: - This Option Is Used To Insert, Row, Column, Sheet, You Can Also
Insert Multiple Cell Row And Column In Worksheet.

Delete Cell: - This Option Is Used To Delete Row, Column, And Cell Sheet From
Your Worksheet.

Shortcut Keys
Row Height Alt+H+O+H

Auto fit Row Height Alt+H+O+A

Column Width Alt+H+O+W

Auto fit Column Width Alt+H+O+C+A

Default Width Alt+H+O+D

Hide & Unhide Alt+H+O+U

Move or Copy Sheet Alt+H+O+M

Rename Sheet Alt+H+O+R

Tab Colour Alt+H+O+T

Protect Sheet Alt+H+O+P

Editing Group
Autosum(Alt+=): - This Option Is Used To Display The Sum, Average, Max, Min, Of
The Selected Cell Directly After The Selected Cell.

Fill (Alt+H+Fi): - By Using This Option You Can Create Liner Growth On A Specified
Row &Column. You Can Quickly Fill Class In A Range With A Series Of Numbers Or
Dates Or With A Series For Days, Week, Month Or Year.

Clear(Alt+H+I): - This Option Is Used To Delete Everything From The Cell Or

Selectively Remove The Clear All, Clear Formatting, Clear Content, Clear
Comment.

Sort & Filter (Alt+H+S): - Change Data So That It Is Easier To Analyze. You Can Sort
The Selected Data In Ascending Or Descending Order. You Can Temporary Filter
Out Specifies Value.

Find & Select (Alt+H+FD): - This Option Is Used To Find &Select Specified Text
Formatting And Other Type Of Information Within The Work Sheet. You Can Also
Replace The Information With Text In Formatting.

Insert Tab
Table Group
Pivot Table(Alt+N+V): - This Option Is Used Summaries Data Using A Pivot Table
Makes It Easy To Arrange & Summaries Complicated Data & Drill Down On
Details.

Table (Alt+N+T): - Create A Table To Manage Analyze Related Data Tables Makes
It Easy To Sort Filter A Format Data With In A Sheet.

Picture - This Option Is Used To Insert Picture From File In Your Document. (Alt + N + P)

Clip Art- This Option Is Used To Insert Clip Art in the Document. You Can Also Change How A
Picture Or Clip Art Is Positioned With Text Within A Document. (Alt + N + F)

Shapes- This Option Is Used To Insert Ready-Made Shapes. Such As Rectangle, Circle, Arrows,
Lines, Flow Chart, Symbol and Callouts. (Alt + N + She)
Smart Art- This Option Is Used To Insert a Smart Art Graphics to Visually Communicate
Information.

(Alt + N + M)

Chart- Ms Word 2007 Includes Many Different Types Of Chart And Graphics That You Can Use
To Inform Your Audience About Inventory Level, Organization Changes, Sales Figure, And Much
More. Charts Are Fully Integrated With Office Word 2007. You Can Also Copy A Chart From
Excel To Ms Word 2007. (Alt + N + C)

Links
Hyperlink- This Command Is Used To Create A Link To A Web Page, A Picture, An E-Mail
Address, Or Link A File. (Ctrl + K)

Text
Text Box- This Option Is Used To Insert Preformatted Text Boxes. (Alt + N + X)

Word Art- This Option Is Used To Insert Decorative Text in Your Document. (Alt + N + Q)

Header & Footer - Headers and Footers Are Areas in the Top, Bottom, and Side Margins
(Margin: The Blank Space outside the Printing Area on a Page.) Of Each Page in a Document.
You Can Insert or Change Text or Graphics in Headers and Footers. For Example, You Can Add
Page Numbers, The Time And Date, A Company Logo, The Document Title Or File Name, Or The
Author's Name. (Alt + N + H), (Alt + N + O)

Signature Line- You Can Create A Signature Line By Underlining Blank Spaces. The Best Way
to Do This

Object- By Using This Option You Can Insert An Object Such As Any File, Word Pad Document,
Ms Excel, and Ms PowerPoint Presentation Etc.

Symbol- Insert Symbol That Are Not On Your Keyboard, Such As Copyright Symbol, Trademark
Symbol, Paragraph Marks and Unicode Characters. (Alt + N + U)

Page Layout Tab

 Themes

Themes- You Can Change the Over All Design of the Entire Document, Including Color, Fonts
and Effects. (Alt + P + Th)

Page Setup

Margins- This Option Is Used To Select the Margins Sizes for the Entire Document or the
Current Section.

Page Margins Are The Blank Space Around The Edges Of The Page. In General, You Insert Text
And Graphics In The Printable Area Between The Margins. However, You Can Position Some
Items In The Margins — For Example, Headers, Footers, And Page Numbers .

 Normal Gives You One Inch On All The Sides Of The Page.
 Narrow Margins Work Well With Multicolumn Documents, Giving You A Little More
Room For Each Column.
 Moderate Margins With Three- Quarter Inches Left And Right Let You Squeeze A Few
More Words In Each Line. (Alt + P + M)
 The Wide Preset Gives You More Room For Marginal Notes When You Are Proofing a
Manuscript.
Orientation- This Option Is Used To Switch The Pages Between Portrait And Landscape
Layouts. (Alt +P)

Size- This Option Is Used To Choose A Paper Size For The Current Section. (Alt + P + Sz)
Print Area: - Merge Specify Area Of The Sheet From The Printing Copy Page Break Of Inserted
Above And To The List Of The Selection.

Back Ground (Alt+P+G): - This Option Is Used To Choose In The Image To The Display As The
Back Ground Of Sheet.

Print Title (Alt+P+I): - Specify Row & Column To Report On This Printed Area.

Gridlines
View(Alt+P+Vg): - Show The Lines In The Between Row & Column In The Sheet To Net Edit &
Reading Error This Line Will Be Not Print Always In Also Check.

Print (Alt+P+G): - Print The Line Between Row & Column In The Sheet To The Make Reading
Error.
Print Heading (Alt+P+Vh): - This Option Is Used To Print Row & Column.

Selection Pane (Alt+P+AP): - Show The Selection Pane To Have Select Indivalues Object And To
The Change. There Order a Visited.

Function Library
Insert Function (Shift+F3): - Edit The Formulas In Current Cell By Choosing Function.

Recently Used (Alt+M+R): - Browser And Select From List By Recently Used Function.

Financial Function (Alt+M+I): - Browser And Select From Tax To Analyze Financial Function.

Logical Function (Alt+M+L): - Browser And Select From Tax To Analyze Logical Function.

Text (Alt+M+T): - Browser And Select From Tax To Analyze Text Function.

Date & Time (Alt+M+E): - Browser And Select From Tax To Analyze Date & Time Fuction.

Defined Name
Name Manager (Alt+M+N): - This Option Is Used To Create Edit Delete & Fined
Out The Named Used In The Work Sheet Name Can Be Used For The Cell
Refrences.

Defined Name: - Name Cell Show That You Can Refer To Then In Formulas By That
Name.

Use in the Formulas (Alt+M+S): - Choose A Name Used In the Workbook and
Insert It into the Current Formulas.

Create From Selection (Alt+M+C): - Automatically Generate Names From The

Selected Cells. Many People Choose To Use The Text In The Top Row Or The Left
Most Column Of A Selection.

Formulae Auditing
Trace Precedent (Alt+M+P): - Show Arrows That Indicated What Cell Effect The
Value Of Currently Selected Cell.
Trace Dependent (Alt+M+D): - Show Arrows That Indicated What Cell Effect The
Value Of Currently Selected Cell.

Remove Arrows (Alt+M+R): - Remove The Arrows Drawn By Trace Precedent Or

Trace Dependent.

Show Formulae (Alt+M+H): - Display Formulae in Each Cell Instead Of the Result
Value.

Error Checking (Alt+M+A): - Check The Common Error In The Formulae.

Evaluate Formulae (Alt+M+V): - Launch The Evaluate Formulae Dialog Box A

Formulae By Evaluating Each Part Of The Formulae Indivalues.

Watch Window (Alt+M+W): - The Value Are Display In A Separate Window That
Rename Visible Regardless Of What Area Of The Workbook Is Shown.

Calculation

Calculation (Alt+M+X): - Specify When Formulae Are Calculated By Default Any

Time You Can Change A Value That Effect Other Value. The New Value Are
Calculated Immediately.

I. Automatic
II. Automatic Expect
III. Manual

Calculate Row (F9, Alt+M+B) : - Calculate The Entire Workbook Now. This Is Only
Necessary If Automatic Calculating Has Been Turn Of .

Calculate Sheet (Alt+M+J): - This Option Is Used To Calculate The Current Sheet
Row. This Is Only Necessary If Automatic Calculating Has Been Turn Of.

Data Tab
 Get External Data Group
From Access (Alt+A+Fa): - Import Data from a Micro Soft Access Data Based.

From Web: - Get External Data from a Web Page.

From Text (Alt+A++Fd): - Import Data From A Text File.

From Other Sources - - Import Data From Other Data Sources.

Exiting Connection (Alt+A+X): - Connect To An External Data Source By Selecting

From A List Of Commonly Used Source.

Connection Group
Refresh (Ctrl+Alt+F): - All The Information In The Workbook Coming From A Data
Source.

Connection: - Display All The Data Connection For The Workbook.

Sort & Filter

Filter (Ctrl+Alt+L): - This Option Is To Enable Filtering Of The Selected All. Once
Filtering Is Turn On Click The Arrow In The Column Header To Choose A Filter For
The Column.

Clear: - Clear The Filter & Sort Step .For The Current Range Of Data.

Re- Apply (Ctrl+A+Q): - Re- Apply The Filter & Sort Step .For The Current Range.

Data Tools
Text To Column (Alt+A+E): - Separate The Contains Of One Excel All Into Separate
Columns.

Ex- You Can Separate A Column Of Food Names Into Separate A First & Last Main
Column.
Remove Duplicate (Alt+A+N): - Delete Duplicate Row From A Sheet. You Can
Specify Which Column Should Be Check Default Duplicate Information.

Data Validation (Alt+A+V): - Prevent Invalid Data From Being Entered In To A

Cell.

Example: - You Could Reject Invalid Data Or No. Greater Than 1000

Consolidate (Alt+A+N): - This Option Is Used To Combined Values From Multiple

Range In To One New Rang.

What If Analysis (Alt+A+W): - Try To Various Value From The Formulas In The
Sheet.

1. Scenario Manager (Alt+A+Ws) Scenario Manager Allow You Create And Save
Different Group Or Value And Switch Between Them .
2. Goal Seek:- Goal Seek Will Find The Right Input Value Know The Result That You Want .
Outline(Group)
(1) Group (Shift, Alt +Right Arrow) Tia A Range Of Cell Together So That They Can Be
Collapse And Expended .
(2) Ungroup(Alt+A+U):-Ungroup A Range Of All That Where Previous Group.
(3) Sub Total:-Total Several Row Of Related Data Together By Automatically Insert In
Sub- Total &Total For The Selected Cell.
(4) Show Details:- (Alt+A+J) :- Expend A Collapsed Group Of Cell .
(5) Hide Details (Alt+A+X):- Collapsed A Group Of Cell ..

Review Tab

Profing Group

1. Spelling & Grammar(Alt+R+S) – This Option Is Used To Check The Spelling & Grammar
Of The Selected Cell.

Comment Group

I. New Comment (Alt+R+C) This Option Is Used To Add A Coment About The
Selection.
II. Delete (Alt+R+C) : - This Option Is Used To Delete The Selected Comments.
III. Previous(Alt+R+U) : - Select The Previous Cmment In The Worksheet.
IV. Next (Alt +R+N) : - Navigate To The Next Comment To The Sheet.
V. Show/ Hide Comment (Alt+R+H) : - Show Or Hide The To The Selected Cell
VI. Show All Comment (Alt+R+A) : - This Option Is Used To Display All Comment
In The Sheet.

Changes Group

Protect Sheet (Alt+R+Aw) : - Prevent Unwanted Change To The Data In Sheet By

Specifying What Information Can Be Changed.

Eg. You Can Protect Symbol From Editing Log Cell Or Changing The Information Of
The Document. You Can Specify Password That Can Be Enterd To The Protect Or
Un-Protect Sheet & Allow This Changes.

Share Workbook(Alt+R+W) : - Allow Multiple People To Work In A Workbook At

The Same Time. The Workbook Should Be Saved To A Network Location.

Protect & Share Workbook (Alt+R+O): - Share the Workbook Protect It with a
Password at the Same Time.

Allow Uses to change Range (Alt+R+U): - Allow Specific People to Edit Range Of
The Cell In A Protect Workbook Or Sheets.

Track Changes – Track All Changes Made To The Document Including Insertion
Deletion & Formatting Changes.

View Tab

Workbook

Normal View (Alt+M+B): -This Option Is Used To View The Document In The Normal
View.

Page Layout (Alt+W+V): - This Option Is Used To View The Document As It Will
Appear On The Printed Page. We This View See Where Page Bringing & End To View
Any Header & Footer On This Page.

Page break Review (Alt+W+D): - This Option Is Used To View a Preview of Where
Pages Will Break When This Document Is Printed.

Custom View (Alt+W+C): -Save A Set Of Display A Print Setting As Custom View Once
You Save The Current View. You Can Apply It To The Document By Sleeting From The
List Of Available.

Full Screen (Alt+W+E); - This Option Is Used To View The Document In Full Screen
Mode.

Show/ Hide (View)

Ruler (Alt+W+R): - View The Rulers Used To Measure & Line Up Object In The
Document.

Gridlines (Alt+W+Vg): -Show The Gridline Between Row &Column In The Sheet
Wake Editing.

Formula Bar (Alt+W+VF): -View The Formula Bar In Which You Can Inter Text
&Formula In To The Cell.

Headings (Alt +W+Va): - Zoom This Option Is Used To Show Row & Column Heading.

Group

Zoom – Zoom Open The Zoom Dialogue Box To Specify The Zoom Level Of The
Document. In Most Cases You Came Also Use the Zoom Controls in the Status bar In
the Window to Quickly Zoom the Document.

100% - (Alt+W+J): -Zoom The Document To The 100% Of The Normal Size.

Zoom To Selection (Alt+W+G); - Zoom The Worksheet Show The Currently Selected
Range Of The Cells Fills Entire Window. This Can Help You To Focus On A Specific
Area Of The Spreadsheet.

Windows Group

New Window (Alt+W+N): - This Option Is Used To Open A New Window

Containing A View Of The Current Document.

Arrange All (Alt+W+A): - Flew All Opens Programmed Windows Side By Side On
The Screen.

Freeze Panes (Alt+W+Ff+): - Keep Row & Column Visible While The Rent Of
Worksheet Scrolls Based On Current Selection.

Freeze Row (Alt+ W+): - Keep Row & Column Visible While Scrolling Through The
Rest Of Worksheet.

Freeze First Column (Alt+W+Fc): -Keep The First Column Visible Scrolling
Through The Rest Of Worksheet.

Split (Alt +W+S): -Split the Windows In To Multiple Panes Contains View Of Your
Worksheet You Can Use This Features to View Multiple Distance Parts of Your
Worksheet At Once

Hide (Alt+ W+H):-This Option Is Used To Hide The Current Window. So That It
Cannot Be Seen. To Bring The Window That Click The Unhide Button.
Unhide Window (Alt+W+V):- This Option Is Used To Unhide Any Window Hidden
Of The Hide Window Feature.

View Side By Side (Alt +W+B): - View To Worksheet Side By Side Show That You
Can Compare Their Content.

Synchronous Scrolling (Alt+W+Vs); -Synchronize The Scrolling Of To The

Document Show That They Scroll Together. To Enable This Features N View Side By
Side Option.

Reset Window Position (Alt+W+T): - Reset The Window Position Of The

Document Being Compare. Side By Side So That They Share The Screen Equally To
Enable This Features Turn On View Side By Side Option.

Save Work Space (Alt+W+K) Save Current Layout Of All Windows Work Space So
The It Can Be Restored.

Switch Windows (Alt+W+W) This Option Is Used To Switch A Different Currently

Open Windows.

Macros Group
View Macros (Alt+W+M+V) This Option Is Used To View The List Of Micros From
Which You Can Create Or Delete A Micros.

Record Micros (Alt+W+M+R) Each Of The Command You Perform Will Be Saved
In To The Micros So That You Can Pay Them Back Again.

Function In M.S. Excel

Mathematical Function
Sum – The Sum Function Add All The Number. That You Have Specified In A Range
Of Cell Each Argument Number That Invoice Contained In A Cell.

Syntax - =Sum (A2: F2)

Sr .No A B C D E F G
1 1st 2nd 3rd 4th 5th 6th Total
2 20 25 40 75 80 50 = Sum(A2:F2)
Sum If – Adds A Cell Specified By A Given Sum If Function Is Used To Sum The Value
In The Meet Criteria That You Specify.

Eg: - Suppose That In A Column That Contains Number You Want To Sum Only The
Value That Are Large Than 60.

Syntax - = Sum If [(A2:F2) “>60”)]

Mod: - It Is A Mathematical Function Return The Reminder After No. Is Divided By

Division The Result Has The Same Sign As Division Is Mode Function No. Which You
Want To Fined The Remainder.

Syntax & Mode (No. Divisor)

A B C
1st 2nd 3rd
100 10 =Mod(A2:B2)
45 2 =Mod (A3:B3)

Product: - The Produce Function Multiple The Number Given As The Return The
Product.

Eg. If Cell No. Contains No. You Can Use Formulas Equal Product. Cell No. To
Multiple Those Two No. Together You Can Also Perform The Some Option By Using
Multiply (*).

Sum product –One Of The Most Sum Product Basic Function In Any Spread Sheet Is
To Retune A Answer Based Upon Some Condition. This Become Specially Useful
when Contain All Sum Based Upon That Seen Product Is One Of The Most Versatile
Function Provide In Excel.

A B C
1 1st 2nd Result
2 100 10 = Sum Product(A2:A3:,B2:B3)
3 45 2 1090

Round: - The Round Function Round A Number To A Specified No. Of Digit.

Egg. If A Contains 23.7825 And If You Want To Round That Value & Decimal Places.

A B C
1 Number Result
2 23.7825 =Round(A2,2) =23.78
 Round Down: -Round Down In Similar To the Round Function. This Functions
Round A No. Of Two A Specify Amount of Decimal Places.

A B
1 Number Result
2 23.785 = Round Down (A2,1) = 23.7

Round Up: - This Function Is Used To Round A Number Up The Next Highest
Number.

A B
1 Number Result
2 23.3821 =Round Up (A2,1) = 23.8

A.B.S.: - (Absolute): - Return the Absolute Value of A Number the Is the Without
Any Sign.

A B
1 -4 =Abs(A1)

Square Root: - The Square Root Function Calculate The Square Root Of Any
Multiple No.

A B
1 100 =Sqrt(A1)
2 121 =Sqrt(A2)

Logic Function: - The Function Is Used To Text To The See If A Certain Condition In
Spreadsheet Is True Or False.

A B
1 67% =If(A1>33,”Pass”,”Fail”)

And – The and Is One of the Excel Logical Function. The and Function Gives Only A
True or False Answer.

A B
StStatistical 1 78 =If[(And(A1<100),(A2<100),(A3<100)]
2 110
3 45
 Function
Count If: - The Count Function Is Used To The No Of The Cells Width In Arrange Of Cells That Meet The
Given Criteria.

A B C D E
1 100 150 50 60 105
2 =Count If (A1:E1,”>70”)

Text Function
Upper Text: - The Upper Function Is Used To Convert Text To Cell Upper Case Or
Capital Letter.

A B
1 Ram =Upper(A1)

Lower Text: - The Lower Function Is Used To Convert Text In To Lower Case.

A B
1 Ram =Lower(A1)
Concatenate – The Concatenate Function Is Used To Join Two Or Move Words.

A B C D
1 Ram Is
2 A Good
3 Boy
= Concatenate(A1,C1,B2c2b3)

Trim Function: - When Text Data Is Important Are Into An Excel Spreadsheet Extra
Space And Some Time Included A Long With The Words So That Be Used To Trim
Function To Remove Extra Unwanted Space.

A B C D
1 Ram Is A Good Boy
2 =Trim(Text)

Financial Function
P.M.T. (Payment): - P.M.T. Function Is Used To Calculate Periodic Payment Of A
Loan Based On Constant Insert Rate.

Rate: - It’s A Fixed Insert Rate for Loan Of

Nper – Nper Is The Total Number Of Payment For The Loan.

R.V. (Resent Value) R.V. Is The Present Value Of The Loan Or Actual Amount Of
Loan?

A B C
1 Rate 5%
2 Month 10 =Pmt(B1/12,B2,B3)
3 Amount 1200 1227.67

If In The Place Of Month Year Present Multiplied By 12 (B2*12)

Today Function – Today Function Written the Current Data.

Syntax: - =Today ( ) Enter Result 2/1/2014

Now - The Written the Current Data and Time

Syntax: - Now ( ) Press Enter 2/1/2014 1:30

P.M.T.: - Payment Is Every Month.

A B C D E
1 Name March April May June
2 Ram 25% 17% 99% 100%
3 Mohan 14% 25% 40% 10%
4 Shan 35% 75% 80% 85%
5 Sham 45% 65% 75% 95%
6 =V Look Up (B5:A2:E2 False) =99