Excel 2003 Introduction (2 days)
This course is
designed to help you to obtain the basic skills required to create and use a
spreadsheet using Microsoft Excel. You will learn to enter and edit data,
create formulas, format elements of a worksheet, and insert charts.
Target audience
Those wanting to
obtain basic skills in using a spreadsheet, such as updating data, applying
formatting, printing, and making simple calculations. Ideally, you will
have completed the course "Microsoft Office 2003 and Windows XP
Introduction". You should be able to use Microsoft Windows and basic
functions in Office to start an application, get help, and open, close, and
save files.
Course overview:
What
is Excel?
·Understand
the functions of spreadsheet application
·Recognise
the different elements of the Excel screen
Creating a Workbook Create a new workbook
·Move
around the worksheet using the mouse and keyboard
·Select
cells using the mouse and the keyboard
·Use
zoom to view a worksheet at different magnification
·Use
Undo and Redo
Entering and Editing Data Enter text, values, and dates in cells
·Edit
cell contents
·Print
a single copy of a worksheet
Creating a Formula Understand what a formula is and how to create simple
calculations
·Use
AutoSum
·Enter
a formula by typing or by using the Formula bar
·Enter
a range within a formula using the mouse
·Use
relative and absolute cell references
Moving and Copying Data Use the AutoFill tool
·Create
a custom list
·Use
Drag-and-Drop to move or copy cell contents
·Move,
copy, and paste data using the Windows Clipboard
Editing a Formula Create a formula using functions
·Revise
a formula
·Use
Insert Function
·Use
basic arithmetic functions (SUM, AVERAGE, COUNT, MIN, MAX)
·Recognise
and resolve common error messages
·Interpret
spreadsheet data
Formatting Cell Values Apply value formats (currency, percent, date, comma)
·Adjust
the decimal place
·Change
the currency symbol
·Apply
other formats (accounting, time, text, general)
Formatting Cell Contents Apply font styles (typeface, size, colour,
enhancements)
·Modify
the alignment and orientation of cell contents
·Merge
and unmerge cells (to centre headings across columns)
·Indent
text in a cell
·Use
the Format Painter to copy cell formats
·Clear
cell contents or formats
Structuring a Worksheet Modify the size of columns and rows
·Insert
and delete rows and columns
·Insert
and delete selected cells
Page
Layout Change paper size and orientation
·Set
page margins and alignment
·Scale
print output to fit a defined number of pages
·Print
column and row titles and set other options
·View
a worksheet in Print Preview
·Change
margin and column widths in Print Preview
·Insert
and remove a page break
·Set
up headers and footers
·Add
fields in headers and footers (page number, date/time, file information)
Printing Worksheets and Workbooks Print a worksheet
·Print
a workbook (all sheets in book)
·Display
formulas in worksheet cells
·Print
a range of cells
·Set,
print, and clear a print area
·Print
a group of worksheets
·Print
to a file
Creating a Chart Use the Chart Wizard to create different chart types
·Reposition,
resize, and delete a chart
·Change
the chart type
·Preview
and print a chart
·Move
and copy a chart
Excel 2003 Intermediate (2 days)
This course is designed to help you to develop your
skills in using the spreadsheet tool Microsoft Excel. You will learn to
use more advanced functions and create more complex formulas. You will
learn to manage and link together multiple worksheets. You will also
learn to apply more advanced formatting to charts and use basic
reviewing tools, such as the spelling checker and comments.
Target Audience
Those wanting to develop their skills in using Excel to
design spreadsheets to solve basic accounting and data analysis tasks.
Ideally, you will have completed the course "Microsoft Excel 2003
Introduction". You should be able to use Microsoft Excel to create,
edit, format, and print a spreadsheet incorporating simple formulas and
charts.
Course overview:
Data Lists
·Create a data list
·Use a form to view, sort, and enter records
·Perform single and multi-level sorts
·Filter a list using AutoFilter
Borders and Shading
·Apply cell borders and shading
·Apply AutoFormat
Viewing a Large Worksheet
·Go To a specific cell or named range
·Split a worksheet into different viewing panes
·Freeze and unfreeze panes as worksheet titles
·View a workbook in multiple windows
·Hide and unhide rows and columns
·Hide and unhide worksheets and workbooks
Managing Worksheets
·Switch between worksheets
·Enter and edit data on multiple worksheets
·Copy data between worksheets and workbooks
·Insert and delete worksheets
·Move and copy a worksheet
·Rename a worksheet
·Change the colour of a worksheet tab
Logical and Nested Functions
·Use the IF, AND, OR, and NOT functions in formulas
·Use nested functions
Financial Functions
·Use financial functions (PMT, FV, PV, NPV, RATE, IRR,
SLN)
String, Date, and Time Functions
·Join strings (CONCATENATE, &)
·Extract text from a string (LEFT, RIGHT, MID)
·Change case (UPPER, LOWER, PROPER)
·Find and replace text within a string (FIND, REPLACE, SUBSTITUTE, TRIM,
CLEAN, LEN)
·Convert between text and number values (TEXT, VALUE, FIXED, DOLLAR)
·Insert specific characters from the Windows character set (CHAR, CODE)
·Enter the date and time using functions (TODAY, NOW, DATE, TIME)
·Return selected parts of a date or time value (YEAR, MONTH, DAY, HOUR,
MINUTE, SECOND)
·Perform calculations using date/time values
·Use other date functions (NETWORKDAYS, WEEKDAYS, WEEKNUM)
Statistical Functions Use counting functions
·Use averaging functions
·Use other statistical functions (MIN, MAX, MEDIAN, MODE, STDEV)
Creating and Applying Styles Define a style
·Apply a style to worksheet cells
·Remove a style from cells
·Modify an existing style
·Copy styles between workbooks
Proofing Tools
·Use AutoCorrect
·Use Find and Replace
·Check spelling across the worksheet
Adding Comments
·Add a comment
·Review, edit, and delete comments
·Print comments
Formatting a Chart
·Add, remove, move, resize, and modify chart elements
(titles, labels, legend, axes, gridlines)
·Format chart elements (borders, patterns, font, values)
·Format chart axes (number format, text, line width)
·Format data series (scale, shading, spacing, width)
Inserting Pictures and Clip Art
·Understand Object Linking and Embedding
·Insert a Clip Art object
·Insert a picture from a file
·Insert a Print Screen graphic
·Insert an image from a scanner or digital camera
·Modify a picture using the Picture toolbar
·Crop a picture
Distributing a Workbook
·Save a workbook as a plain text file
·Save a workbook for a different application
·Save a workbook as a template
Creating a Hyperlink
·Create a hyperlink to a file, place in a document, or
place on the web
·Create an email hyperlink
·Use or modify a hyperlink in a worksheet
Excel 2003 Advanced (2 days)
Overview and objectives
This course is
designed to help you to develop spreadsheets in which data on different
sheets can
be linked, consolidated, summarised, and analysed using a variety of tools.
You will learn to apply different formatting options to sheets and charts
and how to use LOOKUP functions to extract data from a table. You will also
learn to distribute data in different formats, such as publishing an
interactive spreadsheet on the web.
Course overview:
·Application
Options and File Properties
·View
and change application options
·View
and change workbook properties
Using Named Ranges
·Apply
a name to a range of cells
·Apply
a name to a constant value
·Modify
and delete range names
·Print
a list of range names
·Use
a range names and labels in formulas
·Define
and modify a list range
Custom Number and Conditional Formatting
·Use
formatting options with Paste Special
·Create
a custom number or date format
·Use
scientific and fractional number formats
·Use
conditional formatting
Linking Worksheets and Workbooks
·Create
a formula to link worksheets and workbooks
·Consolidate
data using 3D references in common functions
·Use
Paste Special to link worksheets and workbooks
·Use
Paste Special to manipulate data
·Manage
and update linked workbooks
Consolidating Data
·Use
Consolidate to summarise data in multiple lists
Summarising a List
·Extract
data using advanced filters
·Group
and subtotal data using the outlining tools
·Use
Dfunctions to perform calculations on a data list
PivotTable and PivotChart Reports
·Create
a PivotTable Report
·Modify
PivotTable field layout
·Modify
field settings and grouping intervals
·Use
PivotTable AutoFormat
·Create
a PivotChart Report
Lookup Functions
·Use
VLOOKUP and HLOOKUP to get values from multi-column tables
·Sort
values in rows or from left-to-right
·Use
other lookup functions (LOOKUP, MATCH, INDEX, OFFSET)
·Prevent
lookup errors
Customising a Chart
·Change
the source data for a chart and modify data series
·Explode
segments of a pie chart
·Insert
an image into a chart
·Add
a text box to a chart
·Store
a custom chart as a chart template
Importing Text Data
·Import
data from a text file
Inserting Spreadsheets and Charts
·Insert
a worksheet from Microsoft Excel
·Insert
a chart from Microsoft Excel
Publishing to a Web Page
·Save
a worksheet or chart as a web page
·Use
Web Page Preview
Working with Objects
·Select,
position, and delete an object
·Resize
an object
·Move
or copy an object
