Electronic Spreadsheet

 

Unit 4

Electronic Spreadsheet

1. Tick (/) the correct option.

 

a. The shortcut key to select all the cells in a worksheet is__________

i. Ctrl + C                                                                        ii. Shift + C

iii. Ctrl + A                                                                      iv. Shift + A

 

b. The shortcut to move to the first cell of a worksheet is__________

i. Home                                                                          ii. Shift + Home

iii. Alt + Home                                                              iv. Ctrl + Home

 

c. The shortcut to move to the last cell that contain data of a worksheet is__________

i. End                                                                               ii. Shift + End

iii. Ctrl + End                                                                 iv. Alt + End

 

d. The shortcut key to move to the first cell of the current row is __________

i. Home                                                                           ii. Shift + Home

iii. Alt + Home                                                               iv. Ctrl + Home

 

e. The shortcut key to move to the last cell of the current row is_____________

i. End                                                                               ii. Shift + End

ili. Ctrl + End                                                                  iv. Alt + End

 

f. The operation copies data from the source range to target range and erases it from the source range.

i. Cut and Copy                                                               ii. Cut, Copy and Paste

iii. Copy and Paste                                                       iv. Cut and Paste

 

g. If you enter 12+24 in a cell, Calc will display _______________.

i. 36                                                                                  ii. A12+ A24

iii. A12:A24                                                                    iv. 12+24

 

h. The formula in cell A2 is =B2+C3. On copying this formula to cell C2, the formula in cell C2 will be

i. =B2+C3                                                                       ii. =C3+B2

iii. =D2+E3                                                                     iv. =E3+D2

 

i. A ________shows how the total number is broken down into individual parts.

i. Area chart                                                                    ii. Chart

iii. Pie chart                                                                     iv. Bar chart

 

j. A graph that compares pieces of data using vertical stacks is a

i. Stacked chart                                                             ii. Area chart

iii. Pie chart                                                                     iv. Column chart

 

k. A _________is the key to a chart that tells what colour or pattern corresponds to a specific piece of data.

i. legend                                                                         ii. Lines

iii. Title                                                                            iv Subtitle

l. A______ compares pieces of data using horizontal stacks.

i. Pie chart                                                                     ii. Scatter chart

iii. Area Chart                                                               iv Embedded Chart

 

m. Scatter chart is also known as _______chart.

i. Area                                                                             ii. Column

iii. XY                                                                                iv. Bar

 

n. A _______ is a graph that is used for comparing changes in data over a period of time.

i. Line chart                                                                     ii. Area chart

iii. Scatter chart                                                             iv. Embedded chart

 

o. Small boxes around a part of a chart which indicate that it is selected are called________.

i. points                                                                          ii. Handles

iii. Border                                                                       iv. Dots

 

p. When the chart appears on the same worksheet as the data, it is called a(n)______________.

i. Floating chart                                                            ii. Chart Sheet

iii. Inside chart                                                             iv. Embedded chart

 

q. The_________ tells the name of a chart.

i. Plot area                                                                      ii. Title

iii. Axes title                                                                  iv. Chart area

 

2. State TRUE or FALSE.

 

a. By default, a workbook has 5 worksheets.       False

b. A highlighted cell is known as an active cell.  True

c. Pressing Shift + 11 enables us to insert a new blank worksheet. False

d. Formula bar shows the formula entered in a cell.  True

e. A worksheet cannot be renamed in Calc.  False

f. The formula in Calc should start with a '+' sign. False

g. The AutoSum feature counts the number of cells in a range. False

h. Function Library is present on the Insert tab.    True

i. It is difficult to modify a chart.  False

j. Trends over time are shown by a Bar chart. False

k. Chart sheet is the sheet in which the chart placed by default.  False

I. Row range is the number of cells spread across the rows and columns. False

m. The conditional formatting setting can be copied to other cells using the Format pointer tool. True

 

3. Fill in the blanks.

 

a. A Calc worksheet contains 1024 columns and 10,48,576 rows.

b. Text Orientation _helps to rotate direction of contents in a cell.

c. The extension of Calc file is .ods which automatically gets attached with the file name.

d. By default, Calc workbook opens with one worksheet(s).

e. Active cell in a worksheet is known as Selected or Activated Cell.

f. Data present in the cell can be customized or formatted using Format Cell   option.

g. Alignments can also be applied by using shortcut buttons from the Formatting Tool bar.

h. Formatting a cell includes changing the contents of cell with respect to appearance.

i. Arithmetic and Geometric progression can be created and inserted automatically using Auto Fill

   feature.

j. Chart is a visual graphical representation of data from a worksheet which is very useful for instant analysis of data and decision making.

 

4. Short Answer Type Question.

 

1. What is a cell and how is it referred in LibreOffice Calc?

 

Ans. A cell is the basic unit of a spreadsheet, formed at the intersection of a row and a column, where you can enter data like text, numbers, or formulas. In LibreOffice Calc, a cell is referred to by its unique address, which is a combination of its column letter and row number (e.g., F3).

 

2. What do you mean by a range of cells?

 

Ans. A cell range is a group of two or more selected cells in a spreadsheet, typically forming a rectangular block. It is defined by the addresses of the top-left and bottom-right cells, separated by a colon (e.g., A1:C5)

 

3. What is the difference between a worksheet and a workbook?

 

Ans. A worksheet is a single spreadsheet page, while a workbook is the entire file that can contain one or more worksheets.

 

4. How do you freeze some rows/columns in a worksheet?

 

Ans. To freeze rows or columns in a worksheet, go to the View tab and select Freeze Panes. To freeze the top row, click Freeze Top Row. To freeze the first column, click Freeze First Column.

 

5. Write the steps to create cell reference in Calc.

 

Ans. Select the cell where you want the formula to appear and type “=” sign.   Then click the cell to be referenced. The reference will be inserted automatically.  To add another part to the formula, type any operator and then click the next cell or range.  Press Enter when it is finished. 

 

6. How can you rename a Worksheet?

 

Ans. We can rename a worksheet by double-clicking the sheet's tab and typing a new name, right-clicking the tab and selecting "Rename," or using the "Sheet" menu and choosing "Rename Sheet".

 

7. Define Autolnput in Calc.

 

Ans. AutoInput in Calc is a feature that automatically suggests or completes text and numbers as you type, based on other entries in the same column.

 

8. What is relative and absolute cell address in the spreadsheet?

 

Ans. A relative cell address changes when copied or moved, automatically adjusting based on the new location, while an absolute cell address remains constant, no matter where it's copied.

 

9. How can you change the chart type after making a chart?

 

Ans. We can change the chart type by selecting the chart by double-clicking on it. Then do one of the following:

·         Choose Format>Chart type from menu bar.

·         Click the chart type icon on the Formatting toolbar.

·         Right-click on the chart and choose Chart Type.

 

5. Long Answer Type Questions.

 

1. What is formatting? How is it useful?

 

Ans. Formatting is the process of changing the visual appearance of cells and data to improve readability and usability. It is useful for making data easier to understand, identifying trends, and creating visually appealing spreadsheets through techniques like changing font styles, applying number formats such as currency, and using conditional formatting to automatically highlight data that meets certain criteria.

2. How will you format dates so that dates appear in a format similar to 26-January-2022?

 

Ans. We can format dates by taking the following steps: 

·         Select the cell(s) containing the dates you want to format.

·         Click Format tab present in menu bar.

·         Click on “Cells” In the "Format Cells" dialog box, select the Numbers tab.

·         In the "Category" list on the left, select Date.

·         In the Format code input box at the bottom, type DD-MMMM-YYYY.

·         Click OK to apply the new form

 

3. Explain the difference between a formula and a function with the help of an example.

Ans.  A Formula is a user-defined expression that performs calculations using values, cell references, and mathematical operators. It is created manually by the user.

Example:    =A1 + B1

      A function is a pre-defined command in the spreadsheet that performs a specific calculation automatically. It saves time and reduces errors.

      Example: =SUM(A1:B1)

 

4. Explain how would you delete columns and rows in spreadsheet?

 

Ans. To delete a row: Select the row and Right-click on the selected row. Then, choose “Delete Rows” from the context menu. We can alternatively delete the row by clicking on the “Sheet” menu from the menu bar and then clicking the “Delete Row” command or from the “Row” icon on the standard toolbar.

The row will be removed, and the rows below will shift upward.

 

To delete a column: Select the column and Right-click on the selected column. Then, choose “Delete Columns” from the context menu. We can alternatively delete the column by clicking on the “Sheet” menu from the menu bar and then clicking the “Delete Column” command or from the “Column” icon on the standard toolbar.

 

5. How can you define a new AutoFormat?

Ans. We can define a new AutoFormat by taking the following steps:

·         Select the range of the cell with the kind of formatting you want.

·         Click on “Format” menu on the menu bar.

·         Click on “AutoFormat Style”

·         Click on the Add button on the “AutoFormat” dialogue box that appears.

·         Give the suitable name to your AutoFormat style on “Add AutoFormat” dialogue box.

·         Click on “OK” button.

 

6. Write some common ways to enter formulas.

Ans. The following are the some common ways to enter formulas:

Common Ways to Enter Formulas in LibreOffice Calc

 

Using the Equal Sign (=)

Type = in a cell followed by your calculation.

Example: =A1 + B1

 

Using the Formula Bar

Click on the Formula Bar at the top of the sheet and type your formula there.

 

Using Built-in Functions

Type = and then the function name.

Example: =SUM(A1:A5)

Calc also suggests functions as you type.

 

Using the Function Wizard

Go to Insert → Function…

This opens a dialog box where you can search for functions, select one, and enter required arguments step-by-step.

 

7. Explain all types of Cell Referencing with examples.

 

1. Relative Cell Reference: A relative reference changes automatically when the formula is copied to another cell. It adjusts based on the new row or column position.

 

2. Absolute Cell Reference: An absolute reference remains fixed no matter where the formula is copied.

It uses the $ sign before the column and row: $A$1.

 

3. Mixed Cell Reference: A mixed reference is partly fixed and partly relative. Either the row or the column is fixed. There are two types of mixed references:

 

a) Fixed Column, Changing Row: $A1

 

b) Changing Column, Fixed Row: A$1

 

8. Explain the use of Fill tool on cells. How will you copy a formula using fill handle?

 

Ans. The Fill tool helps in quickly copying data or creating a series in adjacent cells by using the pattern. It saves time and ensures accuracy when working with large datasets.

 

We can copy a formula using fill handle in the following ways:

 

·         Enter the formula in the first cell (for example, =A1+B1 in cell C1).

·         Select the cell that contains the formula.

·         Notice a small square at the bottom-right corner of the selected cell—this is the Fill Handle.

·         Click and drag the fill handle down, up, left, or right over the cells where you want to copy the formula.

·         Release the mouse button, and the formula will be automatically copied to all selected cells, adjusting cell references as needed.

 

9. Write the steps to insert a chart in Calc.

Ans.  Steps to Insert a Chart in Calc

 

1. Select the Data:

      Highlight the cells that contain the data you want to represent in the chart.

 

2. Click on the Chart Icon:

     Go to the toolbar and click the Chart icon,

OR

3. Go to Insert → Chart.

     Choose the Chart Type (The Chart Wizard will open)

      Select the type of chart you want (Bar, Line, Pie, Column, Area, etc.).

4. Set Data Range and Series

     Check whether the data range is correct and choose how the data series should be arranged (by rows         or by columns).

 

5. Format the Chart

    Add titles, labels, legend, and customize other chart elements as needed.

 

6. Click Finish

      Your chart will be inserted into the spreadsheet