Diploma in Corporate Advance Excel
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 data for selected view (AutoFilter), Using advanced filter options.
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.
Highlighting Cells Rules, Top/Bottom Rules, Data Bars, Icon Sets, Color Scales Editing Standard Formatting Rules, Using Formulas in Conditional Formatting.
Sorting table, Using multiple-level sorting, Using custom sorting.
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).
Naming Cells and Ranges, Use Excel Names, Create a Dynamic Named Range Use a Named Excel Table, Dynamic Named Range Based on Formula.
Relative and Absolute Reference
Relative Reference, Absolute Reference Using cell references with multiple worksheets.
Average, median, mode, large, max, min, percentile, quartile, rank, small average if, averageifs, count, correl.
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.
CLEAN, trim, lower, proper, upper, concatenate, Left, right, mid, right, rept, text Len, find, search, exact, t, char, code, replace, substitute
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.
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.
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.
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.
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.