Excel - Data Analysis Techniques

This Excel training course will teach you how to analyse and summarise worksheet and database information, consolidate data, forecast and plan budgets, assess risks and draw out information from datasets to use in reports or calculations.

Target Audience

This training course is valuable for anyone who uses Excel regularly to make decisions based on data. This course is aimed for higher level Intermediate to Advanced users of excel who already have a fairly good knowledge of Functions and Formulas. If you want to learn more of the basics about the Functions and Formulas in Excel then do look at our Excel Functions and Formula training course, or for even more advanced knowledge into the world of Macros and Visual Basic then look at our Excel Visual Basic (VBA) training course


This course is about harnessing the power of Microsoft Excel to analyse business problems and identify solutions. You gain the knowledge and hands-on skills required to exploit Excel's intermediate and advanced features, such as PivotTables and PivotCharts.


A reasonable understanding of Excel formulas.

Review of Basic Function

  • AutoFill
  • AutoComplete
  • Basic Cell Referencing
  • Fixed and Mixed Cell References
  • Shortcuts for Fixed Cell Referencing
  • Using Natural Language and Named Cells
  • Using the Name Manager
  • Referencing Named Cells

Tables and Named Ranges

  • Using Data in Tabular format
  • Using External Constants
  • Creating a Data Table
  • Named Table Function
  • Formatting Named Tables
  • Assets of Named Tables
  • Adding Header, Footer and Total rows
  • Using Named Table Data in calculations
  • Named Row and Column conventions

Data Functions and nested Formulas

  • Using Today and Now
  • Calculating working days
  • Interpreting data variations with the IF function
  • Streamlining calculations with referencing
  • Developing nested functions for multiple conditions
  • Capturing information with lookup functions
  • Applying techniques to implement and troubleshoot nested calculations

Conditional Formatting

  • Basic Conditional formatting techniques
  • Good Structural techniques for building workbooks
  • Using Traffic Light analysis
  • Filtering and Sorting based on conditional formats

Analysing Data Sets

  • Using the Autofilter
  • Using the Subtotal Function
  • Extracting unique lists of records from an Excel data set with the Advanced Filter
  • Analysing data sets with filters and aggregation
  • Using Subtotal Feature


  • What are Pivot Tables used for?
  • How to create a basic PivotTabel
  • Presenting PivotTable reports effectively with PivotCharts
  • Examining data patterns with Sparklines

Course Testimonials

"I attended the Data Analysis course, and with a rough knowledge of excel tables and functions. The course started well with basic functions, and this built into a well-structured training programme. The trainer (Andrew Reeves) was well versed in excel, and made the whole training fun. He gave time to allow people who may have been slow in picking things up, and explained it to them well. Overall I would certainly attend more of Qdos' training courses." - Viraj B, January 2015
"Comprehensive one day course into the key features of data analysis in Excel. Clear, structured presentation from Andrew Reeves!" - Zahra S, January 2015
"Very well structured course and very well delivered !" - Assistant Management Accountant, Syner-Med PP Limited
"The excel courses provided by Qdos have really enhanced my knowledge of the software package for use in the workplace, especially with regards to large datasets". January 2015
"A very comfortable and pleasurable experience. I came to learn and I feel that those objectives and goals have been reached". - Renewals Manager, Plan Insurance Group, January 2016
"The course is flexible to be able to tailor to my individual needs and there is plenty of opportunity to ask questions". - Senior Investigator, Apple, January 2016
"Came into the course thinking I was at intermediate to expert level, very quickly humbled. Now know the correct way of using functions, shortcuts and new excel functions". - Project Support Officer, Corporate Real Estate, AIG April 2016
"Great stuff! Double thumbs up! :0) ". - Marketing Executive, Plan Insurance. May 2016
"Really good course to do, I feel like it has really expanded my knowledge of excel, and cannot wait to get back to work to put it in practice". - Admin Team Leader, Plan Insurance. May 2016
"It was great. I learnt a lot that will help me to do my job better". - Auditor, Plan Insurance. June 2016
"Very good course with lovely staff and effective training techniques". - Junior Marketing Executive, Plan Insurance. June 2016
"Very well presented and a nice environment to work in paced just right to take in the information". - Accounts Manager, Plan Insurance. September 2016
"The course covered everything required to successfully analyse the kind of data that I could be asked to look at". - Team Secretary SJAtrees. September 2017