MS Advanced Excel – Level I
Topics covered in the session:
1. Basic move/copying of data from one sheet to the other.
2. Calculations on sample database – as HRA/DA on the Basic salary using relative reference.
3. Calculation using Mixed Reference (A$1,$A1) and Absolute Reference ($A$1).
4. Logical Functions – Use of IF statement with OR, AND, NOT operators with multiple parameters. – Ex. Calculations of Bonus amounts using parameters such as Department, Region, Basic Salary.
5. Functions – Calculations using various functions viz. Sum, Max, Min, Average, Large, Small, Count, Count if, Count ifs, Sum if, Sum ifs.
6. Concept of V Look up (Vertical Lookup) – Working on sample database to search value in leftmost column get value in a specific Column. Ex – Search on Employee Code and get his First name or last name or any other details.
7. Concept of H Look up (Horizontal Lookup) – Same as Vlookup except search it horizontally.
8. Practice Sheet 1 – Vlookup practice on database – To get all other details of an employee from employee code.
9. Practice Sheet 2 – Use Students Database. Calculation on Marks of students & get percentage, class obtained using IF. Get Max percentage, count First Class Students (Use of Max, Countif, vlookup)
10. Concept of Pivot Tables & Pivot charts – Use Pivot Table and generate reports on sample database based on Department, Region, Salary and Joining Date.
MS Advanced Excel – Level II
Topics covered in this session – Analytical Tools, Logical Functions, Conditional Formatting and text / date functions:
1. Sorting of data – single level, multi level and customized sorting.
2. Filtering of data – single level, multi level with conditions such equal to, begins with, does not equal to and contains.
3. Advanced usage of vlookup – multiple statements, absolute matching of selected data, approximate match.
4. Use of Index function to get reference value from a selected range or table.
5. Use of index for exact match and to get value from left columns of the selected data columns unlike in vlookup (which allows only referring to the columns on the right).
6. Define Names and use them in formula.
7. Data Validation to restrict user from entering invalid data for individual cells or cell ranges – validation of numbers, text, date, time and custom validation using formulas. Enter Input Message and Error Message.
8. Lock, Unlock cells, Protect cells, sheets. Hide Formula.
9. Grouping data using subtotal at single level, Nested Subtotals. Copy result data without hiding cells to other worksheet or other workbook.
10. Conditional Formatting – conditional formatting is applied to data only if one or more specific conditions are satisfied. Use cell rules – greater than, less than, equal to, between, text or date conditions. Use of formula in conditional formatting. Sort result on colour and copy result to other worksheet.
11. Use Text Functions to split or merge text values in cells.
12. Use Date Function for date differences in years, months and days.
13. Macro – Use of Macro to automate complex and repetitive tasks. Macro is a series of instructions that execute automatically with a single command. Example of creating Pivot Table.