Advanced Excel & MIS

Advanced Excel & MIS

Advanced Excel Course – Complete Syllabus 1. Excel Fundamentals & Advanced Interface • Excel Workbook & Worksheet • Rows, Columns & Cells • Formatting & Cell Styles • Number Formats • Custom Formatting • Freeze Panes • Sort & Filter • Find & Replace • Page Setup & Printing • Excel Options & Customization 2. Advanced Formulas & Functions • Relative, Absolute & Mixed References • Mathematical Functions • SUM, SUMIF, SUMIFS • COUNT, COUNTA, COUNTIF, COUNTIFS • AVERAGE, AVERAGEIF, AVERAGEIFS • MAX, MIN • ROUND, ROUNDUP, ROUNDDOWN • IF, IFS • AND, OR, NOT • IFERROR • Nested IF 3. Lookup & Reference Functions • VLOOKUP • HLOOKUP • XLOOKUP • INDEX • MATCH • INDEX + MATCH • XMATCH • LOOKUP • CHOOSE • OFFSET • INDIRECT • Advanced Lookup Techniques 4. Text Functions • LEFT • RIGHT • MID • LEN • TRIM • CLEAN • UPPER • LOWER • PROPER • CONCAT • CONCATENATE • TEXTJOIN • FIND • SEARCH • SUBSTITUTE • REPLACE • TEXT • VALUE 5. Date & Time Functions • TODAY • NOW • DATE • DAY, MONTH, YEAR • WEEKDAY • WEEKNUM • EDATE • EOMONTH • DATEDIF • NETWORKDAYS • WORKDAY • Date Difference Calculations • Working Days Calculation 6. Data Cleaning & Data Management • Remove Duplicates • Text to Columns • Flash Fill • Data Validation • Error Handling • Blank Data Management • Duplicate Data Identification • Data Standardization • Data Cleaning Techniques 7. Conditional Formatting • Basic Conditional Formatting • Duplicate Values • Top/Bottom Rules • Data Bars • Color Scales • Icon Sets • Formula-Based Conditional Formatting • Dynamic Highlighting 8. Advanced Data Validation • Drop-down Lists • Dynamic Drop-down Lists • Dependent Drop-down Lists • Custom Validation Rules • Input Restrictions • Error Messages 9. Excel Tables • Create Excel Tables • Structured References • Table Formatting • Dynamic Tables • Table Formulas • Sorting & Filtering Tables • Table-based Reports 10. Pivot Tables • Pivot Table Creation • Rows, Columns & Values • Filters • Grouping • Date Grouping • Calculated Fields • Pivot Table Formatting • Slicers • Timelines • Multiple Pivot Tables • Pivot Table Reports 11. Pivot Charts • Column Chart • Bar Chart • Line Chart • Pie/Doughnut Chart • Combo Chart • Pivot Chart • Interactive Charts • Dynamic Chart Reports 12. Advanced Charts & Visualization • Dynamic Charts • Combo Charts • Histogram • Pareto Chart • Waterfall Chart • Funnel Chart • Scatter Plot • KPI Charts • Dashboard Charts • Professional Data Visualization 13. What-If Analysis • Goal Seek • Scenario Manager • Data Tables • Solver Basics • Forecasting • Sensitivity Analysis 14. Dashboard Development • Dashboard Planning • KPI Cards • Interactive Charts • Slicers & Timelines • Dynamic Formulas • Dashboard Navigation • Sales Dashboard • HR Dashboard • Finance Dashboard • Management Dashboard 15. Power Query • Power Query Introduction • Import Data • Excel/CSV Data Import • Multiple File Import • Data Cleaning • Remove Columns/Rows • Split Columns • Merge Columns • Replace Values • Data Transformation • Merge Queries • Append Queries • Group By • Pivot/Unpivot • Refresh Data • Automated Data Preparation 16. Power Pivot & Data Model • Power Pivot Introduction • Data Model • Relationships • Multiple Tables • Calculated Columns • Measures • DAX Introduction • Basic DAX Functions • Pivot Reports using Data Model 17. Financial Excel • EMI Calculation • Loan Calculation • Interest Calculation • PMT • IPMT • PPMT • NPV • IRR • XIRR • Depreciation • Financial Analysis 18. MIS Reporting • MIS क्या है • Daily MIS • Weekly MIS • Monthly MIS • Sales MIS • Purchase MIS • Stock MIS • Attendance MIS • Employee MIS • Performance MIS • Automated MIS Reports 19. Excel Automation • Excel Macros Introduction • Macro Recording • Relative & Absolute Macros • VBA Introduction • VBA Editor • Variables • Conditions & Loops • VBA Functions • Automated Reports • Basic VBA Automation 20. Advanced Excel for Jobs • Data Analyst Tasks • MIS Executive Tasks • Business Analyst Basics • Sales Analysis • HR Analysis • Financial Analysis • Inventory Analysis • Performance Analysis • Real-world Business Data 21. Practical Projects • Sales Dashboard • Employee Salary Sheet • Attendance Management • Inventory Management • Expense Tracker • Customer Database • Sales & Profit Analysis • Monthly MIS Report • HR Dashboard • Financial Dashboard • Business Performance Dashboard 22. Interview Preparation • Advanced Excel Interview Questions • Formula-based Questions • VLOOKUP/XLOOKUP Questions • Pivot Table Questions • Dashboard Questions • Power Query Questions • Data Analysis Questions • Practical Excel Tests • MIS Interview Questions >