Q. No. 4 (a) Define
Worksheet. What steps are involved to modify an existing workbook when;
. To insert and delete rows and column
. To print cell formulas and displayed values
. To use the page setup command
Answer:
(i)
Worksheet
Microsoft
Excel is part of Microsoft office. The program is used for simple & complex
calculations, formulas and charts etc. in the business.
A
worksheet, also referred to as a spreadsheet, is a computerized page allowing
the user to manipulate many columns and rows of numbers. The worksheet can
contain formulas so that if one number is changed, the entire worksheet is
automatically updated, based on those formulas. Analysts, investors, and
accountants track a company's financial statements, balance sheets, and other
data on worksheets. Worksheets can be made with Microsoft Excel and other
Formula based Software or Programs.
(ii)
Steps to Modify workbook
(a) Insert and Delete Rows and Column
Inserting
& Delete Row / Column
-
There are many ways to insert rows and columns;
(i)
Keyboard Short-keys
(ii)
Menu Command
(iii)
Mouse Right Click
(i) Mouse Right Click
-
First start MS-Excel then open an existing file,
like List.xls.
-
Click in A11 cell and then Right click on that cell.
-
A new popup window, as shown below, will open.
-
For inserting, click on Insert Option, another new
pop up will open as shown fig 2.
-
Here are Four options
o Shift Cell
Right
By
this option, only a cell (column) will be inserted.
o Shift Cell
Down
By
this option, only a cell (row) will be inserted.
o Entire Row
By
this option, an entire row will be inserted.
o Entire
Column
By
this option, an entire column will be inserted.
-
For Deleting, click on Delete Option, another new
pop up will open as shown fig 3.
-
Here are Four options
o Shift
Cell Left
By
this option, only a cell (column) will be deleted.
o Shift Cell
Up
By
this option, only a cell (row) will be deleted.
o Entire Row
By
this option, an entire row will be deleted.
o Entire
Column
By this
option, an entire column will be deleted.
(i) Menu Command
- First start
MS-Excel then open an existing file, like List.xls.
-
Click on Insert menu and then click Rows, a row
above the selected cell will be inserted, without prompting.
-
Click on Insert menu and then click Columns, a column
left to the selected cell will be inserted, without prompting.
-
Click on Edit menu and then Delete for deleting rows
/ columns. This will delete cell or entire row / column as stated above.
(iii) Keyboard Short Cut
-
Click Ctrl + for inserting rows / columns and Click
– for deleting rows and column
(b) Print Cell formulas and displayed values
·
Click the Tools menu and then click Option. A
new window for option will be appeared.
·
Click View Tab and then click on Formulas option
in Windows Options, as show in below fig.
·
Ctrl + ~ command also do the above action
without prompting.
·
After these above command, formula implemented
in the worksheet will displayed, as show below:
·
For printing the formulas and displayed values,
used Print command by click File menu then Print or press Ctrl + P. A new window will opened, click on OK button
for printing.
(c) Page Setup Command
Setup command is used set Page
Orientation, Paper size, Scaling, Margin, inserting Header / Footer and sheet
options.
·
For using Setup command, invoke the File menu
and click Page setup command to display the page setup dialog box as shown in
above figure.
·
To change page orientation from Portrait to
Landscape, click Page tab.
·
To change /set margins, click Margin tab
·
To change / insert header / footer, click Header
/ Footer Tab.
·
To repeat row or column heading, and other sheet
options, click Sheet tab.
(b)
Describe the steps for importing data from a Web query into an Excel
workbook; explain how to refresh the query to obtain current information.
Answer:
We can
insert any web query in the workbook to get current stock prices from the
Internet, by the following steps:
·
Create new file, as shown below, enter data.
·
Invoke the Data menu and click Import External Data
Command, then chose import Data Command to display the Select Data Source
dialog box as shown below.
·
Select the MSN Money Central Investor Stock Quotes query and click Open
button.
· To import
data from Query, select the Cell Address, where to import the Data and then
click OK button.
click OK button.
·
Data inserted on selected cell address, as shown.
Refresh Query Data:
·
Right Click anywhere in the Web Query i.e. on
Cell D14 to display the context-sensitive menu, as shown in above figure.
·
Then click on Refresh Data command to retrieve
the current prices from the Web.
·
Save the workbook, now.
Q. No. 5 MS-Excel Charts, types and difference.
Answer:
A chart or
graph is a type of information graphic or graphic organizer that
represents tabular numeric data
and/or functions. Charts are often used to make it easier to
understand large quantities of data and the relationship between different
parts of the data. Charts can usually be read more quickly than the raw data that
they come from. They are used in a wide variety of fields, and can be created
by hand (often on graphic paper) or by
computer using a charting application.
Certain
types of charts are more useful for presenting a given data set than others.
For example, data that presents percentages of
different groups (such as "satisfied, not satisfied, unsure") might
be best represented as a pie chart, allowing
the viewer to compare the size of each sector slice. On the other hand, data
that represents numbers that change over a period of time (such as
"satisfaction between 1990 and 2000") might be best shown as a bar chart (or histogram).
Microsoft
Excel supports many kinds of charts to help you display data in ways that are
meaningful to your audience. When you use the Chart Wizard to create a
chart — or when you use the Chart Type command to change an existing
chart — you can easily select the type you want from a list of standard or
custom chart types.
A
column chart shows data changes over a period of time or illustrates
comparisons among items. Column charts have the following chart sub-types:
Clustered
Column This type of chart compares values across
categories. It is also available with a 3-D visual effect. As shown in the
following chart, categories are organized horizontally, and values vertically,
to emphasize variation over time.
Stacked
Column This type of chart shows the relationship of
individual items to the whole, comparing the contribution of each value to a
total across categories. It is also available with a 3-D visual effect.
100%
Stacked Column This type of chart compares the percentage
each value contributes to a total across categories. It is also available with
a 3-D visual effect.
3-D
Column This type of chart compares data points (data
points: Individual values plotted in a chart and represented by bars, columns,
lines, pie or doughnut slices, dots, and various other shapes called data
markers. Data markers of the same color constitute a data series.)
along two axes. For example, in the following 3-D chart, you can compare four
quarters of sales performance in Europe with
the performance of two other divisions.
Bar charts
A
bar chart illustrates comparisons among individual items. Bar charts have the
following chart sub-types:
Clustered
Bar This type of chart compares values across categories.
It is also available with a 3-D visual effect. In the following chart,
categories are organized vertically, and values horizontally, to place focus on
comparing the values.
Stacked
Bar This type of chart show the relationship of individual
items to the whole. It is also available with a 3-D visual effect.
100 %
Stacked Bar This type of chart compares the percentage each
value contributes to a total across categories. It is also available with a 3-D
visual effect.
A
line chart shows trends in data at equal intervals. Line charts have the
following chart sub-types:
Line
This type of chart displays trends over time or categories. It is also
available with markers displayed at each data value.
Stacked
Line This type of chart displays the trend of the
contribution of each value over time or categories. It is also available with
markers displayed at each data value.
100%
Stacked Line This type of chart displays the trend of the
percentage each value contributes over time or categories. It is also available
with markers displayed at each data value.
3-D
Line This is a line chart with a 3-D visual effect.
A
pie chart shows the size of items that make up a data series (data
series: Related data points that are plotted in a chart. Each data series in a
chart has a unique color or pattern and is represented in the chart legend. You
can plot one or more data series in a chart. Pie charts have only one data
series.), proportional to the sum of the items. It always shows only
one data series and is useful when you want to emphasize a significant element
in the data. Pie charts have the following chart sub-types:
Pie
This type of chart displays the contribution of each value to a total. It is
also available with a 3-D visual effect, as shown in the following chart.
Exploded
Pie This type of chart displays the contribution of each
value to a total while emphasizing individual values. It is also available with
a 3-D visual effect.
Pie
of Pie This is a pie chart with user-defined values
extracted and combined into a second pie. For example, to make small slices
easier to see, you can group them together as one item in a pie chart and then
break down that item in a smaller pie or bar chart next to the main chart.
Bar
of Pie This is a pie chart with user-defined values
extracted and combined into a stacked bar.
.
An
xy (scatter) chart shows the relationships among the numeric values in several data series (data
series: Related data points that are plotted in a chart. Each data series in a
chart has a unique color or pattern and is represented in the chart legend. You
can plot one or more data series in a chart. Pie charts have only one data
series.), or plots two groups of numbers as one series of xy
coordinates. Scatter charts are commonly used for scientific data and have the
following chart sub-types:
Scatter
This type of chart compares pairs of values. For example, the following scatter
chart shows uneven intervals (or clusters) of two sets of data.
When
you arrange your data for a scatter chart, place x values in one row or column,
and then enter corresponding y values in the adjacent rows or columns.
Scatter
with Data Points Connected by Lines This type of chart can be
displayed with or without straight or smoothed connecting lines between data
points. These lines can be displayed with or without markers.
An
area chart emphasizes the magnitude of change over time. Area charts have the
following chart sub-types:
Area
This type of chart displays the trend of values over time or categories. It is
also available with a 3-D visual effect. By displaying the sum of the plotted
values, an area chart also shows the relationship of parts to a whole. For
example, the following area chart emphasizes increased sales in Washington and
illustrates the contribution of each state to total sales.
Stacked
Area This type of chart displays the trend of the
contribution of each value over time or categories. It is also available with a
3-D visual effect.
100%
Stacked Area This chart type displays the trend of the
percentage each value contributes over time or categories. It is also available
with a 3-D visual effect.
Like
a pie chart, a doughnut chart shows the relationship of parts to a whole;
however, it can contain more than one data series (data
series: Related data points that are plotted in a chart. Each data series in a
chart has a unique color or pattern and is represented in the chart legend. You
can plot one or more data series in a chart. Pie charts have only one data
series.). Doughnut charts have the following chart sub-types:
Doughnut
This type of chart displays data in rings, where each ring represents a data
series. For example, in the following chart, the inner ring represents gas tax
revenues, and the outer ring represents property tax revenues.
Exploded
Doughnut This chart type is like an exploded pie chart, but
it can contain more than one data series.
A
radar chart compares the aggregate 
No comments:
Post a Comment