CoursesAnalytics Advanced Excel
CID11013
Advanced Excel
This course explores advanced features and functions, empowering users in data analysis, modeling, and automation. Topics include pivot tables, advanced formulas, macros, data validation, and conditional formatting, fostering effective data manipulation and visualization for informed decision-making.
duration
45 Hours
NEXT BATCH
Nov 04, 2024
mode
Both
payment options
Program Overview
Unlock Excel's full potential! Enhance data analysis, modeling, and reporting skills. From pivot tables to macros, excel in today's data-driven world. Elevate proficiency, gain a competitive edge, and unlock career opportunities!
Enhance job market competitiveness
Boost career advancement prospects
Showcase valuable analytical skills
Facilitate immediate, impactful contributions
Eligiblility
The Advanced Excel course requires basic proficiency and is open to all levels, beneficial for professionals in finance, accounting, data analysis, marketing, and administration to boost skills and advance careers.
Why should you learn?
Master pivot tables, formulas, macros, and more in our Advanced Excel course. Enhance data analysis, modeling, and automation for confident decision-making and dynamic visualization.
Possible Job Roles
Advanced Excel skills are valuable in roles like Data Analyst, Financial Analyst, Business Analyst, and more, crucial for data analysis, reporting, forecasting, and decision-making across industries.
Salary Range
2.5 to 15 Lakhs Per Annum
Course Modules
45 Hours COURSE
1 hour, 30 minutes
Excel Introduction
+This module covers spreadsheet basics, including layout, navigation, and selections, enhancing efficiency with shortcut keys for improved productivity.
An overview of the screen, navigation and basic spreadsheet concepts
Various selection techniques
Shortcut Keys
1 hour, 30 minutes
Customizing Excel
+Learn customizing Excel's UI and settings, personalize the Ribbon, explore AutoCorrect for text automation, and modify default options for enhanced productivity.
Customizing the Ribbon
Using and Customizing AutoCorrect
Changing Excel’s Default Options
1 hour, 30 minutes
Using Basic Functions
+Covering essential Excel functions like Sum, Average, Max, Min, Count, and Counta, participants learn common calculations and referencing techniques for dynamic formulas.
Using Functions – Sum, Average, Max,Min, Count, Counta
Absolute, Mixed and Relative Referencin
1 hour, 50 minutes
Formatting and Proofing
+Learn effective data formatting in Excel, including currency formatting, using Format Painter, formatting dates, customizing formats, and basic conditional formatting.
Currency Format
Format Painter
Formatting Dates
Custom and Special Formats
Formatting Cells with Number formats, Font formats, Alignment, Borders, etc.
Basic conditional formatting
1 hour, 30 minutes
Mathematical Functions
+Learn advanced Excel functions like SumIf, SumIfs, CountIf, CountIfs, AverageIf, AverageIfs for conditional calculations, Nested IF for complex logic, IFERROR for error handling, and logical functions for decision-making.
SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs, Nested IF, IFERROR Statement, AND, OR, NOT
1 hour, 40 minutes
Protecting Excel
+Learn about protecting Excel files with File Level, Workbook, and Worksheet Protection to prevent unauthorized access or modification, ensuring data integrity.
File Level Protection
Workbook, Worksheet Protection
1 hour, 30 minutes
Text Functions
+Learn Excel text functions: UPPER, LOWER, PROPER for case, LEFT, MID, RIGHT for extraction, TRIM for spaces, LEN for counting, EXACT for comparison, CONCATENATE for joining, FIND, SUBSTITUTE for finding/replacing.
Upper, Lower, Proper
Left, Mid, Right
Trim, Len, Exact
Concatenate
Find, Substitute
1 hour, 40 minutes
Date and Time Functions
+Learn Excel date and time functions like TODAY, NOW for current date/time, DAY, MONTH, YEAR for component extraction, DATE, DATEIF, DATEADD, EOMONTH, WEEKDAY for manipulation.
Today, Now
Day, Month, Year
Date, Date if, DateAdd
EOMonth, Weekday
1 hour, 30 minutes
Advanced Paste Special Technique
+Learn Excel's advanced data manipulation techniques: Paste Formulas, Paste Formats for efficient copying, Paste Validations for consistency, and transposing tables for effective data analysis.
Paste Formulas, Paste Formats
Paste Validations
Transpose Tables
2 hours, 10 minutes
New in Excel 2013/ 2016 & 365
+Learn advanced Excel features: new chart types, combo charts, slicers, Power Map, Power View, Forecast Sheet, Sparklines, 3-D Maps, pivot table controls, timelines, auto-complete, Quick Analysis Tool, Smart Lookup.
New Charts – Tree map & Waterfall
Sunburst, Box and whisker Charts
Combo Charts – Secondary Axis
Adding Slicers Tool in Pivot & Tables
Using Power Map and Power View
Forecast Sheet
Sparklines -Line, Column & Win/ Loss
Using 3-D Map
New Controls in Pivot Table – Field, Items and Sets
Various Time Lines in Pivot Table
Auto complete a data range and list
Quick Analysis Tool
Smart Lookup and manage Store
1 hour, 30 minutes
Sorting and Filtering
+Learn Excel sorting and filtering: filter data by text, numbers, colors, sort data, and advanced filtering methods for precise analysis.
Filtering on Text, Numbers & Colors
Sorting Options
Advanced Filters on 15-20 different criteria(s)
1 hour, 45 minutes
Printing Workbooks
+Learn Excel printing: set print areas, customize headers/footers, design templates, and use print titles for better readability across pages.
Setting Up Print Area
Customizing Headers & Footers
Designing the structure of a template
Print Titles –Repeat Rows / Columns
1 hour, 50 minutes
What If Analysis
+Excel's "What If Analysis" module teaches Goal Seek, Scenario Analysis, Data Tables, and Solver Tool for dynamic scenario exploration and data analysis.
Goal Seek
Scenario Analysis
Data Tables (PMT Function)
Solver Tool
1 hour, 50 minutes
Logical Functions
+Learn Excel logical functions: IF for conditionals, IFERROR for error handling, nested IF for complex logic, and combining IF with AND/OR.
If Function
How to Fix Errors – if error
Nested If
Complex if and or functions
1 hour, 55 minutes
Date Validation
+Learn Excel data validation: validate numbers, dates, times, texts, lists, and use formulas for dynamic validations, and create dropdown lists.
Number, Date & Time Validation
Text and List Validation
Custom validations based on formula for a cell
Dynamic Dropdown List Creation using Data Validation – Dependency List
2 hours, 10 minutes
Lookup Functions
+Learn Excel lookup functions: VLOOKUP, HLOOKUP, INDEX-MATCH, nested VLOOKUP, reverse lookup with CHOOSE, and linking worksheets with INDIRECT.
Vlookup / HLookup
Index and Match
Creating Smooth User Interface Using Lookup
Nested VLookup
Reverse Lookup using Choose Function
Worksheet linking using Indirect
Vlookup with Helper Column
2 hours, 40 minutes
Pivot Tables
+Learn Excel pivot tables: create, configure, filter, and modify; group by numbers/dates; use calculated fields/items; and array formulas for analysis.
Creating Simple Pivot Tables
Basic and Advanced Value Field Setting
Classic Pivot table
Choosing Field
Filtering PivotTables
Modifying PivotTable Data
Grouping based on numbers and Dates
Calculated Field & Calculated Items
Arrays Functions
What are the Array Formulas, Use of the Array Formulas
Basic Examples of Arrays (Using ctrl+shift+enter)
Array with if, len and mid functions formulas
Array with Lookup functions
Advanced Use of formulas with Array
1 hour, 30 minutes
Charts and Slicers
+Learn Excel chart types (bar, pie, line) and slicers for dynamic data filtering, managing axes for clearer interpretation.
Various Charts i.e. Bar Charts / Pie Charts / Line Charts
Using SLICERS, Filter data with Slicers
Manage Primary and Secondary Axis
1 hour, 30 minutes
Excel Dashboard
+Learn to design effective data dashboards in Excel, planning layouts, and incorporating tables and charts for efficient data visualization.
Planning a Dashboard
Adding Tables and Charts to Dashboard
Adding Dynamic Contents to Dashboard
1 hour, 40 minutes
Introduction to VBA
+Introduce participants to Visual Basic for Applications (VBA) in Excel, covering basic VBA concepts, recording macros, and automating tasks.
What Is VBA?
What Can You Do with VBA?
Recording a Macro
Procedure and functions in VBA
1 hour, 40 minutes
Variables in VBA
+Teach effective variable use in VBA, covering variable concepts, data types, non-declared variables, and const variables for defining constants.
What is Variables?
Using Non-Declared Variables
Variable Data
Using Const variables
1 hour, 30 minutes
Message Box and Input Box Functions
+Explore customizing message and input boxes in VBA, learning to personalize messages, read cell values, and use button groups for interaction.
Customizing Msgboxes and Inputbox
Reading Cell Values into Messages
Various Button Groups in VBA
1 hour, 30 minutes
If and Select Statements
+Learn IF statements for basic conditions, ELSEIF for multiple conditions, and SELECT CASE for structured decision-making in VBA.
Simple If Statements
The Elseif Statements
Defining select case statements
1 hour, 40 minutes
Looping in VBA
+Learn basic DO and FOR loops, loop exit methods, and advanced loop examples for efficient task automation in VBA.
Introduction to Loops and its Types
The Basic Do and For Loop
Exiting from a Loop
Advanced Loop Examples
2 hours, 10 minutes
Mail Functions -VBA
+Explore automating email tasks with Outlook in VBA, sending automated emails, configuring settings, and merging and splitting worksheets and files.
Using Outlook Namespace
Send automated mail
Outlook Configurations, MAPI
Worksheet / Workbook Operations
Merge Worksheets using Macro
Merge multiple Excel files into one sheet
Split worksheets using VBA filters
Worksheet copiers
1 hour, 25 minutes
Formulas
+The "Formulas" module covers essential and advanced Excel functions for data analysis and manipulation, providing a deep understanding of Excel formulas.
MAX, IFNA, ODD, EVEN
MAXIFS, MINIFS, RAND, RANDBETWEEN
ROUNDDOWN, COUNTIFS, and 35 additional formulas
Microsoft Office Specialist
Demonstrate that you have the skills needed to get the most out of Excel 2019 by earning the Microsoft Office Specialist (MOS) Expert certification.
Admission Process
Cokonet is the complete career platform that helps you put skill first. Ensure your career is in the driver's seat with courses built for your success
Step 1Speak With our Career Counselor
Our career counselor will help you identify the suitable course for you.
Step 2Complete Payment
Finalize the transaction securely, using the provided payment methods.
Step 3Get Enrolled
Enroll in the chosen course, providing personal details and payment information
Aswathy A
VP, Product
Course Details
This advanced Excel course offered by Cokonet Academy covers everything from basic-level Excel concepts to advanced-level Excel concepts.
This course can help advance your career by enhancing your analytical skills, improving productivity, and making you more valuable to employers.
Cokonet offers genuine placement assistance to the learners. It is imperative to note that getting placed is solely based on the competence of the candidate attending the interview.
All our classes are conducted through Zoom platform.
Yes, we provide a group where students can connect, ask questions, and share insights.
Our instructors are experienced professionals with a strong background in Excel and related topics. They are working professionals who will bring the wealth of practical knowledge and real-world experience to the classroom, ensuring an enriching learning experience for participants.
Companies that our Alumni work in
Join Cokonet
Identify your suitable courses in a few clicks
Find from a list of 60+ courses to launch your career.
corporates
Looking to enroll your employees into this program?
Our Hire-Train-Transfer model revolutionises corporate talent acquisition by seamlessly connecting businesses with skilled professionals through customised training programs.
Know moreOur Corporate Training Services, delivered by industry experts, equip employees with the latest skills and certifications, enabling them to meet project demands and excel in cutting-edge domains.
Know moreCokonet's upcoming job portal, SurePool.in, provides top-notch staffing services, connecting businesses with qualified professionals for seamless workforce augmentation.
Know more
Financing & Support
0% Interest Loans
Access 0% interest loans (6/9/12 Months EMI) for your education, ensuring affordability while you pursue your dreams.
Pay in Installments
Ease your financial burden with our convenient installment payment options.
Scholarships
We believe in supporting aspiring learners by providing financial aid to help them pursue their dreams.
Laptop Support
Seamless assistance with our comprehensive laptop support services.
Not Sure About Learning Excel?
Check the FAQs to understand why Excel is one of the most sought-after skills by employers.
Advanced Excel skills are crucial because they enhance your ability to analyze data, make informed decisions, and increase efficiency. Proficiency in Excel is often a key requirement for many roles across various industries, making you a more competitive candidate.
Advanced Excel functions, such as pivot tables, macros, and complex formulas, automate repetitive tasks, streamline data management, and facilitate faster and more accurate data analysis. This significantly boosts productivity and allows you to focus on more strategic tasks.
Advanced Excel skills are beneficial in virtually all industries, including finance, marketing, human resources, operations, supply chain management, and healthcare. Any role that involves data management and analysis can greatly benefit from advanced Excel proficiency.
Advanced Excel skills include the ability to use pivot tables, perform complex data analysis with functions and formulas, create and manage macros, use advanced charting techniques, perform data visualization, and utilize data validation and conditional formatting effectively.
Yes, certifications such as the Microsoft Office Specialist (MOS) Excel Expert certification can validate your advanced Excel skills. These certifications are recognized by employers and can enhance your resume, demonstrating your commitment to professional development.
