b.com. i practical excel g

8/3/2019 B.com. I Practical Excel g

1/16

Write down the stepwise procedure to:-

Insert a row & columns in a worksheet.

Copy the contents of a cell to another cell.

Copy the range to another range.

Ans.:-

Insert a row & columns in a work sheet.:-

Sometime, it may be happened that after the spreadsheet is created the user may

want to insert some extra row & columns for some p which was forgotten to be

entered when the spreadsheet was initially created. Excel provides facility to insert

row or columns into the existing spreadsheet easily.

(a) Inserting row in a work sheet:-

The command to insert rows is available in the insert menu as well as in the short

cut menu.

E.g:- lets assume that we want insert a row at the top of the worksheet i.e.

Above the row, 1 .The following step to insert a row at the beginning of the

worksheet has to be done.

(i) Move the cell pointer to any cell in row 1, then open the insert menu.

Excel will display insert menu.

(ii) Choose the rows command from the insert menu & excel will insert

a blank row at the top of the worksheet.

The row can be inserted by shortcut menu. Then we have to follow the following

steps:-

(i) If we have, a mouse clicks the mouse on 1st in the row border. Excel

selects all the cell in row1.

(ii) Now right click the mouse anywhere in the selected row. Excel

display the shortcut menu.

(iii) Choose the insert command form the shortcut menu & excel insert a

blank row.

26


2/16

(b) Inserting a column in a work sheet:-

The procedure to insert column in a work sheet is almost identical it the rows. E.g.

lets assume that we to insert a column left to the column B, then we have to

following steps:-

(i) Select any cell from the column B.

(ii) Open the insert menu then choose the column command from the menu.

Excel will insert a column to the left of a column B & renumbering all columns.

To insert a column using the shortcut menu, then we have to following steps:-

(i) Click mouse on B in the column border excel select the entire column B.(ii) Right click the mouse or press shift+F10 to open the shortcut. Choose

the insert command from the menu & excel insert a column.

To copy the content of a cell to another cell

Lets assume that in a work sheet instead of typing the value in a cell, we want to a

copy the figure from cell.

Copying is two steps process:-

(a)First, select the source cell(s) that we want to copy and use the copy

command. This stores the contents of the selected cell(s) in the memory.

(b) Then select the destination area where we want to copy the data & use the

paste command. This step pastes the data from memory (copied previous step

into the destination area).

To copy a range to another range :-

We can also use the copy & paste command to copy a range. For doing it firstly we

have to select a range.

(a)firstly select the source data (range).

(b)Move the cell pointer of destination cell. This is the first cell of range that we

want to copy.

(c) Press & hold the shift key. Now without realing the shift key, press the

arrow key to select the range.

27


3/16

Excel display flashing dotted lines in the border of selected range. This indicates

that data from this range is available in clipboard. Now to copy the data at

destination.

(a)Move the cell pointer to destination cell; this is the starting cell of the

destination area

(b) Press the enter key or edit/paste command or paste tool button on the standard

toolbar. Excel paste the range from the clipboard to the selected range.

What is chart? What are the various elements of chart? Write

in brief about various type of chart .? Also write down the step

wise procedure to make chart?

Ans.:

Chart are visual representation of data in a vary appealing manner & make if easy

for user to see comparison, patterns and trends in data for instance, rather than

making calculation for comparisons on several column, you can see at a glance to

what extent the projected sales met or whether sales are falling or rising over

monthly period.

Element of 2-dimencial chart

(1)X-axis : It is known as category axis which shows category of data points

that are platted.

(2) Chart title: It can be taken from a cell on that or it can be directly added to

chart.

(3) Category Names : They identify individual data points & may be dated

location ,products & so on. Category names are taken from the top most row or the

left most column depending on the orientation of the sheet.

(4) Legend : It is a set of labels that describe of data series. These labels are

attached to a symbol, a color or a pattern that is associated with the series & palace

on the chart.

(5) Data markers: It is use to distinguish one data series from another.

28


4/16

(6) Trick Mark: They are used to divide the axis and provide the two scaling.

(7) Gridlines : They are display for 60th axis to help read scaled according to

the value on the axis & can be charged.

(8) Data labels : They are displayed some times to show the value of data

points.

(9) Active border: It identifies that a particular chart is active & can be edited

an active border is thicker gray line.

(10) Selected Border: It identifies that a chart can be sized, moved or deleted

& contain modes or handle for that purpose.

Some additional Elements of chart are :-(a) Z- axis: It is called as the value axis & shows the value of the data points.

(b) Wall: It is background of the plotted area.

(c) Corners: They can be rotated to give different views to user.

(d) Floor: It is base upon which series are plotted.

Types of Chart

(1)Area Chart: An area shows the magnitude of change overtime. It is

particularly useful when several components are changing and the user is interested

in sum of components. An area chart is a stacked line chart , with area between the

line filled with colors or shading. An area chart plots data series one on top of the

other.

29


5/16

(2)Bar Chart: Bar Chart consists of a series of horizontal bar that allow

comparison of the relative size of two or more items at one point of time. Each bar

in bar chart in a single data.

The Bar Chart has 3 primary sub types:

(a)The Stacked Bar Chart: The Stacked Bar Chart , as name indicates

stock data , series one on the top of the other.

(b)The 100% Stacked Bar Chart: Inn the 100% chart , all bar became

the same height representing 100% , the segments become of the total

instead of the numerical number.

(c) 3-D Bar Chart: A 3- D bar chart simply adds depth to a standard

chart but does not have a third dimension.

(3)Column Chart: A column chart consists of a series of vertical column that

allows comparison of the relative size of two or more items. Each column in a

30


6/16

column chart is single data point or number on sheet. The set of number for a single

set column is data series.

(4)Line Chart: It is use to show trends overtime. In a line chart each of data

series is use to produce a line on chart with each number in the range producing a

data point on the line.

Two variation of line chart:-

(i) 3-D line chart: Give depth to the chart

(ii)High-Low-Close- chart: A High-Low-Close- chart is a line chart

with 3- data series. It can be typically use to display a stocks high ,low or

closing prices for a given time of period. This chart also works well for

commodity process, currency exchange rates & temperature.

31


7/16

(5) Pie Chart: A pie chart is use for comparing a percentage of a sum that

several number represents. The full pie is the sum and each number is represented

by a slice. A pie chart can be exploded i.e. one of slices separated from other sliceby simply clicking on one of the slice & dragging it away from the other. There is

only one data series in the pie chart. If more than one data series are selected excel

plots the first one.

32


8/16

Variation of Pie chart is a 3-D Pie Chart.

(6) Doughnut charts: The doughnut chart is similar to Pie chart. However

pie chart is restricted to one data series while doughnut chart is not.

(7) Radar chart: Radar chart shows data changes in relation to a centre point

and to each other. The value axis radiates from the centre point. Data from the same

series is connected by lines. Radar chart can be use to plot several interrelated series

and easily make visual comparison.

(8) Scatter chart: Scatter chart shows the relationship between pairs of

number & the trend they present for each pair one of the number is plotted on X-

axis and other number is plotted on the Y-axis. Where two meets a symbol is placed

on chart when a number is plotted a pattern may emerge XY. Chart is also used for

numerical analysis. The symbol chart shows the graphing of future.

Y= sqrt(X)

33


9/16

Q. What is macro? Write down the stepwise procedure to

(1) Record a macro

(2) Run a macro

MACRO

Macros can be defined as a program that instructs EXCEL to perform an action. In

other words they are program run by application for which they are created. Hence

to be brief a Macros is a handy tool to automatic repetitive task.

MS EXCEL posses a feature which can be record and play back command

macros. When you record a series or step EXCEL watches and converts then in to

instruction. In MS-EXCEL, these instruction will be coded VISUAL BASIC

34


10/16

These macros can be run by assigning key board short cuts. You can create macro

buttons or macros can be place on menu as commands.

Macro can be created to run a series of commands and such macro are called

command macros. Command macro can be executed by shortcut keys. A macro can

also be generated to evaluate a user defines function. These are called Custom

macro or function macro will be usually stored in the work sheet itself.

RECORDING MACROS:-

To record your own macro, activate TOOLS> RECORDING MACROS command.

This command presents a dialog box.

In the dialog box,

(1) MS-EXCEL suggests a macro name which you can change.(2) In the description box, add a note on macro to remind you why the macro

is designed.

(3) Click a suitable radio button in store in option. This option saves your

macro in the new work book in the current workbook in a personal workbook ( A

collection of macro ) If the current work option is selected macro will be stored in

the separate sheet on the same work book if the new workbook option is selected

separate workbook is created for the macro.

(4) In the assign to option you can assign a key to run macro key combination

may be with the CTRL key or sometimes with the SHIFT key.

The key can be either a upper case or lower case but cannot be number. If the

selected short key is already defined, EXCEL will alert the user and giver the choice

to reassign through a dialog box. In this option, you can also place the macro in the

tool menu by clicking Radio button of menu item on tools menu.

(5) In the Language, you can also mention the language to create Macros.

Click OK after selecting the suitable option. This closes new Macros dialog box the

recording status message is displayed on the status bar. This marks the beginning of

Macros and from here afterwards all your act will be recorded can also be stopped

using VIEW> TOOLBARS command and clicking on the checks box STOP

RECORDING.

35


11/16

All the actions will be stored in a worksheet. Those sheet is known as Macros sheet

and it contains all the Macros command in terms of formulas. However, this is not

so informative for an and user.

RUNNING MACROS:-Recorded Macros can be played back.

(1) Using shortcut key assigned before creating in the RECORD NEW

MACRO Dialog box.

(2) Select tool> Macros command. This lists the Macros available on the

workbook. Select the required Macros and click RUN button.

DELETING MACROSS:-

Macros can be deleting by erasing the range of Macros commands recorded in

Macro sheet.

Assigning Macros to buttons

A part of two ways of playing a Macros i.e. shortcut key and from TOOLMENU

menu, MS-excel offers another efficient way to run by assigning them to button. By

doing this Toolbar, can be customized to the users needs.

Process assigning a Macros to a button is given in the following steps:

(1) Click non-primary (Right mouse) button to pull down TOOLBAR

shortcut menu.

(2) Choose the CUSTOM option. No excels promote the users to select from

existing tool or a tool note assigned.

(3) Choose CUSTOM from list of tool bar buttons.

(4) Drag this button to the required position on a toolbar.

(5) Select TOOL bar> ASSIGN MACRO command from the dialog box,

select the MACRO to be assigned to button and click.

(6) Click on close in the CUSTOMIZE dialog box.

If a tool bar button which already has a standard function is assigned to Macro, it

can be resorted by using VIEW> TOOLBAR> RESET OR BY CLICKING ON

TOOLBAR SHORTCUT MENUS reset button.

Que.: - What is Excel. Describe the various features of excel?

36


12/16

Ans.: - Microsoft Excel for windows is a power full spreadsheet application that

can be used for managing, analyzing & presenting data in a graphical manner.

Features: -

i. Worksheet & chart: - It includes extremely powerful calculating features.Besides working numbers, text, excel also provides for presenting data graphically.

This help to a large extent for data analysis.

ii. Data list & Databases: - Data base functions are another important feature ofexcel. Several effective functions are available for working with data listed in a

tabular form. These are functions for evaluating, coping, and if necessary, rearranging

data lists.

iii. Data exchange with other application: - Excel uses the advantage of thewindows environment. This especially applies to dynamic data exchange (DDE)&

object linking embedding (OLE) with in excel and between excel and other windows

application. As a result data, graphics, sound and animations can be easily mixed in asingle document.

iv. Standardized user interface: - Except for a specific menu in each application,the menus in the main menu bar of excel, word, PowerPoint is identical. The key

combination (for eg. Cut, Copy, Paste etc.) are also standardized.

v. Workbook: - Finally excel works with a consistent file concept. All data isgathered in workbooks. This workbook store current status of the workspace. Along

with all currently opened files & the setting selected for them.

vi. Auto fill: - It helps us to fill rows or columns with the series of data.

vii. Auto sum: - By this feature of excel, we can add a large range of data by simplyselecting a tool button.

viii. Tip wizard: - Guides us to work more effectively while we work by displayingvarious helpful tips & techniques based on what we are doing.

ix. Shortcut menus: - Commands appropriate to task that you are doing appear byclicking the right mouse button.

x. Drag & Drop: - It helps us to reposition the data & text by simply dragging thedata with the help of mouse.

Que.: - Explain the following terms with reference to excel: -i. Row

ii. Columniii. Work sheet

iv. Auto fill

v. Auto sum

vi. Range

vii. Cell

Ans.: -

i. Row: - In a single work sheet. These are 65536 rows from top to bottom.

37


13/16

ii. Column: - columns are labeled from left to right (A to iv) for a total of 256columns

iii. Work sheet: - In excel where data is organized is known as work sheet. The worksheet is displayed as a grid of 65536 rows and 256 columns.across the top of the work

sheet are column headers are labeled as A,B,C and so on till Z the column are labledas AA,AB,AC.AZ,BA,BB,BC.BZ, and so on .

iv. Workbook: - A workbook is an excel file where the user stores his / her data. Eachwork book consist of several work sheets i.e.a work can be thought of as a file folder

and a work sheet as every page in a file folder. Hence a work book is said to be a set

of work sheet grouped together.

v. Auto fill: - Although excel can perform several tasks, the users has to enter thenumbers and text manually. However excel provide a solution to this time consuming

process . whenever users want to fill a cell range with data that forms a series ( e.g.

1,2,3 or Mon., Tue., Wed., or Jan., Feb., Mar.) the data input can be auto mated . this

can be done by fill handle. The fill handle is a black square located on the lower rightcorner of selected cells. This is called auto fill. It helps us to fill rows or column with

the series of data.

vi. Auto sum: - By this feature of excel, we can add a large range of data simplyselecting a tool button.

vii. Range: - a range is a rectangular group of cells. The smallest range is a single cell,while the largest range encompasses all the cells in the work book. A range can be

contain cells from a single sheet only or can include cells from adjacent sheet.

viii. Cell: - The intersection of row and column is called a cell. There are approximatelyover 4 million cells in single work sheet and each can hold up to 255 character or up

to 1024 characters in a formula. Light gray lines know as gridlines surround each cell.Que.: - write about with example any six built in function of following categories:-

1. Mathematical functions

2. Text functions

3. Date & Time functions

4. Statistical functions

Ans.: -

1. Mathematical functions: - Mathematical functions are the functions are

used to perform general mathematical operation. Some of the mathematical

functions are as follows:-

i. ABS (number)- Returns absolute value of a numbers.

E.g.=()

Ans. is

ii. Fact(number)- Returns factorial of a number.

E.g.=Fact(3)

Ans. Is 6

iii. Integer(number)- Returns number round to the nearest integer.

E.g.=Int()

Ans. is

38


14/16

iv. Logarithmic(number)- Returns logarithm of a number to a specified base.

E.g.=()

Ans. is

v. Mod(number, divisor)- Remainder of a number from division.

E.g.=Mod(45,2)

Ans. is 1vi. Product(num1, num2)- Returns product of numbers.

E.g.=Product(2,4)

Ans. is 8

2. Text functions: - text functions are used to convert, purse & manipulatetext strings. Some of the text functions are as follows: -

i. Concatenate (text1, text2)- This function joins several text items in

to one text item. Here text1, text2 are 1to 30 text items to be joined in to a

single item.

E.g.= Concatenate (soft, ware)Result is software

ii. Exact (text1, text2)- It compares two text string & returns true if they

exactly the same, otherwise false.

E.g.= Exact (abra, abba)

Result is false

iii. Find (find text, with in text, start num.)-It finds one string of text with

in another string of text & returns the numbered character at which find text

occurs. Here find text is the text we want to find.

E.g.=find (text)

Result is 6

iv. Left (text, num.)-It returns the left most num. Character from thetext. Here text is the text string containing the characters we want to extract. And

num. Specifies how many character we want to left to ret num. Must be grater

than or equal to zero.

E.g.=Left (version, 3)

Result is ver

v. Right (text, num.)- It returns the right most num. character from the

text. Num. specifies how many characters we want to extract.

E.g.=Right (salesman, 3)

Result is man

vi. Len (text)-It returns the no. of character in a text string. It also counts

the spaces as character. Here text is the text whose length we want to find.E.g.=Len (length)

Result is 6

3. Date & Time: - Date & Time functions are use data serial num.

to calculate various date & time related num. i.e. the argument name in function is

serial number.

Functions Returns Example Result

1.Date (yy,mm,dd) Specified date serial no. Date (1994,8,21) 34567

39


15/16

2.Day (serial no.) A value from 1 to 31

corresponding to the day

given by the serial no.

Day (34567) 21

3.Month (serial no.) A value from 1 to 12

corresponding to the

month given by serial no.

Month (34567) 8

4.Year (serial no.) The year corresponding

to serial no.

Year (34567) 1994

5.Days 360 (start

date, end date)

No. of days between start

date & end date

Days360

(8,12,9,4,8,12,95)

360

6.Now () Current date & time Now ()

Functions Returns Example Result

1.Time (hh,mm,ss) Specified time Time (11,15,20) 046898

2.Hours (serial no.) A value from 0 to 23corresponding to the hour

given by the serial no.

Hour (046898) 11

3.Minutes (serial no.) A value from 0 to 59


minutes given by the

serial no.

4.Seconds (serial no.) A value from 0 to 59


seconds given by the

serial no.

4. Statistical functions: - These are the functions used to make thestatistical computations. Some of these functions are as follows: -

i. Average (no.1, no.2)-Returns average of the numbers given as argument.

E.g.=Average (3,3,3)

Result is 3

ii. Max (no.1, no.2)- Returns max. value of the data set given as argument.

E.g.= Max (2,4,7)

Result is 7

iii. Min (no.1, no.2)- Returns min. value of the data set given as argument.

E.g.= Min (2,4,7)

Result is 2

40


16/16

41

b.com. i practical excel g

Documents