Excel Advanced Level

Excel Advanced Level Training Programs

Overview

Excel Advanced Level courses enhance skills for individuals with basic Excel knowledge. They cover advanced concepts like pivot tables, data analysis, and VBA automation. Participants will manipulate data, perform complex calculations, and create dynamic visualizations. Excel Advanced Level courses automate repetitive tasks, providing essential skills for workplace value.

Course Objectives

Learn the advanced skills of using Excel. By the end of this course, participants will be able to work with logical functions, data validation techniques, pivot tables and more complex scenarios can be handled in Excel.

Prerequisites

It is recommended that participants have experience with the fundamental use and features of Microsoft Excel including the ability to:

1. Create formulae and basic functions
2. Use Absolute Cell references
3. Edit records using formatting and format techniques inclusive of Charting.

Exercises

This route is trainer led, related to the utilisation of examples and sports in a workshop

Course Duration : 16 Hours

Pre-book and Enroll now

Course contents

Introduction to Excel

  • Start-up with MS Excel
  • Different Versions in Excel
  • Key Terminologies in Excel
  • BODMAS Rule
  • Moving Around Excel
  • Working with Excel
  • Few Familiar shortcuts to be known.

Advanced Formulas and Functions

  • Text Functions: Concatenate, Dollar, Left, Mid, Right, Lower, Upper, Proper, Replace, Rept, Find, Search, Substitute, Trim, Trunc, Convert.
  • Date and Time Functions: Date, Datevalue, Day, Day360, Minutes, Hours, Now, Today, Month, Year, Yearfrac, Time, Weekday, Workday, Networkdays.
  • Logical Functions: If, If with OR, If with AND, If with AND & OR, If with OR & AND. Nested IF
  • Math & Trig Functions: ABS, Aggregate, Power, Product, Rand, Randbetween, Round, Rounddown, Roundup, Subtotal, Sum, Sumif, Sumifs, Sumproduct, Trunc.
  • Statistical Functions: Average, AverageA, Averageif, Averageifs, Count, CountA, Countblack, Countif, Countifs, MAX, MAXA, MIN, MINA, Small, Large.
  • Lookup Functions: VLOOKUP, HLOOKUP, MATCH, INDEX
  • Use of Array in Formulas
  • Use of Named Ranges in Formulas

Structuring of Data

  • Sorting Data with Multiple parameters
  • Custom Sorting
  • Advanced Level Filter with multiple conditions
  • Import Data from other application to Excel.
  • Use of Text to Columns for Rearrangement of Data.
  • Remove Duplicates from Data.
  • Use of Data Validation and Consolidation.
  • Data of Grouping, Ungrouping and Subtotal.
  • What if Analysis: Detailed Discussion on What if Analysis. Analysis of Data by using Scenario Manager and Data Table and Goad Seek.

Working with Pivot Tables

  • Understanding Pivot Tables
  • Start With Pivot Table, Do the Multiple Field Setting in Pivot Table.
  • Recommended Pivot Tables
  • Formatting a PivotTable
  • Creating Groups, Insert additional Calculated Field in Pivot Table.
  • Insert slicer in Pivot Table and Pivot charts.
  • Inserting a Timeline Filter
  • Sorting in a PivotTable

Creating Dashboards

  • Conditional Formatting with multiple cell rules and Top/Bottom Rules.
  • Conditional Formatting with Data Bars. Colour Scales and Icon Sets.
  • Apply any formula to Conditional formatting.
  • Working with Formatting as Table
  • Creating Charts – Column Charts, Line Charts, Pie Charts, Bar Charts, Scatter Charts
  • Preparation of Advanced Level of Charts: Gantt Chart, Bubble Chart, Speedo Meter Chart. Pareto Chart
  • Use of Sparkline to your Sheet, Interactive Sparklines
  • Introduction to Dashboard Creation, Sample Dashboards and making of interactive Dashboard.
  • Introduction to Form Controls: Like Form Control, Combo Box, Check Box, Spin Box, List Box and Option Box.

Workbook Protection and Print Techniques

  • Protect and unprotect Workbook.
  • Protect and unprotect Worksheet.
  • Lock and unlock cells before protection.
  • Save as PDF from Excel Report
  • Save each Sheet as Separate Workbook
  • Modifying Printer Properties and Page Setup (Page, Margin, Header/Footer and Sheet) for Printing.
  • Insert Logo to your worksheet while printing.
  • Repeat First Row in all Pages while printing

Introduction to VBA Macros

  • Understanding Excel Macros
  • Setting Macro Security
  • Saving a Document as Macro Enabled
  • Recording a Simple Macro
  • Running a Recorded Macro
  • Relative Cell References
  • Running a Macro with Relative References
  • Viewing a Macro
  • Editing a Macro
  • Assigning a Macro to the Toolbar
  • Running a Macro from the Toolbar
  • Assigning a Macro to the Ribbon
  • Assigning a Keyboard Shortcut to a Macro
  • Deleting a Macro
  • Copying a Macro

Other Courses

EXCEL – ADVANCED LEVEL

Course Duration: 16 hours
Modes: Online/ Classroom/ Workshop
In Batch Training Cost: Rs.5000
In One-One Training Cost: Rs.10000

Enquiry Form