MS Excel

MS Excel is a spar sheet program which run on your computer. It is a package software. It is also part of Microsoft office.
It Excel of data analyze and calculation in row and column with the help of excel we can analyze and calculation any big to big number to easily to easily. In MS excel 1048576 row and 16384 Columns.
Extension name: – .XLSX

Feature of Excel

  1. The spar sheet file is called work book which include word sheet.
  2. In work sheet the row designed with number and column designed with letter.
ABCD
1
2

3. In MS Excel has 3 Default sheet.

Some important word in excel

1. Cell:-

One row and one column is called Cell.

2. Main box:-

It is represent of your cell an address.

EXAMPLE :-

AB
1A1B1
2A2B2

3. Cell pointer: –

In Excel worksheet run pointer that is called cell pointer.

Que. How to open excel ?
Ans :- I. Click on start button — All program— MS Office— Excel
II. Press Win + R —- (type) Excel— OK

Project No 1.

ABCDEF
1Serial numberName of productQtyUnitRateAmount
21Ghari100Piece10=C2*E2
32Nirma100Piece10=C3*E3
43Sugar100KG40=C4*E4
54Rice100KG50=C5*E5
65Dove100Piece20=C6*E6
76Lux100Piece35=C7*E7
87Dettol100Piece50=C8*E8
98Face wash100Piece60=C9*E9
109Fair Lovely100Piece50=C10*E10
1110Pen100Piece20=C11*E11
12Total Amount=sum(F2.F8)

Use of Home Tab

1. Clipboard

a. Paste (Ctrl + v):-

This option is used paste of cut and copy selected object.

b. Cut (Ctrl + X):-

This option is used to cut of selected text.

c. Copy (Ctrl + C):-

This option is used to Copy of selected text.

d. Format painter:

This option is used to copy formation from one place and apply it to another.

2. Font

a. Font:

This option is used to change the font face.

b. Font size: –

This option is used to change the font size.

c. Increase the font size: –

This option is used to increase the font size.

d. Decrease the font size:-

This option is used to decrease the font size.

e. Bold (Ctrl + B):-

This option is used to Bold of selected text.

f. Italic (Ctrl + I):-

This option is used to italic of selected text.

g. Underline (ctrl+u):-

This option is used to underline of selected text.

h. Border:-

This option is used to erase and fill color in your spear sheet.

i. Fill color:-

This option is used to color the background of selected text.

j. Font color:-

This option is used to change the font color.

3. Alignment

a. Top Align:-

This option is used to set to align text to the top of the cell.

b. Middle align:-

This option is used to align text to the middle of the cell.

c. Bottom align:-

This option is used to align text to the bottom of the cell.

d. Orientation:

This option is used to change text angle in many direction in cell.

e. Wrap text:

This option is used to make all contact visible within a cell by displaying it on multiple line.

f. Marge and center:-

This option is used to join the selected cell into one large cell and write text in center.

g. Align text left:

This option is used to text left.

h. Align text Center:

This option is used to text center.

i. Align text Right:

This option is used to text right.

j. Decrease indent (Ctrl + Alt + Shift + Tab):-

This option is used to decrease the margin between the border and the text in the cell.

4. Number Format

a. Number Format:-

This option is used to choose how to value in cell such percentage Currency etc.

b. Accounting number format:-

This option is used to choose an alternate currency for the selected text.

c. Percentage style (Ctrl + Shift + %):-

This option is used to display the value at the cell as percentage.

d. Coma style:-

This option is used to of the cell with thousand separate.

e. Decrease decimal: –

This option is used to Decrease the decimal of selected cell.

f. Increase Decimal: –

This option is used to increase the decimal of selected cell.

5. Style

a. Conditional Formation: –

This option is used to highlight with interesting cell such as color scale and icon.

b. Format and table:-

This option is used to set quickly format arrange of cell.

c. Cell style: –

This option is used to change style of selected cell.

6. Cell

a. Insert: –

This option is used to insert cell row sheet, column sheet and sheet.

b. Delete: –

This option is used to delete selected cell row, column and sheet.

c. Format: –

This option is used to change the row height or column, width organize sheet or protect or hide cells.

Project No -2

ABCDEFGHI
1Serial NumberName of ProductQtyRateAmountSGSTCGSTDiscountNet Amount
21Fair lovely10050=C2*D2=E2*9/100=E2*9/100=E2*10/100=E2+F2+G2-H2
32Dove12060
43Nirma20075
54Dettol30040
65Life boy15012
76Lux10020
87Pears20080
98Toffy10095
109Biscuit12055
Formula:-

Amount:- =Qty * Rate
SGST:- =Amount * 9/100
CGST:- =Amount * 9/100
Discount:- =Amount*10/100
Net amount:- = Amount + SGST + CGST – Discount

7. Editing

a. £ Autosome (alt ++):-

This option is used to calculate of selected range.

b. Fill: –

This option is used to you can fill in any direction and any range.

c. Clear: –

This option is used to delete everything from the cell or remove the formation contact comments.

d. Short and filter: –

This option is used you can sort and filter any data.

e. Find and filter: –

This option is used to find word and go to formulas content conditional formatting.

Use of Insert option

1. Tables

a. Pivot table: –

This option is used to insert summarize data using to pivot data using to pivot table.

b. Pivot chart: –

This option is used to insert many types of chart of you’re create table data such as column line bar etc.

c. Table: –

This option is used to create table to manage analyze related data.

2. ILLUSTRATIONS

a. Picture: –

This option is used to insert picture from a file.

b. Online picture:

This option is used to insert picture from online.

c. Shape:

This option is used to insert many type of shape.

d. Smart art:

This option is used to insert smart art graphics of your sheets.

e. Screenshot: –

This option is used to take a screen shot of your sheets.

f. Chart:-

This option is used to insert any type of chart In your sheets.

g. Recommended Charts –

This option is used to show suggested charts for selected data.

h. Pivot chart:-

This option is used to Graphically summaries data and explore Complicated data.

3. Sparkline Group

a. Line :-

This option is used to insert mini line charts.

b. Column :-

This option is used to insert mini column charts.

c. Win/Loss :-

This option is used to show positive/negative values in small boxes.

4. Filter

a. Slicer: –

This option is used to a slicer to filter data visually.

b. Time Line: –

This option is used to a time line to filter interactively.

5. Links

Hyper Links:

This option is used to create a link in your document for quick access to webpage and file.

6. Text

a. Text Box :-

This option is used to insert a text box.

b. Header & Footer :-

This option is used to add header or footer in the worksheet.

c. WordArt :-

This option is used to insert decorative text.

d. Signature Line :-

This option is used to add a signature line.

e. Object :-

This option is used to insert external objects (like Word files, PDFs, etc.)

7 Equation

a. Equation

This option is used to insert mathematical equations.

b. Symbol

This option is used to insert special characters or symbols.

Use of Page Layout

Themes

1. Themes:

This option is used to set of themes in your document.

2. Colors: –

This option is used to color for the current them.

3. Fonts: –

This option is used to change the fonts for the current themes.

4. Effects: –

This option is used to change the effect for the current themes.

Page setup

1. Margins: –

This option is used to select the margin size for the entire documents or the current selection.

2. Orientation: –

This option is used to switch the page between Portrait and landscape.

3. Page size: –

This option is used to choose paper size for the current selection.

4. Print area: –

This option is used to mark a specific area the sheet for printing.

5. Breaks: –

This option is used to specific where a new page will begin in printed copy.

6. Background: –

This option is used to choose and image to display as the background of the sheet.

7. Print Titles: –

This option is used to specific row and column report on each printed page.

Function of MS Excel

There are six types function in MS excel.

  1. Math metical Function
  2. Logical function
  3. Financial Function
  4. Statistical Function
  5. Date and time Function
  6. Text Function

1. Mathmetical Function: –

a. Sum: –

Add the all number in a range of cell.

Example:-

A
110
220
330
440
550
660
770
8=sum(A1……A8)

b. Mod: –

Return the reminder after a number divide b division.

Syntax: – Mod(Number.Division)
Example: – =Mod (40,5)

c. Pi: –

Return the value of Pi.

Ex: – =Pi ()

d. SQRT (Squire root): –

return the squire root of a number.

Syntax: – =SQRT (Number)
Example: – =SQRT (144)

e. Power: –

Return the power of a number

Syntax: – =Power (Number,Power)
Example: – =Power(5,2)

f. Products: –

Return the multiplies

All number give as cess
Syntax:- =Product (10,3)
30

2. Statistical function

a. Average: –

Get the value from selected cell range.

Syntax: – =Average (A1…A5)
Ex.

A
110
25
36
47
58
6=Average (A1..A5)

b. Count: –

Count the number of cell in a range that contain number.

Syntax: – Count (A1..A5)
Ex:

A
110
220
330
440
550
6=Count (A1..A5)
5

c. Count blank: –

Count blank number of cell in a range that contain number.

Syntax: – =Count (A1..A5)
Ex.

A
110
2
320
4
5=Count (A1..A5)
2

d. Minimum: –

For get minimum value from selected cell range.

Syntax; – =Min(A1…A5)
Ex.

A
110
220
3100
450
5150
6=Min(A1…A5)
10

e. Count if: –

Count if the number with in cell that meet the given condition.

Syntax: – =Count if (A1…D4,20)
Ex.

ABCD
110301525
220201020
330402510
440304040
5=Count if (A1…D4,20)
3

f. Maximum: –

For the get maximum value from selected cell range

Syntax: – =max (A1…A5)
Ex.

A
110
220
3100
450
5150
6=Max(A1..A5)
150

Project No – 4

ABCDEFGHIJKL
1Serial numberName of balanceBalanceDepositWithdrawMinimum BalanceMaximum BalanceMinimum DepositMaximum DepositMinimum WithdrawMaximum WithdrawTotal Balance
21Khushi1000050003000
32Neeraj1200060005000
43Suraj1500040008000
54Suman1600080006000
65Rohan1000060007000
76Roshan2000070006000
87Ram2500080005000
98Shyam3000060004000

Formula :-

Minimum Balance :- =Min(C2…….C9)
Maximum Balance :- =Max(C2……C9)
Minimum Deposit :- =Min(D2……D9)
Maximum Deposit :- =Max(D2……D9)
Minimum Withdraw :- =Min(E2…..E9)
Maximum Withdraw :- =Max(E2…..E9)
Total Balance :- =C2 + D2 – E2

3. Text function

a. Upper:-

Convert to upper case capital letter.
Ex:- =upper (“Rohit”)

b. Lower: –

convert text to lower case small letter.
Ex: – =Lower (“Rohit”)

c. Concatenate: –

Join separate text in to one text item.
Ex:- =Concatenate (Software)

d. Exact:-

Check to see if to text value are addition.
Ex. =Exact (color, column) True/false

e. Left: –

Return the most character from a text.
Ex. =Left (“Ramcharan”,3)
= Ram

f. Right: –

Return the most character from right side of your text.
Ex. =Right (“Ramcharan”,3)
= ran

g. Len: –

Return the number of character a text value.
Ex. =Len (“Ramcharan”)
= 9

h. Proper: –

Capitalize the first letter in each word of text value.
Ex. =Proper (“…This is my project”)

i. Trim:

Remove space from the text value.
Ex. =Trim(“ Hi my name is krishna”)

4. Logical Function

  1. If write less then, then write <=
    Example: 10<12 False
  2. If write greater Then, Then write > =
    Example: 10>12 True
  3. If Write not equal, then write < >
    Example: < > 10,10 =True
  4. Logic value: True/False

Project=6

ABCDEF
1Serial numberNameSalaryWhereAgeSenior/Junior
21Krishna82150HFD27=IF(E2>50,”senior”,”Junior”)
32Neeraj24550CDFD58
43Aman15100HFD31
54Dheeraj72700CDFD50
65Ragini38050ED43
76Maya35350RAD32
87Neha55100RAD48
98Khushi205750RAD26
109Sadhana30350HFD29
Formula :-

Senior/Junior =IF(E2>50,”senior”,”Junior”)

5. Financial Function

I. PN= Present Value
II. NPER= Number of Period
III. ROI= Rate of Interest
IV. PMT= Per month Transection
V. EMI= Every Month Installation
VI. PPMT= Principal of per month transection
VII. IPMT= Interest of per month transection
VIII. PMT/EMI= It is used to see how many pay money is every month.
IX. PPMT= It is used to see how many pay principal many in every month.
X. IPMT= It is used to see how many pay interest money in every month.

Project=7

ABCDEFGHI
1Loan amountNPERROIPMTTotal AmtInterest AmtMonthPPMTIPMT
21000001 Year10%1
32
43
54
65
76
87
98
109
1110
1211
1312
14Total Amount

Formula :-

PMT =(Rate/12,NPER*12,-Loan Amt)
=8791.59
Total Amt =(PMT*12)
=105499.06
Interest Amt =Total Amt-100000)
=5499.06
PPMT =PPMT/Rate/12,month,NPER*12,-Loan Amount
IPMT =IPMT(Rate/12,Month,NPER*12,-Loan Amount

DATE AND TIME FUNCTION

1. TODAY: –

Today formula is used to know today date.
Example: =Today(),
Short cut :- Ctrl+;

2. Time:-

Time formula is used to know currant time.
Example: =time ()
Shortcut :- Ctrl+Shift+;

3. Now:-

Now formula is used to know date and time show in one cell.
Example:- =Now ()

4. Weekday:-

If you can know week day name than you can use this formula.
Example:- =Text(Today date, “DDDD”)

5. Day:-

Day formula is used to know day.
Example:- =Day(today date)

6. Month:-

If you can display month name than you can you this formula:
Example:- =Text (Today date, “MMMM”)

7. Year:-

This formula is used to know current tear.
Example:- =Year (today date)

8. Last day of month:-

If you can know last day of month then you can use this formula.
Example:- =Eomonth (today date,0) + 0

9. First day of Next month:-

If you can know first day of next month then you can use this formula.
Example:- =Eomonth(today date,0)+1

How to know our age?

ABCDE
1Date of BirthTodayYearMonthDay
212/05/200713/10/2026

Formula :-

Year:- =Dated if (DOB, Today, “y”)
Month:- =Dated if (DOB, Today, “YM”)
Day:- =Dated if (DOB, Today, “MD”)

Use of Formula Tab

1. Define Name:-

a. Name manager (ctrl +f2):-

This option is used to create name, edit delete and find all the name used in workbook.

b. Defined Name:-

This option is used to define name that you can refer to them in formula by name.

c. Use in formula:-

This option is used to choose a name used in this workbook and insert in the formula.

d. Create from selection (Ctrl + Shift +f3):-

This option is used to automatically generate from selected cell.

2. Formula auditing:-

a. Trace precedents:-

This option is used to show arrows that indicate what cell effect value of the currently selected cell.

b. Trace dependent:-

This option is used to show arrows that indicated what cell effect value of the currently selected cell.

c. Remove all arrows:-

This option is used to remove arrow drown by precedent or dependent.

d. Show formula (ctrl ++):-

This option is used to display formula in each cell.

e. Error Checking:-

This option is used to check for common error that accur in formula.

f. Evaluate:-

This option is used launch that evaluate formula dialog box.

g. Watch window:-

This option is used to monitor the value of certain cell and charge are made to the sheet.

3. Calculations:-

a. Calculation option:-

This option is used to calculate the entire worksheet.

b. Calculate (F9):-

This option is used calculate the entire worksheet.

c. Calculate sheet(Shift + F9):-

This option is used to calculate the only currently sheet.

Use of Data Tab

1. Get external data

a. From access:-

This option is used to get the data from access in our workbook.

b. From web:-

This option is used to get the data from web in our workbook.

c. From text:-

This option is used to get the data from text in our workbook.

d. From other sources:-

This option is used to get the data from other sources such as SQI Server analyze etc.

e. Existing Connection:-

This option is used to get external data using an existing data.

Scroll to Top