EXCEL INTERMEDIATE 2010

Course Overview

The Excel Intermediate course is suited to students who have either attended a basic course in Excel or spreadsheet training, or those who have been working experience with Excel and have a basic understanding of the features of Excel. Students attending this course are working with large amounts of data and are required to manipulate it to quickly locate information. They are looking for techniques to speed up their processes to manage and manipulate the lists of data they are required to work with. They are familiar with basic formulas and want to implement them more effectively.

As a result of this course students will be able to manage multiple workbooks including construction of formulas across worksheets. They will be learn how to utilise Excels advanced formatting features, manage large amounts of data and work with the sorting, filtering, outlining and subtotal tools. Competency will be gained in advanced charting, auditing techniques and templates.

Course Prerequisites

Microsoft Excel Beginner 2007 or equivalent

Raising the Bar

1300 937 782

www.rtb.com.au

MULTIPLE WORKSHEETS AND WORKBOOKS

Working With Multiple Worksheets

Rename A Worksheet
Group Adjacent Worksheets
Group Non-Adjacent Worksheets
Enter Data Into A Group Of Worksheets
Setup A Summary Sheet

Using A 3-D Formula Across Worksheets

Enter A 3-D Formula
Enter A 3-D Formula Using A Function

Combining Worksheets And Data From Different Workbooks

Consolidating Data
Linking Information From Multiple Workbooks
Managing The Formula Links
Moving Or Coping Worksheets Into Another Workbook

NAMING RANGES

Creating And Using Named Ranges

Quickly Define Named Range
Defining Multiple Named Ranges
Insert A Named Range Into A Formula
Entering Named Ranges Into A Formula From A List
Using ‘Paste Name’ To Enter Named Ranges

TABLES

General Table Setup Rules

Creating A Table

Create A Table
Change The Table Style
Create Your Own Table Style
Show / Hide Table Elements
Add The Total Row
Insert And Delete Table Rows / Columns
Resize An Existing Table
Perform A Simple Sort
Sort By Multiple Columns
Change The Sort Order
Use AutoFilter To Show Specific Records
Turning The AutoFilter On / Off
Appling / Removing A Filter
Custom Filters
Use A Search To Create A Filter
Convert A Table To A Normal Range

VLOOKUP Function

Range Names For The Table Array
Use Vlookup To Automate A Search
Enter A VLOOKUP Using the Insert Function Wizard
Enter Vlookup Functions To Assist with Data Entry
Return The Closest Match Using A VLOOKUP

Using Simple Database Functions

Sum Records Using DSUM
Average Records Using DAVERAGE
Counting Records Using DCOUNT / DCOUNTA
Finding The Highest And Lowest Values Using DMAX And DMIN

OUTLINING DATA IN A WORKSHEET

Data Outlining

Outlining Data Automatically
Removing An Outline Automatically
Auto Outlining Large Worksheets
Creating An Outline Manually
Copy The Outline Results With Visible Cells Only
Subtotal A Table
Showing And Hiding Content Using Outline Symbols
Removing A Subtotal From A Table

USING ADVANCED FORMATTING, STYLES AND THEMES

Advanced Number And Date Formatting

Built-In Number Formats
Advanced Custom Number Formats
Customising Number Formats
Customising Date Formats

Conditional Formatting Data

Highlight Cells Using A Conditional Format
Implementing Data Bars Into A Range Of Cells
Implementing Colour Scales
Use Icon Sets To Rank Data
Create New Conditional Format Rule
Edit Conditional Format Rules
Set The Precedence Of Conditional Format Rules
Stop A Conditional Format Rule
Clear Conditional Format Rules

Maintaining Format Consistency With Themes And Styles

Built-in Cell Styles
Custom Cell Styles
Apply Built-in Or Custom Cell Styles
Modifying And Deleting A Cell Style
Appling A Theme

CHARTING

Create A Chart

Insert A Chart Into A Worksheet
Modify The Chart Type
Setting The Chart Data Source
Change Grouping By Rows Or Columns
Setting The Data Series Order
Move An Existing Chart

Modify An Existing Chart

Change The Chart Layout
Change The Chart Style
Change The Chart Title
Add / Remove The Axis Titles
Change The Location Of The Legend
Add Data Labels To A Chart
Add A Data Table To A Chart
Add Or Remove Gridlines
Insert And Deleting New Chart Elements

Chart Formatting

Select Elements In A Chart
Chart Element Formatting
Adding WordArt To A Chart
Adding Pictures To A Chart
Combination Charts
Working With Pie Charts
Set The Rotation For 3D Charts
Explode A Wedge In A Pie Chart
Adding Sparklines in Excel
Modifying Sparklines

REVIEWING FORMULAS - Appendix

Using A Simple Formulas In Excel

Using The Keyboard To Enter A Formula
Using Manual Operators In A Formula
Relative Cell References
Absolute Cell References
Applying An Absolute Cell References To A Formula

Working With Simple Functions

Entering A Simple Function
Use The AutoSum Button To Calculate Totals
Using Insert Function To Enter A Formula