Advanced Excel Course

Mastering Logical Functions 

Using logical functions  

 IF Function:  

This is the most commonly used logical function. It checks whether a condition is met and returns one value if true and another value if false.  

 Syntax: =IF(logical_test, value_if_true, value_if_false)  

 Example: =IF(A1>10, 'Yes', 'No') checks if the value in cell A1 is greater than 10. If true, it returns “Yes”; otherwise, it returns “No”.  

 AND Function:  

This function returns TRUE if all the conditions are true.  

 Syntax: =AND(logical1, logical2, ...)  

 Example: =AND(A1>10, B1<5) returns TRUE if both conditions are met.  

 OR Function: This function returns TRUE if any of the conditions are true.  

 Syntax: =OR(logical1, logical2, ...)  

 Example: =OR(A1>10, B1<5) returns TRUE if either condition is met. 

Advanced Lookup Techniques 

VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value in the same row.  

Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).  

HLOOKUP works similarly but searches the first row.  

XLOOKUP is a more flexible function that can replace both.  

Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).  

INDEX and MATCH are often used together; INDEX returns the value based on row and column, while MATCH returns the position of a value in a range. 

PivotTables for Data Insights 

PivotTables allow you to summarise, analyse, explore, and present large amounts of data.  

Summarisation: Quickly summarise data by categories using functions like sum, average, count. Data Analysis: Helps in analysing numerical data in detail.  

Flexibility: Pivot rows and columns to see different summaries.  

Filtering and Sorting: Easily filter and sort data.  

Drill Down: Expand and collapse data levels.  

How to Create: Select your data, Insert PivotTable, arrange fields. PivotCharts visually represent PivotTables and update dynamically. 

Automation with Macros 

Macros are a series of recorded actions or commands used to automate tasks. They are useful for tasks performed frequently, such as formatting data or creating reports.  

How to Create: Enable Developer Tab, Record Macro, perform actions, stop recording. Run Macro: Press the assigned shortcut or select from Developer tab.  

Editing: Macros are written in VBA and can be edited in the VBA editor. Benefits: Time-saving, consistency, efficiency.