Teachers and Examiners (CBSESkillEduction) collaborated to create the Electronic Spreadsheet Class 9 Notes. All the important Information are taken from the NCERT Textbook Information Technology (402) class 9.
Electronic Spreadsheet Class 9 Notes
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
Getting Started with Libreoffice Calc
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 |
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
Mathematical operators used in formulae
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 |
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. |
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 |
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 |
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 |
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 |
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 |
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
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.
Creation of Charts Using Spreadsheets
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 |
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
Employability skills Class 9 Notes
- Unit 1 – Communication Skills Class 9 Notes
- Unit 2 – Self-Management Skills Class 9 Notes
- Unit 3 – Basic ICT Skills Class 9 Notes
- Unit 4 – Entrepreneurial Skills Class 9 Notes
- Unit 5 – Green Skills Class 9 Notes
Employability skills Class 9 MCQ
- Unit 1 – Communication Skills Class 9 MCQ
- Unit 2 – Self-Management Skills Class 9 MCQ
- Unit 3 – Basic ICT Skills Class 9 MCQ
- Unit 4 – Entrepreneurial Skillls Class 9 MCQ
- Unit 5 – Green Skills Class 9 MCQ
Employability skills Class 9 Questions and Answers
- Unit 1 – Communication Skills Class 9 Questionns and Answers
- Unit 2 – Self-Management Skills Class 9 Questions and Answers
- Unit 3 – Basic ICT Skills Class 9 Questions and Answers
- Unit 4 – Entrepreneurial Skills Class 9 Questions and Answers
- Unit 5 – Green Skills Class 9 Questions and Answers
Information Technology Class 9 Notes
- Unit 1 – Introduction to IT-ITeS Industry Class 9 Notes
- Unit 2 – Data Entry and Keyboarding Skills Class 9 Notes
- Unit 3 – Digital Documentation Class 9 Notes
- Unit 4 – Electronic Spreadsheet Class 9 Notes
- Unit 5 – Digital Presentation Class 9 Notes
Information Technology Class 9 MCQ
- Unit 1 – Introduction to IT-ITeS Industry Class 9 MCQ
- Unit 2 – Data Entry and Keyboarding Skills Class 9 MCQ
- Unit 3 – Digital Documentation Class 9 MCQ
- Unit 4 – Electronic Spreadsheet Class 9 MCQ
- Unit 5 – Digital Presentation Class 9 MCQ
Information Technology Class 9 Questions and Answers
- Unit 1 – Introduction to IT-ITeS Industry Class 9 Questions and Answers
- Unit 2 – Data Entry and Keyboarding Skills Class 9 Questions and Answers
- Unit 3 – Digital Documentation Class 9 Questions and Answers
- Unit 4 – Electronic Spreadsheet Class 9 Questions and Answers
- Unit 5 – Digital Presentation Class 9 Questions and Answers