Price R1,200.00

Course Features
Language: English
3 Days
7 Lessons
Study Level: Advanced
Certificate of Completion

What Will I Learn?

Excel advanced is a specialised training programme focusing on key Excel functionality for powerful business reporting and decision – making!

Directors, Managers, and anyone else who uses Excel extensively for report writing. Delegates will return to the office after the workshops with an advanced understanding of relevant Excel functionality and practical know-how that will result in significant time-savings each month.

Learning Objectives

Workshop 1: Business Reporting using Formulas and Functions

  • Create your own ribbon/s (in MS Excel 2010).
  • By viewing multiple windows you can Copy and Move worksheets between workbooks.
  • Save time on data capturing in multiple worksheets by using Grouping.
  • Consolidating worksheets by using Paste Special.
  • Quickly identifying certain transactions and highlighting duplicates by using Conditional Formatting including Data Bars, Icons, etc.
  • Ensuring certain cells cannot be selected and formulae protected by using worksheet protection.
  • Simplify formulae by using Named Ranges.
  • Using powerful functions to develop your report such as Lookup Functions, Text Functions, Logical Functions, Information Functions, Statistical Functions and Mathematical Functions.

Workshop 2: Managing Data lists and Basic Macros

  • Sorting your list in any specific order by using a Custom List.
  • Using Subtotals to analyze your list to view totals by category.
  • Identifying certain data based on a criteria by using Filtering.
  • Ensuring you have consistent data capturing by using Data Validation.
  • Summarizing your rows and columns by using Group and Outline and create Custom Views.
  • Forecast outcomes by creating Scenarios.
  • Simplify repetitive tasks by recording, viewing, running, and editing Macros

Workshop 3: Data analysis using PivotTables and Pivot Charts

  • Understanding the definitions and layout of a PivotTable.
  • Summarizing data by creating a PivotTable.
  • Improving the look and feel of the PivotTable by Modifying and Formatting an existing PivotTable.
  • Saving time creating a new PivotTable by moving or copying an existing PivotTable.
  • Using Pivot Tools to manage your PivotTable effectively (MS Excel 2010 includes using slicers to filter and for connection to another PivotTable).
  • Create and customize Sparkline’s (only in MS Excel 2010)
Course Overview - Day 1

1. Data Lists lecture

  • Sort Data
    • Single Level Sort
    • Multi-Level Sort
    • Set a Custom Sort Order
  • Subtotals 
    • Sum Function VS Subtotal Function
    • Create Subtotals
    • More than One Set of Subtotals
    • Remove all Subtotals
    • Copying Subtotals
  • SUBTOTAL Function
  • Filter Data 
    • Apply AutoFilter
    • Clear Filter
    • Show all Data
    • Remove AutoFilter
  • AutoFilter Features 
    • Custom AutoFilter
    • Top 10
  • Advanced Filter Functions 
    • Extract Unique Records
  • Data Validation 
    • Text Length
    • List
    • Decimal
    • Copy Data Validation
  • Group and Outline 
    • Group Rows and Columns
    • Ungroup Parts of an Outline
    • Remove Outline
  • Custom View 
    • Create a Custom View
    • Show a Custom View
    • Delete a Custom View
  • Scenarios 
    • Create a Scenario

2. Macros lecture

    • Security Levels
    • Record a Macro
    • Record a Macro using the Ribbon
    • Record a Macro using the Status Bar Shortcut
    • View a List of Macros
    • Run a Macro
    • Delete a Macro (This Workbook)
    • Delete a Macro (Personal Workbook)
    • Relative Macro  
  • Edit a Macro  
  • Assign a Macro to the Quick Access Toolbar  
  • Assign a Macro to a Button / Object 

Course Overview - Day 2

1. Setup Pivot Tables lecture

  • Concept and Layout  
  • PivotTable Field List 
    • Turn the PivotTable Field List On/Off  
    • PivotTable Ribbon 
    • The PivotTable Tools Options Tab 
    • The PivotTable Tools Design Tab  
  • Create a PivotTable Report  
  • PivotTable Options 
    • Layout & Format 
    • Totals & Filters 
    • Display 
    • Printing  
    • Data   
  • PivotTable Layout 

2. Modify Pivot Tables lecture

  • Format a PivotTable 
    • Selecting Elements of a Pivot Table 
    • Enable Selection 
    • To Select all Instances 
    • PivotTable Styles 
    • Style Options  
  • Remove / Add and Move Fields 
    • Remove a Field 
    • Add a Field 
    • Move Fields within the Table 
    • Changes to Source Data 
    • Changes to the Location / Area of the Source Data  
  • Field Settings in Row / Column Labels 
    • Subtotals & Filters 
    • Layout & Print  
  • Field Settings in Value Labels 
    • Summarize Values By & Number Format 
    • Show Values As 
    • Create a Custom Name  
    • Create Calculated Fields 
    • Add a Calculated Field 
    • Adjust the Order of Calculation for Multiple Formulae 
    • View all Formulae that are used in a PivotTable Report 
    • Edit a Calculated Field Formula 
    • Delete a PivotTable Formula  
  • Group and Ungroup Fields 
    • Group Dates 
    • Ungroup 
    • Crating the Custom Group  

3. Pivot Tables lecture

  • Expand and Collapse Detail 
    • Show or Hide Expand & Collapse Buttons in a PivotTable Report 
    • Expand or Collapse Levels of Detai  
    • Show Details for a Value 
    • Disable or Enable Show Value Field Details 
    • Show Value Field Details  
    • Filter a PivotTable 
    • Label & Value Filters  
  • Use Slicers to Filter 
    • Elements of a Slicer 
    • Create a Slicer  
    • Share a Slicer by Connecting to another PivotTable 
    • Make a Slicer Available in another PivotTable 
    • Format a Slicer 
    • Delete a Slicer  
    • Use Sparklines to Show Data Trends 
    • Create a Sparkline  
  • Customise Sparklines 
    • Control which Value Points are shown 
    • Change the Style or Format Sparklines 
    • Handle Empty Cells or Zero Values 
    • Edit the Sparkline Range 
    • Clear Sparklines  
  • Sort Data in a PivotTable 
    • Quick Sort 
    • More Sort Options 
    • Sort Based on the Values  
  • Report Filter Area 
    • Multiple Selection 
    • Show Report Filter Pages  
  • PivotTable as a Flat Sheet  
  • Copy / Remove / Move a PivotTable  

4. Pivot Charts lecture

  • PivotCharts  
  • PivotChart Terminology  
  • PivotCharts and Regular Charts  
  • PivotChart Tools Ribbon 
    • The PivotChart Design / Layout / Format Tab 
    • The PivotChart Analyze Tab  
    • Create a PivotChart 
    • Start with a PivotTable Report 
    • Start with a Data List  
  • Edit a Chart 
    • Add / Remove Fields 
    • Change Chart Settings 
    • Filter Data on the PivotChart 

5. Course Conclusion lecture

  • Worksheet and Workbook Specifications and Limits 
  • Calculation Specifications and limits 
  • Charting Specifications and Limits 
  • PivotTable and PivotChart Report Specifications and Limits 
  • Shared Workbook Specifications and Limits 


Average Rating

No Votes 0 Votes
0 Ratings

Detailed Rating

5 Stars
4 Stars
3 Stars
2 Stars
1 Stars