Electronic Spreadsheet using LibreOffice Calc (Advanced)

Electronic Spreadsheet using LibreOffice Calc (Advanced)

Class 10 | CBSE IT (Code 402) | Part B – Unit 2 | Session 2026–27


Introduction

LibreOffice Calc is a free, open-source spreadsheet application that is part of the LibreOffice suite. In Part B – Unit 2 (Electronic Spreadsheet using LibreOffice Calc – Advanced), you will learn advanced features for data analysis, calculations, visualization, and automation. This includes advanced formulas, functions, pivot tables, scenarios, goal seek, macros, data linking, and sharing.

These skills are essential for analyzing data, creating reports, managing budgets, and automating repetitive tasks in school projects, future studies, and careers.

💡 Note: LibreOffice Calc is completely free to download and use on Windows, macOS, and Linux. Download from: www.libreoffice.org


1. Getting Started with LibreOffice Calc

What is LibreOffice Calc?

LibreOffice Calc is a powerful spreadsheet program that allows you to create, edit, format, and analyze data in tabular form. It is similar to MS Excel but is free, open-source, and compatible with multiple file formats (including .xlsx, .xls, .ods, .csv, .pdf).

Key Features of LibreOffice Calc:

  • Free and Open-Source – No cost, no license fees.

  • Cross-Platform – Works on Windows, macOS, Linux.

  • File Compatibility – Opens and saves MS Excel (.xlsx, .xls), CSV, PDF, and more.

  • Advanced Formulas – Mathematical, statistical, logical, text, date/time functions.

  • Data Analysis – Pivot tables, scenarios, goal seek, solver.

  • Charts and Graphs – Create visual representations of data.

  • Macros – Automate repetitive tasks.

  • Data Linking – Connect spreadsheets to databases or other files.

  • Privacy-Focused – No cloud dependency; all processing is local.

LibreOffice Calc Interface:

The Calc interface consists of:

  1. Menu Bar – File, Edit, View, Insert, Format, Tools, Data, Window, Help.

  2. Standard Toolbar – New, Open, Save, Print, Cut, Copy, Paste, Undo, Redo.

  3. Formatting Toolbar – Font, size, bold, italic, alignment, number formats.

  4. Formula Bar – Displays cell content; enter formulas and functions.

  5. Spreadsheet Area – Grid of cells organized in rows (numbers) and columns (letters).

  6. Sheet Tabs – Navigate between multiple sheets in a workbook.

  7. Status Bar – Cell information, sum, average, count, zoom level.

  8. Sidebar (optional) – Access to Properties, Styles, Gallery, Navigator, Functions.

Opening LibreOffice Calc:

  • Windows: Start Menu → LibreOffice → LibreOffice Calc.

  • macOS: Applications → LibreOffice → Calc.

  • Linux: Applications → Office → LibreOffice Calc.

  • Keyboard Shortcut: If pinned, use custom shortcut or launch from terminal (libreoffice --calc).


Creating a New Spreadsheet:

  • Method 1: File → New → Spreadsheet (or press Ctrl+N).

  • Method 2: Click the New icon on the Standard Toolbar.

  • Method 3: From the LibreOffice Start Center, click Calc Spreadsheet.

Saving Spreadsheets:

  • Save: File → Save (or Ctrl+S).

  • Save As: File → Save As (or F12).

  • Default Format: .ods (OpenDocument Spreadsheet).

  • Save as MS Excel: Choose "Excel 2007–365 (.xlsx)" in "Save as type."

  • Save as PDF: File → Export As → Export as PDF (or Ctrl+Shift+E).

    📌 Tip: Always save your work frequently. Calc has auto-recovery, but manual saves are safer.


2. Advanced Formulas and Functions

What is a Formula?

A Formula is an expression that calculates values in a cell. Formulas always start with an equals sign (=) and can include numbers, cell references, operators, and functions.

Example: =A1+B1 adds values in cells A1 and B1.

What is a Function?

A Function is a predefined formula that performs specific calculations using values (called arguments) in a particular order.

Example: =SUM(A1:A10) adds all values from A1 to A10.

Cell References:

1. Relative Reference

  • Changes when copied to another cell.

  • Example: =A1+B1 → If copied down, becomes =A2+B2.

2. Absolute Reference

  • Remains constant when copied.

  • Denoted by dollar signs ($).

  • Example: =$A$1+$B$1 → If copied, stays =$A$1+$B$1.

3. Mixed Reference

  • Part relative, part absolute.

  • Example: =$A1 (column fixed, row changes) or =A$1 (row fixed, column changes).

Categories of Functions:

1. Mathematical Functions

FunctionSyntaxDescriptionExample
SUM=SUM(range)Adds all numbers in a range=SUM(A1:A10)
AVERAGE=AVERAGE(range)Calculates average=AVERAGE(B1:B10)
MIN=MIN(range)Finds minimum value=MIN(C1:C10)
MAX=MAX(range)Finds maximum value=MAX(D1:D10)
COUNT=COUNT(range)Counts numbers in range=COUNT(E1:E10)
ROUND=ROUND(number; decimals)Rounds to specified decimals=ROUND(3.14159; 2) → 3.14
POWER=POWER(base; exponent)Raises number to a power=POWER(2; 3) → 8
SQRT=SQRT(number)Returns square root=SQRT(16) → 4
MOD=MOD(number; divisor)Returns remainder=MOD(10; 3) → 1
RAND=RAND()Generates random number (0–1)=RAND()
RANDBETWEEN=RANDBETWEEN(bottom; top)Random number between two values=RANDBETWEEN(1; 100)

2. Statistical Functions

FunctionSyntaxDescriptionExample
COUNTIF=COUNTIF(range; criteria)Counts cells meeting criteria=COUNTIF(A1:A10; ">50")
SUMIF=SUMIF(range; criteria; sum_range)Sums cells meeting criteria=SUMIF(B1:B10; ">=100"; C1:C10)
AVERAGEIF=AVERAGEIF(range; criteria; average_range)Averages cells meeting criteria=AVERAGEIF(D1:D10; "<>0"; E1:E10)
STDEV=STDEV(range)Calculates standard deviation=STDEV(F1:F10)
VAR=VAR(range)Calculates variance=VAR(G1:G10)
MEDIAN=MEDIAN(range)Finds median value=MEDIAN(H1:H10)
MODE=MODE(range)Finds most frequent value=MODE(I1:I10)
LARGE=LARGE(range; k)Returns k-th largest value=LARGE(J1:J10; 3)
SMALL=SMALL(range; k)Returns k-th smallest value=SMALL(K1:K10; 2)

3. Logical Functions

FunctionSyntaxDescriptionExample
IF=IF(condition; value_if_true; value_if_false)Conditional test=IF(A1>50; "Pass"; "Fail")
AND=AND(condition1; condition2; ...)All conditions must be TRUE=AND(A1>50; B1<100)
OR=OR(condition1; condition2; ...)At least one condition TRUE=OR(A1>50; B1>100)
NOT=NOT(condition)Reverses logical value=NOT(A1>50)
IFERROR=IFERROR(value; value_if_error)Returns alternative if error occurs=IFERROR(A1/B1; "Error")
IFS=IFS(condition1; value1; condition2; value2; ...)Multiple conditions (Calc 7.0+)=IFS(A1>=90; "A"; A1>=80; "B"; A1>=70; "C")

Nested IF Example:

text
=IF(A1>=90; "Excellent"; IF(A1>=80; "Good"; IF(A1>=70; "Average"; "Needs Improvement")))

4. Text Functions

FunctionSyntaxDescriptionExample
CONCATENATE / CONCAT=CONCATENATE(text1; text2; ...)Joins text strings=CONCATENATE(A1; " "; B1)
LEFT=LEFT(text; num_chars)Extracts characters from left=LEFT("Hello"; 2) → "He"
RIGHT=RIGHT(text; num_chars)Extracts characters from right=RIGHT("Hello"; 3) → "llo"
MID=MID(text; start; num_chars)Extracts characters from middle=MID("Hello"; 2; 3) → "ell"
LEN=LEN(text)Returns length of text=LEN("Hello") → 5
TRIM=TRIM(text)Removes extra spaces=TRIM(" Hello ") → "Hello"
UPPER=UPPER(text)Converts to uppercase=UPPER("hello") → "HELLO"
LOWER=LOWER(text)Converts to lowercase=LOWER("HELLO") → "hello"
PROPER=PROPER(text)Capitalizes first letter of each word=PROPER("hello world") → "Hello World"
FIND=FIND(find_text; within_text; start)Finds position of text (case-sensitive)=FIND("e"; "Hello") → 2
SEARCH=SEARCH(find_text; within_text; start)Finds position of text (case-insensitive)=SEARCH("e"; "Hello") → 2
SUBSTITUTE=SUBSTITUTE(text; old_text; new_text; occurrence)Replaces text=SUBSTITUTE("Hello"; "l"; "L") → "HeLLo"
REPLACE=REPLACE(old_text; start; num_chars; new_text)Replaces text at position=REPLACE("Hello"; 2; 3; "i") → "Hio"


5. Date and Time Functions

FunctionSyntaxDescriptionExample
TODAY=TODAY()Returns current date=TODAY()
NOW=NOW()Returns current date and time=NOW()
DATE=DATE(year; month; day)Creates date from components=DATE(2026; 9; 9)
TIME=TIME(hour; minute; second)Creates time from components=TIME(14; 30; 0)
DAY=DAY(date)Extracts day from date=DAY(TODAY())
MONTH=MONTH(date)Extracts month from date=MONTH(TODAY())
YEAR=YEAR(date)Extracts year from date=YEAR(TODAY())
HOUR= HOUR(time)Extracts hour from time=HOUR(NOW())
MINUTE=MINUTE(time)Extracts minute from time=MINUTE(NOW())
SECOND=SECOND(time)Extracts second from time=SECOND(NOW())
DATEDIF=DATEDIF(start_date; end_date; unit)Calculates difference between dates=DATEDIF(A1; B1; "Y") (years)
EDATE=EDATE(start_date; months)Adds months to date=EDATE(TODAY(); 3)
EOMONTH=EOMONTH(start_date; months)Returns last day of month=EOMONTH(TODAY(); 0)
NETWORKDAYS=NETWORKDAYS(start_date; end_date)Counts working days (excludes weekends)=NETWORKDAYS(A1; B1)
WEEKDAY=WEEKDAY(date; type)Returns day of week (1–7)=WEEKDAY(TODAY())
WEEKNUM=WEEKNUM(date; type)Returns week number of year=WEEKNUM(TODAY())

6. Lookup and Reference Functions

FunctionSyntaxDescriptionExample
VLOOKUP=VLOOKUP(lookup_value; table_array; col_index; range_lookup)Vertical lookup=VLOOKUP(A1; D1:F10; 3; 0)
HLOOKUP=HLOOKUP(lookup_value; table_array; row_index; range_lookup)Horizontal lookup=HLOOKUP(A1; D1:K5; 3; 0)
INDEX=INDEX(array; row_num; col_num)Returns value at position=INDEX(A1:C10; 3; 2)
MATCH=MATCH(lookup_value; lookup_array; match_type)Returns position of value=MATCH("Apple"; A1:A10; 0)
INDIRECT=INDIRECT(ref_text)Returns reference from text=INDIRECT("A1")
OFFSET=OFFSET(reference; rows; cols; height; width)Returns range offset from reference=OFFSET(A1; 2; 3; 5; 2)
CHOOSE=CHOOSE(index_num; value1; value2; ...)Selects value from list=CHOOSE(2; "Red"; "Green"; "Blue") → "Green"

VLOOKUP Example:

text
=VLOOKUP("Amit"; A2:D100; 4; 0)

Looks for "Amit" in column A, returns value from 4th column (column D), exact match (0).


7. Financial Functions

FunctionSyntaxDescriptionExample
PMT=PMT(rate; nper; pv; [fv]; [type])Calculates loan payment=PMT(0.01; 36; 100000)
FV=FV(rate; nper; pmt; [pv]; [type])Future value of investment=FV(0.005; 60; -1000; 0; 0)
PV=PV(rate; nper; pmt; [fv]; [type])Present value=PV(0.005; 60; -1000; 0; 0)
RATE=RATE(nper; pmt; pv; [fv]; [type])Interest rate per period=RATE(36; -3000; 100000)
NPER=NPER(rate; pmt; pv; [fv]; [type])Number of periods=NPER(0.01; -3000; 100000)
NPV=NPV(rate; value1; value2; ...)Net present value=NPV(0.1; B1:B10)
IRR=IRR(values; [guess])Internal rate of return=IRR(A1:A10)

Using the Function Wizard:

  1. Click the cell where you want the function.

  2. Go to Insert → Function (or press Ctrl+F2).

  3. Browse or search for a function.

  4. Enter arguments in the dialog boxes.

  5. Click OK to insert the function.

    📊 Exam Tip: Practice IF, VLOOKUP, SUMIF, COUNTIF, CONCATENATE, and date functions—they are frequently asked in exams.


3. Data Analysis Tools

3.1 Sorting Data

Sorting arranges data in a specific order (ascending or descending).

How to Sort:

  1. Select the data range (including headers).

  2. Go to Data → Sort.

  3. In the dialog:

    • Choose sort criteria (column, ascending/descending).

    • Add multiple levels if needed (e.g., sort by Class, then by Name).

    • Check "Range contains column labels" if your data has headers.

  4. Click OK.

Quick Sort:

  • Select a column → Click Sort Ascending (A→Z) or Sort Descending (Z→A) on the toolbar.


3.2 Filtering Data

Filtering displays only rows that meet specific criteria, hiding others.

How to Apply AutoFilter:

  1. Select the data range (including headers).

  2. Go to Data → AutoFilter (or click the Filter icon on the toolbar).

  3. Dropdown arrows appear in header cells.

  4. Click a dropdown → Select filter criteria (e.g., specific values, text contains, number greater than).

  5. Only matching rows are displayed.

How to Remove Filter:

  • Go to Data → AutoFilter again to toggle off.

  • Or: Click dropdown → Select All.

Advanced Filter (Standard Filter):

  1. Go to Data → More Filters → Standard Filter.

  2. Set multiple conditions (AND/OR logic).

  3. Choose to filter in place or copy results to another location.

  4. Click OK.


3.3 Subtotals

Subtotals automatically calculate sums, averages, counts, etc., for groups of data.

How to Add Subtotals:

  1. Sort data by the column you want to group (e.g., sort by Department).

  2. Select the data range.

  3. Go to Data → Subtotals.

  4. In the dialog:

    • Group by: Choose the column to group (e.g., Department).

    • Calculate subtotals for: Select columns to calculate (e.g., Sales, Quantity).

    • Use function: Choose SUM, AVERAGE, COUNT, etc.

  5. Click OK.

How to Remove Subtotals:

  • Go to Data → Subtotals → Click Remove.


3.4 Pivot Tables

What is a Pivot Table?
A Pivot Table is a powerful data analysis tool that summarizes, analyzes, explores, and presents large amounts of data in a flexible, interactive table.

Features of Pivot Tables:

  • Summarize data (sum, average, count, etc.).

  • Group data by categories (rows and columns).

  • Filter and slice data dynamically.

  • Drill down to see details.

  • Create calculated fields (custom formulas).

How to Create a Pivot Table:

Step 1: Prepare Data

  • Ensure data is in tabular format with headers.

  • No blank rows or columns within the data.

Step 2: Insert Pivot Table

  1. Select the data range.

  2. Go to Insert → Pivot Table (or Data → Pivot Table → Create or Change).

  3. In the dialog:

    • Current selection: Confirm the data range.

    • New sheet or Existing sheet: Choose where to place the pivot table.

  4. Click OK.


Step 3: Configure Pivot Table (Pivot Table Layout)

  1. In the Pivot Table Layout dialog:

    • Available Fields: List of column headers from your data.

    • Drag fields to:

      • Row Fields: Categories for rows.

      • Column Fields: Categories for columns.

      • Data Fields: Values to summarize (sum, average, count, etc.).

      • Page Fields (optional): Filters for the entire table.

  2. Double-click a data field to change the function (SUM, AVERAGE, COUNT, etc.).

  3. Click OK.

Step 4: Customize Pivot Table

  • Refresh: Right-click → Refresh to update with new data.

  • Change Layout: Right-click → Edit Layout to rearrange fields.

  • Format Numbers: Right-click data cells → Format Cells.

  • Add Calculated Fields (Calc 26.8+):

    • Go to Pivot Table → Insert Calculated Field.

    • Enter formula using existing fields.

    • Click OK.


Example: Sales Data Pivot Table

  • Data: Columns = Date, Region, Product, Salesperson, Units Sold, Revenue.

  • Pivot Table:

    • Rows: Region

    • Columns: Product

    • Data: Sum of Revenue

    • Page Field: Salesperson (filter by person)

      📊 Exam Tip: Understand how to create and configure a pivot table—this is a common practical question.


3.5 Scenarios

What are Scenarios?
Scenarios allow you to create and save different sets of input values (what-if analysis) to see how they affect formula results.

When to Use Scenarios:

  • Compare different budgets (best case, worst case, most likely).

  • Analyze impact of price changes, cost variations, or sales targets.

  • Test multiple assumptions without creating separate sheets.

How to Create a Scenario:

Step 1: Prepare Data

  • Create a spreadsheet with formulas that depend on input cells.

  • Example: Profit = Revenue - Costs (where Revenue and Costs are input cells).

Step 2: Define Scenario

  1. Select the input cells (e.g., cells containing Revenue and Costs).

  2. Go to Tools → Scenarios.

  3. In the dialog:

    • Scenario name: Enter a name (e.g., "Best Case").

    • Comment (optional): Describe the scenario.

    • Copy back: Check to allow updating input cells when scenario is active.

    • Show border: Check to highlight scenario cells.

    • Copy styles: Check to preserve formatting.

  4. Click OK.

Step 3: Create Additional Scenarios

  • Change input values (e.g., higher revenue, lower costs).

  • Repeat Step 2 to create more scenarios (e.g., "Worst Case", "Most Likely").

Step 4: Switch Between Scenarios

  • Go to Tools → Scenarios → Select scenario name → Click OK.

  • Or: Use the scenario dropdown on the toolbar (if visible).

Example: Budget Scenarios

  • Input Cells: B1 (Revenue), B2 (Costs)

  • Formula: B3 (Profit) = B1 - B2

  • Scenarios:

    • Best Case: Revenue = 100000, Costs = 60000 → Profit = 40000

    • Worst Case: Revenue = 70000, Costs = 65000 → Profit = 5000

    • Most Likely: Revenue = 85000, Costs = 62000 → Profit = 23000


3.6 Goal Seek

What is Goal Seek?
Goal Seek is a tool that finds the input value needed to achieve a specific result (goal) in a formula.

When to Use Goal Seek:

  • Find the sales target needed to achieve a desired profit.

  • Determine the loan amount you can afford based on a monthly payment.

  • Calculate the score needed on a final exam to achieve a target grade.

How to Use Goal Seek:

Step 1: Prepare Data

  • Create a formula that depends on an input cell.

  • Example: Profit = Revenue - Costs (where Revenue is the input cell).

Step 2: Open Goal Seek

  1. Go to Tools → Goal Seek.

  2. In the dialog:

    • Formula cell: Select the cell with the formula (e.g., Profit cell).

    • Target value: Enter the desired result (e.g., 50000).

    • Variable cell: Select the input cell to change (e.g., Revenue cell).

  3. Click OK.

Step 3: Review Results

  • Calc calculates the input value needed to achieve the target.

  • A dialog shows the result; click OK to accept or Cancel to revert.

Example: Exam Score Goal Seek

  • Formula: Final Grade = (Midterm × 0.4) + (Final Exam × 0.6)

  • Known: Midterm = 75, Target Final Grade = 85

  • Goal Seek:

    • Formula cell: Final Grade cell

    • Target value: 85

    • Variable cell: Final Exam score cell

  • Result: Calc shows you need 91.67 on the final exam to achieve 85 overall.

    🎯 Tip: Goal Seek works with one variable at a time. For multiple variables, use Solver (advanced tool).


3.7 Solver (Optional Advanced Tool)

What is Solver?
Solver is an advanced optimization tool that finds the best solution (maximum, minimum, or specific value) by adjusting multiple variables subject to constraints.

When to Use Solver:

  • Maximize profit with limited resources.

  • Minimize costs while meeting production targets.

  • Optimize investment portfolio allocation.

How to Use Solver:

  1. Go to Tools → Solver.

  2. In the dialog:

    • Target cell: Select the formula cell to optimize.

    • Optimization goal: Maximize, Minimize, or set to specific value.

    • Variable cells: Select cells to adjust (up to multiple cells).

    • Constraints: Add conditions (e.g., cells must be >= 0, <= 100, integer).

  3. Click Solve.

  4. Review results; accept or revert.

    ⚠️ Note: Solver is an advanced tool; focus on Goal Seek for Class 10 exams.


4. Charts and Graphs

What are Charts?

Charts (or graphs) are visual representations of data that make it easier to understand trends, patterns, and comparisons.

Types of Charts in Calc:

Chart TypeBest ForExample Use
ColumnComparing values across categoriesSales by month, marks by subject
BarComparing values (horizontal)Population by country, survey results
LineShowing trends over timeTemperature changes, stock prices
PieShowing proportions of a wholeBudget allocation, market share
AreaTrends over time with volume emphasisCumulative sales, website traffic
XY (Scatter)Relationship between two variablesHeight vs. weight, price vs. demand
BubbleThree-dimensional data (X, Y, size)GDP vs. population vs. growth rate
Net (Radar)Comparing multiple variablesSkill assessment, product features
StockFinancial data (open, high, low, close)Stock market analysis
Column and LineCombining two chart typesSales (columns) + target (line)

How to Create a Chart:

Step 1: Prepare Data

  • Organize data in rows or columns with headers.

  • Example:

MonthSales
Jan50000
Feb60000
Mar55000
Apr70000

Step 2: Select Data

  • Highlight the data range (including headers).

Step 3: Insert Chart

  1. Go to Insert → Chart (or click the Chart icon on the toolbar).

  2. The Chart Wizard opens with 4 steps.

Step 4: Chart Wizard

Step 1: Chart Type

  • Select chart type (e.g., Column, Bar, Line, Pie).

  • Choose a variant (e.g., 2D, 3D, stacked).

  • Click Next.

Step 2: Data Range

  • Confirm data range.

  • Choose:

    • Data in rows or Data in columns.

    • First row as label (headers).

    • First column as label (if applicable).

  • Click Next.

Step 3: Data Series

  • Review and adjust data series (categories, values).

  • Add or remove series if needed.

  • Click Next.


Step 4: Chart Elements

  • Title: Chart title, X-axis title, Y-axis title.

  • Axes: Show/hide axes, set scale.

  • Gridlines: Show/hide gridlines.

  • Legend: Show/hide legend, choose position.

  • Data Labels: Show values on chart.

  • Click Finish.

Step 5: Customize Chart

  • Resize: Click and drag corners.

  • Move: Click and drag to reposition.

  • Edit: Double-click chart to enter edit mode.

    • Change colors, fonts, styles.

    • Add/remove elements (titles, legends, labels).

    • Format axes, gridlines, data series.

  • Exit Edit Mode: Click outside the chart.

Formatting Charts:

Change Chart Type:

  • Right-click chart → Chart Type → Select new type → OK.

Edit Data Range:

  • Right-click chart → Data Ranges → Adjust range → OK.

Format Data Series:

  • Double-click a data series (e.g., columns, lines).

  • Change colors, patterns, line styles, markers.

Add Trendline (for XY/Line Charts):

  • Right-click data series → Insert Trend Line.

  • Choose type (Linear, Exponential, Polynomial, etc.).

  • Show equation and R² value (optional).

Export Chart:

  • Right-click chart → Export as Image → Choose format (PNG, JPG, SVG) → Save.

    📈 Exam Tip: Practice creating Column, Bar, Line, and Pie charts—these are most commonly asked.


5. Macros in LibreOffice Calc

What is a Macro?

A Macro is a recorded sequence of commands or actions that you can save and replay to automate repetitive tasks in Calc. Macros help you save time and reduce errors by performing complex actions with a single click or keyboard shortcut.

⚠️ Note: LibreOffice macros use LibreOffice Basic (similar to VBA in MS Office) or other scripting languages like Python.

When to Use Macros?

  • Applying the same formatting to multiple cells or sheets.

  • Performing repetitive calculations or data entry.

  • Automating multi-step processes (e.g., data import, formatting, export).

  • Creating custom buttons or shortcuts for frequently used actions.

Benefits of Macros:

  • Time-Saving – One click replaces many manual steps.

  • Consistency – Ensures the same actions every time.

  • Efficiency – Reduces repetitive work and fatigue.

  • Customization – Create tools tailored to your specific needs.

How to Record a Macro:

Step 1: Enable Macro Recording

  1. Go to Tools → Options → LibreOffice → Advanced.

  2. Check Enable macro recording (if not already enabled).

  3. Click OK.

Step 2: Start Recording

  1. Go to Tools → Macros → Record Macro.

  2. Perform the actions you want to automate (e.g., format cells, insert formula, create chart).

  3. When done, click Stop Recording.

Step 3: Save the Macro

  1. In the dialog, click Save As.

  2. Choose a location (e.g., "My Macros" → "Standard").

  3. Enter a macro name (e.g., "FormatSalesData").

  4. Click Save.

How to Run a Macro:

  1. Go to Tools → Macros → Run Macro.

  2. Navigate to your macro (e.g., "My Macros" → "Standard" → "FormatSalesData").

  3. Click Run.

Assign Macro to a Button or Shortcut:

  1. Go to Tools → Customize → Keyboard (or Toolbar).

  2. Select a function key (e.g., Ctrl+Shift+M) or add a toolbar button.

  3. Assign your macro to the selected key/button.

  4. Click OK.


Example Macro: Auto-Format Sales Report

Actions to Record:

  1. Select data range.

  2. Apply currency format to sales columns.

  3. Bold headers.

  4. Add borders.

  5. Auto-fit column widths.

  6. Insert SUM formula at bottom.

Result: Anytime you need to format a sales report, run the macro instead of doing all steps manually.

Security Considerations:

  • Macros can contain malicious code.

  • Only run macros from trusted sources.

  • Adjust macro security: Tools → Options → LibreOffice → Security → Macro Security.

    🔒 Caution: Never enable macros in documents from unknown or suspicious sources.


6. Data Linking and Integration

6.1 Linking Spreadsheets

What is Data Linking?
Data Linking allows you to connect cells or ranges in one spreadsheet to another, so changes in the source automatically update the destination.

When to Use Linking:

  • Consolidate data from multiple sheets or files.

  • Create summary reports that pull data from detailed sheets.

  • Maintain a master database with linked summaries.

Method 1: Simple Reference

  1. Open both source and destination spreadsheets.

  2. In destination cell, type =.

  3. Click the source cell in the other spreadsheet.

  4. Press Enter.

  5. Formula appears as: ='file:///path/to/file.ods'#$SheetName.CellAddress

  1. Copy source cells (Ctrl+C).

  2. Go to destination sheet.

  3. Go to Edit → Paste Special → Paste Link (or Ctrl+Shift+V → Check Link).

  4. Linked cells update when source changes.

  • Manual: Go to Edit → Links to External Files → Select link → Click Update.

  • Automatic: Calc can update links on opening (configure in Options).

  • Go to Edit → Links to External Files → Select link → Click Break Link.

  • Converts linked cells to static values.


6.2 Linking to Databases

What is Database Linking?
Calc can connect to external databases (e.g., MySQL, PostgreSQL, Base, Excel files) to import and analyze data.

How to Register a Database:

  1. Go to File → New → Database.

  2. Follow the Database Wizard:

    • Connect to existing database: Choose type (e.g., Spreadsheet, Base, MySQL).

    • Browse and select the database file.

    • Complete registration.

  3. Save the database registration.

How to Import Data from Database:

  1. Go to Insert → Spreadsheet from File (or Data → Define Range for registered databases).

  2. Select the database or file.

  3. Choose table or query.

  4. Import data into Calc.

Using Registered Databases in Calc:

  • Go to View → Data Sources (or press F4).

  • Browse registered databases.

  • Drag tables or queries into the spreadsheet.


6.3 Consolidating Data

What is Data Consolidation?
Consolidation combines data from multiple ranges or sheets into a single summary, applying functions like SUM, AVERAGE, COUNT, etc.

How to Consolidate Data:

  1. Organize data in multiple sheets or ranges with the same structure.

  2. Go to Data → Consolidate.

  3. In the dialog:

    • Source ranges: Add ranges to consolidate.

    • Consolidate by: Choose row/column labels.

    • Use function: Select SUM, AVERAGE, COUNT, etc.

    • Options: Include labels, create links to source data.

  4. Click OK.


7. Sharing and Reviewing Spreadsheets

7.1 Protecting Sheets and Cells

Why Protect Sheets?

  • Prevent accidental or unauthorized changes.

  • Lock formulas, headers, or sensitive data.

  • Allow users to edit only specific cells.

How to Protect a Sheet:

  1. Go to Tools → Protect Sheet.

  2. In the dialog:

    • Enter a password (optional but recommended).

    • Select allowed actions (e.g., select cells, insert rows, format cells).

  3. Click OK.

How to Unprotect a Sheet:

  • Go to Tools → Protect Sheet → Enter password → Click OK.

Protect Specific Cells:

  1. Select cells you want to remain editable.

  2. Right-click → Format Cells → Cell Protection tab.

  3. Uncheck Protected → Click OK.

  4. Then protect the sheet (as above).

  5. Only unprotected cells can be edited.


7.2 Track Changes

What is Track Changes?
Track Changes records all edits made to a spreadsheet, allowing collaboration and review.

How to Turn On Track Changes:

  1. Go to Edit → Track Changes → Record (or press Ctrl+Shift+R).

  2. All subsequent edits are highlighted with colored borders.

Viewing Changes:

  • Go to Edit → Track Changes → Show to toggle visibility.

  • Use Edit → Track Changes → Manage to see a list of all changes with author names and timestamps.

Accept or Reject Changes:

  1. Go to Edit → Track Changes → Manage.

  2. In the dialog, review each change.

  3. Click Accept or Reject for individual changes.

  4. Or use Accept All / Reject All buttons.

  • Use Edit → Track Changes → Next Change or Previous Change to move through edits.


7.3 Comments

What are Comments?
Comments are notes attached to cells without changing the cell content. Useful for explanations, feedback, or reminders.

How to Add a Comment:

  1. Select the cell.

  2. Go to Insert → Comment (or press Ctrl+Alt+C).

  3. Type your comment in the comment box.

  4. A small red triangle appears in the corner of the cell.

View Comments:

  • Hover over the cell to see the comment.

  • Or: Go to View → Comments to show all comments.

Edit or Delete Comments:

  • Right-click the cell → Edit Comment or Delete Comment.


7.4 Sharing Spreadsheets

Share as PDF:

  1. Go to File → Export As → Export as PDF.

  2. Configure options (page range, security, compression).

  3. Click Export → Save.

Share via Email:

  1. Go to File → Send → Send as PDF Attachment (or Send as ODS Attachment).

  2. Your default email client opens with the spreadsheet attached.

  3. Add recipient, subject, message → Send.

Share on Network or Cloud:

  • Save the file to a shared folder (e.g., Google Drive, OneDrive, school server).

  • Share the link with collaborators.

  • Use File → Properties → Description to add metadata.

Collaborative Editing (Limited in Calc):

  • Calc does not support real-time co-authoring like Google Sheets.

  • Alternative: Use Collabora Online (self-hosted) or export to Google Sheets for collaboration.


8. Advanced Features and Tips

8.1 Conditional Formatting

What is Conditional Formatting?
Conditional Formatting automatically changes cell formatting (color, font, border) based on cell values or formulas.

When to Use:

  • Highlight top/bottom performers.

  • Show pass/fail status.

  • Indicate overdue dates, budget overruns, or thresholds.

How to Apply Conditional Formatting:

  1. Select the cells.

  2. Go to Format → Conditional Formatting → Condition.

  3. In the dialog:

    • Condition 1: Choose criteria (e.g., Cell value is, Formula is, Color scale).

    • Set conditions (e.g., greater than 50, between 1 and 100).

    • Choose formatting style (background color, font color, etc.).

    • Add more conditions if needed.

  4. Click OK.

Examples:

  • Highlight Pass/Fail:

    • Condition: Cell value is >= 50 → Style: Green background.

    • Condition: Cell value is < 50 → Style: Red background.

  • Color Scale (Heat Map):

    • Condition: Color scale → Low values (red), High values (green).

  • Data Bars:

    • Condition: Data bars → Visual bars inside cells.


8.2 Data Validation

What is Data Validation?
Data Validation restricts what users can enter in a cell, ensuring data accuracy and consistency.

When to Use:

  • Create dropdown lists.

  • Restrict input to numbers, dates, or specific ranges.

  • Prevent invalid entries (e.g., negative ages, future birth dates).

How to Apply Data Validation:

  1. Select the cells.

  2. Go to Data → Validity (or Data → Validation).

  3. In the dialog:

    • Allow: Choose type (Whole Numbers, Decimal, List, Date, Time, Text length, Custom).

    • Set criteria (e.g., between 1 and 100, list of items).

    • Input Help (optional): Show a message when cell is selected.

    • Error Alert (optional): Show error message if invalid data is entered.

  4. Click OK.

Example: Dropdown List

  • Allow: List

  • Entries: Enter items (one per line):

    text
    Yes No Maybe
  • Result: Cell shows a dropdown arrow with these options.


8.3 Named Ranges

What are Named Ranges?
Named Ranges assign meaningful names to cells or ranges, making formulas easier to read and manage.

Benefits:

  • Easier to understand formulas (e.g., =SUM(Sales) instead of =SUM(B2:B100)).

  • Simplifies navigation (use Name Box to jump to named range).

  • Useful in complex formulas and macros.

How to Create a Named Range:

  1. Select the cells.

  2. Go to Data → Define Range (or Sheet → Named Ranges and Expressions → Define).

  3. In the dialog:

    • Name: Enter a name (no spaces; e.g., SalesData).

    • Range: Confirm the cell range.

    • Options: Set scope (document or sheet), print area, etc.

  4. Click OK.

How to Use Named Ranges in Formulas:

  • Type the name in a formula: =SUM(SalesData)

  • Or: Use the Name Box dropdown to select the named range.

Manage Named Ranges:

  • Go to Sheet → Named Ranges and Expressions → Manage.

  • Edit, delete, or add new named ranges.


8.4 What-If Analysis Tools

Calc provides several tools for what-if analysis (exploring how changes in inputs affect outputs):

  1. Scenarios – Compare different sets of input values.

  2. Goal Seek – Find input needed for a specific result.

  3. Solver – Optimize multiple variables with constraints.

  4. Data Tables (via Pivot Tables) – Analyze data across different dimensions.

    🎯 Tip: Focus on Scenarios and Goal Seek for Class 10 exams.


8.5 Keyboard Shortcuts (Essential)

ShortcutAction
Ctrl+NNew spreadsheet
Ctrl+OOpen spreadsheet
Ctrl+SSave
F12Save As
Ctrl+PPrint
Ctrl+ZUndo
Ctrl+YRedo
Ctrl+CCopy
Ctrl+XCut
Ctrl+VPaste
Ctrl+Shift+VPaste Special
Ctrl+FFind
Ctrl+HFind & Replace
Ctrl+Arrow KeysNavigate to edge of data
Ctrl+SpaceSelect entire column
Shift+SpaceSelect entire row
Ctrl+Shift+Arrow KeysSelect range
F2Edit cell
F4Toggle absolute/relative reference in formula
Ctrl+Shift+EnterEnter array formula
**Ctrl+`** | Show formulas instead of values |
F9Recalculate all formulas
Shift+F9Recalculate current sheet
Ctrl+Shift+F9Recalculate all open documents
F11Open Functions deck (Sidebar)
F4Toggle Data Sources view
Ctrl+Shift+EExport as PDF

Quick Revision Points

TopicKey Points
LibreOffice CalcFree, open-source spreadsheet; compatible with .xlsx, .ods, .csv, .pdf.
FormulasStart with =; use cell references, operators, and functions.
FunctionsSUM, AVERAGE, IF, VLOOKUP, COUNTIF, SUMIF, CONCATENATE, DATE, etc.
Cell ReferencesRelative (A1), Absolute ($A$1), Mixed ($A1 or A$1).
SortingArrange data in ascending/descending order.
FilteringDisplay only rows meeting criteria (AutoFilter, Standard Filter).
SubtotalsAutomatically calculate sums/averages for grouped data.
Pivot TablesSummarize and analyze large datasets interactively.
ScenariosCreate and compare different sets of input values (what-if analysis).
Goal SeekFind input value needed to achieve a specific result.
ChartsColumn, Bar, Line, Pie, XY (Scatter), Area, etc.
MacrosAutomate repetitive tasks; record and run macros.
Data LinkingLink cells between spreadsheets or to databases.
Conditional FormattingChange formatting based on cell values or formulas.
Data ValidationRestrict input to specific types, ranges, or lists.
Named RangesAssign names to cells/ranges for easier formulas.
Protect SheetsPrevent unauthorized changes; lock cells or entire sheets.
Track ChangesRecord edits for review; accept or reject changes.
CommentsAdd notes to cells without changing content.
Export PDFPreserve formatting; share as read-only document.

Practice Questions

Q1. What is LibreOffice Calc? List any four key features.
Q2. Define a formula and a function. Give one example of each.
Q3. Differentiate between relative, absolute, and mixed cell references with examples.
Q4. Explain any five mathematical functions with their syntax and examples.
Q5. What is the IF function? Write a nested IF formula to assign grades based on marks.
Q6. Describe the VLOOKUP function with an example.
Q7. What are scenarios? How do you create and switch between scenarios?
Q8. Explain the Goal Seek tool with a practical example.
Q9. What is a pivot table? Describe the steps to create one.
Q10. List any five types of charts available in Calc and their best use cases.
Q11. Describe the steps to create a column chart from a data range.
Q12. What is a macro? How do you record and run a macro in Calc?
Q13. Explain conditional formatting with two examples.
Q14. What is data validation? How do you create a dropdown list in a cell?
Q15. Describe how to link cells from one spreadsheet to another.
Q16. How do you protect a sheet in Calc? Why is it useful?
Q17. Explain the difference between Track Changes and Comments.
Q18. What are named ranges? How do you create and use them in formulas?
Q19. Describe the steps to export a spreadsheet as PDF.
Q20. Write the syntax and purpose of the following functions: SUMIF, COUNTIF, CONCATENATE, TODAY, ROUND.


Practical Exercises

Exercise 1: Advanced Formulas

  • Create a student mark sheet with columns: Name, Subject1, Subject2, Subject3, Total, Average, Grade.

  • Use formulas to calculate Total and Average.

  • Use a nested IF formula to assign Grades (A, B, C, D, F) based on Average.

  • Use COUNTIF to count how many students got Grade A.

  • Use SUMIF to calculate total marks of students with Grade A.

Exercise 2: Data Analysis

  • Create a sales dataset with columns: Date, Region, Product, Salesperson, Units Sold, Revenue.

  • Sort data by Revenue (descending).

  • Apply AutoFilter to show only sales from a specific Region.

  • Add Subtotals to calculate total Revenue by Region.

  • Create a Pivot Table with:

    • Rows: Region

    • Columns: Product

    • Data: Sum of Revenue

Exercise 3: Scenarios and Goal Seek

  • Create a budget sheet with:

    • Input cells: Revenue, Costs

    • Formula: Profit = Revenue - Costs

  • Create three scenarios: Best Case, Worst Case, Most Likely.

  • Use Goal Seek to find the Revenue needed to achieve a Profit of 50000.

Exercise 4: Charts

  • Using the sales dataset from Exercise 2:

    • Create a Column chart showing Revenue by Region.

    • Create a Pie chart showing Revenue share by Product.

    • Create a Line chart showing monthly sales trend.

  • Customize charts with titles, labels, legends, and colors.

Exercise 5: Macros

  • Record a macro to:

    • Format a data range (bold headers, currency format, borders, auto-fit columns).

    • Insert a SUM formula at the bottom of a column.

  • Assign the macro to a keyboard shortcut (e.g., Ctrl+Shift+M).

  • Test the macro on different datasets.

Exercise 6: Conditional Formatting and Data Validation

  • Create a mark sheet with conditional formatting:

    • Green background for marks >= 75.

    • Yellow background for marks 50–74.

    • Red background for marks < 50.

  • Apply data validation to create a dropdown list for "Grade" column (A, B, C, D, F).


Exercise 7: Data Linking

  • Create two spreadsheets:

    • Sheet1: Detailed monthly sales data.

    • Sheet2: Summary sheet that links to Sheet1 to show total sales.

  • Update data in Sheet1 and verify that Sheet2 updates automatically.

Exercise 8: Protection and Sharing

  • Protect a sheet with a password, allowing users to edit only specific cells.

  • Add comments to explain complex formulas.

  • Export the spreadsheet as a password-protected PDF.

  • Simulate sharing via email (or actually send to your teacher).


Teacher's Note

  • Part B – Unit 2 is heavily practical—ensure students practice in the computer lab.

  • Focus on formulas (IF, VLOOKUP, SUMIF, COUNTIF), pivot tables, charts, and goal seek as they are frequently asked in exams.

  • Encourage students to create real-world spreadsheets (mark sheets, budgets, sales reports, inventories) for better understanding.

  • Demonstrate macros, scenarios, and pivot tables live in class—students find these features engaging.

  • Remind students to save their work frequently and organize files properly.

  • Emphasize that LibreOffice Calc is free and open-source—a valuable tool for students who may not have access to paid software.

  • Provide practice datasets for students to analyze and visualize.


Final Thoughts

Mastering advanced LibreOffice Calc features transforms you from a basic user to a power user who can analyze data, create insights, and automate tasks—without spending a rupee on software. Whether you're managing budgets, analyzing sales, tracking grades, or preparing reports, these skills will save you time and impress your teachers, colleagues, and future employers.

Practice regularly, experiment with functions and tools, and don't hesitate to explore Calc's features on your own.


End of Part B – Unit 2 Notes: Electronic Spreadsheet using LibreOffice Calc (Advanced)
CBSE Class 10 IT (Code 402) | Aligned with 2026–27 Syllabus


About the Author:
These notes are created by Ravindra Khillare, a Computer Science Teacher at Pioneer International School, Bhokardan. With a focus on CBSE IT 402 curriculum, these resources aim to support students, teachers, and educators in mastering essential spreadsheet skills using free and open-source tools.

Feel free to share, adapt, and use these notes for classroom teaching, blog posts, or student handouts. For queries or suggestions, connect via your school's IT department or educational networks.

Google Forms 31 Questions 47 Mins 60% Pass

Unit1_Digital_Documentation_Advanced_30_MCQ

Imported from Google Form on 9/9/2026



Comments