CLASS_IX_CHAPTER- 10

CHAPTER - 10 ANSWER

A. Tick (3) the correct answer.

a. (ii) Spreadsheet

b. (iv) Worksheet

c. (i) Active cell

d. (iii) 3

e. (iii) Formula bar

f. (i) Left

g. (i) Ctrl

h. (i) F8

i. (iv) All of these

j. (iii) = sign

k. (ii) MIN( )

l. (i) Mixed reference

m. (iv) Area chart

B. Answer the following as True or False.

a. True

b. False

c. False

d. True

e. False

f. False

g. True

h. True

i. True

j. False

k. True

l. True

m. False

n. False

C. Fill in the Blanks:

a. Rows, Columns

b. Cell

c. Spreadsheet

d. Cell address

e. Right

f. 1,048,576 , 16,384

g. 8.43, 15

h. Function

i. Values, Cell address, Cell range

j. Chart

k. Embedded Chart

l. Legends

D. Very short answer type questions.

a. Microsoft Excel 2010 is used to organise, analyse and manipulate data electronically in the tabular form.

b. There are 3 sheets are there by default in a workbook.

c. The extension of files saved in Excel is .xlsx.

d. There are 1,048,576 rows and 16,384 columns in it.

e. Active Cell is the one which is pointed by the cell pointer to store, manipulate or format data in that cell.

f. We select the complete row by clicking in the row header to be selected.

g. The different cell addressing modes used in a formula are: Relative Reference, Absolute reference and Mixed

Reference.

h. The different types of data values can be entered in the Excel Worksheet are: Number, Text and Formula.

i. Pie chart represents only single data series.

j. ^ Exponent ,/ Division ,* Multiplication ,+ Addition ,– Subtraction arithmetic operators can be used for creating

formulas in a spreadsheet.

E. Short answer type questions.

a. An electronic spreadsheet allows storing large amount of data in a single worksheet. The data entered can be

easily manipulated or formatted as per need. It allows automatic calculations and to quickly convert the numeric

data into the graphical form called chart.

b. Workbook is a collection of multiple worksheets. Excel workbook is saved with the extension .xlsx. By default it

contains three worksheets with the default names as Sheet1, Sheet2 and Sheet3.

Worksheet is like an individual page of a workbook made up of cells formed by the intersection of rows and

columns. A single worksheet in Excel contains 1,048,576 rows and 16,384 columns in it.

c. A range is rectangular block of contiguous cells used to perform common operations on multiple cells. Multiple

non- contiguous ranges in a worksheet can be selected using Ctrl key without deselecting the previously selected

ranges.

d. AutoFill handle presents at the bottom right corner of the active cell or range is used to fill automatic series of

number or text in the consecutive cells across the rows or column.

e. The Different number formatting options available in Excel are:

Number: To display general format of number.

Currency: To show values in currency format.

Accounting: To line up the currency symbols and decimal points.

Date: To display date serial numbers as data value.

Time: To display time serial numbers as data value.

Percentage: Multiply the cell value with 100 and show % sign.

Fraction: To show fractional values.

Scientific: To show more precise fractions.

f. Formula is a mathematical expression made up of operators and operands which is used to perform calculating

e.g., =(4+5)*2 whereas Functions are the predefined formulas used to perform complex calculations on the

supplied arguments e.g., =SUM(4,5,6).

g. Making use of cell address in a formula to refer the value stored in that cell is called cell referencing.

There are three ways to refer the cell address in the formula. These are:

Relative Reference: This type of referencing causes the change in cell address used in the formula with

respect to the change in position of the formula when copied. e.g.,=A1:A2

Absolute Reference: In this, the cell address in the formula remains fixed irrespective of its change in position

when copied. e.g., =$A$1+$A$2

Mixed Reference: In this, the change in cell address is restricted to either row or column only when copied.

=A$1+$A2.

h. Charts are the graphical representation of data values stored in the worksheet. These are mainly used to quickly

analyse the data values by representing it in different formats. Some of the commonly used charts in Calc are:

Column chart, Bar chart, Pie chart, Line chart, Area chart, Scattered chart, etc.

i. (i) Line Chart: A Line Chart represents the changing trends of the data over the time at equal intervals.

Area Chart: Area Chart is also used to emphasise the magnitude of change in data over a period of time.

But in this each data series is plotted to show the relationship of a part to the whole.

(ii) Pie Chart: Unlike the other chart types, Pie Chart is used to plot just one data series to represent the

proportional size of one item to the sum of the items in the series. Each item is represented by one slice or

pie of the whole chart.

Doughnut Chart: Doughnut Chart is used to represent the relationship of a part to the whole. But unlike

pie chart, doughnut chart can represent more than one data series where in each series is plotted in the form

of concentric circles.

j. To Change the Data series:

● Click on the Chart to select it.

● Choose Select Data ( ) option in the Data group under the Design tab, to open Select Data

Source dialog box.

● Click on collapse button in the Chart data range box which is showing the current data range.

● Select the changed data values to be plotted.

● Expand the dialog box clicking the expand button and click OK button.

k. To Select the Entire Worksheet:

● Press Ctrl + A keyboard shortcut.

Or

● Click the Select All button at the intersection area of row and column header.

F. Long answer type questions.

a. (i) (a) =(B2+C2)*0.25

(ii) =(B2+C2+D2)-E2

(iii) =MAX (F2:F8)

(iv) =COUNT(B2:B8)

(v) IF(A1>75000,”not eligible for bonus “,” bonus “)and then copy this command to all cells.

(vi) Autofill feature

(vii) Bar chart

(viii) No, he need not to do the calculations again as the relative reference is used in all the formulas created in

the worksheet.

b. The basic components of electronic spreadsheet are:

Workbook: A basic Excel file, contains multiple worksheet in it.

Worksheet: Individual page of a workbook made with the intersection of rows and columns.

Rows: Horizontal sections of a worksheet used to store record values.

Columns: Vertical sections of worksheet used to store field values of the worksheet.

Cell: A small rectangular block formed by the intersection of row and column. These are used to store worksheet

data values.

Cell Pointer: Highlighted cell boundary which appears on currently active cell and is used to modify or format

the cell value.

c. To Rename a Worksheet:

● On the sheet tab area, right click on the sheet name you want to rename and choose Rename option from

the shortcut menu.

● Select the current name and then type the new name. Press Enter Key.

To Add a New Worksheet in the Workbook:

● Click and select the sheet using sheet tab before which you want to insert new sheet.

● Right click on it and choose Insert option from the shortcut menu. Insert dialog box will appear as shown in

figure.

● From the General tab in the dialog box, select Worksheet icon and click OK button to get a new blank

worksheet in the workbook.

To Delete a Sheet:

● Select the worksheet(s) to be deleted.

● On the Home tab in the Cells group, click Delete ( ) drop button and choose Delete Sheet option in it.

d. To fill standard data series in the Excel worksheet:

Using Autofill Handle: Enter the first two values of the series in two consecutive cells along the row or

column. Select these two cells to form a range. Click and hold Auto Fill handle drag across the row/column along

which series is to be filled. Release the mouse button when you reach to last number of the series.

Using Series Command: Select the range of empty cells along which you want to fill the series.

Home Editing Fill Series option.

● Enter the first value of the series in the first cell and select the range to be filled.

● On the Home tab in the Editing group click Fill ( ) and then click Series option in it. A Series dialog

box will appear.

● Under Type, click one of the option:

■ Linear: Series created by adding the values.

■ Growth: Series created by multiplying the values.

■ Date: To create series of date values.

■ AutoFill: Creating series like using fill handle.

● Enter the Step Value by which the series values are to be changed and the last value in the Stop Value

box. Click OK button.

● The range will be filled with the required series.

e. To insert functions in the Excel 2010 worksheet you can make use of function wizard as follows:

● Select the cell in the worksheet where function results are to be displayed.

● Click the Insert Function ( ) option on the Formulas tab.

Or

● Click Insert Function ( ) button on the Formula bar.

Or

● Press Shift + F3 keyboard shortcut.

● Insert Function dialog box will appear as shown in figure.

● Select the function category from the Select a category drop list. Function from the selected category will

appear in the Select a function list box.

● Click and select the function to be used from the list.

● Click OK button. Functions Arguments dialog box appear on the screen.

● Click in the argument box named Number1 and use collapse ( ) button to the right of it, if you want to

select argument list from the worksheet.

● Select the desired data range in the worksheet on which the function is to be performed. The selected range

will appear in the argument box. You can add more arguments if needed.

● Once finished with the arguments selection, press Enter key or click OK button. The result will appear in the

destination cell.

f. To create a Chart in Excel Worksheet:

● Select the data in the worksheet, to be plotted including the column headers.

● Click on Insert tab and in the Charts group click the specific chart category drop button and choose the

required chart from the expanded list.

● The chart plotted with the selected values will appear on the worksheet in default settings.

g. (i) 44 (ii) –40

(iii) =$B$3+D3 (iv) =$B3+D3

(v) =$B4+D$3

G. Application oriented questions.

a. (i) =SUM(C2:G2) (ii) =($H$1)*100

(iii) =AVERAGE(C3:C6) (iv) AutoFill feature

b. (i) Chart title- Quarter wise sales analysis

X-axis title- Region

Y-axis title –Sales(amount)

(ii) Bar chart (iii) Consistent

(iv) South (v) North