Excel - Introduction

Introduction

  • Participants: Ben, Sumodun, Atmos

  • Session focused on learning both Excel and Google Sheets.

  • Acknowledgment of prior experience with Excel by participants, which is why the session emphasizes Google Sheets' cloud features while utilizing shared spreadsheet logic.

Overview of Google Sheets vs Microsoft Excel

  • Shared Core Logic: Both applications use the same fundamental logic for data entry, calculations, and organization.

  • Google Sheets:

    • Cloud-based and updated regularly.

    • Easier sharing and real-time collaboration capabilities.

    • No need for installation.

  • Microsoft Excel:

    • Traditionally a desktop application (though web versions exist).

    • Known for handling larger data sets and more complex advanced data analysis tools.

Core Components (Applicable to both Excel and Google Sheets)

  • Cells: Intersection of rows and columns.

  • Columns: Defined by letters (A, B, C, …).

  • Rows: Called records, defined by numbers (1, 2, 3, …).

Key Concepts and Terminology

  • Definitions:

    • Records: Equivalent to rows in data industry terminology.

    • Columns: Also known as attributes or fields.

  • Importance: Understanding these terms is crucial for technical interviews.

  • Cell Addressing:

    • E.g., P4 refers to the intersection of column P and row 4.

    • Cell address format: ColumnNameRowNumber (e.g., C2, D2).

Entering Formulas (Shared Functionality)

  • Formula Box: Located at the top of the sheet in both programs.

  • Syntax: All formulas must start with an = sign.

  • Best Practices:

    • Incorrect Method: Manually inputting numbers (e.g., 14 + 3).

    • Correct Method: Using cell references (e.g., =C2 + D2).

    • Benefits: Results automatically update if the values in referenced cells change.

Basic Functions

  • Aggregate Functions: Useful for summarizing data in both platforms:

    • SUM: Adds all numbers in a range.

    • AVERAGE: Calculates the mean of a range of numbers.

    • MAX: Finds the maximum value in a range.

    • MIN: Finds the minimum value in a range.

    • COUNT: Counts the number of cells containing numbers in a range.

  • Formula Example: =AVERAGE(B2:B11) to find the mean of the specified range.

Conditional Functions and Logical Statements

  • IF Function:

    • Used to evaluate conditions and return specific values.

    • Syntax: =IF(logical_test, value_if_true, value_if_false).

    • Example: Determine pass/fail based on a score (e.g., > 50 indicates a pass).

Sorting and Filtering Data

  • Sorting: Organizing data in Ascending or Descending order.

  • Conditional Formatting: Highlighting cells based on specific rules (e.g., marking values above a certain threshold).

  • Data Cleaning: Tools available in both programs to remove duplicates and ensure data integrity.

Practical Application and Accessibility

  • Collaboration: Google Sheets allows real-time teamwork via the cloud.

  • Convenience: Access sheets from any device with internet access.

Conclusion of the Session

  • Review: Reiteration of foundational concepts (Rows, Columns, IF statements, and Aggregates).

  • Practice: Attendees are encouraged to use the provided practice sheet to build proficiency in both environments.