Diploma in Corporate Advance Excel

Level 1

Introduction to Excel

Excel Start Screen The Ribbon (Customization and Developer Tabs) Tabs, Groups, Commands, Dialog Box Launcher ,Quick Access Toolbar File Tab (Backstage View),Excel Options, Views and Zooming, Keyboard Shortcuts Workbook Windows, Document Inspector, File Formats,Compatibility and Versions

Grouping, Outlining and Subtotals

Creating subtotals, Multiple-level subtotals, Outlining and Grouping Data

Filtering

Filtering data for selected view (AutoFilter), Using advanced filter options.

Worksheet Formatting

Using auto formatting option for worksheets, Using conditional formatting option for rows, columns and cells, Protecting and un-protecting worksheets Using slicers with table, Cells Formatting, create and modify table.

Conditional Formatting

Highlighting Cells Rules, Top/Bottom Rules, Data Bars, Icon Sets, Color Scales Editing Standard Formatting Rules, Using Formulas in Conditional Formatting.

Sorting

Sorting table, Using multiple-level sorting, Using custom sorting.

Charts

Using Charts, Formatting Charts, Using 3D Graphs, Trend lines, Error Bars, Using Bar and Line Chart together, Using Secondary Axis in Graphs Sharing Charts with PowerPoint / MS Word, Dynamically(Data Modified in Excel, Chart would automatically get updated).

Name Manager

Naming Cells and Ranges, Use Excel Names, Create a Dynamic Named Range Use a Named Excel Table, Dynamic Named Range Based on Formula.

Level 2

Relative and Absolute Reference

Relative Reference, Absolute Reference Using cell references with multiple worksheets.

Statistical Functions

Average, median, mode, large, max, min, percentile, quartile, rank, small average if, averageifs, count, correl.

Consolidation

Consolidating data from multiple sheets and files Using data consolidation feature to consolidate data command on multiple ranges to calculate sums, averages, products, minimum and maximum values.

Pivot Tables And Pivot Charts

Creating Pivot tables, Formatting and customizing Pivot tables Using advanced options of Pivot tables, Pivot charts, Creating Slicers Viewing Subtotal under Pivot, Consolidating data from multiple sheets and files using Pivot tables, Importing Pivot Table Data,Calculated Fields in Pivot Tables, Creating Custom Pivot Tables Styles Formatting and customizing Pivot tables, Using advanced options of Pivot tables, Pivot charts, Creating Slicers, Viewing Subtotal under Pivot Consolidating data from multiple sheets and files using Pivot tables Importing Pivot Table Data, Calculated Fields in Pivot Tables Creating Custom Pivot Tables Styles.

Text Functions

CLEAN, trim, lower, proper, upper, concatenate, Left, right, mid, right, rept, text Len, find, search, exact, t, char, code, replace, substitute

Logical Functions

And, False, IF, If error, Ifna, Ifs, not, or

Vlook up Operator And Lookup Functions

VlookUP with Exact Match, Approximate Match HlookUP with Exact Match, Approximate Match Nested VlookUP with Exact Match VlookUP with Tables, Dynamic Ranges Nested VlookUP with Exact Match Using VLookUP to consolidate Data from Multiple Sheets.

Data Validation

Specifying a valid range of values for a cell Specifying a list of valid values for a cell Specifying custom validations based on formula for a cell drop Down list, prevent duplicates entries, product Codes, Reject invalid dates.

Level 3

Macros

Recording Macros Using Relative options in MACROS, Running Excel Recorded Macros Viewing the VBA Code in macros, Limitations in macro Explore Recorded Macros.

Creating Procedures and Functions

Creating Sub and Function Procedures, Declare and Use Variables Displaying Message to the User, Getting Input from the User.

VBA Introduction to the excel VBA Editor

Starting the Visual Basic Editor, Using the Project Explorer Using the Properties Window, Creating, Importing and Exporting Modules Code Presentation, Data Types , Variables & Constants Arrays Operators and built functions, VBA Errors.

MIS Reports

Profit & loss a/c statement, Balance sheet, Worker contributions statement Cash flow statement, Debtor’s & creditors statement, Inventory statement BRS statement , Fixed Assets & fund statement, Payroll Report P.F & E.S.I Report.

Creating Custom Users Form

Creating a Custom Form, Adding Form Controls, Creating Event Procedures.

Automation

VBA Automation Programme: Data distribution ,Data consolidate , Outlook connectivity program ,User from programs ,data base connectivity with access ,Data insert program ,Data fetch program, Workbook Consolidation Programme, Report Automation, Sample of Productivity Report, Sample of Break Schedule & Time Report, Absenteeism , Shrinkage Report, Sample of Sales Dashboard, Sample of KPI Dashboard & many more as per student requirement.