MS Excel Basic to Advanced

Duration  5 days – 35 hrs.

 

Overview

 

A Microsoft Excel Basic to Advanced Training Course is a comprehensive program designed to teach individuals the fundamental and advanced features of Microsoft Excel, a popular spreadsheet software. The course typically spans multiple days, covering various aspects of Excel functionality to ensure participants gain proficiency from the basics to more advanced capabilities. This course is designed to equip participants with the essential skills needed to navigate and utilize Microsoft Excel efficiently. Starting from the basics and progressing to advanced features, participants will gain hands-on experience, enabling them to confidently handle data, perform complex calculations, create insightful visualizations, and automate tasks using macros.

 

Objectives

 

• Develop a solid understanding of the Microsoft Excel interface, including workbooks, worksheets, and cells.
• Master basic data entry and formatting techniques for effective spreadsheet management.
• Acquire proficiency in fundamental Excel formulas and functions (e.g., SUM, AVERAGE, COUNT) for basic data analysis.
• Explore advanced formulas (IF, VLOOKUP, INDEX, MATCH) to perform complex calculations.
• Learn to create impactful charts and graphs for visual data representation.
• Understand and apply conditional formatting techniques for enhanced data visualization.
• Utilize PivotTables and PivotCharts for efficient data analysis.
• Gain skills in data manipulation using functions like Power Query.
• Learn advanced techniques for data cleaning, including text functions and handling date/time data.
• Understand advanced data analysis tools such as Goal Seek and Solver.
• Work with array formulas for complex calculations and scenario analysis.
• Explore advanced charting techniques, including Sparklines and Combo Charts.
• Create interactive dashboards for dynamic data presentation.
• Introduce the concept of macros and the basics of Visual Basic for Applications (VBA).
• Learn to record and run macros to automate repetitive tasks.
• Build custom functions and gain insights into error handling in VBA.
• Engage in practical, hands-on exercises and projects to reinforce learning.
• Apply acquired skills to real-world scenarios, fostering a deeper understanding of Excel’s practical applications.
• Explore collaboration features, including sharing workbooks and tracking changes.
• Develop skills for efficient collaboration within the Excel environment.
• Apply all learned skills to create an automated reporting macro.
• Present the final project, demonstrating a comprehensive understanding of Excel from basics to advanced automation.

Audience

 

• Suitable for beginners with little or no Excel experience.
• Intermediate users looking to enhance their skills.
• Advanced users seeking to explore automation and VBA.

 

Pre- requisites 

• Foundational computer skills, including file management.

• Understanding of basic Excel operations (opening, saving, navigation).

• Availability of a computer with Microsoft Excel (2016 or later).

• Reliable internet connectivity for course access.

• Positive attitude and eagerness to actively participate.

• Intermediate proficiency for those opting for advanced sessions.

 

Course Content

 

Day 1 – Excel Essentials

1. Productivity tool
   1. Shortcuts and Navigations
   2. Replicating and Clearing formats
   3. Selecting multiple cells based on properties
   4. Flash Fill
   5. Autofill
2. Basic Math Functions
3. Custom Formatting
4. References
   1. Relative and Absolute Cell References
   2. Relative and Absolute Defined Name
   3. Assigning name to formula
5. Excel Table
   1. Features of Excel Table
   2. Structured References in Excel Table

 

Day 2 – Data Management

1. Data Management Tools
   1. Filtering number, text and format.
   2. Advanced Filter Tool
   3. Custom Sorting
   4. Find and Replace
   5. Text-to-Columns
      1. Delimited
      2. Fixed Width
   6. Basic Dropdown Menus and Cell Validations
   7. Remove Duplicates Tool
2. Consolidate Tool
3. Basic Conditional Formatting
4. Security Features
   1. Protecting Cells and Sheets
   2. Assigning Passwords to Files

 

Day 3 – Excel Functions Part 1

1. Concatenations
2. Aggregate functions
   1. Aggregate
   2. Subtotal
3. Conditional Math functions 
   1. SUMIFS, AVERAGEIFS, COUNTIFS
   2. MAXIFS, MINIFS
4. Text Functions
   1.  Format when combining date and numbers
   2. Extract characters from the text
   3. Get the number of characters from the text
   4. Test the content of cell data
5. DATE FUNCTION
   1.  Functions that return to the current date and time
   2. Extracting value from a date
   3. Dynamic Date
   4. A function that returns to the same date with a different month
   5. A function that returns to the end of the month

 

Day 4 – Excel Functions Part 2

1. Lookup Functions
   1. VLOOKUP
   2. XLOOKUP
   3. INDEX-MATCH

 

1. Logical Functions
   1. Basic Logical Functions
   2. Nested conditional statements
   3. Statement from multiple conditions

 

1. Array Formulas and Functions
   1. Single and multi-array formula
   2. Unique, Sort
   3. Sequence
   4. Conditional Filter

 

1. LET and LAMBDA

Day 5 – Analysis and Reports

 

1. Advanced Data Validation
   1. Formula based validation.
   2. Dependent Dropdown

 

1. Advanced Conditional Formatting
   1. Formula based formatting.
   2. Format rows or columns based on conditions.

 

1. Analysis Tools
   1. Goal Seek
   2. Scenario Manager
   3. Data Tables

 

1. Charts
   1. Basic Charts
   2. Combination Charts Overview
   3. Design and Format
   4. Special Charts
   5. Sparklines

 

1. PivotTables
   1. Basic PivotTable Features
   2. Tables
   3. Advanced PivotTable Features
   4. Creating PivotCharts
   5. Slicers

 

1. Introduction to Macros
   1. Macro Recording
   2. Assigning Macros to Buttons