MICROSOFT EXCEL ADVANCED TRAINING MO-201

Module 1: Managing Workbooks and Worksheets

• Navigating Workbooks and Worksheets
• Create, open, and save workbooks and worksheets.
• Use keyboard shortcuts for navigating workbooks.
• Work with multiple workbooks and windows simultaneously.
• Configuring Workbook Options and Settings
• Set workbook properties.
• Customize the Quick Access Toolbar (QAT) and Ribbon.
• Set advanced options (e.g., AutoSave, Workbook Properties).
• Managing Workbook Views
• Use Freeze Panes, Split, and Zoom.
• Create and manage custom views.
• Set print areas and page layout options.

Module 2: Managing Data

• Sorting and Filtering Data
• Use advanced sort options (multiple levels, custom sorts).
• Apply and customize filters for large datasets.
• Utilize slicers and timelines for filtering PivotTables.
• Using Flash Fill and Data Validation
• Automatically format data using Flash Fill.
• Set up custom data validation rules (e.g., date, number ranges).
• Use Data Validation to restrict input.
• Text-to-Columns and Data Import/Export
• Split text using the Text-to-Columns wizard.
• Import and export data from external sources (e.g., CSV, Access).
• Clean data for analysis using advanced text functions.

Module 3: Advanced Functions and Formulas

• Using Logical Functions
• Apply IF, AND, OR to create conditional logic.
• Work with IFS, SWITCH functions for more complex logic.
• Lookup and Reference Functions
• Use VLOOKUP, HLOOKUP, and XLOOKUP for data retrieval.
• Work with INDEX, MATCH, and INDEX/MATCH combinations.
• Use INDIRECT, OFFSET, and ROW functions for dynamic ranges.
• Advanced Date and Time Functions
• Calculate dates and times with DATE, DATEDIF, WORKDAY, and more.
• Use EOMONTH, NETWORKDAYS, and other date-related functions.
• Text Functions for Advanced Data Manipulation
• Use LEFT, RIGHT, MID, TEXT, and TRIM to manipulate text data.
• Concatenate and extract parts of strings using functions like CONCATENATE, TEXTJOIN, and SUBSTITUTE.

Module 4: Working with PivotTables and PivotCharts

• Creating PivotTables
• Design and build PivotTables to summarize large datasets.
• Group data in PivotTables (e.g., by date, categories).
• Customize PivotTable fields, layouts, and formatting.
• Using PivotCharts
• Create PivotCharts from PivotTables for data visualization.
• Customize and format PivotCharts for clarity and insights.
• Advanced PivotTable Techniques
• Use calculated fields and items in PivotTables.
• Use multiple consolidation ranges in PivotTables.
• Filter data with slicers and timelines for interactive reporting.

Module 5: Data Visualization with Advanced Charts

• Creating Advanced Charts
• Create and format complex charts (e.g., combo charts, waterfall charts).
• Use Excel’s built-in advanced chart types like Treemap, Sunburst, and Histogram.
• Add secondary axes to a chart.
• Using Conditional Formatting
• Apply advanced conditional formatting (e.g., icon sets, data bars).
• Use conditional formatting with formulas for dynamic visual changes.
• Visualizing Data with Sparklines
• Create sparklines to visualize trends in individual cells.
• Customize and format sparklines for effective analysis.

Module 6: Advanced Data Analysis Techniques

• Working with Arrays
• Create and manage array formulas.
• Use SEQUENCE, UNIQUE, FILTER, and SORT for dynamic arrays.
• Using What-If Analysis
• Implement Goal Seek to find specific values for target results.
• Create and analyze Data Tables for scenario modeling.
• Use Solver for optimization problems.
• Performing Statistical Analysis
• Use statistical functions like AVERAGEIFS, MEDIAN, and STDEV.
• Analyze data with CORREL, FREQUENCY, and other analysis tools.

Module 7: Automating Excel Tasks

• Creating Macros
• Record macros to automate repetitive tasks.
• Edit macros using the Visual Basic for Applications (VBA) editor (basic).
• Assign macros to buttons and other controls in Excel.
• Using Excel’s Power Query
• Import and clean data with Power Query for efficient data analysis.
• Transform and reshape data from different sources.
• Using Power Pivot for Data Modeling
• Create data models using Power Pivot.
• Work with relationships, calculated columns, and DAX formulas.

Module 8: Collaboration and Sharing

• Sharing and Protecting Workbooks
• Share workbooks for collaboration.
• Protect sheets and workbooks with passwords and restrictions.
• Tracking Changes and Reviewing
• Use Track Changes to monitor workbook modifications.
• Add comments and notes for collaboration.
• Use Compare and Merge Workbooks for collaborative work.