Advanced Excel Bur Dubai, Dubai

Course details

Introduction 

• History and Introduction of spreadsheets 
• Prevent data loss in the Event of System 
  

Crash

Basic Formula & Function

• Advanced Excel Functions 
• Applying Relative, Absolute & Mixed Referencing 
• Defining Names & Using them in Functions 
  

Formatting Techniques

• Advanced Formatting Tools 
• Customized Number Formatting 
• Working with Pictures, Objects and Smart Art 

Dates & Times

• The Science of Dates in Excel & Date Functions 
• Working of Time in Excel and Useful Time  Functions 
• Prepare Daily and Monthly Schedules 
  

Efficient Handling of Data

• Quick Data Selection Techniques 
• Quick Data Entry Techniques 
• Use of Special Paste 
• Accelerating Copy Paste 
• Managing a Large Quantity of Data 
• Excel Advanced New Feature: 
• Flash Fill & Quick Analysis 
• Summarizing Data with Sorting and filtering 
  
• Advanced Use of Find & Replace 
  

Analyzing Data with Conditional Formatting

• Highlighting Values based on Criteria 
• Applying & Managing Formatting Rules 
• Using the Data Bars, Icon Sets & Color Scales 

Charts & Graphs

• Creating a Chart in no Time! 
• Most Commonly Used Charts 
• DO's & Don'ts of Excel Chart Formatting 
• Charts Formatting 
• Advanced Charting Techniques 

Working with Tables

• Exploring the Power of Excel Tables 
• Creating Dynamic Ranges with Tables 

IF Function with AND & OR

• Applying the Powerful IF Function 
• Simple IF & Nested IF Function AND Function Combined with IF Function  OR Function Combined with IF Function 

Lookup Functions

• VLOOKUP and HLOOKUP & its Limitations 
  
• Alternate to VLOOKUP: Combining Index- match 
  
• SUMIF, AVERAGEIF, COUNTIF & INDIRECT Functions  
  
• SUMIFS, AVERAGEIFS & COUNTIFS & OFFSET Functions 
  

Pivot Tables

• A Powerful Reporting Tool 
• Analyzing Data with Pivot Tables 
• Walking Through Report Builder Layout 
• Managing & Presenting Pivot Tables Data 
• Working with Pivot Charts 
•    Excel Advanced New Feature: Slicers & Timeline 

Data Validation

• Creating Drop down List 
• Regulating and Managing the Entry of Data 
• Custom Input Message and Error Alert 

What-If Analysis

• Using the Brilliant Goal-Seek Feature 
• Use of Data Tables to Display Probable Results 
• Using Scenario Manager to Summarize Results 
  

• Identifying the Source Cell 
• Tracking changes made to Data 
  

Managing Worksheets

• Consolidating Data 
• Lining Worksheets & Workbooks 
• Calculating Across Worksheets with "3-D Formula" 
• Creating a Hyperlink 
  

Printing & Security

• Walking Through Advanced Printing 
  
• Techniques 
• Restricting Editing in Data 
  

• What is a Macro? 
• Recording & Running/Executing a Macro 
• Assigning Macros to Objects 
• Adding Interactive Form Controls to Worksheet