MS EXCEL DCA NOTES PART 4

MICROSOFT EXCEL 2007

EXTENSION-.xlsx (2007/2010/2013)

Window’s + R - Excel (enter)

Start - All Programme – Microsoft office 2007

Microsoft excel 2007 - OK

No. of column – 16,384 (2007/10/13)

No. of rows - 10,48,576(2007/10/13)

No. of row - 65,536 (2003)

No. of column - 256 (2003)

Heading

COLUMN

TYPES OF COLUMN

Doctor’s big Symbol

(For Select Only)

Doctor’s thin Symbol (for drag)

Row Separator

Column Separator

Four headed Arrow (for moving

The cell ).

Methods of editing the cells

· Press f2

· Double click on cell

· Click on formula

RESULTS :-

NAME

HIN

ENG

MATH

SST

SCI

TOTAL

MIN

MAX

AVG

F.M

RES

DIV

Formula:-

Total G2 fx = Sum (B2:F2)

Min H2 fx = Min (B2:F2)

Max I2 fx = Max (B2 : F2)

Average J2 fx = Average (B2 : F2)

Results k2 fx = If (G2>300,”PASS”,”FAIL”)

Grade l2 fx = If (J2>=60,”A”,IF ( J2>=42,”B”,”C”))

DIVISION fx = If (B2>=60%,”first”,IF(B2>=45%,”second”,IF(B2>=33%,”Third”,”Nil”))) Press Enter

RESULT

= If (AND(B8>=33,(8>=33,D8>=33,E8>=33,F8>=33),”PASS”,”FAIL”

How to create Marksheet.

1. Name , five subjects, total ,max , min , Avg, Full marks, Per % ,Division , Grade Result.

2. % - [ = Select total / full marks (enter) ]

3. Division – [ = if (N6>=60%, “FIRST” , If (n6>=45%, “SECOND”, If (N6>=33%, “THIRD”,”FAIL”,,,] Here for N6 Select % Column.

4. Grade – [ = If (N6>=80%, “A+”, If (N6>=60%, “A”. If (N6>=45%, “B”,If (N6>=33%, “C”, “D”))))] Here for N6 Select % Column.

5. Results – [ = If (AND (D6>=33, E6>=33, F6>=33, G6>=33, H6>=33), “PASS” , “FAIL”) Here for D6, E6,F6,G6,H6, Select each Subject marks Column.

NAME

TND

TNBD

TNPD

TNAD

MAHI

SHAAN

RAM

ATTENDANCE SHEET

Step ;

Size 3

Select B to AF Format column width

A B

Name 1 2 3

Write attendance sheet Select same column किसी भी date में sunday लिखने के लिए next

Select any date Marge and center write Sunday decrease or increase indent. then fill in blank (P) or (A)

3 OK

budget

INCOME
		JAN	FEB	MAR	----------	----------	YEAR
SALARY		8	2	1	--	--	11
DIVIDEND		8	2	1	--	--	11

TOTAL		16	4	2	--	--	22

EXPENSES

RENT		2	2	2	--	--	4
HEALTH		4	1	--	--	--	5
FOOD		1	1	--	--	--	2
CAR		2	1	--	--	--	3
MOBILE		1	3	--	--	--	4
ADDITIONAL		1	4	--	--	--	5

TOTAL		11	12	--	--	--	23
SULPLUS/DEFICIT
	5		8	--	--	--	1

Year = [=SUM (Jan:Dec)]

Total = [SUM (B8:B10)]

Surplus / Deficit = [=B11-B22] (Surplus/ deficit = income- expense)

HLOOKUP

S.NO.	NAME	JAN	FEB	MAR	------	------	DEC
1	Mamta	10000	10000	-----	-----	-----	10000
2	Sapna	15000	15000	------	-----	-----	15000
3	Arti	12000	12000	-----	------	-----	12000
4	--	--	--	--	--	--	--
5	--	--	--	--	--	--	--
6	--	--	--	--	--	--	--

	JUNE
ARTI	12000

FORMULA = [=HLOOKUP(B24,B9:N19,3)]

Month Number of column

All name of column

LOOKUP

ROLL NO.	NAME	ENG	MATHS	SCI	SST	HINDI	TOTAL
1	Ram	50	60	70	60	80	320
2	Shyam	--	--	--	--	--	--
3	Sohan	--	--	--	--	--	--
4	Rohan	--	--	--	--	-	--

ROLL NO.	1
NAME	RAM
ENG	50
MATHS	60
SCI	70
SST	60
HINDI	80

FORMULAS = For name column:

[=LOOKUP(L38,A8:A32,B8:B32)]

Roll no. cell Name column

Roll no. column

= For subject column (like eng.):

[=LOOKUP(L38,A8:A32,C8:C32)]

Roll no. cell Subject column (ENG)

Roll no. column

VLOOKUP

ROLL NO.	1
NAME	RAM
ENG
MATHS

FORMULAS = For name column:

[=VLOOKUP(C38,A7:H32,2,FALSE)]

Roll no. cell Column number

Select whole table

CRICKET SCORE CARD

Name	One	Two	Three	Four	Six	Dot ball	Total Run	Total

Dhoni	6	4	3	4	6	1	23	24
Kholi	2	3	2	5	4	2
Rohit	1	4	1	7	8	1

Total run – ( h10x6 + i10x4 +j10x3 + k10x4 + l10x6)

Total ball – ( = sum(h10:m10)) [therefore (sum(one + dot ball)

Loan amortization

NAME	DATE	MON.	PRI.	RATE	YR.	INT.	EXTRA	TOTAL	ONLY INT.	ONLY PRI.	END BAL.
Karan	01.01.19	Jan	500000	10%	5	-1062.352	100	-962.352	-416.667	-645.686	51062 .3522
Ram	01.02.19	Feb	51062 .35	10%	5	-1084.924	200	-884.924	- 425.52	-659.404	49977 .43
Roy	--	--	--	--	--	--	--	--	--	--	--
Somya	--	--	--	--	--	--	--	--	--	--	--
Radha
Rahul
Roni

Steps :-

1. Name, month, principle, rate, year, emi, extra total balance, only int. , only principle , ending balance.

2. Emi - pmt (e8/12, 60 , d8 , 0)

RATE MONTH PRINCIPLE

3. Extra - Assuming value ( goes to a)

4. Total Balance - G8 – H8 ( EMI + EXTRA)

5. Only interest – IPMT ( E8/12, 1, 60, D8, 0 )

Rate month future value

6. Only principle - PPMT ( E8 / 12, 1, 60, D8, 0)

7. Ending Balance - D8 + G8

Principle + EMI

SALARY REPORT CARD

No.of employee

salary

HRA

ADD

Total

Bonus

post

Kashish

Simran

Muskan

Swati

40000

50000

60000

70000

Formulas of Salary Report Card [12 columns]

In DA , HRA , TA , MA take any 1 to 20 no. as a percentage and in PF/Tax Take 5% and in Bonus Take 25%.

1. DA (Dearness allowance) = B6

2. HRA (Home Rent allowance) = B6

3. TA (Travelling Allowances) = B6

4. Total = (=Sum (B6:E6))

5. Bonus = (= B6)

6. PF (Provident Fund) = (=F6

7. G Total (Grand total) = (= F6 +G6-H6)

8. Post = (= If(B6>=50000, “MANAGER” , If (B6>=40000 , “ACCOUNTANT” , If (B6>=30000, “CLERK” , “PEONS”)))

= If (logical test , [value if true ] , [value if false].

9. Additional = (= B6

10.MA = (= B6

AGE WISE DEPARTMENT WISE

Name

Age

Salary

Dept.

Age wise

Dept. wise

10.

Anu

Abhay

Ankit

Rahul

Jyoti

Sana

50000

40000

25000

30000

45000

25000

Accountant

Manager

Clerk

Peon

Finance

Sale

Total salary of Accountant

= SUM IF (D2:D20 , “Accountant” , C2:C20)

(range , criteria , [sum – range])

Total salary of finance

=SUM IF (D2:D20 , “Finance” , C2:C20)

Total salary of sale

=SUM IF (D2:D20 , “SALE” , C2:C20)

Age wise salary

=SUM IF (B2:B20 , “>40” , C2:C20) Or

=SUM IF (B2:B20 , “>45” , C2:C20

PIVOT TABLE

Name of items

Purchase

Rate

Total

Discount

Months

Dove

Ponds

Oly

Deo

Surfexcel

Dove

Ponds

200

150

100

200

350

300

220

paste

100

165

9000

9750

10000

33000

28000

13500

14300

450

4875

500

1650

1400

675

715

Jan

Feb

Dec

Total - = B2*C2

Discount - = D2 *5%

How to make Pivot Table:

· Select complete data – Insert – Pivot table – New worksheet or Existing worksheet – OK – Pivot table field list (Tick on all the buttons - Ex. Name of item , Purchase

Rate Total Discount Months

· If we want to hide the table then click on which will convert into button.

PIVOT TABLE

· Option : (Active field)

· Pivotfield Name – Edit the name of the active field.

· Field settings – Launch the field setting dialog box.

· Expand entire field – Expand all items of the active field.

· Collapse entire field – Collapse all items of the active field.

· Group selection – Create a group containing the selected items.

· Ungroup (shift +alt +left) – Ungroup a range of cells that were previously grouped.

· Group Field – Group numeric or date fields.

(SHORT)

· Sort smallest to largest – Sort the selection so that the lowest values are at the top of the column.

· Sort largest to smallest – Sort the selection so that the highest values are at the top of the column.

· Sort – Launch the sort dialog box to sort data based on several criteria at once.

(DATA)

· Refresh (alt+F5) – Update the information in the workbook that is coming from a data source.

· Change data source – Change the source data for this pivot table.

(ACTIONS)

· Clear –Remove fields , formatting and filters.

· Select – Select an element of the pivot table.

· Move pivot table – Move the pivot table to another location in the workbook.

(TOOLS)

· Pivot chart – Insert a pivot chart based on the data in this pivot table.

· Formulas – Create and modify calculated fields and items.

· OLAP tools – Work with a pivot table connected to OLAP data sources.

(SHOW/HIDE)

· Field list – Show or hide the field list.The field list allows you to add and remove fields from your pivot table report.

· Buttons – Show or hide +/- buttons. These buttons allow you to expand or collapse items within the pivot table.

· Field Headers – Display the field headers for rows and column.

DESIGN :

(LAYOUT)

· Subtotals – Show or hide subtotals.

· Grand totals – Show or hide grand totals.

· Report layout – Adjust the report layout.The compact from optimizes for readability , while the tabular and outline forms include field headers.

· Blank Row – Emphasize groups by adding a blank line between each grouped item.

(PIVOT TABLE STYLE OPTIONS)

· Row header = display special formatting for the first row of the table .

· Column header = display special formatting for the first column of the table.

· Banded rows = display banded rows in which even rows are formatted differently from odd rows .

This banding can make table easier to read.

(Pivot Table Style)

CHART

First create a data .Then create any chart .Eg:- Column chart , line chart , pie chart , bar chart etc.

For example you create Result Date using Chart Functions

Name

Eco.

Eng.

Maths

Accounts

Bst

Total

Rupa

Jyoti

Manu

Maya

Umang

Tannu

233

241

Then display in chart.

COLUMN:-

Insert a column chart. Column chart are used to compare values across categories.

Design

Change chart type = Change to a different type of chart.

Save as Tamplets= Save the formatting and layout of this chart as a template you can apply to future charts.

Data

Switch Row/Column = Swap the data over the axis.Data being charted on the x axis will move to the y axis and vice versa.

Select Data = Change the data range included in the chart.

Chart Layouts

Chart Styles

LOCATION

Move Chart = Move this chart to another sheet or tab in the workbook.

LAYOUT

Current Selection

Format selection = Launch the format dialog box to fine –tune formatting for the selected chart element.

Reset to match style = Clear the custom formatting of the selected chart element back to the overall visual style applied to the chart.This ensures that selected chart element matches the overall theme of the documents.

INSERT

Picture = Insert a picture from a file.

Shapes = Insert ready-made shapes , such as rectangles and circles , arrows , lines ,flowchart symbols and callouts.

Text Box = Insert a text box that can be positioned anywhere on the page.

TABLES

Chart Title = Add ,remove or position the chart title.

Axis Title = Add, remove or position the text used to label each axis.

Legend = Add , remove or position the chart legends.

Data Labels = Add , remove or position data labels.

Use data labels the elements of the chart with their actual data values.

Data Tables = Add a data table to the chart.

AXES

Axes = Change the formatting and layout of each axis.

Gridlines = Turn gridlines on or off.

BACKGROUND

Plot Area = Turn the plot area on or off .

Chart Wall = Format the chart wall.

Chart Floor = Format the chart floor.

3-D Rotation = Change the 3-D Viewpoint of the chart.

ANALYSIS

Trendline = Add a trendline to the chart.

Lines = Add other line to the chart such as drop lines or high –low lines.

Up/Down Bars = Add up/down bars to the chart.

Error Bars = Add error bars to the chart.

Properties – Click – Chart name – give this chart a friendly name to help you refer to it when ordering objects on the sheet or writing VBA code.

FORMAT

Current Selection

Chart elements = Select a chart element so that you can format it.

Format Selection = Launch the format dialog box to fine-tune formatting for the selected chart element.

Reset to Match Style = Clear the custom formatting of the selected style applied to the chart. This ensures that selected chart element matches the overall theme of the document.

SHAPE STYLE

Shape fill = Fill the selected shape with a solid color , gradient , picture or texture.

Shape outline = Specify the color width and line style for the outline of the selected shape.

Shape Effects = Apply a visual effects to the selected shape such as shadow, glow, reflection or 3-D rotation.

WORDART STYLE

Text Fill = Fill the text with a solid color , gradient , picture or texture.

Text Outline = Specify the color , width and line style for the outline of the text.

Text Effect = Apply a visual effects to the text such as shadow , glow ,reflection or 3-D rotation.

ARRANGE

Bring to font = Bring the selected object in front of all other objects so that no part of it is hidden behind another objects.

Send to Back = Send the selected objects behind all other objects.

Selected pane = Show the selection pane to help select individual objects and to change their order and visibility.

Align = Align the edges of multiple selected objects.You can also center them or distribute them evenly across the page.

Group = Group objects together so that they can be treated like a single object.

Rotate = Rotate or flip the selected objects.

CHART

1.Column

2.Line = Insert a line chart.Line chart are used to display trends over time.

· Design

· Layout

· Format

3.Pie = Insert a pie chart.Pie charts display the contribution of each value to a total.Use it when values can be added together or when you have only one data series and all values are positive.

· Design

· Layout

· Format

4.Bar = Insert a bar chart.Bar charts are the best chart type for comparing multiple values.

· Design

· Layout

· Format

5.Area = Insert an area chart.Area charts emphasize differences between several sets of data over a period of time.

· Design

· Layout

· Format

6.Scatter = Insert a scatter chart also known as an XY charts.This type of chart compares pair of values.use it when the values being charted are not in X axis order or when they represent separate measurements.

· Design

· Layout

· Format

7.Other Charts = Insert a stock , surface , doughnut , bubbles ort rader chart.

· Design

· Layout

· Format

· HYPERLINK – (ctrl+K) - Create a link to a web page, a picture, an e-mail

Address, or a programe

STEP-:

· If we want to go other pages.

Double click

Insert Hyperlink – Place in this document cell refrence sheet 1,2.

Etc. ok.

· If we want to shutdown the computer through Hyperlink

Win +R Notepad Ok SHUTDOWN-S-T 600 file

Save as My document file ARIT. BAT save

shut

Like other side clicik go back click here

Hyperlink Insert Hyperlink Find name (ARTI) OK.

Then system shut down.

. If we want to go any folder

Insert Hyperlink Existing file or web page Select any folder

Text to display OK .

TEXT BOX Insert a text box that can be positioned any where on the page.

HEADER & FOOTER Edit the Header & footer of the document .

This information in the HEADER or FOOTER will appear at the top or bottom of each printed page .

Word Art Insert decorative text in your document .

Signature Line Insert a signature line that specifics the individual who

Must sign . Inserting a digital signature require that you obtain a digital ID , such as one from a certified MICROSOFT PARTNER .

OBJECT Insert an embedded object .

SYMBOL Insert characters that are not on your keyboard , such as copy right symbols , trade marks symbols , paragraph marks , and Unicode characters .

PAGE LAYOUT

Themes Same as above .

Page setup Same as above .

SCALE TO FIT

Width Shrink the width of printed output to fit a maximum number of pages .

Height Shrink the height of printed out put to fit a maximum number of pages .

Scale Stretch or shrink the printed output to a percentage of its actual size . The maximum width and height must be set to “ Automatic” to use this feature .

SHEET OPTIONS

View Gridlines Show the lines between rows and columns in the sheet to make editing and reading easier . These lines will not print unless print is checked .

Print Gridlines Print the line between rows and columns in the sheet to make reading easier .

View Headings show row and column headings . Row heading are the row number to the side of the sheet . Column heading are the letters or numbers that appears above the columns on a sheet .

Print Heading Print row and column heading . Row heading are the row numbers to the side of the sheet .

Columns heading are the latter or numbers that appear above the columns on a sheet .

ARRANGE

First of all select picture or shape .

Selection Pane Show the selection pane to help select individual objects and to change their order and visibility .

Align Align the edgers of multiple selected objects .

You can also center them or distribute them evenly across the page .

HYPERLINK

. Win + E – My Computer .

. If we want to go directly any folder

Step : HYPERLINK ( “D : /” , “ Arti” )

= Hyperlink ( link – location. { friendly – name }

Formulas

Jan If Define the name

Feb Click : Define name

Mar New name

Apr Name: MONTHS OK

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

10 show formula evaluate formula

30 550 press: trace precedents

100

Formula= C1+C2+C3+C4+C5+C6+C7+C8+C9+C10

= sum( C1 : C10)

G H I

10 2 20 drag - H2*G2

4 40

6 60

8 80

10 100

12 120

14 140 press: trace dependents

16 160

18 180

20 200

Formula box= H2* $G$2 -for drag

Definition

Define NAMES

Define name- Name cell so that you can refers to them in formulas by that name.

For example:- you might name the cell A20 to A40 “expenses”.

Name can be use in formulas to make them easier to understand.

Use in formulas :- choose a name used in this workbook and insert it into the

Current formula .

Create from selection: - (Ctrl+shift+F3) - Automatically generate name from the

Selected cell.

Many people choose to use the text in the top row or the leftmost column of a selection.

FORMULAS AUDITING

Trace Precedent :- show arrows that indicate what cell affect the value of the currently selected cell.

Trace Dependent :- show arrows that indicate what cell are affected by the value of

The currently selected cell.

Remove Arrows :- Remove the arrows drawn by trace precedent or trace

Dependents.

Show Formulas :- (Ctrl’) display the formula in each cell instead of the resulting

Value.

Error Checking :- check for common error that occur in formulas.

Evaluate Formula: - launch the Evaluate Formula dialog box to debug a formula by

Evaluating each part of the formula individually.

Watch Window : - Monitors the values of certain cell as change are made to the

Sheet.

The value are displayed in a separate window that remains visible , regardless of

What area of the workbook is shown.

Microsoft Excel ( Data)

Name Principle rate year EMI

Anjali 5,00000 12.50% 4 ($13,290.00)

If rate & year remain same but principle value & name are different then .

Select principle amount 500,000

Date Date tools What-if analysis

Scenario manager Add....

Scenario name :Manu Enter

Scenario values

1 : $ B $ 2 200000

Add....

Scenario name: Jyoti

Scenario values1 : 4,00,000

Add....

Same as above .

Then

Click in summary.....

Scenario Summary

Current values Manu Jyoti

Changing cells :

$ B $ 2 5,00,000 2,00,000 4,00,000

Result cells :

$E $ 2 ( $ 13.290.00) ($5,316.00) ($ 10,632.00)

Notes : current values columns represents values of changing cells at time Scenario Summary Report was created . Changing cells for each scenario are highlighted in grey .

A B C

MONTH GROWTH RATE

1. JAN 2000 5%

2. FEB 2,100 = B2 + B2 * C2

3. MAR 2205

4. APR 2315.25

5. MAY 2431.013

6. JUN 2552.563

7. JUL

8. AUG

9. SEP

10. OCT

11. NOV

12. DEC

Growth - feb =B2+B2*$C$2

Select any month of Amount

Date What if analysis Goal seek....

Set cell : B7

To value : 300

By changing cell :(select rate )

Goal seek – By change....

(select Rate) $ C $ 2

By changing cell : $ C $ 2

OK OK

DATE TOOLS

What if Analysis – Try out various values for the formulas in the sheet

Scenario manager allows you to creat and save different groups of values ,or scenarios ,

And switch between them.

Goal seek will find the right input when you know the result you want .

Date table allow you to see the results of many different possible inputs at the same time.

OUTLINE

Grope – (Shift + All + Right ) – tie range of cells to gether so that they can be collapsed or expanded .

Ungroup - (Shift + Alt + Left ) – Ungroup a range of cells that were previously grouped .

Subtotal – total several rows of related date together by automatically inserting selected cells.

“

Subtotal Ok

Subtotal

At each change in :

(1) 10 ( )

Use function

Sum

Add subtotal to :

(1) 10

(2) 10

(3) 10

(4) 10

(5) 10

Replace current subtotals

Page break between groups

Summary below data.

REMOVE ALL OK CANCEL

REVIEW

PROOFING

Spelling - (F7) - check the spelling of text .

Research - ( Alt + click ) Open the RESEARCH TASK pane to search though reference materials , such as dictionaries , encyclopedias , and translation services.

Thesaurus - Suggests other words with a similar meaning to the word you have selected .

Translate - translate the selected text into ac different language .

COMMENTS

New comment - ( Shift + F2) - Add a comment about the selection .

Delete comment - Delete the selected comment .

Previous - Select the previous comment in the sheet .

Next - Navigate to the next comment in the document .

Show / Hide comment – Show or hide the comment attached to the selected cell .

Show All comments - Display all comments in the sheet .

Show Ink - Show or hide any ink annotations on the sheet .

CHANGES

Protect sheet – Prevent unwanted changes to the data in a sheet by specifying what information can be changed .

For example : you can present people from editing locked cells or changing the formatting of the document .

Protect structure windows

Protect work book -Restrict access to the work book by preventing new sheets form being

Created or by granting access only to specific people

Shore workbook – Allow multiply people to work in a workbook at the same time .

The workbook should be saved to a network location where multiply people can open it .

Workbook containing tables cannot be shared .

Protect and share workbook –share the workbook and protect it whit a password at the same time .

The password will Prevent others from turning off change tracking .

Allow users to edit ranges – Allow specific people to edit ranges of calls in a protected workbook or sheet .

Before using this feature first set security on the sheet by using the protect sheet command .

To use this feature , your computer must be joined to a Microsoft windows domain.

Tract changes –Tract all changes made to the document including insertions .

Deletions and formatting changes .

VIEW

WORK BOOK VIEWS

Normal –view the document in normal view .

Page layout – view the document as it will appear on the printed page .

Use this view to see where pages begin and end and to view any peaders or footer on the page.

Page Break Preview – view a preview of where pages will break this document is printed.

Custom views – save a set of display and print settings as a custom view ,

Ones you have saved the current view , you can apply it to the document by selecting it from the list of available custom viws .

Full screen- view the document in full screen mode .

SHOW /HIDE –same as above

ZOOM – same as above

WINDOW

New Window – open a new window containing a view of the current document .

Arrange all – fill all open portion windows side by side on the screen .

Freeze Panes – keep a portion of the sheet visible while the rest of the sheet scrolls .

First of all select rows or columns then.

A	B	C	D	E	F
Name Eco Eng Math A/c B st........
Neha 50 70 60 80 90 .........
Manu 56 70 65 54 53 ...
Riya 75 43 48 86 59.....

Freeze panes Freeze pane /freeze top row .

If we want to remove then .

Freeze panes Unfreeze panes.

Split split the window into multiple resizable panes containing views of your worksheet you can use this feature to view multiple distant part of your worksheet at once .

Hide Hide the current window so that it cannot be seen .

Unhide Unhide any windows hidden by the hide window feature .

Save workspace (After use new window, arrange all ) Save the current layout of all window as a workspace so that it can be restored later .

Switch Windows Switch to a different currently open window .

MACROS ( Alt + F8 ) click here to record a macro or to access other macro options .

Write anything Record Macro Macro Name Shortcut key : ctrl + ctrl+ shift +A ok . stop recording

|Again write anything

SELECT

USE SHORTCUT KEY.

SHORT FOR

1.CONCATENATE Joins Several text strings into one text string .

= CONCATENATE (text 1,text 2...)

Ex- A B

Abhay Raj

=CONCATENATE (A1 , b2)

Abhayraj

2.SUBSTITUTE – Replaces existing text with new text in a text string .

=SUBSTITUTE (Text , old –text , new –text , instance-num)

EX- A B C

Abhay ay ishek

= SUBSTiUTE (A3, B3, C3 )

abhishek

.3 . UPPER – Converts a text string to all uppercase letters.

=UPPER – convert a text string to all uppercase letters .

=UPPER (text )

EX- A

Abhishek

=UPPER(A4)

Abhishek

4. PROPER – converts a text staring to proper case;

The first letter in each word in uppercase ,and all other letters to lowercase.

=proper (text )

EX- 5 ABHISHEK

=PROPER (A5)

Abhishek

5 . LOWER –Convert all letters in a text string to lowercase .

Lower (text)

ABHISHEK

=LOWER (A5)

abhishek

6. SQRT – Returns the sqrare root of a number

= SQRT (number )

EX- A

256

=SQRT (A8)

7.INT – Rounds a number down to the hearest integer .

=INT (number )

EX A

256.85

=INT (A10)

256

8.ROUNDDOWN/ROUND- rounds a number down , toward zero .

= Rounddown (number , num –digits )

EX - D

3.141593

=Rounddown (D7,2)

3.14

9. ROUNDUP – Rounds a number up , away from zero .

=ROUNDUP (number ,num –digits)

EX - D

3.141593

=ROUNDUP (D7, 3)

3.142

10.ROUND – Round a number to a specified number of digits .

=ROUND (number , nun –digits ) approx the value.

11.DATEDIF ( )

EX- 33

3/26/1997 (D.O.B)

12.NOW –returns the current date and time formatted as a date and time.

=NOW ( )

1/15/2015 11:06

13.TODAY – returns the current date formatted as a date .

=TODAY ( )

1/15/2015

14.SQRT –Returns the sqrare root of a number .

=SQRT (number )

=SQRT (25)

15.ROMAN – converts an Arabic numeral to roman , as text .

=ROMAN (number ,form)

=roman (100)

16.LEET – Returns the specified number of characters from the start of a text string.

LEET (text ,[num-chars])

10. ABHAY

=LEET (A10 , 2)

17.RIGHT –returns the specified number of characters from the end of a text string .

=RIGHT (text ,[num –chars ])

ABHAY

=RIGHT (A10,2)

18.ROW- returns the row number of a reference .

=ROW ([reference ])

=ROPW (D5 )

19.COLUMN- Returns the column number of a reference .

=CLUMN ([reference ])

=COLUMN (F6)

20.ISEVEN – Returns true if the number is even .

=ISEVEN (number )

=ISEVEN (6)

=TRUE

21.ISODD=Returns true if the number is odd .

= ISODD (number)

=ISODD (6)

= FALSE

22 .EVEN – Rounds a positive number up and negative down to the nearest even integor .

=EVEN (number )

EX - A

20 5

=EVEN (A20)

= 6

23 .ODD – Rounds a positive number up and negative number down to the nearest odd integer .

=ODD (number)

= 21 6

= ODD (A21)

24. LCM – Returns the least common multiple

= LCM (number 1 , [number ],...)

=LCM (2,3,6)

Search This Blog

GYAN SINGH BLOG

MS EXCEL DCA NOTES PART 4

Comments

Post a Comment

Popular posts from this blog

POWERPOINT PRESENTATION

tally important question

DCA TERM-1 EXAM IMPORTANT QUESTION