Agenda - Ms Excel

Agenda Day 1: Basics of referencing (absolute, relative and mixed), IF() and nested IF() functions, AND(), OR() Lookup techniques and nuances such as: • Making the column index in a VLOOKUP() function dynamic • Auto expanding the table array of a VLOOKUP() formula when new records get added to an existing range; • Looking up data within upper and lower limits • Performing a look up operation where the look value is not the first column of the table array Basics of conditional formatting and nuances such conditionally formatting across sheets; Looking up data in large datasets while satisfying one/multiple conditions with and nuances such as: • Overcoming Excel’s (2003 and prior versions) limitations of 7 nested Ifs • Filtering databases on multiple conditions • Extracting unique values from a range Nuances of auto filtering such as: • Applying auto filter to a new column which gets added to an existing filtered range • Running more than one auto filter on one worksheet • Dealing with date related problems such as Knowing the completed year, months and days between two dates; Converting non dates to dates; • Displaying non dates as dates Determining weekdays/weekends between two dates Working with data validation and nuances such as: • Validating across worksheets • Creating dependent validation lists Discussion on worksheet and workbook protection Discussion on various Excel short cuts and tips & tricks Day 2 Basics of SUM() and COUNT() functions and nuances such as: • Dealing with one and multiple conditions • Summing values in a range which has error values (without sorting/filtering and deleting error values);

Summing values workbook

(based

on

one/multiple

conditions)

from

another

Basic and advanced usage of Excel s “naming” feature • Working with naming in formulas • Applying names after formulation • Performing a scenario analysis (or What-if analysis) using Goal Seek • Scenario Manager • Data tables • A scenario analysis on multiple variables • Dynamic consolidation techniques Across multiple worksheets using MS Access; • Across multiple workbooks using MS Access Basics of pivot tables and nuances such as: • Performing calculations in pivot tables; • What to be careful about when working with pivot tables • Creating a pivot table from multiple worksheets without copying and pasting • Basics of graphing and nuances such as Dealing with blanks in line graphs; • Creating 2 y axis charts • Dealing with dates in the x-axis • Creating dynamic data labels in graphs • Dynamically transposing data from rows to columns and vice versa • Discussion on various Excel short cuts and tips & tricks

Aspects not covered in this workshop: Writing macros and programming in Visual Basic for Applications (VBA)*: Before learning programming in Excel i.e. writing macros and coding in VBA, it is imperative to understand the capabilities of the inbuilt functions and formulas of Excel. This workshop is designed to give you a fair level of expertise in solving problems using Excel’s inbuilt functions and formulas