M2 - Excel / Google Sheets

Spreadsheet Basics

Workbook Structure

A workbook is the primary file in spreadsheet applications like Excel and Google Sheets, containing one or more worksheets. Each workbook can be thought of as a folder that houses various sheets where data is organized. The structure of a workbook includes:

• Worksheets: Individual pages within a workbook, each capable of holding data, formulas, and charts.
• Tabs: Located at the bottom of the workbook, tabs allow users to navigate between different worksheets.
• Navigation: Users can easily switch between sheets by clicking on the respective tab.

Example: In a workbook named "Sales Data", you might have worksheets for "Q1", "Q2", and "Q3". Each sheet contains sales figures for that quarter.

Key Points:

• A workbook can contain multiple worksheets.
• Worksheets can be renamed and reordered.
• Understanding the structure is crucial for efficient data management.

Worksheets and Ranges

A worksheet is a grid of cells organized in rows and columns where users input data. Each cell can hold text, numbers, or formulas. A range refers to a selection of two or more cells.

• Rows are numbered (1, 2, 3, ...), while columns are labeled with letters (A, B, C, ...).
• A range can be specified by the top-left cell and the bottom-right cell (e.g., A1:B10).

Example: If you want to sum values in cells A1 to A10, you would refer to this range as A1:A10.

Key Points:

• Ranges can be contiguous (e.g., A1:A10) or non-contiguous (e.g., A1, A3, A5).
• You can perform operations on ranges, such as summing or averaging values.
• Understanding ranges is essential for applying formulas and functions effectively.

Cell Referencing

Cell referencing is crucial for creating dynamic formulas. There are three types of cell references:

1. Relative References: Change when the formula is copied to another cell (e.g., A1).
2. Absolute References: Remain constant regardless of where the formula is copied (e.g., $A$1).
3. Mixed References: A combination of relative and absolute (e.g., A$1 or $A1).

Example: If you have a formula in cell B1 that references A1, copying it to B2 will change the reference to A2 (relative). If the formula is $A$1, it will always refer to A1, no matter where it is copied.

Key Points:

• Use relative references for calculations that need to adjust based on position.
• Use absolute references when you want to keep a specific cell constant.
• Understanding these references is key for efficient formula creation.

Formulas and Functions

Formula Fundamentals

Formulas are expressions that perform calculations on values in your spreadsheet. They always start with an equal sign (=). A basic formula can include numbers, cell references, and operators.

• Operators: Common operators include:
  • Addition (+)
  • Subtraction (-)
  • Multiplication (*)
  • Division (/)

Example: To add values in cells A1 and A2, you would write:

=A1 + A2  

Key Points:

• Formulas can be simple or complex, involving multiple operations.
• Use parentheses to control the order of operations (e.g., =(A1 + A2) * A3).
• Always start with an equal sign.

Mathematical Functions

Mathematical functions are predefined formulas that perform calculations on data. Common mathematical functions include:

• SUM: Adds a range of cells.
• AVERAGE: Calculates the mean of a range.
• MIN: Returns the smallest value in a range.
• MAX: Returns the largest value in a range.

Example: To calculate the total of cells A1 to A10, you would use:

=SUM(A1:A10)  

Key Points:

• Functions simplify complex calculations.
• Functions can be nested within each other (e.g., =SUM(A1:A10) / COUNT(A1:A10)).
• Familiarity with functions enhances data analysis capabilities.

Text Functions

Text functions manipulate text strings in cells. Common text functions include:

• CONCATENATE: Joins two or more text strings.
• LEFT: Extracts a specified number of characters from the left.
• RIGHT: Extracts a specified number of characters from the right.
• LEN: Returns the length of a text string.

Example: To join text in cells A1 and B1, you would write:

=CONCATENATE(A1, B1)  

Key Points:

• Text functions are useful for data cleaning and formatting.
• They can be combined with other functions for advanced manipulation.
• Understanding text functions is essential for effective data presentation.

Date and Time Functions

Date and time functions are essential for managing temporal data. Common functions include:

• TODAY: Returns the current date.
• NOW: Returns the current date and time.
• DATEDIF: Calculates the difference between two dates.
• YEAR, MONTH, DAY: Extract respective components from a date.

Example: To find the current date, you would use:

=TODAY()  

Key Points:

• Date and time functions help in tracking and analyzing time-based data.
• They can be formatted to display in various styles.
• Understanding these functions is crucial for effective project management and reporting.