Track
Financial Accounting
Duration
30 hours
Skill Level
Advanced
Language
English
About this Course
Use Excel for efficient data handling with formulas, functions, conditional formatting, and pivot tables.
Learning Mode: Learn at ALC or at Home
Detailed Course Curriculum
Hands-on module breakdown aligned with MKCL production standards and industry requirements.
- Adjusting Row Heights and Column Widths
- Making Columns and rows Fit Automatically
- Adding and Removing Rows and Columns
- Inserting, Deleting, and Copying Sheets
- Renaming and Coloring Sheets
- Sheet Secrets Hiding and Unhiding Sheets
- Contents: Clearing Cell Contents
- Hiding and Unhiding Rows and Columns
- Protect Sheets with Passwords
- Protect Sheets with Permissions
- Lock and Unlock Cells in Excel
- UnLock Columns and Rows in Excel
- Hide Excel Functions and Formula
- Alternate Methods of Protecting Sheets
- Protect Excel Workbook
- Password to Modify the Excel Workbook
- Protect Workbook Structure
- Protect cells with Password
- Making Text Bold, Italic, and Underlined
- Background and Font Color Options
- Changing Font Size with Ease
- Choosing Font Type
- Styling Cell Borders
- Advanced Font Formatting
- Merging and Unmerging Cells
- Aligning Text
- Controlling Text Orientation
- Managing Text Indents
- Wrapping Text within Cells
- Advanced Alignment Options
- Shape Alignment
- Creating Excel Tables
- Styling and Customizing Tables
- Using Total Rows in Tables
- Removing Duplicate Data
- Resizing and Renaming Tables
- Sorting and Filtering Data in Tables
- Advanced Filtering in Tables
- Slicers in Tables
- Converting Tables to Ranges
- Working with Custom Lists
- Cleaning Data
- Find and Replace
- Using the Go to Feature
- Selection Pane
- Select Objects
- Flash Fill for Text
- Flash Fill for Dates
- Flash Fill for Numbers
- Flash Fill for Splitting Text
- Default Fill Series
- Text Fill Series
- Number Series
- Date Series
- Justify
- Fill Options
- Basic Data Sorting
- Sorting with Multiple Criteria
- Sorting by Color
- Customized Data Sorting
- Basic Data Filtering
- Filtering by Color
- Advanced Data Filtering
- Finding Unique values using Filter
- Advanced Filtering with AND & OR Conditions
- Navigating the Ribbon and QAT
- Excel’s Backend Settings
- Formatting Multiple Sheets
- Finding Your Exact Age in Excel
- Excel Speaking Skills
- Excel Shortcuts : Ctrl 1 to Ctrl 0
- Excel Shortcuts : Ctrl A to Ctrl Z
- Excel Shortcuts : CTRL F1 TO CTRL F12
- Excel Shortcuts : CTRL Command keys
- Excel Shortcuts : ALT A TO ALT Z
- Excel Shortcuts for ALT Command keys
- Excel Shortcuts : F1 to F12
- Excel Shortcuts : SHIFT F1 to SHIFT F12
- Excel Shortcuts : CTRL SHIFT keys
- Hyperlinking to Existing File
- Linking to Webpages
- Navigating Within the Same File
- Adding ScreenTips
- Hyperlinks to Objects
- Find Quarter and Year From Dates
- Adding and Subtracting Days
- Adding and Subtracting Years and Months
- WEEKDAY and WEEKNUM Functions
- NETWORKDAYS Function in Excel
- NETWORKDAYS.INTL Function in Excel
- Calculating Exact Age
- Conditional Formatting using Dates
- Creating Dropdown Lists
- Creating Date ‘Data Validation’
- Creating Number Data Validation
- Creating Text Data Validation
- Input Messages and Error Alerts
- Dependent Dropdown Lists
- Go to and Clear Data Validation
- Using Named Ranges
- Prevent Duplicate Entries
- Introduction to Office 2019 and 365 online
- Excel 2019 and Excel 365
- CONCAT, MAXIFS, MINIFS
- Enhanced visuals (Inserting SVG files to your Excel file)
- New and improved connectors
- Apply Custom Formats and Layouts
- Introduction
- Top & Bottom Categories
- Greater Than
- Highlight Cells Rules - Less Than, Between, Equal To, Clear Rules
- Highlight Cells Rules - A Date Occurring
- Highlighting cells rules
- Data Bars, Colors Scales, Icon Sets
- New Rule
- Creating custom number formats
- Understanding underlying codes
- Applying formats
- Creating custom accounting formats
- Custom number formats
- Deleting a custom format
- Excel date calculation
- Creating custom date formats
- Excel date format codes
- Creating custom time formats
- Excel Time-format Codes
- Fill series options
- Linear series
- Growth series
- Date series
- Auto fill
- Creating a custom fill sequence
- Using a custom fill sequence
- Flash fill
- The flash fill icon
- Manage and Share Workbooks-Connect to external data
- Functions
- Introduction to Functions, Common Types of Arguments, Parts of Functions
- Advanced Aspects of Functions, Searching for a Function
- Using the Functions
- Introduction to Excel Formulas and Functions
- Formula Intro & Fundamentals
- Types of Arguments
- Relative Reference
- Absolute Reference
- Formula Reference Styles - Mixed Reference
- Introduction
- Different Text Functions
- Text Extraction Functions
- Text Cleaning Functions
- Text Replacing Functions
- Char and Code Functions
- Type Conversion Functions
- Text Position Finding Functions
- Splitting first and last name
- Extracting Numbers from Description
- Getting word count from cells
- Concatenate Formula
- Changing the case of the text
- Extracting Text
- Extracting parts of a text string
- Introduction to TWOROWS and Their Use
- Introduction to TWOCOLUMN and Its Use
- Introduction to WRAPROWS and Its Use
- Introduction to WRAPCOLUMN
- Explanation of Function
- HSTACK and VSTACK Functions
- Introduction of TEXTAFTER Function & Its Use
- Introduction of TEXTBEFORE Function & Its Use
- Introduction of TEXTSPLIT Function & its Use
- Introduction Basic Mathmatical function & Its Uses SUM, MIN, MAX, AVERAGE
- Uses of LARGE, and SMALL Functions
- Uses of COUNT, COUNTA, and COUNTBLANK Functions
- Introduction and Various Uses of the SUMIF Function
- Uses of the AVERAGEIF and COUNTIF Functions
- Uses of the AVERAGEIF and COUNTIF Functions and Overview
- Create advanced formulas
- Functions
- Getting Workbook and System Informations
- Identifying Types of Errors
- Checking for Empty Cells
- Identifying Error Types
- Handling All Errors
- Detecting Formulas in Cells
- Checking for Logical Values
- Handling ‘Not Available’ Errors
- Filtering Out Non-Text Cells
- Identifying Numeric Cells
- Filtering Odd Numbers
- Validating Cell References
- Checking for Text Data
- Fetching Cell Information
- Filtering Even Numbers
- Handling Omitted Arguments
- Converting Non-Numbers to Zero
- Getting Active Sheet Information
- Counting Sheets in Workbook
- Working with TYPE for Data Analysis
- Combining Conditions with AND
- Using “OR” for Multiple Criteria
- Inverting Logic with “NOT”
- Working with TRUE Values
- Handling FALSE Conditions
- Conditional Logic with IF
- Switching Between Cases
- Exclusive OR Logic
- Getting Characters by ASCII Code
- Fetching Unicode Characters
- Formatting Numbers as Text
- Converting Text to Numbers
- Converting Values to Text Strings
- Joining Array Elements into Text
- Extracting Text from Cells
- Comparing Text for Exact Match
- Replacing Substrings in Text
- Substituting Text Characters
- Repeating Text Strings_New
- Removing Extra Spaces
- Joining Text with Delimiters
- Converting Text to Number Formats
- Formatting Numbers as Currency
- Converting Numbers to Thai Text
- Creating Dates from Components
- Converting Text to Dates
- Calculating Days Between Dates
- Calculating Days in 360-Day Year
- Finding End of Month Dates
- Getting ISO Week Numbers
- Creating Time from Components
- Converting Text to Time
- Calculating Workdays
- Calculating International Workdays
- Calculating Fractional Years
- Calculating Fiscal Year
- Applying Functions Column-Wise
- Applying Functions Row-Wise
- Handling Errors Gracefully
- Substituting ‘Not Available’ Errors
- Multiple Conditions with IFS
- Creating Custom Functions
- Assigning Names to Expressions
- Generating Arrays on the Fly
- Mapping Functions onto Lists
- Reducing Data Sets
- Scanning Data Arrays
- Getting Characters by ASCII Code
- Fetching Unicode Characters
- Formatting Numbers as Text
- Converting Text to Numbers
- Converting Values to Text Strings
- Joining Array Elements into Text
- Extracting Text from Cells
- Comparing Text for Exact Match
- Replacing Substrings in Text
- Substituting Text Characters
- Repeating Text Strings_New
- Removing Extra Spaces
- Joining Text with Delimiters
- Converting Text to Number Formats
- Formatting Numbers as Currency
- Converting Numbers to Thai Text
- Creating Dates from Components
- Converting Text to Dates
- Calculating Days Between Dates
- Calculating Days in 360-Day Year
- Finding End of Month Dates
- Getting ISO Week Numbers
- Creating Time from Components
- Converting Text to Time
- Calculating Workdays
- Calculating International Workdays
- Calculating Fractional Years
- Calculating Fiscal Year
- Generating Cell Addresses
- Retrieving Column Numbers
- Counting Columns in a Range
- Horizontal Data Lookup
- Creating Clickable Links
- Inserting Images via Formula
- Retrieving Row Numbers
- Counting Rows in a Range
- Selecting from a List of Options
- Choosing Columns Dynamically
- Choosing Rows Dynamically
- Dropping Data into Cells
- Expanding Cell Ranges
- Fetching Field Values
- Filtering Data Ranges
- Displaying Formulas as Text
- Extracting Data from Pivot Tables
- Locating Data by Index
- Referencing Cells Indirectly
- Performing General Lookups
- Matching Data Across Ranges
- Shifting Cell References
- Comparison of various lookup functions
- Counting Number of Areas in a Range
- Sorting Data Efficiently
- Sorting by Custom Criteria
- Extracting Data Subsets
- Transforming Rows into Columns_Extra sound at 30
- Transforming Columns into Rows
- Switching Rows and Columns
- Extracting Unique Values
- Advanced Vertical Lookups
- Advanced Data Matching
- Mastering Data Extraction from Pivot Tables
- Visualizing Data Through Column chart
- Visualizing Data Through Line chart
- Visualizing Data Through Pie chart
- Visualizing Data Through Bar chart
- Visualizing Data Through Area chart
- Visualizing Data Through XY (Scatter) chart
- Visualizing Data Through Map chart
- Visualizing Data Through Stock chart
- Visualizing Data Through Surface chart
- Visualizing Data Through Radar chart
- Visualizing Data Through Treemap chart
- Visualizing Data Through Sunburst chart
- Visualizing Data Through Histogram chart
- Visualizing Data Through Box & Whisker chart
- Visualizing Data Through Waterfall chart
- Visualizing Data Through Funnel chart
- Visualizing Data Through Combo chart
- Conversion Between Units
- Decimal to Binary
- Decimal to Hexadecimal
- Decimal to Octal
- Hexadecimal to Binary
- Hexadecimal to Decimal
- Hexadecimal to Octal
- Octal to Binary
- Octal to Decimal
- Octal to Hexadecimal
- Binary to Octal
- Binary to Decimal
- Binary to Hexadecimal
Eligibility Criteria
• Basic knowledge of computers and keen desire to build skills in this field.
• Open to students, job seekers, and working professionals.
• Open to students, job seekers, and working professionals.
Official Certification
• Official MKCL KLiC Certificate upon successful completion of the course and evaluations.
Work-Centric Learning Approach
• Step 1: Learners are given an overview of the course and its connection to life and work
• Step 2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
• Step 3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
• Step 4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
• Step 5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
• Step 6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
• Step 7: Learners are engaged in appreciation of real-life case studies developed by the experts.
• Step 8: Learners are encouraged to proceed from appreciation to imitation of the experts.
• Step 9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
• Step 10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
• Step 11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
• Step 12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!
• Step 2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
• Step 3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
• Step 4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
• Step 5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
• Step 6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
• Step 7: Learners are engaged in appreciation of real-life case studies developed by the experts.
• Step 8: Learners are encouraged to proceed from appreciation to imitation of the experts.
• Step 9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
• Step 10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
• Step 11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
• Step 12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!
Ready to start Utilizing Excel for Data Entry and Analysis: Formulas, Functions, Conditional Formatting, and Pivot Tables?
Join our upcoming batch at ZICA Kalyani center with certified instructors.
Other MKCL Courses
-
Advanced Excel
120 hours -
Tally Prime with GST
120 hours -
Advance Accounting Insights
60 hours