Course objective:

• To make the student understand the special concepts in MS EXCEL.
• To practice the students how to work in list, data forms and records.
• To understand the concepts of filtering data.

 

UNIT I    ADVANCED EXCEL FORMULAS         

Uses of Advance Excel Formulas -VLOOKUP, HLOOKUP, SUMIF, SUMIFS, SUMPRODUCT, DSUM, COUNTIF, COUNTIFS, IF, IFERROR, ISERROR, ISNA, ISNUMBER, ISNONTEXT, OR, AND, SEARCH, INDEX, MATCH etc

UNIT II   IF CONDITIONS         

Various Methods and Uses of IF Conditions, when should use the “IF” Conditions? Creation of Multiple IF Conditions in One Cell, Use the IF Conditions with the Other Advance Functions, how to use nested IF statements in Excel with AND, OR Functions. Sorting, Data Forms, Adding Data Using the Data Form, Finding Records Using Criteria

UNIT III FILTERING AND SORTING         

Filtering Data, AutoFilter, Totals and Subtotals Total, Row, Various Methods of Filter and Advance Filter options, Creating and Updating Subtotals, Various Method of Sorting Data, Creating, Formatting and Modifying Chart.

UNIT IV DATA VALIDATION AND GOAL SEEK         

Uses of Goal Seek and Scenarios Manager, Data Validation, creating drop down lists, using different data sources, Linking Workbooks and Uses of Edit Link options, Excel Options, Customizing the Quick Access Tool Bar, Managing Windows, Multiple Windows, Splitting Windows.

UNIT V PIVOT TABLES         

Various Methods and Options of Pivot Table, Using the Pivot Table Wizard, Changing the Pivot Table Layout, Subtotal and Grand Total Options, Formatting, and Grouping items

Inserting calculated fields, Pivot Table Options, Display and hide data in fields

Select, Move & Clear Pivot data, Creating and Modifying a PivotChart

 

Total: 30 Hrs