Excel for Academics

Who is this course for?

This two day course is a comprehensive course designed to equip academic professionals with essential Excel skills to enhance their productivity and data management 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’s powerful features.

Objectives

Enhanced Data Management - Learn to efficiently organise and manage large datasets
Improved Analytical Skills - Gain proficiency in using advanced Excel functions and tools for data analysis
Increased Productivity - Discover techniques to automate repetitive tasks and streamline workflows
Better Data Visualisation - Master the use of PivotTables, PivotCharts, and Conditional Formatting to present data clearly
Secure and Accurate Workbooks - Understand how to protect your data and ensure accuracy with auditing and validation tools.

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

Using logical functions

The IF function
The IFS function
AND and OR functions
Combining logical functions

Visualise trends and key information with Conditional Formatting

Use Microsoft’s rules
User defined rules
Manage rules

Documenting and Auditing

Auditing features
Tracing formula errors
Protecting a worksheet
Protecting cell ranges

Working with Lookup and Reference functions

The XLOOKUP function
INDEX & MATCH
The CHOOSE function

Introduction to Array formulas

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

Data Validation

Using data validation
Setting criteria for entry of text, values and dates
Creating a dropdown list

PivotTables and PivotCharts

Creating and rearranging PivotTables
Using the PivotTable toolbar
Creating PivotCharts
Using Slicers to manipulate PivotTables

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

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

Online 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.