Course overview

Duration: 1 day (6.5 hours)

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

It explores techniques to make working with data easier and more efficient and how you can combine these to create interactive worksheets and charts.  It looks at protecting the work you have done and automating tasks with 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
• Name and work with named ranges
• Use formulas to set conditional formatting
• Create linked data validation lists
• Use the XLOOKUP command
• Create interactive charts on linked data
• Set cell protection
• Create macros to automate common tasks

Content

Advanced absolute cell referencing

• Review of absolute vs relative references
• Copying formulas and their behaviour
• Using partial absolute referencing

Range Naming

• Naming cells
• Amending ranges
• Using range names in formulas
• Single cell ranges
• Multi cell ranges
• Finding named ranges

Advanced conditional formatting

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

Advanced data validation

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

Using Lookups

• Using Xlookup to find data on other worksheets

Interactive Charting

• Conditional linking
• Creating graphs on variable data

Cell Protection

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

Starting to use Macros

• Macro concepts
• Recording macros
• Example macros
• Running macros
• Adding buttons to run macros

Public Training Programme

Explore our current selection of public courses, available as convenient and cost-effective virtual sessions.

Engage with expert instructors and a diverse peer group in an interactive, online learning environment.

Ideal for those valuing flexibility and affordability, our public courses are ideal where you only have small numbers requiring training and offer training from the comfort of your home or office.

View our full catalogue of Leadership, Personal Development, Systems, Office Applications and Project Management courses

Contact us

Do you have questions, suggestions, or feedback? We value your input and are here to assist you. Please don't hesitate to get in touch with us via email or phone:

• Email: [email protected]
• Phone: +44 (0)20 8152 6551

Our dedicated team is ready to respond to your enquiries and provide the support you need. Feel free to reach out, and we'll do our best to assist you.

Thank you for visiting our website, and we look forward to hearing from you!