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.

Talk to us
cover-image
course-dot

duration

45 Hours

course-dot

NEXT BATCH

Nov 04, 2024

course-dot

mode

Both

course-dot

payment options

pay

Programoverview

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!

check-circle1

Enhance job market competitiveness

check-circle1

Boost career advancement prospects

check-circle1

Showcase valuable analytical skills

check-circle1

Facilitate immediate, impactful contributions

eligibility-icon

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.

learn-icon

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.

jobroles-icon

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

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

BROCHURE
Get Brochure
logo
CONTACT US
logo
Enquire this Program

certificate-icon

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.

certificate-image
program-icon

Program Cohorts

Nov 2024 Batch

date

timings

batch type

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

course-imageStep 1

Speak With our Career Counselor

Our career counselor will help you identify the suitable course for you.

course-imageStep 2

Complete Payment

Finalize the transaction securely, using the provided payment methods.

course-imageStep 3

Get Enrolled

Enroll in the chosen course, providing personal details and payment information

video-image

Aswathy A

VP, Product

logo-icon

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.

Companies that our Alumni work in

  • Truspeq.png
  • Applexus.png
  • Allianz.png
  • Tcs.png
  • UST.png

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 more
home-asset

Empower yourself with the skills to manipulate, analyze, and visualize data like never before. Elevate your expertise and excel in the world of analytics.

finance-icon

Financing & Support

course-image

0% Interest Loans

Access 0% interest loans (6/9/12 Months EMI) for your education, ensuring affordability while you pursue your dreams.

course-image

Pay in Installments

Ease your financial burden with our convenient installment payment options.

course-image

Scholarships

We believe in supporting aspiring learners by providing financial aid to help them pursue their dreams.

course-image

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.