Tel: 0845 555 1030

Data Analysis with Excel - Introductory/Intermediate/Advanced

Data Analysis with Excel - Introduction (1 Day)

Course level - Introductory / Developmental

Users of Microsoft Excel who are new to data analysis, and those needing to obtain analytical skills for working around their day-to-day spreadsheets. Also, support staff, spreadsheet authors and finance workers looking to improve Excel functionality and processing speed.

Overview

This one day course offers quick tips to improve functionality, spreadsheet formatting, data searching and calculations amongst large data sets within Excel. This involves an introduction to data analysis, reporting tools, data manipulation and searching, plus an insight into simple automation of tasks using macros.

Learning outcomes include obtaining a greater understanding of:

• Time savers
• Keyboard hotkeys and shortcuts
• Data filtering and searching
• Functions
  • Logical / Mathematical
  • Conditional (IF)
  • Textual (for cleaning data)
  • Nesting
• Using VLOOKUP within large data sets
• Applying meaningful Conditional Formatting
• Analytical options
  • Working with SUBTOTAL
  • Goal seeking
• Organise business data via Pivot tables
  • Organising data for analysis
  • Quick breakdown and analysis of complex data
• Automation using macros
  • Creating macros for repetitive tasks
  • Using the macro recorder

Data Analysis with Excel – Intermediate (1 Day)

Course level - Intermediate

This course will benefit those using Excel on a regular basis and wanting to learn more about performing various forms of analysis and exploring more complex functions and calculations. Also support staff, finance professionals and analysts wanting to increase their functional knowledge of Excel.

Overview

This one day course offers quick tips and tricks to better utilising the power of Excel. The course involves analysing and extracting data quickly from data sets, array formulae, nesting functions, data manipulation, as well as an intro to macro automation.

This course is very hands-on and explores some of the more complex and advanced areas and functions within Excel, which include complex nesting techniques, performing unconventional and flexible lookups, summarising and analysing trends, and controlling charts via Pivot Tables.

Learning outcomes include obtaining a greater understanding of:

• Time savers / fast keys / shortcuts
• Key functions
  • IF / MAX / IFERROR / ISERROR
  • INDEX / MATCH / OFFSET / LOOKUP / VLOOKUP
  • LEFT / RIGHT / MID / LEN
  • Introduction to ARRAY functions and complex nesting techniques
• Lookup in-depth
  • Flexible lookup techniques for unconventional datasets
  • Problems and limitations of a VLOOKUP
• Data manipulation
  • Advanced use of text-string functions
  • Text extraction with nesting
• Additional forms of analysis
  • Control a chart via a Pivot Table
  • Summarise data and automate chart elements
  • Frequency distribution table
• Bespoke and extensive conditional formatting
  • Create immediate visuals using Data Bars
  • Writing formulas to control range formatting
• Introduction to scenario building, solver analysis and goal seek
• Bonus macro and VBA exercises
  • Applying meaningful macro functionality
  • Viewing and simple editing of macro VBA code

Data Analysis with Excel – Advanced (1 Day)

Course level - Advanced

Course Outline and Objective

Many of us struggle with the sheer amount of data that is available in this information age. In many cases, reports are built to present the data, but find that the end result still has a limiting structure and usability. This one day course provides attendees with hands-on techniques with a focus on keeping things dynamic and expandable. Covering structured references, dynamic listing techniques, scenario modelling, and through the exercises we will get to use some of the more unexplored functions and areas in Excel.

Learning outcomes include obtaining a greater understanding of:

• Advanced listing techniques
  • Tricks in creating dependent lists
  • Dynamic selection tips
  • Dynamic extraction of unique values
• Facilitating calculations
  • Dynamic and expandable named ranges
  • Multi-nested functions / expandable referencing
  • Structured table references / Table nomenclature
  • Aggregate calculation using the ‘wildcard’ technique
• Key Functions
  • DSUM / SUMPRODUCT / SUMIFS (& building conditions)
  • Advanced uses of: OFFSET / CHOOSE / INDEX / MATCH
  • ROWS
  • INDIRECT
• Further advanced techniques for summarising & presenting data
  • Mini Pivot Table reports
  • Fast calculations with Data Tables
  • Data modelling with Scenario Manager
  • Histogram and Pareto Charts
• Excel dashboards (extracts)
  • Creative charting techniques
  • Building a visual analysis
  • Tips and tricks in custom formatting
• Bonus VBA exercises
  • Apply simple automation to speed up processes

For further information CLICK to complete our quick contact form or call our team of advisors on 0845 555 1030

Workshop Dates In-Company Training / Tailored Solutions Why Choose Bristol Business College?