Excel for Academics

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