• logo
  • 9861600203
  • 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.

SHARMILA SHRESTHA

Project Manager

Hello!! It's me Sharmila Shrestha. I am working as IT Project Manager on different project. I had working as an operation Manager at Mentor IT for 5 years.

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

Lalita Maharjan

We learnt many things from Excel training, with the use of it many things can be easily done because of Excel, Trainer was also good at training session.


Write a Message

Security Check
2 + 2 =

    Course Features

  • Total Credit Hours30 hrs
  • Course CostUSD 75.00