Advanced Excel Training in Bangladesh
Searching for the best computer institute for advanced Excel training in Bangladesh? Drop an email to us at firstname.lastname@example.org, we will reply you within the next business day.
IT Training Bangladesh Pvt. Ltd. has been providing advanced Excel Training for different corporate houses, government organizations, NGO and INGO's. Some of the organizations where we have already provided advanced Excel training are Save The Children, IPAS Bangladesh, Hotel Shambala, LGCDP, Rastriya Banijya Bank Retirement Fund, Teku Hospital, Bangladesh Telecom, Ecpat Luxembourg, SAARC Secretariat and many more.
Microsoft Excel is most commonly used to perform simple calculations, data storage, data analysis, report generation and complex statistical computations. Most of the organization uses Excel for data analysis. The present day managerial job requires lots of analysis related work that requires managers to work with data. Microsoft Excel is one the most powerful yet very easy tool to use for data analysis. Some of the popular features of Microsoft Excel for data analysis are lookup, scenario manager, subtotal, advanced filter, conditional formatting, graphs, solver, what if analysis, pivot table, slicer, index and match and so on. It also provides 18 statistical analysis tools and 80 statistical functions. As there are new features introduced in Microsoft Office 2013, we will guide you on using the latest features.
How is the class conducted?
The course is conducted in a group of 4 to 6 students and is led by an expert instructor. Our hands on training on Microsoft Excel provides an in-depth knowledge to the subject matter. The pre-developed exercise files will be performed by the students along with the instructor which makes the training more effective. Moreover the participatory approach makes the class even more interactive where students raise different real time problems they face during their job. All the queries will be addressed with the class itself.
The main objective of this training is to enable the trainees to use advanced features in Microsoft Excel. The objectives are highlighted as:
- To use the advanced features of Excel using Microsoft Office version 2013 and newer versions.
- To improve efficiency in day to day work.
- To use scenarios, goal seek, pivot table for data analysis.
- To present the data in an effective way using sparklines, dynamic charts, pivot charts and dashboards.
- To simplify and streamline common tasks.
Who can join Advanced Excel Training in Bangladesh?
Advanced Excel training is best suited for candidates who often use this application in their day-to-day work and would like to upgrade their knowledge to use the advanced features and perform sophisticated operations. This course has been especially designed for people who need to work with sorting and filtering data, importing and exporting data, and analyzing data. Managers, administrative staffs and analyst are highly recommended to take this training as it will be great help to them for their professional work. Advanced Excel training course also serves as an ideal platform for trainees appearing for excel certification examination and people aspiring to get professional qualification to enter into jobs.
- Professionals managers, analyst
- Administrative staffs of corporate organizations
- Students appearing for certification examination
- Researchers and student requiring statistical data evaluation and presentations.
Being an advance level course, this course obviously desires familiarity with basic knowledge about functions and operation in Microsoft Excel. A familiarity with basic computer operation is also required before enrolling in Advanced Excel Training.
However, this course is also self-sufficient for novice and intermediate Excel users.
Module 1: Advanced Excel Training Syllabus
Use of Outlines and Subtotals
Apply an Outline, Collapse/Expand an Outline, create subtotals, removing subtotals
Subtotals are very handy when we want to group data into different sections and automatically calculate totals for those groups. Subtotals can be applied more than one time which even makes it more powerful.
Working with Multiple Workbooks
Arranging multiple workbooks and linking them
Working with multiple workbooks is necessary when we have data distributed across multiple workbooks in an excel sheet. Let’s say we have a salary sheet of our employees for 12 months and we want to perform certain calculation where data are to be taken from multiple workbooks then the concept of working along multiple workbooks it necessary. Similarly we can also apply formatting to our data across multiple workbooks at once.
Concept of Named Range and using them in formulas
Define a name range, using name ranges in formulas, creating 3d range names and using it in formulas
Named tables or ranges are very useful because it makes our formulae very easy to understand. Instead of giving cell references we can simply give named range in our formulae. We will also learn the concept of name manager, how to remove existing name range.
Knowledge of Tables
Defining a Table from data present in a spreadsheet, changing table name, generating a row for table, adding Table Rows and Columns, calculating a column of a table, sorting tables and using multi level sort, applying filtering
Applying advanced filters
Concept of filters, applying advanced filters to generate reports or data based on the selection criteria, applying OR/ AND condition, remove duplicate data.
Advanced filters are very helpful when we are working with large volume of data. Simple filters can only be useful when we want to view a certain group of data among multiple groups. However, advanced filters can go one step further where we can apply multiple conditions. For example we can generate sales records having value more than 30,000 only.
Knowledge of advanced functions in excel
using important and advanced functions like if, if and (for multiple conditions), vertical lookup (vlookup), horizontal lookup (hlookup), iserror, round, sumproduct, sumif, sumifs, countif and countifs and so on.
Generate a chart and customizing major gridlines and minor gridlines, showing data table along with the chart, displaying values in the chart, using different types of charts
Usage of Auditing Tools
Study the formulas using tracer, dependent and precedent arrows, error checking, evaluate formulae
Auditing tools can be an important tool in excel when we have to work on excel documents prepared by others. When we don’t know how a value is being computed in excel in that case the concept of precedent and dependent arrows are very useful. Precedent arrow shows all the cells on the basis of which a particular value is computed. Similarly dependent arrow shows which cell is dependent on the cell we are currently pointing to. Along with this evaluate formulae is another important feature where we can trace the formulae step by step to see how the value is being computed.
Advanced data analysis techniques in excel
Scenario manager, goal seek, solver for multiple input and analysis, defining constraints in solver, custom views, pivot table and slicer, pivot charts
One of the most important feature of excel is data analysis. When we are working with huge volumes of data and we want to study those data on the basis of different criteria then data analysis technique like pivot table can be very helpful. It automatically groups data into different rows and columns and calculates sum, average, minimum value, maximum value, percentage of total and so on. Along with pivot table we have scenario manager where we can forecast different scenarios and study those scenarios using a summary table. Similarly using goal seek we can study a particular trend of differently. Solver enhances the features of goal seek by allowing us to work on multiple variables. Hence, what if analysis which we perform using scenario manager, goal seek, solver are very important.
Apply formatting based on the values or criteria
Learn how to consolidating worksheets
Suppose we have a sales record of a company for 6 months and we want to summarize those data then consolidating worksheets comes into use. Instead of working manually on individual data, we can consolidate worksheets and summarize those data distributed across multiple worksheets. We can even link the summarized data to the original data.
Performing financial calculations using Time Value of Money Formulae
PV, FV, PMT (Amortization)
Business math and finance related formulas are very useful for people from finance background. We can easily apply formulas like present value, future value and amortization.
Index and Match
Index returns the value for a defined position in a table array whereas match returns the position of a value for a defined value in a table array. We can also merge these two formulas to lookup data in a table and return it.
Recording and Editing Macros
recording a macro, delete a macro, writing visual basic code for a macro
Career Advantage of Learning Advanced Microsoft Excel Training
Microsoft Excel is the most used business software in present day market. Its feature to incorporate various functionalities from simple data entry to complex problem solving and analysis has made it essential component in business applications. Business Managers, Business Analysts, Management Consultants, Accountants, Marketing jobs will be lot easier with the help of Excel. In addition, other careers such as Data Entry Experts, Data Analyst, Administrative Support Assistants, etc. can be pursued from the knowledge of MS Excel.