Excel Module 3 – Data Management Techniques

Description

Duration: This one day course is run over 2 half days virtually using Teams.

Overview

Our Excel Module 3 – Data Management Techniques course looks at the next steps in working with your data.

It explores some more formulas and functions and auditing tools to help when things go wrong.  It covers using simple Lookups to pull in data, how to protect your spreadsheets and techniques such as using formulas as part of your conditional formatting and having linked data validation lists to make it easier and more efficient to manage your data.  It also includes an introduction to using Macros.

This course is aimed at existing users of Excel who want to further their skills. Participants should be able to confidently create and amend worksheets, build formulas and use basic data validation and conditional formatting.

Objectives

By the end of the course you will be able to:

• Use partial absolute cell referencing
• Use the formula auditing tools
• Set cell protection
• Name and work with named ranges
• Use formulas to set conditional formatting
• Create linked data validation lists
• Use the VLOOKUP command
• Use simple date and time functions
• Start to work with macros

Advanced absolute cell referencing

• Review of absolute vs relative references
• Using partial absolute referencing

Range Naming

• Naming cells
• Using range names in formulas
• Finding named ranges

Exploring date and time functions

• Using date functions
• Using time functions
• Calculating with dates

Advanced conditional formatting

• Formatting based on other column information
• Using conditional formulas
• Finding cells with conditional formatting

Advanced data validation

• Naming lists
• Linked data validation lists
• Using the INDIRECT function

Using Lookups

• Constructing a simple VLOOKUP function to draw information from another source
• Using HLOOKUP

Formula auditing

• Using the auditing facility
• Tracing how formulas are made up
• Tracing precedents and dependants
• Evaluating formulas
• Error checking
• Showing formulas on a sheet

Cell Protection

• Setting cell protection
• Locking and unlocking cells
• Hiding formulas
• Protecting a worksheet
• Setting the level of protection
• Protecting a workbook

Starting to use Macros

• Macro concepts
• Recording macros
• Example macros
• Adding buttons to run macros
• Customising your ribbons