Excel Learning Path

Created by Hales Psych

#Excel, #data, #learning, #mindmap

HLOOKUP

• Purpose: Similar to VLOOKUP but searches for a value in the first row and returns a value in the same column from a different row.
• Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Commonly Used For:
  • Data Analysis: Extracting data points from horizontally arranged data such as monthly sales.
  • Dashboard Creation: Dynamically pulling values into a dashboard based on selected criteria.
• Real-World Use Cases:
  1. Monthly Performance Review: Retrieve the sales data for a specific month in a horizontally structured sales table. Example: =HLOOKUP("March", SalesData, 3, FALSE) to get March’s sales figure.
  2. Survey Analysis: Analyze survey results where responses are recorded horizontally. Example: =HLOOKUP("Question1", SurveyData, 5, FALSE) retrieves the response to Question 1 from the 5th respondent.

LOOKUP

• Purpose: To search for a value in a vector or array and return a corresponding value from another range.
• Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])
• Commonly Used For:
  • Data Retrieval: Simple lookups in less complex datasets.
  • Legacy Data Analysis: Working with older Excel files where data is stored in a more basic format.
• Real-World Use Cases:
  1. Assigning Grades: Assign grades to students based on their scores using a simple lookup. Example: =LOOKUP(Score, ScoreRanges, Grades).
  2. Basic Product Information Retrieval: Get product category or description based on a product code in a simple list. Example: =LOOKUP(ProductCode, ProductCodes, ProductDescriptions).

VLOOKUP

• Purpose: To search for a value in the first column of a range or table and return a value in the same row from another column.
• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Commonly Used For:
  • Invoice Generation: Pulling product prices, descriptions, or customer details into invoices.
  • Database Management: Linking related data between different tables or sheets.
• Real-World Use Cases:
  1. Invoice Creation: Automatically fill in product descriptions and prices when generating customer invoices.
     • =VLOOKUP(ProductCode, ProductTable, 2, FALSE) retrieves the product name based on its code.

1. Employee Data Retrieval: Find and retrieve an employee’s department based on their employee ID. Example: 
   • =VLOOKUP(EmployeeID, EmployeeTable, 3, FALSE).

NOTES:

Use VLOOKUP if you’re looking for a simple, straightforward lookup function and your data is in a simple vertical format. Best For: Simple and quick lookups in a well-structured, vertical dataset .

Strengths:

• Simple and easy to use for basic lookups where the data is organized vertically.
• Commonly used and widely understood, making it easier to share and collaborate on files.

Weaknesses:

• Can only search in the first column and return values from columns to the right, which limits flexibility.
• Requires the data to be sorted if you’re doing an approximate match.
• It’s less efficient with large datasets, especially when combined with other functions.

* IFERROR

IFERROR Function

• Purpose: Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.
• Syntax: =IFERROR(value, value_if_error)
• Example: =IFERROR(A1/B1, "Division Error")
• Real World:
  1. Use Case: Error Management in Calculations
     • Example: In financial analysis, prevent errors from disrupting reports when dividing by zero or referencing invalid data:
       • =IFERROR(Profit / Sales, "Error in Calculation").
  2. Use Case: Data Importing
     • Example: Handle errors when importing data, ensuring that missing or incorrect data doesn’t break formulas:
       • =IFERROR(VLOOKUP(A2, DataTable, 2, FALSE), "Not Found").

XLOOKUP (Excel 2019+)

• Purpose: A versatile function that can search both vertically and horizontally with options for exact and approximate matches.
• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Commonly Used For:
  • Database Management: Cross-referencing data between different tables or sheets.
  • Error Handling: Providing a default value if no match is found, making it robust for reporting.
• Real-World Use Cases:
  1. Customer Support: Quickly retrieve customer details based on an ID or name from a database. Example: =XLOOKUP(CustomerID, CustomerIDs, CustomerDetails, "Not Found").
  2. Sales Analysis: Find the sales figures for a specific product across multiple sheets or tables. Example: =XLOOKUP(ProductCode, ProductCodes, SalesFigures, 0).

NOTES: 

• Use XLOOKUP if you have access to Excel 2019 or later, as it offers the most flexibility and power with the easiest syntax. Best For: Versatile and powerful lookups, especially in complex datasets where flexibility and error handling are important. 

Strengths:

• Combines the functionalities of VLOOKUP, HLOOKUP, and LOOKUP into one function.
• Can search both vertically and horizontally, with more flexibility in specifying the range for lookup and return values.
• Supports exact and approximate matches and can return arrays, making it very powerful.
• Handles errors better with the [if_not_found] argument and can search in reverse order.

Weaknesses:

• • Only available in Excel 2019 and later versions (Excel 365).

TRUE & FALSE

TRUE and FALSE Functions

• Purpose: These functions return the logical value TRUE or FALSE. They are often used as placeholders in other logical functions.
• Syntax: =TRUE() or =FALSE()
• Example: =IF(A1 = B1, TRUE(), FALSE())
• Real World:
  1. Use Case: Simple Logical Tests
     • Example: Use TRUE and FALSE as constants for quick checks:
       • =IF(A1 = B1, TRUE, FALSE) can help in quick data comparisons.
  2. Use Case: Placeholder in Conditional Formulas
     • Example: Use TRUE and FALSE in IF statements or AND/OR functions to streamline conditional logic.

FILTER (Excel 365 & Excel 2019)

• Purpose: To filter a range of data based on specified criteria and return the filtered data.
• Syntax: =FILTER(array, include, [if_empty])
• Commonly Used For:
  • Data Analysis: Extracting subsets of data for deeper analysis or reporting.
  • Custom Reports: Building dynamic reports that adjust based on criteria or user selections.
• Real-World Use Cases:
  1. Sales Data Filtering: Display only the sales records that exceed a certain amount. Example: =FILTER(SalesData, SalesData > 1000).
  2. Customer Segmentation: Generate a list of customers who meet specific criteria, such as those who made purchases in a certain region. Example: =FILTER(CustomerData, Region="North").

CHOOSE

CHOOSE Function

• Purpose: Returns a value from a list based on the index number provided.
• Syntax: =CHOOSE(index_num, value1, value2, ...)
• Example: =CHOOSE(2, "Red", "Green", "Blue") (This would return "Green")
• Real World:
  1. Use Case: Dynamic Selection
     • Example: In project management, select a project lead based on project phase:
       • =CHOOSE(Project_Phase, "Initiation: Alice", "Planning: Bob", "Execution: Charlie").
  2. Use Case: Scenario Analysis
     • Example: Evaluate different business scenarios:
       •  =CHOOSE(Scenario, Revenue_Growth, Revenue_Stability, Revenue_Decline).

INDEX

• Purpose: To return the value of a cell in a specified row and column within a given range.
• Syntax: =INDEX(array, row_num, [column_num])
• Commonly Used For:
  • Data Retrieval: Extracting specific values from a table or range.
  • Dashboard Creation: Creating dynamic dashboards where data updates automatically based on user input.
• Real-World Use Cases:
  1. Financial Reporting: Extract a specific financial figure from a dataset. For example, =INDEX(FinancialData, 4, 2) to get data from the 4th row and 2nd column.
  2. Custom Reports: Combine with MATCH to create flexible reports where the data location can change. Example: =INDEX(SalesData, MATCH(Salesperson, Salespeople, 0), 2) retrieves the sales number for a specific salesperson.

NOTES:

Use INDEX (with MATCH) if you need flexibility and performance in large datasets or if your data isn't structured for VLOOKUP/XLOOKUP. Best For: Advanced users who need powerful and flexible lookup capabilities, especially in large or complex datasets. 

Strengths:

• Extremely flexible and can look up values in both rows and columns.
• Doesn’t require data to be sorted and can return values from any column relative to the lookup.
• More efficient with large datasets and allows for dynamic and advanced lookups when combined with MATCH.

Weaknesses:

• • Slightly more complex to set up than VLOOKUP and XLOOKUP.
  • Requires an additional MATCH function to fully replicate the behavior of XLOOKUP or VLOOKUP.

NOT

NOT Function

• Purpose: Reverses the logical value of its argument. If a condition is TRUE, NOT returns FALSE, and vice versa.
• Syntax: =NOT(logical)
• Example: =NOT(A1 = "Yes")
• Real World:
  1. Use Case: Inverting Conditions
     •  In quality control, flag items that do not meet standards:
       • =NOT(Quality_Check = "Pass")
  2. Use Case: Exclusion Filtering
     •  Filter out specific criteria in reports, like excluding non-responsive customers:
       •  =NOT(Last_Contacted < DATE(2023, 1, 1))

OFFSET

• Purpose: Similar to VLOOKUP but searches for a value in the first row and returns a value in the same column from a different row.
• Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Commonly Used For:
  • Data Analysis: Extracting data points from horizontally arranged data such as monthly sales.
  • Dashboard Creation: Dynamically pulling values into a dashboard based on selected criteria.
• Real-World Use Cases:
  1. Monthly Performance Review: Retrieve the sales data for a specific month in a horizontally structured sales table. Example: =HLOOKUP("March", SalesData, 3, FALSE) to get March’s sales figure.
  2. Survey Analysis: Analyze survey results where responses are recorded horizontally. Example: =HLOOKUP("Question1", SurveyData, 5, FALSE) retrieves the response to Question 1 from the 5th respondent.

XOR

XOR Function (Exclusive OR)

• Purpose: Returns TRUE if an odd number of arguments evaluate to TRUE, otherwise returns FALSE.
• Syntax: =XOR(logical1, logical2, ...)
• Example: =XOR(TRUE, FALSE, FALSE) (This would return TRUE)
• Real World:
  1. Use Case: Exclusive Conditions
     • Example: In IT security, trigger alerts only if there’s exactly one error condition:
       •  =XOR(Login_Fail, Password_Reset) (useful when only one of the two should happen at a time).
  2. Use Case: Survey Analysis
     • Example: Identify responses where participants choose only one option when they should select two:
       • =XOR(Option1_Selected, Option2_Selected).

TRANSPOSE

• Purpose: To convert a vertical range of cells to a horizontal range or vice versa.
• Syntax: =TRANSPOSE(array)
• Commonly Used For:
  • Data Reformatting: Adjusting the layout of data for better readability or presentation.
  • Template Design: Changing the orientation of data when designing reports or templates.
• Real-World Use Cases:
  1. Reformatting Data: Convert a vertical list of monthly sales figures into a horizontal row to fit into a report.
  2. Template Customization: Transpose data to fit a specific template format for printing or analysis.

Lookup Functions

MATCH

• Purpose: To search for a specified value in a range and return the relative position of that item.
• Syntax: =MATCH(lookup_value, lookup_array, [match_type])
• Commonly Used For:
  • Locating Items in Large Datasets: Identifying the position of data within a large list.
  • Dynamic Data Analysis: Used in combination with INDEX to create more flexible data retrieval formulas.
• Real-World Use Cases:
  1. Project Management: Find the position of a specific task in a project list. Example: =MATCH("Task A", TaskList, 0) returns the row number of "Task A".
  2. Inventory Tracking: Determine the position of a product in a large inventory list to use in further calculations. Example: =MATCH("ProductA", ProductList, 0).

Logical Functions

• These logical functions can be powerful tools to enhance efficiency, accuracy, and decision-making in various business contexts, from simple data checks to complex analytical models.

AND

AND Function

• Purpose: Returns TRUE if all conditions are true; otherwise, it returns FALSE.
• Syntax: =AND(condition1, condition2, ...)
• Example: =AND(A1 > 5, B1 < 10)
• Real World:
  1. Use Case: Complex Criteria Evaluation
     • In HR, you might need to check if an employee is eligible for a bonus by evaluating multiple conditions:
       • =AND(Years_of_Service >= 5, Sales > 10000, Customer_Satisfaction >= 90).
  2. Use Case: Project Deadlines
     • Track project status by ensuring multiple milestones are completed:
       • =AND(Task1_Complete, Task2_Complete, Task3_Complete).

UNIQUE (Excel 365 & Excel 2019)

• Purpose: To return a list of unique values from a range, eliminating duplicates.
• Syntax: =UNIQUE(array, [by_col], [exactly_once])
• Commonly Used For:
  • Data Cleaning: Removing duplicates from datasets for more accurate analysis.
  • Data Analysis: Identifying distinct entries in a dataset to understand diversity or variability.
• Real-World Use Cases:
  1. Customer List Creation: Create a unique list of customers who made purchases during a specific period. Example: =UNIQUE(CustomerNames).
  2. Product Inventory Analysis: Identify distinct products sold, helping to manage inventory more effectively. Example: =UNIQUE(ProductList).

OR

OR Function

• Purpose: Returns TRUE if any of the conditions are true; otherwise, it returns FALSE.
• Syntax: =OR(condition1, condition2, ...)
• Example: =OR(A1 > 5, B1 < 10)

SWITCH

SWITCH Function

• Purpose: Evaluates one value against a list of values and returns the result corresponding to the first matching value. If there's no match, it returns an optional default value.
• Syntax: =SWITCH(expression, value1, result1, [value2, result2], ..., [default])
• Example: =SWITCH(A1, 1, "One", 2, "Two", "Other")
• Real World:
  1. Use Case: Categorizing Data
     • Example: In customer service, categorize issues by type for reporting:
       • =SWITCH(Issue_Type, "1", "Billing", "2", "Technical", "3", "General Inquiry", "Other").
  2. Use Case: Regional Formatting
     • Example: Apply specific formatting or language depending on the region:
       • =SWITCH(Region, "US", "English", "FR", "French", "ES", "Spanish", "Other").

Date and Time Functions

Sorting and Filtering Data

IFS (Excel 2016+)

IFS Function (Excel 2016 and later)

• Purpose: Checks multiple conditions and returns a value corresponding to the first TRUE condition.
• Syntax: =IFS(condition1, value_if_true1, condition2, value_if_true2, ...)
• Example: =IFS(A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C")
• Real World:
  1. Use Case: Multi-Level Decision Making
     • Example: In performance evaluations, assign grades based on scores:
       • =IFS(Score >= 90, "A", Score >= 80, "B", Score >= 70, "C", Score < 70, "F").
  2. Use Case: Pricing Tiers
     • Example: Determine pricing based on quantity ordered:
       • =IFS(Quantity >= 100, "Bulk Price", Quantity >= 50, "Wholesale", TRUE, "Retail").

NOW, TODAY

=NOW()

• returns todays current date and time

=TODAY()

• returns the current date

IF

1. IF Function
   • Purpose: Performs a logical test and returns one value if true, and another if false.
   • Syntax: =IF(logicaltest, valueiftrue, valueif_false)
   • Example: =IF(A1 > 10, "Over 10", "10 or less")
   • Real World: 
     1. Use Case: Conditional Reporting
        • In sales reporting, use IF to categorize sales performance.
          • =IF(Sales > 1000, "High Performer", "Needs Improvement") can be used to evaluate sales staff.
     2. Use Case: Expense Approval
        • Automatically approve or flag expenses based on their amount:
          • =IF(Expense <= 500, "Approved", "Needs Review").

Advanced Formulas and Functions

COUNT, COUNTA

=COUNT(A1:A10)

• Counts the number of cells that contain numbers in the range A1 to A10.

=COUNTA(A1:A10)

• Counts the number of cells that are not empty in the range A1 to A10.

Data Management

AVERAGE

=AVERAGE(A1:A10)

• Calculates the average of the numbers in the range A1 to A10

CONCATENATE

=CONCATENATE(A1, " ", B1)

or

=CONCAT(A1, " ", B1)

• Joins the values in cells A1 and B1 with a space in between.

! Data Validation

Text Functions

IF

=IF(A1 > 10, "Greater", "Lesser")

• Checks if the value in cell A1 is greater than 10. If true, it returns "Greater"; otherwise, it returns "Lesser".

LEFT, RIGHT, MID

=LEFT(A1, 3), =RIGHT(A1, 3), =MID(A1, 2, 3)

• LEFT returns the first 3 characters from the left of the text in cell A1.
• RIGHT returns the last 3 characters from the right of the text in cell A1.
• MID returns 3 characters from the middle of the text in cell A1, starting from the 2nd character.

SUM

SHORTCUT to add

• alt =

MIN, MAX

=MIN(A1:A10)

• Returns the smallest number in the range A1 to A10.

=MAX(A1:A10)

• Returns the largest number in the range A1 to A10.

Grouping and Subtotals

! Removing Duplicates

Charts & Graphs

• Creating and Customizing Advanced Charts
• Combo Charts
• Sparklines
• Chart Formatting Options

Basic Formulas and Functions

Cell Referencing

Relative

•  A5
• B4:C4

Absolute

• $A$5
  • A:$5 or $A:5
• $B$4:$C$4
  • B$4:C$4 or $B4:$C4

DAX (Data Analysis Expressions) Basics

Using Power Pivot for Data Modeling

Data Analysis with PivotTables

2. Intermediate

Entering and Formatting Data

Bare Basics

Basic Chart Creation

Number Formatting

• Currency
  • Formats numbers as currency with a currency symbol and two decimal places.
  • Access: Home tab → Number group → Currency
• Percentage
  • Formats numbers as percentages with a percent symbol and typically two decimal places.
  • Access: Home tab → Number group → Percentage
• Number with Comma Separator
  • Adds comma separators for thousands and two decimal places.
  • Access: Home tab → Number group → Number
• Date
  • Formats numbers as dates in various formats (e.g., MM/DD/YYYY, DD/MM/YYYY).
  • Access: Home tab → Number group → Date
• Time
  • Formats numbers as time in various formats (e.g., HH:MM AM/PM, 24-hour format).
  • Access: Home tab → Number group → Time
• Custom

Creating Measures and Calculated Columns

Creating Pivot Tables & PivotCharts

Formatting

Cell Styles & Themes

Cell Styles

• Predefined styles for quick formatting of cells (e.g., Heading, Total, Bad, Good).
• Access: Home tab → Styles group → Cell Styles

Themes

• Apply a consistent set of fonts, colors, and effects to the entire workbook.
• Access: Page Layout tab → Themes group → Themes

1. Basics

Analyzing Data with PivotTables

Excel Mindmap

! Conditional Formatting

1. Highlight Cells Rules
   • Highlights cells based on specific conditions (e.g., greater than, less than, equal to).
   • Example: Highlight cells with values greater than 100.
   • Access: Home tab → Styles group → Conditional Formatting → Highlight Cells Rules
2. Top/Bottom Rules
   • Highlights the top or bottom values in a range (e.g., top 10 items, bottom 10%).
   • Example: Highlight top 10% of sales figures.
   • Access: Home tab → Styles group → Conditional Formatting → Top/Bottom Rules
3. Data Bars
   • Adds horizontal bars to cells to visualize values.
   • Example: Sales figures with varying lengths of data bars.
   • Access: Home tab → Styles group → Conditional Formatting → Data Bars
4. Color Scales
   • Applies a color gradient to cells based on their values.
   • Example: Temperature readings with a color gradient from blue (cold) to red (hot).
   • Access: Home tab → Styles group → Conditional Formatting → Color Scales
5. Icon Sets
   • Adds icons to cells based on their values.
   • Example: Traffic light icons to represent performance levels.
   • Access: Home tab → Styles group → Conditional Formatting → Icon Sets

Merging Cells and Alignment

1. Merging Cells
   • Combines multiple cells into one.
   • Options: Merge & Center, Merge Across, Merge Cells, Unmerge Cells.
   • Access: Home tab → Alignment group → Merge & Center
2. Alignment
   • Aligns text horizontally and vertically within cells.
   • Options: Left, Center, Right, Top, Middle, Bottom.
   • Access: Home tab → Alignment group → Horizontal Alignment and Vertical Alignment
3. Text Wrap
   • Wraps text within a cell to fit within the column width.
   • Access: Home tab → Alignment group → Wrap Text
4. Orientation
   • Changes the angle of text within cells.
   • Example: Vertical text, angled text.
   • Access: Home tab → Alignment group → Orientation

Data Analysis Tools

! PivotTables & PivotCharts

! Data Transformation with Power Query

Data Cleaning & Preparation

• Importing Data from Various Sources
• Cleaning Data with Text Functions
• Removing Duplicates and Handling Missing Data

Using Slicers & Timelines

Printing

• Print Preview and Page Setup
• Adjusting Margins and Orientation
• Setting Print Area
• Header and Footer Insertion

Data Entry & Editing

• Inserting, Deleting Rows and Columns
• Copy, Cut, and Paste Data
• Undo and Redo Actions
• AutoFill and Flash Fill

Using Excel for Depreciation Calculations

5. Data Analysis

3. Advanced

Loan Amortization Schedules

Advanced Charting Techniques

Starting Data Analysis

Financial Analysis

• Ratio Analysis
• Break-Even Analysis
• Financial Modeling

Accounting Functions in Excel

! Inventory Management

Data Visualization

Collaboration & Sharing

• Sharing Workbooks
• Tracking Changes and Comments
• Working with Excel Online

Creating Dashboards

What-If Analysis

• Goal Seek
•  Scenario Manager

Automation

4. Accounting

Machine Learning & Predictive Analytics

! Payroll Calculations

Introduction to Macros

• Recording and Running Macros

Protecting Worksheets & Workbooks

Budgeting & Forecasting

• Creating and Managing Budgets
• Financial Forecasting Techniques
• Variance Analysis

Interactive Visualizations with Slicers and Timelines

Using Power View

Integration with R & Python for Advanced Analytics

Data Tables

Writing Simple VBA Scripts

! Advanced Functions

Basic Accounting Principles

Introduction to Excel's Machine Learning Add-Ins

Solver Add-In

Basic VBA Concepts

(Visual Basic for Applications)

Financial Statements

• Creating Balance Sheets
• Income Statements
• Cash Flow Statements

Dynamic Named Ranges

Introduction to Accounting Concepts

Using Excel for Forecasting

Basic Predictive Modeling

! Advanced Conditional Formatting

Array Formulas

INDEX & MATCH Functions

Advanced Lookup & Reference Functions

! Double-Entry Bookkeeping

Chart of Accounts