|
Microsoft Excel is
the spreadsheet application of choice for
most desk top computers running the Windows operating system (it is also
available on Macintosh computers).
As you know from our
earlier course, cells in spreadsheets can hold both numbers and text data. Excel
is capable of manipulating
this data in the twinkling of an
eye - adding, subtracting, multiplying and applying formulae to
extremely large numbers. Excel can also manipulate textual information
very efficiently - finding text information in vary
large arrays of data and efficiently sorting large lists of text is part
of Excel's strength.
Our
earlier course covered the
basics of spreadsheet use. This course builds on that
knowledge and
explores some of
the more sophisticated features of Excel.
We will begin by discussing
spreadsheet design - how can a builder of spreadsheets organize the
operations of a sheet to make it easier for the user? Then we will show you how to create and edit
macros to speed up repetitive
tasks, add buttons to toolbars with links to those macros, set up error
checking in data entry, scan data for variables, import data from other
applications, create name ranges, use some advanced functions,
set up sophisticated scientific charts, introduce some
statistical analysis (Chi squared tests, Student's T-Tests), validate
spreadsheets, introduce the Visual Basic for
Applications programming language, work with ranges
and group sheets etc..
 |
Click
here to play a movie which
summarizes the earlier course. The movie is quite large
(5Mb), but if you are contemplating taking this course with
us, we would encourage you to download and play it. |
A sample from the course - using the
LOOKUP function.
|
|
Click
here to play a movie which
illustrates the use of the VLOOKUP function. |
Excel
is a very
sophisticated application indeed. Even in this so-called 'Advanced' Excel course, we will not be
able to cover all aspects of the use of the software.
However, after
you finish, you
will be certainly be able to create some quite sophisticated
spreadsheets.
We offer three different pricing models covering
the same material:
1.
group or one-on-one courses (Adelaide or Canberra - in our offices or
yours)
Each course takes one day and
costs $AUD750.00. A
comprehensive workbook on CD-ROM and sample files is provided
to each participant. Participants are given access to the same
materials on our broadband learning web site. Here you can
complete additional exercises, join various forums, take
multiple choice tests and submit work for comment.
2. CD-ROM based distance learning -
Many courses are available via
our
distance learning program at a
substantial discount -$AUD280.00. Again, we provide a CD-ROM containing many instructional on-screen
movies. The whole course can be completed at home or at work, on
your own computer, at your pace. No Internet connection is
required.
3. Online learning via broadband - fast Internet connection required
Many courses are available by online
learning at $AUD250.00 - these courses feature rapid enrolment,
online forums, multi choice reviews questionnaires etc. Click
here to visit our online
learning site. A reasonably fast broadband Internet connection is
required. Dial up access is not suitable.
Experience over many years has taught us that adults learn best by
progressing through a course of study at their own pace and at times
that suit them. Wherever possible, they like to learn by working on
their own materials, rather than work through long practice exercises. Our courses are specifically designed so that this can
occur- we introduce a topic, provide a movie which shows you how to do
it and then give detailed step-by-step instructions for you to follow.
Courses may be taken at Thebarton in Adelaide, South Australia, in
Canberra in hired facilities, or by distance learning or
broadband learning.
If a course is taken 'in-house' at Thebarton, it normally runs for a
whole day
with a break for lunch. Starting time is 9.15 am and finish time around
4.15 - 4.30 pm. We usually work group or one-on-one, but can take up to 3 students
in our training room. **We only run groups if all students come
from the same organization.**
We
use
PayPal, an organization which takes all credit cards
using a secure Internet facility. PayPal is a partner
within the eBay system. We have chosen PayPal because it
is independent of us, allows us to hold money in trust
until we have delivered the course and is now
operational in Australia. We will provide a tax invoice on
receipt of payment.
If you cannot attend after booking a
course, there is no penalty, we will simply re-schedule
another session.
If this is not possible, we will refund payment in full.
Here is a link to
background material on the philosophy behind our course
design.
If your company or
organization is paying for the course, all we need is a
purchase order and an address to send the course
materials.
An
understanding of basic
computer operation including the ability to create, save and locate files. We
would expect that you have used a computer with
the Windows operating system for some
time and perhaps developed reasonable facility
with a word processor
such as Microsoft Word.
In this course we want take you on a journey
where we work with examples of the use of Excel in a number of different fields.
In some instances we will conduct 'case studies' where we
work from an
initial idea of how to solve a problem, to the generation of a
finished
'professional' spreadsheet which does the job. We encourage you to
drive your learning, either by brining along your particular Excel
problem or by selecting modules from the list below which are
appropriate to your needs.
We
hope that you will return to us and show us what you have been able to
achieve as a result of your study. We would be pleased to receive
example spreadsheets from different disciplines as teaching aids.
The course consists of a series of
modules which even though we have said that there is no particular order
of importance, is perhaps best taken in sequence. [There
is of course, the opportunity to revisit modules in any sequence at a
later stage.]
 As in
our introductory
Excel course, we will
work though a series of modules . Unlike the earlier course however, this time no importance should be attached to the
order in which we cover various topics. We encourage you to examine the
list of topics below and then indicate those of most interest to you in
an email to us. This will help us prepare the ground for your visit with
us.
|
Revisiting the Excel Interface |
A second look at the Excel interface.
Examining the 'Getting Started' pane. Manipulating toolbars.
Problems occurring when the standard and formatting
toolbars are combined. A look at the functions available on the
various Excel toolbars - standard, formatting, borders,
chart, control box, drawing, exit design mode,
external data,
forms, formula auditing, list, pivot table, visual basic, word
art, picture, reviewing and web toolbars.
Creating a new custom toolbar; populating toolbars with icons.
Adjusting the properties of a toolbar item.
Naming sheets, switching between
worksheets, copying and moving
worksheets around in a workbook. The value of input sheets. The importance of mouse pointer. Manipulating
toolbars. Splitting and freezing the screen. |
|
Using forms |
Forms provide a convenient
mechanism for entering data into spreadsheets. We use a
spreadsheet which contains a form used to scroll through a large
data list. We use a find facility built into the form and then
show how to build your own form and populate it with action
buttons from the toolbox. |
|
Ranges |
Why use named ranges in
Excel? Using ranges effectively. Examining the various methods of creating named ranges.
Using named ranges across different sheets in a workbook. Using ranges in
a formula which draws on data in different sheets and calculates average values
on a summary sheet. Ranges live in memory
- creating a named range which reports year in a cell. The value
of named ranges when using drop down lists and Data Validation. Case study: creating
range names in a spreadsheet of your own. |
|
Charting |
This module
covers the generation of charts in Excel more comprehensively
than was done in the earlier course. We
survey chart types, creating various charts using the
chart wizard, show how to graph non-contiguous data, select data ranges,
add to data ranges etc. Emphasis is placed on formatting
charts for ease of data interpretation. Instruction is provided
on moving and re-sizing a chart, changing chart data,
3D charts, adding extra data, placing charts on separate sheets, adding
a trend line to a graph, linking charts to other
applications using both DDE and OLE links. |
|
Spreadsheet Design:
-
from a user perspective |
In this module we
consider some of the mechanisms which can be used to increase
the functionality and usefulness of spreadsheets. We emphasize
that the 'look and feel' of an Excel application is important.
Advice is provided on the value of dividing a workbook into a logical
set of sheets. The value of an input sheet. Formatting cells for
input and output. A brief look at error checking, cell
validation, naming ranges etc. |
|
Performing Calculations |
Using static values in a worksheet. Entering formulae in cells. Using Excel to perform calculations.
The value of the F9 key to recalculate values in a sheet -
generating a set
of random numbers. Displaying formulae in a sheet. Printing
a master copy of your formulae for audit purposes. A general
discussion regarding functions in formulae. Some common
functions - minimum cost, average cost, total costs, number of
costs and the count function. Using operators in formulae.
Using constant values in formulae. Displaying
formulae in a spreadsheet. Order of calculation - operators.
Using logic operators - IF, NOT, AND in formulae. Locking data (and formulae) in cells.
The importance of correctly formatting data in cells. |
|
Using the IF
statement |
Using if to set up
conditional statements - checking columns of numbers for value
above and below a nominated number. Paying bonuses to sale staff
based on performance. |
|
Using the LOOKUP function |
Experimenting with
this great time saver. Examining a sheet which automatically
grades student results. Using VLOOKUP to create drop down
lists of allowed input values. |
|
Goal Seeking |
Using Excel to find
the 'solution' to a problem. We change a single input in a cell
and
have Excel calculate a desired target result. |
|
Grouping |
Using the group option
to 'pull' data from different workbooks as a summary. Grouping
and outlining. hiding details, promoting and demoting groups. |
|
Managing
Stocks & Shares |
Using Excel to keep track of a share
portfolio. Setting up a share analysis worksheet, buy and
sell spreadsheets covering various stock market sectors.
Advanced sorting techniques are illustrated and links from Excel
to Access databases are established. |
|
Pivot tables |
Using the pivot table
wizard, building pivot tables.
Pivot table reports. |
|
Access/Excel interactions |
Using Excel to hold
database information. Excel as the best 'root' container for
your Access applications. |
|
Solver |
The companion approach
to goal seek. Using Excel to find the 'solution' to a problem in
which there are multiple variables. |
|
Mail Merge |
Mail merge is not a
simple process. However, when Excel is used in combination with
Microsoft Word, merging can be set up to proceed smoothly. In
this module we using Excel as a
database to hold details of names and addresses, create a form letter in
Word, review the data source information, add merge fields and merge
this data with the form letter. The filtering
data functions in Excel/Word are used to produce letters for
specific sub groups. |
|
Some Preliminaries
|
Exchanging
spreadsheet data with
different versions of Excel. Scrubbing diskettes for safe export of
data. Different methods of starting Excel.
Creating a suitable folder structure to hold Excel workbooks.
Changing the user preferences - adjusting the AutoSave time and back up
type. Changing
the International settings.
Installing 'add-on' applications such as Microsoft Map and
statistical packages.
|
|
Export/Import |
Understanding Excel's data format.
File types supported in Excel. Text files; *.prn, *.txt, *.CSV, Backup files; *.xlk, *.bak, Data Interchange Format; *.dif, SYLK files; *.slk, Toolbars; *.xlb, Add-Ins; *.xla, xll, Templates; *.xlt, Workspaces; */xlw, dBase files; *.dbf, Works 2.0; *.wks, Quattro Pro; */wq1, Lotus 1-2-3; *.wk?,
Web Pages; *.htm etc. Importing data by opening CSV files. Exporting
data from Excel - space delimited & comma delimited files. Linking
Excel to Microsoft Access - two methods of sharing data. Sharing Excel
sheets among different users. Exchanging workbooks with other
users.
|
|
Installing Add-Ins
to the Excel environment |
Installing additional functions in Excel.
Developing a spreadsheet for calculating the interest rate on an
investment using one of these extra functions - the INTRATE function.
We develop a model for calculating the 'proper' rate of return
on an investment. Installing tools for statistical analysis. |
|
Using
Excel Filters
|
A case study updating and
enhancing a spreadsheet used to manage attendance of teams of
workers working rotating shifts. Using the COUNT function, a practical example of the database functions in Excel. |
|
Fundamentals |
A review of
spreadsheet fundamentals. Displaying formula in a sheet. Static
(constant) and dynamic data. AutoFill. Creating series of
different types. |
|
Making Excel perform calculations |
Reviewing your
knowledge of building formulae. Entering formulae, editing
formulae, functions, AVERAGE, MAX, MIN, COUNT, mathematical
operators, sequence of operations, logic operators, range checking.
Exposing formulae in a sheet. The mechanism for locking cells containing with formulae.
Protecting worksheets. |
|
Cell references |
A review of relative
and absolute cell references. Copying cells with references,
switching between absolute and relative references. Using Excel
as a database, using filters. |
|
Macros and Visual Basic for
Applications |
The first step in
automating tedious tasks in Excel is to learn to create macros.
We create and edit various macros. Groups of macros can be combined using the Visual Basic
for Applications
programming language. |
|
Designing
& building spreadsheet models |
Designing a model on paper before moving to the computer,
specifying the problem in pseudo - language, identifying the variables needed to solve the problem, gathering together any algorithms that we may need to solve
the problem, build the sheet. |
|
Data lists |
Database applications in
Excel. Using Auto filters, advanced filtering, creating a custom
filter, finding records, sorting
sub-totals etc. |
|
Analyzing scientific data |
XY charting, Chi
square test, t test, linear regressions, etc. |
|
Validating spreadsheets |
Making sure that your spreadsheet gives the correct answers.
Testing your spreadsheet on a regular basis for compliance. |
|
Graphic elements in
spreadsheets |
Using the graphics
toolbar, types of graphic files suitable for use in the Excel
environment, formatting objects, resizing graphics. |
|
Customizing toolbars |
Creating new toolbars,
placing new buttons on toolbars, editing and removing buttons. |
|
MYOB to Excel |
Importing data from
accounting packages such as MYOB and QuickBooks. Using Excel to present
accounting
data in a palatable
form. |
|
Excel functions |
An overview of the use of functions in
formulae using some case studies. |
|
