Excel Intermediate Formulas and Functions – 1 Day (2007/2010/2013/2016)

 

Description

You need to:

• Analyse data with intermediate level formulas
• Name cell ranges to translate formulas easily
• Analyse data with intermediate level functions
• Error Check and Auditing tools
• Link formulas between worksheets and workbooks
• Change cell formats based on certain conditions using Conditional Formatting
• Customise text, date and number formats

Aim

This course aims to provide you with the skills and knowledge necessary to manage data and solve problems using functions and formulas.

 

Pre-requisites

To gain the most from this course, you need to have successfully completed the Excel 2007/2010/2013/2016 Essentials course or have equivalent skills and knowledge.

 

Learning Outcomes

At the completion of this course you should have the skills and knowledge to:

 

· Understand Absolute cell referencing

· Analyse data using formulas and functions

· Simplify formulas by naming cell ranges and using the names in formulas

· Work with text date and time functions

· Navigate through errors

· Link cells across worksheets and workbooks using formulas

· Enhance the look of worksheets by using customised number formats and conditional formatting

 

Topics Covered

Key topics covered on this course include:

Work with Formulas

· BODMAS Order of Operations

· Understand the differences and uses of relative, absolute and mixed references

 

Named Ranges in Formulas

· Why naming cell ranges is useful

· Use names in formulas and functions

 

Functions

· Understand the SUMIF and COUNTIF functions

· Understand the COUNTIFS() and SUMIFS() functions

· Learn the IF functions with values and text

 

Date and Time Functions

· Understand dates and times in Excel

· NETWORKDAYS function

· Display a summed time value

 

Text Functions

· LEFT, RIGHT, MID functions

· TRIM, CLEAN functions

· TRIM and CLEAN nested functions

· SUBSTITUTE function

· CHAR function

 

Error Check and Audit

· IFERROR function

· Error checking and formula auditing

 

Link Worksheets and Workbooks

· Create link formulas to multiple sheets

· Create links between workbooks

· Manage links

 

Improve Worksheet Readability

· Create customised text, date and number formats

· Create rules in Conditional formatting

· View conditional formatting options

 

Dates:              Wed 19 Sept 2018 
Time:               9.30am – 4:00pm (morning tea & Lunch provided)
Venue:            ChiaVic, Level 1, 128 Exhibition St. Melb.       

Cost:                $418 (GST inclusive) member  - $ 440 (GST inclusive) non-member

 

***** Please bring a laptop with you to this training.

 

Click Here to register

 

*** For all inquiries about this training please email Holly at: holly.mullaney@chfv.org.au