Excel for Scientists

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