Master Advanced Excel Skills with Expert Training at Code IT, Nepal
Enhance Your Data Analysis & Reporting Skills with Advanced Excel Techniques
Duration: 21 Days
Fee: Rs.1499 /-
Rs.8500
Course Overview
Advanced Excel Training at Code IT is designed to help professionals, students, and business owners develop advanced data management and analysis skills. This course covers complex formulas, pivot tables, data validation, conditional formatting, and dynamic charts to enhance efficiency and decision-making.
Participants will gain hands-on experience with Power Query and Power Pivot, enabling them to handle large datasets, automate data processing, and create insightful reports. The training also focuses on dashboard creation, helping learners visualize key metrics and trends for business intelligence.
With expert-led instruction and practical exercises, this course ensures a deep understanding of Excel’s powerful tools, making it ideal for those looking to enhance productivity and streamline data-driven tasks.
Materials included
Free Certificate
Life time video access
Future Support
Live sessions on Google Meet
Requirements
Anyone can join this course
Course Syllabus
Introduction to Excel and Formatting excel work book
- How to open excel
- Title bar
- Quick Access Tool bar
- Tabs(Home, insert, page layout…..)
- Ribbon Hide/Show
- Name Box
- Formula bar
- Rows, columns, zoom in zoom out..
- Sheet(Create and rename)
- Row & Column (height, width..)
- Fill Handel
- Formatting Cells(font, size,color…)
- Alignment( top, middle,bottom…)
- Wrap text
- Merge and center
- Inser Row and Column
- Number section(cell style)
- Status Bar
- File Save & Save as
Data entry and Formatting
- Data entry work
- Data transpose
- Fill series
- Keyboard navigation
- Data entry Trick
- Data entry form
- Data validation
- Flash fill
- Gridline, formula bar, heading…hide/show
Number, Editing
- Across worksheet
- Cell format &style(pcs, Rs,..)
- Clear format & input data
- List show trick(alt+pgdn)
- Blank cell find and auto fill
- Blank row find and delete
- Remove duplicate
- Convert number into Roman number
- Find Square
- Find Cube
- Find Cube
Basic Formulas and Functions
- Arithmetic Operations
- Using Sum, Average, Min, Max function
- Cell References (relative and absolute)
- Percentage
- Decrease & increase Decimal
- Count Number
- Trace Presidents
- Trace Dependents
- Freeze pane
- Data selection trick
- Clear data
Perform Calculations with Functions
- How to Apply Formula
- SUM
- Subtraction
- Multiplication
- Percentage
- Count
- Counta
- Countblank
- Countif
- Rank
- Logical Functions (If, and, or, if or)
- Rank
Logical Function
- Logical functions: IF, AND, OR.
- Nested formulas.
- Text Function: CONCATENATE, LEFT, RIGHT.....
Lookup Function
- Using LOOKUP, VLOOKUP and HLOOKUP.
- Introduction to XLOOKUP
- Using INDEX and MATCH for advanced lookups.
Types of Error
- #### error
- #NAME? ERROR
- #VALUE! ERROR
- #DIV/0! Error
- #NULL! ERROR
- #REF! ERROR
- #N/A ERROR
- #NUM! ERROR
Data Analysis
- Sort
- Filter
- Conditional formatting
- Highlight Cell Rules
- Top Bottom Rules
- Data Bars
- Colors Scales
- Icon Sets
- Manages Rules
- Quick Analysis
- Data Analysis
Working with Tables
- Creating and formatting tables
- Using table features like filters
- Total row and structured references
- Convert table to Range
Date Formulas
- Day, Month, Year
- Date
- Text
- Eomonth
- datedif
- Networkdays.Intl
- Workday.Intl
- Edate
- Weeknum
- Number to month
- AD2BD
- BD2AD
- Convert Date Format
- Number to word
Data cleaning and Preparation
- Remove Duplicates
- Remove Blanks Rows
- Remove blank Space
- Change case using formula
- Proper, upper,lower,
- Fix negative stock values
- Split data
Chart
- Creating basic Charts
- Formatting chart elements
- Using different chart types
Pivot Table
- Pivot table
- Pivot chart
- Slicer and slicer settings
- Timeline
- Grouping, custom grouping
- Pivot table option setting
- Show value as
- Calculated field
- Filters and filter report pages
- Dynamic pivot table
- Pivot table on multiple sheets
- Getpivot data formula
- Auto refresh pivot table
- Creating Dashboard Using pivot table
Power Query
- Split column in power query
- Group by Power Query
- Unpivot Column
- Append Query
- Merge Query
- Append Multiple Table using Power Query
- Date function in power query
- Time Difference in power query
- Add Column in Power Query
Statistical Formula
- Sumif/Sumifs
- Countif/Countifs
- Averageif/ Averageifs
- Maxif/Maxifs
- Minif/Minifs
Advanced Features
- Protecting worksheets and workbooks
- Macros basics
- Automatic data enytry form
Page Setup
- Paste Special
- Page Setup
- Print titles
- Heading
- Header & Footer
- Page num, time,date,file path,picture
- Print area set
- How to print
