Ενότητα 1:
MANIPULATING AND ANALYZING DATA IN LISTS
TEXT FUNCTIONS
· CONCATENATE
· LEFT, RIGHT
· UPPER, LOWER
· LEN
· FIND
· Nested Functions using the Functions listed above
· Useful application in Manipulating Lists
MATH & TRIG Functions
· SUMIF and SUMIFS
· SUBTOTAL
· ROUND
· Useful Applications in Lists
STATISTICAL Functions
· COUNTIF and COUNTIFS
· AVERAGE – MEDIAN
· MAXIF, MINIF
· Variance, Standard Deviation
· Useful Applications in Lists
Ενότητα 2:
ARRAY FORMULAE & FUNCTIONS
· What are they?
· Examples
· Useful Applications—Case Study
Ενότητα 3:
LOOKUP FUNCTIONS
· Introduction to ARRAY Functions
· TRANSPOSE
· ROWS and COLUMNS
· MATCH and INDEX
· INDIRECT
· OFFSET
· Useful Applications in Lists
Ενότητα 4:
LOGICAL & INFORMATION FUNCTIONS
· IFERROR, ISERROR, ISNA etc
· When Information functions are useful?
· Useful applications and combination of Logical & Information Functions
· Examples
Ενότητα 5:
ADVANCED Conditional Formatting
· Basic Conditional Formatting
· Creating your own Conditional Formatting with Rules
· Combining Conditional Formatting with Functions for customization
· Examples
· Highlight dates where you have a weekend
· Highlight those employees with below average KPIs using Conditional Formatting and Function
Ενότητα 6:
ADVANCED DATA VALIDATION: Creating Nested Lists
· Combining Data Validation with Functions
Ενότητα 7:
Comparing Lists
· Using VLOOKUP
· Nested functions with IF and VLOOKUP
· INDEX and MATCH: More powerful than VLOOKUP
· Automating Calculations with IF and formulae
Ενότητα 8:
Finding Duplicates
· Using Conditional Formatting
· Using the Remove Duplicates Tool
· Using function combinations
· Using Advance FILETRING
Ενότητα 9:
ADVANCED PIVOT TABLES
· Calculated fields
· Calculated Columns
· Converting PIVOT with ARRAY FORMULA
Ενότητα 10:
Importing Data
· Connecting to External Data Sources
· Importing from text files
· Importing csv files
· Using TRIM to spot redundant spaces