Excel for Scientists

Who is this course for?

This two day course is a comprehensive course designed to equip scientists with essential Excel skills to enhance their productivity and data analysis capabilities.

The course covers a wide range of topics, from basic best practices in workbook planning to advanced data analysis techniques. Participants will learn how to efficiently organise and analyse data, create dynamic reports, and automate tasks using Excel VBA code.

Benefits

Excel best practice

Planning a Workbook
Identifying the audience and co-users
Recognise inputs, workings and outputs
Creating consistency and durability
Structure and Organising source data

Essential Excel knowledge to improve efficiency

Absolute and Relative cell references
The Table tool
Power query introduction

Visualise trends and key information with Conditional Formatting

Use Microsoft’s rules
User defined rules
Manage rules

Introduction to Array formulas

Using embedded Excel Array formulas
TREND, GROWTH, FREQUENCY
UNIQUE, SPILL, SORT, SORTBY, FILTER, GET
Creating bespoke Array formulas

Using analytical options

Using Goal Seek
Creating, saving and viewing scenarios
Creating single input data tables
Creating double input data tables
Projecting with data tables

Solver

Creating models
Projecting scenarios with Solver

Macros

Creating macros
Using the macro recorder
Viewing and editing macro VBA code

Analysing data with the Analysis ToolPak

Exploring the data analysis tools in Analysis Toolpak

VBA Understanding Visual Basic

Working with the Visual Basic Editor
What is an Object?
Examining the Excel object hierarchy
Using the object browser
How to explore methods and properties
Getting help in VBA

VBA Making decisions in code

Using logical comparisons
The IF...ENDIF structure
VBA Repeating code with loops
The DO... LOOP structure
The FOR... NEXT structure
The FOR EACH... NEXT structure
How to debug problems with loops

Storing information with variables

How and why you should declare variables
Determining which data type to use
Public v Private scope
Using variables to trap errors
Using the Locals window to observe variables

Creating functions

Writing your own User Defined Functions (UDFs)
Working with multiple arguments
Using your function in Excel                                          

Face-to-face training

Training is held in our modern, comfortable, air-conditioned suites.

Modern-spec IT, fully networked with internet access

Lunch, breaks and timing

A hot lunch is provided at local restaurants near our venues:

• Bloomsbury
• Limehouse

Courses start at 9:30am.

Please aim to be with us for 9:15am.

Browse the sample menus and view joining information (how to get to our venues).

Refreshments

Available throughout the day:

• Hot beverages
• Clean, filtered water
• Biscuits

Virtual training

Regular breaks throughout the day.

Learning tools

In-course handbook

Contains unit objectives, exercises and space to write notes

Reference material

Available online. 100+ pages with step-by-step instructions

24 months access to Microsoft trainers

Your questions answered on our support forum.