Class 9 Information Technology 402
Unit 4 Electronic Spreadsheet
Sumita Arora Book Solution
Session 1 – Getting Started with Spreadsheet
Que 1. What is a cell and how is it referred in OOo Calc?
Answer:- A cell on a spreadsheet is the intersection of a row and a column. In applications with spreadsheets, a cell is a box where you can enter a single piece of data. OOo Calc deals with the so-called spreadsheet elements.
Que 2. What do you mean by a range of cells?
Answer:- A cell range in a Spreadsheet file is a collection of selected cells.
Que 3. What is the difference between a worksheet and a workbook?
Answer:- A worksheet is a grid of cells made up of rows and columns. Multiple worksheets combined under a file is known as Workbook.
Que 4. What is the name of the package which permits people to quickly create, manipulate and analyze data arranged in rows and columns?
Answer:- Spreadsheet package.
Que 5. What does an electronic spreadsheet consist of?
Answer:- Rows, Columns, Cells.
Que 6. Write cell references for the following:
i) Cell formed by the intersection of row 18 and column Z.
ii) The rightmost cell in row 32 in a worksheet.
Answer:-
i) Z18
ii) A32
Que 7. How will you enter i) cells ii) rows iii)columns in a worksheet?
Answer:- To enter/insert a cell, row, Columns in a worksheet,
Right Click a cell > select > insert > select option
(for cell – ‘shift cells right /down’, for rows – ‘Entire row’, for the column – ‘Entire
column’). Or you can simply use ‘CTRL SHIFT +’.
Que 8. Why do you freeze some rows/columns in a worksheet?
Answer:- When you freeze panes, it keeps specific rows or columns visible when you scroll in the worksheet. For example, if the first row in your spreadsheet contains headers, you might freeze that row to make sure that the column headers remain visible as you scroll down in your spreadsheet.
Que 9. How do you freeze some rows/columns in a worksheet?
Answer:- Freezing a row and a column
Click into the cell that is immediately below the row you want frozen and immediately to the
right of the column you want frozen.
Select Window > Freeze.
Que 10. How is splitting of screen useful?
Answer:- Splitting screens, whether vertically or horizontally, is a great way to streamline your
workflow when using Excel. Using this feature lets you simultaneously view separate
sections of a spreadsheet, enabling you to compare data quickly.
Session 2 Working With Data
Que 1. What are the different types of data that can be entered in OOo Calc?
Answer:- Three types of data that can be entered in OOo Calc worksheet are:
ï‚· Numbers
ï‚· Text
ï‚· Formulas
Que 2. Give the keyboard shortcut keys for Copy and paste.
Answer:- The shortcut keys for Copy is CTRL+C and that for paste is CTRL+V.
Que 3. Write cell references for the following:
(i) If you select on the entire worksheet, which range of cells gets selected?
Answer: A1 : AMJ1048576
(ii) Reference (Fixed Column and relative row) formed by row 134 and column BD.
Answer: $BD134
(iii) Mixed Reference (Relative column and Fixed row) formed by row 120 and column IA.
Answer: IA$120
(iv) Absolute reference formed by 45 and column Z.
Answer: $Z$45
(v) Relative Reference formed by row 19 and column AB.
Answer: AB19
Que 4. Suggest the Calc functions that can be used for carrying out following operations:
(i) To calculate total marks of a student if his marks in five subjects are given in five different cells
Answer:– SUM()
(ii) To calculate average sales made by salesman of a company, if sales made each of the salesmen is available
Answer:– AVERAGE()
(iii) To find out the marks of top scorer in a class, if marks of all the students are available
Answer:- MAX()
(iv) To find out minimum quoted rate from various quoted rate from various quotations available
Answer:– MIN()
Que 5. What are the rules to be followed while entering the following in OOo Calc?
i) Number
ii) Text
iii) Formulas
Answer:-
i). OOo Calc ignores the leading plus (+) sign and treats a single period as a decimal.
ii). In OOo Calc , text is any combination of numbers , spaces and non-numeric characters
iii). Formulas in OOo Calc starts with ‘=’ sign
Que 6. Differentiate between mixed referencing and absolute referencing by giving suitable examples.
Answer:- In Absolute Cell Referencing the cell references remain constant and do not change with change in formula position when copied to a new position.
$ (Dollar) sign us used to make a row or column absolute.
For example => $A$1
In Mixed Cell Referencing either a column or a row is made absolute or fixed with help of $(Dollar) sign.
For example
=> Constant Row => C$1
=> Constant column => $C1
Que 7. What is the difference between copying and moving a range?
Answer:- In copy, as data gets copied at the new location it remains at the original location also. Whereas in Move, as data gets copied at the new location it gets deleted from the original location.
Unlike copy, the move operation does not change the cell address in a formula.
Que 8. What is the difference between the following commands:
(i) Edit → Delete Contents → Delete all and Edit → Delete Contents → Text
Answer :
Delete All – deletes all contents from the selected range
Text – deletes text only. Formats, formulas, numbers and dates are not affected.
(ii) Edit → Delete Contents → Text and Edit → Delete Contents → Formats
Answer :
Text – deletes text only. Formats, formulas, numbers and dates are not affected.
Formats – deletes format attributes applied to cells. All cell contents remain unchanged.
(iii) Edit → Delete Contents → Text and Edit → Delete Contents → Numbers
Answer :
Text – deletes text only. Formats, formulas, numbers and dates are not affected.
Numbers – deletes numbers only. Formats and formulas remain unchanged.
Que 9. What are regular expressions? How are they useful?
Answer:- Regular expressions are a way of defining patterns using some special characters that can apply to sequences of things.
Using a Regular expression one can match multiple sets of data that match the criteria set by a
regular expression.
Que 10. Suggest regular expressions for the following:
(a) To search for words care and case
Answer: \bcare\b, \bcase\b
(b) To search for the word ‘Initially in the beginning of a line.
Answer: ^Initilly
(c) To search for the word ‘initially in the end of a line.
Answer: Initilly$
(d) To look for all word combinations having a single character between n and t
Answer: n.t
(e) To look for all word combinations starting with ‘baÅ¥ and having zero or more ‘s’ followed it.
Answer: Bats*
(f) To look for all word combinations having one or more instances of ‘a’ between words ‘ko’ and ‘s’.
Answer: Koa+s
Que 11. A finance officer uses a spreadsheet to record sales from an equipment shop. This is part of the spreadsheet (refer to book page no – 186)
(a)
(i) Write the label of the formula in cell D7.
Answer:- = C7 * B7
(ii) Write the label of the formula in cell D10.
Answer:- =SUM(D4:D8)
(b) The price of the generator is reduced to 4000.00.
(i) Describe how the spreadsheet should be edited.
Answer:- We have to use the formula in B6 as =B6-1000
(ii) When the spreadsheet was edited, the values displayed in some cells changed automatically.
Write in the box the cell reference of two cell that changed automatically.
Answer: D6 and D10
(iii) Tick the box to show why a cell would change automatically when another cell is edited.
Answer:- the cell contains a formula that refers to the other cell.
Que 12. A university student uses a PC to help plan a budget for the first term. Their first attempt is shown below (refer to book page no 187)
a) Tick one box to show which type of software package has been used.
Answer:-Spreadsheet
b) Which cell contains the spending on rent per week?
Answer:- D21
c) Which one of the following is the formula used in cell D23?
Answer:- =D21-D8
d) Tick one box to show a disadvantage of using a software package to help work out the budget rather than using a calculator, pen and paper.
Answer:- The wrong prices could be input
Que 13. A self-employed car mechanic uses a spreadsheet to calculate bills for customers. (refer to book page number 188)
Answer:
a)
i) C7
ii) highlight
b) C10 and C11
c) Formula
d) A search engine to help the mechanic find the information needed
e) i) Go to the online store using the web browser
ii) IN is best used for all the websites that need to target the Indian audience and users and it will show the price in rupees by default.
f) i) There is no last range of weeks for paying.
ii) The code can be improved by setting last week for paying money.
Que 14. A customer downloads a bank account statement from an online bank. (refer to page number 190).
Answer:-
a) A date, A number, A picture
b) i) B5 ii) C2
c) E5-C6+D6
d) =E8
e) i) Calculating mortgage repayments, Modelling future spending, Displaying the balance carried forward each month of the year in a graph
ii) Payroll system
f) A->D->B->C
g) The file may be corrupted. So the best option to save it in a secure disk.
h) 80 Gigabytes
Session 3 Formatting and Sorting Data
Que 1. What is formatting? Why/How is it useful?
Answer:- The general arrangement of data is known as formatting.
Formatting provides worksheets a neater and more legible outlook. It can emphasize certain data, and can create a hierarchy of relative importance among different elements of data.
Que 2. List some common formats that you can use for formatting numbers.
Answer:- Several number formats can be applied to cells by using icons on the Formatting toolbar.
Select the cell, then click the relevant icon. Some icons may not be visible in a default setup; click
the down arrow at the end of the Formatting bar and select other icons to display.
Number format icons: currency, percentage, date, exponential, standard, add decimal place, delete decimal place.
Que 3. How would you format monetary values so that the currency symbol is shown?
Answer:-
- Select the range of cells to be formatted.
- Select cells on the format menu.
- Now Format Cells dialog box appears.
- Click the numbers tab.
- Select firstly Category of formats and then select or specify the additional information (here you have to select category as Currency and Format as desired currency format i.e. INR Rs. English (India)
Que 4. How will you format dates so that dates appear in a format similar to 26 January 2022.
Answer:
- Select the cells you want to format.
- Click the command Format menu-> Cells or press the shortcut key Ctrl+I.
- From the format cell dialog that appears, click on the numbers tab.
- Select the date category from the category box.
- For a format that is not listed, click in the format Code text box and type your own format.
Que 5. What is conditional formatting?
Answer: Conditional formatting is a feature that allows you to apply specific formatting to cells
that meet certain criteria.
Que 6. List at least three examples where conditional formatting is useful.
Answer:- Conditional formatting is usually used to mark cells with something based on the cell
value. It can be marked by signs, colors, etc.
Examples-
- –Highlight Duplicates in Column.
- –Highlight Duplicate Records in a List.
- –Highlight Cells With Formulas.
Que 7. What is the use of sorted data?
Answer:- Sorting is the process of arranging data into meaningful order so that you can analyze it more effectively.
Que 8. How many fields can you use for sorting data in Calc?
Answer: You can sort by up to three columns or rows at a time.
Que 9. Is it possible to perform (i) case sensitive sorting? (ii) columnwise sorting?
Answer:- (i) Yes Possible (ii) Yes Possible.
Que 10. How will you accomplish case sensitive and column-wise sorting?
Answer:- Column-wise sorting:
- Select the Range of the table.
- Go to Data tab and then select Sort.
- Select sort criteria (i.e. sort by column name)
Case-sensitive sorting:
- Follow all the steps of column-wise sorting and in the sort dialog box select option as case sensitive.
Session 4 Charts and Graphs
Que 1. State True or False:
i) Graphs let the audience visualize trends quickly.
Answer:- True
ii) Graphs are a compact way to show information.
Answer:- True
iii) Graphs make it hard to find the main point of some data.
Answer:- True
iv) Graphs add a visual model to a presentation.
Answer:- True
v) Graphs are not good at showing changes and relationships.
Answer:- True
vi) Line graphs are good at showing changes over time.
Answer:- True
vii) Line graphs are also called histograms.
Answer:- False
viii) Pie Charts show how parts relate to the whole.
Answer:- True
ix) Pie Charts can make multiple comparisons.
Answer:- True
x) Bar Graphs and Line Graphs show trends.
Answer:- True
Que 2. What does this button mean <-> ?
Answer:- (b) Extend the content on the entire length of the cell.
Que 3. What type of chart is this ?
Answer:- (d) Pie Chart
Que 4. We need to bold the contents of Columns A and C, Row 14 and Cells D8 and E7. Is it possible to do all the formatting in one action?
Answer:- (a) Yes
Que 5. I have created a bar chart that represents sales for September, October and November. I add December figures to the end of the range that the chart was created from. Will the chart automatically update to include these figures?
Answer: Yes
Que 6. I want to print the gridlines on my Calc worksheet so that my data is easy to read. The first time I print my worksheet I choose File Print Ok from the Menu. Will the gridlines be printed?
Ans: No
Que 7. When you insert a column into a worksheet, it will always be inserted to the left of the column that contains the active cell.
Answer:- Yes
Que 8. What are the different components of chart? Explain.
Answer:- Different components of chart are :-
- X-axis: – This is the horizontal axis known as category axis.
- Y-axis: – This is the vertical axis known as value axis.
- Data series: – This is the set of values you want to plot in the chart.
- Chart area: – This is the total region surrounding the chart.
- Plot area: – This is the area of chart in which your data is plotted.
- Chart title: – This is the title of the chart.
- Axes titles: – These are the titles given to three axes i.e. X, Y and Z axes.
- Legend: – The legend helps to identify various plotted data series.
- Gridlines: – These are the horizontal and vertical lines in the plot area.
- Data label: – This label provides additional information about data.
Que 9. Define the following terms:
(i) Embedded Chart
(ii) Chart Sheet
Answer: (i) Embedded Chart: – Embedded Chart is a chart object placed inside a worksheet along with other data.
(ii) Chart Sheet: – Chart sheet is a sheet having only a chart and no other data. A chart sheet is beneficial when you want to view a chart separately from workbook data.
Que 10. The graph below has been produced using the data on the spreadsheet given just before i.e. Favourite Lunchtime Sandwiches spreadsheet. (refer to page number 227)
Answer:
i) A1:A10 and B1:B10
ii) Gives a better pictorial aspect.
iii) Favourite Food
iv) Total income in Rupees.
v) Column Chart
vi) Bar Chart
vii) Convey the same information as it is a circular statistical graph.
Que 11. Gina spright has collected data on how much each member of her class spends on magazines each month. She wishes to display this information in a graph.
(i) Name one suitable graph that Gina could use.
Answer: Column Graph.
(ii) Explain how Gina could use her computer to produce the graph.
Answer: -Using Open Office Calc (Insert Chart option after entering the data in the spreadsheet).
Session 5 Adding Graphics in Calc and Printing Worksheets
Que 1. What does it mean to ‘rotate’ an object or image?
Answer: – (a) The object is flipped so that it is displayed as a mirror image.
Que 2. What does the ‘crop’ tool do?
Answer: -(c) It allows you to select and use part of an image.
Que 3. When using a word processing package, you can change a shape by adding _____.
Answer: – (d) All of the above (Colour, A thicker Outline, A shadow)
Que 4. You have ‘grouped’ several shapes in order to make the image of a train. If you change your mind and want to edit just one of the objects, you can ..
Answer: – (a) ‘ungroup’ and select the objects, you can.
Que 5. When you add graphics to a word processing document, some images appear to go behind other images. To control this, you can _____ .
Answer: – (c) select ‘text wrapping’ from the drawing tools and choose the correct effect.
Que 6. What is a gallery? How is it useful?
Answer: The Gallery provides a convenient way to group reusable objects such as graphics and sounds
that can be inserted into documents.
Que 7. How do you view the gallery in Calc?
Answer: – To open the Gallery, choose Tools > Gallery, or click on the Gallery icon.
Que 8. Write steps to insert an object from Gallery.
Answer: – 1) Open a file browser window and locate the image you want to insert.
2) Drag the image into the Calc document and drop it where you want it to appear.
Que 9. Images can be managed through a menu and a toolbar. Name both of these.
Answer: -The Graphic Filter toolbar and view (View > Toolbars > Picture).
Que 10. How will you print multiple sheets from Calc?
Answer: -To print all of the sheets, go to File > Print and select Options.