Information Technology
Information Technology
Class IX
Spreadsheet application is tool which is used to perform all kinds of calculations easily and accurately. LibreOffice Calc is used to perform the following activities accurately and efficiently.
Tabulation of data
Simple mathematical calculations
Complex calculations using formula and functions
Arranging data in ascending and descending order (sorting)
Filtering the required data
Check the validity of data
Protection of data using passwords
Saving for future use
Electronic Spreadsheet Class 9 Notes
Spreadsheet apps come in many different forms and are created by different software companies. The spreadsheet programme in the LibreOffice suite is called LibreOffice Calc.
Starting LibreOffice Calc
To start the LibreOffice Calc in Windows
In Windows, find the shortcut of LibreOffice on Start menu or on the desktop. Double click the shortcut to open LibreOffice.
To start LibreOffice Calc in Linux
In Ubuntu Linux, find the Calc icon on application launcher or search it by clicking on “Show Applications”
Brief explanation about the parts of LibreOffice Calc
Title bar – The Title bar, located at the top, shows the name of the current spreadsheet.
Menu bar – Menu bar is located just below the Title bar. It contains the menus with commands for various tasks. for example File, Edit, View, Insert, Format, Style etc.
Toolbars – The Calc opens with the Standard and Formatting toolbars at the top of the workspace by default. There is three different type of toolbars available in Calc. Standard toolbar, Formatting toolbar, Formula toolbar
Worksheet – The worksheet in Calc is also referred to as spreadsheet. The spreadsheet can have many sheets. Each sheet can have many individual cells arranged in rows and columns. The sheet tab shows its default
name as Sheet1, Sheet2, Sheet3, ….
Rows and columns – The sheet is divided into vertical columns and horizontal rows. Each sheet can have
a maximum of 1,048,576 (220) rows and 1024 (210) columns.
Cell and cell address – The intersection of a row and column is called a cell. It is the basic element of a spreadsheet. It holds data, such as text, numbers, formulas and so on. A cell address is denoted by its column (letter) and row number. For example, D4, E9, Z89 are the valid example of cell address.
Active cell – In a spreadsheet, cell is the place where we enter the data. Before entering any data in the cell, it has to be first selected by placing a cursor on it. When we position the mouse cursor on a cell, it gets selected, and is ready to take data from the user. This selected or activated cell is called as active cell.
Key or
Key Combination
Result of Key or Combination
Arrow keys (←↑→↓)
Move a single cell in arrow direction
Ctrl + Arrow Keys
Moves the cell to the end of the data range in a particular direction
Home
Moves to column A along the row where the active cell is
Ctrl + Home
Moves the cell to A1 position
Ctrl + End
Moves to bottom right cell of the data range
Page Up
Moves the worksheet one screen up
Page Down
Moves the worksheet one screen down
Electronic Spreadsheet Class 9 Notes
Range of cells – A block of adjacent cells in a worksheet which is highlighted or selected is called a range of cells. Observe the worksheets below.
Entering data
It is necessary to select the cell in a spreadsheet before entering any data, practically in the cell. The pointer can be placed inside a cell to select that cell. The label, values, or formula can be the data that has to be entered.
Label – Label is the any text entered by using a keyboard. It may combine with letters, numbers, and special symbols.
Values – The numerical data consisting of only numbers are called values. By default values are right aligned. There are various forms of values, such as integer, decimal and so on.
Formulae – Any expressions that begins with an equals ‘=’ is treated as formula. In the expression, the ‘=’ followed by values, cell address and functions are called as formula.
Electronic Spreadsheet Class 9 Notes
Calc can add, subtract, divide, multiply and much more. LibreOffice Calc uses standard operators for formulae, such as a plus(+), minus(-), multiplication (*), a division (/) for arithmetic operation.
Note: The order of evaluation can be changed by using brackets. (The expressions within the brackets are evaluated first).
Mathematical Operators
Operator precedence
+ addition
First ( )
– subtraction
Second ^
* multiplication
Third /, *
/ division
Fourth + , –
^ exponentiation (power
Electronic Spreadsheet Class 9 Notes
Formulae with cell addresses and operators
The formula’s outputs are updated in line with any changes to the values of the affected cells. Consider adding the numbers in cells A1 and A2 to obtain the sum in cell A3. The right addition will appear in cell A3 if the pointer is placed there and the values are simply added as =5+8. However, if we modify the numbers in cells A1 and A2 to 6 and 7, respectively, we must once more enter the addition in cell A3 by writing =6+7. However, if we enter the general equation in cell A3 as =A1+A2,
Use of functions to do calculations
Function
Syntax
Use
SUM
=SUM(Number1,Number2,…..)
Adds the values contained in a range of cells.
AVERAGE
=AVERAGE(Number1,Number2,….)
Finds out the average of the values contained in a range of cell
MAX
=MAX(Number1,Number2,……)
Finds out the largest value contained in a range of cells.
MIN
=MIN(Number1,Number2,……)
Finds out the smallest value contained in a range of cells.
COUNT
=COUNT(Number1,Number2,…..)
Counts the number of cells within a range of cells.
Electronic Spreadsheet Class 9 Notes
Lets identify the various ways in which a function can be used –
Let us identify the various ways in which a function can be used. Based on the sample data given above.
Sum Function
Formula
Meaning
Result
=SUM (A1,B1,C1)
The sum of cells A1,B1 and C1
17
=SUM(A1:C1)
The sum of cells in the range of cells from A1 to C1
17
=SUM(A1:C1,B2)
The sum of cells in the range of cells from A1 to C1 and B2
24
=SUM(B1:C2)
The sum of cells in the range of cells from B1 to C2
23
=SUM(A1:A3,C1:C3)
The sum of cells in the range of cells from A1 to A3 and C1 to C3
37
Electronic Spreadsheet Class 9 Notes
Average Function
Formula
Meaning
Result
=AVERAGE (A1,B1,C1)
The average of cells A1, B1 and C1
5.66
=AVERAGE (A1:C1)
The average of cells in the range of cells from A1 to C1
5.66
=AVERAGE (A1:C1,B2)
The average of cells in the range of cells from A1 to C1 and B2
6.33
=AVERAGE (B1:C2)
The average of cells in the range of cells from B1 to C2
5.75
=AVERAGE (A1:A3,C1:C3)
The average of cells in the range of cells from A1 to A3 and C1to C3
6.16
Electronic Spreadsheet Class 9 Notes
MAX Function
Formula
Details
Result
=MAX(A1,B2,C1)
Finds out the largest value among cells A1,B2 and C1
7
=MAX(A2:C2,B3)
Finds out the largest value among the range of cells from A2 to C2 and the cell B3
8
=MAX(A1:C1)
Finds out the largest value among the range of cells from A1 to C1
7
=MAX(A1,B1:C2)
Finds out the largest value among the range of cells from A1 to B1 and the cell C2
7
Electronic Spreadsheet Class 9 Notes
Min Function
Formula
Details
Result
=MIN(A1,B2,C1)
Finds out the smallest value among cells A1, B2 and C1
5
=MIN(A2:C2,B3)
Finds out the smallest value among the range of cells from A2 to C2 and the cell B3
4
=MIN(A1:C1)
Finds out the smallest value among the range of cells from A1 to C1
5
=MIN(A1,B1:C2)
Finds out the smallest value among the range of cells from B1 to C2 and the cell A1
4
Electronic Spreadsheet Class 9 Notes
Count Function
Formula
Details
Result
=COUNT(A1,B1)
Counts the number of cells that contain numbers among cells A1, B1
2
=COUNT(A1:C1)
Counts the number of cells that contain numbers in the range of cells from A1 to C1
3
=COUNT(A1:A4)
Counts the number of cells that contain numbers in the range of cells from A1 to A4
3
=COUNT(A1:C1,B2)
Counts the number of cells that contain numbers in the range of cells from A1 to C1 and B2
4
=COUNT(B1:C3)
Counts the number of cells that contain numbers in the range of cells from B1 to C3
6
=COUNT(A1:A3,C1:C3)
Counts the number of cells that contain numbers in the range of cells from A1 to A3 and the range of cells from C1 to C3
6
Electronic Spreadsheet Class 9 Notes
Speeding up data entry
Calc also comes with a number of useful tools for automating input, particularly for repeated tasks. They consist of the selection lists, the fill tool, and the capacity to enter data onto many sheets of the same document.
Using the fill handle
The Calc Fill Handle tool is used to fill the subsequent cells with the subsequent predefined value till you drag it. For instance, to fill in the numbers 1, 2, 3, or the days of the week Monday, Tuesday,…., or the name of the month Jan, Feb,…., enter the first two values, select them, then drag them to the following cells until you want to continue the series in succession.
For number series – Type the numbers 1, 2 in two consecutive cells and select them using a mouse.
Copying a formula – If you wish to apply the same formula to the number of cells in the rows or columns, you need not enter the formula again and again in each cell. A formula can be copied.
Referencing is the way to refer the formula or function from one cell to the next cell along the row or column.
There are three types of referencing.
• Relative referencing
• Mixed referencing
• Absolute referencing
Relative Referencing
Any formula that is moved in any row or column in any direction copies itself into the new cell with the appropriate reference. Nearly all spreadsheet programmes by default employ relative referencing.
Mixed Referencing
The $ symbol is used in Mixed Referencing to make a row number or column name constant. In Mixed Reference makes either the column or the row constant. When a column or row is made constant, the column name or row number do not change when the formula is copied to another cell (s).
Absolute Referencing
When using absolute referencing, the column name and row number are made constant in all formulas by using the $ sign before them. As an illustration, C$12, D$5, etc. In this instance, the cell name does not change address of which direction you drag your formula. In upper classes, this kind of referring is employed.
When data is represented as numbers, it is difficult to grasp, compare, analyse, or convey the data. However, statistics are more effectively communicated when it is shown as a chart. Let’s examine the use of spreadsheet programmes in this context.
The many chart kinds are listed below.
Types
Purpose
Column Chart
Comparing classes of data items in group. Group comparison
Bar Chart
Comparing classes of data items in group. Group comparison
Line Chart
Comparing classes of data items in group. Group comparison
Pie Chart
Comparing classes of data items as percentage.
XY Scatter Chart
Comparing data in pairs
Electronic Spreadsheet Class 9 Notes
Let us use the worksheet below to create a column chart
• Follow the steps given below to create charts.
• Select the range of data (A1:F7)
• Insert → Chart
• Select the type of chart
.
Name of document
Name of document
Name of document
Name of document
Electronic Spreadsheet Class 9 Questions and Answers
1. What is the difference between spreadsheet, worksheet and sheet?
Answer – Difference between Spreadsheet, Worksheet and Sheet are –
a. Spreadsheet – Users can use spreadsheet software to organize data in rows and columns and conduct calculations on it. A worksheet is made up of all of these rows and columns.
b. Worksheet – In LibreOffice calc by default 3 worksheet is given. Worksheets are similar to notebooks and contain numbers, characters and special characters.
c. Sheet – A cell is the point where a column and a row meet.
2. What do you call the document created in a spreadsheet application?
Answer – A spreadsheet is a computer program that allows you to organize, analyze, and save data in a tabular format.
Users of spreadsheets can change any stored value and see how it affects calculated numbers. Because various scenarios can be quickly evaluated without manual recalculation, the spreadsheet is ideal for “what-if” analysis.
Modern spreadsheet software has numerous interconnected sheets and can show data as text, numbers, or graphs.
Electronic Spreadsheet Class 9 Questions and Answers
3. What are the steps to create a new spreadsheet?
Answer – To create a new spreadsheet you can follow the following steps
Step 1 – Click on Start Menu
Step 2 – Click on LibreOffice 5.4 Menu
Step 3 – Select LibreOffice Calc
4. What is the default name of the worksheet? How can it be renamed?
Answer – By default the names of worksheets are Sheet1, Sheet2, Sheet3, you may easily rename the sheet using double click or using right click. You can also add multiple sheets in Spreadsheet.
5. Write the steps to insert and delete the worksheet in Calc.
Answer – To insert and delete worksheet in Calc –
Insert Sheet
Click on Insert tab to insert sheet in Spreadsheet
Rename the sheet
Delete Sheet
Right click on the sheet and select delete option
Electronic Spreadsheet Class 9 Questions and Answers
6. What is an active cell? How to delete the contents of an active cell?
Answer – When you start typing the cell begins active, Only the active cell can be used to enter data.
How to delete Cell –
If you wish to delete the contents of an active cell, select it and right-click on it, then select delete cell.
7. What is relative and absolute cell address in the spreadsheet?
Answer – Difference between relative and absolute are –
a. Relative Reference – In a spreadsheet, the default cell reference is relative. It’s only a combination of column name and row number, with no dollar ($) sign in between. When you copy a formula from one cell to another, the relative reference changes.
b. Absolute Reference – The dollar ($) sign is put before the column name and row number in an absolute reference cell. When copying a formula from one cell to another, the absolute reference will not change.
Electronic Spreadsheet Class 9 Questions and Answers
8. Explain any two operations performed on data in a spreadsheet.
Answer – The two major operations done on data in a spreadsheet are formulas and functions.
Formula is a software application that is used to keep organized and analyze data values in tabular form.
In the spreadsheet, predefined formulas such as sum count average Max and mine are used.
9. How do formulae work in a spreadsheet?
Answer – A formula is a statistical phrase that works with values in a set of cells. Even if the result is an error, these formulas return a result. You can use Spreadsheet formula to execute operations like addition, subtraction, multiplication, and division.
For example, =A1+A2+A3 calculates the sum of the values in cells A1 to A3.
Electronic Spreadsheet Class 9 Questions and Answers
10. Can you include more than one mathematical operator in a formula?
Answer – Yes, a formula can contain multiple mathematical operators. For example =(a1+a2)*2
11. How to make visible the desired toolbar a spreadsheet?
Answer – A “spreadsheet” is a software application that allows you to store data in a tabular format. A toolbar is a group of images/icons that acts as a shortcut to the different functions.
In a spreadsheet, press the “Ctrl+F1” key to make and hide the relevant toolbar commands.
Electronic Spreadsheet Class 9 Questions and Answers
12. Give the syntax and example of any three mathematical functions in the spreadsheet.
Answer – The following is the syntax and an example of any three mathematical functions in a spreadsheet:
a. SUM() – This is a function that adds all of the values in a range of cells….
b. COUNT() – is a function that counts the number of cells which have a number.
c. AVERAGE() – is a function that calculates the average of a set of cells.
Electronic Spreadsheet Class 9 Questions and Answers
13. Give the syntax and example of any three statistical functions in the spreadsheet.
Answer – The three statistical functions in a spreadsheet.
=count() & =counta()
=average()
=Countblank()
Electronic Spreadsheet Class 9 Questions and Answers
14. Give the syntax and example of any three date and time functions in the spreadsheet.
Answer – Excel’s ability to store and regulate times and dates is one of its most significant features.
a. =now() – Use the NOW function to get the current date and time.
b. =date() – There are three arguments to the DATE function: year, month, and day.
c. =time() – Use the TIME function to add a number of hours, minutes, and/or seconds.
15. Give the syntax and example of any three logical functions in a spreadsheet.
Answer – There are three types of logical function in spreadsheet
a. AND – The logical AND function returns TRUE if all conditions are true, and FALSE if any condition is false.
Example – AND(A1>10, A1<100)
b. OR – When one of the terms is TRUE, the logical OR function returns TRUE; otherwise, it returns FALSE if all of the conditions are false.
Example – OR(A1>10, B1>100)
c. NOT – The logical NOT function converts true to false and false to true values.
Example – NOT(A1>10, A2>100)
Electronic Spreadsheet Class 9 Questions and Answers
16. Give the syntax and example of any three string functions in spreadsheet.
Answer – The three string functions are –
a. Len() – The len function in Excel allows you to determine the length of a string, or the number of characters in it.
b. MID() – In Excel, the MID() function is used to extract the character from the middle of a string.
c. Left() – Excel’s left function extracts the left character from a string.
Electronic Spreadsheet Class 9 Questions and Answers
17. Explain the advantages of drawing a chart in Calc.
Answer – The following are some of the benefits of drawing a chart in Calc:
You can represent the data easily
It shows the data in a proper manner
It is a powerful tool for every organization
Electronic Spreadsheet Class 9 Questions and Answers
18. Explain in one line each the various types of charts.
Answer –
a. Line Chart – The Line Chart is very useful for depicting patterns. The vertical axis (Y-axis) of a Line Chart always shows numeric values, while the horizontal axis (X-axis) shows time or another category.
b. Pie Chart – Only one series of data can be displayed in a Pie Chart. A data series is a set of numbers that may be plotted in a row or column.
c. Column Chart – The Column Chart is an excellent method of comparing one or more sets of data points.
d. Bar Chart – The Bar Chart is similar to a Column Chart. The numeric numbers are displayed on the horizontal axis of a Bar Chart.
e. Area Chart – Area Charts are similar to Line Charts, Area Chart, are usually used to display patterns over time or in other categories.
f. Scatter Chart – A Scatter Chart’s goal is to compare the values of two series across time or in another category.
19. Write the steps to insert a chart in Calc.
Answer – To insert Chart in Calc
Step 1 – Click on Insert tab
Step 2 – Select Chart
Step 3 – Enter your data in the chart
Electronic Spreadsheet Class 9 Questions and Answers
20. Name and explain any five components of a chart in a spreadsheet package.
Answer – The five components of a chart in a spreadsheet package are as follows:
Chart area
Chart Title
Axis Title
Plot Area
Axis Values