In this course, our experts will walk you through important hands-on experiences on how to segment your data for further reporting using VLOOKUP, and how to present the most relevant data with dynamic pivot reports and charts that inspire management action.

In this module you will learn:

  1. Data cleansing techniques
  2. Prepping your data for analysis
  3. Data mining and analysis techniques
  4. Visual presentation of data (charts and pre-built conditional formatting)

A good foundation in Excel is essential for this course. Participants should not just be regular users of Excel but must be able to write basic formulae taught in the foundational level principles in the previous module.

  • Save loads of time dealing with blank cells in Excel –filling blanks
  • Identify and eliminate repeated records in Excel –learn more than 3 ways
  • Compare two lists of data -3 methodologies
  • Text extractions – combining and splitting columnar data using LEFT, RIGHT, MID, LEN, FIND, etc.
  • Nesting the IF function to segment data; combining the IF function with information functions such as ISBLANK, ISNUMBER, ISNONTEXT
  • Looking up data from a list using the VLOOKUP (and/or HLOOKUP) function; Create auxiliary columns needed for reporting and mapping/integrating different sources of data
  • Creating a drop down in Excel
  • Enhancing readability with named ranges; expandable named ranges with Excel tables
  • Using the SUMIF and COUNTIF functions for analysis
  • Intercepting and handling errors with IFERROR
  • The ins and outs of Pivot Tables (Slicers for interactive reports, grouping dates into quarters, show values as percentages, computing YoY% inside pivot reports, styling your pivot reports, working with pivot charts)
  • Various date scenarios
    • Counting working days with the NETWORKDAYS function
    • Determine a completion date or an invoice due date with the WORKDAY function
    • Calculating future and past dates using EOMONTH and EDATE
    • Compute the tenure of various employees using the YEARFRAC function
  • Dual axis charts with a secondary axis
  • Applications of the REPT function for in-cell charts
  • Special charts –mimicking a thermometer for Actual versus Budget analysis
  • Conditional formatting charts to display variances in different colours without manually colouring the bars
  • Applications of pre-built Conditional Formatting features –data bars for in-cell charts, evaluate performance (exceeded target, average, below target) given some thresholds using Icon Sets

10may(may 10)8:30 am11(may 11)4:00 pmIntermediate Excel TrainingFormula intensive class to ground you to achieve more for less time8:30 am - 4:00 pm (11) The Heron Portico Hotel, Nairobi

12jul(jul 12)8:30 am13(jul 13)4:00 pmIntermediate Excel TrainingFormula intensive class to ground you to achieve more for less time8:30 am - 4:00 pm (13) The Heron Portico Hotel, Nairobi

Reserve your seat now

What you get:

  • 12 hours instructor-led, practical sessions

  • Comprehensive study materials

  • After-course instructor support

  • Certificate of participation

Do you have 8 or more staff who could benefit from this program?

Run this course in-house