Excel Data Analysis Training

This course will enable you to inspect, clean, transform, and model data with the goal of discovering useful information, suggesting conclusions, and supporting decision

  • Get to grips with using Excel in you Sales and Marketing departments.
  • Compare price lists to historical or competitive sources on a product by product basis
  • Include % movement calculations comparing to budget and last year in you sales data
  • Establish what increase in volume will justify what level of price decrease;
  • Highlight your average and below average products or sales people with a click of a button; and
  • Easily look up client’s information from different databases to remove duplicate content.

Topics Covered

Day 1

  • How errors happen in models and how to avoid them
  • Essential Excel Knowledge
  • Following formulas
  • IF function
  • Absolute and Relative referencing ($ signs)
  • Short Cuts, Tips and Tricks
  • Importing data into a model
  • Data Cleanup using the Data Autofilter, Advanced Filter and Duplicate remover
  • GOTO Special tool for working with cells
  • Using the text to column tool
  • 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

Day 2
  • Manipulating the data
    • SUMIF, COUNTIF, SUMIFS, COUNTIFS, VLOOKUP
  • High level overview of what can be done with Pivot Tables
  • Reporting results
    • Linking spreadsheets to Word and Powerpoint
    • Conditional Formatting
    • Sparklines
    • High Level Graphing

Training Methodology

  • Every delegate will be required to have a laptop with Microsoft 2013 professional pro (Activated) installed. There are some training aspects not available in Excel 2010 and the lower versions.
  • The training is practical and everyone shall be expected to practically participate.
  • Soft and hard copy training materials shall be provided.