Online
Organized by: PMI KERALA CHAPTER
Time: March 16, 2024 9:00 am to March 17, 2024 6:00 pm
Email: administrator@pmikerala.org
Phone: +919074772731
Maximum Capacity: 25
Open: 20
Microsoft Excel (Version: 2010)
Duration: 2 days
This program is designed for executives who are already familiar with the basics of Microsoft Excel, and who would like to work with more advanced features of Microsoft Excel that help in improving their efficiency of working with worksheets, analyzing data, creating MIS reports, and automating various tasks.
Program Objectives
This Advanced Excel training program will empower the participants to be able to do the following:
• Performing complex calculations more efficiently, using various Excel functions.
• Organizing and analyzing large volumes of data.
• Creating MIS reports.
• Designing and using templates.
• Analyzing data using chart
• Consolidating and managing data from multiple workbooks.
• Recording Macro to automate work
Audience:
Executives and managers who have already been using Microsoft Excel, but now feel the need for learning more powerful features and options of Excel, to manage their worksheet-related tasks more efficiently.
Prerequisites:
Participants attending this training should be familiar with the basic operations in Microsoft Excel, such as simple calculations, formatting and printing.
Modules:
Module 1 – Overview of the Basics
Module 2 – Working with Cell Reference
Module 3 – Working wit Formulas & Functions
Module 4 – Sorting & Filtering data
Module 5 – Working with Styles & Formatting
Module 6 – Working with Vlookup & Index and Match
Module 7 – Working with Range Names
Module 8 – Data Validation
Module 9 – Working with Templates
Module 10 – Working with Reports
Module 11 – More Functions
Module 12 – What-If Analysis
Module 13 – Basic of Macros
Course Outline
Module 1: Overview of the Basics
• Explore the User Interface
• Work with the Ribbon & Backstage view
• Work with Contextual Tabs
• Use the Excel Galleries
• Customize the Excel Interface
Module 2: Working with Cell Reference
• Understanding Cell Reference
• Working with all kind of cell reference (Relative, Absolute & Mixed)
• Understanding Mixed cell reference with some formulas
Module 3: Working with Formulas & Functions
• Working with general & advance function(SUM, AVERAGE, MAX,MIN, LARGE & SMALL,SUMIF,SUMIFS,AVERAGEIF,AVERAGEIFS,COUNTIF,COUNTIFS,RANK)
• Writing conditional expressions (using IF)
• Using Nested IF
• Limitations of IF
• Using logical functions (AND, OR, NOT) inside IF
• Using Date & Time function in IF
Module 4: Sorting and Filtering Data
• Sorting lists
• Sorting data according to color
• Different option of Sort
• Using multiple-level sorting
• Using custom sorting
• Using simple Filter
• Working with top/bottom records
• Working with date filter
• Using wild card in filter
• Working with text filter
• Understanding advance filter
• Copying filter data into different location using
• Working with unique records in filter
Module 5: Working with Styles & Formatting
• Cell Styles
• Creating lists using Table
• Formatting the structure of a list
• Conditional Formatting
• Working with different option of conditional formatting
• Using formula/function in formatting
Module 6: Working with VLookup & Index and Match
• Using simple VLookup
• Using Column function in Vlookup
• Limitation of Vlookup
• Joining two Vlookup using Concatenate function
• Working with Index & Match function
• Understanding Hlookup function
Module 7: Working with Range Names
• Defying a range name
• Defying the scope of a range name
• Modifying/deleting the range name
Module 8: Data Validations
• 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
Module 9: Working with Templates
• Designing the structure of a template
• Using templates for standardization of worksheets
Module 10: Working with Reports
• Creating subtotals
• Multiple-level subtotals
• Creating Pivot tables
• Formatting and customizing Pivot tables
• Using Multiple options of Pivot tables
• Creating group in Pivot tables
• Pivot charts
• Using external data sources
Module 11: More Functions
• Date and time Functions
• Text Functions
• Math & Statistical Function(SUMIF,COUNTIF, COUNT, COUNTA & COUNTBLANK)
Module 12: What-If Analysis
• Using goal seek
• Using data tables
• Creating and editing scenarios
Module 13: Basic of Macros
• Recording and executing macros
• Understanding different types of references in macros
• Assigning macros to Quick Access Toolbar
• Editing macros using VBA Editor
PDUs for Credential Holders: 14 PDUs