Login

Lost your password?
Don't have an account? Sign Up

Excel Advanced Level Training

Overview:
Our Advanced Excel 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.

  • Absolute
  • Relative
  • Mixed Reference
  • Find & Replace – Blanks, Negative, Zero Values
  • Paste Special – Values, Formats, Formulas
  • GoTo Special – Visible Cells
  • Format as Currency, Comma Separator
  • Format as Date
  • Custom Format – Currency, Date & Other
  • Highlight Cells – Single, Multiple Conditions
  • Condition based on selection Referencing
  • Condition based on selection Functions
  • Naming Cells and Ranges
  • Various ways of Naming Cells
  • Using named ranges in formulas
  • IF, Nested IF, AND, OR, NOT
  • Combining Logical Functions
  • VLOOKUP / HLOOKUP
  • MATCH, INDEX
  • INDIRECT
  • COUNTIF, SUMIF, AVERAGEIF
  • COUNTIFS, SUMIFS, SUMPRODUCT
  • Setting Criteria – Text, Values and Dates
  • Drop-Down List
  • Dependent Drop Down
  • Basic Pivot
  • Value Field Setting – SUM, COUNT, AVERAGE, Value as %
  • Advanced Date Grouping & Number Grouping
  • Report Filter – Ultimate Tips
  • Creating Pivot Charts
  • Using Slicers to manipulate PivotTables
  • Basic Sort & Filter
  • Multilevel Sorting, Custom Sort, Create Custom List
  • Advanced Filter – Number, Text, Condition / Filter using criteria
  • Subtotal – Formula, Function
  • Goal Seek, Data Table
  • Consolidation by cell position & headings
  • Creating Multiple Chart Type
  • Primary, Secondary Chart
  • Trend line Charting, Spark Line
  • Dynamic Charting Techniques
  • Custom Formatting
  • Templates
  • PROPER, UPPER, LOWER, TRIM
  • LEFT, RIGHT, MID, FIND
  • LEN, CONCATENATE
  • Text to Columns – Date, Text
  • Formatting Date and Time
  • Advantage of Global Date Format
  • Calculating Dates and Time differences
  • TODAY, DAY, MONTH, YEAR, DATE
  • EDATE, EOMONTH, WEEKDAY
  • WORKDAY, WORKDAY.INTL
  • NETWORKDAYS, NETWORKDAYS.INTL
  • Hidden Function – DATEDIF
  • Format as Table
  • Rename Table, Table Reference
  • Formulas using Table Reference (auto update)
  • Slicer for Table
  • Chart, Pivot Table
  • Formula linking to other sheets
  • Hyperlinks Text – Sheets/Files/Webpage
  • Making Sheets Invisible
  • Preventing Sheets from Deletion
  • Cell/Row/Column Level Security
  • Hide Formulas, Limited access to cell