Intermediate to Advanced Microsoft Excel Skill

customer loyalty
Front Desk and PA Management Skills.
April 28, 2017
INTERPERSONAL SKILLS, COMMUNICATION AND ASSERTIVENESS
April 28, 2017
Show all

Intermediate to Advanced Microsoft Excel Skill

excel-training-academy

 

 

Course Overview

Microsoft Excel is the world’s most popular spreadsheet program; learning how to use the software with REMOIK NIG LTD Training shows that you’ve taken one of the best Excel courses available.

Our Microsoft Excel course is very flexible – you will have the freedom to take the Excel lessons at with support on hand should you need it. And a workbook will be yours to take away as a handy reference guide. This Excel course comprises several lessons, which provide a detailed but manageable study programme, so you can be confident about your new skills in the workplace.

Microsoft Excel is one of the most commonly used spreadsheet programmes, whether used in businesses for customer data and accounting purposes or at home for household budgets. Having proven skills in this software will demonstrate to employers that you have had practical, hands-on experience and give you full confidence in your ability to navigate your way around the software with ease.  Various interactive role plays are used to utilize skills in the participant-driven learning environment. Instructor feedback is provided to all participants so as to allow changes in the training process, habits and other skills to affect effective career. Lectures, comprehensive materials and roundtable discussions all function to improve participant success.
What to expect
This course is a highly practical, vocational, “how-to” programme based around real-world tools, checklists and processes that will be immediately useful to you back in your day job. You will not have to sit through long lectures or presentations – this course uses active learning to help you internalize the topic and apply it in the real world.

  • Training format 
    This course comprises modules to be covered in 2-days. Each covering a set of key concepts and skills. Within each module, best practice and key concepts are covered off in a concise briefing, followed by stimulating learning activities like workshops, discussions, case studies and role-play exercises.

Course Content

Day 1

11) Data Manipulation in Excel

  1. How Excel handles different data types
  2. Data consistency, starting with the end in view
  3. Building Datasheets that can easily scale
  4. Sorting
  5. Multi-level sorting
  6. Sorting across rows (left to right sorting, not the usual up to down sorting)

iii. Sorting and Conditional Formatting to identify trends

  1. Filtering
  2. Data cleaning
  3. Removing duplicates
  4. Text-to-column

iii. Grouping

  1. Data Validation
  2. Conditional Formatting
  3. Data formatting
  4. Using Tables (and when to convert to tables)
  5. Formatting for printing

iii. Formatting for email

  1. Data Review and formatting for 3rd party use
  2. Named Ranges

 

2) Charts

  1. Chart types
  2. Line chart and when to use it
  3. Column chart and when to use it

iii. Bar chart and when to use it

  1. Pie chart and when to use it
  2. Combining charts; when and how.
  3. Dynamic Charts, using filter.
  4. Best practices when making charts
  5. Sparklines
  6. Power Map and Power View (Excel 2013)

 

3) Pivot Table, Pivot Chart and PowerPivot

  1. Pivot Table
  2. Default Pivot Table
  3. Tabular Pivot Table

iii. Pivot Table Filtering

  1. Making a very dynamic regular table from Pivot Table
  2. Calculations and Formula use with Pivot Table
  3. Advanced Pivot Table tricks
  4. Pivot Chart
  5. Pivot Chart and its limitations
  6. Dynamic Pivot Charts
  7. PowerPivot (for Excel 2010 and 2013)

 

DAY 2

 

4) Business Data Analysis

  1. Linking sheets
  2. Duplicating sheets (better than copy and paste)
  3. Inserting sheets, labeling and coloring the professional way
  4. Freezing Panes and splitting windows
  5. Conditional formatting
  6. To identify patterns
  7. Using formulae

iii. To make extremely intelligent reports

  1. Lookup functions
  2. Vlookup
  3. Hlookup

iii. Looking up the last data or pattern in a particular row or column

  1. Overcoming the limitations of Vlookup and Hlookup using index and match functions
  2. Power Functions
  3. IF, IFERROR, AND, OR, ISBLANK, and others in the same family
  4. TEXT manipulative functions to make a completely automated Dashboard

iii. COUNTIFS, SUMIFS, AVERAGEIFS and others, to make dynamic summary tables

  1. MATCH and INDEX to do the impossible
  2. Other Functions
  3. Math Functions
  4. Text Functions

iii. Logical

  1. Others
  2. Formula Auditing
  3. Goal Seek, Scenario Manager and Solver
  4. Excel Web Query
  5. Most useful Excel keyboard shortcuts

 

5) Executive Dashboards and Reporting

  1. Best Practices
  2. Executive Dashboards
  3. Executive Dasboards
  4. Dynamic Reports

iii. Determining the KPIs and tracking them

  1. Strategic Insights & Analysis
  2. Data Visualization
  3. Having the audience/recipient in mind
  4. E-mails and Excel reports

 

DAY 3

 

6) Excel to PowerPoint and Word

  1. Linking PowerPoint/Word Charts to Excel
  2. Embedding Excel sheets in PowerPoint/Word
  3. Making a Powerful PowerPoint Presentation

 

7) Excel VBA

  1. Recording Excel macros

Outlines, Subtotals & Consolidation

  • Automatic Outlining
  • Manual Outlining
  • Subtotals
  • Data Consolidation
  • Advanced Calculations
  • Absolute Cell References
  • Linking: Cells, Worksheets, Workbooks
  • Updating Links
  • Precedents and Dependents
  • Create & Define Names

Protection and Sharing

  • Worksheet, workbook and file protection
  • Sharing workbooks and Tracking
  • Dealing with Irate Customers – The Procedure
  • The importance of Service Recovery and Customer Retention
  • External Data
  • The Query Toolbar
  • Creating a Query from Excel
  • Updating and Modifying Queries

 

Templates

  • Creating a Workbook Template
  • Templates Folder
  • XLStart Folder

 

The Scenario Manager

  • Creating a Scenario
  • Editing a Scenario
  • Summaries
  • Merging Scenarios

Macros

  • Recording Basic Macros
  • Running Macros
  • Assigning Macros

Course format 

Practical: This course is heavy on reality and light on theory. Your trainer will introduce the concepts clearly, and then focus on real-world skills that connect the big picture to your job. Courses are updated regularly and trainers are active in the industry so your knowledge will be fresh and relevant.
Active: Training consists of concise briefings on best practice, backed up by interactive learning activities like workshops, role-plays, case study analysis, coaching, brainstorms and structured group discussions. You will not sit passively through long lectures.

Stimulating: This course is interesting, intellectually stimulating and delivered in a relaxed and professional style.

Inspiring: All REMOIK Pinnacle trainers are hands-on communication professionals with years of experience. We don’t employ academic teachers – we insist on good-humoured enthusiasts who will inspire your creativity