Advance Excel Module I
Skip Ribbon Commands
Skip to main content
Skip Navigation Links.
Course Contents
1. Formula Knowledge
  • Learn to use SUMIF, COUNTIF and SUMPRODUCT to add cells only when certain conditions are met
  • Learn about creating and updating external links, and about the potential dangers of external links in Excel
  • Understand how dates work in Excel using the TODAY, YEAR, MONTH, DAY and DATE functions
  • Learn to work with the EOMONTH and EDATE functions that easily shift dates from one month or year to another
  • Using Logical Functions making more complex by nesting AND or OR within them.
  • Using Error Functions and understand the errors type 
  • Learn to break apart text with the LEFT, RIGHT, TRIM functions, and to combine text with the & character and learn how to convert text in different cases
  • Learn to apply simple statistical functions and make the life easier
  • Learn how to use transpose function and linked the cells to source data
  • Learn to use more advance lookup functions to overcome a lot of limitations
  • Familiar with the power of DSum, DCount and more data formulas
2. Optimizing Data
  • Learn to apply advance filter
  • Learn how to extract filtered data in different worksheets and bypass the Excel’s limitation
  • Learn about creating Excel tables, and their advantages
  • Using formulas in data validation function to avoid errors caused by erroneous user inputs, or use it to give helpful tips to a user of your Excel spreadsheets
  • Learn advance tools to sort data based on multiple criteria
3. Using Named Range
  • Understanding the named range concept and assigning names
  • Learn how named range make life easier in complex formulas
  • Learn using name ranges in 3-D formulas
  • Creating 3-D named ranges
4. Data Analysis
  • Learn to use pivot tables for ultra-flexible and insightful analysis and reporting
  • Learn usage of advanced PivotTable techniques to do more with your data 
  • Learn using PivotTables as the calculation engine behind management reports 
  • Learn to create and modify basic calculated fields for PivotTables
  • Learn how slicer and sparklines make your PivotTables more interactive
  • Learn working with Pivot Charts
  • Learn building and interactive dashboard using PivotTables and Slicers
5. Presenting & Reporting
  • Learn to create custom conditional formatting rules based on formulas
  • Learn types of number and excel formats best employed
  • Custom number formats and how to maximize impact and clarity
  • Learn outlining and grouping large data
  • Learn using subtotals functions for large data
6. Developing Excel Charts
  • Learn the key components of Excel charts and how they interact with underlying data
  • Master essential chart functionality to have the skills to tailored the chart to your audience/purpose
  • Master the magic of F11, for customised instant analysis
  • Gain insights from charting of multiple data series on separate axes which is a powerful way of highlighting correlated drivers and results
  • Prepare mixed charts that inspire your boss
  • Learn to change the boring shapes of charts with pictures
  • Taking dynamic pictures with the Excel Camera
  • Learn using the Excel camera to combine areas from multiple sheets on one papers
  • Review tips and tricks to give you increase confident in advanced Excel charting techniques
  • Developing the Excel Chart and apply conditional formatting to give powerful impact.
7. Introduction to Macros in Excel
  • Understanding what VBA is and it increase efficiency
  • Walk-through of recording, reading, and editing your own macro
  • Gain insight to use relative and absolute references
  • Understanding macro security
8. Protecting Your Data
  • Learn workbook protection
  • Gain insight how to protect cells
  • Learn protecting the structure
  • Learn how to track changes
9. Formula Auditing
  • Understanding and debugging spreadsheets
  • Tracing calculations and monitoring the effect of changes
  • Knowing formula evaluator and watch window
  • Understand the different categories of errors and how they can be identified (before it is too late)
  • Learn to work with formula auditing tools including tracing precedent and dependent cells and formula evaluation 

Training Objectives
Having practiced all of these new skills extensively during the course enabling the delegates to hit the ground running on their return to the office and should be able to complete their Excel work far more quickly.

Benefits to Organization

By allowing employees to upgrade their skills to higher level, employees are empowered to find new and innovative ways to perform their jobs.  
Advance techniques in MS Excel will enable the delegates to automate certain tasks, which will leave them in a better position to take on more work, or to perform the tasks that require personal attention more effectively. This will lead to a more productive workforce.

Benefits to Individual
By the end of this workshop, participants will be able to:- 
Understand the anatomy of excel formula e.g. Logically Sum/Count, Array, multiple functions from date & time, solving multiple logics, using text functions, advanced lookup function and statistics functions;
Interact with basic VBA functions and record macros to remove the feelings of “Black Magic”; 
Optimize the data by using various excel tools e.g Advance filters, consolidation, subtotal, auto outlining and data functions; 
Use formulas in data validation function to avoid errors caused by erroneous user inputs; 
Use PivotTables to turn raw data into refined information that supports key decisions;
Create a dynamic dashboard with the help of supper Excel tools;
Understand what error says and how to trace and resolve the problem;
Apply conditional and customized formatting; and
Create effective charts by reducing ink and noise and apply conditional formatting on charts.
Further, you will enjoy the networking opportunity with professionals from different industries. 
Certificate endorsed by NUST.