Get 20% discount

Microsoft Excel 2010 – Advance Level

Introduction

This course focuses on the more advanced features of Excel 2007. Advanced analysis tools, especially pivot tables, Solver, outlining and summarizing are overed, as are some key automation features such as Macros.

Learning outcomes
At the completion of this course participants will be able to:

- use a range of lookup and reference functions
- modify Excel options
- create and use labels and names in a workbook
- protect data in worksheets and workbooks
- summarise data using subtotals and relative range naming
- use data linking to create more efficient workbooks
- use the Data Consolidation feature to combine data from
- several workbooks into one
- understand and create simple PivotTables
- construct and operate PivotTables using some of the more
- advanced techniques
- use goal seeking to determine the values required to reach a desired result
- group cells and use outlines to manipulate the worksheet
- use Solver to solve more complex and intricate problems
- create recorded macros in Excel
- use the macro recorder to create a variety of macros

Methodology

The programme will be delivered using formal lectures combined with practical and interactive case studies and exercises. There will be a great emphasis on gaining practical experiences.

Contents of Training:

Lookup Functions
● Understanding Data Lookup Functions ● Using CHOOSE ● Using VLOOKUP ● Using VLOOKUP For Exact Matches ● Using HLOOKUP ● Using INDEX ● Using MATCH ● Understanding Reference Functions ● Using ROW And ROWS ● Using COLUMN And COLUMNS ● Using ADDRESS ● Using INDIRECT ● Using OFFSET

Setting Excel Options
● Understanding Excel Options ● Personalising Excel ● Setting The Default Font ● Setting Formula Options ● Understanding Save Options ● Setting Save Options ● Setting The Default File Location ● Setting Advanced Options

Chart Object Formatting
● Understanding Chart Object Formatting ● Selecting Chart Elements ● Using Shape Styles To Format Objects ● Changing Column Colour ● Changing Pie Slice Colour ● Changing Bar Colours ● Changing Chart Line Colours ● Using Shape Effects ● Filling The Chart Area And The Plot Area ● Filling The Background ● The Format Dialog Box ● Using The Format Dialog Box ● Using Themes

Labels And Names
● Understanding Labels And Names ● Creating Names Using Text Labels ● Using Names In New Formulas ● Applying Names To Existing Formulas ● Creating Names Using The Names Box ● Using Names To Select Ranges ● Pasting Names Into Formulas ● Creating Names For Constants ● Creating Names From A Selection ● Scoping Names To The Worksheet ● Using The Name Manager ● Documenting Range Names

Protecting Data
● Understanding Data Protection ● Providing Total Access To Cells ● Protecting A Worksheet ● Working With A Protected Worksheet ● Disabling Worksheet Protection ● Providing Restricted Access To Cells ● Password Protecting A Workbook ● Opening A Password Protected Workbook ● Removing A Password From A Workbook

Summarising And Subtotalling
● Creating Subtotals ● Using A Subtotalled Worksheet ● Creating Nested Subtotals ● Using Subtotals With AutoFilter ● Installing and Using The Conditional Sum Wizard ● Creating Relative Names For Subtotals ● Using Relative Names For Subtotals

Data Linking
● Understanding Data Linking ● Linking Between Worksheets ● Linking Between Workbooks ● Updating Links Between Workbooks

Data Consolidation
● Understanding Data Consolidation ● Consolidating With Identical Layouts ● Creating An Outlined Consolidation ● Consolidating With Different Layouts

PivotTables
● Understanding Pivot Tables ● Creating A PivotTable Shell ● Dropping Fields Into A PivotTable ● Filtering A PivotTable ● Clearing A Report Filter ● Switching PivotTable Labels ● Formatting A PivotTable

PivotTable Techniques
● Using Compound Fields ● Counting In A PivotTable ● Formatting PivotTable Values ● Working With PivotTable Grand Totals ● Working With PivotTable SubTotals ● Finding The Percentage Of Total ● Finding The Difference From ● Grouping In PivotTables ● Creating Running Totals ● Creating Calculated Fields ● Providing Custom Names ● Creating Calculated Items

PivotCharts
● Creating A PivotChart Shell ● Dragging Fields For The PivotChart ● Changing The PivotChart Type ● Using The PivotChart Filter Pane ● Moving PivotCharts To Chart Sheets

Goal Seeking
● Goal Seek Components ● Using Goal Seek

Grouping And Outlining
● Understanding Grouping And Outlining ● Creating An Automatic Outline ● Working With An Outline ● Creating A Manual Group ● Grouping By Columns

Solver
● Understanding How Solver Works ● Setting Solver Parameters ● Adding Solver Constraints ● Performing The Solver Operation ● Running Solver Reports ● Refining Solver Answers

Recorded Macros
● Understanding Excel Macros ● Setting Macro Security ● Saving A Document As Macro Enabled ● Recording A Simple Macro ● Running A Recorded Macro ● Relative Cell References ● Running A Macro With Relative References ● Viewing A Macro ● Editing A Macro ● Assigning A Macro To The Toolbar ● Running A Macro From The Toolbar ● Assigning A Keyboard Shortcut To A Macro ● Deleting A Macro ● Copying A Macro ● Tips For Developing Macros

Recorder Workshop
● Preparing Data For An Application ● Recording A Summation Macro ● Recording Consolidations ● Recording Divisional Macros ● Testing Macros ● Creating Objects To Run Macros ● Assigning A Macro To An Object

Related Courses

Mostofa Monower
  • 26 May - 3 Jun 2017

This training program builds upon advanced level knowledge ...

IT
Price 3,000 Tk + VAT
Khan Mohammad Mahmud Hasan
  • Saturday, May 27, 2017

IT
Md. Shafiul Alam
  • 28 May - 3 Aug 2017

This hands-on object oriented PHP5 and MySQL training ...

IT
Price 18,000 Tk + VAT
Muhammad Rabiul
  • 30 May - 18 Jul 2017

IT

Contact

9140345, 9117179

workshop@bdjobs.com

Bdjobs.com Ltd.

19 th floor (East), BDBL Building (Old BSRS)
12 Karwan Bazaar
Dhaka - 1215

This course is designed for users who are already familiar with basic Excel features and operations and who are now ready to explore more of the advanced analysis and automation tools in Excel.
Arrangement for Certificate, lunch and two tea-break would be made by the Organizer during the workshop.


It was an excellent workshop and I learned many excel related issues from here which will help me to perform my job activities more efficiently and quickly. Md. Jahangir Alam, Manager, Global Factory Ltd.