Advanced Excel Welcome to the our Advanced Excel course detail page


Advanced Excel Training

 

OverviewClass-

 

Our Excel Advanced training will really show you how to make Excel work for you. The course is aimed at fairly experienced Excel users whom need to learn more complex functions, nesting, data manipulation and protect data using the security features. You will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information.

 

Duration: 15 Hours (3 Days)

 

Course Content:

 

Excel 2007 & 2010 Quick Overview


Difference between Excel 2003, 2007 and 2010, Use of Excel, its boundaries & features

  • Formulae that Add/Subtract/Multiply/Divide
  • BODMAS/Formula Error Checking
  • The Sum Function

 

Absolute Referencing


Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References

LOOKUP Functions


The VLOOKUP/HLOOKUP Functions

  • Creating, Formatting Simple PivotTables
  • Page Field in a PivotTable
  • Formatting a PivotTable
  • Creating/Modifying a PivotChart

 

  • IFs and Nested IF Functions
  • Using AND/OR/NOT Functions

 

Statistical Functions

 

  • Using The AVERAGE/COUNT/LARGER/SMALLER Functions

 

  • Adding new calculated Fields / Items
  • Changing the Summary Function
  • Consolidate Pivot table

 

  • MATCH with VLOOKUP Functions
  • INDEX & MATCH Functions
  • OFFSET/ INDIRECT functions

 

  • If Loop and Nested IF Loop Functions
  • Using IF/ISERROR Functions

 

Chart Data Techniques

 

  • Chart Types
  • Adding Title/Legends/Lables
  • Printing Charts
  • Adding Data to a Chart
  • Formatting/Renaming/Deleting Data Series
  • Changing the Order of Data Series

 

Date/Time Functions

 

  • Now & Date Functions
  • Using The Datedif/ Networkdays/ Eomonth Functions
  • Using The Weeknum Functions
  • Using The Edate/ Networkdays.Intl/

 

Weekdays.Intl Functions.

 

Text Functions Using

 

  • Using The Trim/ Clean/ Upper/ Lower Functions
  • Using The Subsitute/ Text Functions
  • Using The Trim/ Clean/ Proper/ Dollar Function/li>

 

  • Input Messages / Error Alerts/ Drop-Down Lists
  • Conditional Formatting

 

  • Sorting by Top to Bottom / Left to Right
  • Creating / Deleting Custom List
  • Sort by using Custom List

 

  • Hyper linking data, within sheet / workbook
  • Linking & Updating links between workbooks & applicatio

 

  • Hyper linking data, within sheet / workbook
  • Linking & Updating links between workbooks & application

 

  • Using SUMPRODUCT Functions
  • Using FLOOR/ CEILING/ MROUND/ MOD/ QUOTIENT Functions

 

Summarizing Data

 

  • SUBTOTALS Formula

 

  • Creating/Working with an Automatic/Manual Outline
  • Grouping / Ungrouping

 

Consolidation


Consolidating Data with Identical/Different Layout

  • Displaying/Removing Dependent & Precedent Arrows
  • Evaluate Formula – Step IN/ Step Out

 

  • Creating Custom Views
  • Displaying Custom Views
  • Deleting Custom Views

 

  • Sharing Workbooks & Tracking Changes
  • Protecting sheets / workbooks / Files

 

  • Importing Data from Database/ Text Files/ Web
  • Exporting Data
  • Changing External Data Range

Scroll to Top