Exercise Solutions Available
Step-by-step answers for all textbook questions in this chapter
I. Choose the Best Answer (MCQs)
Q1. LibreOffice Calc is a
- (a) Word processor (b) Spreadsheet application (c) Presentation software (d) Database
Q2. The default file extension of a LibreOffice Calc file is
- (a) .doc (b) .odt (c) .ods (d) .odp
Q3. A cell in a spreadsheet is identified by
- (a) Row number only (b) Column letter only (c) Column letter and row number (d) Cell number
Q4. Which of the following is a valid formula in Calc?
- (a) A1+B1 (b) =A1+B1 (c) A1+B1= (d) +A1+B1
Q5. The function used to find the average of a range of cells is
- (a) =SUM() (b) =COUNT() (c) =AVERAGE() (d) =MAX()
Q6. In a spreadsheet, rows are labelled with
- (a) Letters (b) Numbers (c) Both letters and numbers (d) Symbols
Q7. How many sheets are there by default in a new LibreOffice Calc workbook?
- (a) 1 (b) 2 (c) 3 (d) 4
II. Fill in the Blanks
1. The intersection of a row and a column in a spreadsheet is called a cell. 2. The default file extension of a LibreOffice Calc file is .ods. 3. A formula in Calc must begin with the = (equals) sign. 4. The function used to add a range of numbers is SUM. 5. The function used to find the largest value in a range is MAX. 6. The function used to find the smallest value in a range is MIN. 7. The function used to count the number of cells containing numbers is COUNT. 8. Rows in a spreadsheet are labelled with numbers (1, 2, 3...). 9. Columns in a spreadsheet are labelled with letters (A, B, C...). 10. The active cell is highlighted with a border/rectangle.
III. True or False
IV. Match the Following
| Column I | Column II | Answer |
|---|---|---|
| =SUM(A1:A5) | Adds values from A1 to A5 | =SUM(A1:A5) — Adds values from A1 to A5 |
| =AVERAGE(B1:B10) | Finds mean of values from B1 to B10 | =AVERAGE(B1:B10) — Finds mean of values from B1 to B10 |
| =MAX(C1:C20) | Finds largest value from C1 to C20 | =MAX(C1:C20) — Finds largest value from C1 to C20 |
| =MIN(D1:D15) | Finds smallest value from D1 to D15 | =MIN(D1:D15) — Finds smallest value from D1 to D15 |
| =COUNT(E1:E8) | Counts numeric cells from E1 to E8 | =COUNT(E1:E8) — Counts numeric cells from E1 to E8 |
| .ods | LibreOffice Calc file extension | .ods — LibreOffice Calc file extension |
V. Short Answer Questions
Q1. What is LibreOffice Calc? What are its uses? Answer: LibreOffice Calc is a free, open-source spreadsheet application that is part of the LibreOffice office suite. It is used for: 1. Organising data in rows and columns 2. Performing mathematical and statistical calculations 3. Creating charts and graphs from data 4. Analysing and managing data (sorting, filtering) 5. Creating budgets, invoices, and financial reports 6. Formatting and presenting data professionally
Q2. What is a cell? How is it identified? Answer: A cell is the intersection of a row and a column in a spreadsheet. It is the basic unit where data is entered.
A cell is identified by its cell reference, which consists of the column letter followed by the row number. For example:
- A1 refers to the cell in column A, row 1
- C5 refers to the cell in column C, row 5
- Z100 refers to the cell in column Z, row 100
Q3. What is the difference between a formula and a function? Answer:
| Property | Formula | Function |
|---|---|---|
| Definition | User-defined expression for calculations | Predefined built-in operation |
| Example | =A1+B1+C1 | =SUM(A1:C1) |
| Complexity | Can be simple or complex | Simplifies common calculations |
| Flexibility | Custom calculations | Standard operations |
| Syntax | =expression | =FUNCTION_NAME(range) |
Q4. Write the syntax and use of the following functions: SUM, AVERAGE, MAX, MIN. Answer: 1. =SUM(range): Adds all the numeric values in the specified range. Example: =SUM(A1:A10) adds all values from cells A1 to A10.
2. =AVERAGE(range): Calculates the arithmetic mean of the values in the specified range. Example: =AVERAGE(B1:B5) calculates the average of values from B1 to B5.
3. =MAX(range): Returns the largest value in the specified range. Example: =MAX(C1:C20) finds the maximum value from C1 to C20.
4. =MIN(range): Returns the smallest value in the specified range. Example: =MIN(D1:D10) finds the minimum value from D1 to D10.
Q5. How do you create a chart in LibreOffice Calc? Describe the steps. Answer: Steps to create a chart in LibreOffice Calc: 1. Select the data range you want to include in the chart (including labels). 2. Click on Insert → Chart from the menu bar (or click the Chart icon on the toolbar). 3. The Chart Wizard opens. Select the chart type (Bar, Line, Pie, etc.) and click Next. 4. Verify the data range and select whether the data is in rows or columns. Click Next. 5. Enter the chart title, X-axis title, and Y-axis title. Click Next. 6. Review the chart preview and click Finish. 7. The chart is inserted into the spreadsheet and can be moved, resized, or formatted as needed.
Q6. What is the difference between absolute and relative cell referencing? Answer:
- Relative reference: Changes when a formula is copied to another cell. Example: A1 — if a formula =A1+B1 is copied from row 1 to row 2, it becomes =A2+B2.
- Absolute reference: Does not change when a formula is copied. It is written with $ signs. Example: $A$1 — if a formula =$A$1+B1 is copied from row 1 to row 2, $A$1 stays the same but B1 changes to B2.
Q7. What are the different types of charts available in Calc? Answer: 1. Bar chart: Compares values across categories using rectangular bars. 2. Column chart: Similar to bar chart but with vertical bars. 3. Line chart: Shows trends over time by connecting data points with lines. 4. Pie chart: Shows the proportion of each value as a slice of a circle. 5. Scatter chart: Plots data points to show relationships between two variables. 6. Area chart: Like a line chart but with the area below the line filled in.
VI. Detailed Answer Questions
Q1. Explain the main features of the LibreOffice Calc interface. Answer: The LibreOffice Calc interface consists of the following components:
1. Title Bar: Displays the name of the current workbook and the application name (LibreOffice Calc).
2. Menu Bar: Contains menus like File, Edit, View, Insert, Format, Tools, Data, Window, and Help. Each menu provides access to various commands.
3. Standard Toolbar: Contains icons for common tasks like New, Open, Save, Print, Copy, Paste, Undo, and Redo.
4. Formatting Toolbar: Contains icons for formatting cells — font, font size, bold, italic, alignment, borders, background colour, and number format.
5. Formula Bar: Displays the contents of the active cell. It has three parts: the Name Box (shows cell reference), the Function Wizard button, and the Input Line (shows/edit cell content or formula).
6. Worksheet Area: The main grid area with cells organised in rows (numbered) and columns (lettered). This is where data is entered and calculations are performed.
7. Sheet Tabs: At the bottom, tabs show Sheet1, Sheet2, Sheet3. Click a tab to switch between sheets. Right-click a tab to rename, insert, or delete sheets.
8. Status Bar: At the bottom, it shows information about the current state — the current cell, sum of selected cells, zoom slider, and page style.
9. Scroll Bars: Vertical and horizontal scroll bars to navigate through the spreadsheet when data extends beyond the visible area.
Q2. Explain how to enter data, formulas, and functions in LibreOffice Calc with examples. Answer: Entering Data: 1. Click on the cell where you want to enter data. 2. Type the data (number, text, or date) directly in the cell or in the Input Line of the Formula Bar. 3. Press Enter to confirm or Esc to cancel. 4. Use Tab to move to the next cell on the right, or Enter to move to the cell below.
Examples: Type "Student Name" in A1, "Marks" in B1, "85" in B2.
Entering Formulas: 1. Click on the cell where you want the result. 2. Type an equals sign (=) followed by the formula. 3. Press Enter to calculate the result.
Examples:
- =A1+B1 (adds the values in A1 and B1)
- =C2*10 (multiplies the value in C2 by 10)
- =(A1+B1+C1)/3 (calculates the average of three cells)
Entering Functions: 1. Click on the cell where you want the result. 2. Type = followed by the function name and arguments in parentheses. 3. Press Enter to calculate.
Examples:
- =SUM(A1:A10) — adds all values from A1 to A10
- =AVERAGE(B1:B20) — calculates the average of values from B1 to B20
- =MAX(C1:C5) — finds the largest value from C1 to C5
- =MIN(D1:D8) — finds the smallest value from D1 to D8
- =COUNT(E1:E15) — counts the number of cells with numeric values from E1 to E15
Editing Data: 1. Double-click on the cell to edit its contents, or 2. Click the cell and edit in the Formula Bar's Input Line. 3. Press Enter to confirm changes.
Q3. Explain formatting of cells in LibreOffice Calc. Answer: Cell formatting improves the appearance and readability of data in a spreadsheet.
Steps to format cells: 1. Select the cell(s) you want to format. 2. Right-click and choose "Format Cells" (or press Ctrl+1). 3. The "Format Cells" dialog box appears with several tabs:
Number Tab:
- Number: General, Number (with decimal places), Currency, Date, Time, Percentage, Scientific, Fraction, Boolean, Text
- Example: Format 0.75 as 75% using Percentage format
Font Tab:
- Choose font family (e.g., Arial, Times New Roman), font style (Regular, Bold, Italic), and font size
- Set font colour
Alignment Tab:
- Horizontal alignment: Left, Center, Right, Justified
- Vertical alignment: Top, Middle, Bottom
- Text wrapping, rotation
Borders Tab:
- Choose border style, colour, and position (top, bottom, left, right)
- Add diagonal lines
Background Tab:
- Choose a background colour for the selected cells
Key formatting shortcuts:
- Bold: Ctrl+B
- Italic: Ctrl+I
- Underline: Ctrl+U
- Align Left: Ctrl+L
- Center: Ctrl+E
- Align Right: Ctrl+R
Q4. Explain the steps to sort and filter data in LibreOffice Calc. Answer: Sorting Data: Sorting arranges data in ascending or descending order based on one or more columns.
Steps: 1. Select the data range you want to sort (including column headers). 2. Click on Data → Sort from the menu bar. 3. In the Sort dialog box:
4. Check "Range contains column labels" if your first row has headers. 5. Click OK to sort the data.
- Choose the column to sort by (Sort Key 1).
- Select Ascending (A→Z, 1→9) or Descending (Z→A, 9→1).
- Optionally, add Sort Key 2 and Sort Key 3 for multi-level sorting.
Example: Sort a student marks list by marks in descending order to find the toppers.
Filtering Data: Filtering displays only the rows that meet specific criteria, hiding the rest.
Steps: 1. Select the data range (including headers). 2. Click on Data → AutoFilter (or Data → Filter → Standard Filter). 3. Dropdown arrows appear in the header cells. 4. Click the dropdown arrow in the column you want to filter. 5. Select the condition (e.g., "Marks > 80" or "City = Chennai"). 6. Only the rows that match the condition are displayed; others are hidden. 7. To remove the filter, click Data → Filter → Remove Filter.
Example: From a class list, filter to show only students who scored above 80 in Science.
Common Mistakes
- Forgetting the = sign in formulas: Every formula in Calc must start with =. Without it, the entry is treated as text.
- Confusing rows and columns: Rows are horizontal and numbered (1, 2, 3...). Columns are vertical and lettered (A, B, C...).
- Using wrong cell references: Double-check cell references in formulas. A1 is not the same as A10.
- Confusing SUM and AVERAGE: SUM adds all values; AVERAGE divides the sum by the count. They give different results.
- Forgetting parentheses in functions: Functions need parentheses around the range. =SUM A1:A5 is wrong; it must be =SUM(A1:A5).
- Confusing relative and absolute references: Use $ for absolute references ($A$1) when you don't want the reference to change when copying formulas.
Exam Checklist
- [ ] Can you define a spreadsheet and explain the use of LibreOffice Calc?
- [ ] Can you identify and explain the parts of the Calc interface?
- [ ] Can you define cell, cell reference, and active cell?
- [ ] Can you write formulas and functions with correct syntax?
- [ ] Can you use SUM, AVERAGE, MAX, MIN, and COUNT functions?
- [ ] Can you distinguish between relative and absolute cell references?
- [ ] Can you create different types of charts in Calc?
- [ ] Can you format cells (number, font, alignment, borders, background)?
- [ ] Can you sort and filter data in a spreadsheet?
- [ ] Can you explain the steps to enter, edit, and format data?