Advanced MS Excel

Book Now img

Advanced MS Excel Course Contents Duration : 2 days



1) Conditional formatting & Custom Setting
  • Highlight data on basis of cretin Condition , Add data bar, Trend lines
2) Absolute Referencing
  • Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References
3) PivotTables
  • Creating, Formatting Simple PivotTables
  • Page Field in a PivotTable
  • Formatting a PivotTable
  • Creating/Modifying a PivotChart
4) Chart Data Techniques
  • The Chart Wizard
  • Chart Types
  • Adding Title/Legends/ Lables
  • Printing Charts
  • Adding Data to a Chart
  • Formatting/Renaming/Deleting Data Series
  • Changing the Order of Data Series
5) Logical Functions
  • IFs and Nested IF Functions
  • Using AND/OR/NOT Functions
6) Statistical Functions
  • Using The SUMIF/COUNTIF Functions
  • Using The AVERAGE/COUNT/LARGER/SMALLER Functions
7) Text Functions
  • Using The MID/ SEARCH/ LEFT/ RIGHT Functions
  • Using The TRIM/ CLEAN/ UPPER/ LOWER Functions
8) LOOKUP Functions
  • The VLOOKUP/HLOOKUP Functions
9) Validations
  • Input Messages / Error Alerts/ Drop-Down Lists
10) Date/Time Functions
  • Using The TODAY
  • NOW & DATE Functions
  • Using The DATEDIF/ NETWORKDAYS/ EOMONTH Functions.
11) Fill Series
  • Filling a Series with formatting/without formatting
  • Filling a weekdays
  • Fill data using stop value
12) Filters & Data Sorting
  • Applying & Using AutoFilter
  • Creating a Custom AutoFilter
  • Sorting On More Than One Column
13) Paste Special
  • Paste Special to Copy Formulae/Formats/Validations
  • Paste Special to Add/Multiple & Transpose
14) Working With Worksheets
  • Copying/Moving/Renaming Worksheet(s)
  • Inserting/Deleting/Grouping Worksheets
  • Hiding & Un hiding Worksheets
  • Inserting/Deleting/Resizing Rows/Columns
15)Printing
  • Simple Printing/Previewing
  • Page Setup & Orientation
  • Creating and Formatting Page Headers/Footers
  • Custom views
16) Advance LOOKUP Functions
  • MATCH with VLOOKUP Functions
  • INDEX & MATCH Functions
  • OFFSET/ INDIRECT functions
17) Logical Functions
  • If Loop and Nested IF Loop Functions
  • Using IF/ISERROR Functions
18) Pivot Tables / Advance Level
  • Adding new calculated Fields / Items
  • Changing the Summary Function
  • Consolidate Pivot table
19) Consolidation
  • Consolidating Data with Identical/Different Layout
20) Outlining - Grouping
  • Creating/Working with an Automatic/Manual Outline
  • Grouping / Ungrouping
21) Advanced Filters
  • Extracting Records with Advanced Filter
  • Using Formulas In Criteria
22) Advanced Sorting
  • Sorting by Top to Bottom / Left to Right
  • Creating / Deleting Custom List
  • Sort by using Custom List
23) Summarizing Data - SUBTOTAL
  • Creating Subtotals/Nested Subtotals
  • SUBTOTALS Formula
24) Hyper / Data Linking
  • Hyper linking data, within sheet / workbook
  • Linking & Updating links between workbooks & application
25) Sharing and Protecting Workbooks
  • Sharing Workbooks & Tracking Changes
  • Protecting sheets / workbooks / Files
26) Date/Time Functions
  • Using The WEEKNUM Functions
  • Using The EDATE/ NETWORKDAYS.INTL/ WEEKDAYS.INTL Functions
27) Text Functions
  • Using The SUBSITUTE/ TEXT Functions
  • Using The TRIM/ CLEAN/ PROPER/ DOLLAR Function
28) Using Data Tables
  • Placing Formulas in a Data Table
  • Creating One & Two-Way Variable Tables
29) Using Scenarios & Goal Seek
  • Using the Scenario Manager
  • Creating a Scenario Summary Report
  • Using Goal Seek
30) Using Auditing Tools
  • Displaying/Removing Dependent & Precedent Arrows
  • Evaluate Formula - Step IN/ Step Out
31) Custom Views
  • Creating Custom Views
  • Displaying Custom Views
  • Deleting Custom Views
32) Importing & Exporting Data
  • Importing Data from Database/ Text Files/ Web
  • Exporting Data
  • Changing External Data Range
33) Recorded Macros
  • Recording/Running a Simple Macro
  • Relative Cell References
  • Viewing a Macro Module
  • Executing Macros through shortcuts and command buttons