Course Aims:
In this course we will teach you to create templates so that files are in keeping with the corporate image. We will learn techniques to speed up navigation and assist with simplifying formulas. Work effectively with multiple sheets and multiple files and use auditing tools to track data. Use utilities to quickly filter large files and present them with uniform formatting and layouts.
Contents:
Creating & Using Templates
About Templates and Template techniques
Create New Workbooks from existing files
Tips and tricks for designing Templates
Cell, Sheet and File Protection
Working with Multi-Sheet Files
View areas of a workbook with split panes
Freeze Panes
Group sheets to work on several at once
Create Links between Workbooks
Multi-sheet Formulas and Functions
Performing Special Pastes
Retaining Formats when Pasting
Name and Colour Sheet Tabs
Move and Copy Sheets to New Files
Naming and Labeling Ranges
Relative v Absolute Addressing
Simplify Formulae with Range Names
Name a Range of Cells
Naming of Cells with Create Label feature
Naming of Grouped Cells across sheets
Using the Range Navigator and F5
Calculating with Range Names
Use Auditing to control and track data
Excel Functions
Formulas v Functions
Screen Tips for Functions
Conditional Functions SumIF( ), CountIF( )
Nesting Functions such as IF( )
Search for Values within a list
VLOOKUP( )
HLOOKUP( )
AutoFill Formulas & Functions
Charting
Creating Quick Charts
Tips and Tricks
Formatting Charts
Custom Formatting
Create Custom Number Formats
Formatting of Text, Blanks and Zeroes
Conditional Formatting
Database Lists
What is a Database List
Sorting Data
Multilevel Sorting
Using AutoFilters
Automatic Sub Totals
Advanced Filters
Extracting Data with Criteria
Database Functions
Target Audience:
Current users of Excel that wish to increase their depth of knowledge and for anyone who has mastered Excel's fundamentals and is ready to get the tools, tips, techniques and shortcuts required to become a power user.
Assumed Knowledge:
Attendees must have a thorough understanding of spreadsheet fundamentals, and have used simple functions and formulas in Excel.
Duration: 1 Day
Suggested follow-up course:
Excel XP Specialists
