Home
about banner

Advanced Excel





Advanced Excel
Course Duration: 2 Day( 14 Hours).

1-Using the Interface
  • Using the Backstage View
  • Use the Quick Access Toolbar
  • Customize the Quick Access Toolbar
  • Use Ribbons and Tabs
  • Use Contextual tabs
  • Use Live Preview
  • Shortcut keys
2. Using References in Excel
  • Relative cell reference
  • Absolute cell reference
  • Mixed cell reference
  • Using Name Manger (Define, Search, Edit and Delete Names)
3. Using various types of Functions in Excel
  • Summary Functions (Sumif, Sumifs, Countif, Countifs, Averageif, Subtotal etc.)
  • Date Functions (Today, Now, Month, Year, Datedif, Weeknum etc.)
  • Text Functions (Left, Right, Mid, Concatenate, Trim, Upper, Lower, Proper etc.)
4. Using Lookup and Conditional Functions in Excel
  • Conditional Functions (If, And, Or, Nested If etc.)
  • Lookup Functions (Vlookup, Hlookup, Match, Index etc.)
5. Validating and Protecting your work
  • Data Validation
  • Hiding formulas
  • Protect sheet
  • Allow working post protection
  • Protect workbook
  • Encrypt document
6. Extracting useful info
  • Auto Filter
  • Advanced Filter
  • Remove duplicate data
  • Consolidate
7. Arranging your data
  • Single column sort (Quick sort)
  • Multiple column sort (Data sort)
  • Color and custom sort
8. Format Data conditionally to stand out
  • Format data using fixed value
  • Format data using Absolute cell reference
  • Format data using Mixed cell reference (Comparison)
  • Format data using Formula
  • Format data using Data Bars
  • Format data using Color Scale
  • Format data using Icon Set
  • Modify and Remove CF
9. Using Tables to Summarize and Visualize your data
  • Creating Pivot Table
  • Creating Table to add dynamism to your data (Auto update
  • Use of sections and Layout (Row, Column, Filter and Values)
  • Using Predefine functions and values format
  • Insert Slicer
  • Insert Timeline
  • Add design to make reports attractive
  • Add charts to make summary visual base
10. Analyzing Data using predefine tools
  • Goal seek
  • Data Table
  • Scenarios (Optional)
11. Macros
  • Record macros
  • Add button to run a macro
  • Add quick access toolbar icon to personal macro
  • Edit, Delete and Customize macros using VBA