Advance Excel Training Course

The Advanced Excel Training Program Course covering the basic and advanced modules of Microsoft Excel. In this training program, you will learn how to use Microsoft Excel and the advanced concepts of Excel. These advanced Excel functions can be combined to create some very advanced and complex formulas to use.

What to Learn

Students will be presented with interesting real-life scenarios and have to use the Excel tools, functions and data visualization techniques learned to solve problems, make inferences and present data in a meaningful and effective manner using Excel.

Advance Excel Syllabus

Basics of Excel

  • Spreadsheet Basics
  • Creating, Editing, saving and Printing spreadsheets
  • Data Formatting in Excel like colors, fonts, Bullets etc
  • Basic Sorting and Filtering
  • Basic Formulae like SUM,AVERAGE,COUNT,MAX,MIN etc

Most Useful Shortcut in excel

  • Basic Shortcut
  • Advance Shortcut

Getting Advance in excel

  • Sorting Data by values, colors
  • Filtering by numbers, text, values, colors etc.
  • Using Filters to Sort Data
  • Creating a custom AutoFilter
  • Advance Filtering Options

Custom and Conditional Formats

  • Creating a custom format
  • Create a custom number, percentage, fraction etc. format
  • Conditional Formatting
  • Creating Conditional Formatting
  • Editing Conditional Formatting
  • Adding Conditional Formatting
  • Deleting Conditional Formatting from the selected range

Data Validation

  • Specifying a valid range of value of a cell
  • Validation Criteria
  • Invalid Data Identification

Working with Define Names

  • Using Define names
  • Creating define names
  • Using define names in formulas
  • Name Manager
  • Deleting, Editing names

Using Formulas and Functions

  • Index Function
  • Simple if function
  • If combined with AND/OR
  • Simple Offset function
  • Offset combined with SUM or AVERAGE
  • Choose function
  • XNPV and XIRR function
  • Sum, sum if and count if function
  • PMT FV and IPMT function
  • LEN and TRIM function
  • Concatenate function
  • Cell, Left, Right and Mid function
  • LOOKUP (V-Lookup & H-Lookup) function
  • MAX & MIN function
  • AND & OR Function
  • Round Function
  • Now & Date function
  • Change case function
  • TRIM function
  • REPT function
  • TYPE Function
  • RANDBETWEEN function
  • Convert function
  • PV function
  • Time Function (Weeknum, Workday, Networkday, Yearfrac & Edate function)

Absolute and Relative cell reference

  • Relative cell reference
  • Absolute cell reference

Excel Auditing Tool

  • Trace precedents
  • Trace dependents
  • Remove arrows
  • Error Checking

Explore more in Excel

  • Freeze of windows
  • Splitting of Windows
  • Paste Special            
  • Spark lines
  • Text to columns
  • Consolidate
  • Watch Window
  • Remove duplicates
  • Go to function tab

Managing Tables

  • Creating Tables
  • Naming the Tables
  • Changing the Table style
  • Summarize with PivotTable
  • Using Filtering in Table
  • Removing the duplicate record

Working with Pivot Tables

  • Creating Pivot tables
  • Adding Pivot table Report Fields
  • Refreshing Pivot table Reports
  • Changing the summary functions
  • Creating report filter

Working with Reports

  • Creating Subtotals
  • Multiple Level subtotals
  • Creating Pivot tables
  • Formatting and customizing Pivot tables
  • Using advanced options of pivot table
  • Pivot charts
  • Using external data source
  • Fields, Items and Sets in Pivot Table
  • Viewing subtotal under pivot
  • Creating Slicers

Getting data

  • Creating subtotal
  • Using Automatically Outline
  • Grouping Data Manually

Working with Charts

  • Creating a chart
  • Formatting a chart
  • Adding Labels
  • Changing the chart type, Data source

Worksheet Protection

  • Protecting your work sheet
  • Protecting range with password
  • Protecting your Workbook and Excel Files

What-IF Analysis

  • Goal Seek
  • Data Tables
  • Scenario Manager