• logo
  • 9762422336
  • 5555545
  • info@mentorit.com.np
header-logo.png

Overview

Importance
Advance excel is a spreadsheet tool that is used to store and make sense of all the data that are generated. The sheer amount of data can be presented visually giving you the comfort of using graphics, data can also be calculated, organized and evaluated by the use of Advance Excel. This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does,  we teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. We will not train you to regurgitate functions and formula syntax; We will teach you how to THINK like Excel.
This course aims to provide trainees with knowledge that will further boost their ability to perform complex business operations and overall management functions.
Benefits of the course:

  • High demand of data analysts
  • Forecasting and predicting of  future performance
  • Can organize data in better way
  • Process data and analyze with graphs and charts
  • Sufficient training materials
  • Highly qualified trainer 
  • Practical classes to implement Advance Excel in real work situations
  • Chances of promotion in an existing job
  • International Job opportunities
  • Ability to Present your data in an organized and effective manner

Popular Career opportunities

  • Data Analyst
  • Business Analyst
  • Financial Analyst
  • Data Management
  • Big Data Engineer
  • Project Manager
  • Administrative Assistant
  • Accountants
  • Market Research Analyst

Course Content

  • Dates and time in Excel
  • Inserting and formatting dates
  • Entering date functions
  • Using dates in formulas
  • What is a Table?
  • Creating Tables
  • Changing the Table Range
  • Inserting Table Columns
  • Inserting Table Rows
  • Deleting Rows or Columns
  • Sorting and Filtering Tables
  • Sorting Data in a Table
  • What is an AutoFilter?
  • Custom AutoFilters
  • Removing Filters
  • Filtering with Wildcard Characters
  • Using an Advanced Filter
  • Creating Subtotals
  • Use Slicers to filter tables
  • Creating a Chart
  • Manipulating a Chart
  • Moving and resizing a chart
  • Adding a chart title
  • Adding a chart Axis title
  • Changing the Type of Chart
  • Formatting a Chart
  • Recommended Charts
  • Quick Analysis Option
  • Chart formatting control
  • The IF function
  • Using the Function Library
  • Manually entering a function
  • Conditional Formatting
  • Applying Conditional Formatting
  • Creating a Conditional Formatting Rule
  • Removing Conditional Formatting
  • Using the Manage Rules Option
  • Adding Conditional Formatting using the Quick analysis tool
  • Working with Names and Ranges
  • What Are Range Names?
  • Defining and Using Range Names
  • Using Logical Functions
  • Using the Function Library
  • Manually entering a function
  • The IF function
  • The AND Function
  • The OR Function
  • Working with Nested Functions
  • Nested IF functions
  • Applying Logical Functions
  • Using VLOOKUP to Find Data
  • How to Find an Exact Match with VLOOKUP
  • Finding the Closest Match with VLOOKUP
  • How to use the MATCH Function
  • Index Function
  • Lookup using Match and Index Function
  • Using Multiple Worksheets
  • Navigating between sheets
  • Creating -D formulas
  • Consolidating data
  • Linking your workbooks
  • Linking Workbooks
  • Using the Paste Link option
  • Creating a formula to link workbooks
  • Controlling link updates
  • Manually update all of the links or none of the links in a workbook
  • Manually update only some of the links to other workbooks
  • Dealing with Broken Links
  • Comments
  • Adding, editing and deleting comments
  • Excel Workbook and Worksheet Protection
  • Protecting your Worksheets
  • Unlocking Cells
  • Validating Your Data
  • Creating Data Validation Settings
  • Using Database Functions
  • Creating a Criteria Range
  • Entering the Database Function
  • What is a PivotTable?
  • Preparing Data to Create a PivotTable
  • Creating a PivotTable
  • Quick Analysis
  • Adding Fields to the PivotTable
  • Creating a PivotTable Frame (Classic PivotTable Layout)
  • Rearranging PivotTable Data
  • Hiding and Showing Field Data
  • The PivotTable Tools Ribbon
  • Modifying Calculations and Data Area Display
  • Formatting a PivotTable
  • Refreshing a PivotTable
  • Time Grouping in PivotTables (Excel )
  • Using Slicers to Manipulate PivotTables
  • Use Timelines with Pivot Data
  • Creating a PivotChart
  • Using Scenarios in Excel
  • Creating a Scenario
  • Using Goal Seek
  • Using Solver
  • Checking Solver Installation
  • Setting Solver Parameters
  • What is a macro?
  • Creating a macro
  • The Developer Ribbon
  • Recording a macro
  • Playing a Macro
  • Macro Security
  • More Macro Tasks
  • Recording a Relative Reference Macro
  • Running a Relative Reference Macro
  • Assigning a Keystroke to a Macro
  • Copying a Macro from a Workbook or Template
  • Viewing and Organising Spreadsheet Data
  • The Zoom Feature
  • Hide rows and columns
  • Using Automatic Outlining
  • Grouping Data Manually
  • Printing Large Worksheets
  • Print Titles
  • Page Break Preview
  • Inserting a Page Break
  • Printing Multiple Sheets
  • Using Templates
  • What is a Template?
  • Creating a Workbook from a Template
  • Creating a Template

About the instructor

DIPU MAHARJAN

BI Solution Architect

Hi I am Dipu Maharjan. I am working as BI Solution Architect. I have experience more than 10 years as Data Analyst, Database Programmer, Developer and Trainer.

Subash Manandhar

Software Engineer

Hello, It's me Subash Manandhar graduated as Computer Engineer from KEC Kalimati, Kathmandu. I am currently working as Software Engineer at Cotiviti Nepal Pvt. Ltd .

Jes Shakya

Software Engineer

Hello, it's me Jes Shakya, currently working as a Software Engineer in Cotiviti Nepal Pvt. Ltd. and a freelance full stack web developer.
reviewer image

Roshan Shrestha

After the Advanced Excel training it help me to enhace my skill and help me to do work in short time for the comlicataed task.

reviewer image

Roshani Hamal

It was nice experience to have training session with respected Dipu Sir. Thank you

reviewer image

Ranjana koirala

This course was really effective.It helps me in my daily work.

reviewer image

Sunil Tandukar

If the exercises could be done on the actual field of data of the project. It would be more beneficial.

reviewer image

Jayanti Sedhain

Training was very fruitful.Thank you for the training.


Write a Message

Security Check
2 + 2 =

    Course Features

  • Course Price10,000
  • Course Duration30 hrs