Spreadsheet Notes A2 chap 4

Importance of Spreadsheets

• Crucial for many careers, even outside ICT.
• Useful for:
  • Handling financial statements
  • Arranging documents and schedules
  • Organizing information and data

Introduction to Spreadsheet Software

• Facilitates data processing.
• Assists in:
  • Complicated calculations
  • Data visualization using graphs/charts
  • Processing tabular data

Features of Spreadsheet Software

• Formulae and pre-defined functions.
• Sorting and filtering.
• Conditional formatting.
• Graphs and charts.

Examples of Spreadsheet Software

• Microsoft Excel, Google Sheets, LibreOffice Calc.

Workspace Components

• Quick Access Toolbar: Commands of your choice.
• Tabs: Different commands in the ribbon.
• Ribbon: Displays commands for the current tab.
• Name Box: Shows the current cell's address.
• Formula Bar: Shows the editable formula in the current cell.
• Column Letters: Identify the columns.
• Row Numbers: Identify the rows.
• List of Worksheets: Displays the worksheets.

Cell Address

• Identified by a column letter and a row number (e.g., B4).

Data Entry

• Methods to input/change data:
  • Select cell, enter data (replaces original data).
  • Double-click cell, enter data.
  • Select cell, click formula bar, enter data.
• To delete content, select cell and press “Delete”.

Cell Format

• Data can be stored in different number formats (default is “General”).
• Other formats change cell appearance (wrap text, merge and center, column width/row height, table style).

Editing Skills

• Freeze Panes: Keeps table headers visible while scrolling.
• AutoFill: Fills multiple adjacent cells automatically (copy cells or fill series).

Workbook and Worksheet

• A workbook can contain multiple worksheets (e.g., "Form 4.xlsx" with "Class 4A", "Class 4B", and "Class 4C" worksheets).

Formula

• An expression that evaluates automatically.

Constants

• Numbers, text strings, and Booleans.

Operators

• Symbols for operations between constants, cell references, and functions.
• Examples: +, -, *, /, & (concatenation)

Cell Reference

• Identifies a cell or range of cells by their addresses.
• Can refer to cells in other worksheets or workbooks (external cell references).

Functions

• Predefined formulae for calculations.
• Require arguments.
• Example: $=LARGE(range, k)$

Types of Functions

• Mathematical, logical, text, informational, statistical, lookup.

Mathematical Functions

• $SUM(number1, number2, …)$
• $SUMIF(range, criteria, sum_range)$
• $ABS(number)$
• $INT(number)$
• $RAND()$
• $SQRT(number)$
• $ROUND(number, num_digits)$
• $ROUNDDOWN(number, num_digits)$
• $ROUNDUP(number, num_digits)$
• $RANDBETWEEN(bottom, top)$
• $POWER(number, power)$

Logical Functions

• $AND(logical1, logical2, …)$
• $OR(logical1, logical2, …)$
• $NOT(logical)$
• $IF(logical_test, value_if_true, value_if_false)$

Text Functions

• $CHAR(number)$
• $LEN(text)$
• $LEFT(text, num_chars)$
• $MID(text, start_num, num_chars)$
• $RIGHT(text, num_chars)$
• $LOWER(text)$
• $UPPER(text)$

Information Functions

• $ISBLANK(value)$
• $ISNUMBER(value)$
• $ISTEXT(value)$
• $ISEVEN(value)$
• $ISODD(value)$

Statistical Functions

• $AVERAGE(number1, number2, …)$
• $COUNT(value1, value2, …)$
• $COUNTA(value1, value2, …)$
• $COUNTBLANK(range)$
• $COUNTIF(range, criteria)$
• $MAX(number1, number2, …)$
• $MIN(number1, number2, …)$

Lookup Functions

• $VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)$
• $HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)$

Cell References

Types

• Relative: Default, changes when copied.
• Absolute: Uses $, remains unchanged when copied.</li> <li>Mixed: One$, either column or row remains unchanged.

Data Manipulation and Presentation

Filtering

• Hides rows temporarily based on criteria.

Sorting

• Organizes data in ascending or descending order.

Chart Types

• Column chart, clustered column chart, stacked column chart, line chart, pie chart.

Data Analysis Tools

What-if Analysis

• Scenario manager, Goal Seek.

Pivot Table

• Summarizes data for analysis.