Electronic Spreadsheet Class 9 Notes PDF

# Electronic Spreadsheet Class 9 Notes ## Spreadsheet * A spreadsheet package is an application software that does analysis, calculations, comparisons and displays information in required format and in charts and graphs. ## Features of Spreadsheets * Formulas(mathematical expressions) and functi...

# Electronic Spreadsheet Class 9 Notes ## Spreadsheet * A spreadsheet package is an application software that does analysis, calculations, comparisons and displays information in required format and in charts and graphs. ## Features of Spreadsheets * Formulas(mathematical expressions) and functions(built-in formulas): Formulas help in performing simple and complex calculations and functions help in calculating percentage, interest, average etc. * Formatting features: for improving the appearance of data. * Search and Replace: fast searching and instant replacing of data. * Auto fill: It helps in entering data automatically. * Charts: Data can be viewed graphically as charts in order to understand, analyse and compare data in an effective manner. * Sorting and Filtering data: Data can be arranged(sorted) in ascending or descending order. Also, some specific data can be retrieved using filter feature. ## Some Popular Spreadsheet Software * Microsoft Excel * LibreOffice Calc * OpenOffice Calc * Google Sheets * Gnumeric * WikiCalc * Apple Inc. Numbers ## 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 ## Components of LibreOffice Calc * Title Bar * Menu Bar * Standard Toolbar * Formatting toolbar * Formula bar * Active cell * Active cell Reference (Name box) * Row Headers * Column Headers * Sheet tabs * Status bar ## Some Terms Related to Spreadsheet * **Workbook:** It is a collection of worksheets in a single file. The MS Excel file/ LibreOffice Calc file is referred to as a workbook, in which we work and store our data. By default its name is Book1 in MS-Excel and Untitled 1 in LibreOffice Calc. * **Worksheet:** A grid like area, made up of rows and columns where you enter data and work with it. * **Rows (1,2,3...)** run horizontally from left to right and **Columns (A,B,C...)** run vertically from top to bottom. * **By default**, an Excel Workbook contains 3 sheets and Calc contains 1 sheet. The sheets can be added or deleted as per the requirement. * **Cell:** It is the intersection of rows and columns. It can store data such as text, numbers, formulas, functions etc. The selected cell is called an active cell. ## Range of Cells * Range refers to a series of values between two other values. So, we can say cell range is a collection of cells within a row or column. For e.g the range between 1 and 5 includes 2, 3 and 4 as well. * Column range: It refers to the range of cells spread across a column. In this type of range, the column is constant. e.g- B1:B5. * Row range: It refers to the range of cells spread across a row. In this type of range, the row number is constant.e.g- B5:F5. * Row and Column range: it is the number of cells spread across the row and columns.e.g- B3:F10. ## Rows and columns: * The sheet is divided into vertical columns and horizontal rows. * Each sheet can have a maximum of 1,048,576 (2^20) rows and 1024 (2^10) 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. ## Entering data * Text * Values * formula ## Key or Key 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 ## 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) | | |Function | Syntax | Use | |---|---|---| | SUM | =SUM(Number 1, 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 cells. | | 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(Number 1, Number2,.....) | Counts the number of cells within a range of cells. | ## 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 ## (a) Relative Referencing * When you drag any formula in any row or column in any direction, the formula gets copied in the new cell with the relative reference. * Almost all spreadsheet applications use relative referencing by default. ## Absolute referencing * In Absolute referencing, a **$ symbol** is used before the column name as well as row number to make it constant. * For example, $C$12, $D$5, etc. ## Mixed Referencing * In Mixed Referencing, the **$ sign** is used before row number or column name to make it constant. * The mixed referencing in which one cell address name is variable and one cell address is constant. ## Electronic Spreadsheet Important Questions 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. 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 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. 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. 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. 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. 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 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 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