Monday, February 20, 2012

Download

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.
·              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.
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