Get 20% discount

Microsoft Excel 2010 – Starter to Expert Level

Introduction

The starter level topics to be covered in this course would help the learners to achieve the skills and knowledge to create workbooks in Microsoft Excel. It would cover all the topics to create a new workbook, adding data, editing data, working with formulas, printing and charting. On the intermediate level, the course extends the learner's basic knowledge of Excel and provides the learner with the skills and knowledge to produce more effective and productive workbooks. It covers formulas and function techniques, more intricate formatting, setting complex printing options, using intricate charting features, and working more effectively with existing worksheets and workbooks. Later on the advance level focuses on more advanced features of Excel 2007. Advanced analysis tools, especially pivot tables, Solver, outlining and summarising are covered. Besides, some key automation features of Macros will be covered.

Learning Outcomes:
 Work within the basic excel environment
 create, open and navigate within workbooks and worksheets
 make changes to data in a workbook
 understand, create and work with formulas and functions used to perform calculations
 understand and use formula cell referencing to create more complex formulas
 understand and work with ranges in a worksheet
 copy and paste data in excel
 use font formatting techniques to greatly enhance the look of a worksheet
 align the contents of cells in a number of ways
 format rows and columns in a worksheet
 understand and use the number formatting features in excel
 print your workbook data
 create effective charts in microsoft excel
 use the fill operations available to fill a data series
 move the contents of cells and ranges within and betweenworkbooks
 use a range of formula techniques
 use a range of logical functions
 apply a range of number formatting techniques to data
 apply conditional formatting to ranges in a worksheet
 apply borders to cells and ranges
 work with various elements of a worksheet
 use a range of techniques to work with worksheets
 use a range of find and replace techniques
 apply a variety of page setup techniques
 sort data in a list in a worksheet
 filter data in a table
 use a range of techniques to enhance charts
 apply formatting techniques to text on charts
 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:


Getting To Know Excel
● Starting Excel ● The Excel Screen ● Using The Ribbon ● Using Key Tip Badges ● Minimising The Ribbon ● Using Shortcut Menus ● Launching Dialog Boxes ● Customizing The Quick Access Toolbar ● The Office Button ● Using The Office Button ● The Status Bar ● Customizing The Status Bar

Start Working with Workbooks
● Understanding Workbooks ● Using The Blank Workbook Template ● Typing Text Into A Worksheet ● The Save As Dialog Box ● Saving A New Workbook ● Typing Numbers Into A Worksheet ● Typing Simple Formulas In A Worksheet ● Easy Formulas ● Typing Dates In A Worksheet ● Easy Formatting ● Checking Spelling In A Worksheet ● Making And Saving Changes ● Printing a Worksheet ● Safely Closing a Workbook

Working With Workbooks
● Opening An Existing Workbook ● Moving About A Worksheet ● Moving About A Workbook ● Going To A Specific Location ● The Open Dialog Box

Editing In A Workbook
● Understanding Data Editing ● Overwriting Cells Contents ● Editing Longer Cell Entries ● Editing Formulas ● Editing Functions ● Clearing A Cell ● Deleting In A Worksheet ● Undoing And Redoing Operations

Formulas And Functions
● Understanding Formulas ● Creating Formulas That Add ● Creating Formulas That Subtract ● Formulas That Multiply And Divide ● Understanding Functions ● Using The SUM Function To Add ● Summing Non-Contiguous Ranges ● Calculating An Average ● Finding A Maximum Value ● Finding A Minimum Value ● More Complex Formulas ● What If Formulas

Formula Referencing
● Absolute Versus Relative Referencing ● Relative Formulas ● Problems With Relative Formulas ● Creating Absolute References ● Creating Mixed References Selecting Ranges ● Understanding Ranges ● Selecting Ranges ● Selecting Non-Contiguous Ranges ● Using Special Selection Techniques ● Selecting Larger Ranges ● Selecting Rows ● Selecting Columns ● Viewing Range Calculations ● Creating An Input Range

Copying Excel Data
● Understanding Copying In Excel ● Using Fill For Quick Copying ● Copying From Cell To Another ● Copying From One Cell To A Range ● Copying From One Range To Another ● Copying Relative Formulas ● Copying To A Non-Contiguous Range ● Copying To Another Worksheet ● Copying To Another Workbook

Font Formatting
● Understanding Font Formatting ● Working With Live Preview ● Changing Fonts ● Changing Font Size ● Growing And Shrinking Fonts ● Making Cells Bold ● Italicising Text ● Underlining Text ● Changing Font Colours● Changing Background Colors ● Using The Format Painter ● Applying Strikethrough ● Subscripting Text ● Superscripting Text

Cell Alignment
● Understanding Cell Alignment ● Aligning Right ● Aligning To The Centre ● Aligning Left ● Aligning Top ● Aligning Bottom ● Aligning To The Middle ● Rotating Text ● Indenting Cells ● Wrapping And Merging Text ● Merging And Centering ● Merging Cells ● Unmerging Cells

Row And Column Formatting
● Approximating Column Widths ● Setting Precise Column Widths ● Setting The Default Column Width ● Approximating Row Height ● Setting Precise Row Heights ● Hiding Rows And Columns ● Unhiding Rows And Columns

Number Formatting
● Understanding Number Formatting ● Applying General Formatting ● Formatting As Currency ● Formatting Percentages ● Formatting As Fractions ● Formatting As Dates ● Using The Thousands Separator ● Increasing And Decreasing Decimals Printing ● Understanding Printing ● Previewing Before You Print ● Performing A Quick Print ● Selecting A Printer ● Printing A Range ● Printing An Entire Workbook ● Specifying The Number Of Copies ● The Print Dialog Box

Creating Charts
● Understanding The Charting Process ● Choosing The Chart Type ● Creating A New Chart ● Working With An Embedded Chart ● Resizing A Chart ● Dragging A Chart ● Printing An Embedded Chart ● Creating A Chart Sheet ● Changing The Chart Type ● Changing The Chart Layout ● Changing The Chart Style ● Printing A Chart Sheet ● Embedding A Chart Into A Worksheet ● Deleting A Chart

Filling Data
● Filling A Series ● Filling A Growth Series ● Filling A Series Backwards ● Filling Using Options ● Creating A Custom Fill List ● Modifying A Custom Fill List ● Deleting A Custom Fill List

Moving Data
● Understanding Moving In Excel ● Moving Cells And Ranges ● Moving Data To Other ● Worksheets ● Moving Data To Other Workbooks

Formula Techniques
● Scoping A Formula ● Developing A Nested Function ● Creating Nested Functions ● Editing Nested Functions ● Copying Nested Functions ● Using Concatenation ● Switching To Manual ● Recalculation ● Forcing A Recalculation ● Pasting Values From Formulas ● Pasting Formulas As Pictures

Logical Functions
● Understanding Logical Functions ● Using IF To Display Text ● Using IF To Calculate Values ● Nesting IF Functions ● Using IF ERROR ● Using TRUE And FALSE ● Using AND ● Using OR ● Using NOT

Number Formatting Techniques
● Using Alternate Currencies ● Formatting Dates ● Formatting Time ● Creating Custom Formats
Conditional Formatting
● Understanding Conditional ● Formatting ● Formatting Cells Containing ● Values ● Clearing Conditional Formatting ● More Cell Formatting Options ● Top Ten Items ● More Top And Bottom ● Formatting Options ● Working With Data Bars ● Working With Colour Scales ● Working With Icon Sets

Applying Borders
● Understanding Borders ● Applying A Border To A Cell ● Applying A Border To A Range ● Applying A Bottom Border ● Applying Top And Bottom ● Borders ● Removing Borders ● The More Borders Option ● Using The More Borders Option

Working With A Worksheet
● Understanding Worksheets ● Changing Worksheet Views ● Worksheet Zooming ● Viewing The Formula Bar ● Viewing The Gridlines ● Viewing The Ruler ● Inserting Cells ● Deleting Cells ● Inserting Columns ● Inserting Rows ● Deleting Rows And Columns ● Switching Between Worksheets

Worksheet Techniques
● Inserting And Deleting ● Worksheets ● Copying A Worksheet ● Renaming A Worksheet ● Moving A Worksheet ● Hiding A Worksheet ● Unhiding A Worksheet ● Copying A Worksheet To Another ● Workbook ● Moving A Worksheet To Another ● Workbook ● Changing Worksheet Tab Colours ● Grouping Worksheets ● Hiding Rows And Columns ● Unhiding Rows And Columns ● Freezing Rows And Columns ● Splitting Windows

Finding And Replacing
● Understanding Find And Replace ● Operations ● Finding Text ● Finding Cell References In ● Formulas ● Replacing Values ● Using Replace To Change ● Formulas ● Replacing Within A Range ● Finding Formats ● Finding Constants Using Go To ● Special ● Finding Formulas Using Go To ● Special ● Finding The Current Region ● Finding The Last Cell

Page Setup
● Understanding Page Layout ● Using Built In Margins ● Setting Custom Margins ● Changing Margins By Dragging ●Centring On A Page ● Changing Orientation ● Specifying The Paper Size ● Setting The Print Area ● Clearing The Print Area ● Inserting Page Breaks ● Using Page Break Preview ● Removing Page Breaks ● Setting A Background ● Clearing The Background ● Settings Rows As Repeating Print ● Titles ● Clearing Print Titles ● Printing Gridlines ● Printing Headings ● Scaling To A Percentage ● Fit To A Specific Number Of ● Pages <● Strategies For Printing Larger ● Worksheets

Sorting Data
● Understanding Sorting ● Performing An Alphabetical Sort ● Performing A Numerical Sort ● Sorting On More Than One ● Column ● Sorting Numbered Lists ● Sorting By Rows

Filtering Data
● Understanding Filtering ● Applying And Using A Filter● Clearing A Filter● Creating Compound Filters ● Multiple Value Filters ● Creating Custom Filters ● Using Wildcards

Charting Techniques
● Understanding Chart Layout ● Elements ● Adding A Chart Title ● Adding Axes Titles ● Positioning The Legend ● Showing Data Labels ● Showing A Data Table ● Modifying The Axes ● Showing Gridlines ● Formatting The Plot Area ● Adding A Trendline ● Adding Error Bars ● Adding A Text Box To A Chart ● Drawing Shapes In A Chart

Chart Text Formatting
● Understanding Chart Text ● Formatting ● Using Font Formatting And ● Alignment ● Using WordArt Styles ● Changing Text Fill ● Changing Text Outline ● Changing Text Effects

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

SummarisingAndSubtotalling
● Creating Subtotals ● Using ASubtotalled 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

Abu Shams Mahmood Arif
  • Friday, July 7, 2017

Day to day HR and Admin data analysis, reporting, tools and ...

IT
Muhammad Arif-ur-Rahman
  • 7 - 8 Jul 2017 (2 Days)

If you are a new comer in Excel or after all these years of ...

IT
Mostofa Monower
  • 8 - 21 Jul 2017 (4 evenings)

This training program builds upon advanced level knowledge ...

IT
Muhammad Arif-ur-Rahman
  • 9 - 18 Jul 2017 (4 session)

Advanced Microsoft Excel 2007 & 2010 course to develop ...

IT

Contact

9140345, 9117179

workshop@bdjobs.com

Bdjobs.com Ltd.

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

Anyone who wishes to get expertise on MS Excel.
Arrangement for Certificate, lunch and two tea-break would be made by the Organizer during the workshop.