Mastering Microsoft Excel

These days, any managerial or analyst role requires that you work with data. Microsoft Excel remains one of the most powerful and easy to use tools when it comes to crunching data. Learning Excel in a proper, structured way can not only help you save time, but it will also help you become better at your job and can give you time to focus on things and people that matter.

As one of the most used computer software programs for businesses today, Mastering Microsoft Excel is an important skill that employees should have. It is no wonder that companies and businesses demand that their employees learn Excel so that they can stay competitive. Excel helps companies maximize the value of their data, helping to control costs more effectively and obtain business information.

Excel is commonly used across business to display financial information and other data relevant to the running of the business. This could be information relevant to the Engineering, Sales, Marketing, HR, Quality or Finance department. With so many business functions now reliant on IT and the internet, Excel continues to be seen as a vital tool for administration and the effective running of a business.

Looking at it from the employer’s point of view, especially for those in the field of information technology system, Excel is an essential end-user computing tool that can help perform a wide range of tasks. From making some of the mundane tasks more bearable through automation, to everyday functional tasks. Microsoft Excel is also used as a decision-making support for employers.

It isn’t surprising how Excel has been dominating the spreadsheet product industry with a share of more than 90%.

It is important to stay on top of career, Future in Hands wants to make sure you have the edge when it comes to Microsoft Excel training. Mastering Microsoft Excel will arm you with the knowledge of how to use Excel more effectively and efficiently and ultimately help you make smarter decisions.

Coverage

Initial Warmups

  • Microsoft Excel Version History
  • Features Comparison
  • Advantages of Microsoft Excel
  • Overview of Ribbon, Groups & Tabs
  • Customization of Tabs
  • Overview of Quick Access Toolbars
  • Customization of Quick Access Toolbar
  • Overview of Back Stage View

Overview of Basic Excel skills

  • Overview of Common Keyboard Shortcuts
  • Worksheet Operations
  • Cells & Range Operation
  • Tricks of Format Cells
  • Working with Custom Number Formatting
  • Calculations in Number Formatting
  • Freeze Panes with Different Scenarios
  • Working with Excel Comments
  • Excel Security – Protecting a Selected Range
  • Excel Security – Protecting an Entire Sheet
  • Excel Security – Protecting different Range with different Password in the Same Sheet
  • Excel Security – Protecting Workbook Structure
  • Excel Security – Full Protection of Workbook
  • Excel Security – Partial Protection of Workbook
  • Working on Excel Data Backup

Time Saving Utilities

  • Techniques of Paste Special
  • Format Painter
  • Splitting Data using Text to Columns with fixed and delimited parameters
  • Tricks of Data Sorting in different Scenarios
  • Summarizing data using Group and Outline
  • Summarizing Data using Subtotal

Concept of Cell Referencing

  • Introduction to Cell Referencing
  • Importance of Cell Referencing
  • Relative Referencing
  • Absolute Referencing
  • Mixed Referencing

Working with Functions & Formulas

  • Difference between Functions & Formulas with scenarios
  • Concept of Nested Formulas
  • Formula Auditing
  • Various Calculation Modes and How to use them
  • Circular References – What are they?
  • Concept of Wild Cards
  • Concept of important Functions: AND, OR, NOT, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, MAX, MIN, LARGE, SMALL, UPPER, LOWER, PROPER, RIGHT, LEFT, MID, SEARCH, TEXT, VALUE, POWER, SUM, SUMIF, SUMIFS, AVERAGE, AVERAGEIF, AVERAGEIFS, EXACT, CONCATENATE, LEN, SUBSTITUTE, NETWORKDAYS, NETWORKDAYS.INTL, DATE, TIME, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WORKDAY, WORKDAY.INTL, EOMONTH, DATEDIF, VALUE, INT, ISERROR, ISTEXT, ISNUMBER, ISBLANK, ISODD, ISEVEN, MATCH, INDEX, IF, VLOOKUP – 1D, VLOOKUP – 2D, Reverse LOOKUP, HLOOKUP, CHOOSE, RANDBETWEEN, ROW, ROWS, COLUMN, COLUMNS, MOD, IFERROR, SUBTOTAL, SUMPRODUCT, OFFSET, INDIRECT, REPT
  • Numerous Combinations of the above Functions
  • Data Consolidation using 3D Formulas

Array Formulas for quick conditional calculations

  • Introduction to Arrays
  • Concept of Logical Operators in Array Formulas
  • The internal logic behind Arrays
  • The power of Array Functions
  • Techniques of creating complex Array Functions
  • Array Formulas using basic Excel Functions

Name Manager

  • Introduction to Name Management in Excel
  • Overview of Name Box
  • Defining Static & Dynamic List using names
  • Use of Names in advance Reports
  • Use of Name Manager for Extracting Data

Dynamic views using Conditional Formatting

  • Conditional Formatting using Formulas & cell values
  • Highlighting Duplicates in various ways
  • Highlighting top records, bottom records
  • Highlight above average, Max, Min Errors, etc.
  • Highlight single color with multiple conditions
  • Calculation based Conditional Formatting
  • Complex formula based Conditional Formatting
  • Creating Dashboard using Bars & Icons

Restriction using Data Validation

  • Introduction to Data Validation
  • Different Data validation in Excel
  • Complex calculation based data validation
  • Using Dynamic list in validation
  • Using Basic Dependent List
  • Using Multilevel Dependent List
  • Hacks of Data Validation
  • Highlighting Invalid Data

Prevent wrong decisions using Excel Tables

  • Introduction of Excel Tables
  • Power of Excel Tables
  • Time Saving features of Excel Tables
  • Calculation Logic of Excel Tables
  • Format As Table
  • Understanding Table Formatting Options
  • Filters, Can Get Multiple Filters In The Same Sheet
  • Total Row, Ability To Select Type Of Summary
  • Structured References – What Are They?
  • Advantages Of Tables
  • Convert Table To Named Range
  • Compatibility Of Tables With Excel 2003 And Earlier
  • Limitation of Excel Tables

Charts in Excel

  • Elements of Charts
  • Major charts used in Industry
  • Column Charts
  • Bar Charts
  • Line Charts
  • Pie Charts
  • Donought Charts
  • Scatter Chart
  • Combination Chart
  • Introduction to Tiny Charts – Sparklines
  • Column Sparklines
  • Line Sparklines
  • Win-Loss Sparklines

The Power of Pivot Tables

  • Pivot Table creating methods
  • Rearranging a Pivot Table
  • Filtering Pivot Table data
  • Performing custom calculation
  • Creating dynamic dashboards using Pivot Charts and Slicer
  • Creating dynamic views using Timeline
  • Consolidating data from multiple files in less than a minute
  • Consolidating data from multiple sheets in less than a minute
  • Splitting Data to Multiple Reports in less than a minute
  • Creating Pivot Tables through Database Connections

Analyzing Data using Advance Filter

  • Introduction to Auto Filter
  • Challenges in Auto Filter
  • Power of Advance Filter
  • Complex calculations based filter
  • Filter using wild Cards
  • Extracting Unique list with Advance Filter

Form Controls in Excel

  • Introduction to Form Controls
  • Usage of Form Controls
  • Creating Dynamic Reports using Form Controls
  • Creating Dynamic Dashboards using Form Controls

Analyzing Data with What-If Analysis

  • Introduction to What-If Analysis
  • Determine unknown with Goal Seek
  • Analyze data with Data Table
  • Enable Decision making using Solver
  • View results with Scenario Manager
  • Case Studies

Discussion on Quality Control Tools

  • Introduction to Quality Control (QC) tools
  • Significance of QC tools
  • Ishikawa or Fishbone chart
  • Check Sheet
  • Control Charts
  • Histogram
  • Pareto Analysis
  • Scatter Diagram
  • Stratification
  • Case Studies