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.

Logical Functions

IF Function

The IF function is a powerful tool in Excel and Google Sheets that allows you to perform logical tests and return different values based on whether the test is TRUE or FALSE. The syntax is IF(logical_test, value_if_true, value_if_false). For example, =IF(A1 > 10, "Over 10", "10 or less") checks if the value in cell A1 is greater than 10. If TRUE, it returns "Over 10"; if FALSE, it returns "10 or less". Key Points:

• The logical test can involve comparisons (>, <, =).
• You can nest multiple IF functions for more complex conditions.
• Use quotes for text values.

Example:

=IF(B2="Yes", "Approved", "Denied")

AND Function

The AND function is used to test multiple conditions at once. It returns TRUE only if all conditions are TRUE. The syntax is AND(condition1, condition2, ...). For instance, =AND(A1 > 10, B1 < 5) checks if A1 is greater than 10 and B1 is less than 5. If both conditions are met, it returns TRUE; otherwise, it returns FALSE. Key Points:

• Combine AND with IF for complex logical tests.
• You can test up to 255 conditions.

Example:

=IF(AND(A1 > 10, B1 < 5), "Valid", "Invalid")

OR Function

The OR function checks if at least one of the conditions is TRUE. It returns TRUE if any condition is met. The syntax is OR(condition1, condition2, ...). For example, =OR(A1 > 10, B1 < 5) returns TRUE if either A1 is greater than 10 or B1 is less than 5. Key Points:

• Use OR with IF to create flexible conditions.
• Like AND, you can test up to 255 conditions.

Example:

=IF(OR(A1 > 10, B1 < 5), "At least one condition met", "No conditions met")

Nested Logic

Nested logic involves using multiple logical functions together to create complex conditions. For instance, you can nest IF functions within each other to handle multiple scenarios. The syntax remains the same, but you can add additional IF statements. An example would be =IF(A1 > 10, "Over 10", IF(A1 = 10, "Exactly 10", "Under 10")). This checks if A1 is over, exactly, or under 10. Key Points:

• Nesting allows for more detailed decision-making.
• Keep track of parentheses to avoid errors.

Example:

=IF(AND(A1 > 10, B1 < 5), "Condition 1", IF(OR(A1 = 10, B1 = 5), "Condition 2", "Condition 3"))

Lookup Functions

VLOOKUP

The VLOOKUP function is used to search for a value in the first column of a table and return a value in the same row from a specified column. The syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP(A1, B1:D10, 2, FALSE) looks for the value in A1 within the range B1:D10 and returns the corresponding value from the second column. Key Points:

• The first column of the range must contain the lookup value.
• Use FALSE for an exact match and TRUE for an approximate match.

Example:

=VLOOKUP("Apple", A2:C10, 2, FALSE)

HLOOKUP

The HLOOKUP function works similarly to VLOOKUP but searches for a value in the first row of a table and returns a value from a specified row. The syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). For example, =HLOOKUP(A1, A1:E5, 3, FALSE) searches for A1 in the first row and returns the value from the third row. Key Points:

• The first row of the range must contain the lookup value.
• Use FALSE for an exact match.

Example:

=HLOOKUP("Sales", A1:E5, 3, FALSE)

XLOOKUP

The XLOOKUP function is a more versatile replacement for VLOOKUP and HLOOKUP, allowing for both vertical and horizontal lookups. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). For example, =XLOOKUP(A1, B1:B10, C1:C10, "Not Found") searches for A1 in B1:B10 and returns the corresponding value from C1:C10. Key Points:

• XLOOKUP can return values from any column or row.
• It includes options for handling not found cases.

Example:

=XLOOKUP("Product1", A2:A10, B2:B10, "Not Found")

INDEX and MATCH

The INDEX and MATCH combination is a powerful alternative to VLOOKUP and HLOOKUP. The INDEX function returns a value from a specified position in a range, while MATCH finds the position of a value in a range. The syntax for INDEX is INDEX(array, row_num, [column_num]) and for MATCH is MATCH(lookup_value, lookup_array, [match_type]). For example, =INDEX(B1:B10, MATCH(A1, A1:A10, 0)) retrieves the value from B1:B10 that corresponds to the position of A1 in A1:A10. Key Points:

• This combination allows for more flexibility in data retrieval.
• It can look up values in any direction.

Example:

=INDEX(B1:B10, MATCH("Product1", A1:A10, 0))

Data Cleaning

Removing Duplicates

Removing duplicates is a crucial step in data cleaning to ensure data integrity. In Excel, you can easily remove duplicates by selecting your data range, navigating to the "Data" tab, and clicking on "Remove Duplicates." This will prompt you to select which columns to check for duplicates. In Google Sheets, you can use the "Data" menu and select "Data cleanup" followed by "Remove duplicates."

Key Points:

• Always back up your data before removing duplicates.
• Consider the context: sometimes, duplicates may be valid entries.
• Use conditional formatting to highlight duplicates before removal.

Example: If you have a list of customer emails and want to ensure each email is unique, applying the remove duplicates function will help maintain a clean dataset.

Handling Missing Values

Handling missing values is essential for maintaining data quality. In Excel, you can identify missing values using the "ISBLANK" function. For example:

=ISBLANK(A1)

This formula returns TRUE if cell A1 is empty. You can choose to fill missing values with a placeholder, average, or median. In Google Sheets, you can use the "IF" function to manage missing data, such as:

=IF(ISBLANK(A1), "N/A", A1)

This replaces any blank cells with "N/A."

Key Points:

• Assess the impact of missing values on your analysis.
• Use imputation techniques to fill gaps if necessary.
• Document your approach to handling missing data.

Text Cleanup

Text cleanup involves standardizing and correcting text data for consistency. In Excel, functions like "TRIM" can remove extra spaces:

=TRIM(A1)

This removes leading and trailing spaces from the text in cell A1. Additionally, the "LOWER" or "UPPER" functions can standardize text case. In Google Sheets, similar functions are available.

Key Points:

• Standardize text formats to avoid discrepancies.
• Use "FIND" and "REPLACE" to correct common typos.
• Regular expressions can be employed for advanced text cleanup in Google Sheets.

Data Validation

Data validation ensures that the data entered into your spreadsheet meets specific criteria. In Excel, you can set data validation rules by selecting a cell, going to the "Data" tab, and clicking on "Data Validation." You can restrict entries to a list, a range of numbers, or specific dates. In Google Sheets, similar options are available under the "Data" menu.

Key Points:

• Use dropdown lists for standardized entries.
• Set error messages to guide users when invalid data is entered.
• Regularly review and update validation rules as needed.

Pivot Tables

Pivot Table Fundamentals

Pivot tables are powerful tools for summarizing and analyzing data. In Excel, you can create a pivot table by selecting your data range and navigating to "Insert" > "PivotTable." In Google Sheets, use "Data" > "Pivot table." A pivot table allows you to rearrange data dynamically, providing insights without altering the original dataset.

Key Points:

• Understand the layout: Rows, Columns, Values, and Filters.
• Use drag-and-drop functionality to customize your pivot table.
• Refresh your pivot table to reflect changes in the source data.

Aggregations and Summaries

Pivot tables allow for various aggregations such as sums, averages, and counts. In Excel, you can change the aggregation method by clicking on the value field settings. For example, if you want to calculate the average sales, select the field, then choose "Value Field Settings" and select "Average." In Google Sheets, similar options are available.

Key Points:

• Choose the right aggregation method based on your analysis needs.
• Use calculated fields for custom calculations within pivot tables.
• Summarize data effectively to identify trends and patterns.

Pivot Charts

Pivot charts provide a visual representation of pivot table data. In Excel, after creating a pivot table, you can insert a pivot chart by selecting "Insert" > "PivotChart." In Google Sheets, use the chart editor to create a chart based on your pivot table. Pivot charts are dynamic and will update automatically when the pivot table changes.

Key Points:

• Choose the right chart type for your data (e.g., bar, line, pie).
• Customize your chart with titles, labels, and colors for better clarity.
• Use filters in your pivot chart to focus on specific data segments.

Conditional Formatting

Highlighting Rules

Conditional formatting allows you to apply specific formatting to cells based on their values. This feature is crucial for quickly identifying trends and outliers in your data. Highlighting Rules include options such as 'Greater than', 'Less than', 'Between', and 'Equal to'. To apply a highlighting rule in Excel or Google Sheets, follow these steps:

1. Select the range of cells you want to format.
2. Go to the 'Home' tab in Excel or 'Format' in Google Sheets.
3. Click on 'Conditional Formatting'.
4. Choose 'Highlight Cells Rules' and select your desired rule.
5. Set the value and choose the formatting style (e.g., fill color).

For example, if you want to highlight all sales figures greater than $1,000, you would set the rule accordingly. This visual cue helps stakeholders quickly assess performance metrics. Remember, effective use of highlighting can significantly improve data readability and decision-making.

Data Bars and Color Scales

Data Bars and Color Scales are powerful tools within conditional formatting that provide a visual representation of data values. Data Bars display a gradient bar within the cell, indicating the relative size of the value compared to others in the range. Color Scales apply a gradient color scheme based on the values, allowing for quick visual assessment of data distribution.

To apply Data Bars in Excel or Google Sheets:

1. Select your data range.
2. Navigate to 'Conditional Formatting'.
3. Choose 'Data Bars' and select the desired style.

For Color Scales:

1. Select your data range.
2. Go to 'Conditional Formatting'.
3. Choose 'Color Scales' and pick a color gradient.

For instance, if you have a list of sales figures, using a color scale can help you quickly identify the highest and lowest performers. This visual approach not only enhances data interpretation but also aids in identifying patterns and anomalies effectively.

Charts and Dashboards

Chart Selection

Choosing the right chart type is critical for effectively communicating your data insights. Common chart types include bar charts, line charts, pie charts, and scatter plots. Each type serves a different purpose:

• Bar Charts are ideal for comparing quantities across categories.
• Line Charts are best for showing trends over time.
• Pie Charts illustrate parts of a whole.
• Scatter Plots display relationships between two variables.

To select a chart in Excel or Google Sheets:

1. Highlight the data you want to visualize.
2. Go to the 'Insert' tab.
3. Choose the appropriate chart type from the Chart options.

For example, if you want to show monthly sales trends, a line chart would be most effective. Always consider your audience and the story you want to tell with your data when selecting a chart type.

Dashboard Components

A dashboard is a visual representation of key metrics and data points, providing an at-a-glance view of performance. Key components of a dashboard include charts, tables, and KPIs (Key Performance Indicators). When designing a dashboard:

• Ensure clarity and simplicity; avoid clutter.
• Use consistent color schemes and fonts for a professional look.
• Include interactive elements like slicers or drop-down menus for user engagement.

In Excel, you can create a dashboard by:

1. Compiling your data in a summary sheet.
2. Inserting various charts and tables that represent your data.
3. Arranging these elements in a logical layout on a single sheet.

A well-designed dashboard allows stakeholders to monitor performance and make informed decisions quickly.

Interactive Dashboards

Interactive dashboards enhance user engagement by allowing users to filter and manipulate data dynamically. In Excel and Google Sheets, you can create interactivity using slicers, dropdown lists, and pivot tables. To create an interactive dashboard:

1. Set up your data in a structured format.
2. Create pivot tables to summarize your data.
3. Insert slicers or dropdown lists linked to your pivot tables.

For example, if you have sales data by region, you can use a slicer to filter the dashboard by specific regions, allowing users to focus on relevant data. This interactivity not only improves user experience but also facilitates deeper insights into the data.

Power Query Fundamentals

Data Import

Power Query allows users to easily import data from various sources, including databases, web pages, and files. To start importing data, navigate to the Data tab in Excel or Google Sheets and select Get Data. You can choose from options such as From File, From Database, or From Web.

Key Points:

• Supported Sources: Excel files, CSV, JSON, XML, SQL databases, and more.
• Connection Types: You can create a connection to the data source or import the data directly into your workbook.

Example: To import data from a CSV file:

1. Select Get Data > From File > From Text/CSV.
2. Browse to your file and click Import.
3. Power Query will preview the data, allowing you to make adjustments before loading.

This process streamlines data acquisition, making it easier to work with large datasets.

Data Transformation

Data transformation is a crucial step in preparing your data for analysis. Power Query provides a user-friendly interface to apply various transformations, such as filtering, sorting, and merging datasets.

Key Transformations:

• Filtering Rows: Remove unnecessary data by applying filters based on specific criteria.
• Changing Data Types: Ensure that each column has the correct data type (e.g., text, number, date).
• Adding Calculated Columns: Create new columns based on existing data using custom formulas.

Example: To filter rows:

1. Click on the dropdown arrow in the column header.
2. Select the criteria you want to filter by (e.g., greater than a specific value).

By transforming your data, you can enhance its quality and relevance, leading to more accurate analysis.

Data Shaping

Data shaping involves organizing your data into a format that is suitable for analysis. Power Query allows you to reshape your data through operations such as pivoting, unpivoting, and grouping.

Key Shaping Techniques:

• Pivoting Columns: Convert unique values from one column into multiple columns.
• Unpivoting Columns: Transform multiple columns into rows for easier analysis.
• Grouping Data: Aggregate data based on specific fields to summarize information.

Example: To pivot data:

1. Select the column you want to pivot.
2. Go to the Transform tab and click on Pivot Column.
3. Choose the values to aggregate and the aggregation function (e.g., sum, average).

Shaping your data correctly is essential for effective reporting and visualization.

Load and Refresh Operations

Once your data is imported, transformed, and shaped, the final step is to load it into your Excel or Google Sheets environment. Power Query provides options to load data directly into a worksheet or to create a connection only.

Loading Options:

• Load to Worksheet: Directly place the data in a new or existing worksheet.
• Load to Data Model: Store data in the Excel Data Model for advanced analytics.
• Connection Only: Create a connection for future use without loading data immediately.

Refreshing Data: To keep your data up-to-date, use the Refresh feature. This allows you to pull the latest data from the source without redoing the entire import process.

Example: To refresh data:

1. Right-click on the query in the Queries pane.
2. Select Refresh.

Understanding load and refresh operations ensures that your analyses are based on the most current data available.