Excel Training: Function Explanations
Module 1 - Advanced Formulas & Functions
IF:
Returns one value if a condition is TRUE and another if FALSE.
Example: =IF(A1>10, "Yes", "No")
VLOOKUP:
Searches for a value in the first column of a range and returns a value in the same row from another column.
Example: =VLOOKUP(101, A2:C10, 3, FALSE)
INDEX:
Returns the value at a specified row and column in a range.
Example: =INDEX(B2:B5, 2)
MATCH:
Returns the relative position of an item in a range.
Example: =MATCH(45, A2:A5, 0)
IFERROR:
Returns a custom value if the formula results in an error.
Example: =IFERROR(A1/B1, "Error")
Module 2 - Data Tools
Flash Fill:
Automatically fills in values based on patterns detected in your data.
Example: Extract first name from full name column.
Data Validation:
Restricts the type of data or values that users enter into a cell.
Example: Limit input between 1 and 100.
Text to Columns:
Splits a single column of text into multiple columns.
Example: Split full name into first and last names.
Remove Duplicates:
Removes duplicate values in a dataset to retain unique entries.
Module 3 - PivotTables & PivotCharts
PivotTable:
A tool to summarize large datasets quickly by rows and columns.
Example: Total sales by region and product.
PivotChart:
A graphical representation of PivotTable data.
Example: Create a column chart for monthly sales.
Slicer:
A visual filter added to a PivotTable for easy filtering.
Example: Filter sales data by product or region.
Module 4 - Advanced Charting
Combo Chart:
Combines two chart types to highlight different data series.
Example: Column for revenue, line for expenses.
Sparklines:
Tiny charts embedded in cells that show trends.
Example: Visualize performance trends in rows.
Data Validation (Drop-down):
Create drop-down lists to select items and update charts dynamically.
Module 5 - What-If Analysis & Scenario Tools
Goal Seek:
Finds the input value needed to achieve a specific goal.
Example: Calculate units to sell to hit $10,000 profit.
Data Table:
Shows how changes in one or two variables affect a formula.
Example: Compare outcomes of pricing models.
Scenario Manager:
Allows you to create and switch between different groups of input values.
Example: Best Case, Worst Case scenarios.
Module 6 - Macros and Introduction to VBA
Macro Recorder:
Records actions performed in Excel to automate tasks.
VBA Editor:
Environment to view/edit macro code.
Range.ClearContents:
Clears data from a specified range.
Example: Range("A1:A10").ClearContents
Highlighting with VBA:
Use code to format/highlight top values or apply color rules.
Module 7 - Data Security & Workbook Management
Protect Sheet:
Prevents users from changing data in the worksheet.
Example: Lock formula cells.
Hide/Unhide Sheets:
Control visibility of worksheets.
Lock Cells:
Restrict editing to specific cells only.
Inspect Document:
Review hidden properties or metadata before sharing a file.
Module 8 - Automation with Power Query
Get Data > From Folder:
Import multiple files from a folder for batch processing.
Split Columns:
Divide data in one column into multiple using delimiters.
Merge Queries:
Join multiple tables together based on a key column.
Load to Excel:
Output the cleaned and processed data to Excel.
