Microsoft Excel Intermediate

This 2-day intermediate-level course is designed to enhance your Excel skills for more advanced reporting, data manipulation, and analysis. Participants will learn how to create professional reports, handle complex formulas, use pivot tables, generate dashboards, and protect data. This hands-on course will provide the necessary tools to become proficient in using Excel for business decision-making and data presentation.

By the end of this course, participants will be able to:

1. Create advanced reports using Excel.
2. Master worksheet formatting and data organization techniques.
3. Use advanced formulas and functions for data manipulation.
4. Perform data analysis and summarization quickly and efficiently.
5. Build and format charts and PivotTables to display data insights.
6. Protect and secure sensitive data in Excel.
7. Use Excel’s built-in features for efficient data visualization and decision-making.

DAY 1: Advanced Excel Techniques & Data Manipulation

Module 1: Excel Naming Techniques

1. Easier-to-Understand Formulas with Named Ranges.
2. Assigning names to constants and managing names.
3. Name Manager for managing multiple named ranges.
4. Understanding Worksheet vs. Workbook Scope for names.

Module 2: Data Manipulation

1. Sorting Data: Sorting by multiple columns and custom sort options.
2. Auto Filter & Advanced Filter for efficient data filtering.
3. Filtering by number, date, text, and custom conditions.
4. Using Subtotal and Outlines for better data organization.
5. Hands-On Activity: Sort and filter sales data for analysis.

Module 3: Data Validation

1. Verifying data types (text, numbers, dates) with data validation.
2. Customizing error messages for invalid inputs.
3. Advanced Data Validation: Creating custom validation formulas.
4. Hands-On Activity: Set up data validation for a data entry form.

Module 4: Conditional Formatting

1. Applying conditional formatting rules for numbers, text, dates.
2. Using multi-conditional formatting and icon-based rules.
3. Rules Manager for organizing and managing formatting rules.
4. Hands-On Activity: Apply conditional formatting to highlight top performers and low sales.

Module 5: Functions for Data Analysis

1. Text Functions: LEFT, RIGHT, MID, FIND, LEN.
2. Number Functions: SUM, AVERAGE, ROUND, CEILING.
3. Date Functions: DATE, DAY, MONTH, YEAR.
4. Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH or XLOOKUP.
5. Using AND, OR, IFERROR for logical tests.
6. Introduction to new functions in Excel 2010/2013+.
7. Hands-On Activity: Apply functions to calculate discounts, sales totals, and date-based metrics.

DAY 2: Pivot Tables, Dashboarding, and Charting

Module 6: Pivot Tables & Pivot Chart (Excel Dashboard Creation)

1. Introduction to Pivot Tables: Creating and using pivot tables for summarizing large data sets.
2. Drag & Drop Techniques for field placement.
3. Pivot Fields Grouping for better categorization.
4. Applying Subtotals and managing blank lines.
5. Using GETPIVOTDATA to extract data from pivot tables.
6. Hands-On Activity: Create a sales dashboard using Pivot Tables and Charts.

Module 7: Dashboarding

1. Building interactive dashboards using PivotTables, PivotCharts, and slicers.
2. Creating Slicers for better data segmentation.
3. Designing user-friendly reports for easy interpretation.
4. Hands-On Activity: Build a dashboard with filters and slicers for dynamic reporting.

Module 8: Advanced Charting

1. Creating and formatting charts with data labels, axis formatting, and title customization.
2. Combination charts for comparing different data sets.
3. Pie Charts for visual representation of parts-to-whole relationships.
4. Creating Dynamic Charts that automatically update with new data.
5. Using Sparklines for small, in-cell charts.
6. Hands-On Activity: Create dynamic visualizations based on real-time data updates.

Module 9: Protection & Hyperlinks

1. Applying password protection for Excel workbooks and worksheets.
2. Locked and Hidden Cells for data protection.
3. Working with different types of Hyperlinks.
4. Creating hyperlinks to external files and web pages.
5. Hands-On Activity: Secure an Excel file with passwords and add hyperlinks to external resources.

Module 10: Final Hands-On Project

1. Create a complete report with:
   1. Pivot Table data analysis
   2. Conditional formatting and data validation
   3. Charts and a simple dashboard
   4. Hyperlinks and data protection
2. Presentation: Share the report and explain the logic behind each step.

We’d happy to help.
Feel free to talk to us today, however most suitable for you.

Mobile call: +603 – 7493 6868

WhatsApp: +6018 – 380 5878

Email: enquiry@apexitraining.com.my

Website: www.apexitraining.com.my