Advanced Excel Training Course

advanced excel training courseThis practical, 3 – day Advanced Excel Training course takes delegates from an Intermediate to Advanced Excel level with practical lessons and examples.

Some of the key outcomes of the 3 days will be that the delegate will be able to:

  • Use the new tools that Excel has introduced
  • Understand and make use of Pivot Tables
  • Report their findings graphically via charts and other visualisation techniques
  • Perform advanced sorting including sorting columns and using customised sort orders
  • Identify/ remove duplicates in a spreadsheet
  • Perform fuzzy logic ( find cells that are similar to xyz)
  • Change the colour of cells depending on what is in the cell
  • Add risk robots to their reports
  • Understand what formulas are doing and easily see where they come from
  • Handle text issues i.e. joining cells, splitting them, finding key words in cells
  • Joining/ comparing databases to identify duplicates and merge spreadsheets
  • Understand and use dates correctly
  • Avoid errors in their spreadsheet work.

In order to do this course we recommend a skill level of >3.5 (we offer a free Skills Assessment to make sure you get the correct level of training).

For updated information look at the Advanced Excel Course offered in the Johannesburg/ Pretoria region or the Cape Town region.

This is a very hands-on, non-intimidating, course – delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel’s advanced functions.

Course Outline

Day 1

MORNING: Pivot Tables

  • Pivot table trainingHow to (quickly) convert non-compliant data into Pivot friendly data
  • Pivot Tables – Step by Step
  • Manipulating the Data Portion of a Pivot Table
  • Manipulating the Rows of a Pivot Table
  • Manipulating the columns in a pivot table
  • Combining rows and columns to create meaningful Pivots
  • Report Filter
  • Grouping Data in the rows and columns
    • Manual
    • Dates
    • Numbers
  • Simple Pivot Charting
  • Creating Calculated Fields within a pivot table
  • Creating Calculated Item within a pivot table
  • GETPIVOTDATA function
  • Multiple Consolidation ranges in Pivot Tables
  • Conditional Formatting on Pivot Tables (Excel 2007 and higher)
  • Linking to an external database via Pivot Tables

AFTERNOON: Advanced Excel

  • Advanced Excel Techniques for use in modelling
  • How errors happen in models and how to avoid them
  • Financial Model and spreadsheet design principals
  • Essential Excel Knowledge
  • Importing data into a model
  • Data Cleanup using the Data Autofilter and the Advanced Filter
  • GOTO Special tool for working with cells
  • Using the text to column tool
  • Common pitfalls / problems at this stage

DAY 2

  • Working with imported text data
    • CLEAN, CONCATENATE, DOLLAR, EXACT, FIND/SEARCH, LEFT/RIGHT/MID, LEN, LOWER/UPPER/ PROPER, REPLACE, T, TEXT, TRIM, VALUE
  • Handling Dates in your models
    • EOMONTH, DATE, DATEVALUE, DAY, EDATE, MONTH, NETWORKING DAYS, WEEKDAY, WORKDAY, YEAR
  • Manipulating the data
    • (SUMIF, COUNTIF, SUMIFS, COUNTIFS, VLOOKUP)
  • Ranking Results
  • Error checks and spreadsheet integrity

DAY 3

MORNING: Completion of Advanced Excel

  • Analysing Results
    • Data Filter
    • Speech to Text
    • Database Functions
  • Reporting results
    • Conditional Formatting
    • Cell formatting
    • Data Protection

AFTERNOON – Data visualisation

  • Excel graphingGetting the Data right for Graphing
  • Understanding Excel graphs and incorporating them into your model
  • Adding another series to a graph
  • Creating a secondary axis in a chart
  • Changing default colouring to match corporate colours
  • Create a chart template for consistent use
  • Different Chart Types
    • Column Charts
    • Bar Charts
    • Pie Charts
    • Area Charts
    • Radar
    • Waterfall graphs
    • Combination Charts
  • Trendlines in models
  • Goalseeking straight off the graph
  • Linking your Word documents directly to the Excel graph
  • Conditional Formatting Graphs
  • Using a custom picture for your graphs
  • Creating flexible graphs

Feedback & Who Should Attend

  • I’m glad i invested my money and time on this course, it was worth it.I also like the fact that i will be assisted even after completing the course. I walked out a different lady, just confirming what De Wet said.- Kefiloe M.
  • Adrian was really good. He explained everything really professionally- Bookkeeper.
  •  It dealt with my daily challenges at work- IS Management Accountant.
  • There was nothing I did not like about the workshop. Adrian was fantastic- Accountant.
  • This course really add significant value to my life as a data analyst- Lonmin
  • Thanks for really equipping me with the necessary skills to make my job quite easy. I now know how to use VLOOKUP to join my databases together.- Lonmin
  • I enjoyed everything about the course. Adrian explained everything we needed to know, and he was always willing to assist. It was also fun to do all the exercises. I got more than my expectations. I am excited to use Excel now!- Lonmin
  • … first the assessment is a good tool to determine that the trainee is allocated to the correct training, secondly, although the training is intense the content is brilliant and very well presented. Being able to express real live occurrences during the training makes for understanding of applications. All Krones personnel that have attended this training has had their skillset enhanced. Being proficient in excel has allowed the flow of work from department to department to become more and more efficient and not to mention, look more professional. Audit Excel is my only choice for Excel Training.- Debbie L- Krones

Typical Attendees

  • All persons using Excel spreadsheets. Professions that have attended include accountants, actuaries, auditors, business analysts, chartered accountants, civil engineers, construction, consulting, corporate finance, engineers, farming, finance, hr function, landlords, lawyers, quantity surveyors, research analysts, retail, scientists and engineers
  • All persons involved in the construction and use of spreadsheet models, including budgets, projections, evaluations, cash flows, projects, etc.Other areas that would benefit include budgeting, business, data analysis, debt, logistics, non profit, project management, property management, real estate, statistics, and trading
  • All delegates should have a score of above 3.5 on our Excel Skills Assessment
  • Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise).

Pre Requisite (s)

Excel knowledge required:

  1. Basic Excel skills
  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  1. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  2. Formatting