Chapter 2 — Electronic Spreadsheet Advanced

Introduction: An advanced electronic spreadsheet extends basic sheet skills (entering data, basic SUM) with powerful tools to analyze, visualize and automate data. It helps convert raw rows and columns into meaningful reports, forecasts and interactive dashboards.

Key capabilities:

• Formulas & functions — complex calculations (conditional, statistical, financial, lookup and array formulas).
• Lookup & reference — VLOOKUP/HLOOKUP, INDEX+MATCH to fetch data from tables.
• Structured Tables & Named Ranges — automatic expansion, readable formulas and consistent references.
• Pivot Tables & Pivot Charts — fast multi-dimensional summarization and drill-down.
• Charts & Visualization — multiple chart types, combo charts, secondary axis, trendlines and sparklines for quick insights.
• Conditional Formatting & Data Validation — visual rules, input controls (drop-downs), and error-prevention.
• What‑If Analysis — Goal Seek, Scenario Manager, Data Tables for sensitivity analysis and forecasting.
• Automation & Macros — recorded macros or scripts to automate repetitive tasks (introduces basic programming).
• Data tools — sorting, filtering, advanced filters, text-to-columns, import from CSV/SQL/web and data cleansing functions.
• Absolute vs Relative References — use $A$1 to fix references when copying formulas; relative (A1) changes with position.
• Best practices — use headers, freeze panes, keep raw data separate from calculations, use tables and named ranges, document formulas.

How it helps (brief workflow): import or enter data → convert to a Table → add calculated columns and named ranges → build pivot tables/charts → apply conditional formatting and slicers → create what‑if scenarios → protect and share results.

Overview
Advanced formulas extend basic spreadsheet calculations into powerful, real‑world solutions. They combine functions across categories (math, statistical, logical, text, date/time, lookup/reference, financial, array) and use conditional, aggregate and lookup techniques to analyze and automate data.

Why it matters
Advanced formulas let you: automate reports, compute conditional totals, clean and transform text, look up values from tables, analyze trends, and build decision logic (e.g., grade assignment, payroll, budgets).

Core concepts

• Function categories — each group solves a class of problems (SUM-like math, AVERAGE-like statistics, IF-like logic, VLOOKUP/INDEX for table lookup, TEXT functions for names/dates).
• Nested functions — putting one function inside another (e.g., IF(AVERAGE(range)>70, "Pass", "Fail")).
• Conditional aggregation — SUMIF, COUNTIF, SUMIFS, COUNTIFS to total or count only when criteria are met.
• Lookup and reference — VLOOKUP/HLOOKUP, INDEX+MATCH (more flexible), XLOOKUP (modern), to pull related data from tables.
• Text handling — split/concatenate and clean text for reports (LEFT, RIGHT, MID, FIND, TRIM, CONCAT/CONCATENATE, TEXTJOIN).
• Date/time calculations — age, service length, or elapsed days using DATE, TODAY, DATEDIF, NETWORKDAYS.
• Array/dynamic formulas — operate on ranges to return multiple results (FILTER, UNIQUE, SEQUENCE) or use array formulas in older spreadsheets.
• Error handling — IFERROR to replace error values with friendly messages or alternatives.

Tips for building advanced formulas

• Break complex tasks into helper columns (intermediate results) for clarity.
• Test nested functions step by step.
• Use absolute references ($A$1) when copying formulas that must keep a fixed cell or range.
• Prefer INDEX+MATCH (or XLOOKUP) over VLOOKUP when you need left‑side lookup or better performance.
• Document key formulas with comments so others understand the logic.

What are Logical Functions?

Logical functions are spreadsheet functions that test conditions and return TRUE/FALSE, or choose values based on those conditions. They let you make decisions in formulas — for example, to mark students as Pass/Fail, apply discounts, or check multiple criteria before taking an action.

Common logical functions

• IF — returns one value if a condition is true and another if it is false.
• AND — returns TRUE only if all given conditions are TRUE.
• OR — returns TRUE if at least one condition is TRUE.
• NOT — reverses the logical value: TRUE becomes FALSE and vice versa.
• XOR — exclusive OR: TRUE when an odd number of arguments are TRUE (depends on spreadsheet version).
• IFS — evaluates multiple conditions in order and returns a corresponding value for the first TRUE condition (newer function in many spreadsheets).
• IFERROR — returns a specified value if the formula results in an error.

How they work (basic patterns)

• Single test: IF(test, value_if_true, value_if_false)
• Combine tests: IF(AND(test1, test2), value_if_true, value_if_false) or IF(OR(test1, test2), ...)
• Nested decisions: IF(..., IF(..., ...), ...) to handle multiple outcomes.

Best practices

• Keep logical tests simple and readable; prefer IFS over deeply nested IF where available.
• Use IFERROR to handle divide-by-zero or lookup errors gracefully.
• Document complex logic with adjacent notes or column headers so others can understand the decision rules.

Short example explained:

To mark Pass/Fail where a pass requires marks >= 33: =IF(A2>=33,"Pass","Fail"). The test is A2>=33. If TRUE the formula returns "Pass", otherwise "Fail".

What they do
Lookup and reference functions let a spreadsheet find, return or point to data stored elsewhere in the sheet or workbook. They are used to search tables, fetch matching records, build dynamic cell references and combine data from different ranges.

Common functions

• VLOOKUP — vertical lookup: searches the first column of a table and returns a value from a specified column in the same row.
• HLOOKUP — horizontal lookup: searches the first row of a table and returns a value from a specified row in the same column.
• INDEX — returns the value of a cell in a range given its row and column numbers.
• MATCH — returns the position of a lookup value within a one‑dimensional range.
• INDEX + MATCH — a flexible alternative to VLOOKUP that can look left, is robust to column order changes and often faster on large data.
• LOOKUP, OFFSET, INDIRECT, CHOOSE, ADDRESS, ROW, COLUMN — functions that build or return references and positions.

Syntax essentials

• VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  Use FALSE (or 0) for exact match and TRUE (or omitted) for approximate match (table must be sorted ascending for approximate).
• HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• MATCH(lookup_value, lookup_array, [match_type]) — match_type 0 for exact, 1 for <= (requires ascending sort), -1 for >= (requires descending sort).
• INDEX(array, row_num, [column_num]) — returns the value at the specified position.
• Combine: INDEX(col_to_return, MATCH(lookup_value, lookup_col, 0)) for reliable exact lookups.

Errors and tips

• #N/A appears when no match is found — handle with IFERROR(..., "Not found") if needed.
• Use absolute references (e.g., $A$2:$D$100) for the table_array so formulas copy correctly.
• Prefer INDEX+MATCH when you need to look left or when the lookup column is not the leftmost column.
• For many lookups on large tables, INDEX+MATCH or using named ranges improves performance and readability.

How it works (basic flow)
1. Find the lookup value’s position (MATCH or first column search). 2. Return the value from the same row/column (VLOOKUP, INDEX or HLOOKUP). 3. Optionally build dynamic references (INDIRECT, ADDRESS, OFFSET) or select among values (CHOOSE).

Overview: Text functions in spreadsheets let you inspect, clean, convert, join and extract parts of text (strings). They are essential for preparing data (names, IDs, addresses) for analysis, for creating standardized outputs (emails, labels) and for parsing codes.

Common categories:

• Case & formatting: change or standardize text case (UPPER, LOWER, PROPER) and format numbers as text (TEXT).
• Extraction: get parts of a string by position (LEFT, RIGHT, MID) or by searching (FIND, SEARCH).
• Cleaning & length: remove unwanted spaces/nonprintable characters (TRIM, CLEAN), measure length (LEN).
• Replacement & splitting: replace substrings (SUBSTITUTE, REPLACE) or split/join text (CONCAT/CONCATENATE, &, TEXTSPLIT in modern sheets or Text to Columns in Excel).
• Conversion & lookup: convert text that looks like numbers (VALUE), compare strings (EXACT), and convert characters to codes or vice versa (CODE, CHAR).

How they help in real life:

• Cleaning student lists: remove extra spaces, standardize case, extract roll numbers embedded in IDs.
• Automatic email creation: combine initials and surnames to build school emails.
• Formatting phone numbers or product codes consistently for databases.
• Parsing address fields (e.g., extracting pin code from end of address).

Tips: Use TRIM before extracting to avoid wrong positions; use FIND for case-sensitive search and SEARCH for case-insensitive; use SUBSTITUTE to change all occurrences of a substring; use TEXT to present numbers (e.g., dates) consistently when concatenating.

What are Date and Time Functions?
Date and time functions in a spreadsheet are built-in formulas that let you create, extract, calculate and format dates and times. They treat dates and times as serial numbers (date = integer days since epoch; time = fractional part of a day) so you can perform arithmetic and logical operations on them.

Key ideas:

• Dates are stored as whole numbers (days) and times as fractions of a day. For example, 1 means 1900-01-01 (in Excel’s default system) and 0.5 means 12:00 noon.
• Date/time functions help to: create dates/times, get parts (day, month, year, hours), compute differences, add/subtract intervals, find workdays, and format results for display.

Common functions and brief usage:

• TODAY() — returns the current date (updates automatically).
• NOW() — returns current date and time (updates automatically).
• DATE(year, month, day) — builds a date from components.
• TIME(hour, minute, second) — builds a time from components.
• DAY(date), MONTH(date), YEAR(date) — extract day, month, year.
• HOUR(time), MINUTE(time), SECOND(time) — extract time parts.
• DATEVALUE(date_text), TIMEVALUE(time_text) — convert text to date/time values.
• EDATE(start_date, months) — return date shifted by whole months.
• EOMONTH(start_date, months) — return last day of month after adding months.
• NETWORKDAYS(start_date, end_date, [holidays]) — count workdays (excludes weekends; optional holidays).
• WORKDAY(start_date, days, [holidays]) — returns a workday after adding business days.
• WEEKDAY(date, [return_type]) — gives weekday number (e.g., 1 = Sunday or Monday depending on type).
• WEEKNUM(date, [return_type]), ISOWEEKNUM(date) — week-of-year functions.
• DATEDIF(start_date, end_date, unit) — calculates difference in 'Y', 'M', 'D', 'MD', etc. (available in Excel; some spreadsheets provide equivalent).
• TEXT(value, format_text) — format a date/time into text using patterns like "dd-mmm-yyyy" or "hh:mm AM/PM".
• YEARFRAC(start_date, end_date, [basis]) — fraction of year between dates (used for interest calculations).

Important notes:

• When adding days to a date, you can simply use arithmetic: =A1 + 30 adds 30 days to date in A1.
• To add hours or minutes, add fractional day values: 1 hour = 1/24, 1 minute = 1/1440.
• Formatting is separate from the stored value. A cell may contain a date serial but show a formatted string like "12 Mar 2025".

How it helps in real life: plan deadlines, compute ages, calculate interest for exact days, generate due dates avoiding weekends and holidays, compute working hours, prepare employee tenure reports, and auto-fill today's date in invoices.

What is Conditional Formatting?
Conditional Formatting (CF) is a spreadsheet feature that changes the appearance of cells (color, font, border, icons, data bars) based on rules or conditions. It helps you quickly spot patterns, outliers, trends, and exceptions without manually checking each value.

Types of Conditional Formatting

• Highlight Cell Rules (greater than, less than, between, text contains, dates)
• Top/Bottom Rules (top 10 items, above average)
• Data Bars (bar inside a cell proportional to value)
• Color Scales (gradient colors by value: heatmap style)
• Icon Sets (arrows, flags, traffic lights)
• Custom Formula Rules (use any logical formula to control formatting)

How to apply (Excel / Google Sheets)

1. Select the target range (if using a formula, base it on the first cell in the range).
2. Excel: Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Google Sheets: Format > Conditional formatting > Custom formula is.
3. Enter the condition/formula (use relative/absolute references carefully: e.g. =A2>100 or =$B$1>A2).
4. Choose formatting (fill color, font color, icon set, etc.) and OK/Done.
5. Manage rules to change priority or use “Stop If True” (Excel) to prevent lower rules from applying.

Key tips

• When using a formula-based rule, write the formula as if it applies to the first cell of the selected range. The engine will auto-adjust for other cells.
• Use $ to fix row/column when needed: e.g. =$D$1 (fixed cell), $A2 (fixed column only).
• Combine functions: AND(...), OR(...), NOT(...), ISBLANK(...), TODAY().
• For chart coloring based on conditions, create helper columns/series and use rule-driven series colors (charts don’t inherit cell CF directly).

Why use Conditional Formatting (benefits)

• Immediate visual cues for exceptions (failures, overdue items, low stock).
• Helps in data analysis: spotting trends, clustering high/low values.
• Reduces manual checking and reporting time.

What is Data Validation?
Data Validation is a spreadsheet feature that restricts the type, range, or format of data that can be entered into a cell or range of cells. It helps prevent errors, enforce consistency and improve data quality.

Why use it?

• Prevents invalid entries (e.g., marks above 100 or negative quantities).
• Provides dropdown lists for consistent categorical input (e.g., subjects, product types).
• Shows input messages to guide users and error alerts to block or warn about wrong data.

Main types of validation rules

• Whole number — restrict to integers in a range (e.g., 0 to 100).
• Decimal — allow numeric values with decimals in a range.
• List — allow only values from a predefined list or named range (creates a dropdown).
• Date / Time — restrict to dates or times in a given range.
• Text length — limit number of characters.
• Custom — use a formula that returns TRUE or FALSE (powerful and flexible).

How to apply (typical steps in Excel / Google Sheets)

1. Select the cell(s) to protect.
2. Open Data > Data Validation (or Data > Validation in Google Sheets).
3. Choose the validation criteria (Whole number, List, Date, Custom formula, etc.).
4. Optionally set an Input Message to guide the user.
5. Set an Error Alert type: Stop (block), Warning, or Information.
6. Click OK / Save. Test by entering invalid data to see the alert.

Useful features and tips

• Use named ranges for lists to make dropdowns easy to manage.
• Copy validation to other cells (use Paste Special > Validation in Excel).
• Use conditional (custom) formulas to enforce complex rules, e.g., uniqueness or relational checks between columns.
• Excel has "Circle Invalid Data" to highlight entries that currently violate validation rules.

Overview
Sorting and filtering help you organize and view relevant records in a spreadsheet. "Data tools" are built-in features that clean, validate and summarise data so it is accurate and easy to analyse.

Sorting

• Definition: Rearranging rows based on values in one or more columns (e.g., alphabetically or numerically).
• Types: Single-level sort (one column), multi-level sort (e.g., by Class then by Marks), ascending/descending, and custom lists (e.g., small→medium→large).
• How to do it (general steps):
  • Select the whole data range (include headers).
  • Use Sort command; choose column, sort order, and add levels for additional columns.
  • Make sure "My data has headers" is checked so column titles stay at top.
• Notes: Use stable sorting (multi-level) to preserve previous order where appropriate.

Filtering

• Definition: Temporarily hide rows that don’t meet criteria so you only see relevant records.
• Types: AutoFilter (simple dropdowns per column), custom filters (e.g., > 75 and < 90), and Advanced Filter (complex criteria, copy results to another place).
• How to do it (general steps):
  • Turn on Filter (usually a funnel icon). Dropdown arrows appear on headers.
  • Choose checkboxes, or set text/number/date conditions or multiple criteria (AND/OR via Advanced Filter).
  • Use SUBTOTAL to compute sums/averages that ignore hidden rows created by Filter.

Key Data Tools

• Data Validation — restricts input (e.g., whole number 0–100, or a drop-down list of subjects). Helps prevent data-entry errors and enforce rules.
• Remove Duplicates — deletes duplicate rows based on selected columns (useful for cleaning lists).
• Text to Columns — split a column into multiple columns using a delimiter (comma, space) or fixed width (e.g., split "Name,Roll" into two columns).
• Consolidate — combine data from multiple ranges or sheets (sum, count, average) into one summary table.
• Subtotal — insert group subtotals after sorting (useful for category-wise totals). Works well with grouped/outline view.
• Goal Seek (What‑If Analysis) — find the input value needed to reach a desired result (e.g., what score needed on final test to reach target average).
• Pivot Table — powerful summarising tool to aggregate, group and filter large datasets (useful for quick cross-tab reports).

Practical tips

• Always keep a backup of raw data before removing duplicates or transforming text.
• Use headers and freeze panes to keep column titles visible while sorting/filtering large tables.
• When filtering, use SUBTOTAL rather than SUM to ignore hidden rows.

Summary: Sorting orders data, filtering selects relevant rows, and data tools validate, clean and summarise data — together they make data reliable and easy to analyse.

What is a Pivot Table?
A Pivot Table is an interactive summary tool in spreadsheet software (Excel, Google Sheets) that quickly groups, aggregates and analyses large datasets without changing the original data. It lets you rearrange ("pivot") rows and columns to view data from different angles.

Core components

• Rows – fields shown down the left side (categories).
• Columns – fields shown across the top (sub-categories).
• Values – numeric fields aggregated (SUM, COUNT, AVERAGE, etc.).
• Filters – fields used to filter the whole Pivot Table (e.g., Year, Region).

How to create a Pivot Table (basic steps)

1. Select a clean data range with headers (no fully blank rows/columns).
2. Insert > PivotTable (or Data > Pivot table in Google Sheets).
3. Place fields into Rows, Columns, Values and Filters areas.
4. Change summary function (Sum, Count, Average) or display options (Show Values As > % of Total, Running Total, etc.).
5. Use Refresh when source data changes.

Advanced features

• Grouping – group dates into Months/Quarters/Years or numeric ranges (bins).
• Calculated fields – create a field inside the Pivot to compute values (e.g., Sales = Units * UnitPrice).
• Slicers & Timelines – visual, clickable filters for user-friendly interaction.
• Drill-down – double-click a value to see the source records that form that aggregation.
• GETPIVOTDATA – function to fetch specific Pivot values into normal cells for custom reports.

What is a Pivot Chart?
A Pivot Chart is a chart linked to a Pivot Table. It updates and pivots together with the table, enabling visual, interactive analysis. Pivot Charts support most chart types: column, bar, line, pie, area, stacked, etc.

Why use them?

• Summarise large data quickly without complex formulas.
• Explore data interactively by dragging fields or using slicers.
• Create dashboard-ready visuals that update automatically when the pivot changes.

Practical tips

• Keep source data as a proper table (Excel Table or named range) so pivots auto-expand.
• Use "Show Values As" to get % of row/column/grand total without extra calculations.
• Refresh the pivot after changing source data; enable auto-refresh on file open if needed.
• Use distinct count (if available) to count unique items in a pivot.

What‑If Analysis in an electronic spreadsheet is the process of changing input values to see how those changes affect the results computed by formulas. It helps you test assumptions, compare scenarios and make decisions without changing the actual model permanently.

Common What‑If tools in spreadsheets:

• Manual change — directly edit input cells and observe results.
• Goal Seek — find the input value that produces a desired output (one input, one output).
• Data Table — show results for many values of one (one‑variable table) or two inputs (two‑variable table).
• Scenario Manager — store and compare several named sets of input values (scenarios) and view outcomes side by side.

How they work (brief)

1. Model: build a sheet where outputs are computed from one or more input cells using formulas (keep inputs separate and clearly labeled).
2. Change inputs: either manually or with a tool (Goal Seek / Data Table / Scenarios).
3. Observe outputs: results update automatically because formulas recalculate.

When to use each tool

• Use Goal Seek when you know the desired result and need the single input value that achieves it (e.g., what price gives target profit).
• Use Data Table to produce a table showing how an output varies across many possible input values (e.g., profit at different prices and quantities).
• Use Scenario Manager to compare complete sets of assumptions (e.g., Best/Worst/Most Likely sales scenarios).

Tips

• Keep input cells separate and named (use cell names for clarity).
• Lock or protect formulas to avoid accidental overwrite.
• Check results for plausibility (e.g., negative quantities often indicate wrong inputs or formulas).

What is Error Handling?
Error handling in a spreadsheet means detecting, interpreting and responding to formula errors so the worksheet remains correct, readable and useful. Common error values include #DIV/0! (divide by zero), #VALUE! (wrong type), #REF! (invalid reference), #NAME? (unrecognized name or function), #N/A (value not available), #NUM! (invalid numeric computation), and #NULL! (incorrect intersection).

What is Formula Auditing?
Formula auditing is the process and set of tools used to inspect, trace and validate formulas and cell relationships so errors can be found and corrected. Auditing helps you understand how a result was produced and identify broken references or unintended dependencies.

Why it matters
In real-world spreadsheets (budgets, sales reports, lab data), an unnoticed error can produce misleading totals, bad decisions, or reporting mistakes. Good error handling preserves data quality and makes spreadsheets robust and user-friendly.

Common auditing features and how to use them

• Show Formulas — displays formulas instead of results so you can scan for typos and wrong ranges.
• Trace Precedents — draws arrows from cells used in the current cell's formula; useful to see inputs.
• Trace Dependents — shows which cells depend on the current cell; useful before changing or deleting a cell.
• Remove Arrows — clears precedent/dependent arrows.
• Evaluate Formula — steps through calculation parts to find where an error begins.
• Watch Window — monitors important cells across sheets so you can track values/formulas while editing elsewhere.
• Error Checking — identifies common issues and suggests fixes (e.g., inconsistent formulas in a region).
• Circular Reference Detection — warns when a formula refers (directly or indirectly) to its own cell; these require manual correction or iterative calculation settings.

Principles for robust error handling

• Anticipate likely errors (division by zero, missing lookup keys) and handle them with functions like IFERROR or conditional tests.
• Prefer specific tests where appropriate (e.g., ISNA for lookup misses) so you don’t hide other bugs.
• Keep formulas readable: use named ranges, break long formulas into helper columns, and document assumptions in comments or a README sheet.
• Use auditing tools regularly when changing structure or copying formulas across ranges.

Example error-handling patterns

• Replace error with friendly output: =IFERROR(A2/B2, "-" ) to show a dash instead of #DIV/0!.
• Test for missing lookup: =IFNA(VLOOKUP(E2,Table,2,FALSE), "Not Found") — shows a message when key absent.
• Validate inputs before calculating: =IF(B2=0, 0, A2/B2) to avoid division by zero without hiding other errors.

Workflow to audit and fix an error

1. Locate the error cell. If many errors, use Error Checking or filter the column.
2. Use Show Formulas to inspect formulas or Trace Precedents to find the source cells.
3. Use Evaluate Formula to step through subexpressions and find the failing operation.
4. Decide whether to (a) correct the underlying data/reference, (b) change the formula, or (c) wrap the result with an error-handling function like IFERROR/IFNA.
5. Re-run audits (Trace Dependents, recalculation) to ensure changes didn’t break dependent calculations.

Overview
Protection, security and collaboration are features used with electronic spreadsheets (Excel, Google Sheets, LibreOffice Calc) to keep data safe, control who can view or change it, and allow multiple users to work together reliably.

Protection
Protection prevents accidental or deliberate modification of important parts of a sheet or workbook. Basic protection tasks are:

• Locking cells: mark cells as locked/unlocked and then enable sheet protection so only unlocked cells can be edited.
• Protecting worksheet structure: prevents adding, deleting, renaming or moving sheets.
• Password protecting files: encrypts the file so it cannot be opened without the password.
• Protecting ranges: allow only specific users to edit certain ranges (supported in Google Sheets/Excel with permissions).

Security
Security covers confidentiality, integrity and availability of spreadsheet data. Important practices include:

• Access control: use file passwords, folder permissions, or cloud-sharing settings to restrict who can view or edit.
• Data validation: restrict what users can enter (e.g., numbers only, list choices, date ranges).
• Audit & versioning: keep version history and change logs; enable track changes or use cloud version history to review edits and restore prior versions.
• Backups: keep regular backups and copies in secure locations.
• Digital signatures and file integrity checks: sign a workbook or use checksum tools to detect tampering.

Collaboration
Collaboration allows several users to work on the same spreadsheet concurrently or sequentially:

• Co-authoring: multiple users edit simultaneously (Google Sheets and modern Excel with OneDrive/SharePoint).
• Comments and notes: discuss cells without changing values; resolve comments when addressed.
• Track changes / Suggesting mode: see who changed what and accept/reject edits.
• Merging / combining data: merge copies or import ranges when offline edits must be reconciled.

How protection, security and collaboration work together
A typical secure collaborative workflow balances openness with control: share the workbook with collaborators, allow editing only in specific unlocked ranges, use data validation to prevent incorrect entries, keep version history and backups, and apply file encryption for sensitive data.

Common menus & steps (examples)

• Excel: Lock cells (Format Cells > Protection > Locked), then Review > Protect Sheet (set password). Encrypt file: File > Info > Protect Workbook > Encrypt with Password.
• Google Sheets: Protect sheet/range via Data > Protect sheets and ranges; set editor permissions. Share via Share button and set Viewer/Commenter/Editor roles. Use Version history to restore.
• LibreOffice Calc: Format > Cells > Cell Protection; Tools > Protect Sheet/Document; File > Digital Signatures.

Best practices

• Only give edit access to trusted users; prefer Commenter/Viewer roles when appropriate.
• Use data validation and conditional formatting to reduce errors.
• Keep an audit trail: enable track changes or rely on cloud version history.
• Use strong passwords for encryption and change them periodically.
• Keep sensitive data (e.g., salaries, personal identifiers) in separate, highly restricted files.

What it means
Importing, exporting and interoperability describe how spreadsheet data moves between systems and file formats so different programs (spreadsheets, databases, web services) can share information. Importing = bringing external data into your spreadsheet. Exporting = saving or sending spreadsheet data out to other systems. Interoperability = ensuring data keeps its meaning and usability across different applications.

Importing — key steps

• Preview: look at a sample of the file (CSV, TXT, XML, JSON, XLSX, HTML).
• Choose delimiter and encoding: CSVs often use comma or semicolon; choose UTF-8 to avoid character problems.
• Set headers and data types: tell the importer which row is the header and which columns are dates, numbers or text.
• Clean and parse: trim spaces, remove non-printing characters, split combined fields (e.g., full name → first/last).
• Map fields: map source columns to your spreadsheet columns (often in an import wizard or Power Query).

Exporting — key points

• Choose format by recipient: use CSV/TSV for databases or web upload, XLSX/ODS to preserve formulas and formatting, PDF for printing.
• Decide whether to export values or formulas: CSV exports values only; XLSX preserves formulas.
• Set locale/encoding and delimiters so the receiving system reads numbers and dates correctly.
• Document column headers and units to avoid ambiguity.

Interoperability — common issues & solutions

• Format loss: formats, charts, macros and pivot tables may not survive conversion (e.g., XLSX → CSV). Solution: keep a master XLSX and export copies.
• Formula differences: function names or behavior may differ between programs (Excel vs Google Sheets). Solution: use simple, standard functions or recalculate after import.
• Date and number locale problems: 01/02/2023 can be Jan 2 or Feb 1. Solution: use ISO dates (YYYY-MM-DD) or set locale during import.
• Encoding and special characters: non-UTF-8 can break characters. Solution: use UTF-8 when exporting/importing.
• Mismatched delimiters: commas inside text fields. Solution: use proper quoting or choose a different delimiter.

Tools and techniques

• Built-in import wizards (File > Open / Data > Get External Data).
• Power Query / Get & Transform (Excel) for repeatable, transformable imports.
• Google Sheets functions (IMPORTRANGE, IMPORTDATA, IMPORTXML, IMPORTHTML) for live web or cross-sheet imports.
• CSV for universal tabular transfer; JSON or XML for structured hierarchical data; ODBC/ODBC connectors and APIs for databases.

Best practices

• Keep a single master file; exchange derived copies as needed.
• Always document column headers, units and date format.
• Remove merged cells and avoid implicit formatting before exporting.
• Validate imported data (row counts, totals, sample checks).
• Automate repetitive imports with Power Query or scripts (VBA, Apps Script).

Typical workflow example
A school receives student marks from a testing system as CSV. The teacher: (1) opens import wizard, selects UTF-8 & comma delimiter, (2) marks first row as headers, (3) converts dates using DATEVALUE or sets column as Date, (4) trims whitespace and validates totals, (5) saves master workbook as XLSX and exports a copy as CSV to upload to the school portal.

Overview
Printing, Page Setup and Presentation covers how to prepare an electronic spreadsheet for hard‑copy or PDF output and how to format it so printed reports are clear, professional and readable. It includes configuring page size and orientation, margins and scaling, selecting print areas and titles, headers/footers, page breaks and visual layout choices to produce good printed output.

Key page setup options

• Orientation: Portrait (tall) or Landscape (wide) depending on the sheet width.
• Paper size: A4, Letter, Legal, etc.
• Margins: Top, bottom, left, right; use narrow margins to fit more columns or wider for binding space.
• Scaling / Fit to: Fit sheet to X pages wide by Y pages tall or scale by percentage to shrink/expand printout.
• Print area: Define a specific cell range to print; avoids printing unnecessary cells.
• Print titles: Repeat header rows/columns (e.g., column headings) on every printed page.
• Gridlines & headings: Optionally print cell gridlines and row/column headings (A, B, 1, 2).
• Headers & footers: Insert page numbers, date/time, filename, author, or custom text.
• Page breaks: Automatic and manual page breaks define where pages start/end; use Page Break Preview or Page Layout view to adjust.
• Print selection / active sheet / entire workbook: Choose whether to print only selected ranges, the current sheet, or all workbook sheets.

Presentation (print-friendly formatting)

• Use consistent fonts and sizes: body text 10–12 pt for readability on A4/Letter.
• Align numbers right, text left; use thousands separators and set decimal places for currency.
• Use borders and subtle shading to separate header rows; avoid heavy colors that print poorly in grayscale.
• Keep column widths and row heights appropriate so data is not cut off; wrap text where needed.
• Use conditional formatting, data bars or sparklines to highlight key values for a printed summary.
• Place charts on the same sheet or a separate chart sheet; ensure chart size fits chosen page orientation.
• Check Print Preview and do a test print (or export to PDF) to confirm layout before final printing.

Practical steps (typical workflow)

1. Design the sheet with final printed layout in mind (headers, column widths, fonts).
2. Select the range you want to print and set Print Area.
3. Open Page Setup (or Page Layout tab): set orientation, size, margins and scaling; set Print Titles for repeating headers.
4. Adjust page breaks in Page Break Preview or Page Layout view so rows/columns don’t split awkwardly.
5. Add headers/footers (page numbers, date, filename).
6. Enable/disable gridlines and headings as required; preview in Print Preview.
7. Export to PDF or print a test page; tweak layout and recheck.

Tips

• If you need the sheet to fit on one page wide, use Fit to 1 page wide and allow height to span multiple pages, or Fit to 1 by 1 if a single‑page report is required (may reduce font size).
• Center content horizontally/vertically on the page for a polished look.
• For multi‑page reports, repeat the heading row(s) and include page numbers in the footer.
• Use Print Preview frequently — it shows exactly what will print and highlights page breaks.

Overview
Templates, macros and automation are tools in electronic spreadsheets (Excel, Google Sheets, LibreOffice Calc) that save time, reduce errors and make repetitive work consistent. Templates provide predefined spreadsheet layouts and formulas. Macros are recorded or coded sequences of actions that you can run to perform repetitive tasks automatically. Automation combines formulas, built‑in features (like conditional formatting, data validation, pivot tables) and macros/scripts to streamline workflows.

Templates
A template is a ready‑to‑use spreadsheet file with layout, styles, headings, formulas, and sample data. Use templates when you need the same structure repeatedly (invoices, attendance sheets, monthly budgets). Benefits: uniform layout, fewer setup steps, built‑in formulas and charts.

• How to create a template:
  1. Design the worksheet: headers, frozen panes, column widths, labels.
  2. Add formulas, data validation and sample data.
  3. Remove specific data (leave placeholders) and save as a template file (.xltx, .xlt, .ots) or a normal workbook to copy as a master.

Macros
A macro records or runs code to automate repetitive steps (formatting, sorting, copying, calculations). In Excel macros are often written in VBA (Visual Basic for Applications); in Google Sheets you can record macros or write Google Apps Script (JavaScript‑based).

• Types: Recorded macros (captures keystrokes & actions) and scripted macros (editable code).
• Typical macro uses: apply consistent formatting, create monthly reports, generate PDFs from worksheets, automate import/cleanup tasks.
• Security: Macros can contain harmful code. Only enable macros from trusted sources and use protected workbooks where needed.

Example macro (VBA-like, simplified)

Sub FormatReport()
  Range("A1:G1").Font.Bold = True
  Columns("A:G").AutoFit
  Range("A2:G50").Sort Key1:=Range("C2"), Order1:=xlDescending
End Sub

Google Apps Script example (simplified)

function formatReport() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1:G1').setFontWeight('bold');
  sheet.autoResizeColumns(1,7);
}

Automation (broader)
Automation uses formulas, functions, built‑in tools and macros/scripts to reduce manual work. Common automation building blocks:

• Formulas/functions (SUM, IF, VLOOKUP/XLOOKUP, COUNTIF, SUMIF) to compute values automatically.
• Conditional formatting to highlight important values (low stock, failing grades).
• Data validation to restrict input (drop‑down lists for categories).
• Pivot tables for summarized reports and quick analysis.
• Charts that update automatically with source data.
• Scheduled scripts/triggers (in Google Sheets or using Windows Task Scheduler with Excel) to run reports automatically.

When to use which:

• Use a template when you need a consistent layout and repeated use (e.g., monthly invoice form).
• Use a macro when you need to repeat a sequence of actions quickly (e.g., format, filter, export PDF).
• Combine both: place macros/scripts inside a template so every new file already contains automation.

Practical tips for students

• Record a macro to learn the steps, then view the generated code to understand how actions map to code.
• Keep templates simple and clearly label input cells (use light fill colors or borders).
• Test automation on a copy of data before using on real data.

Summary
Templates save setup time and enforce consistency. Macros automate repetitive actions. Automation combines formulas, validation, formatting, pivot tables and macros/scripts to create efficient, low‑error spreadsheet solutions useful in real life (invoices, payroll, gradebooks, inventory).

Overview
Good spreadsheet design makes work accurate, easy to understand, maintainable and reusable. It reduces errors, improves performance and communicates results clearly. Follow planning, consistent layout, clear formulas and documentation.

• Plan before you build — sketch required inputs, outputs, intermediate calculations and reports. Decide how many sheets you need (raw data, calculations, reports).
• Keep raw data separate — store unmodified source data on one sheet (or table). Use other sheets for calculations and reporting. This prevents accidental changes to source records.
• Use structured tables — convert data ranges to tables (Excel: Insert > Table). Tables auto-expand, allow structured references and make formulas clearer.
• Avoid hardcoding numbers in formulas — place constants (tax rates, thresholds) in clearly labeled cells and reference them with cell addresses or named ranges.
• Name important ranges — use named ranges for key inputs (e.g., TaxRate). Names improve readability of formulas and reduce errors.
• Use helper columns — break complex formulas into simpler steps across columns; this helps debugging and increases clarity.
• Prefer functions over manual steps — SUM, AVERAGE, lookup and conditional functions are less error-prone than manual edits.
• Use absolute and relative references correctly — use $ (e.g., $A$1) for constants so formulas copy properly. Understand when to lock rows, columns or both.
• Apply Data Validation — restrict inputs with lists, number limits or date ranges to reduce input errors (Data > Data Validation).
• Use Conditional Formatting sensibly — highlight important values, outliers or duplicates, but avoid excessive rules that distract.
• Format consistently — use consistent number formats, fonts, colors and alignments. Use cell styles for headings, inputs and outputs so users know where to edit.
• Document assumptions and calculations — add a cover sheet or a comments area listing version, author, data sources and assumptions.
• Protect sheets and ranges — lock formula cells to prevent accidental changes while leaving input cells editable.
• Optimize performance — avoid entire-column references in large workbooks, limit volatile functions (NOW, INDIRECT), and reduce unnecessary array formulas.
• Keep one purpose per sheet — e.g., raw data on one sheet, pivot tables and charts on another. This makes maintenance and reuse easier.
• Use consistent file naming and version control — include date/version in file names, keep backups or use cloud version history.
• Test and validate — perform checks using totals, sample checks, and cross-sheet reconciliations. Use COUNT/COUNTIF to spot missing data.

Design tips for readability

• Left-align text, right-align numbers, and center short headings.
• Use borders sparingly to separate sections; avoid merged cells where possible (they break sorting/filtering).
• Provide clear column headings and freeze panes so headings stay visible.

Error handling and auditing
Use ISERROR/IFERROR to show friendly messages instead of #N/A or #DIV/0!. Use formula auditing tools (trace precedents/dependents) and show intermediate checks so users can validate results.